Search Contextures Sites

Excel Pivot Table -- Dynamic Data Source

Contextures
Excel news
by email

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

  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 ©2013
All rights reserved.

 

Search Contextures Sites
Last updated: June 3, 2013 6:57 PM