You can make an interactive dashboard in Excel, even if you can't use macros, or prefer to avoid them. Select a chart or chart data from a drop down list, and the selected item will instantly appear on the dashboard – no macros required.
See how to build this dashboard, watch the video, and download the sample file to see the completed version.
Name That Range
In the screen shot above, "Chart Data – East" is selected in the drop down list, and that data will appear on the dashboard, replacing the current data.
There are 4 choices in the drop down, and on a separate worksheet (ChartInfo), there are four matching ranges, shown below.
The contents of the selected range will appear on the dashboard.
Items for the Drop Down List
The list items for the drop down are stored in a range named ShowList, on the ChartInfo sheet.
Then, on the dashboard sheet, a data validation list is based on the ShowList name.
Create a Lookup Table
In the column to the right of the drop down list items, the matching range names for each item are entered.
Above the lookup table, cell P5 is named SelRange, and it has an INDEX/MATCH formula that returns the range name for the item selected in the drop down list:
The Chart Data – East item was selected, and its range is named EastData
Add a Linked Picture
Next, a named formula -- ShowChart was created. It uses the INDIRECT function to return the range from the SelRange cell:
Finally, on the dashboard sheet, a linked picture was added, and its link was set to ShowChart. Now, when you select an item from the drop down list, the linked picture changes, to show the range that matches the selected item.
In the screen shot below, Chart Date – East was selected, so the EastData range is shown.
Video: Choose Dashboard Items from Drop Down List
This example is an update of an earlier workbook, in which you could choose between 2 items – a chart, or the chart's data. The video below shows how to set up that version.
The steps are similar for the new 4-item version, with the differences noted in the instructions above. Download the sample file (link below), to follow along with the video.
Or watch on YouTube: Show Excel Chart or Data on Dashboard With No Macros
Free Excel Dashboard Webinars
- How to Build Excel Dashboards
- Dashboards with Power Query and Power Pivot.
Mynda has also opened registration for her acclaimed Excel Dashboard course, and you'll get 20% off, if you sign up by today -- July 30th. The dashboard course is a great investment, and you can read my review for the highlights, and more information.
Download the Sample File
To see how the interactive dashboard works, you can download the sample file from the Excel Sample Files page on my Contextures website. In the Charts and Graphics section, look for CH0002 – Show or Hide Excel Chart. There are 3 versions of the file, so download the ones that you need:
- Excel 2003,
- Excel 2010 (2 item drop down)
- Excel 2010 (multi-item drop down)