Office hacks: Excel dynamic charts that point to change

Excel in the conventional charts are often static, generally in the selection of the corresponding data automatically generated after the presentation is fixed. But encounter more changes in the data, such as a company’s quarterly production plan, the analysis needs to compare a month or months of the actual task accounted for, when the conventional static charts are difficult to perform, in this case we may wish to try dynamic charts.

First of all, enter the original data in Excel, select column D as an auxiliary column, and then in cell D2:D5 filled into the word True, it is selected as the option to determine the value (Figure 1).

201824jdjb01

Figure 1 Auxiliary column

insert a column in front of the original data, select A2, click “Development Tools → Controls → Insert → Checkbox (form control)”, insert a checkbox in A2 (form control), double-click the inserted control, in turn, the original “checkbox” word delete To retain only the check box, and then drop down the fill, the check box will be filled in turn to A3: A5 cells (Figure 2).

201824jdjb02

Figure 2 Add checkbox

Right-click the checkbox A2 and select “Set control format”, the value is set to “Selected” (that is, the checkbox is selected when its value is True), click the link to the cell and select “$D$2 “, that is, the selection of the check box and the value of the corresponding cell in column D corresponds to. Operation as above in turn on cell A3:A5 controls to do the same settings (Figure 3).

201824jdjb03

Figure 3 Set the control format

Return to Excel, at this time, if we select the check box for the cell, you can see the selected / unchecked, the corresponding cell value of column D will change to True / Flase, so that with the insertion of the check box we can achieve the flexible selection of the project yield value (Figure 4).

201824jdjb04

Figure 4 Checkbox selected/unselected changes

With the checkboxes we can achieve a flexible selection of items, because the final selection needs to be reflected in the dynamic chart changes, so below we have to involve the selected data in the actual summation. In order to sum the selected data, here we can use the SUM array function to achieve. Position to cell C6, enter “= SUM (C2: C5 * D2: D5) / B6 * 100%”, and then press Ctrl + Shift + Enter to fill the array formula (Figure 5).

201824jdjb05

Figure 5 array formula

Tip: Here the use of the concept of array formula will be the original quarterly number of summation and D2:D5 judgment value associated together, when the check cell in the selected state, the value of column D cells is True, then the value corresponding to column C will participate in the summation, otherwise displayed as Flase, the value corresponding to column C will not participate in the summation, so as to achieve a flexible selection of values.

In order to facilitate the identification of the selected chart data, here you can use the conditional formatting of the selected cell color settings to fill the display. selected A2: D2, click “Start → Conditional Format → New Format Rules”, in the open New Rules window, select “use the formula to determine the format of the cell to be set”, in the formula bar, enter “$ $ D $ 2 ” (Figure 6).

201824jdjb06

Figure 6 Conditional formatting

Continue to click “Format → Fill”, the eligible format will be filled with blue (that is, and the chart has been completed in the same color data). So that when we check the checkbox mark in the data column, the selected cell will automatically fill the corresponding color (Figure 7).

201824jdjb07

Figure 7 conditional formatting settings

Now position to D6, enter the formula “=1-C6”, select C6:D6 data area, click “Insert→Chart→Circle Chart”, insert a circle chart. Select the inserted chart, right-click and select “Set Chart Area Format”, set the fill method to “None”. Continue to select “Set Data Series Format”, the inner diameter of the circle size set to 14%, so that we can more visually view the percentage of the chart (Figure 8).

201824jdjb08

Figure 8 Set data series format

Right-click the chart and select “Add Data Label”. After adding the completed data labels, change the corresponding data labels to “Completed+Value” and “Incomplete+Value” in turn, and Set the incomplete font alert to eye-catching red. Finally, the chart title, series markers and other irrelevant content are deleted, select column D, the words “auxiliary column” to delete, and then the column size will be reduced, the auxiliary column hidden display (Figure 9)

201824jdjb09

Figure 9 Adding data labels

You can see that this circle chart is actually composed of C6 (selected actual production value / planned production percentage) and the rest of the numbers (1 – percentage), so when we select the data to be counted in the original data column, the chart will undergo dynamic changes. For example, it is now possible to select any data for 1 or several quarters and to see the chart change very visually after selection (Figure 10).

201824jdjb10

Figure 10 Chart changes after selection

You can see that by adding the checkbox control and setting the TRUE/Flase values, we can achieve the display and non-display of the specified data, and you can make more charts to meet your needs. For example, you can use a bar chart and use the TRUE/Flase values to display/not display the superimposed data, so that the dynamic chart can be more convenient to compare the data you choose.

Leave a Comment