Create an Interactive Excel Chart

If you add a chart to a dashboard, it’s helpful to let people choose what they’d like to see in the chart. In this example, you’ll choose a date range, and select other criteria, and the interactive Excel chart automatically adjusts to show total values for the selected data.

interactive excel chart

Interactive Excel Chart – Set Data Range

In an earlier tutorial, I showed how to use check boxes to show or hide chart data for specific regions. That chart always showed 6 months of data, but you could show East, Central or West regions.

chartinteractive01

That example was based on a technique that I learned from Jon Peltier, whose Excel charting tools can make your life easier, if you build charts too.

If a series isn’t selected, its data shows #N/A, and that doesn’t appear as a line in the chart.

chartinteractive03

A New Interactive Excel Chart

My new interactive chart has check boxes too, and uses that #N/A technique, so you can show the Quantity or Price or both series. It also lets you select dates and criteria, and creates totals from the source data, based on your selections.

The chart is described briefly below, and all the details are on the Excel Interactive Totals Chart page on my website.

chartinteractivetotals09

Interactive Excel Chart – Dynamic Date Range

Instead of using a set range of cells for the chart data, the new chart uses a dynamic date range. Enter a start date and an end date, and formulas show that list of months.

chartinteractivetotals19

An OFFSET formula is used to define that ChartData range, so it automatically expands or contracts when you change the start or end date.

=OFFSET(ChartStart,1,0,COUNT(ChartDatesALL),3)

chartinteractivetotals21

Other Chart Criteria

There are drop down lists above the chart, where you can select a Region and Category.

chartinteractivetotals13

In the table that has the sales data, additional columns were added, to calculate Quantity and Priced, for the rows that match the selected criteria.

chartinteractivetotals17

Calculate the Chart Totals

On the Chart Data sheet, where the selected date range is listed, there are formulas that total the calculated Quantity and Price. For example, this formula is in cell D4, to get the total quantity for the first month in the date range.

=IF($C4=””,””, IF(D$2=FALSE,NA(), SUMIFS(Sales_Data[CalcQty], Sales_Data[YrMth],C4)))

In cell E4, a similar formula get the total for calculated price, and shows #N/A, if the Price check box is not checked.

chartinteractivetotals20

Make a Dynamic Chart

A Clustered Column chart was created first, using the static range C3:E7. Then the chart data range was changed to the dynamic named range, Chart Data. chartinteractivetotals26

After that, the series and the date labels were changed, to use their dynamic ranges, instead of static ranges. All the details are on the Excel Interactive Totals Chart page on my website.

Add Chart Titles

If you plan to print the chart on its own, you can add a chart title that shows the selected date range, and other criteria. In this example, there is a formula in cell A1, and the chart title is linked to that cell. Text boxes are linked to the two criteria cells, to show those selections.

chartinteractivetotals32

Get the Interactive Excel Chart Workbook

All the details for building this chart are on the Excel Interactive Totals Chart page on my website. On that page, you can download a file that has the data only, so you can build from scratch. Or, download the completed workbook, to see how the interactive Excel chart works.

_____________________

Leave a Reply

Your email address will not be published.

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