Question about Excel pivot tables

Factual Questions
1

Let’s say I have a spreadsheet. In one area is a block of raw data, that gets updated periodically. In another area I’ve created a pivot table that refers to that block of data.

When I update the data, I have no way of knowing precisely how many rows will be in the new block – could be more than there were before, could be fewer.

So if I update the data and there are now more rows than before, my pivot table doesn’t seem to realize that. Hitting “refresh” picks up the new contents, but only through however many rows were specified when I created the pivot table.

Is there a way to make the pivot table smarter – to know how many rows should be included?

Excel 2007, if it matters.

2

set your table to the maximum size of any data imported and hide the blanks?

3

Kinda kludgey…but it would work in practice. In theory, there’s no way of knowing the maximum size.

4

Then you set it to the maximum rows of Excel. This is what I use to do because I had data dumps into sheets which had additional columns grinding up data. The ultimate goal was data that I pivot out into something useful. It was easy for me to just turn off blanks. Once you have blanks turned off it’s a done deal. You don’t have to keep repeating the option.

You can’t use the column filter directly on the sheet because it will still see the hidden rows.

5

FYI, I didn’t reference the whole sheet because I knew the maximum number of rows possible in my downloads. I also color coded the area so I knew what the maximum field was just in case.

6

Cool; thanks for the tip.

7

You need to create a named dynamic range to do what you want. You then use it as the source of the pivot table. Here is how you do it.

But the easy way is over reference and suppress the blanks.

8

Good stuff on that link – short term, I’ll just over-reference; but in the long term, I’ll convert to using the Excel Table feature of 2007.

9

I was hoping someone would chime in with another method. What happens if less data is dumped than the original setup. Will it recognize a smaller number of rows?

10

If I read that link correctly it is counting rows with data in it.

11

Yeah, the OFFSET function counts the non null rows and columns each time.

12

Yes, this is the way to do it.

13

Unless I’m doing something wrong creating a table using existing data doesn’t take into account less data. It returns a blank in the pivot table. Also, when I pasted new data into the table it did it as an insert (versus paste) and left the last line of data in place.