Magic Formulas in Excel

Excel is part of Microsoft Office. These articles discuss the basics of the program:

• Start with Excel
• Formatting and formulas in Excel
• Calculating with Excel

At the very bottom of the program there is invariably a green or gray bar. On the left the text ‘Done’, on the right ‘Display settings’. But if you select more than one cell, you will see more information in the bar.

Try it yourself. Fill some cells with numbers and select them.

• At the bottom appears ‘Average’, ‘Count’ and ‘Sum’.
• Right-click on an empty space in the bar. The menu Customize status bar appears.
• Place a check next to ‘Numerical count’, ‘Minimum’ and ‘Maximum’.
• Click anywhere outside the menu to make it disappear again.
• From now on, when you select a range of cells, you will immediately see these categories of data in the bar:

Average
Calculates the average of all numbers within the selection. The function only recognizes numbers: ‘7’ counts but ‘seven’ does not.

Number
Shows the total number of non-blank cells within the selection.

Numerical Count
Shows the total number of non-blank cells with numbers. So ‘7’ counts, but ‘seven’ doesn’t.

min
Displays the smallest number within the selection.

Max
Displays the largest number within the selection.

Sum
Calculates the total of all numbers within the selection.

Suppose your energy advance changes and you would like to know by what percentage.

• Make sure that the numbers you want to compare are in cells of the worksheet, for example in cells A1 and B1. You want to know how much number B1 has increased or decreased compared to number A1.
• Click in cell C1.
• In the ribbon (the collection of possibilities in the wide gray bar at the top of Excel), find the options that are collected above the word ‘Number’. Click there on the percent sign (%).
• Now type the following: =(B1-A1)/A1. That text appears in cell C1.
• Press the Enter key.

The result of the calculation is in cell C1. Now if you type other numbers in cells A1 or B1, the result will be adjusted automatically.

Do you want to know exactly how old someone is, how long you have lived in your place of residence or how many days have passed since a certain day? Use the Date Difference formula.

• Type a date in cell A1.
• Type a date in cell B1.
• Click on cell C1.
• Click in the formula bar to the right of fx.
• Copy the following formula: =DATEDIFFERENCE(A1;B1;”d”)
• Press the Enter key.

Cell C1 now shows the number of days between the two dates. Are the dates in cells other than A1 and B1? Then adjust the formula by replacing A1 and B1 with the desired cells.

Curious on which day a particular anniversary falls? This can be difficult to calculate, especially with half years, such as 12.5. Make it easy on yourself and use a formula in Excel.

• Click in cell A1.
• Click in the formula bar to the right of fx.
• Copy the following formula: =SAME.DAY(“9/1/2019”;12.5*12) and paste it into the formula bar.
• Change 1-9-2019 to the desired date.
• Change the 12.5 to a different number if necessary.
• Press the Enter key.
• A large number appears on the screen. Press the button a above the cells in column A.
• If necessary, click tab Start if not already selected.
• In the ‘Number’ group, click the arrow next to the white box.
• Scroll down the menu.
• click on Short date format.

Now the number changes to the anniversary date.

Is July 6, 2025 a Sunday by any chance? Or a Wednesday? With one of Excel’s formulas, you don’t have to guess.

• Click in cell A1.
• Enter a date, for example 11/18/2025.
• If necessary, click tab Start if not already selected.
• In the ‘Number’ group, click the arrow next to ‘Default’.
• Go down in the menu.
• In the ‘Number’ group, click the arrow next to the white box.
• Scroll down the menu.
• click on Long date format.

You will now see the day of the week next to the date. In this example, it says “Tuesday, November 18, 2025.”

Do you regularly use address lists? And would you like to put the names or places of residence in alphabetical order? Which can! The following ‘trick’ works for directories where there are no empty rows or columns between the data. So for lists that look like this:

• Click on one of the surnames.
• Click on the tab Start.
• Click on the top right Sort and filter (click on the arrow under ‘Edit’ first) > Sort from A to Z.

The surnames are put in alphabetical order and the rest of the data that goes with it is moved with it.

In this way you can also sort by first names, surnames, addresses and postal codes. Postal codes are numbers, but they are also displayed in ascending or descending order by Excel.