July 5, 2018

I was watching my friend tabulating up monthly expenses on an old fashion paper notepad and pencil and calculator and it made me realize that there should be a simple way to educate people about the benefits of using Excel Spreadsheets. Today I will start off with some basics of using Excel so that you can use it to the maximum advantage and save time for future use. I intend to possibly put some educational videos to make this a recurring episodic series because “if a picture is worth a thousand words then a video is probably worth a million words”. You heard it here, I’m coining that phrase.

Basics: row & columns, cell location, & basic formula entry

Anyways first we will start off with some basics. Excel starts off with a page that’s called a Sheet that is short for Worksheet. In it contains squares called cells. And cells that a next to each other from left to right or horizontally are called a row. Cells that are on top of each other from top to bottom, that is vertically, are called columns.

[Example A. The above shows rows span horizontally. In this case there are 7 rows.]

In this above example. You should see red arrows that identify Row 1. The row starts at the first cell as the leftmost cell and goes on almost infinitely to the right.

Here are what columns would look like:

[Example B. This above example shows that columns are vertical. In this case there are 8 columns. These are labelled A, B, C, D, E, F, G, H.]

As you can probably see in the above examples that their label headings that make it easier to identify what row you are talking about. So, in the second example, Example B, you can see that that we are emphasizing Column C which is the third column on the Sheet. The columns start at the first cell at the top and goes almost infinitely in a downward direction.

Now if you want to identify a particular cell you would use where the two labels intersect to identify what cell you are in sort of like coordinates when playing Battleship and chess. When you want to identify what cell you are in, you always start off with the Letter first in the columns and then the row. So, in Example B above the cell with the word “Columns” is highlighted with a border in green and that one is cell C8.

The following picture should show you how to identify what are the names of cells. Those are just a few examples.

[Example C. Notice that you always name your cells beginning with a letter of the alphabet from the column label and then the number of the row. Can you guess what cell is highlighted with a border in green? In this case it is 7 cells to the right in column G and 11 rows down in column 11 so you would call it cell G11. This also appears in the upper left-hand box which tells you what cell you are in at all times.]

Now this lesson will not teach you about the typical menu options like how to increase your font size and make your text bold. We will assume you know how to navigate and do all the formatting and styles. However, we will try to show you how to do basic formulas and possibly conditional formatting.

What I will do is show you how to easily use a computer to add up totals in your spreadsheet. The first thing is to show you an example.

[Example D. This is what we will use to show how to create basic formulas.]

Now in this example, we have numbers in cells B4, C4, D4, E4, F4 and G4.

We are going to make a simple formula and there are a few ways to do this. What we can do is use the “fx” to do a function or we can type in a formula by hand. Let’s show you both ways.

The first way I learned is to do it by hand. And that would mean writing each and every cell you want to include. What you have do first is make sure you are in the cell that you want the end result to appear in. In the above Example D, we want the Total Rent to add up all the numbers of rent paid from January to June. So, the formula could look something like this B4+C4+D4+E4+F4+G4. However, in order for Excel to recognize this as a formula you first have to always put an equals sign in there such as “=” so it doesn’t get confused with just regular text you might put in a cell. For example, I might put a word like “Vehicle Insurance” or “A1 Insurance” to make the spreadsheet more readable to a human being. You don’t have to put text labels because you could have just put in numbers but it helps you see what you are adding or subtracting etc.  Normally text labels don’t have an equals sign as the first character which is why the program Excel also used the equal sign to let the software realize a formula is about to be entered.

So now that we have that formula we can enter it in the cell and it will also appear in the formula bar above. The formula will look like this:

=B4+C4+D4+E4+F4+G4

Here is a screenshot of what it would look like.

[Example E. You can see in the formula bar that “=B4+C4+D4+E4+F4+G4” is entered at the top and that I4 has automatically changed to the total of this formula value with the result of 3000. It also added a small pop up that shows a paste icon and “(Ctrl)”. You can ignore that for now. The main thing is that this is a powerful way to create worksheets that do all your arithmetic for you.]

Now rather that typing in each cell you could just as easily put “=” and click a cell, type the addition sign “+” and click another cell, type the addition sign “+” and repeat for each item.

Here is an example of what it might look like if you used your cursor to highlight each item and put a plus sign between each cell click.

[Example F. Notice that each of the cells in the formula that are used are highlighted. I also added annotation to help you locate the box where the formula bar is.]

This will show you what it might look like before you commit the formula to the cell. I find that it’s usually a good idea to hit “Enter” or “Tab” to commit it to the cell before you try to move around the cells or using arrow keys otherwise it may accidentally add a neighboring cell.

Now there is another way to add a formula and that is by using the function option.

First you want to start where you were before and delete or clear out the formula you created in cell I4. We’re going to use the fx button. See below:

[Example G. Notice that I used a red circle to highlight around the fx function to help you locate it and cleared I4’s value.]

Next you have to click it and choose SUM under the “Insert Function menu” which looks like the same in the photo.

[Example H. This is the Insert Function menu. It allows you to add a formula or apply it over a data-set. Sum is selected in the case already.]

The next screen comes up when you click OK giving you the option to select cells and ranges. It also gives you a chance to preview the result.

[Example I: Shown here are selecting ranges in Excel.]

Then you can go in there to add or highlight the cell range that you wanted in your final result. In this case we’re using B4 through G4 which shows up as B4:G4. Notice the colon in the middle. You will often see ranges written in this type of notation.

Mathematical symbols

Now some basic background about creating your own math formulas. Most people know adding is the plus sign (+), subtracting is a minus sign (-). They may not remember or know that asterisk (*) is multiplication while a forward slash is division (/).

Conditional Formatting

The great thing about Excel is a lot of the values and content will update automatically if you set up the formulas and spreadsheet correctly.

However sometimes you want to add some colors or fonts to make it so when you reach a value it triggers an alert of sorts or makes a particular value or values stand out. In the following picture you can see where to find a conditional formatting tab. Basically once a certain condition or value is reaches it will cause the cell to either highlight a certain color or font to make it easier to identify in the future.

[Example J. The Conditional Formatting tab is found under the Home ribbon location in Excel.]

As you can see there are a lot of options to choose from. In this next shot I am showing you that the Greater Than condition will cause cell I4 to highlight with a light red on dark red text once the total on I4 exceeds 3000.

[Example K. Notice how I4 is highlighted red when the totals on the left cause the value to go above the trigger value of 3000.]

Now if you entered an incorrect rule you can go into the rules management to delete the rule(s) and start over. See the next image.

[Example L. There are a few ways to get rid of rules that you created mistakenly. There’s a Clear Rules option and Manage Rules… where you can go in there and view and edit and delete any rules you created as well as add new ones.]

See how powerful Excel can be? Now the last thing to mention for this lesson is ways to reference cell locations in the next section.

Absolute and Relative cells

The cool thing about Excel is that if you create a formula in one cell you don’t have to copy and paste it into each cell or reenter the formula repeatedly. This saves a lot of time and work. You can type the formula and then drag the lower right corner of the cell over and it will adjust the formula based on which direction you’re dragging the cell. For example, if you are inside I4 or any other cell you will notice a small square handle that you can drag.

[Example M. This picture shows that the square allows you to select a range whereby you can duplicate a formula. It only lets you move vertically or horizontally like a Rook in chess, not diagonally.]

Let’s say you had a formula in a box I4 again and you want it to all the boxes it did in the formula from January to June. That is Column B through G and only in Row 4. But you want it to now add the items in the Cellphone row. If you select the cell handle and drag down it will duplicate the formula but adapt it to be the same columns but down a row. So let’s say the formula was =SUM(B4:G4) so what would happen is it would now read =SUM(B5:G5) instead. It increments the formula Row to Row 5. Remember the rows are labelled 1, 2, 3, 4 …. etc. whereas Columns are A, B, C, D …

If you had dragged the box to the right it would have changed the formula to =SUM(C4:H4) which in this case would have been a useless formula because column H4 is empty but there are certain situations where you have your spread sheet data oriented differently and might want to tabulate your numbers and need to duplicate to the right. You can experiment with that.

The ability of Excel to adapt the column and rows in the formula means that you are using relative values all this time. If for any reason you need to hard code what’s called an absolute cell reference then you would put a special character in front of the column or row identifier.

Here’s an example below.

[Example N. This uses an absolute cell reference to cell L10.]

Note in this reference we have filled the other fields. So basically, a person is figuring out his monthly bills for half of the year using expenses such as rent, cellphone, gas and electricity for the month. Column I shows totals of each expense type for six months, that is it uses a summation of January through June. The person setting up the worksheet just dragged or expanded the formula downward so that cellphone, gas and electricity all use the same formula but it’s just adapted to those rows. Now where we are using the absolute reference is in column J. First, we have a Max Budget that we have set for the month. In this example we don’t want to spend over $5000 let’s say for those six months. So, we’re trying to see what percentage those expenses are of the max budget. However, we don’t need to have $5000 in every single row to do the tabulation. We can set a reference to it since it’s not going to change by using $L$10. This tells Excel not to increment columns or rows. We could have just put L$10 also because since we’re copying only downward from cell J4 down the columns are not going to change to columns K, L, M, N… etc. In this case we used the dollar sign, $, to show that that column or row will not increment or change or adapt depending on which way you copy the formula. The dollar sign always goes in front of the label.

Also note that if you go farther right in the columns the columns start having a second letter to differentiate what column you are in. For example, you might have column AW or BC. In that case if it was row 10, you might see an absolute reference such $AW10 or AW$10 or $AW$10. For BC you might see $BC10 or BC$10 or $BC$10.

Here’s an example where you might see columns that are identified with two alphabetical characters. In that case you would put a dollar sign in front of the first letter to denote an absolute reference there.

[Example O. See how the column with two letters could cause an error in your formulas if you aren’t paying attention where you put your dollar sign $ for absolute reference.]

So, this basically gives you a quick and dirty idea of how powerful Excel can be but also how much flexibility and how you must be careful where you put your cursor or references to avoid confusion. I typically like to use Excel to keep track of finances, stocks, 401k allocations and budgeting. For more complex bookkeeping there are other software programs that are probably tailored and pre-programmed to do certain things fast, better or easier. Always use the tool to your advantage and not the other way around. Don’t let the tool use you and waste your time.

I hope this has been a helpful article. And please comment or make any suggestions or questions below.

Thanks for reading.