Share via

Excel 2021 Pivot Table "Show Items with no data on rows" is greyed out

Anonymous
2022-05-02T23:38:16+00:00

Hi

I am creating a report where I am using two different pivot tables based on the same data but with different filters applied to each of the two pivot tables. This is creating tables which are not the same dimensions. Both tables are showing day on the column and year on the rows. After the filter is applied, table 1 may have all years showing but table 2 has 2017 and 2019 missing. This is because there is no data for years 2017 and 2019 after the filter is applied for table 2. This is expected. However, when I want to do a calculation that involves both tables, I cannot easily match up the cell entries because table 2 is missing years (2017 and 2019). I need table 2 to show years 2017 and 2019 even if all the values are zero. I thought that the "Show Items with no data on rows" option would help but it is greyed out. How do I make it available so I can test my theory that it works or not?

Thank you

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

Answer accepted by question author

  1. Anonymous
    2022-05-03T03:31:20+00:00

    Hi Manny213!

    I'm Jen, and I'd be happy to help you out with this issue.

    Please go to Pivot Table Options > Display > Select 'Show items with no data'. If this option is greyed out, you may check this article for work-around.

    https://www.myonlinetraininghub.com/show-items-...

    Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.

    I hope this information helps. If you have any questions, please let us know and we'll be glad to assist you further.

    Best Regards!

    Jen :)

    9 people found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Anonymous
    2022-05-08T01:44:20+00:00

    thank you! that worked!

    1 person found this answer helpful.
    0 comments No comments