How To Create A Multiple Pie Chart In Excel
Sun 20th February 2011
Suppose we have a small table in Excel showing the number of drinks sold in three shops in a day. The table has four columns. The first column has the heading Drink and the words Juice, Water and Milk in separate cells under the heading. Then in separate cells to the right of the heading Drink we have Shop1, Shop2 and Shop3. Now add some figures for juice, water and milk for all three shops. Then add the label Total under the cell with the word Milk, and sum all the numbers for each shop. So your completed table is four columns by five rows, with the bottom row showing totals.
The first pie will show the totals for each shop. To create this first highlight the labels Drink, Shop1, Shop2 and Shop3. Then press the CTRL key and also highlight the four cells containing the totals, including the label Total. Then build a normal flat pie chart using the chart wizard and drag it into some free space under the table. Remove the legend in this pie and instead add data labels within the pie slices. Next size the overall pie shape so that it is square. Then select the actual pie part of the chart so that you see a squared zone immediately surrounding the pie slices. Size this as big as possible so that the pie takes up most of the chart space. That's the main pie finished, showing the total sales for each shop.
We're going to make three smaller pies for each Shop sales by drink type. The trick here is to create only one of the smaller pies, for Shop1, with the size and legend you want, and then copy this pie to create two more, and change the data source for the others to Shop2 and Shop3.
So create the Shop1 pie by first selecting the left hand column in the table, containing Item, Juice, Water and Milk and also the Shop1 column containing Shop1 and its three numbers. Then build the pie, normal flat type. This time keep the legend to the right of the pie, but again size the overall pie chart to be a square, and then increase the internal pie part as much as possible, just like you did earlier. That's the first smaller pie finished, showing the Juice, Water and Milk sales for Shop1. Now carefully position this pie next to the main pie, lined up with the Shop1 slice. You may need to adjust pie sizes in both pies until you're happy with how it looks.
Once the Shop1 pie is complete, copy it, or copy and paste it, to create a second pie. Carefully drag this second small pie into a clear space. With this second pie still selected you'll see that Excel helpfully highlights the data the pie is based on, in the original table. Being a copy of the Shop1 pie the data highlighted is the data in the Shop1 column. Very carefully drag the highlighted data one column to the right, so that the highlighted data is now Shop2. The pie will now show the data for Shop2. You can now position the Shop2 pie next to the main pie, lined up with the Shop2 slice. You may want to change the positioning to suit but don't resize the pie.
Now you again copy one of the smaller pies, change the data source of the resulting pie, and you will have a pie for Shop3. Position it next to the main pie, lined up with the Shop3 slice. Again you may want to change positioning to suit, to ensure all the smaller pies are lined up neatly with the main pie.
Once you're happy with the pies you could add some visual touches such as drawing lines between the edge of each small pie and the start and finish of each slice in the main pie. Then to combine all the pies (and any drawn lines) you need to group them. To do this, select all the pies and lines - you can do this using the SHIFT key. Then with all the items selected carefully right click over any of the selected items and choose Grouping, then group. Click into any blank cell away from the chart to finish. Now you have a single item, your multiple pie chat.
If you've found this article useful you might like to consider finding out more about Excel charts and its many other features by attending a training course to really boost your Excel skills.
Original article appears here:
Excel courses in London and UK wide.
London & UK
London's widest choice in
dates, venues, and prices
On-site / Closed company:
Very good trainer, clear, precise and to the point.
Watson Farley & Williams LLP
Andrew pitched the info perfectly and used good humour to keep us all alert and active. Job well done.
RAF Centre of Aviation Medicine
Dreamweaver 8 Intermediate
Communication to class was great. VERY HELPFULL.