Hello, group!

I have an Excel Pivot table that is used to display daily census lists. These lists can vary in the number of rows greatly.

Now, at first, the data source was set to the range of cells that encompassed all the populated rows of the source data worksheet; say, 'Pivot Data'!$A1:$X25

But, tomorrow, it may be 'Pivot Data'!$A1:$X58 or 'Pivot Data'!$A1:$X8

I really hate having to go to the Pivot Table to reassign the Data Source to grab the full rowset each time.

So, what I did was change the source to 'Pivot Data'!$A:$X

This worked fine - well, except that it put a row of (blank) at the bottom of the pivot table. A minor annoyance, but not a deal-breaker. Although, I tried but could find no way to get it to NOT display this annoying row.

Now, a new column has been introduced. This column is assigned to be first and is used for sorting. However, it may not always be populated for all rows, even though the remainder of the fields may have data.

Now what's happening is that, when using 'Pivot Data'!$A:$X, the group of rows where the first column is not populated, are all put to the bottom of the pivot table. That's not so much a problem - though, having them first would be nice. The problem is that the row of all (blank)s is being placed as the first row of the group. This is annoying. I don't want that row to separate the two groups of rows.

I can fix this problem by going back to the old way by specifying the exact data source, requiring me to alter it every time the report is run, but I want to avoid that.

So, the question is, is there any way to use the 'Pivot Data'!$A:$X data source template, but have it NOT display all those (blank)s?

• Edited by Adam Quark Monday, June 29, 2015 4:00 PM
• Moved by Lanlan HuangMicrosoft contingent staff Tuesday, June 30, 2015 2:58 AM

Thanx, Hans. The filter idea wouldn't be any better than just changing the data source each time - well, maybe a little, hmm... I'll try it and see.

I'll have to ponder the dynamic table idea a little, but, at first glance, it looks like it won't handle a data source where the data is just a Copy & Paste from a SQL query result. But, like the filter idea, I'll have to try it out to see what happens.