How to create a dynamic chart using a Data Validation list and VLOOKUP

Excel

This could very well be the fastest and easiest way to create a dynamic chart. It’s a great way to impress the boss when you need to quickly make a dynamic chart to look at lots of data. If you know a faster method, please let me know so I can share it on Critical to Success.

Use a Data Validation list to select data for a dynamic Excel chart

Use a Data Validation list to select data for a dynamic Excel chart

In the figure above the cell C16 contains a data validation list. When you select that cell an arrow appears on the right side. Click the arrow and a list of valid choices drops down. Select from the list and the data in row 16 and the chart update from the database below.

Download the Use a Data Validation list to create a quick dynamic Excel chart example file.

Want to make a data validation list automatically expand?

Check out how to use a dynamic range name so your data validation list adjusts automatically to an expanding or contracting list of menu items. See How to Create Dynamic Range Names for Automatically Expanding Menus.

Creating the Dummy Database

The data in rows 20 to 24 are just dummy data for the example. Here’s how to quickly create the dummy data. In cell D21 use a RANDBETWEEN function to create random data you can copy down and across. The RANDBETWEEN function’s arguments are,

=RANDBETWEEN(bottom,top)

where bottom is the lowest random number and top is the upper random number. After entering RANDBETWEEN in D21, copy it down and across to fill the dummy database range. If you want to generate new random numbers, just press F9 the calculate key. Each press of F9 recalculates RANDBETWEEN. I’d recommend you create your chart from one row of random numbers, then press F9 until you get some data you like, then freeze the data.

You can “freeze” the random numbers by copying the cells with RANDBETWEEN and pasting them back in with Paste, Special, Values.

Creating the Data Validation List

A cell containing a data validation list, like C16, appears the same as any other cell until you select it. So to give your users a visual cue that the cell is special format it as light green, like a data entry cell.

To create the Data Validation list,

1. From the Data tab, in the Data Tools group, click Data Validation, then select the Data Validation tool to display the Data Validation dialog box.

Use the Data Validation dialog box to specify the list.

Use the Data Validation dialog box to specify the list.

2. Select List from the Allow drop-down and check the Ignore Blank and In-Cell Dropdown check boxes.

3. In the Source box drag across the range $C$21:$C$24 to identify the range of labels that will appear in the list. Click Ok.

Now you can click in cell C16 and see a drop-down list that shows the labels from C21:C24. The next step is to create the formulas that pull the data into the chart range, C15:I16.

Using VLOOKUP to Fill Your Dynamic Excel Chart Range

At this point you can select one of the region names from a list in cell C16. That’s part of what you need, but you need a way to use name to pull information from the database into the chart area. Using VLOOKUP you can do just that, use the region name from C16 to pull data out of the range D21:I24 into the charting range D16:I16.

The VLOOKUP function uses the parameters,

VLOOKUP(lookup_value,table_array,column_index_num,[range_lookup])

In cell D16 enter the formula,

=VLOOKUP($C$16,$C$21:$I$24,2,FALSE)

$C$16 is the region name selected from the data validation list and used to lookup information. The information being looked up and retrieved is in the table (or array) $C$12:$I$24.

At this point I have to tell you that while VLOOKUP is great and super and all that, it does have an inherent flaw. It only looks for a match in the leftmost column in this case column C. (The get around this leftmost limit you use a combination of MATCH and INDEX to do the same as VLOOKUP.) I’ll be writing more about when to use VLOOKUP and when to combine the powerful functions INDEX and MATCH.

The number 2 specifies that the data being retrieved will be from the second column, “Jan”. The last parameter, FALSE, specifies that whatever is in C16 must exactly match the item in C21:C24. If it doesn’t match you’ll get a #NA error.

Here’s how VLOOKUP works. VLOOKUP uses the “North” in C16 to look down the leftmost column of the table $C$21:$I$24. Because of the FALSE parameter it looks for an exact match. When it finds “North” it looks in the same row as “North” across to the second column, remember you entered 2 for the second column.

To fill the rest row 16 with VLOOKUP formulas copy the formula in C16 across. You’ll see that all the results are the same. This is because all of the VLOOKUPs are retrieving data from the second column. You need to change the column number 2 in each formula to the appropriate column (2, 3, 4, etc.) to retrieve the data that matches each month’s header.

Now make selections from the data validation drop-down and see the results change. The only thing you have left is to create a chart based on the data in C15:I16.

This is a very quick (at least the second time you do it will be) and easy way to create a dynamic chart using a menu. For most cases it works well; however, for more advanced uses you may want to use a range name to create an automatically expanding list of menu choices. In place of entering the cell reference in the Data Validation dialog box you enter the range name, for example,

=mnuRegions

To see how that works, read < How to Create Dynamic Range Names>

Combining Data Validation lists and VLOOKUP is a quick and simple way to create a dynamic chart but, when you want a lot of power. For more power, like expandable databases and lists, you’ll want to learn how to create dynamic range names. And then, if you want to push Excel charting to its greatest capabilities learn how to use MATCH, INDEX, OFFSET, and a few simple macros.

Download the Use a Data Validation list to create a quick dynamic Excel chart. example file.

Now, go out there and be dynamic!

Download the Use a Data Validation list to create a quick dynamic Excel chart example file.

Related Topics

How to Create a Dynamic Chart using a Drop-Down List

How to Create Dynamic Range Names for Automatically Expanding Menus

 

Share the power...