• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Interactive Excel Charts

You are here: Home / Excel Charts / Interactive Excel Charts
Interactive Excel Charts
May 10, 2016 by Mynda Treacy

Using interactive Excel charts in your dashboards and reports allows the user to pick and choose what they want to see.

Interactive Excel Charts with Slicers

This means you only have to build the report once and then the user can easily create their own view instead of you having to create multiple permutations of the same report, customised for each user. Happy days.

Download the Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: this is a .xlsx file please ensure your browser doesn't change the file extension on download.

Interactive Excel Charts with Slicers – Excel 2010 Onwards

In Excel 2010 you can use Slicers to quickly and easily create interactive charts like the one above.

Now, I love Slicers but they have got some downsides:

  1. They’re not available in Excel 2007 (or earlier…in case anyone still uses Excel 2003)
  2. They take up a lot of space, and while I could make the Slicer in my example above smaller, I still can’t get it as compact as the next example.
  3. They filter data in PivotTables (and Tables in Excel 2013), so you are limited to using Pivot Charts, or you have to build another manual chart table if you want to use regular charts.

Interactive Excel Charts with Check Boxes – All Versions of Excel

In all versions of Excel we can use Check Boxes (and other types of Form Controls) to enable filtering in our charts.

Interactive Excel Charts with Check Boxes

It takes a bit more set up but it also comes with more flexibility than Slicers offer.

To create an interactive chart using Check Boxes:

  1. Double the Data. To create this chart I need my original data table plus another table that actually feeds the chart. The second table tests which check boxes are selected and only displays the data for those check boxes:

    Interactive Excel Chart source data

    In the image above we can see column I is displaying #N/A errors because the South check box is unchecked. More on building the second table for 'Chart Data' in step 4.

    Tip: your 'original data' table could be created using a PivotTable.

  1. Insert your check boxes. You’ll find them on the Developer tab (you may have to enable the Developer Tab first).

    insert check boxes

    After selecting the check box from the Form Controls simply left-click and drag to draw them onto your worksheet, then right-click to edit the text and give each one a name.

  1. Set the cell link for each Check Box. This is a cell on your worksheet that captures whether the Check Box is checked or unchecked. To set it right-click > Format Control. I’ve linked mine to cells H27:K27 and you can see below that the North check box is linked to cell H27, right above the North column of data for my chart:

    set check box cell link

    The Check Box returns TRUE in the Cell Link cell when checked, and FALSE when unchecked.

    We can use the value in the Cell Link cell to control what data is visible in the chart by referencing it in a formula.

  1. Write the Formula for your Chart Table: I used an IF formula in columns H:K. For example, in cell H29 I have the following formula:
    =IF(H$27=FALSE,NA(),B29)

    It tests whether cell H27 contains FALSE. If it does it returns #N/A (using the NA() function), and if it doesn’t it grabs the value from the Original Table.

    IF formula

    Tip: we use NA() in the formula to return #N/A if the check box is unchecked, because #N/A’s don’t display a line in the chart. If you were to replace NA() with zero or blank you would have a straight line across the bottom of the chart.

    Note: if you’re using a column chart you can use blank or zero instead of NA() since you don't have the line problem. In fact, your formula could be simplified to =B29*H$27 since multiplying anything by FALSE is the same as multiplying by zero and multiplying by TRUE is the same as multiplying by 1.

  1. Insert Chart: now that you have built your Chart Data table you can go to the Insert tab of the ribbon and insert your chart.
  2. Position the Check Boxes: You can put them anywhere but in this example I chose the bottom of the chart area and used Shapes to manually create a legend beside each Check Box.

    insert shapes

Don’t Stop There

I’ve shown you how to create one interactive chart but why stop at one. You can use this technique to build multiple charts, or even a Dashboard, all linked to these Check Boxes, or Slicers. I teach these techniques and more in my Excel Dashboard course.

Interactive Excel Charts

More Excel Charts Posts

burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
highlighting data in power bi visuals

Highlighting Data in Power BI Visuals

Learn several techniques to highlight or label important data points in your Power BI visuals. Sample file and code to download.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
project management dashboard

Excel Project Management Dashboard

Excel project management dashboard video tutorial covering various techniques including conditional formatting, PivotTables, Slicers, charts and more.
jitter in scatter charts

Jitter in Excel Scatter Charts

Jitter introduces a small movement to the plotted points, making it easier to read and understand scatter plots particularly when dealing with lots of data.
Custom Excel Chart Label Positions

Custom Excel Chart Label Positions

Custom Excel Chart Label Positions using a dummy or ghost series to force the label position neatly above the columns of data
Lookup Pictures in Excel

Lookup Pictures in Excel

Lookup Pictures in Excel using values in cells returned by data validation lists (drop down lists) or Slicers. No VBA/Macros required!

Cross Highlight Excel Charts

Filter and Cross Highlight Excel Charts like you can in Power BI using some Excel Power Pivot magic, regular charts and a Slicer.

More Excel Charts Posts

picture fill excel charts

Picture Fill Excel Charts

Using a stylish picture fill in your Excel Charts is a simple way to make your data visualizations more captivating and memorable
excel speedometer charts

Excel Speedometer Charts

How to build Excel Speedometer Charts or Gauge Charts as they're also know, why they are BAD and what to use instead.
burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excel S Curve Charts

Excel S-Curve Charts

Easy Excel S-curve Charts made with PivotTables for project management. Track progress by including budget amounts.
chart axis switch

Excel Chart Axis Switch

Use radio button form controls to create an Excel chart axis switch enabling you to toggle pannel charts between same axis and own axis.

Excel Charts with Shapes for Infographics

Excel Charts with Shapes for Infographic styling and increased interest in your charts. Easy to insert but there are a few tricks required.
excel pyramid chart

Excel Pyramid Charts

Excel Pyramid charts are useful for visualising demographic data across multiple categories. Let’s look at 3 ways we can build them in Excel.

Highlighting Periods in Excel Charts

Highlighting Periods in Excel Charts helps your users interpret them more quickly and or focus their attention on a point or area.


Category: Excel ChartsTag: excel charts
Previous Post:CHOOSE FunctionExcel CHOOSE Function Toggle
Next Post:Parts to a Whole Excel ChartsPart to a whole Excel Charts

Reader Interactions

Comments

  1. Rita

    June 11, 2016 at 12:32 am

    Hi Mynda, Thank you! I have managed to improve one of my reports with this neat trick. 🙂 The only thing I cannot figure out if there is a way to keep the chart on screen while I scroll down in the table (I have a long table on the left and the the chart on the right.) I tried to investigate and it seems it should be solvable by adding some VBA into the sheet but none of the examples I found actually worked. Do you have maybe an idea how to solve this?
    Thank you in advance!

    Reply
    • Mynda Treacy

      June 11, 2016 at 1:34 pm

      Hi Rita,

      That’s a nice idea but I don’t have any VBA code I could point you to, sorry.

      I prefer to make the table scrollable so you don’t have to use VBA. I teach this scrollable table using form control scroll bars in my Dashboard course. You can see an example in my le Tour de France Dashboard.

      Mynda

      Reply
  2. Mark

    May 14, 2016 at 6:09 am

    Hello Mynda, in your opinion is there a downfall in using (“”) vs ,NA in the formula? Are there any pros or cons in using one above the other?

    =IF(H$20=FALSE,NA(),B22) will show #N/A in cell B22
    =IF(H$20=FALSE,(“”),B22) will show a blank in cel B22

    Reply
    • Mynda Treacy

      May 14, 2016 at 3:04 pm

      Hi Mark,

      The downfall is when you’re using this data in a line chart. In a line chart a “” will display the line on the horizontal axis because Excel reads “” as zero. The only way to hide this line is to return #N/A as these errors don’t display a line in the chart.

      If you’re using column charts then you can use “”.

      Mynda

      Reply
      • Mark

        May 15, 2016 at 6:37 am

        Thank you Mynda.

        Reply
    • Catalin Bombea

      May 14, 2016 at 3:57 pm

      Hi Mark,
      There is a major difference between those: #N/A values are ignored in charts, empty strings are displayed as gaps, which in most cases is not a desired behaviour. Of course, there may be situations when you want to see all results, you have to choose the one you need.
      Cheers,
      Catalin

      Reply
      • Mark

        May 15, 2016 at 5:20 am

        Thank you Catalin.

        Reply
  3. Joe

    May 13, 2016 at 6:18 am

    I like this idea but I’m wondering if it can be adapted when the data range is changing (let’s say it is a daily chart and there is additional data each day). Since slicers rely on pivot tables, they automatically resize. Any options?

    Reply
    • Mynda Treacy

      May 13, 2016 at 10:07 am

      Hi Joe,

      You could use Dynamic Named Ranges and use these as your chart references so they grow as the data grows.

      Note: when you use the dynamic named range in your chart source you also need to prefix it with the sheet name e.g. =Sheet!your_dynamic_named_range

      Kind regards,

      Mynda

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.