Compare Data in an Excel Chart using Drop Down Lists

An excel chart is a graphical representation of a set of data that can be used for analyzing the data easily. A drop-down list allows the user to choose a specific item or element from a list. In order to learn more about excel charts please refer to Types of Charts in Excel and to learn dropdown lists refer here. Here, we will compare and analyze data using an excel chart with the help of a drop-down list. We will use mobile phones and compare them with the help of a drop-down list and represent the comparison data in excel charts. 

Steps to Compare Data in an Excel Chart using Drop-Down Lists

Step 1: Create a Database

In this step, we will create a database for our mobile phones. For this, we will be using the following data which we will be using to compare with the help of a dropdown list.

Step 2: Create Table Schema for Comparison

In this step, we will create a table schema that will be used to change the mobile devices with the help of a drop-down menu and returns the comparison data output. For this Select 2 Columns from the Database and then Paste Them into Some Other Columns.

Once we have copied the columns, we need to insert a graph for our copied columns. For this Select Column (Mobile Phone) & Memory (GB) and then go to Insert on the top of the ribbon and then in Charts select 2 D Column Chart.

After we inset the 2D Column chart for the above data, we will have the following output chart diagram.

Before moving further, we will format our chart to enhance its look and feel. To learn more about chart formatting please refer here. (Here, we are adding Data Labels & changing Chart Color only).

Once we are done with our chart formatting, we need to make a copy of our chart. Here, we copy & paste it 3 more times for the rest of our columns. i.e., Camera, Screen & Price.

As all of these charts are pointing to our same columns (Here, Mobile Phone & Memory columns). So, we need to change the underlying data of these charts. For this Select Chart and then Press & Hold Ctrl Key and then Move Cursor from Memory Column to Camera Column. 

Similarly, we need to do this for our remaining charts for the Screen and Price columns. Once we have the above steps in all the charts we will have the following output.

Now, to enhance the look, feel & behavior of the charts we will change the color of the chart as per our need. Once we are done with formatting the charts, we will have the following output.

Step 3: Adding VLOOKUP

In this step, we will add a VLOOKUP formula. So that, every time whenever we will update the name of our mobile phone, excel will change the data of our table and update the chart. We will be using the following VLOOKUP formula.

=VLOOKUP(G2,A1:E8,2,FALSE)

• G2 – It represents the comparison value. (Here, Mobile Phone Column).
• A1:E8 – It represents our database, excel will look up the values in this database.
• 2 – It represents the column number. Here, it is the second column (Column B) which is for our Memory Chart.
• False – It represents that we want excel to find an exact match.

Similarly, we need to add the same VLOOKUP to other columns (Here, Camera, Screen & Price) by updating the number of the columns (Here, for Camera column it is 3, for Screen, it is 4 and for Price, it is 5)

Once we added to VLOOKUP for all our columns it will update the corresponding charts. We need to copy the same VLOOKUP to our second row. For this, we will hold the corner and drag it down. Excel will automatically copy it to our second row of data.

Now, if we will change the data in our Mobile Phone Column, the data in the remaining column will get changed itself which further updates the charts.

Step 4: Adding Dropdown Menu

As we can see, while changing the data in the Mobile Phone Column, excel is changing data in the other columns of the table and updating the chart. So, we are now required to add a drop-down menu list over the Mobile Phone Column. For this Select Columns & Delete Its Value.

Now, we will insert a dropdown menu. For this Select Columns (Here, Mobile Phone Column) then go to the Data tab on the top of the ribbon and select Data Validation.

Once we click on it, excel will open a Data Validation Tab.

In Data Validation, we need to set the validation criteria. For Allow Option we will be using List and for Source we will use our database.

Once we click OK, we will have a drop-down menu for each of the columns which we will use to compare the data and update the chart.

Step 5: Output

Here now you can see when we change the mobile phone column with the dropdown list then the other columns and charts automatically change and update.