How to hide blank rows from pivot-table so that the pivot-chart does not contain that row?

1
votes

  1. scenario: I have a pivot-table with years in rows and sales in values. Additionally I have sales as Difference to previous year (built-in pivot function "show as ...") in values. It works fine, but the first row in the pivot-table is blank for sales and sales-diff. It is obvious, because there is no year before the first year, so the first row is blank.

  2. scenario: If I remove the sales and keep the sales-diff in values then the entire first row in the pivot-table is blank except for the year. The option "Show elements without data" under year field-options is deactivated! Still the blank row is there.

Now when I make a pivot-chart the blank row is also shown in the chart. I could hide the entire blank row in the worksheet, but it does not affect the chart. I could also make another non-pivot-table on top of the pivot-table and take it as the data source for the charts. But I prefer not to complicate the thing.

So the question is: how can I get rid of the blank row in pivot-table and more important in the pivot-chart?

Excel Version in use: 2013, 2016, 2019, 2019 365

Thanks for any help.

1
excelpivot-tableblank-linepivot-chart
couldn't you simply filter out the blank row from the underlying pivot table ? - JLCH
Unfortunately not. There is a filter just for the year and not for the values. If I filter the first year (blank row) then the second year becomes the first year and the problem is the same. - Asg
I updated my Question just now. - Asg
You can have same field in the 'Report filter' and the 'values' simoultaneously, simply drag it in there as well - from there you can filter whatever you want - JLCH
I can put the sales field in the "Report filter" but not the sales-diff field and that causes the problem. Sales does not have blanks but sales-diff. Am I missing something? - Asg

1 Answers

0
votes

I ran into the same issue, and was able to resolve it. Providing my answer here to hopefully help anyone that might be having the same issue. The way I did it is as follows:

  1. At your source table, fill your "blank" cells of the column added in the Pivot Rows field (Row Label) with any text (I used "Not Applicable").

  2. Once your Pivot Table is established, select to filter "Filter by Labels" from the "Row Labels" drop-down menu.

  3. Select "Does Not Contain" from the sub-menu.

  4. Enter the identifying text used to fill the blank cells with. (In my case, I entered "Not Applicable".

  5. Click "OK".

Now your Pivot Table will no longer show the row with blank label, and it will not show in the Chart, as well; even if slicers are used and selections are changed.