A1D18 and click Add to add it to the range of data that will be used to create

A1d18 and click add to add it to the range of data

This preview shows page 45 - 48 out of 51 pages.

in Figure 43-60) the East sales data, EAST!$A$1:$D$18, and click Add to add it to the range of data that will be used to create our PivotTable. F43xx60 FIGURE 43-60 Adding the East data. F43xx60: This figure shows the East sales data added to the range that is generating the PivotTable.
Image of page 45
© 2016 Microsoft Corporation. All Rights Reserved. Page 46 2/24/2017 Then clear the East data from the Range portion of the dialog box, and select the West data, WEST!$A$1:$D$24, and then click Add to add this data to the All Ranges section. After clicking Next, you can decide whether to place the final PivotTable in a new worksheet or the current worksheet (step 3). Choose a new worksheet, and after selecting Finish, you obtain the PivotTable (see Figure 43-61) in the worksheet PT of the workbook West.xlsx (in this chapter’s Practice Files folder). F43xx61 FIGURE 43-61 A PivotTable summarizing the East and West sales. F43xx61: This figure summarizes the total sales of each product during each month. We find, for example, that total sales of Product A in February were 1,317, and so on. You can filter the products by selecting the drop-down arrow in cell A4; filter the months by using the drop-down arrow in cell B3. The drop-down arrow in cell B1 allows you to filter the PivotTable so that only East or West sales data is used. Refresh updates the PivotTable, showing data changes. The Slicer and Timeline tools do not work with a PivotTable created from multiple ranges. If you do not like using the Alt+D+P combination, you can add the PivotTable and PivotChart Wizard to the Quick Access Toolbar by selecting File, Options, Quick Access Toolbar, Commands Not In The Ribbon (from the Choose Commands From list),and selecting PivotTable And PivotChart Wizard. Then click Add to add the command to your customized Quick Access Toolbar, and click OK to close the Excel Options dialog box. To create a PivotTable from multiple ranges, the headings (in this case January, February, and March) in each range must be identical. In Chapter 44, we will discuss a feature added in Excel 2013, the Data Model, which allows you to create PivotTables even when a heading from one source range occurs in none of the other source ranges. How can I create a PivotTable based on an already created PivotTable? Often, we want to create a PivotTable based on an already created PivotTable. This allows us to view several PivotTables based on the same data. For example, in the file Makeuptimeline.xlsx, we created (as shown in Figure 43-53) a summary of makeup sales with a list of salespeople’s names going down and a list of products going across. Suppose we also want to create a PivotTable with the list of products going down and names of salespeople going across. After selecting a cell in the worksheet Pivot Table of the file Makeuptimeline.xls, press Alt+D+P to bring up the PivotTable and
Image of page 46
© 2016 Microsoft Corporation. All Rights Reserved. Page 47 2/24/2017 PivotChart Wizard. Then choose the Another PivotTable Report Or PivotChart Report option in step 1, and click Next. Then, in step 2, choose the PivotTable from which you wish to build your new table.
Image of page 47
Image of page 48

You've reached the end of your free preview.

Want to read all 51 pages?

  • Summer '16
  • naveen rathi
  • Dialog box, All rights reserved

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture