Pivot Table Dynamic Data Source

Create a pivot table that is based on a dynamic data source -- that is a range that adjusts automatically, if new records are added or existing data is changed or removed.

Author: Debra Dalgleish

Get 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 check that there are no rows that are completely blank.

In the screen shot below, the data is set up correctly, and has these features:

• every cell in the list headings, in row 3, is filled
• each heading is unique - there are no duplicates that could cause problems
• the data is in rows 4 to 9, from column A to column H
• there is no data in the adjacent rows or columns - there are empty rows and columns surrounding the data

Tip: To make sure the data is in a solid block of cells, try this:

• Select any cell in the range of cells
• On the keyboard, press Ctlr + A (select All)
• Are all the cells selected? If not, check for hidden rows or columns that might be empty.

Try This Pivot Table

Before you build your own pivot table in an Excel workbook, you can see how a pivot table works, by trying the interactive Microsoft Excel example shown below.

Note: This embedded interactive Excel file might not work in all web browsers.

Set Up a Dynamic Data Source

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

1) Named Table

2) Formula-Based Dynamic Range

Advantages of using a dynamic range as a data source:

• A dynamic range will automatically expand or contract, if new columns or rows of data are added, or data is 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 or columns have been added.

NOTE: You must update pivot table after making changes to the records in the dynamic data source. The pivot table does NOT update automatically, to show revised or new data

Create a 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.

To see the steps in creating an Excel Table, you can watch this short video tutorial. There are written steps below the video, and more Table tips and videos on the Excel Tables page.

Note: If you're using Excel 2003, organize your data in Excel Lists

Create an Excel Table

After your data is organized, as described above, follow these steps to create a named Excel Table.

• Select a cell in the list of data that you prepared on the worksheet.
• On the Excel Ribbon, click the Insert tab.

• In the Tables group, click the Table command.
• In the Create Table dialog box:
  • range for your data should automatically appear
  • check box for My table has headers option should be checked
• If necessary, you can adjust the range, and check box for table headers
• Click the OK button, to create a named table with the selected settings.

Formula-Based Dynamic Range

If you don't want to use a named table, you can use an Excel formula to create a dynamic range. This formula can use the INDEX or OFFSET functions to create the range.

This video shows the steps for using the OFFSET function, in Excel 2007, and the written instructions are below the video.

Create Dynamic Named Range With Formula

To create a dynamic named range, based on a formula, follow the steps below.

Note: Dynamic named ranges will not appear in the Name Box drop down list. However, you can type the names in the Name Box, to select that range on the worksheet. Or, go to the Name Manager, and you can see all the names listed there.

1. On the Ribbon, click the Formulas tab
2. In the Defined Names group, click the Define Name command
3. In the New Name dialog box, type a one-word name for the range, e.g. NameList
4. Leave the Scope set to Workbook.



5. In the Refers To box, enter an Offset formula that defines the range size, based on the number of items in the column, e.g.:
   =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
   In this example, the list is on Sheet1, starting in cell A1
   The arguments used in this Offset function are:
• Reference cell: Sheet1!$A$1
• Rows to offset: 0
• Columns to offset: 0
• Number of Rows: COUNTA(Sheet1!$A:$A)
• Number of Columns: 1
• Note: for a dynamic number of columns, replace the 1 with:
              COUNTA(Sheet1!$1:$1)
6. Click OK TOP

Create the Pivot Table

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

Watch this video, to see the steps for creating a pivot table in Excel 2013 and later, using a dynamic pivot table data source. There are written instructions below the video, and you'll find more pivot table tips on the Create an Excel Pivot Table page.

NOTE: To create a pivot table in Excel 2010 or Excel 2007, follow the pivot table setup instructions here

How to Create a Pivot Table

First, follow these steps, to see which pivot table layouts are suggested by Excel.

1. Select any cell in the source data table.
2. On the Ribbon, click the Insert tab.
3. In the Tables group, click the Recommended PivotTables button.

   

4. In the Recommended PivotTables window, scroll down the list, to see the suggested layouts.
5. Then, click on a layout, to see a larger view, on the right side of the window.

   

6. Click on the layout that you want to use, then click the OK button.

The pivot table will appear on the Excel worksheet, based on the recommended layout that you selected.

Note: If you change column headings in the source table, the pivot table will not recognize the old names any longer. If the old names were in the pivot table layout, they will disappear. You'll need to manually add the renamed fields.

Locate and Fix Pivot Table Source Data

In this video, you'll see how to locate the data source for a pivot table. Then, check the data source, to make sure it includes all the rows and columns that you need. If necessary, change data source, or adjust the change the pivot table data source range, to include new rows or columns.

For the written instructions and sample file, go to the Pivot Table Source Data page.

Video Timeline

• 00:00 The Orders Pivot Table
• 00:21 Manually Check the Numbers
• 00:33 Find the Source Data
• 01:08 Change PivotTable Data Source Window
• 01:27 Fix the Data Source Range
• 01:57 Create a Dynamic Source
• 02:48 Create a Named Table
• 04:22 Use Dynamic Source for Pivot Table
• 04:56 Test the Dynamic Source
• 05:31 Conclusion

Get the Sample File

• Food Sales: To practise building a pivot table from a dynamic data source, you can download this zipped Food Sales Orders sample Excel file. The zipped Excel workbook is in xlsx format, and does not contain any macros.

Note: If you want to try this technique with other types of sample data, such as work ordersdata, or hockey player statistics, go to the Excel Sample Data page, and take a look at the sample files there.

More Pivot Table Tutorials

How to Plan and Set Up a Pivot Table

Pivot Table Introduction

Multiple Consolidation Ranges

Clear Old Items in Pivot Table