Excel – Hiding a column from a pivot table without removing it from the chart

chartsmicrosoft excelpivot table

I have a pivot table with two columns: number of users who visited a website (impressions) and number of users who registered on the site (regs). The rows are for dates.
I want to visualize the percentage of users who registered after visiting the site. Thus, I have the number of users for each cell as a value field, displaying it as percentage of impressions. Generating a pivot chart from the table, impressions and regs are plotted over date as a percentage of impressions. This means there is one line at 100% for impressions (always 100% of itself) and the graph for registrations below that.
I'd like to remove the line for impressions, but when I set a filter to do so, registrations vanish as well, since the column for impressions is filtered from the pivot chart as well, turning the value field invalid.
How can I just show registrations as a percentage of impressions in the chart?

Best Answer

I think your title has what you want backwards. Your description sounds like you want to hide data from the chart but not from the table.

You could:

  1. Make a regular chart from the pivot table data, which includes only some of the pivot data. Updating the chart will fail if the pivot table changes size.

  2. Hide the unwanted series in the pivot chart (no markers and lines, or no borders and fills).

  3. Exclude the unwanted data from the pivot table.

  4. Make a second pivot table with just the data you want to show in the chart.

Related Question