12

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.

3 Answers 3

11

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.

3
  • 1
    I used number 2 and it did exactly what I wanted. Thank you very much.
    – GeoffDS
    Oct 17, 2014 at 18:03
  • For a small project, the quick workaround is 1), but I do not like the regular chart setup. Therefore, I see 2) as the main trick if you do not face a big project. Dec 15, 2023 at 9:37
  • In a big project, I once had to make a lot of such charts from one big pivot chart. Then program this in VBA: change the pivot table "on the run" with VBA and then either make a pivot chart from that and copy that chart without links to a new sheet (what I did). Or: make a regular chart from it (but that would make more work to catch the full table size and VBA and change the design of the chart). Another VBA trick is under 4), to copy the pivot table only with its needed columns as values into a new sheet, with a linked chart on it. Not good if you have a big dataset. Dec 15, 2023 at 9:37
3

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.

4
  • 1
    @Ramhound edited the answer now to provide more information
    – s-heins
    Apr 22, 2016 at 11:44
  • Thank you for your input, this is actually the only answer.
    – dan
    Jun 10, 2016 at 13:50
  • @dnLL glad I could help!
    – s-heins
    Jun 14, 2016 at 14:42
  • Downvote. The question is about choosing only one of two columns, not about refreshing the rows. Even if you have a nice workaround for that. Another workaround would be to just make the table 1000 rows instead of 100, and you will have plenty of rows to fill and need to change the table size only once you reach the end. Dec 15, 2023 at 9:12
2

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.

1
  • This takes up the other answer that lists under 2) that you should "Hide the unwanted series in the pivot chart (no markers and lines, or no borders and fills)." Dec 15, 2023 at 9:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .