Search Contextures Sites

Excel Pivot Table Tutorial -- Multiple Consolidation Ranges

Create a pivot table from multiple consolidation ranges Limitations of multiple consolidation ranges Excel Pivot Table Tutorial List   Download the sample pivot table tutorial file   Watch the Pivot Table from Multiple Sheets video

Amazon.com

To create a Pivot Table, you can use data from different sheets in a workbook, or from different workbooks, if those tables have identical column structures. However, you won't get the same pivot table layout that you'd get from a single range. This pivot table tutorial explains the steps to create a pivot table from multiple consolidation ranges, describes the limitations, and suggests workaround solutions.

Source data
Create a pivot table from multiple consolidation ranges

Choose Data | PivotTable and PivotChart Report (In Excel 2007, press Alt+D, then press P) Select Multiple consolidation ranges, click Next Select one of the page options, click Next Select each range, and click Add If you chose 'I will create the page fields', you can select each range, and assign field names, in step 2b Click Next

Select a location for the PivotTable, then click Finish In the Column dropdown, hide any columns that contain meaningless data. For example, the Colour column might contain all zeros, because the colours are text, not numbers.

Limitations of Multiple Consolidation Ranges In this example, Item is the first column in the data source, and the pivot table row heading shows the item names. Remaining fields are shown in the column area. You can change the function (e.g. SUM) that is being used by the data value, but it will use the same function on all these columns. The Pivot Table contains some meaningless data, such as sum of Date and columns full of zeros where the database columns contain text. To avoid this, you can rearrange your database columns, and then use data ranges that only include the columns that you want to total. If possible, move your data to a single worksheet, or store it in a database, such as MS Access, and you'll have more flexibility in creating the pivot table. Or, you can create named ranges in an Excel file, and use MS Query to combine the data. There are sample files here: http://www.contextures.com/excelfiles.html#PT0007 For instructions on automating a Union query, read the Contextures Blog article: Create a Pivot Table from Multiple Sheets Download the sample file for this pivot table tutorial Watch the Pivot Table from Multiple Sheets video

Pivot Table Tutorials Excel Pivot Table -- Introduction  Excel Pivot Table -- Clear Old Items Excel Pivot Table -- Create a Pivot Table in Excel 2007  Excel Pivot Table -- Custom Calculations  Excel Pivot Table -- Data Field Layout Excel Pivot Table -- Dynamic Data Source Excel Pivot Table -- FAQs Excel Pivot Table -- Field Settings Excel Pivot Table -- Filter Source Data   Excel Pivot Table -- Filters, Top 10  Excel Pivot Table -- GetPivotData Excel Pivot Table -- Grand Totals Excel Pivot Table -- Grouping Data Excel Pivot Table -- Layout, Excel 2007 Excel Pivot Table -- Multiple Consolidation Ranges Excel Pivot Table -- Pivot Cache    Excel Pivot Table -- PivotTable Style Excel Pivot Table -- Printing    Excel Pivot Table -- Protection   Excel Pivot Table -- Report Filters Excel Pivot Table -- Running Totals   Excel Pivot Table -- Show and Hide Items  Excel Pivot Table -- Sorting Excel Pivot Table -- Subtotals  Excel Pivot Table -- Summary Functions Excel Pivot Table -- Unique Items

Pivot Table Books Pivot Tables, Beginning (Excel 2007)  Pivot Tables, Recipe Book (Excel 2003)  Pivot Tables, Recipe Book (Excel 2007)  Pivot Table Add-Ins Pivot Tables - Add-in -- Pivot Power  Pivot Tables - Add-in - Pivot Play PLUS  Pivot Table Videos Pivot Tables - Clear Old Items Pivot Tables - Copy a Custom PivotTable Style Pivot Tables - Create in Excel 2007 Pivot Tables - Create from Multiple Sheets Pivot Tables - Data Field Layout Pivot Tables - Date Filters, Add Pivot Tables - GetPivotData Pivot Tables - Group Data Pivot Tables - Layout, Excel 2007 Pivot Tables - Report Filters, Add Pivot Tables - Running Totals Pivot Tables - Select Sections Pivot Tables - Subtotals, Create Multiple Pivot Tables - Top 10 Filters

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2010 All rights reserved.	Debra Dalgleish