Office hacks: Combine two to create a combination of Excel pie charts

We often insert charts in Excel to enhance the persuasive power of data, such as the use of pie charts to indicate the proportion of each data, etc.. However, traditional pie charts can only show one type of data, if you need to show multiple data in a pie chart at the same time, for example, a company’s sales share, now you need to show the share of both the region and the province where the region is located, you can create your own double-layer pie chart to achieve (Figure 1).

202001zhpt01

Figure 1 Example of a double-layer pie chart

The above example, the chart is composed of two pie charts inside and outside, the inner circle is the region, the outer circle is the region corresponding to the province. For similar combined charts, the corresponding charts are generated in Excel using the primary and secondary axes respectively. Therefore, the idea is to create separate province and region pie charts, and then they can be combined.

First of all, the production of the outer circle of the main coordinates of the pie chart, the production of this pie chart is very simple, in Excel, select the data in cells D2:D13 and F2:F13, and then click on the “Insert → Chart → Pie Chart. In order to facilitate the province and region correspondence, first right-click in the pie chart and select “Set the format of the data label”, in the right pane to check the “category name”, “value” and “Show guide lines” (Figure 2).

202001zhpt02

Then double-click any province, switch to “Chart Tools → Shape Fill”, according to each province corresponding to the region of the sector filled with the same color, a total of three colors to fill the chart, respectively, corresponding to three regions (Figure 3).

202001zhpt03

Figure 3 Fill color according to the region

Next, create a regional pie chart by selecting the above pie chart cut and clicking “Chart Tools→Design→Select Data” in the menu bar, click “Add” in the opened window, select “=Sheet1!$A$2:$A$13” for the series name and “=Sheet1!$B$2:$B$13” for the series value, click “OK” to add the regional data values. 2:$A$13″, the value of the series select “=Sheet1!$B$2:$B$13”, click “OK” to add the regional data values to the pie chart (Figure 4).

202001zhpt04

Figure 4 Adding data

Because the primary and secondary axes are not set here, the two pie charts are overlapping, so the next step is to separate the two pie charts. Right-click on the pie chart and select “Change icon type”, then switch to “All charts → Combined charts” in the window that opens, you can see the new combined pie chart mentioned above, select the region in the series name, check the checkbox of its secondary axis, and set it as secondary axis Pie chart, so that the regional pie chart can be distinguished from the original province pie chart (Figure 5).

202001zhpt05

Figure 5 Change the chart

Return to Excel, select the regional pie chart, right-click and select “Set Data Series Format”, set the pie chart separation to 96% in the right pane, and fill the pie chart color to red, so that it is easy to distinguish the primary and secondary pie charts by color (Figure 6).

202001zhpt06

Figure 6 Regional pie chart settings

Next, combine the pie chart into the inner circle of the province pie chart, double-click the “South China” part of the separated regional pie chart (only single selection), right-click and select “Set Data Point Format”, and set the “Point Separation” percentage in the right pane to “Point Separation In the right pane, set the percentage of “Point Separation” to “0”, as above, and set the format of other regional data points to “0”, so that the regional pie chart will be automatically assembled in the center of the province pie chart. As above, select the independent pie chart parts in turn and fill them according to the corresponding province color (Figure 7).

202001zhpt07

Figure 7 Formatting data points

Finally, beautify the pie chart. Right-click on the regional pie chart to add regional markers and values, and fill the outlines of different regions with different colors, bold the outlines, set the appropriate background image, and remove other unnecessary elements, so that a combined pie chart is completed. From the combination of the map can be very intuitive to see the largest sales in South China, of which Guangxi Province sales are the largest among the provinces (Figure 8).

202001zhpt08

Figure 8 final effect

Skill extension.

Although combination charts are generally combined with the help of primary and secondary coordinates, but if it is just a simple display of data, we can also use manual methods to combine the two charts. The above combination of charts is actually a superposition of two pie charts, so we can also insert the above two independent pie charts in Excel first, as above, now the pie chart sectors in accordance with different colors for filling, and then select the regional pie chart right-click to select “Set Chart Area Format”, the background color is set to “No fill”. Continue to right-click the chart and select “Place at Top → Place at Top” to display it at the top (Figure 9).

202001zhpt09

Figure 9: Chart background set to no fill and displayed at the top

Move the unfilled regional pie chart to the top of the province pie chart, and at the same time, according to the regional area, overlap the edge of one of its sectors with the edge of the province pie chart sector, then resize the regional pie chart so that the other side of it also overlaps with the edge of the corresponding province sector, and finally beautify it as above. Using the same method, we can combine any charts together (Figure 10). However, although this manual combination method is simple, it is only necessary to realign the data after each adjustment.

202001zhpt10

Figure 10 Manual combination effect display

QQ图片20210420165237

Leave a Comment