Yamazumi charts

The Yamazumi chart – also known as the operator line balance chart – is a load chart that breaks down the individual work tasks in a process, detailing the time they take. Obviously, it’s used in lean six sigma to good effect. You can download my version of the chart here.

While there are lots of different ways to plot this data, often a stacked bar chart is used (especially in Excel), usually in combination with another chart to show the % Waste, VA and NVA. The point though, is to help you to identify Value Added activities (VA), Non-Value added Activities (NVA) and Waste activities, so that you can work on reducing NVA and Waste.

Having been asked to help a colleague with a lean dashboard I did some research on the subject and found some great examples online. One chart in particular by Adaptive Business Management Systems is particularly good having a stacked bar chart with a pie chart to show the 2 sets of data. You can download their version for your personal / commercial use.  It even comes loaded with vba to update the chart as needed.

excel yamazumi chart

There are even online versions, like this one by Lean Lab.

yamazumi chart

Personally though I’d prefer to have all my information in one chart, have it organized and show very simply where I need to concentrate. It took a bit of work, but I came up with this version, a stacked bar chart with the VA, NVA & Waste plotted on the secondary axis behind the individual tasks. Basically any task that falls within the red (Waste) or yellow (NVA) areas needs to be eliminated / reduced. I’ve put the task name and category in the data label to make it clearer, similar to the ABMS version above.

yamazumi chart new

From the chart it’s clear to see that process 3 is already streamlined, whereas processes 1, 5 and 6 need the most work.

You can download my sample workbook and chart here if you wish to use it. Let me know what you think! Obviously this is still in draft form.  I need to add vba to make it update automatically as new data is added and I’ll probably tweak the formatting further – remove the visible secondary axis for example, but overall I’m pleased with the results.

Hope it helps!

+Alesandra Blakeston

Coin chart in Excel 2010

Whether you are presenting total spend, or simply analyzing spend over time, using the right chart is always key. If you are a regular visitor to my blog, you will have seen the dollar chart & total spend chart I created a while back:

Create a dollar graph using an overlay image as the fill of a series plotted on the secondary axisdollar chart 3

Both of these charts show the breakdown of expenditure over a set period. However one thing I haven’t shown you (yet) is a chart showing spend over time like the one below. This chart uses a simple coin edge graphic to show the amount spent each month.

Coin chart

I’m not going to post the how to, as I think it’s pretty simple, but it just shows you what using the right graphic can do to help present your data.  Without saying a single word you can tell immediately that this chart is about money…

Hope you like it, you can download the example worksheet here.

+Alesandra Blakeston

Animate a single series in a chart

Last week I posted a simple video showing a couple of graphs that had a single (or couple) of series animated for emphasis.  I thought this week I would post the how to!  It’s actually very simple.  You can download my PowerPoint here.

Adding an animation to a PowerPoint Chart is the same as adding an animation to any other object.  Click on the chart, then click on “Animation” on the ribbon and choose the animation you want to add.  (I chose “Wipe”).  You can then adjust the options for the animation using the “Effects Options” button.  Unfortunately when you add an animation it adds the animation to the whole chart – including the axes etc.  To see this, while the chart is selected click on the “Animation Pane” button in the animations toolbar.

animation pane

To be able to adjust the animation to the different elements of the chart, right click on the animation, and then select effect options > Chart animation.

chart animation

To be able to animate the series individually, click on the down arrow on the right hand side of the text “As one object” and select “By Element in Series” or “By Series” depending on the configuration of the chart.   I also deselected the “Start animation by drawing the chart background” check box.  Then press the OK button.

A double down arrow will now appear in the animation pane underneath the animated chart.

double down arrow

Click the double down arrow to see the individually animated series.

4 series animated

My chart has 4 series, so there are four elements that have been animated.  Since in my chart I want to emphasize series 3 and 4 only, I simply click on the ones I don’t want to see and press the delete key.  You can then adjust the options of the 2 animated series so that they start together or individually as you prefer.

I adjusted my charts so that the animation starts as soon as the viewer navigates to the page (using the start with previous option) and so that both lines start together.  You can do this using the down arrows next to each animation in the animation pane.  Click on the down arrow and then select “Start with previous”.

And that’s it – hope you find it useful.

+Alesandra Blakeston

Using animation with charts

Recently I posted a blog on how to simplify charts so that the key message came across simply by looking at the chart.  I got a few messages after the post from colleagues saying that while the overall message of “Less is more” is great, what about adding emphasis to really sell your message?  Can you animate charts?

Well the simple answer is yes, of course you can animate charts.  That being said, I would immediately qualify it with – less is still more.  You don’t want to emphasize the entire chart and every single data point.  The short video below shows 2 charts that have been animated.  The first is a column chart.  The last series has been animated to emphasize the improvement.  The second is a line chart where the progress of the last 2 series have been animated.

Let me know what you think!  I’ll be posting the how to do this later next week, but until then you can download the PowerPoint presentation here.

Hope this helps

+Alesandra Blakeston

Less is more

In the business world I come across some really awful charts.  Take for example the one below (I’ve changed the categories and title to protect the innocent!):

awful chart

What exactly is the message behind this chart?  What data is important to know?

It turned out that the “bird” data was important.  The category had increased by 60% in the last month.  Looking at that chart can you tell that the “bird” category has improved?  Can you even tell that the “bird” category is important?

If you want to show an improvement, plot the before and after.  This can be done very simply using a line chart with the before and after being plotted, or even as a stacked bar chart.  Either of those alternatives would have presented the data in a better way.  Leaving aside the incorrect chart choice, and the improvement for now though, how could you simplify the above graph so that the important data is being shown?  Have a look at the video below to learn more.


I leave it to you to decide which is the better version!

awful chart     better chart

Hope this helps!

+Alesandra Blakeston

Create a series of traffic light charts

datapoint-3-add-new-connectionI’m always on the lookout for new and interesting ways to present data in Excel and PowerPoint. So when I saw this DataPoint add in for Powerpoint I was definitely interested. As I looked through the site and of course their blogs, I stumbled across this post: “Traffic Lights as Dynamic Semaphores” and was immediately inspired. Don’t misunderstand me, DataPoint is a very sophisticated add in for PowerPoint and does a lot more than just this one chart, but it got me wondering… Do I need the software to create this effect? Could I recreate this manually and still have the traffic lights update when I adjust the data?

If you’ve read any of the posts on this blog, you’ll already know that I love a challenge, and so off I went. The idea was to create a series of traffic lights that show a warning light (orange) when the stock level is the same as the reorder level, a danger light (red) when the stock is lower than the reorder level and of course a full steam ahead light (green) when the stock levels are healthy and therefore above the order level for that product.  As usual I started in Excel and created a data table.

table 1The figures above are just an example and if you want to recreate this using your own data, you can download my worksheet here.

Then I needed to create some calculations to plot the different graphs.  So I added three columns labelled “Red”, “Orange” and “Green”.  Using IF statements, I then got excel to calculate whether the traffic light should be red, orange or green by putting a 1 in the appropriate column. The three calculations I used can be seen below:

Assuming your titles are in Row 1 and your stock column is in Column D and your reorder column is column E:

  • Cell F2 =IF(D2<E2,1,””)
  • Cell G2 =IF(D2=E2,1,””)
  • Cell H2 =IF(D2>E2,1,””)

Copy and paste the calculations down the table and then your table should look like the one below:

table 2

As you can see, the stock for product ax234 (row 2) is higher than than the reorder level so there is a 1 in the Green column for that row.  Product ss213 on the other hand has less stock than the reorder level, so there is a 1 in the red column.  And so on.

As it stands though, all we have is a nice table.  We could use conditional formating and create a table like the one below, but it doesn’t really look like traffic lights.  In theory, we could change the formatting of the cells etc., change the direction of the table so that it works, but I wanted something a little more flash!

table 3

What I did was create a series of 3 pie-charts for each line.  Two would be blank as there is no number in their cells, the third would be filled and create the “light”.  Then behind the three pie charts, I create the illusion of the traffic light. Again, if you want to see more, you can download the worksheet here.

How is it done?

Step 1 – create a basic pie chart

With cell H2 highlighted, click on Insert > Pie > 2-D Pie > Pie

insert chartYou may get a blank pie chart.

original pie

If this happens, right click on the chart and choose “Select Data”.

select data

Click on the “Edit” button and then adjust the fields as shown below and press the OK button:

edit seriesYou should then have a filled pie chart. Delete the legend and title. Then adjust the fill of the chart and the background to “No fill” by pressing Ctrl+1 to bring up the formatting menu box and adjusting the parameters. Finally remove any border on the chart by selecting “No line” on the border color. Your chart should now look like this:

new pie chart

You can then adjust the fill of the pie chart so that it has a green radial gradient as shown below:

green gradientI’ve chosen a vivid green that changes gradually to a darker green with a radial gradient radiating from the top left corner.

Finally adjust the position and size of the pie chart so that it is perfectly centered inside your rectangular chart background, taking up as much of the space as possible.  You do this by dragging the corners of the pie chart to the edge of the background.  Once done, you can adjust the size as you wish, just keep the aspect ratio squared i.e. length and width the same to ensure you have a perfect circle.

Step 2 – create 2 more pie charts

Copy and paste the pie chart you have just created and position the new chart above it.  Repeat so that you have three pie charts on top of each other.  You can use the align commands on the Drawing tools tab to ensure that they are vertically aligned and correctly spaced.

three green lights

We need to adjust each pie so that it is looking at the data from the correct cell.  Click on the topmost pie chart.  The table should now have lines on it indicating which cell the pie chart is taking its data from.

cell reference

Drag the blue square from cell H2 to cell F2. The pie chart should update and appear blank. Repeat for the middle pie chart, dragging its blue square from cell H2 to cell G2. Your three lights should now look like this:

updated lights

We need to adjust the fill of the top and middle lights. It’s easier to do this when there is data in there, so TEMPORARILY, put a 1 in cells F2 and G2. The fill should reappear in the pie charts. Click on the top most pie chart again, press Ctrl+1 to bring up the formatting box and give it a red radial fill.

red radial fill

Then click on the middle pie chart and give it an orange radial fill. Your charts should now look like this:

three lights

Once you’ve adjusted your fill, don’t forget to put the calculations back into cells F2 and G2. The fills will disappear again, but that is normal!

Step 3 – Create the background and highlight

To give the traffic lights a real flair, I added a black rounded rectangle behind the three lights. I also added 2 circles as shown below:

additional shapes

The first circle is black with 40% transparency.  The second is red with a red 12pt outer glow. When put together they look like this:

background red

I copy / pasted the 2 circles twice and adjusted the glowing circle to be orange and green for the other two “lights”.

traffic light background

All that’s left is to position the three pie charts on top of the background and you have your first traffic light.

traffic light 1

You can see that the green light shines brightly, showing that for the product in row 1 we have plenty of stock.

Step 4 – Repeat for the other rows

Before doing anything, click on the home tab of the ribbon and then choose Find & Select > Select Objects.

find and select

This will let you select all of the objects that make up the traffic light by dragging your cursor over them.  Once done you can press Ctrl+C to copy the traffic light and then Ctrl+V to paste another one.  Click on the first “light” and update the cell reference as shown in step 2 above.  It should now be looking at cell F3. Repeat for the other 2 lights, for cells G3 and H3.  You should now have 2 traffic lights for the first 2 rows.

Keep copying and pasting and adjusting the cell references until you have a traffic light for every row:

traffic lights

And that’s it! You can download the worksheet here.

Hope you find it useful!

+Alesandra Blakeston

Create a step chart

If you don’t know what a step chart looks like, it looks like the picture below.  Step charts are a specialized form of line charts that use only vertical and horizontal lines to connect the data points.  These charts are used when the data is not continuous in nature but characterized by a jump from one level to another.  They are preferred over line charts in such cases because they form a step-like structure and bring out the intermittent pattern (the rise and fall in data) better.

Step chart

While Excel doesn’t have this as an in-built chart, you can create one using horizontal and vertical error bars as shown in this post by Peltier Tech.  His method creates the chart using an XY scatter chart.   He also wrote this post for older versions of Excel, but it’s pretty self explanatory nonetheless.

While it’s a great post, I’ve added a custom X-axis, as due to the use of an XY scatter chart, it is impossible to show the dates in months (as shown in my picture). You can download my sample chart here.

Hope it helps.

+Alesandra Blakeston

Highlighting the difference between actual and target

Sometimes it is important to know the performance against a target.  There are of course lots of ways of doing this, my favourite being bullet charts.   That being said, you can also show this with line charts if you have a lot of data points over time for example.


If you are a regular reader you will have seen an example of this chart last week in this post.  I promised I would show the “how to” and here it is.  You can download the sample worksheet here.

Step 1: Set up your data table.

Your original data table with the actual and target columns will probably look something similar to the one below:

Original data table

Add three extra columns in between your actual and target columns and label them “Copy”, “Diff mid” and “Difference”:

new columnsIn the “Copy” column simply copy the data from the actual column.

In the Diff mid column, in cell D2, type in the following calculation:


In the “Difference” column, in cell E2, type in the following calculation:


Copy and paste the calculations to the last row in the table.

Step 2: Create the line chart

Highlight the whole table and click on Insert > Chart > Line > 2-D Line > Line

insert line chartA chart like the one below should appear:

line chartDelete the gridlines and legend.

Step 3: Format the chart

Add the drop lines

Click on the “Target” line (the top most line in the chart), then click on chart tools > Layout > Lines > High-low lines.  Your chart should update as shown below:

high-low lines

Format the lines as you wish by clicking on them and pressing Ctrl+1.

format high-low lines

I changed the width, made them dotted and changed the colour to match the target line.  My chart now looks like this.

formatted high-low lines

Format the remaining lines

Delete the “Difference” series.  You aren’t going to need it as a series.

Then click on the “Copy” series and press Ctrl+1.  Move it to the secondary axis.  Repeat for the “Diff mid” series.

format data series

Your chart should now look like this:

secondary axis

Adjust your secondary vertical axis so that the maximum is the same as your primary vertical axis.

adjusted secondary axis

You should be able to delete the secondary axis now.

Adjust the series on the secondary axis

You’re almost there.  Right click on the “Diff mid” series and select Change series chart type.

change series chart type

Change it from a basic line chart to a stacked line chart and press the OK button.

change chart type

Your chart should now look like this:

stacked line chart

Click on the “Copy” series (red line) and press Ctrl+1.  Change it so that it has no line (it’s a duplicate of the blue line which is currently hidden).

Before closing the formatting box, click on the “Diff mid” series and also give it no line.  Then press the Close button.  With the series still selected, click on Chart tools > Layout > Data labels > Center.  Your chart should update as follows.

data labels

Update data labels

Unfortunately, these aren’t the labels we want.  The labels we want are in the “Difference column”.  So click on the chart and choose “Select data”

select data source

Because the “Diff mid” series is plotted on the secondary axis, it can have different horizontal axis labels to the “Actual” series and the remainder of the graph.  So click on the “Diff mid” series and then click on the Edit button for the horizontal (Category) Axis Labels.

Change the reference to the “Difference” column:

='Target vs actual'!$E$2:$E$9

Then press the OK button twice to close the menu box.

Click on the data labels and press Ctrl+1 once more.

In the pop up menu box that appears, deselect the Value checkbox and check the Category name checkbox.  Then press the close button.

format data labelsYou can also format the data labels as you wish (change the colour, the fill, font size etc.)  Your finished chart should look something like this:

cool chart

Hope it helps!

+Alesandra Blakeston

Related posts:

Conditional formatting intersect area of line charts

area filled intersect

Conditional formatting intersect area of line charts

So late last week a colleague came into my office and started doodling on my whiteboard.  He wanted to know if I had a template for showing positive and negative intersect areas of line charts.  Something like this:

area filled intersect

The idea is that when the blue series achieves better results than the red, the colour is blue; when the red series is ascendant, the colour is red.  It’s actually not easy to do.  You can’t just create a positive and negative series and then one underneath that has no fill.  Trust me, I’ve tried.  Better yet, try it yourself, you’ll see exactly what I mean.

The good news, however is that I did have a template.  The work, although amazing, isn’t mine.  It is in fact work by David Merle Montgomery, shown on his blog david @ work, which in itself is inspired by work by the amazing Jon Peltier.

Hat’s off to both of them for the great work.

The link to the original work by David no longer works, but fear not, here it is.

Hope it is useful!

+Alesandra Blakeston