I would like to create a pivot table with a summary of some sales orders. The sales orders include lists of hardware and software license items. The software licenses have "end dates" as they are licenses for a duration. I would like the finished table to report the numbers of each product sold for each purchase order and, for licenses only the "average" end-date. I have columns set for "hardware" and "software" so that it is easy to tell which product is in which category (product names are somewhat challenging to interpret). Original data and Pivot images attached.

Question: how can I only display the "Average of End Date" summary for the Software category, as it is not relevant to the "Hardware" category - i.e. I don't want the third column in the shown pivot table to display at all

I would like to create a pivot table with a summary of some sales orders. The sales orders include lists of hardware and software license items. The software licenses have "end dates" as they are licenses for a duration. I would like the finished table to report the numbers of each product sold for each purchase order and, for licenses only the "average" end-date. I have columns set for "hardware" and "software" so that it is easy to tell which product is in which category (product names are somewhat challenging to interpret). Original data and Pivot images attached.

Question: how can I only display the "Average of End Date" summary for the Software category, as it is not relevant to the "Hardware" category - i.e. I don't want the third column in the shown pivot table to display at all

• excel
• pivot-table

1 Answer 1

You can't. Every element in the Values well will get assigned its matching column and you can't dynamically suppress it.

Consider

• hiding the whole Excel column after a pivot table refresh
• writing VBA that runs after pivot table refresh to automate hiding the undesired column(s)