Office hacks: a chart to create an unequal-width bar chart

In our daily work, we often use multiple bar charts to represent some related data, such as sales and market share data. This bar chart can be seen through the comparison of different data, but the conventional bar charts are equal-width shape, and not very intuitive to see the differences in different data. Now we can create a better comparison effect in Excel by making unequal-width bar charts.

Knowledge: what is the unequal width bar chart

Unequal width bar chart as the name implies is the width of the bar chart, so that the two types of data associated through the width of the bar chart arranged together, can easily help us to simply and intuitively compare two different information, thus helping us to identify problems and solve them. For example, the data in the table below reflect the number of people and the amount of revenue generated per capita for each bank, respectively. If we simply insert the bar chart, we can see that after inserting the chart, the equal-width bar chart does not make a good distinction between the number of people and the amount of revenue generated for each bank (Figure 1).

201812bdk1

Figure 1 General chart

Using scatter charts to create unequal-width bar charts

By default, Excel generates equal-width bar charts. Therefore, to achieve unequal-width bar charts, we need to rearrange the original data and construct unequal-width bar charts by adding auxiliary data.

Take the above data as an example, enter X and Y values in F1 and G1 respectively, and enter the formula “=SUM($B$2:B2)” in F2 to represent the progressive value of sales, and use this value as the width of the unequal-width bar chart. Enter “=C2” in G2, this is the sales, and use this value as the height of the bar (Figure 2).

201812bdk2

Figure 2 Constructing data

Click “Insert → Scatter Plot”, select the first scatter shape, then right-click the scatter plot and select “Select Data”, click “Add”, select the above X, Y values of the data, respectively. Then fill the color of the scatter with red (Figure 3).

201812bdk3

Figure 3 Insert scatter plot

You can see that there are four scatter points, where the X-axis corresponds to the width (number of employees) and the Y-axis corresponds to the height (revenue per capita). Click “Insert→Shape”, insert a rectangle, and the right vertices of the rectangle correspond to the scatter points respectively. Proceed as above and insert three more rectangles, coinciding with the vertices of the scatter points and filling them with different colors to distinguish them. Finally, select the four inserted rectangles, click “Align → Bottom Align”, they will be aligned neatly (Figure 4).

201812bdk4

Figure 4 Scatter + Insert Rectangle

If the original X and Y axis data are small, you can also select the X and Y axis data in turn, right-click and select Set Axis Format to set the scale values to make the scale display more precise. After finishing the scale adjustment, you need to readjust the size and arrangement of the rectangle, and finally you can also beautify the chart, such as not showing the underline, inserting a text box, and adding data marker text to the rectangle. In this way, with the help of scatter + insert rectangles you can create an unequal-width bar chart, through which we can clearly see the comparison of each bank, for example, Bank D has the lowest number of people, but the highest per capita income generation. The width and height allow us to visualize the effect of the comparison of the two elements of the different banks (Figure 5).

201812bdk5

Figure 5 Final result

Take the local material, the conventional bar chart transformed into unequal width

The above is to insert a scatter chart + insert rectangular method to create unequal-width bar chart, if you need to use the bar chart directly, the original data can be slightly modified.

Confirm the total number of employees of banks A → D, such as the above example is 26 (* 100,000) people, positioned to A10 input 1, and then filled in order to A35 (that is, the same as the total number of employees of all banks), respectively, at B10 → B17 input 16 (that is, the corresponding per capita income generation figures of Bank A, filling the cell for 8). Similarly, fill in the corresponding numbers at C18, D19, E34 in turn to form a new data structure (Figure 6).

201812bdk6

Figure 6 Constructing data

Select the above four columns of data, click “Insert→Chart→Bar Chart”, follow the prompts to generate the above four groups of data bar chart, because the above data is staggered, you can see that each color of the bar chart corresponds to the data of different banks. The number of bars indicates the number of employees, while the height value indicates the value of revenue generated per capita (Figure 7).

201812bdk7

Figure 7 Insert bar chart

Because we only need one bar chart for each bank, select each group of bar data, right click “Set data series format”, set “Series overlap” to 100% in the series option, and “Category spacing ” is set to 0%, so that each color of the bar chart will automatically overlap together to form four different widths of the bar chart (Figure 8).

201812bdk8

Figure 8 Overlapping bar chart

Continue to beautify the overlapping bar chart, such as adjusting the fill color of the bars, the background fill color of the chart area and drawing area, setting the scale of the axes as well as the label display and position, adjusting the position of the legend, and adding text comments, so that by overlaying different color bars you can also create a bar chart of unequal width (Figure 9).

201812bdk9

Figure 9 bar chart of unequal width

As you can see, in Excel we can insert rectangles through the scatter plot + or directly insert multiple bar charts for overlap, you can get unequal-width bar charts, in practice we can choose which method is flexible according to the actual data structure to produce unequal-width bar charts.

Leave a Comment