Top 11 Best Dynamic Excel Chart Tips

Charts

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.

Automatically Expanding a Dynamic Excel Chart to Include New Data using a Table

The Table feature in recent versions of Excel is the easiest method of creating dynamic charts, but it is not backward compatible

The Table feature in recent versions of Excel is the easiest method of creating dynamic charts, but it is not backward compatible

Click here to use the Dynamic Excel Chart using an Excel Table tutorial.

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.

Automatically Expanding a Dynamic Excel Chart to Include New Data using OFFSET and Range Name

Use Excel's OFFSET and COUNTA functions to create dynamic range names automatically adjust as data is added or deleted.

Use Excel’s OFFSET and COUNTA functions to create dynamic range names automatically adjust as data is added or deleted.

Click here to use the dynamic chart using OFFSET and range name.

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

Use a Data Validation list to select data for a dynamic Excel chart

Click here to learn how to use a data validation list to select the chart data.

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.

Selecting Data from a Drop-Down List for Dynamic Excel Charting

Can You Make a Dynamic Chart with Drop-Down List Faster than This?

You can quickly create a dynamic chart controlled by a drop-down list.

Click here to learn how to select chart data from 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.

Scrolling a Dynamic Excel Chart through Time

Use INDEX to scroll the X-axis in an Excel chart.

Use INDEX to scroll the X-axis in an Excel chart.

Click here to learn how to scroll a dynamic Excel chart through time.

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.

You might also want to checkout how to scroll a timeline. Click here to learn how to scroll a timeline.

The dynamic Excel timeline chart allows you to scroll backwards or forwards through time.

The dynamic Excel timeline chart allows you to scroll backwards or forwards through time.

 

Using a Check Box to Show or Hide Data or Trend Lines in a Dynamic Excel Chart

Adding the check box and drop-down gives your Excel dashboard user full control.

Adding the check box and drop-down gives your Excel dashboard user full control.

Click here to learn how to show or hide trend lines in a dynamic Excel chart.

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.

Click here to learn how to switch between different trend lines.

The Format Control sets up your check box.

Use a drop-down list to give your Excel dashboard user the ability to select between different trend types.

Creating Dynamic Text in Excel Chart Titles or Legends

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.

Click here to learn how to link Excel titles to worksheet cells.

Linking Excel Chart Titles to Cell Contents

Dynamic Excel chart titles link to cells and show multiple text lines.

Dynamic Excel chart titles link to cells and show multiple text lines.

Click here to learn how to create dynamic Excel chart titles.

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.

Linking Excel Legends to Cell Contents

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.

Click here to learn how to create dynamic Excel chart legends.

Click here to download the tutorial file on how to link Excel legends to cell contents http://criticaltosuccess.com/creating-dynamic-excel-chart-legends-that-link-to-worksheet-cells/.

Displaying Form Controls like Drop-Down Lists and Check Boxes

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.

Click here to download the tutorial example that shows you how to add the Developer tab to the Excel 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.

 

Share the power...

Leave a Comment:

Leave a Comment: