Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

10000+ Copies Already Downloaded

How To Use Dynamic Data Labels To Create Interactive Excel Charts

Create Highlight dynamic Data Labels in Excel Charts

In this post, I will show you that how to create Dynamic Data Labels which you can Highlight when you need them.

The basic idea of creating these Dynamic Data Labels is to Create Interactive Charts which can further help you to create some Interactive Dashboards.

How this Dynamic Data Labels In Chart Work

You can highlight these Data Labels In Charts when you need them. This technique will help you in two ways. You can hide or unhide Data Labels In Your Charts & you can also able to Highlight Single Data Label In Your Charts. These both ways will help you to Create Your Charts More Interactive.

Ahead in this post, I will show you that how to apply this technique to a Line Chart as well as on a Clustered Column Chart.

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

I have a Simple & Clean Revenue Data in my worksheet. First of all, I have to make this data ready to create Dynamic Data Labels In A Line Chart.

Create Highlight dynamic Data Labels in Excel Charts - Data

Getting Data Ready To Create a Line Chart With Dynamic Data Labels

  • Enter a blank column next to the Revenue Column for Data Labels. We will use values from this column to create data labels.

Create Highlight dynamic Data Labels in Excel Charts - Enter Column

  • Now, I have to insert a Scroll Bar & a CheckBox to control Data labels in my chart.
  • Enter a CheckBox from the Developer Tab and linked it to a cell in your worksheet. We will use this check box for Hiding or Unhide Data Labels.

Create Highlight dynamic Data Labels in Excel Charts - Insert Check Box

  • After that, I need to Insert a Scroll Bar  which can help me to Highlight Single Data Label.

Create Highlight dynamic Data Labels in Excel Charts - Insert Scroll Bar

  • Now, In the blank column which you have inserted in your data enter 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 first verify that if CheckBox is Checked, Then it will show all the value in the column & you will get all the Data Labels in Your Chart. And, If CheckBox is not Checked it will further check the Scroll Bar Value to Highlight a Single Data Label.

Now, Our data is ready to create an Interactive Line Chart With Dynamic Data Labels.

Create Highlight dynamic Data Labels in Excel Charts - Data To Create a Interactive Chart

Insert A Interactive Line Chart With Dynamic Data Labels

  • Select the data & Insert a Combo Chart. Go to Insert → Charts → Combo Charts
  • Now, Select a Line Chart for Revenue Data & a Line Chart With Markers for Data labels.

Create Highlight dynamic Data Labels in Excel Charts -Insert a Combo Chart

  • After that, Select Data Label line & remove fill color from it. Once you remove the line fill color only markers will be left for that line.

Create Highlight dynamic Data Labels in Excel Charts - Remove Line Color

  • Now, Insert Data Labels for Data Label Line.

At this point, your have a Line Chart With Dynamic Data Labels. You can able to Hide or Unhide the Data Labels or you can highlight a Single Data Label In your chart.

Create Highlight dynamic Data Labels in Excel Charts - Line Chart

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

Data Should be created in the same way as I have created data for Line Chart.

Insert An Interactive Column Chart With Dynamic Data Labels

Follow these simple steps to Create a Column Chart with Dynamic Data Labels

  • Select the data & Create a Combo Chart.
  • Select a Column Chart for Revenue Data & a Line Chart With Markers for Data labels.
  • Add Data Labels to the Line Chart With Marker.
  • After that, Just un-fill Line Color & Marker Color.
  • Your Interactive Column Chart with Dynamic Data Labels is ready to rock this world.

Create Highlight dynamic Data Labels in Excel Charts - Column Chart

Download Sample File

Download Sample File To Learn About Dynamic Data Labels

More Tips Related With Dynamic Data Labels

Conclusion

Using Dynamic Data Labels will make your charts more Interactive. You can use these Dynamic Data Labels in your dashboards. Hiding data labels will also make your chart simple & when you need them just tick the checkbox.

I hope you found this useful. Please share if you have any Idea to create Interactive Charts.

Other Chart Related Tips

Back To Blog