In the last video, we introduced the tips for making dynamic charts quickly. And this video will show you how to create dynamic charts with the help of the VLOOKUP function. Some knowledge points such as drop-down lists, VLOOKUP function, and absolute references are involved in making dynamic charts. If you are not familiar with these knowledge points, you're welcome to enter WPS Academy to search for learning.

Take this table as an example. We're going to create a dynamic chart of the salesperson's monthly performance.

In this “Name”, our object variable is name. We can show different sales data by switching names.

So we first need to create a drop-down list of names. Select cell O2, click the Data tab, and then click the Insert Drop-down List button. In the pop-up dialog, check Select choices drop down from cellsand enter the A2:A12 cell range in the edit box.

Step 2: Create a chart title

Select cell O5 and enter the formula “=O2&”’s sales performance””

By the way, the sign & here is a merge connector, which connects the following character string. The double quotes here arereferences to the text. At this point, the chart title has been created. When we change the name in the drop-down list, the chart title will change accordingly.

Step 3: Create a chart data source

Copy the titles in the B1:M1 cell range and paste them into the P4:AA4 cell range. In cell P5, enter the formula=VLOOKUP($O$2,$A$1:$M$12,COLUMN(B1),0)

Note: To prevent data areas from moving, we need to press F4 to add an absolute reference.

We all know that the VLOOKUP function can only refer to one column of data. In order to solve this problem, we need to work with the COLUMN function. Let me show you the effect: We fill one cell to the right, the cell B1 in the reference function will move one cell to the right and change into C1. That's the magic of the COLUMN function.

If you only use the VLOOKUP function, the data filled to the right will not follow the change.

Step 4: Create a dynamic chart

Select cell range O4: AA5, click the Insert tab and the Insert Column Chart, choose Clustered Column. Then, we can insert a column chart in the worksheet.

Note: Here, we need to add the cell where the title is located so that the dynamic chart can display the title normally when switching.

At this point, when we choose the different names in the drop-down list of cell O2, we find that the column chart can display the data chart for that name to achieve the effects of the dynamic chart.

The above operations can be achieved by the slicer. When we copy one more table out, the slicer will link the two tables together. As long as the slicer data is changed, the data of the two tables will change. This can be avoided by using the function to create the dynamic chart.

It is difficult for us to understand dynamic charts at the beginning, but as long as we understand the logic of creating dynamic charts, we will learn it to do more with less. Make your work easier with WPS Spreadsheet like Microsoft Excel.