M.L. Humphrey - Basic Excel Primer

The purpose of this pamphlet is to discuss how to use Excel for basic budgeting calculations. If you're a little rusty on basic math (addition, subtraction, multiplication, and division) then read Juggling Your Finances: Basic Math Primer first.

This pamphlet will walk through the same calculations as that primer, but talk about how to use Excel to make the calculations as opposed to a calculator.

If you're familiar with Excel or don't plan on using it for budgeting, then you don't need to read this primer.

The primer will walk through how to do the following calculations in Excel.

• Total income.
• Total expenses.
• Total balance in your accounts if you have multiple accounts.
• Total amount you owe if you have more than one credit card, loan, etc.

• How much money will be left over after you pay your bills.
• Your net worth or liquid net worth. (In other words, how much you'd be worth if you sold everything you own and paid off everything you owe. The first one, net worth, is how much you're worth in theory, the second one, liquid net worth, is how much you're worth in reality.)
• How much of a shortfall you might have if you're spending more than you earn.

• How much you'll earn when you know the rate you'll be paid per hour or task.
• Your net earnings (how much you actually take home) from your gross earnings (how much you earn before taxes are taken out).
• Your annual earnings or expenses based upon one month's earnings or expenses.

• How much you earn or spend on average.
• How many hours you need to work to earn a certain gross amount.
• How much you need to gross in order to take home a certain net amount.
• How many months of expenses you can cover with the current amount of cash you have in the bank.

In order to make it as easy as possible to learn the basics of using Excel for addition, subtraction, multiplication, and division, the main chapters focus only on how to do those mathematical computations. However, if you work in Excel to any great extent you will quickly find that you need to perform a number of other tasks such as widening columns, bolding text, and formatting text.

The Tips and Tricks chapter at the end covers these other functions in Excel. You'll likely refer to it often as you work through this pamphlet and when I use a trick that's covered in that section I'll let you know.

This will probably seem pretty daunting at first, but just keep working with it and practicing and you'll quickly be able to perform any computation you want in Excel with ease.

As I mentioned in the Basic Math Primer, it's a really, really good idea to be comfortable enough with math that you can at least judge whether an answer makes any sense or not. A spreadsheet is only as good as the person creating and using it. If you put in the wrong information or set up a formula wrong, you will get a wrong answer.

Excel is just a tool and you need to know enough to understand when the tool isn't working properly.

Okay, enough of that.

Ready?

Let's get started.

Before we start, I want to make sure we're using the same terms.

This is what Excel looks like when I first open it.

I'm working in Excel 2007, which is VERY different from earlier versions of Excel in terms of how it looks. Your basic math functions are pretty much the same, but the menus up top are completely different. If you're working in an earlier version and just now trying to learn Excel, I'd recommend that you upgrade your version of Excel. They're different enough that you really don't want to have to learn the newer version later.

But I'll try to keep this at a level that can work with any version of Excel, because your basic formulas really haven't changed across versions. Sometimes, though, that won't be possible. Especially when we talk about using the tabs at the top of the page to find the various options.

So, let's start by going over the terms I'll use throughout the rest of the pamphlet.

Excel uses columns and rows to display information. Columns run across the top of the worksheet and, unless you've done something funky with your settings, are identified using letters of the alphabet. As you can see below, they start with A on the far left side and march right on through the alphabet (A, B, C, D, E, etc.). If you scroll far enough to the right, you'll see that they continue on to a double alphabet (AA, AB, AC, etc.).

Rows run down the side of the worksheet and are numbered starting at 1 and up to a very high number that will not matter for what you're doing here.

A cell is a combination of a column and row that is identified by the letter of the column it's in and the number of the row it's in. When you've clicked on a specific cell it will have a darker black border around the edges. Directly above you can see that for Cell A1 the border only shows on the bottom and right side. (See next page for image pointing to Cell A1.)

If I tell you to click on something that means to use your mouse (or trackpad) to move the arrow on the screen over to a specific location and left-click or right-click on either the cell or tab or menu option. (See the next definition for the difference between left-click and right-click). If you left-click, this selects the item. A selected cell should then be surrounded with a dark black border. If you right-click, this generally creates a dropdown list of options to choose from. If I don't tell you which to do, left- or right-click, then left-click.