Search Contextures Sites

Excel Pivot Table -- Dynamic Data Source

Contextures
Excel news
by email

 

 

 

 

 


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. Make sure that there is a heading in every column, and no rows that are completely blank.

Then, set up the data range as a dynamic range. This type of range will automatically expand or contract, if data is added or removed. Then, when you refresh the pivot table, it will include all of the data in the range, even if new rows have been added.

  • In Excel 2010/ 2007, 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, 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, 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 in Excel 2002

In Excel 2002 and earlier versions, where tables and lists are not available, you can use a dynamic formula to define the source range for a Pivot Table. As new items are added to the table, the dynamic named range will automatically expand.

You can download the zipped sample file used for this tutorial

1. Name the Range

  1. Choose Insert>Name>Define
  2. Type a name for the range, e.g. Database
  3. 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:
    1. Reference cell: Data!$A$1
    2. Rows to offset: 0
    3. Columns to offset: 0
    4. Number of Rows: COUNTA(Data!$A:$A)
    5. Number of Columns: 7
        Note:
      for a dynamic number of columns,
           replace the 7 with: COUNTA(Data!$1:$1)
  4. Click OK

2. Base the Pivot Table on the Named Range

  1. Select a cell in the database
  2. Choose Data>PivotTable and PivotChart Report
  3. Select 'Microsoft Excel List or Database', click Next.
  4. For the range, type your range name, e.g. Database
  5. Click Next
  6. Click the Layout button
  7. Drag field buttons to the row, column and data areas
  8. Click OK, click Finish

Learn how to create Excel dashboards.

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

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Search Contextures Sites
Last updated: April 13, 2014 10:14 AM