Choose Excel Dashboard Chart in Drop Down List

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.

Choose Excel Dashboard Chart in Drop Down List http://blog.contextures.com/archives/2015/07/30/choose-excel-dashboard-chart-in-drop-down-list/

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.

  • ChartData
  • EastData
  • ChartRange
  • ChartEast

The contents of the selected range will appear on the dashboard.

dashboardselect03

Items for the Drop Down List

The list items for the drop down are stored in a range named ShowList, on the ChartInfo sheet.

dashboardselect07

Then, on the dashboard sheet, a data validation list is based on the ShowList name.

dashboardselect06

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:

=IFERROR(INDEX(P7:P10,MATCH(O5,ShowList,0)),P7)

The Chart Data – East item was selected, and its range is named EastData

dashboardselect02

Add a Linked Picture

Next, a named formula — ShowChart was created. It uses the INDIRECT function to return the range from the SelRange cell:

=INDIRECT(SelRange)

dashboardselect05

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.

dashboardselect01

In the screen shot below, Chart Date – East was selected, so the EastData range is shown.

dashboardselect04

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.

Free Excel Dashboard Webinars

If you’d like to learn more about building Excel dashboards, Mynda Treacy is offering two free one-hour webinars. Get the details, and sign up for a date and time that is convenient for you.

  1. How to Build Excel Dashboards
  2. 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:

  1. Excel 2003,
  2. Excel 2010 (2 item drop down)
  3. Excel 2010 (multi-item drop down)

___________________

Choose Excel Dashboard Chart in Drop Down List http://blog.contextures.com/archives/2015/07/30/choose-excel-dashboard-chart-in-drop-down-list/

Save

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *