Search Contextures Sites

Excel Pivot Table -- Dynamic Data Source

Table of Contents	1. Excel Pivot Table Introduction     Getting Started with Excel Pivot Tables     Use a Dynamic Data Source 2. Create a Pivot Table in Excel 2007 3. Excel Pivot Table Tutorial List   Getting Started with Excel Pivot Tables	Table of Contents
	The first step in creating an Excel Pivot Table is to organize your data in a list of rows and columns. In Excel 2007, you can format this list as an Excel Table, and use that as the dynamic source for your Excel Pivot Table. There are instructions here: Excel Tables -- Creating an Excel Table. In Excel 2003, you can use the Data | List feature to create a dynamic list, and use that as the source for your Excel Pivot Table. In Excel 2002 and earlier versions, you can create a dynamic data source, as described below. This will ensure that when new data is added to the source table, the Excel Pivot Table will automatically include those rows or columns when it is refreshed. Many books and web sites have information on creating Pivot Tables in Excel 2003, and earlier versions. For an introductory pivot table tutorial, and list of Excel Pivot Table links, visit Jon Peltier's site.

Use a Dynamic Data Source You can use a dynamic formula to define the source range for an Excel Pivot Table. As new items are added to the table, the named range will automatically expand. You can download the zipped sample Excel Pivot Table file used for this pivot table tutorial. 1. Name the Range Choose Insert>Name>Define Type a name for the range, e.g. Database In the Refers To box, enter an Offset formula that defines the range size, based on the number of items in a column that doesn't contain any blank cells. , e.g.: =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),7) In this example, the list is on a sheet named 'Data', starting in cell A1. The arguments used in this Offset function are: Reference cell: Data!$A$1 Rows to offset: 0 Columns to offset: 0 Number of Rows: COUNTA(Data!$A:$A) Number of Columns: 7   Note: for a dynamic number of columns,      replace the 7 with: COUNTA(Data!$1:$1) Click OK 2. Base the Pivot Table on the Named Range Select a cell in the database Choose Data>PivotTable and PivotChart Report Select 'Microsoft Excel List or Database', click Next. For the range, type your range name, e.g. Database Click Next Click the Layout button Drag field buttons to the row, column and data areas Click OK, click Finish

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	Search Contextures Sites