Speaking of the production of interactive Excel charts, many people will first think of the use of controls to achieve. In fact, we can also not use controls, only three short lines of VBA code, plus a small number of functions, you can achieve the interactive effect of the cursor over the chart changes, so you can save the use of controls to set the tedious process. In this article, we will implement the effect that the corresponding chart automatically changes when the cursor moves between departments (Figure 1).
1. Set up the interaction code
Open the data worksheet, switch to the “Development Tools” tab (if there is no such tab, click “File → Options”, in the pop-up “Excel Options” window) Select “Customize Ribbon”, and then check “Development Tools”), click “Visual Basic”, and in the VBA window that appears, click “Insert → Module”, in the code window, enter the three lines of simple code shown in the figure (Figure 2).
The core part of this code is the middle sentence, its meaning is to let the p1 cell to get the value of the cell corresponding to tmp this parameter.
2. modify the original data
To make the chart interactive, you also need to modify the original data. In cell A2, enter “=IFERROR(HYPERLINK(changetmp(A2)), “A department”)”, in cell A3, enter “=IFERROR(HYPERLINK(changetmp(A3)), “B department”)”, the cell of this column, and so on (Figure 3).
In the process of entering the formula will appear warning prompts, then, directly click “OK” can be. 3.
3. Construct the chart data source
Switch to the “Formula” tab, click “Name Manager”, click “New” in the pop-up window, in the new pop-up window “Name “Enter “TEMP” in the new pop-up window, enter “=OFFSET(Sheet1!$A$1,MATCH(Sheet1!$P$1 Sheet1!$A$2:$A$5,0),1,1,12)”. In this way, a data source is constructed for the chart (Figure 4).
4. Create a chart
Select a blank cell, insert a line chart with data points. Right-click this blank chart, select “Select Data”, click “Add” in the new pop-up window at the “Legend Item”, and enter “Series Value” in the pop-up window. TEMP” in the pop-up window, click “Edit” in the “Horizontal (Category) Axis Labels”, and select “=SHEET1! Select “=Sheet1!$B$1:$M$1” for the label area. In this way, the chart is built, when the mouse slides over the departments, the effect of chart interaction appears (Figure 5).
Enter “=$P$1&”January-December 2019 Sales Change”” in cell Q1, select the chart title, and enter “=Sheet1!$Q$1” in the formula edit bar (or click the cursor behind the formula edit bar and use the (or click the cursor to the formula editing bar, and then click the Q1 cell with the mouse). In this way, the chart title will also follow the change (Figure 6). 5.
5. Beautify the chart
In order to make the chart look better, you need to perform further beautification of the chart. Delete the horizontal grid lines of the chart, right-click the vertical axis of the chart, select “Set Coordinate Format”, set “120” at “Maximum” in the axis option, and delete the vertical axis. Coordinate axis; switch to the “Chart Design” tab, click “Add Chart Element → Data Label → Above”, set the required font, color and size of the label; select the data points in the chart, and select “Set Data Series Format→Fill & Line→Label” tab, select “Built-in” at “Label Options”, select “Image” at “Type”, and select the desired image in the In the pop-up window, select the desired image, “border”, select “no lines”; set the font color and size of the horizontal axis (Figure 7).
6. Save the file
Because the interaction process uses VBA code, so you need to save the file as a macro-enabled file. Click “File → Save As”, enter the required file name in the pop-up window, save the type select “Excel macro-enabled workbook”, click “Save” on it (Figure (Figure 8).
In fact, the chart data source can also be obtained through the VLOOKUP function, in cell Q2, enter “=VLOOKUP($P$1,$A$2:$M$5,COLUMN()-14,FALSE)”, and drag to the right to cell AA2, so that you can also get the chart required data source (Figure 9).