Dynamically Highlight Data point on Your Chart Without VBA

Posted: January 6, 2015 by MaxFraudy in Excel
Tags: , , ,

It’s no secret that I’m a big fan of beautiful data visualizations. What may not be so obvious is that I’m huge on the KISS (keep it simple stupid) principle when it comes to getting work done. I believe that any moron can complicate things, but it takes a genius to simplify them. (On a separate note, I often recall a quote on life from one of my all-time favorite movies – “It is very simple to be happy, but it is very difficult to be simple”)

Today I’m sharing a simple tip on how you can highlight a data-point on your chart, based on a selection, without using VBA. And the best part is that it can be used for any chart type.

Chart - Highlight Data PointI have data for average precipitation in Delhi by month (for years 2000-2012). I have placed my data in cells A4:B15. Data validation has been put in cell B2 such that it can only take month names from January-December. Now that we are set up, follow these steps:

  1. Create a column chart with this data range.
  2. In cell C4, write this formula ‘=IF(A4=$B$2,B4,0)’ and drag it till C15.
  3. Add another series to the chart with range C4:C15.

    Untitled

    Chart with two Series

  4. Right click on the series and select ‘Format Data Series’.
  5. Move the Series Overlap Slider to extreme right at 100%.Overlapped
  6. Format the series with colors/patterns of your choice.Final

You’re done! Try changing the month name to see the magic.

If you don’t feel like doing the hard work, download Dynamically Highlight Data Point workbook.

You may also like:

Advertisements
Comments
  1. Thomas says:

    Elegant and simple trick worth sharing. It doesn’t have to be advanced to be worth publishing ☺

  2. […] Fraudy shows how to dynamically highlight a data point on your chart, without using […]

  3. u.yasar says:

    I have a question about data labels. When i choose a month from the list, i see the label belongs to the selected month but also i see zero for the other months. How can i hide the zero labels? thank you

    • MaxFraudy says:

      for that, you’ll need to use custom format such that zeros are not displayed. you can have a look at how i have used them by downloading the file. just right-click on the data labels and look at their format.
      hope that makes sense

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s