Excel 2024: Create Interactive Charts


March 19, 2024 - by

Excel 2024: Create Interactive Charts

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.
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.
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.
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.
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.
With a different customer selected from the slicer, the chart updates.



This article is an excerpt from MrExcel 2024 Igniting Excel

Title photo by Алекс Арцибашев on Unsplash