Office hacks: Excel in contrast to highlight chart data

When we use the chart in Excel to display data, if you need to highlight the way to compare the display of certain data, you generally need to use dynamic charting technology. With the help of checkboxes, we can achieve the dynamic highlighting of data in the chart contrast effect (Figure 1).


First, select the “Development Tools” tab (if you don’t have this tab, you need to click “File→Options” and check “Development Tools” in the Customize ribbon), click “Control→Insert→Checkbox (Form Control)”, drag out a checkbox in the data source worksheet. Right-click the check box, select “Set control format”, in the pop-up window of the “Control” tab, “Value”, select “Not selected “, the cell link to select cell B4. Similarly, insert 11 more such check boxes, the value is “not selected”, the cells are linked in turn to C4, D4, E4 ……, the corresponding month corresponding to the fourth row of cells. The 12 checkboxes are arranged horizontally in order (Figure 2).


Then, in cell B3, enter the formula “= IF (B4 = TRUE, B2, NA ())”, and then fill to the right.

Tip: the formula “TRUE” is generated according to the corresponding check box, if the cell link to the B4 check box is checked, then the value of cell B4 has become “TRUE”, otherwise it is ” FALSE”. The formula means that if the value of cell B4 is TRUE, then the value of cell B3 is equal to the value of cell B2, otherwise it is “NA ()”, NA () = # N / A, can not get a valid value. In the chart the empty value will be displayed as 0. So, here we used the # N / A error value instead, so that in the chart will not be displayed.

Next, select B1:M3, select the “Insert” tab, and then click “Chart → Line Chart” to insert a chart in the worksheet. Right-click the chart, select “Change Chart Type”, in the pop-up window, select “Combination”, select “Line Chart” at Series 1, Series 2, select Select “Line Chart with Data Markers” for Series 1 and “Line Chart with Data Markers” for Series 2. Check a few random checkboxes first, and then the line chart with data points will be displayed. Right-click the line graph, select “Set Data Series Format”, in the “Series Options → Fill Lines” tab, the line is set to “no lines”, that is, so that the line between the points are not displayed In the “Series Options→Fill Lines” tab, set the line to “No Line”, i.e., the line between the points is not displayed. Then in the “Marker” tab, expand the “Data Marker Options”, select “Built-in”, set the size and fill and border color (Figure 3).


Finally, delete the legend, grid lines, vertical axes, and add data labels. Set the chart background and title as needed. Select the drawing area of the chart and move the chart up a bit. Select all checkboxes, place them all on the top level, and drag and drop them under the chart X category axis (month) so that the checkboxes are aligned with the corresponding month. Then select the chart and checkboxes all together and combine them. Thus, the effect of using checkboxes to highlight the comparison of chart data is complete.


Leave a Comment