Search Contextures Sites

Excel Pivot Table -- Dynamic Data Source

Contextures Excel news by email Email: Name (optional): We respect your email privacy       Table of Contents Table of Contents	1. Excel Pivot Table Introduction     Getting Started with Pivot Tables     Use a Dynamic Data Source 2. Create a Pivot Table in Excel 2007 3. Pivot Table Tutorial List   Getting Started with Pivot Tables The first step in creating a Pivot Table is to organize your data in a list of rows and columns. In Excel 2010/ 2007, you can format this list as a Named 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 use a formula to 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. For more pivot table tutorials, see the list at the bottom of this page -- Pivot Table Tutorial List 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 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 Pivot Table Introduction  Clear Old Items in Pivot Table Create a Pivot Table in Excel 2007  Custom Calculations  Data Field Layout Dynamic Data Source FAQs - Pivot Tables Field Settings Filter Source Data   Filters, Top 10  Filters, Report Filters GetPivotData Function Grand Totals Grouping Data Layout, Excel 2007 Multiple Consolidation Ranges Pivot Cache    PivotTable Style Printing a Pivot Table    Protection   Running Totals   Show and Hide Items  Sorting Subtotals  Summary Functions Unique Item Count

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

Home Excel Tips Excel Files Products Blog Contact   Privacy Policy   Contextures Inc., Copyright ©2013 All rights reserved.   Debra Dalgleish	Search Contextures Sites