Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Tacoma, Washington, USA
    Posts
    431
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Adding Empty Cells to Pivot Table (2000)

    Each month I create a report in Excel to calutate the number of new cases opened in each of about two dozen referral types. For example, in January files were opened under six referral types. No files were opened in the other referral types. The table only shows data for the six but not the others that had nothing in the month of January. Is there a way I can setup the Pivot table to include the empty columns?

    Here's an example of what I have now:

    <table border=1><tr><td>
    Daisy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding Empty Cells to Pivot Table (2000)

    A pivot table can only display existing data, not non-existing data. You could add rows for each referral type without other data; that would add the "missing" columns but also a row "(blank)". You can then right-click on the "(blank)" and select Hide from the popup menu. Next, right-click on one of the referral types and select Field Settings from the popup menu. Tick the check box "Show items with no data", then click OK.

    Alternatively, you can set up a table with all row and column headings you want, and use the SUMPRODUCT function to calculate the numbers.

    See attached workbook.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Tacoma, Washington, USA
    Posts
    431
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Adding Empty Cells to Pivot Table (2000)

    Right-clicking on the Referral Codes header and going to Field Settings to check the box "Show more items with no data" did the trick. I don't use this feature enough to stay familiar with it. Thank you SO much!
    Daisy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •