Office hacks: Use the data switch card to display dynamic charts

This is an ideal way to present dynamic data in a way that is not only easy to use in practice, but also intuitive and clear in presentation (Figure 1). To achieve this effect, if you do not want to use controls or code, using a slicer is a good choice. There is no additional setup required to use the slicer, just check the desired items and drag and drop them into the page.

1819A-QHK-1

1. Construct dynamic data labels from the data

First, the original data used in the chart needs to be processed. In the original worksheet A10 position to enter “¥ amount”, A11 position to enter “= A1”, select A11, drag to the right to fill to G11, select A11: G11, drag down to fill to A16: G16, so that the construction of the The first data label needed for the chart shown in Figure 1.

To construct the second data label, enter “% share” at A18, “=A1” at A19, select A19, drag the fill to the right to G19, drag the fill down to A24, and enter “=TEXT(IF)” at B20. “=TEXT(IFERROR(B2/B$6,0), “0.0%”)”, fill down and to the right to B24:G24.

Similarly, enter “% month change” in A26, enter “=A1” in A27, fill to the right to G27, fill down to A32, enter “=TEXT(IFERROR((C2 -B2)/B2,0), “0.0%”)”, fill down and right to B32:G32, and the third data label can be constructed (Figure 2).

1819A-QHK-2

2. Insert the slicer and associated with the label

In Sheet2 worksheet A1:A4 enter “label”, “$ amount”, “% share”, “% month change “, in B1:B4, respectively, enter “index number”, “1”, “2”, “3 “, this is done to use the data in A2:A4 as the label of the switch button.

Select A1:B4, switch to the “Insert” tab, click “Pivot Table”, and select “Existing Work” in the pop-up window “Select a location to place the pivot table”. Select “Existing Work” and tap F1; after confirming, drag and drop the “Label” and “Index Number” fields to the row interval in the right pivot table fields window; click the “Label” and “Index Number” fields in the row window respectively. The right arrow of the “Label” and “Index” fields, select “Field Settings”, in the pop-up window “Category Summary and Filter ” tab, select “None” at the category summary, and in the “Layout and Print” tab, select “Show item labels in table form” at the layout. “. Right-click “Total” in the pivot table, select “Delete Total”; select the inserted pivot table, in the “Pivot Table Tools → Analysis” tab, click “Insert Slicer”, in the pop-up window to check the “tab” (Figure 3).

1819A-QHK-3

Sheet1 worksheet B34, enter “=Sheet2!$G$2”, so that cell B2 will get the index value of the selected label. In the A35 position, enter “=A1”, select A35, drag to the right to fill to G35, down to fill to A40, at B36, enter “=CHOOSE($B$34,B12,B20,B28)”, down and to the right This way, the corresponding data labels can be generated according to the slicer selection (Figure 4).

1819A-QHK-4

Next, select A1:G5, insert the stacked bar chart, add the number of labels, select the chart “North China” corresponding to the number of labels, right-click, select “Set Data Label Format”, in the right-hand side of the Set Data Label Format window “Label Options” tab, check the “value in the cell”, select the B39:G39 cell area, do not check the “value”. Similarly, select the chart “South China” corresponding to the digital label, set its label to the corresponding B38: G38 cell area, do not check the “value”, and so on for other settings (Figure 5).

1819A-QHK-5

3. Slicer settings and the beautification of the chart

Switch to Sheet2 worksheet, right-click the slicer, select “Slicer Settings”, and remove the check mark in front of “Show Header” in the pop-up window. In the “Slicer Tools → Options” tab, set column 3, set the size of the button and the entire slicer (Figure 6).

1819A-QHK-6

Select the slicer, cut and paste it into the blank space of the worksheet where the chart is located. Thus, the dynamic chart labels are created (Figure 7).

1819A-QHK-7

Finally, just set the background and title of the chart as needed.

Leave a Comment