O'Reilly Hacks
oreilly.comO'Reilly NetworkSafari BookshelfConferences Sign In/My Account | View Cart   
Book List Learning Lab PDFs O'Reilly Gear Newsletters Press Room Jobs  



HACK
#18
Identify Formulas with Conditional Formatting
Excel does not have a built-in function that identifies formulas. Once a formula is entered into a cell, you can tell whether the cell is a static value or a value derived from a formula only by clicking in each cell and looking in the Formula bar. This hack fills that gap with a custom function
[Discuss (0) | Link to this hack]

The VBA code in this custom function (also called a user-defined function) enables you to identify cells that contain formulas without having to click through 10,000 cells and examine each one.

To become a clever formula hunter, start by selecting Tools → Macro → Visual Basic Editor (Alt/Option-F11) and then select Insert → Module. Enter the following function:

Function IsFormula(Check_Cell As Range)
    IsFormula = Check_Cell.HasFormula
End Function

Close the window (press Alt/&command;-Q, or use the Close button in the window's titlebar). Now this function is available in any cell on any worksheet in this workbook when you enter the formula =IsFormula($A$1). You also can access the function by selecting Insert → Function, then selecting UserDefined from the Category option and choosing IsFormula from the functions displayed.

The formula returns TRUE if the reference cell houses a formula and FALSE if it does not. You can use this Boolean result in conjunction with conditional formatting so that all formulas are highlighted automatically in a format of your choice.

One of the best things about using this method is that your spreadsheet's formula identification capabilities will be dynamic. This means that if you add or remove a formula, your formatting will change accordingly. Here we explain how to do this.

Select a range of cells on your spreadsheet—say, A1:J500—and incorporate some extra cells in case more formulas are added at a later stage.

WARNING

Avoid the temptation of selecting an entire worksheet, as this can add unnecessary overhead to your spreadsheet.

With these cells selected, and with A1 the active cell of the selection, select Format → Conditional Formatting.... Under Cell Value Is, select Formula Is and enter =IsFormula(A1) in the Formula box. Click the Format button and choose any formatting you want to use to identify formula cells. Click OK, then OK again.

TIP

Sometimes, when entering formulas into conditional formatting, Excel will try to put quotation marks around the formulas after you click OK. This means Excel has recognized what you entered as text, not as a formula. If this happens to you, go back into the Conditional Formatting dialog, remove the quotation marks, and click OK.

At this point, the specified formula should be applied to all cells on your worksheet that contain formulas. If you delete or overtype a cell containing a formula, the conditional formatting will disappear. Similarly, if you enter a new formula into any cell within the range, it too will be highlighted.

This simple conditional formatting hack can make your spreadsheets a lot easier to deal with when it comes time to maintain or modify them.


O'Reilly Home | Privacy Policy

© 2007 O'Reilly Media, Inc.
Website: | Customer Service: | Book issues:

All trademarks and registered trademarks appearing on oreilly.com are the property of their respective owners.