1

  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.

John
  • 35
  • 1
  • 7
  • couldn't you simply filter out the blank row from the underlying pivot table ? – JLCH Mar 19 '19 at 09:06
  • 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. – John Mar 19 '19 at 09:10
  • I updated my Question just now. – John Mar 19 '19 at 09:17
  • 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 Mar 19 '19 at 09:20
  • 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? – John Mar 19 '19 at 09:49
  • Have you tried formatting to "hide" zero values ? https://stackoverflow.com/questions/10705159/excel-2010-hide-0-values-in-pivot – JLCH Mar 19 '19 at 12:01
  • The row that I want to hide has no values at all - they are really blank not zeros. So formatting does not affect the pivottable. Nevertheless I tried it just now and it does not work. Any further ideas? Thanks – John Mar 19 '19 at 14:38
  • Pivot will not show 'no values' line, unless you told it to. got to your row labels (year) -> in the field setting -> layout & print -> uncheck "show item with no data" – JLCH Mar 19 '19 at 15:15
  • This is already done. Please see my post 2nd scenario. – John Mar 19 '19 at 16:06
  • Upvoted because I am experiencing a similar issue. Any resolution? – macsmith Dec 03 '20 at 19:03

1 Answers1

0

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.

David Buck
  • 3,752
  • 35
  • 31
  • 35
Bandar Fakeeha
  • 1
  • I don't think this works, based on how I interpret the problem. There are no blank rows in the source table for the column added in the Pivot Rows field. The pivot table uses a calculated field to do the YoY calculation and therefore it does not exist in the source table. – macsmith Dec 03 '20 at 19:02