If you have been using Excel for some time, you have probably learned to appreciate a well-crafted formula. The first thing we teach in our Excel Module 1 workbooks is the importance of using cell references whenever possible in a formula.

For instance, suppose you are storing gross sales of $275,000 in cell A5. You know that expense is 38% of sales, and have stored this percent in cell A6. You wish to compute expense in cell A7. One plausible formula would be:

=275000*38%

You know, however, that a more flexible formula is:

=a5*a6

Why? Because if the gross sales figure or the expense percent change, the result in cell A7 will automatically adjust.

In fact, a well-designed spreadsheet should consist almost entirely of formulas, with just a few "raw" numbers representing variables such as predicted first-year sales or the amount you wish to borrow to purchase a home. (Of course, there are always exceptions to this principal; some spreadsheets are simply containers for numbers that cannot be computed).

Another advantage to using cell coordinates in formulas is that they are very easy to replicate (copy). For example, suppose you are preparing a 5-year revenue projection for the Acme Widget Company. You have computed gross sales, cost and margin for the first year, using the figures above (275,000 in gross sales and 38% for cost as a percent of sales). You further compute margin as sales minus cost:

Let us suppose that sales in Year 2 are expected to increase by 9%. What formula can you use to compute Year 2 sales? Consider that sales in Year 2 are 9% greater than Year 1 sales. This means Year 2 sales are 100% of Year 1 sales, plus another 9%:

100%+9%=109%

You can use this as your multiplier:

=109%*b4

Or, you can use the decimal equivalent:

=1.09*b4

If you complete the formula in C4, then calculate cost and margin in C5 and C6, the worksheet will look like this:

Because the values in C4, C5 and C6 are based on formulas that contain cell references, you can copy them into the range D4:F6. Thanks to relative cell referencing (also explained in Excel Module 1), Excel will automatically adjust each copy of the formula to correctly compute sales, cost and margin for years 3 - 5.

This is where AutoFill can be used.

1. Highlight the range C4:C6
2. Move the mouse pointer onto the tiny box in the lower right corner of the highlighted range:

3. While holding down the mouse button, drag to the right to column F:

4. Release the mouse button

When you do, Excel should automatically fill in the blank cells with the correct results:

This function, known as AutoFill, can be used to copy the contents of any cell into a range of contiguous cells. If you do not see the tiny box in the lower right corner of the current cell (or range), it means this function has been disabled. To activate it:

1. Click on Tools on the menu bar
2. Click on Options...
3. Click on the Edit tab
4. Click in the Allow cell drag and drop checkbox
5. Click on OK

AutoFill is covered in detail in Excel 2000 Module 1 and Excel 97 Module 1.

Because dates are an important type of spreadsheet data, Excel is not only able to recognize date entries, it can also perform calculations based on dates. In fact, Excel provides a series of date functions that can be used to automate many date operations.

The Gregorian calendar is not the most precise measure of time ever devised by man. Because of this, spreadsheets employ a brutally simple and efficient way to perform date math: They simply store, in memory, an invisible table of dates, with arbitrary starting and ending points, and a corresponding serial number for each.

The most common technique is known as the 1900 date system. Excel uses January 1, 1900 as the starting date in the series, and December 31, 9999 as the ending date. January 1 is assigned serial number 1, January 2 is assigned 2, and so forth. This system was originally developed by Lotus Corporation for use in Lotus 1-2-3, an early spreadsheet program.

Excel also recognizes when you type a date in a cell, assuming you adhere to some fairly liberal restrictions. For example, you can enter a date using one of the following formats:

6/18/02
6-18-02
jun/18/02
jun-18-02

If you don't include the year in the date entry, Excel will assume the current year. Let's try some activities.

1. If necessary, run Excel and make sure you are looking at a blank worksheet
2. In cell A1 type:

9/23

3. Press Enter

In cell A1 you will probably see:

23-Sep

On the Edit line you will see the date, probably displayed like this:

9/23/2002

The year will represent the current year.

4. In cell A2 type:

=today()

(Be sure to include the pair of parentheses at the end).

5. Press Enter

This time Excel should display today's date. It may be displayed in a different format than the date in A1.

This is an example of a date function. Excel includes many such functions for performing date math. Unlike functions such as SUM() and AVERAGE(), the TODAY() function contains no arguments. Thus, the parentheses are empty. However, they are still required.

6. In cell A3 type:

=now()

7. Press Enter

Excel should again display today's date and the current time, probably in a 24-hour format. The NOW() function is similar to TODAY(), but adds the precision of the current time.

8. In cell A4 type your birthdate, including the year
9. In cell A5 type:

=a3-a4

10. Press Enter

Initially, the value in A5 will be displayed as a date. However, the serial number lurking behind this date represents the number of days you've been alive. Let's change the format.

11. Move back to A5
12. Click on the Comma Style button on the toolbar:

The number displayed represents the number of days you've been alive. The decimal portion represents the percent of the current day that has transpired. For example, if you do this exercise at noon, the percent will be .50, since half the day has already passed.

This type of date math is very simple for Excel, because it stores all the possible dates (within a more than reasonable range) as serial numbers. Hence, subtracting one date from another automatically calculates the difference in days.

For example, suppose you need to what the date was 135 days ago. There are several solutions.

13. Move to A6
14. Type:

=today()-135

15. Press Enter

Excel will probably display the answer in a date format. This is an example of a situation in which Excel "guesses" at the how you want the result displayed. Because you included a date function as part of the formula, it surmised that the result should also be displayed as a date. As we saw earlier, however, Excel sometimes guesses wrong, requiring that you change the format manually.

This same concept can be used to calculate dates in the future. Just add the number of days instead of subtracting.

Using an IF() function, you can create a spreadsheet that automatically calculates a late charge when an invoice due date expires. Date math is covered in detail in Excel 2000 Module 2 and Excel 97 Module 2.

Suppose you wanted to borrow money to buy a new car. You know how much you are borrowing, what interest rate you are paying, and the length of the loan. You wonder how much your monthly payments will be.

This can be calculated very easily using the PMT() function. It requires 3 arguments:

• The interest rate, expressed as a monthly percent (percents are usually annual)
• The number of payments you will be making
• The amount you are borrowing

The syntax for the PMT() function is:

=pmt(monthly interest rate,# of payments,principal)

Like all functions, commas are used to separate the arguments.

Let's try a simple example. We'll suppose you wish to borrow $20,000 at 9.5% over 4 years.

1. Type this information into a blank worksheet:

Because the 9.5% is an annual interest rate, we need to convert it to a monthly rate. To do so, simply divide the annual rate by 12.

2. In cell B4 type:

=pmt(b2/12,b3,b1)

3. Press Enter

Excel should display:

($502.46)

Note that the number may be displayed slightly differently, such as in black, or preceded by a minus sign to denote that it is a negative amount. Excel provides several different formatting options for negative numbers. The reason the answer is displayed as a negative is because Excel considers a payment to be an expense. It is the opposite of income, which of course is displayed as a positive value. If you prefer to display the number as a positive, simply insert a minus (-) in front of the principal amount or its cell reference:

=pmt(b2/12,b3,-b1)

Now let's suppose you want to borrow 30,000 instead of 20,000. How will this affect your monthly payment?

4. Move to cell B1 and type 30,000
5. Press Enter

Cell B4 should now display:

($753.69)

Again, the format may differ somewhat from this. If you inserted a minus in front of b1 in the formula, you should see a positive value.

You can also play around with different interest rates or number of payments, and cell B4 will re-calculate. The PMT() function is described in detail in Excel 2000 Module 2 and Excel 97 Module 2.

Excel provides a neat little utility that allows you to adjust values in a formula to reach a particular goal. What does that mean? Perhaps an example would help.

Suppose you use the PMT() function to compute monthly payments on a loan (see above). We'll say that you are borrowing 30,000 at 9.5% over 4 years. Your monthly payment would compute to $753.69. Let us say that you can only afford $550.00 per month in payments. You know you have to borrow less, or borrow at a lower interest rate, or spread the payments over a longer period. But how do you calculate these adjustments?

Using Goal Seek makes such calculations easy.

1. If necessary, create the small spreadsheet above that calculates monthly payments when borrowing 30,000 at 9.5% annually over 4 years (48 payments).
2. Move to cell B4
3. Click on Tools on the menu bar
4. Click on Goal Seek...

You should see this dialog box:

5. In the To value box, type -550

Because the amount of our payment is expressed as a negative number, we need to express the desired outcome the same way. Hence the minus sign (-).

6. In the By changing cell box type b1

This tells Excel to adjust the amount being borrowed (cell B1) so that the monthly payment in B4 calculates to exactly -550.00.

7. Click on OK

You should see this dialog box:

Assuming that cell B1 is visible, you should see that the value has changed to 21,892.

If you wish to accept this change, you would click on the OK button. To try a different scenario, you can cancel.

8. Click on Cancel

The Goal Seek function is described in more detail in Excel 2000 Module 2 and Excel 97 Module 2.

A lot of spreadsheets require some sort of series of labels or values. For instance, if you are keeping track of daily gross receipts, you might need to create a series of dates, incremented daily. Excel makes it quite easy to create lots of series, especially when they involve numbers.

1. In an empty spreadsheet, in cell A1, type today's date
2. Press Enter
3. Return to cell A1
4. Move the mouse pointer onto the tiny black rectangle located in the lower right corner of the cell pointer:

When the cell pointer is on top of this tiny rectangle, it will turn into a black plus sign like this:

5. While holding down the mouse button, drag down to cell A10
6. Release the mouse button

Assuming you dragged the AutoFill rectangle, you should now see a series of 10 dates, starting with the current date and incremented by 1 day.

Note that this increment will not occur if you use either =today() or =now(). Doing so will simply repeat the current date in the 10 cells.

7. Move to cell B1
8. Type 1
9. Move to cell B2 and type 2
10. Highlight cells B1 and B2:

11. Again move the mouse pointer onto the tiny black rectangle
12. While holding down the mouse button, drag down to cell B10
13. Release the mouse button

This time Excel should fill the range with the digits 1 - 10, incremented by 1. Had you tried using AutoFill without include the 2 in cell B2, Excel would have repeated the digit 1 ten times. The two values provide a pattern that Excel uses to generate the rest of the numbers. To count by 2's or 5's or any other increment, simply type in the first two values and highlight them to provide a pattern. Using this technique, you can even count backwards.

14. Move to cell C1
15. Type January
16. Move back to C1
17. Drag the tiny black rectangle down to cell C12

Excel should fill the cells with the names of the months. If you prefer abbreviations, start with Jan instead.

Filling series is described in more detail in Excel 2000 Module 2 and Excel 97 Module 2.

Above you learned how to create a series using the AutoFill handle. If you wish, you can create your own custom list, then use AutoFill to quickly fill a range with the values. To create the custom list, you can either type the items in a series of cells, or enter them directly in the Custom Lists dialog box. To demonstrate, we'll enter them directly in the dialog box.

1. Click on Tools on the menu bar
2. Click on Options...
3. Click on the Custom Lists tab

You will see the current custom lists, which typically includes days of the week and names of the months.

4. Click on the Add button

The cursor should now be flashing inside the List entries box. To create a list, type each item, then press Enter.

To demonstrate, we'll create a list of cities.

5. Type these cities, pressing Enter after each:

Los Angeles
San Francisco
Denver
Minneapolis
Chicago
Atlanta
Boston
New York

6. Click on the Add button

The items should now appear below the other lists. Had you entered these cities in a column or row of cells, you could have highlighted them, then clicked on the Import button to create the same list.

7. Click on OK
8. In cell A1, type Los Angeles
9. Press Enter
10. Return to cell A1
11. Grab the AutoFill box and drag down to cell A8

The cities in your list should appear in A1:A8. Before continuing, you may wish to delete this custom list. To do so:

12. Return to the Custom Lists dialog box
13. Click on the new list
14. Click on the Delete button

You will see this dialog box:

15. Click on OK
16. Click on the dialog box OK button

The default lists, by the way, cannot be deleted.

Suppose a cell in your spreadsheet contains a formula that is vital for calculating results, yet you do not want the contents of the cell to be displayed. You cannot delete the cell contents without eliminating the formula. Instead, Excel allows you to hide the cell contents. Note that this is not the same as hiding an entire column or row.

1. Select the cell whose contents you wish to hide
2. Press Ctrl 1 to invoke the Format Cells dialog box
3. Click on Custom in the Category list
4. Highlight the current entry in the Type list box
5. Press the Delete key to delete it
6. Type ;;; (e.g., 3 semicolons)
7. Press Enter

The value in the selected cell should disappear. To make it re-appear, change the cell format to anything else. To hide the contents of other cells, use the Format Painter.

When building a spreadsheet in Excel, we tend to take text entry for granted. Most of the time we just type in labels to identify ranges of cells.

However, Excel is quite flexible in terms of how cell text can be formatted. In addition to changing the font face, size, color and attributes (bold, italics, underline) of the entire entry, you can apply different formats to, literally, every character.

For example, suppose your company receives monthly revenue and expense reports from several branch offices. Although each worksheet is structured the same, the header also identifies the branch name:

The city name above is part of the same line as Accounts Payable. To change the color and add underline, drag your mouse through the appropriate portion of the text on the Formula Bar at the top of the screen:

Once highlighted, simply select the font attributes you want, and they will be applied only to the highlighted text. Note: When you drag past the list character on the Formula Bar, the highlighting will probably extend to the right end of the Formula Bar. This has no effect on the results.