## How to use Microsoft Excel as a finance tool

Knowing Microsoft Excel is great. Knowing finance is even greater.

But when you can do BOTH?

That’s when you call yourself Superman!

…because there’s nothing stopping you!

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

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. What the Undo button does is that it undo:s the last action and the Redo button undo:s the Undo.

### 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:

1. It’s faster to change the numbers directly in the cells.
2. If you’ve labeled your cells properly, you clearly see what the individual numbers are.
3. 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: Figure 13. Screenshot of the Function Argument dialog box with individual cell references.

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: 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: Figure 34. Screenshot of Excel showing the localization of “Evaluate Formula”.

When I click on “Evaluate Formula” I get a dialog box, like so: Figure 35. Screenshot of Excel showing the “Evaluate formula” dialog box.

Then when I click on “Evaluate” Microsoft Excel will calculate the underlined value for me. This comes in handy when you want to check your formula and you don’t know the order of operations.

4. Number formatting

The next thing that I will talk about is number formatting. The reason for me to do this is there are a lot of people who get it wrong and that we want to avoid.

If I for instance want to change a naked number to a number with a comma and two decimals I first highlight the numbers, like this: Figure 36. Screenshot of Microsoft Excel where I highlight C3 to C5 in order to change the appearance.

If I then click “Ctrl” and “1” at the same time I open the Format Cells dialog box. Now if I click on “Number” I will get this: Figure 37. Screenshot of Excel showing the Format Cells dialog box.

Here I can decide how many decimals I want to show. I this case I go for zero and I click the box that says “Use 1000 Separator (,)”: Figure 38. Screenshot of Excel where I’ve chosen to display my numbers without any decimals but with a 1000 separator.

Next thing that we want to look at are the numbers in A3 to A5. They all say \$67.35 but if I look closer I see that what it really says is \$67.351383489, like so: Figure 39. Screenshot of Excel to show that the number in A3 (\$ 67.35) really masks the longer number 67.351383489.

If I want to get rid of the number formatting in A3 to A5, I type Ctrl + Shift + ~ and I get get this result: Figure 40. Screenshot of Excel to show how to get rid of number formatting by typing Ctrl + Shift + ~.

If I then jump to Currency in column D, you can see that by pressing Ctrl + Shift + 4, I get the currency formatting that I want: Figure 41. Screenshot of Excel to show how to format currency with Ctrl + Shift + 4.

If you then check with Ctrl + 1, you can see that the Format Cells dialog box has indeed chosen Currency.

What is the difference between Currency and Accounting?

If I highlight cells E3 to E5 and click on Accounting in the format drop down-menu, like so: Figure 42. Screenshot of Excel showing the Accounting option in the format cells drop down-menu.

What happens when you click Accounting is that all the numbers that you are working with will align so that you can compare them directly.

I will be talking about loans and cash flow later on where you will have to do math on those dates.

Let’s say thatI have a loan that is due on a specific date and today is another date and I want to know how many days the loan is behind: Figure 43. Screenshot of Excel showing two dates where I want to know how many days are between them.

In the cells there are dates written, but behind the scenes Excel consider these as numbers. That means that there is an actual number there where Excel can do calculations.

You can check which actual numbers Excel uses by changing the formatting drop down to Text. First I highlight cells F7 and F8 and then change to Text: Figure 44. Screenshot of Excel to show which numbers it is using behind the scenes.

You can then see that Excel use the numbers 42675 and 42712. If you want to know the difference between those you can write a formula: Figure 45. Screenshot of Excel showing how to calculate the difference between two dates.

First of all I’m typing an equal sign, then I click on the later date (F8), I click on the earlier date (F7) and hit Enter.

The result is 37, like so: Figure 46. Screenshot of Excel showing the difference the dates in cell F8 and F7.

If you get a date as a result, it’s because the formatting is set to date. Just change to General (Ctrl + Shift + ~) and you will see the number.

The keyboard shortcut for today’s date is Ctrl + ; so if I for instance would like to add today’s date in cell F3, I first activate the cell by clicking in it and then type Ctrl + ; like so: Figure 47. Screenshot of Excel showing the result of typing Ctrl + ; (today’s date).

If I then want to increment my dates I click in the bottom right corner of cell F3 to get the crosshairs. Then I can simply drag it down: Figure 48. Screenshot of Excel where I increment dates by dragging down the fill handle. The red arrow points to Fill options.

If I then click on the Fill options handle I can choose to increment months or years instead of days. That is going to be handy when we want to analyze cash flows.

If we now want to know which numbers Excel use behind the scenes, you can highlight the dates that I just used (F3 to F5) and click General: Figure 49. Screenshot of Excel to look at which numbers Excel use behind the scenes. The result is shown in cells F3 to F5.

In Figure 49 I went from a date to a number so let’s do the opposite instead. I cells G3 and H3 I have written 1 and 2: Figure 50. Screenshot of Excel where I want to check what dates 1 (G3) and 2 (H3) are.

How do I make these numbers into dates? I type Ctrl + 1 to bring up the Format Cells dialog box. Here I choose the option “Date” and the format 3/14/01: Figure 51. Screenshot of Excel showing how to go from a number to a date by option Date in the Format Cells dialog box.

The result then becomes 1/1/00 where 00 is short for 1900: Figure 52. Screenshot of Excel where I show the dates given by the numbers in F3 to G5.

As a sidenote I will now discuss how dates and text are differently treated by Microsoft Excel: Figure 53. Screenshot of Excel showing that numbers and dates are aligned to the right while text is aligned to the left.

By default, Excel align numbers to the right and text to the left. From Figure 53 it is obvious that our dates are aligned to the left. That means that I have made something wrong in the formatting: Figure 54. Screenshot of Excel showing a left aligned date by using an apostrophe as lead character in cell C3.

What has happened here is that we have started the date in cell C3 with an apostrophe and in C4 I’ve formatted the date as “Text” in the number formatting.

The reverse of converting dates to numbers can also be achieved. I have some numbers in cells I3 to I5: Figure 55. Screenshot of Excel showing numbers in cells I3 to I5.

Then I go to the number formatting drop down menu and choose “Short Date”: Figure 56. Screenshot of Microsoft Excel showing the Numbers Formatting Drop Down Menu where I choose “Short Date”.

Then I click and I get these dates out: Figure 57. Screenshot of Microsoft Excel showing dates in cells I3 to I5.

You can also find the maturity date of a loan that is issued on a specific date: Figure 58. Screenshot of Microsoft Excel showing how to calculate a maturity date of a loan issued on a specific date.

In this case the Loan Issue Date is in cell C3 and the Loan Length In Days is in cell C4. When I then type =C3+C4 I get the maturity date in cell C5.

5. Percent Number Format

In this section I will talk about Percent Number Format, Percent and Percent Change.

One of the biggest mistakes made in Excel with the Percent Button: Figure 59. Screenshot of Microsoft Excel showing the Percent Button.

If I then type 0.02 in cell B2 and I click the Percent Button above I get the following: Figure 60. Screenshot of Microsoft Excel showing how the Percent Button works.

The 0.02 is then transformed into 2%. On the other hand if I click the Percent Button with the value 2 highlighted in cell C2, I get the following: Figure 61. Screenshot of Microsoft Excel showing the percentage of the number 2 in cell C2.

That is an example of Number First, Then Format. The other way to do it is by first formatting and then typing. Here I highlight the cells B3 and C3 before I click the Percent Button: Figure 62. Screenshot of Excel showing how to first highlight cells B3 and C3 and then click the Percent Button and increase the decimals.

If I then type the number 3 in cell B3 I get the result that I want (which in my case is 3%). Figure 63. Screenshot of Excel showing what happens i preformatted cells.

But when I type 0.03 in cell C3 I also get 3.00% so it doesn’t matter if you use 3 or 0.03: Figure 64. Screenshot of Excel showing its weird behavior when typing in pre-formatted cells.

I don’t know why this has been programmed this way, but it’s the way Microsoft Excel works.

Sometimes you have just one interest rate and then you can just type for example 3% in cell B4: Figur 65. Screenshot of Excel showing an example of Format As You Type.

You will then get the result 3.00% when you hit enter.

Then I am going to talk about Percent and Percent Change. A Percentage is calculated as a Part of the Total. In Figure 66 I am comparing the Cost Of Goods Sold to the Total Revenue: Figure 66. Screenshot of Excel showing calculations of a percentage.

The result in cell B9 is of course the following: Figur 67. Screenshot of Microsoft Excel showing the resulting percentage of B8 divided by B7.

Then we have the concept of Percent Change. This is calculated as (End – Beginning)/Beginning: Figure 68. Screenshot of Microsoft Excel showing how to calculate percentage change.

The result is of course as follows: Figure 69. Screenshot of Excel showing the result of the percent change in cell B14.

### Conclusion:

Microsoft Excel is a very powerful tool for doing calculations and data analysis. It is very often used in finance and we will dive deeper into the topic in later articles.

Today I have only gone through the basics of Microsoft Excel to whet your appetite.

I hope you come back for more.