By Microsoft’s own admission, Excel gets more use as a simple list-management tool than as a real spreadsheet. Why is that? It’s as if millions of people bought four-wheel-drive SUVs just to drive to the supermarket – but maybe that’s a bad analogy.
We can’t help you justify your SUV, but if you’re one of the millions of Excel owners who have never entered a formula or generated a graph, here’s a chance to road test Excel 2001’s interface with a simple project. It’s actually a complement to a spreadsheet called Daily Food Diary, which Microsoft ships with Excel. The idea is that if you’re trying to lose a few pounds through a sensible diet, it helps to record your progress, keep yourself motivated, and make sure you aren’t losing weight too fast. This spreadsheet will let you do all of those things – and teach you some Excel skills you can apply to other tracking projects, financial or otherwise. You’ll see how using formulas is both more interactive and more fun than simply making a list. Of course, you have to do the weighing yourself – USB-equipped scales are still on the drawing boards.
1: Create a New Worksheet
Office 2001 ships with a Project Gallery that’s full of useful templates. We’re going to modify one called Daily Food Diary, an Excel template designed for keeping track of what you eat.
First, open the Project Gallery. Under the File menu, select Planners: Meals-Diets: Daily Food Diary. Insert a new worksheet into the file (Insert: Worksheet). By default, Excel places the worksheet’s tab before that of the Daily Food Diary and labels it Sheet 1. Double-click on the tab to rename the worksheet. You can change the order of the worksheet tabs by clicking and dragging to new positions. Save this new workbook (which is what Excel calls a file) under a new name with the File: Save As command.
For consistency of style, select, copy, and paste the main header of the Daily Food Diary worksheet into cell A1 of your new worksheet. Then change the wording by double-clicking on the cell and typing
Weekly Weight Tracker
(A) . In the same manner, enter the four column headings in their cells as shown (B) . Select and style them as bold.
2: Get Your Fill
The Fill commands(listed under Edit: Fill) are some of the most powerful time-savers in Excel. They automatically copy the contents of one cell to a selected group of adjacent cells, whether those contents are text, data, or a formula.
In the first column, you’ll use Fill: Series to set up a weekly schedule. Start by typing the first date you’ll weigh yourself under the Week heading, in cell A4 (A) .
Then, select cells A4 through A25 by drawing a marquee with the cursor (selected cells will be outlined). Choose the Fill: Series command from the Edit menu. Enter
(for days in a week) in the Step Value field (B) , and click on OK. Excel then automatically generates weekly tracking dates.
3: See You Later, Calculator
The Calculator, which is new to this version of Excel, makes creating even complicated formulas easy. But first we’ll try it out on something simpler.
For the Weight column, enter your starting weight in the first cell (B4) (A) . Then highlight the next cell down (B5) and click on the Calculator in the Formula tool bar. (If you don’t see the Formula tool bar, you can turn it on under the View menu.)
With the Calculator showing, click on cell B4 in the spreadsheet so that the formula displayed in the Calculator result window becomes
(B) . Click on OK to close the Calculator. Cell B5 should now have the same value as cell B4. Click on B5 and drag your cursor down to the bottom of B25 so that B5 through B25 are highlighted. Use the Edit: Fill: Down command to copy the formula in B5 to the rest of the cells.
4: Get in Condition
One big difference between a boring list and an interactive spreadsheet is the use of conditions. That’s where the smarts of this spreadsheet reside.
For the Loss column, click on cell C4 (A) and open the Calculator. Click on the If button (B) to enter a conditional formula, and fill out the windows to the right as shown. This will cause the difference to appear only if your weight goes down. Click on Insert and then on OK to insert the formula.
For the Comments column, you’ll use a text string in a conditional statement. Click on cell D4 (C) and fill out the Calculator window as shown after clicking on the If button.
Use Edit: Fill: Down again to copy both of the formulas in this step to the rest of their respective columns. Now, if you’re losing weight too quickly, the Comments column will remind you to slow down.
5: Seeing Is Believing
Tracking your weight as it goes down (or up) will be a lot easier if you can see it graphically, and Excel is a great graphing tool.
To create a simple graph that follows your weight over the time period covered in the Weekly Weight Tracker spreadsheet, click on cell A3 and drag your cursor to cell B25 to select the data in rows A and B. Then select Chart from the Insert menu. When the Chart Wizard dialog box appears, select Line for both the chart type (A) and subtype (B) .
Click on Finish to automatically create the chart. You can make it bigger by dragging the lower right-hand corner (C) .