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.

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

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.

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:

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.

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.

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.

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.

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

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!):

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!

     

Hope this helps!

+Alesandra Blakeston

Create a series of traffic light charts

I’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.

The 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:

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!

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

You may get a blank pie chart.

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

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

You 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:

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

I’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.

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.

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:

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.

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

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:

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:

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

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

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.

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:

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.

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

Create a block bar doughnut chart

While not a big fan of pie charts and doughnut charts, when I am sent a challenge, I like to follow through.  The challenge was to create a doughnut chart in Excel for use on an infographic with block bars like the picture shown below.

As usual, it’s not straightforward to do this in Excel.  Creating the doughnut chart is relatively easy, but creating different sizes for the slices is not built in.  In fact, I had to cheat.  First I increased the hole size and then I adjusted the size of the rings for each series by setting the line thickness of the main slice to really thick and having no line on the other in order to get the effect.  You can download my example here.

There’s a disadvantage to this approach. Regardless of what type of cap you choose, (round, square, or flat) the thickness of the line makes the slice appear slightly bigger than it actually is.  If you are only displaying one figure, with a data label as shown above, it’s ok, but it isn’t perfectly accurate.

The options you can choose in Excel for the join type are shown below.  I chose a square cap, with a miter join.

I’d actually be interested in knowing if there’s a better way of doing this in Excel – perhaps vba?  If you know of a better way – drop a comment below.

Hope it’s useful.

+Alesandra Blakeston

Broken column chart with a stacked bar chart

A long time ago now, I wrote a “how to” guide for creating a broken column / bar chart.  Since then I have been inundated with requests for variations on this chart.  The one I get the most often is “How can I create a broken column when I want to show more than one data set (and therefore already have a stacked bar chart before I add in the break and after columns).  Note: If this doesn’t make sense – allow me to explain!

The original post creates a broken column chart using a standard stacked column chart to create the before, break and after series combined with an XY scatter chart to create the custom axis as shown in the picture below.

However, if you want to have multiple data sets and already have a stacked column, it can make altering the template file I gave difficult.  The request I liked the most showed land area and water area for each US state.

As you can see there is a large gap between Alaska and the other states in terms of total land mass.  What we need is a chart more like this:

It makes it a little easier to see some of the detail than the earlier chart!  Of course, you could have the break at any point, or have multiple breaks, so long as you can do the calculations.

The answer to how to do it is all in your preparation.  Work out where you want the break to be and plot your basic data first – including your multiple series. Then once you have your stacked column data you can work out your custom axis using the data labels.  I’ve attached my work to create the graph above here, so hopefully those of you who want to use it can alter it accordingly.  If you need the basic how to, don’t hesitate to go back to the original post!

Hope it helps!

+Alesandra Blakeston