Excel -- Pivot Tables -- Dynamic Data Source


Table of Contents

1. Pivot Tables -- Introduction
    Getting Started
    Use a Dynamic Data Source

 

Getting Started

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.

You can download the zipped sample file used for this example.

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.

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

       
1. Pivot Tables -- Introduction
2. Pivot Tables -- Data Field Layout
3. Pivot Tables -- Show and Hide Items
4. Pivot Tables -- Clear Old Items
5. Pivot Tables -- Field Settings
6. Pivot Tables -- GetPivotData
7. Pivot Tables -- Grouping Data
8. Pivot Tables -- Multiple Consolidation Ranges
9. Pivot Tables -- Printing   
10. Pivot Tables -- Custom Calculations 
11. Pivot Tables -- Pivot Cache     
12. Pivot Tables -- Protection  
13. Pivot Tables -- Grand Totals


Table of Contents 
Dec 20, 2007

  
   
       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store
Last updated: July 18, 2008 11:44 PM