WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free Download

Windows • MacOS • Linux • iOS • Android

banner

Use VLOOKUP function to create dynamic charts

Uploaded time: October 22, 2021 Difficulty Advanced

Use VLOOKUP function to create dynamic charts

Use VLOOKUP function to create dynamic charts

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. 

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.