Excel 2010: Using Named Ranges and the Name Manager

For larger spreadsheets, Range Names are more convenient than scroll bars.  To name an individual cell or block of cells highlight the cell or cells first, click on the Name Box, type in an appropriate name, and press the Enter key. (Range names do not accept spaces.)

Range Name list and selected areaTo find the block again, use the drop-down list in the Name Box and click on the name. Alternatively use the GoTo command (Ctrl+G or F5). Note: Range names can also be used in formulae instead of their cell reference equivalent.

GoTo windowTo edit or delete range names, use the Name Manager from the Formulas tab. Note that the delete option only deletes the range name, and not the contents of the cells in that range.

Excel 2010: Auditing Tools

There are times when you inherit a spreadsheet and wonder how it works. You can identify the cells with formulae, but still have difficulty tracing the sources or subsequent calculations. This is where Excel’s Auditing Tools can help point you in the right direction (literally).

Tracing Precedents and Dependents

Select a cell with a formula. To find the cells that feed into your chosen calculation, click on the Formulas tab and choose Trace Precedents. The blue arrows or marked areas indicate which cells are used in the calculation of this formula. Choose Trace Dependents to indicate which cells use this cell in further calculations.

Blue arrows on a spreadsheetIt is worth knowing that these are multiple-level tools so you can take the calculations to the next stage and beyond. For example, if the original data cell is used in a calculation, Trace Dependents will point the cell out. If this is used in a further calculation, click Trace Dependents again (you don’t need to move from that cell) to see the next calculation, and so on. Just keep clicking until you reach the end of the route.

For 3-D spreadsheets, a sheet icon will appear if the precedents are coming from a number of other sheets. Double click the arrow to open the sheet list where you can select a particular location to view further (double-click).

3D spreadsheets list