<b>Pivot Tip 20</b> - Change the Range name to A:K if you intend to add more records

ExcelZone's Five Minute Tips aim to develop your skill at using Excel pivot tables. To test these tips for yourself, try them out on your copy of the pivot_practice.xls database.

Suppose that you have created a pivot table report which you intend to update regularly each week or month. Before running it each time, you will be adding some new records to the database.

Every time you add new records you will have to manually change the data range.

Continued...

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.

Comments

all right, column headings need to be in row 1

David Carter | | Permalink

Well, OK. It only works if the column headings are in the first row. But doesn't any normal person put them there anyway?

Using a Dynamic Named Range is better

tony.stevens | | Permalink

Personally, I would suggest that using a "Dynamic Named Range" would work better and get around the problem of how many columns/rows & the starting row of a pivot table import range.

The solution you suggest, also puts "blank" data into the pivot table, which then needs to be manually excluded.

Information of Dynamic Named Ranges can be found at: http://www.ozgrid.com/Excel/DynamicRanges.htm

It takes a little bit more work, but is a much better solution in the long run.

Tony

of course it works!

David Carter | | Permalink

Steve, Excel's message about "pivot field name is not valid" means that you have included a blank column in your data range.

You have to type in A:D, A:G, A:M or whichever is the rightmost column in your data.

If you've just typed in A:K but your data range is A1:F1000, for example, columns G to J will be blank and you'll get this error message. You will need to type in A:F