Search Contextures Sites

Table of Contents	Excel -- Pivot Tables -- Dynamic Data Source
	1. Pivot Tables -- Introduction     Getting Started     Use a Dynamic Data Source 2. Create a Pivot Table in Excel 2007   Getting Started
	The first step in creating a 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 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 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 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 a brief introduction, and list of 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 a Pivot Table. As new items are added to the table, the named range will automatically expand. You can download the zipped sample file used for this example. 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 Tables -- Introduction  Pivot Tables -- Create a Pivot Table in Excel 2007  Pivot Tables -- Data Field Layout Pivot Tables -- Show and Hide Items Pivot Tables -- Clear Old Items Pivot Tables -- Field Settings Pivot Tables -- GetPivotData Pivot Tables -- Grouping Data Pivot Tables -- Multiple Consolidation Ranges Pivot Tables -- Printing    Pivot Tables -- Custom Calculations  Pivot Tables -- Pivot Cache      Pivot Tables -- Protection   Pivot Tables -- Grand Totals Pivot Tables -- Running Totals   Pivot Tables -- Filter Source Data	Table of Contents

Home     Excel Tips     Excel Files      Blog    Contact
Privacy Policy		The Excel Store	Debra Dalgleish