Office hacks: truncated charts

When using bar charts to represent data, if the largest data and the smallest data between the disparity is too large, the resulting chart is very unattractive. If you make a truncated chart, artificially “truncate” the high value part, you can take into account the contrast and aesthetics, and make a visually balanced chart.

Let’s take a comparison chart of the completion of a task by a Liang Shan soldier as an example. First, add the auxiliary data needed to construct the stacked bar chart based on the original data. The “Simulated grid lines” column in the table is used to build virtual data axes in the chart, and the “Truncated data points” column is used to display the fracture diagram in the chart (Figure 1).


After the data is constructed, select the A1:A16 and C1:E16 data areas and insert a stacked bar chart. Delete the People tab in the chart, right-click the X-axis, select “Set Axis Format”, and set the minimum value of the numeric axis to -40 and the maximum value to 100 in “Axis Options→Axis Options”. Select each series in the chart and set them to have no lines. Add a series of simulated grid lines, select the data in the simulated grid line column (i.e. G2:H9), copy it, select the chart, click “Paste→Selective Paste”, check “Category (X label) in the first column” in the pop-up window, OK.

Right-click the newly added series in the chart, select “Change Chart Type”, and change it to XY scatter chart. Select the scatter chart, click “Chart Tools → Design → Add Chart Elements → Axis → Secondary Horizontal Axis”, set the minimum value of the secondary axis to -40, the maximum value is set to 100; set the maximum value of the secondary Y axis to 15, the minimum value of 0, and then delete the number of value axis. Select the data in the truncated data points column (i.e. J2:K3), copy, select the chart, click “Paste → Selective Paste”, in the pop-up window, check the “first column in the category (X label)”, OK. As shown in Figure 1.

Next, respectively, set the series of people and data for no border without fill, and add people, percentage labels. Select the characters, data labels, respectively, in the “Label Options” tab, remove the “value”, “Show guide line” check box, check the ” value in the cell”, select A2:A16 and C2:C16, respectively, set the data label for the white fill, solid line; delete the value axis and grid lines; select the analog grid line series in the chart, click “Chart Tools → Design → Add Chart Elements → Error Line → Other Error Line Options” In the right window, select “Negative Deviation”, the amount of error at the choice of “Custom”, the negative error in the pop-up window, select the value in cell G9 (Figure 2).


Select the series of simulated grid lines in the chart, set its marker to no fill, no lines, add a value label, select “Up” for the position, in the “Label Options” tab, remove “Value”, “Show guide lines”, check “Value in cell”, select G2:G9. In the “Label Options” tab, remove the checkboxes for “Value” and “Show guide lines”, check the box for “Value in cell”, and select G2:G9. Draw a truncated chart using the self-selected chart, set its fill color to match the background color of the chart, copy it, select the truncated data point series in the chart, and paste it. Select the truncated series of data points in the chart and paste it. Select the truncated data point series, click the “Label” tab, select “Built-in”, size 12; add the data label, right, and select the corresponding cell value (Figure 3).


Finally, add the necessary descriptions using the rectangles in the self-selected graphics, then embellish the data labels and delete the legend and you’re done.

Leave a Comment