Chapter 11: Data Analysis and Formula Troubleshooting

This chapter covers two groups of MO-211 skills. The first group analyzes data: Consolidate combines several ranges into one summary, and Goal Seek and Scenario Manager answer "what if?" questions. The second group troubleshoots formulas: tools that show how formulas connect, watch values change, and step through a calculation to find what went wrong.

11.1 Consolidate

Consolidate combines data from several ranges into one summary range. The ranges can be on different sheets or in different workbooks. A typical use is a workbook with one sheet per region or month, all laid out alike, and a summary sheet that totals them.

11.1.1 Two ways to match the data

• By position. Every source range has the same layout: the same items in the same rows and columns. Excel combines the first cell of each, the second cell of each, and so on.
• By category. The source ranges have labels, but the items may be in a different order or some may be missing. Excel matches rows and columns by their labels.

11.1.2 Consolidating data

1. On the summary sheet, click the cell where the result should start (the top-left cell).
2. Choose Data › Consolidate.
3. Choose the Function: Sum, Count, Average, Max, Min, Product and others.
4. Click in Reference, go to the first source sheet, select the range and click Add. Repeat for each source range. Each one appears under All references.
5. For consolidation by category, include the labels in each range and check Use labels in Top row, Left column or both. For consolidation by position, leave them unchecked and select only the numbers.
6. Optionally, check Create links to source data (see below).
7. Click OK.

11.1.3 Static or linked

Without Create links to source data, the result is plain numbers that don't change when the source data changes. To update them, open Data › Consolidate again (it remembers the references) and click OK.

With Create links to source data, Excel inserts hidden rows with formulas that link to each source cell, and outlines the result so each total can be expanded to show its parts. The result updates when the source changes. This option isn't available when the source and the summary are on the same worksheet.

11.2 Goal Seek

Goal Seek works backward. You know the result you want from a formula, and Goal Seek finds the input value that produces it.

For example, a loan payment formula in B5 uses the loan amount in B1, the rate in B2 and the term in B3. You can afford a payment of $450. What's the largest loan you can take?

1. Choose Data › What-If Analysis › Goal Seek.
2. Set cell: the formula cell whose result you want to set (B5).
3. To value: the result you want (-450, because PMT returns a negative payment).
4. By changing cell: the input cell Goal Seek may change (B1). It must hold a value, not a formula.
5. Click OK. Goal Seek tries values until the formula hits the target, then shows the Goal Seek Status.
6. Click OK to keep the new value in the changing cell, or Cancel to put the original back.

11.3 Scenario Manager

Goal Seek finds one input. Scenario Manager stores whole sets of input values, called scenarios, so you can switch between them and compare their results. For a budget, you might keep Best case, Expected and Worst case scenarios, each with its own sales growth, costs and staffing.

11.3.1 Adding scenarios

1. Choose Data › What-If Analysis › Scenario Manager, then click Add.
2. Type a Scenario name.
3. In Changing cells, select the input cells the scenario sets. Separate several ranges with commas, or select them with Ctrl. A scenario can change up to 32 cells.
4. Add an optional Comment. Under Protection, Prevent changes stops edits to the scenario when the sheet is protected.
5. Click OK. In Scenario Values, type a value for each changing cell and click OK.
6. Repeat for each scenario.

11.3.2 Showing, editing and deleting scenarios

In the Scenario Manager:

• Show puts the selected scenario's values into the changing cells, so the worksheet's formulas show its results.
• Edit changes a scenario's name, changing cells or values.
• Delete removes a scenario.
• Merge copies scenarios from another sheet or workbook.

11.3.3 Scenario summaries

Summary builds a report comparing every scenario:

1. Click Summary.
2. Choose Scenario summary (a formatted report) or Scenario PivotTable report.
3. In Result cells, select the formula cells to compare, such as net income and cash on hand.
4. Click OK.

Excel creates a new sheet, Scenario Summary, with the changing cells and result cells for the current values and for each scenario. The summary doesn't update if the scenarios change later. Make a new one instead.

11.4 Tracing precedents and dependents

The Formula Auditing group on the Formulas tab draws arrows between cells to show how formulas connect.

• A cell's precedents are the cells its formula uses. If C10 contains =C8*C9, then C8 and C9 are precedents of C10.
• A cell's dependents are the cells whose formulas use it. C10 is a dependent of C8 and C9.

11.4.1 Drawing arrows

Select a cell and choose:

• Trace Precedents to draw arrows from its precedents to the cell.
• Trace Dependents to draw arrows from the cell to its dependents.

Click the command again to go one more level back (precedents of the precedents) or forward. The arrows mean different things:

Arrow	Meaning
Blue arrow	A normal connection. A blue box around a range means the formula uses the whole range.
Red arrow	The connection leads to or from a cell with an error.
Black dashed arrow with a sheet icon	The connection goes to another worksheet or workbook. Double-click the arrow to see the list and go there.

Double-click any arrow to jump to the cell at its other end.

11.4.2 Removing arrows

Remove Arrows clears all arrows. Its arrow menu has Remove Precedent Arrows and Remove Dependent Arrows, which remove one level at a time. Arrows also disappear when you edit the worksheet or save the file.

11.5 The Watch Window

The Watch Window shows the values of chosen cells, wherever they are, while you work somewhere else. Use it to see how a change on one sheet affects a total on another, without switching back and forth.

1. Choose Formulas › Watch Window.
2. Click Add Watch, select the cell or cells to watch, and click Add.

Each watched cell gets a row showing its Book, Sheet, Name (if it has one), Cell, current Value and Formula. The window stays open as you move around the workbook, and you can dock it at the edge of the screen.

• Double-click a row to go to that cell.
• Select a row and click Delete Watch to remove it.

11.6 Error checking and Evaluate Formula

11.6.1 Error values

When a formula can't calculate, it shows an error value. Knowing what each one means is the first step to fixing it:

Error	Usual cause
#DIV/0!	Dividing by zero or by an empty cell
#N/A	A lookup function couldn't find the value
#NAME?	A misspelled function or range name, or text without quotation marks
#REF!	A reference to a cell that was deleted
#VALUE!	The wrong kind of data, such as text where a number is needed
#NUM!	An impossible number, or a calculation that can't find an answer
#NULL!	A space between two ranges that don't overlap, often a missing comma
#SPILL!	A dynamic array result is blocked by other data
#CALC!	A calculation problem in a dynamic array function, such as FILTER with no matches
######	Not an error: the column is too narrow for the number or date

11.6.2 Error checking

Excel checks formulas in the background as you work. When a formula breaks one of its rules, a small green triangle appears in the cell's top-left corner. Select the cell to see a warning button. Its menu explains the problem and offers fixes, such as Copy Formula from Above, Update Formula to Include Cells, Convert to Number or Ignore Error.

To step through every flagged cell, choose Formulas › Error Checking. The dialog box shows each problem with buttons to Show Calculation Steps (Evaluate Formula), Ignore Error, Edit in Formula Bar and go to the Previous or Next one. Formulas › Error Checking › Trace Error draws arrows to the cells that cause an error value.

11.6.3 Error checking rules

File › Options › Formulas controls error checking. Enable background error checking turns the green triangles on or off, and Indicate errors using this color changes their color. Reset Ignored Errors brings back warnings you ignored.

The Error checking rules list decides what Excel flags. Each rule can be turned on or off:

• Cells containing formulas that result in an error
• Inconsistent calculated column formula in tables
• Cells containing years represented as 2 digits
• Numbers formatted as text or preceded by an apostrophe
• Formulas inconsistent with other formulas in the region
• Formulas which omit cells in a region
• Unlocked cells containing formulas
• Formulas referring to empty cells
• Data entered in a table is invalid
• Misleading number formats

11.6.4 Evaluate Formula

Evaluate Formula calculates a formula one step at a time, so you can see exactly where it goes wrong. Select the cell and choose Formulas › Evaluate Formula.

The dialog box shows the formula with the next part to be calculated underlined:

• Evaluate calculates the underlined part and shows its result in italics. Click it repeatedly to work through the formula.
• Step In shows the formula in the underlined cell, if that cell itself has a formula. Step Out returns to the original.
• Restart starts over from the beginning.

For example, if =IF(AVERAGE(B2:B9)>D1, "Over", "Under") returns the wrong answer, Evaluate shows the average, then the comparison as TRUE or FALSE, so you can see whether the problem is in the average or in D1.

11.7 Practice