Office hacks: Product comparison is more intuitive Make a combination of separation and stacking

In order to facilitate the display of multiple product data and its trend, we often use the “stacked bar chart + line chart” in Excel combination chart. However, this way each product data and line are stacked together, the chart does not look very beautiful, the visualization effect is also poor. In this regard, we can separate the product data and through the method of auxiliary data, to create a more image and better visualization of the combination of stacked charts (Figure 1). The following I will introduce the operation in Excel 2019 as an example.

Fig. 1 Comparison of traditional chart (left) and separated combination chart (right)

The analysis of the split combination chart shows that it consists of a “stacked bar chart + scatter chart with smoothed lines and data markers”. However, the stacked bar chart differs from the traditional one in that it is a combination of segmented product charts (where the bottom of each product is aligned), as well as the “scatter chart with smoothed lines and data markers”, which corresponds to the segmented bar chart (starting coordinates are not the traditional ones from zero).

Let’s start by creating a segmented product stack bar chart. In order to achieve the alignment of the bottom of each product, you need to add auxiliary data here, and then set the auxiliary data of the bar chart to “no fill” can be. Position to cell B9 and enter the formula “= B3”, and then fill the formula to the right, the data in B3:G3 to fill the ninth row. Continue to locate cell B10, enter the formula “=MAX(\$B\$3:\$G\$5)*2-B3”, the same as above to fill the formula to the right. Next, enter the formula in cell B11 “= B4”, cell B12 enter the formula “= MAX (\$B\$3: \$G\$5)*2-B4”, cell B13 enter the formula “= B5 “, cell B14, enter the formula “=MAX (\$B\$3:\$G\$5)*2-B5”, and finally fill the formula to the right to complete the data collation (Figure 2).

Figure 2 Organizing data

Formula “MAX (\$B\$3:\$G\$5)*2-B3” explanation: first use the MAX function in the “B3:G5” to find the maximum value (this example is “70”, that is G3 value), and then multiply it by 2 (the function of the multiplier is to separate the stack of products, you can set it to any value, the larger the value, the larger the interval), and then finally subtract this value and B3, so as to ensure that the data in rows 9 and 10 always add up to 140 (rows 11 and 12, 13 and 14 are similar). This way, when subsequently using the data in rows 9 and 10, 11 and 12, and 13 and 14 to create stacked plots, the lengths of all stacked plots remain the same, ensuring that they can achieve bottom-aligned results.

Select the data in A9:G14, click “Insert→Chart→Bar Chart→Stacked Bar Chart”, so you can get a stacked bar chart of equal length. Select the A, B, C auxiliary column chart as prompted, click the right mouse button and select “Set Data Series Format → Fill & Line → Fill → No Fill”, while “Column” is set to “No Line “, so that the actual data of each product bar chart to achieve bottom alignment (Figure 3).

Figure 3 Equal length alignment stacking diagram

The next step is to add a “scatter plot with smoothed lines and data markers” to the stacked bar chart above. Position to cell B15 and enter the formula “= B9 / SUM (\$ B9: \$ G9)”, and then fill the formula to the right, you can calculate the monthly sales of product A accounted for (the formula uses “\$” to indicate the absolute reference to the column), and so on, in turn Calculate the sales share of products B and C (Figure 4).

Figure 4 Calculating sales share

Next, click the right mouse button on the generated bar chart and select “Select Data”, in the window that opens, select cell A15 for “Series Name”, select “Y-axis Series” for the data in “B15:G15” in the data, “Chart Type” set to “Scatter Chart with smooth lines and data markers”, so that you can add a scatter chart on the stack of A products (Figure 5). Scatter Chart (Figure 5).

However, because we used the multiply by 2 setting when creating the auxiliary data, the scatter and stacked charts appear to be intermingled in their placement (different from the example in Figure 1), as does the scatter chart of the sales share of products B and C. In order to make the scatter chart can be placed on top of the stacked chart, here we need to add another auxiliary data. For example, if the starting percentage of product B is 140% and product C is 280%, locate cell A18, enter the formula “=A15*2”, then fill the formula to the right and enter the formula “=(B16*2)+1.4″ in cell A19 ” (because the starting percentage of 140%), cell A20, enter the formula “= (B17 * 2) + 2.8”, then fill the formula to the right (Figure 6).

Figure 6 Adding Sales Share Supporting Data

The following will just add the scatter chart of product A to delete, and then click the right mouse button on the bar chart and select “Select Data”, in the window that opens “Series Name” select cell A18, “Y-axis series “Select the data in “B18:G18”, and set the “Chart Type” to “Scatter Chart with Smoothed Lines and Data Markers”, so that Add a secondary data scatter chart to the stacked chart of product A. Similarly, add the auxiliary scatter charts of product B and C in turn. Then select the auxiliary scatter plot of product C sales ratio, click the right mouse button and select “Set Data Label Format”, check the “cell value” in the right pane, click “Select Range “, select “B17:G17” in the data, so that you can display the percentage of sales of product C as a percentage of the value, and then set the data labels of products B and A (Figure 7).

Figure 7 Setting data labels

After completing the above operations, you can finally beautify the chart by adding chart titles, removing grid lines, setting backgrounds, and removing axis displays (Figure 8).

Figure 8 Final effect

Firelink: The article “Split-order Bar Chart Do You Know How to Make” in Issue 13, 2020 of this publication introduces the use of 2D stacked bars to create a split-order display bar chart of multiple data, for readers with this need.