Have Pivot Chart show only some columns in pivot table

Question

I have a data table. In the table I have some dollar amounts. I also have formulas that use lookups to group these dollar amounts into groups in the data itself.

I then have a pivot table where my row field is the dollar group. And, in the values I show both the percent of rows in each group and also the number of rows in each group.

That is, for one I use "Show values as" "% of total" and for the other I use "Normal".

Now, I have a pivot chart based on this pivot table but I don't want it showing both of these columns, as they're essentially the same thing twice (though the one that does a count has a much higher scale). But, for some reason, I can't figure out how to get the pivot chart to only show one column. Is there a way?

I am open to any fix that, in the end, leaves only one column showing in the pivot chart but both in the pivot table.

Answer 1

A pivot chart shows all data in the pivot table. You have a few ways to not show data from a pivot table:

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.

Answer 2

Another option, and the one I ultimately picked, is to define names for the columns in the pivot table. Thus, the length will update automatically with the pivot table.

It is done by defining names per column by typing the below code into the "refers to"-box under "define name" (assuming the information you want to include is in column A, and the header is in A1)

=OFFSET($A$2,0,0,COUNTA($A:$A)-1)

Define names for all series and the axis labels you want included in your chart. Then, type these names in the chart as the series values for the series or axis label range, respectively.

The process of how to do this is described here in more detail.

Thus, the name definition will expand or shrink when you refresh the pivot table and the chart will refresh itself with it, now accommodating the correct number of rows.

Answer 3

It doesn't look like this topic has been addressed recently on forums, but a workaround I found was to format the unwanted series in the pivot chart to 1) have 100% overlap, 2) no fill, and 3) no outline. That resulted in the appearance of removing the unwanted series while still maintaining the data link. I'm using excel 2013.