Combining Column Values in an Excel Pivot Table

Question

In order to simplify a stacked bar chart, I am looking to sum up the counts of multiple columns I have in my pivot table. For example, in this sample table, I would like to combine Fruits and Vegetables into one column, so that each bar will comprised of three colors: one for Meats, one for Grains, and one for Fruits+Vegetables.

Pivot Table and Stacked Chart

For reference, this is the data source:

Data Source table

I tried adding a Calculated Field, but I'm not sure if I can use specific values from a column in the Formulas field. Is there a way to create something like this from a pivot table, or should I be taking a different approach to this all together?

Answer 1

I'm using Excel 2016, -- pretty sure it's the same with older versions, but the premise is the same.

In your pivot table, Select the Pivot Table Tools> Analyze tab, then "Fields, Items",then pull down to"Calculated fields". Enter a name for the generated field, and the formula you want to use:

In my example, I added the fields Fruit and Vegi's from my available pivot table fields (which is based on my data table).

Then select the calculated field as you would any other..

and create the chart as usual.

Hope this helps.