Consolidating by category
Here are the steps to follow to consolidate multiple worksheets by category:
1 Create a new worksheet for the consolidation.You use this worksheet to specify your destination range.
2 If necessary, open the workbooks that contain the worksheets you want to consolidate.If the worksheets you want to consolidate are in the current workbook, you can skip this step.
3 In the new worksheet from Step 1, select the upper-left corner of the destination range.
4 Choose Data ⇒ Consolidate.The Consolidate dialog box appears.
5 In the Function list, select the summary function you want to use.
6 In the Reference text box, select one of the ranges you want to consolidate.When you’re selecting the range, be sure to include the row and column labels in the range.
7 Click Add.Excel adds the range to the All References list.
8 Repeat Steps 6 and 7 to add all the consolidation ranges.
9 If you have labels in the top row of each range, select the Top Row check box.
10 If you have labels in the left-column row of each range, select the Left Column check box.Figure 1-12 shows a completed version of the Consolidate dialog box.
11 Click OK.Excel consolidates the data from the source ranges and displays the summary in the destination range.
FIGURE 1-12: When consolidating by category, tell Excel where your labels are located.
Chapter 2
Working with Data-Analysis Tools
IN THIS CHAPTER
When it comes to data analysis, you're best off getting Excel to perform most — or, ideally, all — of the work. After all, Excel is a complex, powerful, and expensive piece of software, so why shouldn’t it take on the lion’s share of the data-analysis chores? Sure, you still have to get your data into the worksheet (although a bit later in the book, I talk about ways to get Excel to help with that chore, too), but after you’ve done that, it’s time for Excel to get busy.
In this chapter, you investigate some built-in Excel tools that will handle most of the data-analysis dirty work. I show you how to build two different types of data tables; give you the details on using the very cool Goal Seek tool; delve into scenarios and how to use them for fun and profit; and take you on a tour of the powerful Solver add-in.
Working with Data Tables
If you want to study the effect that different input values have on a formula, one solution is to set up the worksheet model and then manually change the formula’s input cells. For example, if you’re calculating a loan payment, you can enter different interest rate values to see what effect changing the value has on the payment.
The problem with modifying the values of a formula input is that you see only a single result at one time. A better solution is to set up a data table, which is a range that consists of the formula you’re using and multiple input values for that formula. Excel automatically creates a solution to the formula for each different input value.
Creating a basic data table
The most basic type of data table is one that varies only one of the formula’s input cells. Not even remotely surprisingly, this basic version is known far and wide as a one-input data table. Here are the steps to follow to create a one-input data table:
1 Type the input values.To enter the values in a column, start the column one cell down and one cell to the left of the cell containing the formula, as shown in Figure 2-1.To enter the values in a row, start the row one cell up and one cell to the right of the cell containing the formula.
2 Select the range that includes the input values and the formula.In the example shown in Figure 2-1, you'd select the range B7:C15.FIGURE 2-1: This data table has the input values in a column.
3 Choose Data ⇒ What-If Analysis ⇒ Data Table to open the Data Table dialog box.
4 Enter the address of the input cell, which is the cell referenced by the formula that you want the data table to vary.That is, for whatever cell you specify, the data table will substitute each of its input values into that cell and calculate the formula result. You have two choices:If you entered the input values in a row, enter the input cell’s address in the Row Input Cell text box.If the input values are in a column, enter the input cell’s address in the Column Input Cell text box. In the example shown in Figure 2-1, the data table’s input values are annual interest rates, so the column input cell is C2, as shown in Figure 2-2.
5 Click OK.Excel fills the input table with the results. Figure 2-3 shows the results of the example data table.
