Use Dynamic Data Labels To Create Interactive Excel Charts

In excel worksheet Chart is a perfect tool to present data in an enhanced and more understandable way but most of the time it is seen that it sucks because of the overloading of excess of data representation. Actually the fact is that Chart looks more understanding when it is neat and clean. So the best way to keep your chart representation clean and understandable you need to make interactive charts in excel so that it don’t look too messy. As by doing this you can present more data in a single chart without worrying about all that clutter.

If you want to do something creative with your excel spreadsheet then creating dynamic charts is a good option to work with. The key is to define the chart’s source data as a dynamic range. As, this dynamic data labels will makes your chart to automatically reflect changes and additions to the source data. The basic idea of creating these dynamic data labels is to create interactive charts which can further help you to create Interactive Dashboards.

So let’s start knowing that how to use dynamic labels to create interactive excel charts.

How this Dynamic Data Labels In Chart Work??

In your chart you can highlight these Data Labels. This technique will help helps you in two ways firstly you can hide or unhide data labels in your charts and secondly, you can also highlight single data labels in your charts. Both of these ways will help you to create your chart more interactive of course.

Not happy with your MS Excel performance?
Don't Miss The BEST TOOL To FIX .xls/.xlsx Errors & Issues

How To Create These Dynamic Data Labels for a Intractive Line Chart In Excel?

First of all you need to have a simple and clean revenue data in your worksheet, just as shown in below figure. And then this data should be used to make Dynamic Data Labels In A Line Chart.

Create These Dynamic Data Labels for a Intractive Line Chart

MakingData Ready To Create a Line Chart With Dynamic Data Labels

  • Take a blank column just next to the revenue column for data labels. Later these column’s values will be used to create data labels.

MakingData Ready To Create a Line Chart

  • Now insert a scroll bar and a checkbox to control data labels in your chart.
  • Enter a checkbox from the developer tab and linked it with the cell in your worksheet. Later this checkboxes will help you in hiding or unhiding data labels.

MakingData Ready To Create a Line Chart 2

  • After this, insert a scroll bar which helps you in highlighting the Single Data Labels.

MakingData Ready To Create a Line Chart 3

  • Now in that empty column that you have previously inserted in your data , enter the below formula.

=IF($C$9=TRUE,[@Revenue],IF(AND(MATCH([@Revenue],[Revenue],0)=$B$9,$C$9=FALSE),[@Revenue],NA()))

How this Formula Work To Create Data labels Dynamic In Our Excel Charts??

This formula will verify that checkbox is checked or not, if it is checked then it will show all the value in the column and you will get all the data labels in your chart. And if in case the checkbox is not checked then it will further check the scroll bar value to highlight a single data labels.

Now, your data is ready to create an interactive line chart with dynamic data labels.

MakingData Ready To Create a Line Chart 4

Insert A Interactive Line Chart With Dynamic Data Labels

  • Select the data and insert a combo chart. For combo chart go to the Insert → Charts → Combo Charts
  • Now make a selection for the Line Chart for revenue data and a Line Chart With Markers For Data Labels.

Insert A Interactive Line Chart With Dynamic Data Labels

  • Now select the Data Label Line and remove fill color from it. After removing the fill color you will see that only markers will be left for that line.

Insert A Interactive Line Chart With Dynamic Data Labels 2

  • Now insert Data Labels for Data Label Line.

This step will give you a line chart with dynamic data labels. Now you have the option either to hide/unhide the data labels or you can highlight a single data labels in your chart.

Insert A Interactive Line Chart With Dynamic Data Labels 3

How To Create Dynamic Data Labels for a Interactive Column Chart In Excel??

For this also, you need to create data in the same way as you have created for Line Chart.

Insert An Interactive Column Chart With Dynamic Data Labels

To create a column chart with dynamic data labels, you need to follow these below mentioned steps.

  • Select the data & Create a Combo Chart.
  • Now select the column chart for revenue data and a line chart with marker for data labels
  • Add Data Labels to the Line Chart With Marker.
  • After then remove the Line Color and Marker Color.
  • Your interactive column chart with dynamic data labels is ready to impress anyone.

Insert An Interactive Column Chart With Dynamic Data Labels

Conclusion

Dynamic data label will surely going to give more attractive look to your chart. Hiding data labels will make your chart to look clearer and when you need them just tick the checkbox.

 

1984 Total Views 6 Views Today

How To Use Dynamic Data Labels To Create Interactive Excel Charts