fbpx
Search
Close this search box.

Hourly Goals Chart with Conditional Formatting

Share

Facebook
Twitter
LinkedIn

A while back I developed a solution to a Chandoo.org Forum question, where the user wanted a 4 level doughnut chart where each doughnut was made up of 12 segments and each segment was to be colored based on a value within a range.

HGC01

You can read the original post here: http://forum.chandoo.org/threads/hourly-goals-chart.30621/

This post will examine the techniques I used for the solution.

Data

Download the sample file: Download Hourly Goals Chart File

HGC02

The first thing to note is that there are 4 column of data, one for each measure of Safety, Quality, Delivery and Cost.

Secondly is that each measurement has 12 values representing the times from 4:30 am to 3:30 pm.

We need to setup a Doughnut Chart with 4 layers of 12 segments each

The easiest way to do this is to replicate the data area, but fill it with the same value in all cells,

I choose 1, but as long as all values are the same value, it can be any value

HGC03

Add a Doughnut Chart

Select the Range A16:E28

Goto the Insert, Chart and select the Pie/Doughnut menu

HGC04

HGC05

We have a bit of work to do yet to get the charts format correct

First select the chart then select the Chart’s Legend and press Delete

HGC06

Next with the chart still selected, Right Click on any Doughnut and select Format Data Series

Set the Doughnut Hole Size to 25%

Do not change the angle of the first slice

HGC07

Right  click on the Outer Doughnut and select Add Data Labels, Data Labels

HGC08

Right Click on any Data Label and Select Format Data Labels

Tick Value From Cells, Select a range A17:A28

Untick Value

Untick Leader Lines

HGC09b

Now manually click and drag each data label outwards to its final location

HGC10

 

Finally set the Border Color for the doughnuts

Right Click on each Doughnut in turn

Set the Doughnut’s Border Line to a Grey Color and a 2 Pt line size

HGC11

We can now add a text box for the Doughnut Labels

With the chart selected, goto the Insert, Text Box menu

Drag a Text Box inside the chart

Right click on the Text Box and edit Text and type in the value Cost

Now repeat this for the other 3 Doughnuts

HGC31

 

Connect the Doughnut Segments to the Data Area

We now have a basic Doughnut chart with all the facilities we require.

Unfortunately, Excel doesn’t have a built-in Conditional Formatting option for charts.

So we will need to develop a system using some simple VBA.

Understand the Doughnut Chart

To write a piece of code we will need to loop through each segment of each doughnut and reference it back to the source data area

Then use some code to set the fill color

then repeat for each segment

 

To do this we need to understand which doughnut is which column of data and which segment in the doughnut is which time period

First select the inner Doughnut, Note that when you select it, Excel highlights the Safety Series as well as showing the Series Number in the Formula Bar

HGC12a

Repeat with the outer Series and you will see that Doughnut 4 is connected to the Cost Data and is series 4.

HGC12b

To determine which segment is which goto cell E17 and change the value from 1 to 2

HGC13b

So we understand that the series go from Value 1 to 4, Inner to Outer Doughnuts and that the segments go from value 1 to 12 clockwise, starting to the right of 12 O’Clock.

Finally select the Chart and make note of it’s name.

The Charts Name is shown in the Name Dialog above cell A1

HGC16a

Now for some VBA

Lets start by first manually recording a macro in VBA and we will then edit and add to the macro to get our final result

Start the macro Recorder by Pressing the Macro Button in the lower left corner of the Excel Window

HGC14a

Note the Macro Name, which is most likely Macro1 and press Ok

Now everything that you do is being recorded by the Visual Basic Editor (VBE)

Select the Outer Doughnut, then select Segment one, then Right Click on Segment one, Format Data Point

Select the Fill & Line menu

Set the Fill to a Solid Fill and Select a Color Red

You can now stop macro recording by pressing the Macro button again

HGC15a

 

Lets look at our code

To change to VBA press the Alt+F11 button

You should have a screen similar to this:

HGC17

Take note of the above.

We can see that we have a Macro1 subroutine, located in Module 1 of our Excel file.

If you can’t see a Properties or Immediate window, don’t worry.

Looking at the VBA Code we can see

HGC18

  1. That the chart is called Chart 1
  2. We selected Doughnut 4, the outer doughnut
  3. We selected the first segment in Doughnut 4
  4. We set the Fill Color of Segment 1 to Red  = RGB(255, 0, 0)

So this little bit of code will form the basis of our macro

What we need to do next is to place that within 2 loops, one loop for the Doughnut and one loop for the Segment

So lets do that:

HGC19

You can see above that we have initialised two variables Doughnut and Segment as Integers

We have setup two loops, one for the Doughnut which will loop from 1 to 4 and a second loop for the Segment, which will loop from 1 to 12.

We can now use these variables within the code to reference each Doughnut / Segment as relevent

HGC20

The next thing is to add lines to lookup the value of the measure in the original data table.

We can use our variables to assist us with this:

I have added a new variable declaration myVal and declared it an Integer as it is only storing the values from, 0 to 3.

Then we retrieve the value from the data area by using a Range(“”).Offset(Row,Column) combination.

We know that the segment loops from 1 to 12 and this is the Row Offset in each Doughnut.

The Doughnut loops from 1 to 4 and this is the Column Offset from the cell A1

 

 

HGC21

Next we need to allow for each fill color remembering that the data area has a legend

HGC22

We could loop from a value of 0 to 3 and check the new variable myVal against each value and set the color.

But VBA has a Select Case function which is ideally suited to this task

HGC23

A also took the opportunity to streamline the Chart selection process in the previous step

That allowed the use of the With Object construct, allowing the Select case to use the myVal to apply different colors to the fill property of each segment

 

At this stage we can run the code, by simply pressing F5 in VBA

HGC24

 

We can change the code to allow it to update automatically when Data range changes

To do this we need to shift the code to a Sheet1 Code Module associated with Worksheet Sheet 1

HGC26

Note above that the code is now located in a Private Sub Worksheet_Change event. This means that the code runs whenever worksheet1 chnages.

The next line If Intersect(ActiveCell, Range(“B2:E13”)) Is Nothing Or Target.Count > 1 Then Exit Sub

Checks whether the cell that changed was not part of our Data Area or that multiple cells were selected.

If either are are true the macro ends

Then finally I removed the MyVal calculation and made it part of the Select Case function.

because we aren’t using myVal I removed the Dim myVal statement

We can now also remove Module 1, right click on it and Remove Module.

Save the file and return to Excel with Alt+F11

You can now change any cells in the data area and the macro updates the chart accordingly

Can we tidy up the layout of the worksheet?

Although we now have a fully functional model, we are stuck with an ugly worksheet layout because our template of 1’s is being used to support the framework of the 4 Doughnuts in the chart.

What if there was another way to achieve that?

Well there is.

Firstly, we could simply shift the range A18:ER30 well away from the Chart and data area or even move it to another worksheet.

HGC27

This will work, but risks a person adding data, rows or columns and messing up the layout

But there is a better way

I am going to add 4 Named Formula to the worksheet, one for each Doughnut

Goto the Formula, Name Manager Tab and add 4 Names as listed below:

_Safety      =1+(ROW(OFFSET(Sheet1!$A$1,,,12,1))-1)*0

_Cost        =_Safety

_Delivery  =_Safety

_Quality  =_Safety

HGC28

The 4 Names now contain an array of 12 x 1 each with a value 1.

We can use that to link the Doughnuts to instead of the Physical Range

Right click on the chart and Select Data

HGC30

Select each Doughnut in term and Edit

Change the Series Name to Row 1 and insert the Names into the Series values dialog.

Note that the formula must include the Worksheet name =Sheet1!_Safety etc

HGC29

Repeat this for the 4 Series

You can now select the framework range: A18:E30 and press Delete

HGC32

The chart remains intact and is now supported by the Named Formula

Change some values in the Data range at the top and the Chart updates as it should.

You can download the final version of the file here: Download Completed File

Final Thoughts

The technique applied to the doughnut chart above can fairly easily be modified to any chart type or in fact any other shapes.

Let me know what you think in the comments below:

ps: This has been one of my hardest posts to write, simply because Microsoft has misspelt Doughnut. In my native Australian English it is Donut.

 

 

 

 

 

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

13 Responses to “Hourly Goals Chart with Conditional Formatting”

  1. Ankur says:

    What are some real world examples where such charts are used?

    • Hui... says:

      @Ankur

      To me this is part of a dashboard showing radially the progress of a measure against time. And linearly the snapshot of 4 measures at the same time.

      The OP never explained the requirements/use.

  2. milang says:

    The doughnut chart doesn't add value here.
    This could be a table, where each column would represent Time.

    It is easier to track rows trough time then to go around the doughnut chart.

    • Hui... says:

      @Milang
      The idea of the post is to demonstrate a Technique, and nowhere Do I imply that this is a must use chart

      If you read the original post, I offered exactly what you quote, a Column Chart. Having used this chart now for a few weeks, I find it naturally really easy to work with.

      In a real world environment I would change the start point so that 4:30pm is at 4:30pm, not at 12:30

  3. Bob says:

    This seems like a lot of effort that results in a very confusing chart.

  4. Ranjith says:

    This is a novel way of representing data. And the coolest and most difficult one I have seen.
    @Chandoo, I noticed that successive editing of the table columns was not possible, as after each cell edit, the chart gets active. This one line in the macro

    'ActiveSheet.ChartObjects("Chart 1").Activate

    is actually not required. Commenting this out still updates the chart.

    Awesome work, as always, Chandoo!

  5. Kiki says:

    This is interesting, but I got lost w/VBA portion. I have no knowledge of VBA. I was a little unsure as to when you would use this doughnut chart, but it was good read, to enhance critical thinking on how to visualize data. Thank you!

  6. Jeff says:

    Excellent! Great ideas in this, I always believe sharing original out-of-the-box thinking with application is as Done here. Great concept to use for managing your Labor, FTEs, UOS, etc. on a daily or per shift

  7. Anne says:

    Really a doughnut chart? Is that not just a pie chart with a hole in the middle and we were told in so many posts that pie is for dessert only?!? 🙂

    • Hui... says:

      @Anne

      Pie charts have there uses but this isn't one of them

      In this chart every wedge is the same size, they are markers for a particular time, they are not being used to compare measurements as a pie chart does.
      As a dashboard I have come to like this layout.
      It allows you to see the change of 4 KPI's over time as well as the status of the 4 Measures at any time

      As I said in the post the post is about the techniques used to get the result, how you use or don't use these techniques is entirely up to you.

  8. Leon-K says:

    Excellent post Hui, thank you, I for one am always looking for new and innovative ways to utilise these types of chart.

    As a BI developer, I prototype dashboards in Excel before incorporating into a given BI platform such as SAP, Microstrategy, SSRS, etc. There are 2 instances where I'm requiring to use doughnut and pie charts. The first is when an external client insists on having them, regardless of opinion on their efficacy or the gospel according to Stephen Few. The second use is when I'm asked to create visually engaging dashboards with an emphasis on 'eye candy'; Chandoo (12 Apr 2013*) wrote an excellent blog on the dangers of such charts, demonstrating how 3d Pie charts could 'hide' specific insight.

    * Chandoo, 12 Apr 2013, 'Some charts try to make you an April fool all the time (or why 3d pie charts are evil)'.

  9. Pandu says:

    Thanks Hui! This can be used to represent the site KPI metrics. Gold!

  10. Adrián Chiogna says:

    I want to provide a different application. A 3d heatmap that allows to visualize the rotation that have different positions in a warehouse:

    A 3D Warehouse Heat Map in MS Excel (by Adrián Chiogna).
    https://es.slideshare.net/HECTORADRI/a-3d-warehouse-heat-map-in-ms-excel-by-adrin-chiogna

Leave a Reply