none
How to fill in missing numbers in a Pivot Chart RRS feed

  • Question

  • Let's say I have a simple Table with 2 columns ... the first column allows a user to enter week number (1-52), and the second column allows the user to enter some data ... imagine every time the user eats a piece of fruit (apple, orange, banana).

    So, we end up with something like this:

    Week / Fruit

    1 / Apple

    1 / Orange

    2 / Banana

    2 / Apple

    2 / Apple

    2 / Orange

    4 / Banana

    It is now very easy to create a pivot table from this table to give a summary of total number of each type of fruit that has been eaten each week.

    However, in my above example, you can see the user didn't eat any fruit in week 3.

    How can I get the pivot table to include a row for week 3?

    Note - I realize I can always manually add a dummy entry in the table listing the missing week number without any fruit:

    3 /

    However, I don't really I want to do this (my real tables are much bigger and more complicated, and updated by many users, and so finding 'missing' rows will be quite hard).

    Tuesday, October 22, 2019 2:03 PM

Answers

  • Hi,

    Pivot Tables in Excel based on the fields values.

    The only way to do this is to complete the empty weeks. There are several ways to do it one option is to join this table with weeks table by Power query


    Guy Zommer

    • Marked as answer by JimGWhite Wednesday, October 23, 2019 1:04 PM
    Wednesday, October 23, 2019 12:55 PM

All replies

  • Hi,

    Pivot Tables in Excel based on the fields values.

    The only way to do this is to complete the empty weeks. There are several ways to do it one option is to join this table with weeks table by Power query


    Guy Zommer

    • Marked as answer by JimGWhite Wednesday, October 23, 2019 1:04 PM
    Wednesday, October 23, 2019 12:55 PM
  • OK, thanks. I thought that might be the case. I'm familiar with Power Query, so I could do that.
    Wednesday, October 23, 2019 1:04 PM