Automatically Add New Data to an Excel Pivot Table
By ddalgleish
After you create a pivot table based on a list in Excel, you might add
new records to the existing list. For example, the pivot table shown below was created when there were 21 sales orders in the list, and it's based on rows 1 to 22. The next day, you might add five new orders at the bottom of the list. You would have to change the pivot table, so it includes rows 23 to 27.
You don’t want to manually change the pivot table range every time you add new data. Instead, you'd like the new orders to be automatically included, when you refresh the pivot table.
This pivot table tutorial will help you create a dynamic range for the Excel list. It will automatically include any new rows, and the pivot table will be based on that dynamic list.
Note: If you're creating the pivot table in Excel 2007, you can use an Excel Table as the pivot table source, and it will expand automatically. Use the following method for earlier versions of Excel, or if you don't want to create an Excel Table.
Create a Dynamic Range
The first step is to create a dynamic range, which will automatically expand to include the
new rows. To do this, you'll define a name, using a formula.
- Select a cell in the source list -- in this example, the list of orders.
- On the Ribbon, click the Formulas tab, then click Define Name (In Excel 2003, choose Insert | Name | Define.)
- In the Name box, type a name for the dynamic range, for example, PivotData.
- From the Scope dropdown list, select Workbook.
- In the Refers to box, type an OFFSET formula that refers to the
top left cell in the list. For this example, the list starts in cell A1, and is on a worksheet named Data (Note: If necessary, replace the commas with the separator for your regional settings, e.g. semi-colon)
=OFFSET(Data!$A$1,0,0, COUNTA(Data!$A:$A), COUNTA(Data!$1:$1))
- Click OK to close the New Name dialog box.
The OFFSET formula counts the items in column A on the Data sheet, and the headings in row 1, and that is the size of the named range.
Note: This technique will not work if there are other items in row 1 or column A of the Data worksheet. Those items would be included in the count, and would falsely increase the size of the source range.
Change the Pivot Table Source (Excel 2007)
The next step is to change the pivot table’s source to the dynamic range that you created:
- Click any cell in the pivot table.
- On the Ribbon, click the Options tab, under the PivotTable Tools tab.
- In the Data group, click Change Data Source.
- In the Table/Range box, type the name of the dynamic range -- PivotData
- Click OK.
Change the Pivot Table Source (Excel 2003)
- Right-click a cell in the pivot table
- Click PivotTable Wizard
- In Step 3, click the Back button
- In Step 2, for the range, type your range name, e.g. PivotData
- Click Finish
Update the Pivot Table
Now, when you add new sales orders, then refresh the pivot table, the new rows of data will automatically be included.
The PivotData named range is based on the count of items in column A on the Data sheet, and the pivot table is based on that dynamic range.
As soon as the pivot table is refreshed, the 5 new records show up, and now there are 26 orders in the grand total, instead of 21
Pivot Table FAQs
There are answers to many common questions about Excel Pivot Tables and Pivot Charts at the Pivot Table FAQs page.
After you add new data, the new items might appear at the bottom of the pivot table's drop down lists. To fix this problem, follow the instructions in my pivot table tutorial New Items at End of Pivot Table Drop Down Lists
My Pivot Table Books on Amazon
 | Amazon Price: $19.09 List Price: $34.99 |
 | Amazon Price: $22.37 List Price: $39.99 |
 | Amazon Price: $21.40 List Price: $34.99 |
Pivot Table Tutorial Videos
More of My Excel Hubs
- Organize Data in an Excel 2007 Table
In Excel 2007, the formatted Excel Table feature makes it easier for you to sort and filter your data, and to scroll through thousands of rows. An Excel Table expands when you add new data, so it can be used as the source for a Pivot Table, and new data will automatically when you update the pivot table. - 2 years ago
- How to Fix Blank Cells Before Sorting in Excel
Blank cells could be putting your Excel data at risk! Some Excel worksheets have cells that were left blank, to make the headings and subheadings easier to read. For example, in the screen shot... - 2 years ago
- Holiday Dinner Planner in Excel
In Excel Dinner Planner, select a time to eat dinner, and enter the menu items. Excel will create a timetable, to help you stay organized, and have dinner ready on time. - 2 years ago
Add Running Totals to an Excel Pivot Table
Comments
Albert, thanks for asking about Excel 2003.
I've added a new section in this hub, with the title, "Change the Pivot Table Source (Excel 2003)". Instead of selecting a range, just type the name of the dynamic range that you created.
Works a treat now!
Thank you very much for your help.
Albert, you're welcome! Thanks for letting me know that you got it working.
The 'dynamic range' trick is one of the neatest things I've learned in EXCEL. This website is great!! I've bookmarked it....good job!
Thanks Jethro! Glad the dynamic range trick helped you.
Hi,
Thanks for a valuable advice.
I noticed that to get regular charts to refresh automatically, this trick won't work.
Do you have a suggestion?
Thanks Emmad. For charts, you could try the dynamic range suggestion in my blog article: http://blog.contextures.com/archives/2009/05/06/pr
Hi ddalgleish...
It works fine for me. I was having this problem for long and since I'm new to excel programming... I wasn't able to state my query well. Anyways I stumbled on your site and I'm glad that my issue is resolved :)
Wish to learn more from you.
Thank you
Beautiful!
I'm designing a prototype to showcase using a pivot table to connect to an analysis cube. I was getting tired of manually "refreshing" the data each time I added new rows and/or columns. I thought to myself, "there has to be a better way!"
Then I ran across your blog. Thank you! This will save me copious amounts of time.
BadBoysDriveAudi, you're welcome! Thanks for letting me know how it helped you.
Hello,
It did not work for me...:( not sure whether I am doing something wrong, but i keep getting the error "invalid type" once I enter the name of my dynamic range in the Table/Range box... any help is much appreciated !!
Thanks for sharing the wonderful screen shots, it helped me a lot.
@Cat, if you open the Name Manager, and click in the Refers To box for your dynamic range, do you see an outline around the range?
Hi,
I tried to follow your steps, but when I enter the OFFSET code in the refers box, I get an error message:
'The formula that you typed contains an error'
I have a lot of data in one sheet(headers going up to CQ) and every other day about 100 rows of data are added. So I would like my pivot table to be dynamic.
I think my formula should look like this:
=OFFSET(cst!$A$1,0,0,COUNTA(cst!$A:$A),COUNTA(cst!$1:$1))
I also found something like this:
=OFFSET(cst!$A$1,,,COUNTA(cst!$A:$A),COUNTA(cst!$1:$1))
Excel marks the part: $A$1,0,0,COUNTA
thanks for your help :)
@inanutshell, your first formula looks fine. Maybe you need to use a semi-colon separator in your formula, instead of a comma.
Very useful, thanks!
Thanks Martin!
Great! I got what I was looking for. I also got an easier solution for simpler tasks. Select your data and convert it to a table. Use that table to create Pivot. Next time you add data to your table, just refresh your pivot! No formulas to learn :)
Thanks Ankit, I've added a link to my hub on How to Create an Excel Table --
Hello, I am trying your formula but keep getting a "Reference is Not Valid" error. I am not quite sure I am following the directions correctly. I am using Excel 2003.
Thank you,
Eric
Nevermind - I got it to work! I think I was fudging the formula but now it is fine.
Dynamic range trick is really working well for me . Thanks for sharing this here. Can you please brief this to some extent so it really make easy to understand. Very good videos though.
Hi Debra,
Excellent article about practical uses of dynamic ranges!
I agree with Ankit; Excel 2007 and 2010 users don't need to use dynamic ranges in Pivot Tables.
At the source table side...
Excel tables are automatically named: Table1, Table2, etc.
Also, new entries are added automatically to the table, assuming you use the TAB key to add a new row or you simply type the new data at the last row of the table.
At the Pivot Table side...
When you use the "Summarize with PivotTable" command, the new PT source data is Table1, Table2, etc.
As Ankit pointed out, you need only to refresh data
John, thanks, and you're right about using Excel Tables in Excel 2007 and later versions. There's a link to my Excel Table hub in the 4th paragraph of this hub, so users of those Excel versions can get the details.
My user change office 2007 to office 2010. Now the excel always crashes and restart while using pivot with ms query.
Thx.
I have a spreadsheet that was created in Excel 2003 and the Refresh function works fine on the pivot tables. However, when I open the spreadsheet in Excel 2007 and try to refresh, I receive an error message "Reference is invalid." Any suggestions?
Trish, try changing the source data to an Excel Table, and see if that solves the problem.
My Excel Tables hub has info on setting one up:
http://hubpages.com/hub/Organize-Data-in-an-Excel-
Then, use the table name as the pivot table source.
Please help. I added a new row of data to my pivot table and the refresh is not picking it up. it is a new character within an existing field list. i am using a dynamic data range so new data should technically be updating.
Lisa, if you're using Excel 2007 or Excel 2010, try changing the source data to an Excel table, and base the pivot table on the Excel Table name.
If you're using an earlier version of Excel, check your dynamic range, to make sure it's counting the rows correctly. Maybe there are blank cells in the column that's being used for the count.
Hi ddalgleish.
Nice article. I have an Dutch Excel 2007 version and can confirm that the ',' needs to be replaced with ';' to save the named range.
After saving the named data range I can't change or make a new pivot table with the named range. It doesn't recognise the named range.
I will try Excel Tables.
@florisz, Thanks, I've added that as a note in the instructions.
First, thanks for sharing this. Really solved a problem.
On the matter of Excel 2007 not needing dynamic ranges, it may not always be true. I'm pulling data in from an XML web services source to update the source table. The pivot table still maintains its original view on the size of the source data. In this case, I needed dynamic range.
@Art, there might be a sheet level name that is automatically created for the web services data. If you click the drop down arrow in the name box, while on that sheet, you should see the data range name.
Then, you could use that name as the pivot table source, including the sheet name.
Great tutorial, thanks very much for explaining in such great detail.
@Geoff, you're welcome! Thanks for your comment.
Sure it is very useful & great idea.
Thanks for your effort.
Here is better than MS-office support web.
Thank you very much : )
Excellent stuff!! Thanks
This does not work for me if I select the scope as Workbook - I have to select the scope as DATA. I am running Excel 2010 but trying to build to support all versions. Any comments on why I need to select data?
@Austin, it should work the same in Excel 2010 as it does in earlier versions. What happens when you try to use Workbook as scope? What name are you using, and what other names are in the workbook?
Hi Debra,
I keep my Data and PivotTable in two separate workbooks. In the Data File I have a Dynamic Range named LocalSource. If I use this named range as a source in the Pivot Table file, it works fine. But If create a Dynamic Range named ForeignSource in the PivotTable workbook but (which too refers to the same database in the Data workbook) and try to use it as a Pivot Source, I keep getting "Reference is not valid" error. Any guess !
Regards
Kanwal
Hi Debra,
Just to clear that both the files are open at the time of changing the source of the Pivot Table.
Regards
Kanwal
Excellent explanation. The pivot table is new to me, I'm currently learning more about it and I find it very interesting. Thanks for this hub.
Albert 2 years ago
Using Excel 2003 I have Created a Dynamic Range, but can't do the Change the Pivot Table Source step. It only gives me an option to put in a range (such as Data!A1:B2). Advice would be appreciated.