Locked lesson.

Upgrade

About this lesson

This complex looking chart conveys a TON of information comparing a single target value against multiple different markers.

Quick reference

Bullet Charts

Adding easy to use Bullet Charts to your system.

When to use

Bullet charts can convey not only target versus actual, but add performance bands as well.

Instructions

Adding the Bullet Template to your system

  • Open your target workbook
  • Open the Bullet Template.xlsx file
  • In the Bullet Template file, right click the Bullet Chart tab --> Move or Copy
  • Check the box to make a copy, then select your target workbook from the list
  • Close the Bullet Template file without saving changes

Updating the bullet chart for your data

  • Update the chart title to something that makes sense for your data
  • Link the green Target cell (B12) to the goal you want to achieve
  • Set the Max OK band (B13) to the top of what you would consider “OK” performance
  • Set the Max Poor band (B14) to the bottom of what you would consider “OK” performance
  • Link the Actual (B17) to your current results

Deploying the chart

  • Copy the desired chart (vertical or horizontal)
  • Paste it on your dashboard

Hints & tips

  • The Max OK value can be set above or below your target value
  • The Max Poor band must be set below your Max OK value
  • It’s a good idea to rename your Bullet Chart worksheet so you can add multiple bullet chart worksheets in your workbook
  • Hide the bullet chart sheet when done so that users can’t mess with it
Login to download
  • 00:04 We're now gonna look at a special type of chart called a bullet chart.
  • 00:09 The challenge is that this chart doesn't exist in Excel by default so
  • 00:13 we have to create it from scratch, but it's kinda complicated.
  • 00:16 So what I'm gonna do is I'm actually gonna show you how to use a template that we've
  • 00:20 included, which was called the Bullet template file.
  • 00:24 I'm gonna flip over to that file now and you'll notice that it looks like this.
  • 00:28 This has got pre-built bullet charts in both the horizontal and a vertical format.
  • 00:34 The key to understanding these charts is this.
  • 00:37 We have a black band, which is our target or maybe our budget.
  • 00:41 We've got this dark blue line that goes through here,
  • 00:44 this is our actual performance, and
  • 00:46 ideally we'd like to see it get all the way to our target or even surpass it.
  • 00:51 We also have these three shaded bands that we actually have here.
  • 00:55 Now this first band is an indicator for poor or substandard performance.
  • 01:01 The second band is for performance that's deemed as okay or acceptable.
  • 01:07 And the third band is the place that we really want to get to
  • 01:10 where we've got great or exceptional performance.
  • 01:13 And the same is true of both the vertical and the horizontal bullet chart.
  • 01:18 Now to make use of this template there's a couple of tricks.
  • 01:21 Number one, we open the template first, we right click on the the worksheet tab and
  • 01:26 say Move or Copy.
  • 01:28 You must check the Create a copy button, because if you try and
  • 01:31 move the only worksheet out of a file,
  • 01:33 it won't let you because every file has to have at least one worksheet.
  • 01:36 We're then gonna check, and
  • 01:38 grab the file that we want to put it into, which is called Bullet Charts.
  • 01:42 And optionally you can decide to put it where ever.
  • 01:45 I'm gonna move it to the end and say create a copy.
  • 01:48 You've now got a bullet chart template in your file.
  • 01:49 So let's quickly go back to the bullet template, close it.
  • 01:55 Don't save changes, now it's perfectly ready to go next time, and
  • 01:58 it's just up to us to tweak this chart.
  • 02:01 So let's give this chart a new title,
  • 02:03 we'll call this one Sales Performance vs Budget.
  • 02:10 You'll notice that both the horizontal and the vertical charts update right away.
  • 02:15 Next thing that we need to do is set the target that we want to achieve,
  • 02:18 what is our black band gonna be?
  • 02:19 And the most logical thing here is to grab the budget.
  • 02:23 So, we'll go back to the source data page.
  • 02:26 We'll say, let's grab the 130,000 in N7.
  • 02:31 Ad now the chart starts to look a little bit funny but
  • 02:32 that's because we haven't set out performance band.
  • 02:35 So now we need to determine what do we want to set as the maximum of the okay.
  • 02:39 Anything greater than this is good or exceptional performance.
  • 02:43 For budgets I like to say, you know what, why don't we try and make it times 0.95.
  • 02:48 That's a good number.
  • 02:51 We can say if you get 95% of the way to budget
  • 02:54 you're probably going to get bonused out.
  • 02:55 That's if you set aggressive budgets.
  • 02:57 If you set really, really light budgets you might want it to be 110% of budget.
  • 03:02 What's the maximum of a poor band here?
  • 03:04 So this is the part where we say this is no longer acceptable and
  • 03:07 you may be looking for a new job.
  • 03:09 Well you know what?
  • 03:10 Why don't we go and say you know what if you only achieve say 80% of budget, so
  • 03:15 0.8, that's not good enough.
  • 03:17 There we go, we've now got our band set.
  • 03:20 The final thing is to link the actual performance.
  • 03:23 We can set equals, go back to the source data and
  • 03:27 pick up our total to date for actual and say enter.
  • 03:31 And what we should see now here is that this band goes all the way to
  • 03:35 the very end.
  • 03:36 What would happen if our sales were just banging, we had 160,000, for example?
  • 03:41 It would blow right through the bar.
  • 03:45 I'm just gonna undo that quickly.
  • 03:46 What if sales were only say 70,000?
  • 03:51 You can see somebody's gonna be looking for a new job.
  • 03:53 Let me undo that as well.
  • 03:56 So these charts dynamically update to show a lot of information.
  • 04:00 It's the one or two charts that I actually recommend that people use,
  • 04:04 that have to be taught.
  • 04:06 The only other thing you might wanna do in here
  • 04:09 is change the number formats that you're using in this area.
  • 04:12 Those will carry through to the chart, and that's pretty much about it, or
  • 04:15 maybe the chart colors.
  • 04:17 From here, though, what I'm gonna do, Ctrl-C to copy.
  • 04:21 Gonna go back to the dashboard page, Ctrl-V to paste.
  • 04:25 And at this point, again, I can resize my charts to fit them in where they belong.
  • 04:31 And I might even at this point, go back and even right click, and
  • 04:34 hide this entire worksheet because nobody really needs to see it.
  • 04:37 That way I've still got these two guys lying around if anybody does anything that
  • 04:41 compromises my original chart, but
  • 04:43 right now, everything's looking pretty darn good.

Lesson notes are only available for subscribers.

Gift this course