Excel Data Analysis For Dummies. Paul McFedries

Excel won’t outline a worksheet with, say, SUM functions where some of the range references are above the formula cell and some are below.

      Here are the steps to follow group-related data:

      1 Display the worksheet you want to outline.

      2 Choose Data ⇒ Group ⇒ Auto Outline.If you don’t see the Group command, choose Outline ⇒ Group. Excel outlines the worksheet data.

As shown in Figure 1-10, Excel uses level bars to indicate the grouped ranges and level numbers to indicate the various levels of the underlying data available in the outline.

      FIGURE 1-10: When you group a range, Excel displays its outlining tools.

Here are some ways you can use the outline to control the range display:

       Click the − (collapse) button to hide the range indicated by the level bar.

       Click the + button (expand) for a collapsed range to view it again.

       Click a level number to collapse multiple ranges on the same outline level.

       Click a level number to display multiple collapsed ranges on the same outline level.

Consolidating Data from Multiple Worksheets

      Companies often distribute similar worksheets to multiple departments to capture budget numbers, inventory values, survey data, and so on. Those worksheets must then be combined into a summary report showing company-wide totals. Combining multiple worksheets into a summary report is called consolidating the data.

      Sounds like a lot of work, right? It sure is, if you do it manually, so forget that. Instead, Excel can consolidate your data automatically. You can use the consolidate feature to consolidate the data in either of two ways:

       By position: Excel consolidates the data from two or more worksheets, using the same range coordinates on each sheet. Use this method if the worksheets you’re consolidating have an identical layout.

       By category: Excel consolidates the data from two or more worksheets by looking for identical row and column labels in each sheet. Reach for this method if the worksheets you’re consolidating have different layouts but common labels.

      In both cases, you specify one or more source ranges (the ranges that contain the data you want to consolidate) and a destination range (the range where the consolidated data will appear).

      Consolidating by position

Here are the steps to trudge through if you want to consolidate multiple worksheets by position:

      1 Create a new worksheet that uses the same layout — including row and column labels — as the sheets you want to consolidate.The identical layout in this new worksheet is 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.

      7 Click Add.Excel adds the range to the All References list, as shown in Figure 1-11.

      8 Repeat Steps 6 and 7 to add all the consolidation ranges.

      9 Click OK.Excel consolidates the data from the source ranges and displays the summary in the destination range.

      FIGURE 1-11: Consolidate multiple worksheets by adding a range from each one.

If the source data changes, you probably want to reflect those changes in the consolidation worksheet. Rather than run the entire consolidation over again, a much easier solution is to select the Create Links to Source Data check box in the Consolidate dialog box. You can then update the consolidation worksheet by choosing Data ⇒ Refresh All.

      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

      

Creating basic and two-input data tables

      

Analyzing your data using the Goal Seek tool

      

Creating and running scenarios

      

Optimizing your data with the Solver tool

      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