8. Add interactive Buttons to the Dashboard

 
Subtitles Enabled

Sign up for a free trial to continue watching this lesson.

Free trial

Overview

Interactive buttons allow you to include more information on the dashboard and enhance the user experience. Find out how to add a simple dropdown button in this lesson

Lesson Notes

Dropdown button with Data Validation

- Data validation is a simple way to create a dropdown button
- The button is attached to a cell and the value within the cell can be used in formulas
- One drawback is when you move away from the cell, the dropdown button disappears

Dropdown button with Form Controls

- Form controls offer a better user experience but are slightly harder to create
- Open the Developer tab in the ribbon and select the Combo Box
- Include your sales managers in the Input range and select a cell to be the cell link output
- Use the cell link as the reference to the button in your formulas

Keyboard shortcuts

ALT + A , V, V: Open Data Validation
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
CTRL + SHIFT + →: Select all cells within data region
CTRL + →: Move to the last cell in the data region

Transcript

In the final section of our dashboard, I want to include an interactive button that will allow a user to select a sales manager from a drop-down list and provide him with a number of metrics that are relevant to the sales manager�s performance. To create these drop-down lists in Excel we have a couple of options. The first option is data validation. As you may have seen in other courses, data validation allows us to create drop-down lists very quickly in Excel. To open data validation I'll use a shortcut Alt + A V V. And in settings we'll switch Allow to List. In the Source, I then simply select the cells that I want to include in my dropdown. So going to the data input sheet, I'll just select all of the sales managers. And then press OK, to finish. And I now have a drop-down list with all of my 15 sales managers included. This cell can now be used as a LOOKUP value so we can calculate Total Revenue, New Revenue Rate, and Lapsed Revenue rate. One of the drawbacks with data validation is that when I move away from the data validation cell I can no longer see the dropdown button. If you'd like to use a dropdown list that always appears then we need to use another tool in Excel called Form Controls. Form controls exist in the Developer Tab of the Ribbon. If you can't see the developer tab in your ribbon, simply go up to the top, right click and go to customize the ribbon. Then select Developer and press OK. You should now have the developer tab in your ribbon. Then go to Insert, and select Combo Box which corresponds to a drop-down List. Form controls are not associated with a particular cell and can be any size and located anywhere on your sheet. Once you've created the combo box right click and go to Format Control. For the input range, we'll select the 15 sales managers as before. So I'll go to the dashboard input and select these 15 cells. The cell link is a cell which captures the output from our form control. And to start with I'll select cell I31 at the bottom of our dashboard.

I'll also change our drop-down lines to 15 so that we don't need a scrollbar and then press OK. Now we can use our drop-down button to select a sales person. When we select a sales person you can see the number changing in the cell link and this is because the form control is telling us what number is being selected when I pick a sales manager. To link our drop-down list to our output metrics, we use this cell link value so in our dashboard input sheet, I've created a new column called LOOKUP NUMBER, which numbers each sales manager from 1 to 15. We now perform a LOOKUP on this data set using our cell link to find the total revenue. So going back to the total revenue cell, I'll write, equals VLOOKUP, open the bracket, and the lookup value will be the cell link. The table array would be our revenue data set by sales manager. The callindexnum would be revenue, which is column 3, and I want an exact match so I'll write FALSE. This tells me the total revenue for Moll. If I check my data set I can see that this is the correct value. If I change the sales person in my drop-down button, the revenue number changes accordingly. Before I wrap up this lesson, you might have noticed that the title of this value is incorrect. This is because the formula is linked to the cell that we had for data validation. As an exercise try to change the formula so that when I update my sales manager, the title updates accordingly. I'll leave my answer to this question in my after file below the video.