Los Angeles Tech Writer

If you have switched from Microsoft Office 2003 to Office 2007, you know how much the user interface has changed (er...completely). I know I've spent many hours hunting down particular functions that I know are there but haven't been able to intuitively find.

Be that as it may, here are a couple of different ways to view the formulas contributing to a cell's value.

1. Change cell display to show formulas

This can be handy if you want to look at all the cell formulas instead of their values. Use CTRL + ` (the tilde/accent key, usually at the top left of the keyboard).

The result is this:


Click for larger image.

To change back to the cell values, use CTRL + ` again.

2. View contributions to cell formulas

If you want a visual representation of the cells contributing to a cell formula, you can of course click on the cell and view the formula in the Formula bar above the spreadsheet. But you can also use the Trace Precedents function via Formula Auditing.

To do this, click on the Formulas tab to display Formula Auditing. Highlight the desired cell, then click Trace Precedents. A colored arrow shows the contributing cells:


Click for larger image.

To clear the arrow, click Remove Arrows.

Additional Tip(because it's Saturday):

If you're wondering what the "NETWORKDAYS" formula is in the example 1. screenshot above, it is pretty nifty. It is the number of business work days between a start date and an end date, excluding legal and company holidays (if you define them). To set this up:

1. Create a cell with a start date and another cell with an end date.

2. Somewhere in your workbook, or in a linked worksheet, enter a list of holidays in date format. All dates should be formatted as dates, not as text.

3. Select the cell for the net work days total, and on the formula picker, select NETWORKDAYS. Using the pop-up calculator, select the start date, end date and cells containing the holiday dates.

Excel calculates the number of business workdays minus the holidays, as in the screenshot below:


Click for larger image.

Images created with TechSmith SnagIt 8.