5

Suppose I have a spread sheet that looks something like this...

Excel Data

Now, I'd like to create a column chart that has 3 series, one for each country. Then, I want series for each category, but I want to plot the total, not each individual order total. So, something like this (excuse the horrible artwork)...

Excel Chart

The data label placement isn't all the important, the key is that for each Category (Bikes and Clothes) I chart the total for each country, not individual values from the "Order Total" column.

Is this possible? Is it possible to do the same idea, but to switch Country and Category around?

Improve this question
1
  • // , Thanks for the question! I'm still learning the use cases for Pivot Tables, so I think it's relevant to the tags you used. Maybe consider adding the tag "pivot-tables"?
    – Nathan Basanese
    Feb 8, 2016 at 22:50

2 Answers 2

Reset to default
7

Pivot tables and pivot charts are perfect for this purpose. They'll let you summarise your data into a table and chart and to easily reorganise both of them.

Improve this answer
3
  • Yes, but i'd really like a regular chart unfortunately.
    – Chaulky
    Jan 6, 2011 at 0:55
  • 1
    @Chaulky: I don't think it can be done any other way. If there is an alterative you'll probably find it to be so complicated as to outweigh any disadvantages you perceive pivot charts having over normal charts. Hopefully some can enlighten us both. :)
    – Mike Fitzpatrick
    Jan 6, 2011 at 1:14
  • You can make a regular chart from a pivot table. Start by selecting a blank cell that doesn't touch any data and insert the chart, then use the Edit Data dialog to add the series one at a time. See Making Regular Charts from Pivot Tables,
    – Jon Peltier
    Jun 7, 2016 at 12:05
0

You may want an array formula (I found it by looking for "double sumif()") which essentially lets you specify more than one criteria for the IF part.

See this page for more information: http://www.cpearson.com/excel/ArrayFormulas.aspx

Improve this answer

You must log in to answer this question.

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