Use VLOOKUP function to create dynamic charts

Uploaded time: October 22, 2021 Difficulty: Primary

Use VLOOKUP function to create dynamic charts

Use VLOOKUP function to create dynamic charts

Tips: After starting to play, you can adjust the video clarity, click

Quality

Auto 720p

Graphic skills

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 cells and enter the A2:A12 cell range in the edit box.

_1.gif


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 are references 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.

_2.gif


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.

_3.gif


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.

_4.gif


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

_5.gif


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.

_6.gif


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.

_7.gif


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.

_8.gif


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.