“Of Mice and Megabytes Supplement”

EXCEL BASICS
Previous || Next

Let’s tackle some spreadsheet basics. Spreadsheets, especially a blank one, can be intimidating by their very nature. Intuitive tools are lacking, but more importantly the WHY of using them isn’t readily apparent. What follows will provide a glimpse of its power and convenience.

I’ll be using Microsoft Excel 2000. To find out your installed version, click on Help, About Microsoft Excel from within Excel. My instructions should be generic enough to cover the various versions.

For this example, let’s put together a simple form containing one item, its price and quantity, with sales tax, and the total.

Start Excel. You should be presented with a blank worksheet. If not, click File, New. Navigating around a worksheet is fairly straightforward. Note the lettered columns and the numbered rows. Cells are referenced as coordinates, such as “cell D3.” In such a case, your eye would find then travel down column D, and across from row 3. Cell D3 is the intersection of the two. It’s just like using a Thomas Brothers brand map.

Create column titles: Highlight cell A1. Type the word ITEM. Move the highlight to B1. Type PRICE. For cell C1, type QTY. In cell D1, type SUB.

Enter these values: Place the cursor in cell A2; type Widget. In B2, under PRICE, enter 1.23. In C2, type 2. In D2, type the following: =b2*c2. Press Enter. (That’s the equals sign, followed by b2, an asterisk, then c2. The formula is case insensitive.) The equals sign is important. It signifies that the data that follows is a formula, and this formula translates as follows: Take the contents of cell B2, multiply (that’s the asterisk symbol) it by the contents of C2, and then display the results. The D2 cell’s formula should display 2.46.

Now, add the sales tax info: In C3, type Sales tax. In D3, type =d2*.0825. The resulting sales tax of 0.20295, now displayed in D3, needs to be rounded off. There are several ways to do this, but let’s format the entire column. Click once on the letter D located within the column heading. The entire column will become highlighted. Then click Format, Cells. Click on the tab titled Number, then the option Number. On the right, make sure the option is set for two decimal places. Click OK. Now the sales tax amount should read 0.20.

To finish this up, do the following: In C4, type TOTAL. In D4, type =D2+D3, then press Enter.

Your spreadsheet is complete. It probably seems like an awful lot of work for one lousy item. But you can easily demonstrate for yourself the spreadsheet’s power: Change the price (B2), quantity (C2) or sales tax rate (D3) and watch as the it automatically recalculates the total.

Save this worksheet because we’ll re-visit it in two weeks. Also remember these are basic instructions and there are other ways and shortcuts to accomplish the same thing.

Re-printed from the Rafu Shimpo, July 2002. Copyright©2002 Rafu Shimpo. All rights reserved.