Today I am going to talk to you about Microsoft Excel so that you feel like you have Excel superpowers.

I am going to give you insanely actionable Excel tools to use for corporate and personal finance.

To kick things off I am going to start with an introduction of Excel.

Like so:

1. Introduction to Microsoft Excel

Look:

Most people think that Excel is overly complicated and would like some guidance on how to use it properly.

So it definitely pays off to go back to the old definition of what Excel actually does. Excel essentially does three things:

a. Calculations, which include things like adding a column or calculating a total monthly loan payment. We can add some numbers, or find a minimum of some values or simply subtract numbers from each other.

b. Data analysis. That’s when you look at raw data, you organize it in some way and then come to a conclusion. A very simple example of this is illustrated in the figure below (1). If we for example have many interest rates and we would like to find the lowest we can do that with Excel.

c. Excel also stores raw data, but that is out of the scope for this article.

Figure 1. Explanation of what Excel does.

Columns and rows

Excel is a two-dimensional grid where you have different columns horizontally and rows vertically.

Cells

The intersection between a column and a row is called a cell. If we for instance highlight the green cell to the right of the numbers and below the Add-cell, you can see that cell is called I3.

That is the heart of Excel because as you will see in this series you can reference a specific cell to make formulas.

Range of cells

A range of cells is when you choose a number of cells in order to make a calculation. In the example above we can highlight F3, G3 and H3 to select this range.

Worksheet

What about all the cells?

That is called a worksheet.

Sheet tab

A sheet tab is shown below:

Figure 2. Explanation of sheet tabs.

What you can do here is to double click on it and change the name of the sheet.

CTRL + PgUp/CTRL + PgDn

To change between the different worksheets you can use the shortcuts CTRL + PgUp or CTRL + PgDn which is very useful.

Ribbons

When I use Excel I always have my Ribbons open so that I have easy access to the functions.

It looks like this:

Figure 3. Screenshot of Excel ribbons in open state.

Now, if you want to close the ribbons for whatever reason you can just right click on your mouse and select the “Minimize the ribbon”-option when you hover over the Home tab.

Quick Access Toolbar

We then have the Quick Access Toolbar which looks like this:

Figure 4. Screenshot of the Quick Access Toolbar in Excel.

What the Undo button does is that it undo:s the last action and the Redo button undo:s the Undo.

If you have a particular task that you like and use often, you can put it in the Quick Access Toolbar by right clicking your mouse and scroll down to the “Add to quick access toolbar” option.

Scroll bars

Sometimes in this article series I am going to show you large tables. The way to navigate up and down in these is by clicking on the scroll arrows or pulling the scroll box.

Formulas and functions

There will be separate articles on this concept, but just to give you foretaste I will show you how it works:

Let’s say that I want to subtract 85 from 98. How do I go about doing this?

Figure 5. Screenshot of Excel where we are going to focus on the subtraction part.

In order to do that you’ve got to know how to tell Excel that what it wants is a formula in I9.

If you just type an equal sign in I9, you’ve just to Excel that what is coming is a formula.

What you do next is that click on G9 for the first number.

Then you type a minus sign on your keyboard and click on H9 for the second number.

When you then hit Return you will get your answer in I9.

2. Formulas and functions

In this chapter we are going to go a little bit deeper into the formulas and functions that we discussed last time.

First of all let’s repeat what I talked about in the last chapter. The way to tell Excel that you want to input a formula is by typing an equal sign (=).

An illustration:

Figure 6. Screenshot of Excel showing a formula.

For the sake of argument, let’s say that we have an interest rate of 8.4 percent (C3) and this has to be paid twice a year.

To figure out how much you have to pay each time we then have to divide the number in C3 with 2, which is the number in C5.

Instead of taking each cell the way we described above, many people type the numbers directly. This of course gives the same result, like so:

Figure 7. Screenshot of what never to do in Excel.

The problem comes if we want to update the rate. Then the C5 is updated automatically whereas C6 stays the same. You can see what I mean in this picture:

Figure 8. Example of dynamically updated cells in Excel.

There are three reasons for why you want to update your data dynamically like that:

It’s faster to change the numbers directly in the cells.

If you’ve labeled your cells properly, you clearly see what the individual numbers are.

If there is a spreadsheet filled with direct edits and they are not labeled properly it’s difficult to edit.

To calculate the sum of different numbers, there’s a nifty shortcut to use: Alt + =

Figure 8. Screenshot of Microsoft Excel and of how to use a keyboard shortcut to obtain the sum of several numbers.

When you type the keyboard shortcut, Excel will guess which numbers you want to add together and there will be “marching ants” around these.

When you are done selecting the numbers you want, just hit ENTER and the formula will automatically update.

What you don’t want to do is to type =sum and then click on D2, D3, D4 and D5. The reason for this is that a range (as we have in Figure 8) is dynamic. This means that you can insert or remove numbers to your liking, whereas if you insert numbers into a formula with only clicked numbers, the final sum will not update.

We are going to look at a few more formulas and we will begin with an average or a mean:

Figure 9. Screenshot of Excel where I calculate the average of four numbers.

What I do is that I type in the start of the formula and Excel then give you suggestions of what to choose based on what you have typed. Then you can select the corresponding correct formula and press the Tab key to select. As always Excel then guesses which numbers you want to choose.

We then want to calculate a loan function.

Figure 10. Screenshot of Excel where I calculate the yearly loan payment.

What I want is my yearly interest payment. First of all I click in C5 to activate the cell. Then I click the fx in the upper center of Figure 10. Then I begin to type “Loan payment” in the dialog box where it says “Search for a function”.

You will then get a list of a number of different functions that you can use, like so:

Figure 11. Screenshot of the functions dialog box after typing “loan payment” in the search area.

Then you have to read the description to figure out exactly what the formula does. In my case the description of PMT seems fine so I click OK.

I then get this dialog box:

Figure 12. Screenshot of the Function Argument dialog box.

Here it says that I’ve come to the Function Arguments box.

What is then an argument?

It’s a mathematical term of the individual parameters within a formula. In my example there are three different arguments that I want to insert and they are highlighted in bold.

The first argument being the rate so I go back and click on C3 to insert the rate.

The next argument is the number of periods and because I have annual rates in my example, I simply click on C4 for the number of years.

The last argument is the Present Value which in our case is how much the loan is worth right now. So I click C2, like so:

What you see to the right of the dialog box in Figure 13 are the actual numbers of the cells that I’ve entered. Down below you will see the unformatted result of the formula.

Down to the left I get the formatted result which has parentheses. These are financial numbers so parentheses mean that the number is negative. The reason for this is that if you have borrowed that amount of dollars at that rate you will get a negative number because the money will come out of your wallet.

One other thing that I will talk about is screen tips. When you have done these numbers for a while you will be fluent. Once you are fluent, you can just type =PMT( and you will see that the formula pops up in the screen:

Figure 14. Screenshot of what happens once I type in the formula name in Microsoft Excel. The required parameters are highlighted.

These screen tips allow you to see which value to enter next. You can between the different values by typing a comma on your keyboard.

3. Math Operators and Orders of Operation

I now want to turn your attention math operators and orders of operation. In this part of the article I will talk about all operators below:

Figure 14. The different math operators that are used in Microsoft Excel.

In Figure 14 you can see how you can type the different operators.

Let’s see some math examples:

Adding

Figure 15. Screenshot of Excel to show how you add, subtract, multiply, divide and put your numbers as an exponent.

If you want to add two numbers together the easiest way is to type Alt + = in the result cell. In my example that is B5. That way you will get a formula of =SUM(B3:B4) and if you hit enter Excel will calculate that addition for you, like so:

Figure 16. Screenshot of Excel where I have typed Alt + = and gotten the result =SUM(B3:B4).

Subtracting

To subtract a number from another I type an equal sign in C5 and then I use the mouse to click in C3 (or the up arrow twice on my keyboard). Then I type a minus sign on the number pad and click in C4:

Figure 17. Screenshot of Excel where I subtract one number (C4) from another (C3). The result is shown in cell C5.

The result is then shown in C5.

Multiplication

There are two ways to multiply numbers in Excel. The most common is to type an equal sign and then click the individual numbers, like so:

Figure 18. Screenshot of Excel to show multiplication of numbers.

You can also use the PRODUCT function, like so:

Figure 19. Excel screenshot of another way of multiplying numbers by using the PRODUCT function.

Division

To divide two numbers we first type an equal sign and then click on the individual numbers. In this case E3 is called the numerator and E4 is called the denominator:

Figure 20. Screenshot showing how to divide two numbers when the nominator is bigger than the denominator.

If the numerator is greater than the denominator, the result will be a number that is bigger than 1 (E5):

Figure 21. Screesnshot showing how the resulting number is greater than 1 if the nominator is bigger than the denominator (E5).

Similarly, if the nominator is less than the denominator then the resulting number will be less than 1 (F5):

Figure 22. Screenshot of a division when the denominator is bigger than the nominator (F5).

Exponent

If I want to take the number in cell G3, which in my case is 5, squared, I first have to type an equal sign. Then I have to click in G3 to get the number in G3. Then I have to type Shift + 6 to get to the caret symbol. Then I can click in G4:

Figure 23. Screenshot showing how to do cell G3 squared. It is done with the caret symbol (shift + 6).

That means that the base is 5 and the exponent is 2. The result is then shown in cell G5, like so:

Figure 24. Screenshot of Microsoft Excel to show how to type 5 to the power of two.

Logical formulas

Are two numbers equal?

I will now show you how to use logical formulas. If I want to check if two numbers are equal, I click the first cell and then the second:

Figure 25. Comparing two numbers (B3 and B4) to see if they are the same with an equal sign (B5) and comparing if a number (C3) is greater than the other (C4).

It may come as a surprise, but when I hit Enter now I see that I get the Boolean value “False” in B5.

Figure 26. Microsoft Excel screenshot of getting the Boolean value False in cell B5 when comparing the two numbers.

Why is that?

It comes from that Excel does not always show you everything. In this case if I click in cell B3, you can see that the value is not 5, but really 5.02:

Figure 27. Screenshot of Microsoft Excel where the real value in cell B3 is different from the displayed value.

How can that be?

This comes from the number formatting in Microsoft Excel. If you look up in the formula bar you see that the number in B3 is actually 5.02 and not 5.

How do you go about changing this?

The answer is that you change the number of displayed decimals simply by clicking the Increase Decimals or Decrease Decimals box like so:

Figure 28. Screenshot of Microsoft Excel showing how you can increase or decrease decimals by clicking the buttons.

Is one number greater than another?

I then want to check if a number is greater than another number.

Is the value in C3 greater than the value in C4? I do that with a greater than symbol.

Figure 29. Here we want to check if the number in cell C3 is greater than the one in C4.

And of course if I hit Enter:

Figure 30. Screenshot to show the Boolean value TRUE in cell C5.

I get the Boolean value TRUE in cell C5.

Order of operations

The next thing that I will talk about is the order of operations. In particular I will focus on the question if 2+2*4^2 is 256 or 34:

Figure 31. Screenshot of Excel where the orders of operations are given in descending order. Then I ask the question if 2+2*4^2 is 256 or 34.

If I calculate from left to right, the first calculation is 2 + 2 which is 4. Then the next is times 4 which would make 16. If I then take that number squared I would end up with 256.

But that is not the way calculations work. The parenthesis always come first followed by exponents, multiply and divide follow and addition and subtraction come last.

If we then type in the formula in the example into Excel we will see this:

Figure 32. Screenshot of Microsoft Excel showing the result of the formula in cell C8. The result is shown in C9.

So Excel knows in which order to do the calculations and it does it automatically.

The correct way of calculating is first to look for any parenthesis, but in this case I don’t have any. Then I see that I have look at exponents where I have 4 raised to the second. This turns out to be 16. I then have to multiply that number with 2 which gives 32. Finally I have to add 32 with 2 which gives a result of 34.

There is a neat acronym to memorize the order of operations:

Please Excuse My Dear Aunt Sally.

If, on the other hand, I want to have 256 as a result I have to put parenthesis around the number, like so:

Figure 33. Screenshot of Microsoft Excel showing how to force parenthesis into a calculation. The result is shown in cell C9.

If I hit enter I will get the result 256 in cell C9.

Evaluation of formulas

In this section I want to show you how to evaluate a formula.

What I do is that I go up to the formulas ribbon where I first click “Formulas” and then “Evaluate formula” as shown in the picture: