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 15 hours 28 minutes ago
• Moved by Lanlan HuangMicrosoft contingent staff 4 hours 30 minutes ago

You can apply a filter that hides the (blank) row:

• Click the dropdown arrow in the row labels cell.
• Clear the check box for (blank)
• Click OK.

Alternatively, you can base the pivot table on a named table or on a defined name that represents a dynamic range - see Excel Pivot Table -- Dynamic Data Source.

Hi Adam,

This forum is for Developer discussions and questions involving Microsoft Excel, your issue is more related with Pivot Tables which is a product function in Excel, I will move this thread to the more related forum.

Reference: http://social.technet.microsoft.com/Forums/en-US/home?forum=excel

Thanks for your understanding.

Best Regards,

Edward