Office hacks: create dynamic charts with the cell selected and moving

We have seen dynamic charts before, are the use of controls or data validity, to achieve the selection of different categories of items. In fact, dynamic charts can also change dynamically with the selection of cells, we just need to use CELL, INDIRECT, ADDRESS, MATCH, OFFSET and other simple functions, you can achieve dynamic display (Figure 1).


Take the example of creating a dynamic table of sales performance of salesmen.

First, you need to construct the data source of the chart. In the worksheet on the basis of the original data, in the appropriate location to use the function to construct the data source required by the chart. Such as in cell B12, enter “= INDIRECT (ADDRESS (CELL (“row”), 1))”, used to obtain the value of the current cell; in cell C12, enter “= INDIRECT (ADDRESS (3, CELL (“col”)))”, used to get the current cell is located in the project type; in cell D12, enter “=MATCH($B$12,$A$4:$A$10,0)”, used to get the current cell in the name data in the index value; in E12 cell, enter “=MATCH($C$12,$B$3:$C$3,0)”, used to obtain the current cell in the item type in the index value; in cell B13, enter “=OFFSET($A$2,0,COLUMN(A1)*2 1,1,1)”, fill to the right to M13, used to obtain the chart’s X classification axis data, that is, the month; in cell B14, enter “=OFFSET($A$3,$D$12,$E$12+COLUMN(A1)*2-2,1,1)”, fill to the right to M14, according to the D$3,$D$12,$E$12+COLUMN(A1)*2-2,1,1) right fill to M14 and get the specific chart data source according to the index value of D12 and E12.

Add the chart. Select cell B13:M14 and insert a cluster bar chart. Thus, the required chart is basically created (Figure 2).


In order to make the dynamics of the chart change more smoothly when you click on a cell, you need to add a few simple VBA code. Select the “Development Tools” tab, click “Visual Basic”, and in the VBA code editing window that appears, double-click Sheet1 and enter the three lines of code shown in the editing window on the right (Figure 3).


Tip: If there is no “Development Tools” tab in the window, you can click “File → Options”, in the pop-up window, check the “Development Tools” on it.

Next, set the chart style, add chart labels, etc. as needed. Select cell F12, enter “= CONCATENATE (B12, C12, “annual statistics”)”, select the icon title, enter “= F12” in the formula editing column, so that the icon title will follow the changes in the cell and change. Finally, click “File → Save As”, in the pop-up window, select the file type “Excel macro-enabled workbook (*.xlsm)”, to save on it.

Leave a Comment