Office hacks: deformation with bar charts to achieve special markers

Since Saturday and Sunday are legal holidays, the company wanted to make a special display in the bar chart table for the sales of these two days. If the conventional method is used to create the bar chart first, and then reset the series corresponding to Saturday and Sunday in the chart, it seems too much work. It is easier to achieve this by using auxiliary rows to participate in the creation of the chart.

First, you need to do a little sorting of the data source of the chart to identify the data corresponding to Saturday and Sunday. Select all the data corresponding to the date, select the “Start” tab, click “Conditional Format → New Rule”, in the pop-up window “Select Rule Type” select “Use the formula to determine the cells to be formatted”, in the “format for the value of this formula”, enter “= WEEKDAY (B1,1)>5”, the format set the font to red, bold. In this way, the data corresponding to Saturday and Sunday will be identified (Figure 1).

1816A-TSZX-1

Next, move the data corresponding to Saturday and Sunday to the auxiliary rows by dragging them with the mouse. Select all the data and choose the “Insert” tab to insert a bar chart (Figure 2).

1816A-TSZX-2

Finally, the bar chart can be trimmed as necessary to meet your needs.

Extended application.

The above operation is actually a deformation of the bar chart, and such a deformation will be used frequently in practice. For example, if you want to create a chart comparing the planned daily production with the actual daily production, the part above the plan is shown in green and the part below the plan is shown in red. Similarly, the data used in the chart need to be processed, adding four auxiliary lines of data, the first auxiliary line “higher”, enter the formula “=IF(B3>B2,B3-B2,””)”, the second auxiliary line “above 2”, enter the formula “=IF(B3>=B2,B2,””)”, the third auxiliary line “below”, enter the formula ” =IF(B3>B2,””,B2-B3)”, the fourth auxiliary line “below 2”, enter the formula “=IF(B3<B2,B3,””)”, the unity are filled to the right to the desired location (Figure 3).

1816A-TSZX-3

Next, select A1:AF2, A4:AF7 data area, insert a stacked bar chart, right-click the chart, select “Select Data”, in the left-hand side of the legend item will be higher than 2, lower than 2 move to the top, and then right-click the chart, select “Change Chart Type “In the pop-up window, select “Combination”, change the chart type corresponding to “Plan” to line chart, and the rest are stacked bar charts. Select the series corresponding to “higher than 2” and “lower than 2” in the chart, set their no fill, no lines; select the series corresponding to “higher” in the chart, set Select the series corresponding to “higher” in the chart and set the fill color to green, select the series corresponding to “lower” and set the fill color to red. Finally, delete the legends corresponding to 2 above and 2 below (Figure 4).

1816A-TSZX-4

Leave a Comment