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

 

 

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

    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

   

Excel Pivot Table Tutorial List

     

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  

Pivot Table Blog -- Related Posts

Deleting the Entire Pivot Table  
Locate the Source Data For a Pivot Table  

 



Table of Contents 

 
Learn how to create Excel dashboards.

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright © 2009.
All rights reserved.

 

Search Contextures Sites
Last updated: January 30, 2010 2:01 AM