Customize highlighting series 1

Highlight specific¬†data series in your chart --> Set it up in your dashboard --> Impress your boss --> Get promoted faster ūüėÄ

Download the Start File

1. Select the data (assume salesmen and sales figures) and draw a clustered column chart. Skip to point 2  if you know how to make a clustered chart

  • Select the data
  • Choose clustered column chart in the Insert Tab
  • Format the Chart – Add the data labels, hide¬†the axis, give a suitable title

 

Customize highlighting series 3

 

2. Create the drop down for choosing the salesmen. The option is available under the Data Tab --> Data Validation --> Choose the List Option --> Select the source of the list

Customize highlighting series 4

 

3. Make a dummy series for defining the height of the highlighting box. Use the following formula in the cell next to sales value =IF(B4=$F$2,1100,0)

Customize highlighting series 5

 

4. Add the Dummy Series in the chart and in the Format Data series dialogue box (opens with Ctrl+1 on selecting the data series) put the new series on the secondary axis and set Gap Width option to 0%

Customize highlighting series 6.jpgCustomize highlighting series 6 Customize highlighting series 6

 

5. Also hide the secondary axis РSelect the axis, Right click --> Format Axis --> Labels --> Label position --> None. Set the outline of the dummy series as red dashed thick outline (follow the graphic below) and make the fill as no fill.

Customize highlighting series 7

If you are an Excel 2007 or before user, you will find the above options in Format Tab (appears when you select the chart)

6. Now simply change the drop down to see the effect ūüôā Download¬†Solved File

That’s it for now .. take care!!¬†Please share your secrets of highlighting any particular data series in the chart

 

Also Learn

How to highlight max and minimum data points in your chart

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI