First thing’s first – let’s enter the data. Please open a new Excel workbook and enter the data as shown so that we’re all looking at the same thing. To put information in a cell, simply click on it and begin typing. As with MS Word, you can also select formatting options for what you type (try making your title bold or italic – to change the formatting in any cell, you just need to select it [by left clicking on it] and then select the formatting option just as you would in Microsoft Word). You may also need or want to resize some of the columns.

Now we have a basic budget worksheet, though you’ll notice that all the “totals” are blank at the moment. This is where MS Excel becomes much more useful than simply providing a format for a spreadsheet: we’re going to enter a mathematical formula in these cells that will calculate that total for us. Before we do this in our worksheet though, I want to go over some formula basics.

Formulas

Formulas are simply mathematical equations. 2+2=4 is a formula. In Excel, you can make one cell do a mathematical formula involving the contents of other cells (don’t worry, this will become more clear in a few minutes). Before we start putting in formulas though, it’s important to understand what symbols are used to represent various mathematical functions, and the order precedence (which dictates in what order the various computations will be carried out).

Mathematical Operators

+ Addition
– Subtraction
* Multiplication
/ Division
% Percentage

^ Exponentiation

= Equal to
< Less than
<= Less than or equal to
> Greater than

>= Greater than or equal to
<> Not equal to

Order Precedence : Excel performs operations left to right, and in the following order:

1st Operations within Parentheses
2nd Exponential operations
3rd Multiplication and division
4th Addition and Subtraction

Example: Compare the answers of the following formulas according to the order precedence:

•  (5 + 2) * 10 = 70

•  5 + 2 * 10 = 25

Now, to bring this abstraction back to reality, I give you the formula bar:

As you can see from the tool tip, the formula bar (long blank field preceded by an “=”sign) is where you’ll actually be entering formulas. In essence, every cell has its own formula bar – so whatever cell you currently have selected (in this case, A1) corresponds to that formula bar.

The biggest thing you have to remember when creating formulas in the formula bar is that you must always start the formula with an “=” sign or it won’t be recognized as a formula at all. Behold the evidence:

Without the equals sign, it just thinks you want to type 2+2 into the cell. You must always put an equals sign before the formula so that Excel recognizes what you are doing.

So, going back to our budget, let’s go ahead and calculate the total for January using a formula. There are a few ways to do this, but we’ll start with the basic one:

Click on the cell you’d like to put the formula in (B10, in this case) and then type “= B5+B6+B7+B8″ In effect, we are telling Excel that the cell B10 should be equal to the sum of cells B5 – B8. (Instead of typing them in, you can also -after you put in the equals sign!- just click on the cells and it will type them automatically).

An alternate method to typing in the formula on your own is to use a predefined ‘function’ As soon as you type that equals sign and Excel is clued in to the fact that you’d like to create a formula, the field which displays what cell is currently selected will change. You can then click the dropdown menu and select a function from the list. In this case, we’d want to use SUM:

It will come up with a wizard that allows you to select which cells you’d like to add up. It will be in the format (First Cell) : (Last Cell) where the colon includes everything in-between.

These are just several different ways to accomplish the same result.

The Extender Tool

One very helpful tool is Excel’s context-sensitive extender tool. If you look up at that last picture where I have entered the SUM formula, you’ll notice that cell B10 is selected, and that in the lower right corner of the cell is a small square that stands out a little from the rest of the outline.

That is the “grab point” for the extender tool, meaning you can grab it by left clicking the mouse and holding the button down, and then extend whatever is in the cell. I’ll show you a few examples of what I mean, but the extender tool is useful for any kind of linear information (such as on a list or a budget sheet).

One example of the extender tool is months:

Notice how it displays a sort of dotted rectangle outlining how far I’ve extended my data, and what the last cell is going to display (May). As soon as I let go of the mouse, it will display all values between the beginning cell and ending cell:

Also note that you can use this for abbreviations (Jan, Feb, Mar, etc.)

The great thing is that you can also extend formulas! Take the formula we just entered to calculate the total for January, for example. If I grab the extender tool and extend that all the way across to June, Excel is smart enough to recognize that I don’t want an exact copy of the formula, but one that is the same only with different cells used in the calculation (=SUM(C5:C8) instead of =SUM(B5:B8), for example).

You’ll notice if you click along those cells, that it has modified the formula for each column.

You’ll have to spend some time playing with the extender tool to see what kinds of things it will recognize. If it doesn’t recognize what you’re trying to extend as a linear list, then it will simply copy whatever you have in that cell into each one to which you extend it.

* Tip * sometimes it will help to recognize a list when you type in the first 2 or 3 items and then extend it. For example, if I type in “1″ and try to extend it, I’ll just get 1s:

but if I type “1″ in the first cell and “2″ in the next, select both, and then extend it:

Cell Formatting

There are many, many different options for formatting your cells. To reach this menu, the easiest way is to right click the cell in question (or right click anywhere in the selection of multiple cells) and then select “format cells.”

As an example, let’s select all our cells containing numbers and change the formatting:

This will bring up the formatting menu:

You can see there are a number of tabs across the top, each one containing a different category of options. For now, we’ll just modify the type of number in the cells. Since we’re talking about money, I’ve selected the “Currency” option and set it to 2 decimal places (you can see a sample of what it will look like toward the top of the window). Once I’ve selected this, my worksheet will now display all those numbers as currency:

Alignment is also a useful section of formatting – it modifies where in the cell your entry will appear. You can alter the horizontal and vertical justification, as well as tilting the text to whatever angle you’d like. (With smaller cells like we have it makes little difference, but if you make a big title cell, for example, it can be very useful):

The Font section is also helpful and you’ll recognize it as much the same thing you’ve seen in MS Word or Outlook before. It can often help as well to use the “paint can” feature and the text color feature, respectively:

which will fill whatever cells are selected with the color of your choosing, or change the text to that color, respectively. Try it out.

Now, given what we’ve learned about Formulas and formatting, take some time to work some more on this dummy budget worksheet. Make formulas to calculate the totals in the “H” column and also to calculate the totals for Q1 and Q2. Get them to display in bold or italics, see if you can use the paint can feature to make it easy on the eyes (generally speaking, using the light colors -the very bottom row of color choices- is the best way to go. Dark colors tend to make it harder on the eyes rather than easier). In the end, your worksheet should look something like this:

Feel free to ask me any questions as you do this.

Printing

Before you print an Excel document, you’ll want to take a look at the Page Setup menu, which can be accessed by going to “File”:

The two most important tabs on this menu are “Page” and “Sheet.”

“Page” has settings for the orientation of the document (Portrait and Landscape). Also, it has a scaling option (Adjust to ___% of normal size) which you can on a spreadsheet that’s, say, one column too many to fit on one page. Just change it to 75% of normal size – this will make it smaller, but it will fit on the page.

“Sheet” allows you to specify whether or not to print the gridlines, and the order that the pages will print out, if there are more than one.

** At this point, we’ll spend whatever time is left going through some of the other menus and talking about some useful functions, especially cut/copy/paste, sorting, and some more formatting options.

Back to Top