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

Customize Line charts for added analysis

Ever wanted to plot 3 characteristics of a series of data in the same line chart?  So for example you want to show:

  • The period of time
  • The number of persons who attended a training course
  • The percentage who passed the course

You could of course plot this with two lines by calculating the number or people who passed the course:

initial line chart

However it is also possible to do this with one line…

Step 1: Plot your initial data for the number of attendees

Set up your data table as shown below:

Data table

Highlight the data in columns A and B and click on Insert > Line > Line (the first chart available in the drop down menu box as shown below):

insert line chartYou’ll see a chart like the one shown below:

Attendees chart

Format the initial graph as needed so that the no of attendees is displayed as you wish.

Step 2: Adjust the data points to show the third variable

First click on the initial data series (Attendees) and press Ctrl+1 to bring up the formatting menu box as shown below.  Then click on the Marker options tab.

marker options formatting menu box

Ensure that the Marker Options are set to “Built-in” and change the marker to either a circle or a square as you prefer.

A: Change the size of the individual data points

Double click on the first data point on your graph.  If you have closed the formatting data menu box after the previous step, open it again by pressing Ctrl+1 again (ensuring that the first data point is selected).  The title of the menu box will now say Format Data Point rather than Format Data Series:

format data point

According to my data table, the % passed for the first data point, is 70%.  I am therefore going to change the size of my first data point to 35 (half the size, though you can use the full size if you wish).  To do this type in 35 into the Size data entry field.  Then click on the next data point and change its size and so on until you have changed them all. Note: The size has to be a whole number.  If you original data is an odd number e.g. 75, half would be 37.5, so round it up to 38.

Your chart should now look similar to the one below, (kind of a cross between a bubble chart and a line chart):

Adjusted data pointsB: Change the colour of the dots to show Positive and Negatives

In my chart I want to be able to see at a glance when the pass rate of the training is below a certain percentage (e.g. 75%).  To do this, click on the first data point again and with the formatting data point menu box still open, click on the Marker Fill tab.  Then click on Solid fill and change the fill to your desired colour.

change desired fill colour

Repeat for any data points you want to highlight

Variably filled data pointsOnce complete, close the menu box by click on the close button.

 

C: Add individual data labels to selected points

You might want to explain why some points are lower than others.  In this case, click on the series and then click on Layout > Data Labels > Below.

Click twice slowly on any data label that you want to delete and then press the delete button on your keyboard.  Repeat as needed.

Then click on any label you wish to add custom text to and then type in your new label into the fx text box.

Fx text box

Reposition the label by clicking on it and moving it.  Repeat as needed.  Your graph should look like the one below:

Edited data labels

And that’s it!  Hope it helps!

+Alesandra Blakeston

 

 

Very hidden Excel Sheets

Have you ever been frustrated because you cannot find the sheet an Excel calculation is referring to?  It’s not a variable listed in the name manager, it’s not hidden in the normal manner, so where on earth could it be!  I give you Excel’s “very hidden” worksheets.  Sneaky, no?

In actual fact a worksheet can be visible, hidden or very hidden.  Before we look at very hidden sheets, though, let’s go over the basics.

Hiding / Unhiding a sheet – the basics

Hiding a sheet from view is actually very easy.  Simply right click on the tab and choose the “Hide” option.

right click hide

To make the sheet visible again, click on any of the remaining tabs and click on “Unhide…”  Of course, if no tabs are hidden this option will be greyed out (unavailable).  It will also appear greyed out if you have set a sheet / sheets to be very hidden, making it more difficult for a user to access or edit important calculations for example on this very hidden sheet.

Very hidden sheets

To make a worksheet very hidden or to make it visible again (in Excel 2010), you will need to access the Visual Basic Editor via the developers tab on the ribbon.  First if you haven’t got the developer tab visible on your ribbon, you will need to make it visible.  Step 1 of this previous article shows you how to do this.

Once the developer tab is visible, click on the “Visual Basic” button.

Visual Basic

This will open the Visual Basic Editor.

Visual Basic Editor

If the “Properties window” is not already open, click on “View” > “Properties Window” or press the function key F4.

view properties windowThis will open up the window as shown below:

properties windowIn the project window above the properties window, click on the sheet you want to make “Very Hidden”.  In my example, I have chosen sheet 2 as shown in the picture above.  The very last property of the sheet, is the visible property. There are three possible options.  Click once on the value of the Visible property to see the drop down arrow to access the different options.

three options

To make the sheet “Very Hidden”, choose the last option “2 – xlSheetVeryHidden”.  To make a very hidden sheet visible again, choose “-1 – xlSheetVisible”.  The second option “0 – xlSheetHidden” simply hides the sheet.  You can make it visible again by right clicking on any of the excel tabs and choosing “Unhide…” as shown earlier.

Remember: when a sheet is very hidden you cannot see it except in the Visual Basic Editor.  A user of the sheet cannot right click a tab and see it listed among the other sheets that are simply hidden.

Using vba to make a sheet very hidden / visible

If you are familiar with vba, you can of course use the following code to change the Visible property of a sheet instead.

To make a sheet very hidden: Sheets("SheetName").Visible = xlVeryHidden"

To make a sheet hidden: Sheets("SheetName").Visible = xlHidden"

To make a sheet visible: Sheets("SheetName").Visible = True"

Hope you find this useful!

+Alesandra Blakeston

20 Excel charts for your dashboards

Effectively displaying data is always a challenge. Making sure that the right data is being displayed is one thing, but making sure that the way it is displayed visually matches the message you want to send is quite another. I’m not going to go into what charts you should use and when, instead I am simply going to give you 20 charts to choose from. You can download the Excel file here.  Visually appealing, these charts should be great to use in dashboards or even infographics.

Enjoy

+Alesandra Blakeston

Total spend chart

A while back I posted a blog on how to make a dollar graph.  While this is a fun chart, what if you could actually use a dollar as a backdrop?  Something like the picture below taken from coj.net

cityexpensegfdollar

Can this be done in Excel?  Yes, definitely.  You can download the sample worksheet here:

Step 1: Set up your data table

For simplicity, I am going to use the same figures as the picture above, but of course you can use your own!  Incidentally, when I added the percentages up from the picture above, they actually totally 100.1%, so I reduced the first category by 0.1% to make 100%.

data tableStep 2: Create a stacked bar chart

Highlight the data table and click on the Insert tab of the ribbon.  Then click on the “Bar” button in the “Charts” section and select the “100% Stacked Bar chart”, (3rd icon on the top).

insert chart

Your chart should look like the one below, if not right click on the chart and choose “select data” to edit the chart.  If you have only 2 series and several categories, it’s because data has been plotted as rows instead of as columns.  Click on the Switch row / column button to correct it.  You can also remove any series that have been added by mistake.  Once you are done, close the menu box.

chart 1

You can then do some simple formatting.  Delete the legend, and the grid lines.  Double click on one of the series to open up the formatting menu box, and then adjust the chart so that there is “No Gap”.  I also made each of the series have the same fill, with white borders. You don’t need to do the fill or the lines, by the way, but it may help you identify the “real” sections from the added ones in step 3.  Finally delete the two axes, so that all you have is a block of colour with white lines to separate the series’. Your chart should now look something like this:

chart 2

Step 3: Add the picture fill.

First you need to find a high resolution picture of a dollar bill (or similar).  I used this one from wikipedia.

Usdollar100front

To make the separations easier to see, we are going to create some additional series.  I’ve added a gap of 3 in this picture, but you can use any number:

data table 2

Once you’ve added these to the chart and put them in the right order, you’ll have a chart that looks like this: (Note, I’ve coloured the series that I want to show as part of the dollar bill in blue, the others in white).

chart 4

Then, with the chart selected, in the “Format” section of the ribbon, Choose “Plot Area” from the drop down and then press Ctrl+1 to bring up the formatting menu box again.

plot area

Choose the “Fill” tab, and then “Picture or texture fill”.  Click on the “Insert from File” button and navigate to your dollar picture.  Click the close button to finish.

formatting menu box

For the next step, change all of the blue filled series to no fill.  Your graph should now look like this:

dollar chart 1

It’s difficult to see the smaller sections on the left, so I’m going to change the fill of the chart and the separating series to black. (I’ve also removed any white borders).

dollar chart 2

Step 4 add the data labels

Click on the chart once more and on the “Layout” tab of the “Chart Tools” section of the ribbon, click on the “Data labels” button and then “More data label options”.  Choose to show the Series Name and the Value and put the label in the centre (we’re going to move them once they’ve been added). Click on the close button to add the labels.

Then click on one of the data labels.  Change its font colour to white, and position it above or below the dollar.  Repeat for the other labels that you need to show the sections of the dollar.  Delete any separation series data labels as these are not needed.  If necessary (for the smaller sections), add a white line to connect the label to the section of the dollar.  (Note, I’ve left the labels quite small, but you can adjust them as necessary).

And that’s it.  You can download the finished chart here.

dollar chart 3

Enjoy!

+Alesandra Blakeston

How to create demographic charts for infographics

Creating charts for infographics is not easy.  You need a chart that is simple and easy to read and one that doesn’t need a lot of text or explanation.  To that end I wondered if I could create this kind of chart using Excel…

finished grid

 

It’s actually relatively easy.  Let’s imagine that you want to create an infographic showing the percentage of households owning different pets. You have the data table as shown below:

table 1

We can also create a grid of squares 10 x 10 across by altering the width and column size of columns D to M and rows 7 to 16.  I have changed them to approximately 10 pixels across / down.  To make it easier to see, I also put a frame around the grid.  I then repeated this for each of the different types of animal.  I also added internal borders in white.  To do this I pressed Ctrl+1, clicked on the border tab and added black borders on the outsides and white borders on the insides.

grids

 

I then added numbers along the bottom and right hand side, one for each column / row.

numbers

 

 

To make the chart look cleaner, I then made these numbers white.

To add the fill, I added a calculation into each of the cells in the grid. The first cell calculation is:

=IF((D$17+$C16)<($B$2*100),”1″,””)

The $ symbols are there to prevent the row or column from changing as I copy paste the calculation across the columns and up the rows.  But basically if the sum of the two numbers in that specific row and column are less than the corresponding figure in the table, then to add a “1”, otherwise, to add nothing.

Copy and paste this calculation so that it fills every cell in the grid.

For the first cell, the calculation adds up the 0 from the left, plus the 1 from below to equal 1), and then compares that to the % household with dogs (36.2%), since 1 is less than 36.2, the figure 1 is put into the cell.  The next cell would add 0 and 2.  The cell above would add 10 + 1 and so on.

I then repeated this for the other grids, substituting $B$2 with the corresponding cell reference for each of the animals.  Each of the grids now contain lots of “1”s, however this is not very attractive, so lets add some conditional formatting!

Highlight the first grid and on the home tab of the ribbon, click on conditional formatting > new rule.  Click on “Format only cells that contain” and change the formula to say “equal to” and put the number 1 in the data entry text box as shown in the picture below.  Change the format so that the cell fill is the same colour as the text colour, I have chosen light blue for the dogs grid and press the OK button.

formatting

 

Repeat the procedure for the other grids using a different fill (and text) colour for each animal grid.  Your grids should now look like the pictures below:

coloured gridsAll that you need to do now is alter the text colour of the animals in the original data table to match the grids:

final table

 

You can of course add formatting and titles as you wish to make the charts easier to read, but the basics are there…  finished gridsYou can download the source file here.

Enjoy

+Alesandra Blakeston

 

Broken column and bar charts

It’s been some time since I’ve written a post on Excel graphs and charts, mainly due to a lack of inspiration on my part.  I have to be visually inspired with my charts and the ones that I usually use are all on my blog.  That being said, since hosting a webinar on Excel for the women’s network of the company I work for, I have been inundated with requests and as a result I am inspired again!

One of the requests boiled down to a mismatch of data needing to be displayed in the same chart.  I.E. one of the series displayed in the chart would be very large compared to the others.  Here is my sample data table:

data tableAs you can see most of the data is below 1,000 units.  The last series, (series 4) is just above 35,000 units.  If you plot that data as a normal column chart you will get something like this:

normal columnIt’s almost impossible to read the values for Series 1-3, 4 & 5.  What we need is a broken column chart.  By using a broken column, you can see both the detail at the bottom of the scale and at the top.  How is it done?  You can download the worksheet I used here.

Step 1 – work out what you want the chart to look like

The first thing we need to do is work out where the different groups of data lie.  We have one group of data that ranges from 100 to 900.  Series 4 on the other hand is just over 35,000.  So how could we display that data to get clarity?  We need to have a lot of room on the graph between 0 and 1,000 to be able to view the different heights of the columns.

Looking at the data, if we had a chart that had 0, 250, 500, 750, 100, a break, then 35000, 35250 and 35500 as the scale we would be able to see most of the detail clearly.  Something like the graph below:

broken column

Step 2 – organise the data

To do this, we need to do is make some calculations to work out how to draw the chart.  We want the break to occur at 1,000 units and restart at 35,000 units.  The minimum is zero units and the maximum 35,500 units.

scale break

Once this data is entered into excel, we can use the Name Manager to assign these values to formulae.  In my worksheet, the value for the break is entered into cell B15.  By clicking on this cell, and then clicking on the Name box, we can enter a name.  I’ve named this value SPLIT_BREAK_COL.

name boxI’ve entered similar names for the other values: SPLIT_MIN_COL, SPLIT RESTART_COL, and SPLIT_MAX_COL

Then we can do some calculations.  In your worksheet create three new columns: “Before”, “Break”, and “After”.  Assuming that your first row of data is in Row three, the “Before” column is column C, and that the “2013” column (the original data) is column B, your calculation in cell C3 should look like this:

=IF(B3>SPLIT_BREAK_COL,SPLIT_BREAK_COL,B3)

This means if the value in cell B3 is greater than the value of the break, then the cell should display the value of the break, if not, the original value.  If you copy the formula down the rows, every cell should show the original value except for series 4 which should read 1,000.

In the Break column, the calculation in cell D3 should read something like this:

=IF(B3>SPLIT_BREAK_COL,50,NA())

This means if the value in cell B3 is greater than the value of the break, then the cell should show the value 50, otherwise enter a #N/A value.  This value fo “50” will create the “Break” once graphed.

In the After column, your calculation in cell E3 should look like this:

=IF(B3>SPLIT_BREAK_COL,B3-SPLIT_RESTART_COL-1,NA())

This means if the value in cell B3 is greater than the value of the break, then the cell should make a calculation (the original value subtracted by the restart value, otherwise enter a #N/A value.  If you copy the formula down the rows, this will create the “remainder of the column.  Every cell should show #N/A except for series 4.

The table should now look like this:

New data table

Step 3 – Plot the table as a stacked column

First, highlight columns C, D and E and then click on the Insert tab > Column > 2D stacked column.

stacked columnExcel will create a chart as shown below:

graph 1Right click on the chart and then click on “Select Data” and click on the “Edit” button to adjust the Horizontal (Category) Axis labels.  Use column A as the range.

Delete the legend and gridlines by clicking on them and pressing the delete key.

We now need to format the Before and After series so that they are the same.  Click on one of the series and press Ctrl+1 to open the dialog box.  You can then change the fill color so that they match.

graph 2Make sure that the plot area fill is white and that the graph has no line (outline).

To create the break, simply insert a white rectangle by clicking on Insert > Shapes > Rectangle.  Then create the teeth by adding triangles filled with the same colour as the Before and After series.  Group the shapes together:

shape

Then once it is grouped, click on the group to select it and then press Ctrl+C.  Click on the break series (red in the chart above) and press Ctrl+V.  Your chart should now look like this:

graph 3

Step 4 – calculate the new Y axis and scale

To show the break Y axis, we will need to create it.  This is done by adding an XY scatter line to the graph.  First create three new columns: “Labels”, “Xpos” and “Ypos”.  Write in the labels you want (see step 1).  Leave a blank where the break should be.

Then to create the arrow on the Y Axis where the break will be, we need to fill the Xpos column.  I have put zeroes for every label except the break.  This will help to create a vertical line with an arrow.  I want the arrow to be around a quarter of the size of one of the six series, so I entered a value of 0.25 (assuming my scale will be 0 to 6).

The Ypos values will determine where the labels will lie vertically.  In the calculations in Step 2, we determined that the break would be 50 units in value.  To begin with the Ypos and the labels will have the same value. This will change when we hit the break.  To centralize the arrow we will put the “blank” label as the previous value + 25.  The first label after the break will be the blank label value + the other 25.  The rest is steps of 250 to match the earlier scale.

I’ve also added calculations to work out the minimum and maximum of the Y scale:

New scale

Step 5 – add an XY scatter line to the chart

Right click on the chart and choose “Select Data”.  Then click on the “Add” button.

Enter “Yaxis” as the Series Name and the Ypos data as the values.  In my worksheet that is:

=column!$E$13:$E$21

Press the OK button and then the OK button again.  The chart should update as follows:

graph 4Right click on the new series then press Ctrl+1.

In the new menu box that appears, choose to plot the new series on the secondary axis and press the close button.  The graph should update again and you should be able to see the new axis on the right hand side of the chart:

secondary axisRight click again on the new series and this time choose “Change Series Chart Type”.  In the menu box that appears, choose a XY Scatter chart with Straight Lines.

XY scatterThe chart will again update:

XY scatter chartRight click once more on the chart and choose “Select data”.  Choose the Y axis series and press the “edit” button.  Add the X axis values (xpos column).  In my worksheet this is:

=column!$D$13:$D$21

Then press the OK button and then the OK button again.  You will probably be no longer able to see the XY line on your chart.  This is because of the scale.

Click on the “Layout” tab in the “Chart Tools” section of the ribbon. Then click on Axes > Secondary Horizontal Axis > Show Default Axis.  Your XY line and a new Axis at the top of the screen will appear.

graph 5

Step 6 – Formatting

First if you haven’t already done so, make the primary vertical (left Y axis) and secondary vertical (right Y) axis match in terms of scale.  As you can see in the image above, I’ve given both of mine a minimum of 0 and a maximum of 1550 to match my calculations in step 4.  Once this is done you can delete the right hand axis.

Then format the (top) secondary horizontal axis so that the arrow is to one side only.  I’ve given mine a scale of 0 to 6.

Then double click the (left hand) primary vertical axis.  in the pop up menu that appears, choose to have no major tick marks, no minor tick marks and no axis labels.  Click on the line color tab and choose no line and press the close button.

tick marks

Repeat for the (top) secondary horizontal axis.  Your graph should now look like this:

graph 6

Double click on the XY line and format it to match the colour scheme you have chosen for the X axis.  In my case grey with a 1px line.  You can do this on the Line Color and Line Style tabs.

Step 7 – add the XY labels

Click on the XY line.  Then click on the Layout tab of the Chart Tools section of the ribbon.  Then in the labels section, click on Data Labels > Left.  You may need to re-position the chart so that the labels can be read clearly:

data labels chart

Unfortunately these are not the labels we want.  There are addins that you can buy to give you custom labels.  However, it can also be done manually.

Double click on the first data label.  Then in the function (fx) bar, type in the cell reference for the label you want to appear.  So if I am starting at the top most label (1550), I double click it and then in the function (fx) bar I type:

= C21

Repeat this for each label, including the one in the centre of the arrow, which should update to show a blank label.

Re-position those that are difficult to read by double clicking on them and then with the mouse button held down, dragging them into their new position.

Your graph should now be finished!  You can use the same technique with a bar chart as well:

broken columnbar chart

You can download the sample worksheet here.  Any questions, please don’t hesitate to post a comment!

+Alesandra Blakeston

Creating a score meter in Excel

As most people who know me will attest, I am a big fan of using gauge and bullet charts and / or altering the standard excel charts to make them more visually appealing.  So when I was asked by a colleague if I could create a gauge style graph, with an arrow as an indicator of the result (kind of like a linear speedometer), my exact words were, challenge accepted.

To make sure I fully understood what they wanted, I asked them to send me a picture.  This is what I received:

example

After further discussions, we realised that she wanted a percentage scale going from 1 – 100%.  The blue arrow would move based on the indicator for that month.  There are actually several ways that this chart could be made.  I chose in this instance to make a bar chart.  It could also be done with a column chart, though!  You can download my chart and worksheet here.

Step 1: Set up the data

In this instance, my colleague had only 1 data point she wanted to display, a YTD indicator.

indicatorHowever, to set up the coloured scale behind the indicator arrow, we would need more data to be plotted.

For this reason, I set up two more columns:

extra columnsThe first will be used to created the coloured scale.  In this case, the scale is even, but it doesn’t need to be, it is dependent on the visual aspect the user wishes to show.

The second column is a set of two calculations to create the width of the arrow.

  • E2 = B2 – 0.025 (this will find the value of the indicator minus half of the width of the arrow)
  • E3 = 0.05 (the width of the arrow)

Step 2: Create the basic stacked column graph

First highlight the data in column D, then click on Insert > Bar > Stacked Bar

stacked bar

The chart will probably look something like the one below:

1st chart

We need to adjust the way the chart is displaying the data.  Right-click on the graph and then choose “Select Data”.  Click on the “Switch Row / Column” button and press the OK button

switch

You should now have 5 series as shown in the chart below.  Adjust the colours of the series and the chart itself as you wish.  I added a grey gradient to the chart fill, removed the X axis, horizontal lines and legend and then adjusted each series so that it matched the traffic light idea with a white border.

second chart                                3rd chart

Step 3: Add the indicator arrow

For this we need to add 2 more series.  First click on cell E2 and press Ctrl+C.  Then click on the graph and press Ctrl+V.  The graph will automatically adjust to show the new data.

new data

Click on the new series, and press Ctrl+1 to bring up the formatting menu box.

Choose to plot the series on the secondary axis in the “Series Options” tab and give it a gap width of “0”

secondary axisBefore pressing the close button, go to the fill tab and choose no fill.  Then you can close the formatting menu box.  The graph should now look like the one below:

4th chart

Repeat the process, this time selecting cell E3 and copying and pasting it into the chart.  Your graph should look something like this now:

5th chart

The next step is to adjust both of the vertical axes so that the maximum scale is 100% for both.  To do this double click an axis.  In the format axis menu box ensure that the Fixed Maximum = “1”

axes

Repeat for the other axis.

6th chart

You can now delete the top axis.

We now need to add the arrow.  To do this first we insert the arrow shape.  Click on Insert > Shapes > Down Arrow.  Then draw the arrow on the page and adjust it to have the shape you want.  You can see mine in the picture below.  I’ve given it a black outline and an 80% transparent white fill.

down arrow

Then click on your arrow and press Ctrl+C.  Click on your seventh series (which you want to replace with the arrow), and press Ctrl+V.  And voila, the chart is complete:

finished chart

Obviously this is a large chart for just one data point, but you can add other points to it as well…

alternative

Have fun!

+Alesandra Blakeston

Linear bubble charts for comparison

A colleague of mine uses a lot of bubble charts and wanted a simpler way to compare the data in each chart.   I have to say I am not a big fan of bubble charts as I find them difficult to analyse and compare, however I accepted the challenge!  This is what I proposed:

The chart is actually quite simple and I’ve uploaded it here if you wish to see how it was done.  Each line is set up on a different tab, (data1, data2, etc) but in essence, each line of bubbles in the chart has a different number in the Y axis column.  For the first line, the value is 22, for the second line, the value is 17 and so on until the last line which has a value of 2.  The value column gives the size of the bubble and the date column is uniform to allow the positioning of the centre of each bubble.

excel data

What do you think?

+Alesandra Blakeston