Search Contextures Sites

More Tutorials:

 

 

Contextures
Excel news
by email

 

 

 

 


Table of Contents 

 

160x600

 

 

Excel Pivot Table -- Dynamic Data Source

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.

In the screen shot below,

  • the list headings are in row 3
  • the data is in rows 4 to 9, from column A to column H.

prepare data for excel table

Set Up a Dynamic Data Source

Next, set up the data range as a dynamic range, using one of the 3 options shown below.

Advantages of using a dynamic range as a data source:

  • A dynamic range will automatically expand or contract, if data is added or removed.
  • You can base a pivot table on the dynamic range. Then, when you refresh the pivot table, it will include all of the data in the range, even if new rows have been added.

Option 1 -- Named Table

In Excel 2007 and later versions, you can format your list as a Named Table, and use that as the dynamic source for your Pivot Table.

There are detailed instructions here: Excel Tables -- Creating an Excel Table.

To see the steps in creating an Excel Table, please watch this short video tutorial.

Option 2 -- List

In Excel 2003, use the List feature to create a dynamic list. It is a built-in tool that will create a List object on the worksheet.

To create a List:

  • Select a cell in the list
  • Click the Data menu, click List, then click Create List

    create list in excel 2003

  • In the Create List dialog box, check that the range is correct, and fix the range, if necessary
  • Add a check mark to the "My List has Headers" option.
  • Click OK

    create list in excel 2003

When a cell in the List is selected, the list has a solid blue border around it, and there are filter drop down arrows in the heading row. There is also a blank row at the end, where you can add new items.

create list in excel 2003

Option 3 -- Formula-Based Dynamic Range

If you prefer not to use a named table or list, you can use a formula to create a dynamic range. This formula can use the INDEX or OFFSET functions to create the range.

The written instructions are here: Create a Dynamic Named Range in Excel 2003

This video shows the steps for using the OFFSET function, in Excel 2007.

Create the Pivot Table

Once you have the dynamic range set up, you can create a pivot table, based on that range.

Create a Pivot Table in Excel 2003

  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

Download the Sample File

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

Pivot Table Training

If you're looking for a video-based pivot table course,I recommend the following. Both are excellent quality, with clear, easy-to-follow instructions.

Xtreme Pivot Table course

By John Michaloudis, at My Excel Online.

Videos: 200+, total time, approx 9 hours, download

Sample Excel workbooks, to use with the video lessons.

Summary: Detailed coverage of pivot table topics, with in-depth coverage of topics.

 

Core Pivot

By Dave Bruns at ExcelJet.

Videos: 49, total time, approx 2.5 hours, online

Sample Excel workbooks, to use with the video lessons.

Summary: Short, to the point, coverage of pivot table topics, for rapid learning

 

 

More Pivot Table Resources

Tutorials:

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Search Contextures Sites

Last updated: November 2, 2014 3:28 PM