Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Pivot table - getting all possible column headings

    Dear loungers,

    My pivot table uses a status column to create the column headings. This status column is driven by a drop-down based on a list. I want the pivot tabel to have all possible status values even though they may not occur in the status column.

    Can I force this to happen?

    thank you.......................... liz

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If the values do not appear anywhere in the source data they will not be in the pivot table. But if you have the table set to retain old values, then you can simply add some dummy rows to the data source so that all items are available, refresh the pivot and then delete the new rows and refresh again. If you then set the field to display items with no data, you should get the effect you want, I think.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thank you Rory.

    However, my sheet has two pivot tables, they work from the same data just showing slightly different summaries. The Column heading will be identical.
    So, following your idea I had some rows with the status set so that all status values were used, the pivot tables showed all values, perfect! After I deleted the rows with the values the first table still shows all status values in the column headings but the second only shows those used. I used Alt F5 to update them. I can't see any settings that are different for each table other than the row labels.

    Just as an aside the second table also loses one of the vertical grid lines every time where the other table doesn't. And generally it doesn't seem possible to make any formatting like column widths stick... or is it?

    Thank you..................... liz

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Which version of Excel?
    Any chance you can post the workbook?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Excel 2010

    here's the sheet, nothing confidential in it the Summary tab is the problem and it's working off the Config progress tab

    Thank you...................... liz
    Attached Files Attached Files

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Right-click the column header field in the second pivot, choose Field Settings, and then on the Layout and Print tab, check the 'Show items with no data' option.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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