MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2020: Create Interactive Charts


March 11, 2020 - by Bill Jelen

Excel Create Interactive Charts. Photo Credit: Alex Knight at Unsplash.com

It is easy to create interactive charts without using VBA. By default, if you hide rows in Excel, those rows will be hidden in the chart. The technique is to build a chart with every possible customer and then use a slicer or a filter to hide all except one of the customers.

Say that you have the following list of customers. Make the data into a table by using Ctrl+T.

Customers down column A, Q1 through Q4 across the top. The AutoFilter icons appear across the top row.

Select the table and insert a chart. In most cases, Excel will create the wrong chart, with customers along the X-axis.


Because there are 10 customers and only four quarters, the initial chart has customer along the x-axis and quarter in the legend. This is reversed of what you want.

Click the Switch Row/Column icon in the Chart Tools Design tab of the Ribbon.

Click the Switch/Row icon in the Data group of the Chart Tools tab of the Ribbon.

Select one cell in the table. In Excel 2013 or newer, go to the Insert tab of the Ribbon and choose Slicer. In Excel 2010 or earlier, you have to use the Company dropdown in A17 to choose a single company.

By default, every slicer starts as a single column in the middle of the screen. Plan on dragging the slicer to a new location and size. While the slicer is selected, you can use the Columns spin button near the right side of the Slicer Tools Options tab of the Ribbon to change the number of columns in the slicer.

In the following figure, choose one customer from the slicer and the chart updates to show just that one customer.

Using a Slicer to hide all but one customer, the chart shows the only visible customer. Choose a different customer from the slicer, and the chart updates.

Choose a different customer, and the chart updates for that customer.

With a different customer selected from the slicer, the chart updates.

Title Photo: Alex Knight at Unsplash.com


This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.