• PC World
• »Software
• »Office

6. Zap the Numbers, Keep the Formulas

One common type of spreadsheet is a fill-in-the-blanks template. Typically, such a sheet will have a number of input cells, and a number of formula cells. You enter your data in the input cells, and the formula cells kick into action and use those values to display some type of (usually) useful result.

For example, you may have a worksheet set up to calculate a loan amortization table. Input cells would include items such as loan amount, interest rate, loan term, and so on. Formulas in other cells use this information to display the calculated results.

At some point, you may want to clear the slate and delete all the values in the input cells. Rather than scroll around and look for the non-formula cells, you can take advantage of an often-overlooked Excel feature that lets you select cells in a "special" way:

1. Select Edit, Go To (or press Ctrl-G or F5) to bring up the Go To dialog box.
   
   
2. Click the Special button to show the Go To Special dialog box.
   
   
3. Choose the Constants option, then clear all of the check boxes except Numbers (if some of the input cells in the spreadsheet accept text, leave the check mark next to Text).
   
   
4. Click OK, and Excel will select all of the non-formula cells that contain a value.
   
   
5. Press the Delete key, and those numbers are history--but the formulas remain intact.
   
   

7. Protect and Hide Your Formulas

When a cell contains a formula, the formula is visible for all to see. Just activate the cell and glance up at the Formula bar. In some cases, you many want to hide your formulas to give your worksheet a cleaner look--or to keep others from seeing how your calculations are done.

You can hide the formulas by setting the Hide option for the formula cells and protecting the sheet. But before you protect the sheet, you need to "unlock" all non-formula cells (by default, all cells are locked).

Here's how to hide the formula cells:

1. Select Edit, Go To (or press Ctrl-G or F5) to bring up the Go To dialog box.
   
   
2. Click the Special button to show the Go To Special dialog box.
   
   
3. Choose the Formulas option, and make sure the four check boxes are all checked.
   
   
4. Click OK, and Excel selects all cells that contain a formula.
   
   
5. Choose Format, Cells, and click the Protection tab.
   
   
6. Place a check mark next to the Hidden option, and make sure that there is a check mark next to the Locked option. Click OK.
   
   

Here's how to unlock the non-formula cells:

1. Repeat Steps 1 and 2 above to show the Go To Special dialog box.
   
   
2. Choose the Constants option, and make sure the four check boxes are all checked.
   
   
3. Click OK, and Excel selects all non-empty cells that don't contain a formula.
   
   
4. Choose Format, Cells, and click the Protection tab.
   
   
5. Remove the check mark next to the Locked option. Click OK.
   
   

At this point, the formula cells are set to Hide, and the non-formula cells are set to Unlock. But these settings have no effect unless the sheet is protected. Choose Tools, Protection, Protect Sheet to protect the worksheet. You can enter a password if you like.

After performing these steps, you can select any formula cell, and the formula will not be displayed in the Formula bar. Furthermore, the formula cannot be changed. But the cells that contain other information can be changed.

It's important to understand that protecting a worksheet--even if you use a password--is a very weak form of security. Password-cracking utilities are widespread on the Internet.

8. Spot Duplicate Entries

This tip describes a way to use the Conditional Formatting feature to quickly identify dupes in a range.

Say that you've been saddled with the job of organizing the seating list for the company dinner party. You have the list of assignments in range A1:B18. Obviously, the same person should not be assigned to more than one table. Here's a quick way to determine if the range contains duplicate names.

1. Select the range (in this case, A1:B18).
   
   
2. Choose Format, Conditional Formatting to display the Conditional Formatting dialog box.
   
   
3. Select Formula Is from the first drop-down list box, and enter =COUNTIF($A$1:$B$18,A1)>1 in the second box.
   
   
4. Click the Format button to bring up the Format Cells dialog box.
   
   
5. Select the Patterns tab, and specify a background color.
   
   
6. Click OK twice to return to your worksheet.
   
   
7. If the range contains any duplicate entries, they will be flagged with the background color you chose in Step 5.
   
   

You can adapt this technique to any other worksheet by changing the ranges used in the COUNTIF function. The first argument should be the full address of the range you selected--and make sure that you use the dollar signs to signify an absolute reference. The second argument should be the address of the upper left cell of your range, expressed as a relative reference (no dollar signs).

9. Skip From Cell to Cell

You may have a spreadsheet set up with a number of data input cells. Wouldn't it be nice if the user could just press the Tab key to jump to the next input cell?

This type of thing is fairly easy to set up. The key is to unlock the input cells, and then protect the worksheet. Here's how to do it:

1. Select all of the input cells on your worksheet. (To select non-adjacent cells, hold down Ctrl while you select the cells.)
   
   
2. Select Format Cells to display the Format Cells dialog box.
   
   
3. Click the Protection tab and remove the check mark from the Locked check box.
   
   
4. Click OK.
   
   

Keep in mind that all cells are locked by default. But also remember that locking or unlocking cells has no effect unless the worksheet is protected. To protect the worksheet, select Tools, Protection, Protect Sheet. If you're using Excel 2002, you'll be able to specify some additional options. Specifically, you can remove the check mark from the Select locked cells option.

When the sheet is protected, you'll find pressing Tab moves the heavy-bordered active cell indicator to the next unlocked cell. Be aware that this does not prevent the user from selecting unlocked cells using the cursor keys--unless you're using Excel 2002 and you removed the check mark from 'Select locked cells' as described above.

10. Hide Those %&@# Formula Errors

If you've dabbled with formulas, you've probably encountered the dreaded formula error. Rather than return a numeric result, the formula cell displays a weird message such as #VALUE! or #DIV/0!.

Most of the time, this means you need to track down the source of the error and fix it. But sometimes a formula error simply means that the data used by the formula is not yet available. For example, say you run a small telemarketing company. You might have a spreadsheet set up to track your daily sales as a percentage of calls made.

The formulas in column D do the calculations that come up with the percentages. For example, cell D4 contains the formula =C4/B4. The formula in D4 was simply copied down the column to handle the other days.

The formula does its job well--as long as there is data to calculate. An empty cell (such as B9) is treated as a zero, and division by zero is not allowed on this planet. As a result, Excel displays an ugly #DIV/0! error message, which makes your entire worksheet look like it was created by a novice.

You can avoid displaying formula errors by re-writing your formula to use an IF and an ISERROR function. For example, =IF(ISERROR(C4/B4),"", C4/B4) displays a blank if the division operation results in an error (cell B4 is empty or contains 0), yet still displays valid results.

Although this formula looks complex, when you break it down, it's not that daunting. In plain English: If you get an error performing the formula, then display an empty string (that is, nothing); otherwise, display the result of the formula.

It's actually easy to adapt this technique to any formula you might have. The original formula serves as the argument for the ISERROR function, and it repeats as the last argument of the IF function, like this: =IF(ISERROR(OriginalFormula),"",OriginalFormula)

If you prefer, you can replace the empty string ("") with other text of your choice--just make sure the text is enclosed in quote marks.

By the way, if you really own a small telemarketing company, please put me on your "do not call" list. Thank you.