Accelerate your thought leadership by contributing to our blog. Join our community of experts now!
Are you preparing a dashboard for your next meeting with the stakeholders, but are afraid that it simply isn’t too interactive?
Sure, you threw in a couple of impressive charts, but there is still something missing.
The solution might be drop down menus.
Drop down menus are a great way to leave an impression on the readers.
For example, say you’re preparing an Excel dashboard for your company’s product performances. Wouldn’t it look much better if you could pick a specific product and see its specific data?
While drop down menus can be found in most project management tools, Excel has long been the go-to place for executives building dashboards with drop down menus.
In this report, we will explain how to use drop down menus to make interactive charts and dashboards in Excel, and provide you with a step-by-step guide you can follow.
An Excel drop down menu works as a data validation function, it essentially provides users with the ability to pick one of the options from a list of choices.
Having an Excel dashboard with a drop down list is especially useful when companies need to perform financial modeling and analysis since the spreadsheet becomes much more dynamic and it’s easy to create different scenarios.
Here are a few more examples of where an Excel dashboard with drop down list can come in handy:
Excel drop down menus can go a long way in making your dashboard both interactive and easy for the viewers to engage with.
Want to know how to create a dynamic chart with drop-down list?
Here is a step-by-step guide you can follow to do it in no time.
First of all, you need to prepare the data you’ll be using for the drop down list.
You can either organize the data in columns and build an Excel table, or use rows. Each column should include data for a different variable (product, city, month, etc.).
In this picture, you can see an example of four outlets set up to summarize specific sales:
No matter whether you choose a column or a row, make sure you spend enough time preparing and filtering the data so everything is accurate.
There are three different methods you can use to create an interactive drop down list Excel.
Let’s go through them.
For instance, let’s assume that you have a list of items that looks like this:
Firstly, select a specific cell for which you want to create a drop down list.
Open ‘Data’ > ‘Data Tools’ > ‘Data Validation’.
Once you open Data Validation, you will a ‘Settings’ tab in which you can choose List as the primary Validation criteria. After doing so, a source field will pop up.
Now, once you are in the source field you should copy-paste this formula – =$A$X:$A$X (X is the number of the cell you want to include). Or, you can simply select the cells you want with your mouse.
You should check the In-cell drop down option since it’s the one responsible for displaying the drop down.
Also, it’s possible to create drop down menus for multiple cells at the same time, just choose all the cells you want and follow the same steps.
The other option is to add the items directly by manually entering the data in the source field.
For instance, let’s say you need two options displayed in the drop down menu – Yes and No.
You should choose the cell in which you want to include them and go to ‘Data’ > ‘Data Tools’ > ‘Data Validation’ (same pathway as in the previous method).
Again, the Validation Criteria in the ‘Settings’ tab should be set on ‘List’. Once you do that, the source field will pop up.
Now, you should enter ‘Yes’ and ‘No’ in the source field and make sure that the In-cell dropdown option is checked (it typically already is by default).
That’s it for method number two.
The last method we are going to go over involves using the OFFSET formula.
Notably, this formula isn’t the only one that can be used for creating a drop down menu, any formula that indicates a list of values will work.
Let’s imagine your data set looks something like this:
Now, you should follow the exact same steps as in the first two methods to get to the source field (select a cell, go to Data Validation, and select List as the validation criteria).
In the source field, type in the offset formula: OFFSET (reference, rows, cols, [height], [width]). This is the syntax of the formula, just type in your specific metrics in it.
After doing so, the drop down list should appear and look like this:
The next step in creating an interactive chart is to generate the data by using a data extraction formula.
To create dynamic charts in excel using data filters, we are going to use VLOOKUP and MATCH functions to dynamically generate the column numbers.
This is how the formulas should be entered:
Once you auto-fill this function to the columns located on the right side, you will acquire a constant value because the column index number is already coded (the #2 in the formula).
Now the ‘dynamic’ part.
Delete the 2 and enter this formula instead: =MATCH (D$11, $C$3:$O$3, 0)
Note: Apply your specific numbers in your formula
By using this formula, you will get column variables for different months.
Okay, so now the data will be automatically updated each time someone chooses one of the outlets in the drop down we created.
Time to create an interactive chart.
Go to ‘Insert Menu’, open ‘Charts’, and select ‘Line with Markers’.
This chart should appear:
You can later customize the color and other elements of the chart in any way you like.
Creating Excel multiple dynamic charts can’t be done unless the data included is also dynamic.
In the previous steps, we have shown you three methods you can use to make the data interactive.
However, you should know that there are other options to create interactive Excel charts.
For example, you can use a combination of Slicers and Pivot tables, or a Combo Box in the developer menu.
The reason we didn’t go over these methods is that they require a bit more technical knowledge and ultimately end up delivering the same result.
You can never get tired of useful information, so here are some additional tricks you can use when creating dynamic Excel charts.
We already showed you how to create a drop down list using the OFFSET formula, but did you know that you can also use it to create a dynamic drop down menu?
You can do this by opening the Source Field (follow the steps to it in the above section of the OFFSET formula method).
Once the Source Field pops up, enter this formula: =OFFSET($A$2,0,0,COUNTIF($A$2:$A$100,”<>”)).
In our example, argument 5 is replaced with the COUNTIF($A$2:$A$100,”<>”). We use the COUNTIF function to include all the non-blank cells.
Note: Apply your own numbers in the formula instead of copy-pasting this exact one.
For this method to work, you should make sure there aren’t any blank cells between the filled ones.
If you copy-paste the cells that have data validation to other cells, then the data validation will be duplicated there as well.
Let’s assume that there is a drop down list in cell A2 and you want to use it in A3:A6. You can simply copy-paste A2 into A3:A6.
This way, you will apply the drop down list in cells A3:A6, alongside the formatting.
Now, if you don’t want the formatting and you only want to copy the drop down menu, you can follow these steps:
In some situations, you will need two drop down menus, with the second one displaying items depending on what the viewers chose in the first menu.
This is known as dependent or conditional drop down menus.
Here is an example of an Excel dynamic chart with multiple drop down list:
You can see that the items in ‘Drop Down 2’ are dependent on what the viewers chose in the first drop down list.
To start the creation process, choose the cell in which you want the first drop down list to be.
Go to ‘Data Validation’ and choose ‘List’ in the dialog box.
Select the range containing the items in the Source Field and click ‘OK’ to wrap up the first drop down list.
Choose the entire data set (in this case we will take A1:B6).
Next, open ‘Formulas’, go to ‘Defined Names’, and select ‘Create from Selection’.
In the dialog box, uncheck all the items except for ‘Top row option’. This is what creates the two separate name ranges.
Choose which cell you want the conditional drop down list to be in and go to ‘Data’ > ‘Data Validation’. Select ‘List’ in the dialog box.
Now, enter this formula once you open the Source Field: =INDIRECT(X). The X is the cell that includes the primary drop down list.
Voila! You got yourself a conditional drop down list now, the items in the second drop down will now automatically update depending on what you choose in the first one.
Excel is a great tool for organizing data through comprehensive dashboards and then presenting them to your internal stakeholders so they can quickly understand the message.
There is just one problem – creating Excel dashboards requires a ton of hard work.
Not only will you need a huge amount of free time, but you should also have nerves of steel when dealing with the different Excel functions.
From gathering raw data and cleaning it to learning how to visualize the information through graphs and charts, creating Excel dashboards simply isn’t the way to go for busy executives.
Luckily, Databox understands the importance of dashboards and also provides clarity, time-saving solutions, and expert support, making the dashboard creation process much more efficient and effective for professionals like you.
Doesn’t having a flawless dashboard built for you sound like a better alternative? That’s exactly what you’ll be getting through our services.
The whole process is simple – you can connect to someone from our team, explain which metrics you want to highlight in the dashboard, and the rest is up to us.
If you don’t know which data should be prioritized in the dashboard, we got a solution for that as well. Our team will determine the most important KPIs by analyzing your company’s overall objectives.
Your Excel dashboard will be ready in less than 24 hours and we will even highlight the key functions our other customers typically use when it comes to performance analysis.
Not enough time to create a flawless dashboard for your stakeholders? Sign up for our free dashboard setup and let us take care of it.
Get practical strategies that drive consistent growth