Show Excel Chart or Data in Dashboard With No Macros

In this Excel dashboard example, you can select “Chart” or “Chart Data” from a drop down list. Magically, with no macros in the workbook, the selected item appears on the worksheet.

select "Chart" or "Chart Data" from a drop down list
select “Chart” or “Chart Data” from a drop down list

With this technique, you can store your data and chart on a hidden sheet in the workbook, where no one can mess with the numbers. (Not that anyone would!)

How It Works

The drop down list is created with data validation.

To show the selected item, named ranges are added to the workbook, and a linked picture shows one of those ranges.

Get the Sample File

To download the sample file, you can visit the Excel Sample Files page on the Contextures website:

There is also an Excel 2003 sample file, if you’re using an older version of Excel.

Watch the Video

To see the steps for creating this dashboard feature, you can watch this short Excel video tutorial.

Video Timeline

  • 00:00 Excel Dashboard Demo
  • 00:17 Create Excel Chart
  • 00:45 Name Worksheet Ranges
  • 01:28 Make Drop Down List
  • 02:07 Add Picture on Dashboard
  • 02:51 Create a Formula
  • 04:00 Change Picture Link

__________________

0 thoughts on “Show Excel Chart or Data in Dashboard With No Macros”

  1. Very interesting. I found I could also use Insert > Object > Bitmap Image to create the linkable picture.

  2. Interesting concept.
    As an exercise I tried this using excel 2007 but could not make it work properly.
    I have 3 graphs on the same worksheet and want to use your dropdown option to select any of the 3 graphs. However the Define names range wont let me put in the 3 graph names defined earlier.
    Can you explain why this is the case? many thanks

  3. I have created a master pivot table for all departments with all the columns needed. I want to copy the master pivot table in the same worksheet and remove any columns not needed for a specific department. The issue is that when I copy that pivot table and apply the filter for that department, the first pivot table filters against the copied one. Is there a way to do this.
    Regards
    Mike

  4. thanks ,
    really interesting. i was trying this feature for long but couldn’t do it. i some how found it on theory but was hard to understand.
    your video was really helpful in better understanding the steps

Leave a Reply to Bob Ryan Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.