Dynamic Excel charts automatically customize and update charts, allow users to scroll through time and select data, and give greater analytic power. What’s not to like?
There are so many way to make charts dynamic so they update and customize automatically, that at first you don’t know what you don’t know. So, here is a list of eleven powerful and professional techniques for making awesome dynamic Excel charts. This list has techniques ranging from very simple to more complex. None of them use macros, so if you are comfortable with charts and formulas you can make the charts shown here. (The more complex and powerful are at the beginning of this list and the simpler are at the end.)
Most of these tips work in Excel 5/95/97/2000/2003/2007/2013 and none of them need macros.
The Table feature in recent versions of Excel is the easiest method of creating dynamic charts, but it is not backward compatible
Excel 2007, 2010, and 2013 contains a Table feature that identifies a range of cells that belong to the same collection of data. Once you create a Table, which is super simple to do, any chart based on that Table will automatically include new rows or columns of data added to it. If you are working only with more recent version of Excel then this is the way to make charts that expand automatically.
Use Excel’s OFFSET and COUNTA functions to create dynamic range names automatically adjust as data is added or deleted.
In all versions of Excel you can use the OFFSET function combined with range names to create charts that expand or contract as the amount of data changes size. The great thing is that when you master this technique and learn how to use the INDEX function you have the power to create scrolling charts, select data to be charted, hide/show chart data, and more. Those three, OFFSET, INDEX and range names give you incredible charting flexibility. They are the foundation for a whole toolbox of tricks.
Selecting Data from from a Data Validation List for a Dynamic Excel Chart
Use a Data Validation list to select data for a dynamic Excel chart
This might be the fastest way to create a dynamic Excel chart that updates when the user selects data from a list. In this example a data-validation menu gives users the power to select the data they want charted. It’s easy, fast, doesn’t need the Developers tab in the ribbon, and just uses a VLOOKUP to retrieve the data. You can’t get much more streamlined than that.
This dynamic Excel chart tip only works in versions of Excel that have Data Validation, Excel 2007/2010/2013.
You can quickly create a dynamic chart controlled by a drop-down list.
If your users have a lot of data to analyze and they want to select the data they see in the dynamic Excel chart, then you should use this simple technique. You will learn how to add a drop-down list so users can select data. Then an INDEX function selects the data they choose to be charted. As soon as the user selects from the drop-down the chart updates. And, you don’t even need to create a macro. Very simple and it is a powerful tool to give your dashboard users.
Use INDEX to scroll the X-axis in an Excel chart.
Using a dynamic Excel chart to scroll X-axis data through time gives your users a time machine. With this dynamic dashboard users can scroll backward or forward through an entire database of data. You set the limit for how far backward or forward through data they can scroll. It is very intuitive to use also since it uses a scroll bar at the bottom of the chart to scroll the X-axis.
The dynamic Excel timeline chart allows you to scroll backwards or forwards through time.
Adding the check box and drop-down gives your Excel dashboard user full control.
Sometimes you need to show or hide data or trend lines on a chart. (This works for showing or hiding any data on any chart.) This technique makes it easy to show or hide data with a check box. Click to show. Click to hide. This is as easy as it gets. All it takes is inserting a check box and the IF function.
Check boxes give your users the power to show or hide data in a chart so they can get rid of the clutter and focus what is important. I’m sure you’ve seen charts that have ten or twelve lines or columns of data. It’s impossible to tell where there is a trend or to compare two different lines out of twelve. With this easy-to-apply technique your users can easily show or hide show or hide data in the chart.
You might also want to see how to switch between different trend lines. This example gives users the ability to switch between linear and exponential trend lines.
Use a drop-down list to give your Excel dashboard user the ability to select between different trend types.
Your titles and legends in Excel can automatically update to show titles based on cell contents. By using the TEXT and & functions in cells you can create dynamic titles that include currency, dates, percentages, and more. When the formulas update to reflect new dates, calculated results, or such the titles that are linked also update.
This tip shows you how to create the dynamic text in a cell. The next tip shows you how to link the text in the cell to a chart’s title.
Dynamic Excel chart titles link to cells and show multiple text lines.
You don’t have to settle for Excel’s static chart titles. Your Excel chart titles can be dynamic and link to cell contents. The previous tip shows how to use the TEXT and & funcitons to create dynamic titles in cells. When the worksheet changes, the title changes. They can even have multiple lines that include dynamic data.
You may want a different legend or legend with more information that what Excel charts normally create. With this technique your legends update to include any text or numbers you can create in a worksheet cell. Be sure you check the earlier tip on how to create dynamic text in a cell using the TEXT and & functions.
Form controls, like a drop-down list or check box, are located on the Developer tab in the Excel 2007/2010/2013 ribbon. If you don’t see the Developer tab, click here to learn how to add the Developer tab to your ribbon.
In earlier versions of Excel form controls are found in the Forms toolbar. To see them, choose View, Toolbars, Forms.
To use a Form Control, click on the control you want then drag across the worksheet where you want the control to appear. The tutorials above explain how to position, size, and set the properties of the drop-down list, scroll bars, and check boxes.
Get Dynamic with Your Charts
When you combine two or three of these techniques in a chart you leave the mundane world of static Excel charts and give you and your dashboard users the power to interact with the data. They can compare time frames, compare multiple sets of data, and use data that is always “live.” Now, go out there and get dynamic.
