Dynamic Charts

Dynamic charts help us to improve the quality of data visualization. In this post, we will show you the most used solutions in Excel. Use these methods and save your time.

Good to know that a great looking dynamic chart is a key element of an excel dashboard.

Compare Yearly Sales using dynamic charts

Let us see an example! We have said quite a number of times that less is more.

Okay, we have to compare three-period sales data using line charts. What is the best way to highlight the trends? Use combo charts! The graph based on two line chart and a single column chart.

In the picture below, we present the year 2021 data using a column chart. To show the other two years’ data, we are using line charts.

Just a few words about Excel form controls. As first, insert two checkboxes. If we do that, we can decide what years’ data will be shown on the dynamic chart.

steps to create a form control based chart

Right-click on the inserted checkbox and add a cell link. The Format Object menu will appear. Click on the cell link option and browse cell D9. If you want to show the line chart, the cell value will be TRUE. If the checkbox remains unclicked, the line chart will not show.

Data in Focus – A dynamic scatter chart

In the next example, we summed up the data, and we will show them on the chart. As first, select the company from the drop-down list.

After choosing a company, a red circle will mark the current one out of the point of the chart. Creating this dynamic chart is not difficult; download the example and check the used methods.

To be fully effective, highlight the current data this way! If you are working with key performance indicators, your eyes focus on an important point. Just imagine how much more exact it would be to search for the right point. Looks great, right?

focus on the data

Dynamic Tooltips

As we stated above, our main goal is to place even more information using a small space. The following example is based on a popular linked picture method.

As first, create the main bullet chart on the first Worksheet. We would like to display the Incomes, Costs, and Profit chart using a tooltip style. How to do that? The answer is simple: We will build three helper tables on the second sheet and create a chart for each series.

Jump to the ‘Calc’ sheet! Now create names from a range using the Name Manager.

simple name manager for dynamic charts

After that, insert a checkbox and three radio buttons. Let us see how the trick works:

Checkbox -like a switchable button – has a two value: TRUE or FALSE. If the button is checked you will able to select one tooltip from the three options.

Try to click on the Profit button. The Profit trend chart will be appear! You can switch to Costs or Incomes anytime.

dynamic chart tooltips

Download the practice files! Stay tuned.