# Question about Excel pivot tables **URL:** \ **Category:** Factual Questions\ **Created:** [January 14, 2011, 9:09pm UTC](https://boards.straightdope.com/t/question-about-excel-pivot-tables/567644 "2011-01-14T21:09:09Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [January 14, 2011, 9:09pm UTC](https://boards.straightdope.com/t/question-about-excel-pivot-tables/567644/1 "2011-01-14T21:09:09Z")
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. ---
**Author:** ![Magiver](https://avatars.discourse-cdn.com/v4/letter/m/4491bb/32.png) [@Magiver](https://boards.straightdope.com/u/Magiver)\ **Post date:** [January 14, 2011, 9:30pm UTC](https://boards.straightdope.com/t/question-about-excel-pivot-tables/567644/2 "2011-01-14T21:30:49Z")
set your table to the maximum size of any data imported and hide the blanks? ---
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [January 14, 2011, 9:32pm UTC](https://boards.straightdope.com/t/question-about-excel-pivot-tables/567644/3 "2011-01-14T21:32:29Z")
> [@Magiver](#): > > set your table to the maximum size of any data imported and hide the blanks? Kinda kludgey…but it would work in practice. In theory, there’s no way of knowing the maximum size. ---
**Author:** ![Magiver](https://avatars.discourse-cdn.com/v4/letter/m/4491bb/32.png) [@Magiver](https://boards.straightdope.com/u/Magiver)\ **Post date:** [January 14, 2011, 9:59pm UTC](https://boards.straightdope.com/t/question-about-excel-pivot-tables/567644/4 "2011-01-14T21:59:12Z")
> [@jsc1953](#): > > Kinda kludgey…but it would work in practice. In theory, there’s no way of knowing the maximum size. 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. ---
**Author:** ![Magiver](https://avatars.discourse-cdn.com/v4/letter/m/4491bb/32.png) [@Magiver](https://boards.straightdope.com/u/Magiver)\ **Post date:** [January 14, 2011, 10:02pm UTC](https://boards.straightdope.com/t/question-about-excel-pivot-tables/567644/5 "2011-01-14T22:02:59Z")
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. ---
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [January 14, 2011, 10:06pm UTC](https://boards.straightdope.com/t/question-about-excel-pivot-tables/567644/6 "2011-01-14T22:06:37Z")
Cool; thanks for the tip. ---
**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\ **Post date:** [January 14, 2011, 10:25pm UTC](https://boards.straightdope.com/t/question-about-excel-pivot-tables/567644/7 "2011-01-14T22:25:41Z")
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](http://hubpages.com/hub/Automatically-Add-New-Data-to-an-Excel-Pivot-Table) is how you do it. But the easy way is over reference and suppress the blanks. ---
**Author:** ![jsc1953](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jsc1953](https://boards.straightdope.com/u/jsc1953)\ **Post date:** [January 14, 2011, 10:43pm UTC](https://boards.straightdope.com/t/question-about-excel-pivot-tables/567644/8 "2011-01-14T22:43:02Z")
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. ---
**Author:** ![Magiver](https://avatars.discourse-cdn.com/v4/letter/m/4491bb/32.png) [@Magiver](https://boards.straightdope.com/u/Magiver)\ **Post date:** [January 15, 2011, 3:09am UTC](https://boards.straightdope.com/t/question-about-excel-pivot-tables/567644/9 "2011-01-15T03:09:03Z")
> [@don\_t\_ask](#): > > 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](http://hubpages.com/hub/Automatically-Add-New-Data-to-an-Excel-Pivot-Table) is how you do it. > > But the easy way is over reference and suppress the blanks. 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? ---
**Author:** ![Magiver](https://avatars.discourse-cdn.com/v4/letter/m/4491bb/32.png) [@Magiver](https://boards.straightdope.com/u/Magiver)\ **Post date:** [January 15, 2011, 3:13am UTC](https://boards.straightdope.com/t/question-about-excel-pivot-tables/567644/10 "2011-01-15T03:13:10Z")
If I read that link correctly it is counting rows with data in it. ---
**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\ **Post date:** [January 15, 2011, 3:43am UTC](https://boards.straightdope.com/t/question-about-excel-pivot-tables/567644/11 "2011-01-15T03:43:41Z")
Yeah, the OFFSET function counts the non null rows and columns each time. ---
**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\ **Post date:** [January 15, 2011, 5:05am UTC](https://boards.straightdope.com/t/question-about-excel-pivot-tables/567644/12 "2011-01-15T05:05:07Z")
> [@jsc1953](#): > > 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. Yes, this is the way to do it. ---
**Author:** ![Magiver](https://avatars.discourse-cdn.com/v4/letter/m/4491bb/32.png) [@Magiver](https://boards.straightdope.com/u/Magiver)\ **Post date:** [January 15, 2011, 6:38am UTC](https://boards.straightdope.com/t/question-about-excel-pivot-tables/567644/13 "2011-01-15T06:38:23Z")
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.