Pointy Haired Dilbert - Chandoo.org

Bullet graphs provide an effective way to dashboard target vs. actual performance data, the bread and butter of corporate analytics.

Howmuchever effective they are, the sad truth is there is no one easy way to do them in excel. I have prepared a short tutorial that can make you a dashboard ninja without writing extensive formulas or installing unknown add-ins. So get out your shinobigatana and join me in a fresh excel sheet arena.

Before we create our first bullet graph, let us spend a few moments understanding these graphs. Stephen Few proposed bullet graphs as way to provide crisp view of “target vs. actual performance” numbers. Shown below is a sample bullet graph and how you would read it.

Read up more on this at PTS blog and on a Gauge chart that actually works.

Let us create your first bullet graph

Click here to download bullet-graph template excel sheet so that you can see while reading

Our technique of involves conditional formatting and simple formulas applied to a cell grid. Just follow these 4 easy steps:

Step 1: Prepare your data for charting

Since we are going to plot bullet graphs on a cell grid, we first need to normalize our data. I have chosen to plot each bullet graph on 20 cells in a row as shown in the raw grid shown to the right:

Assuming we have fictitious sales data like this:

You can normalize YTD sales figures using a simple formula like this : ROUND(YTD-sales/target*20,0)

Now that we have our data steaming hot, lets brew the graphs

Step 2: Lets make the raw grid formatted based on data

Now we will take the raw 20 cell grid in each row and conditionally format these cells so that we have background of the bullet graph drawn on them.

For eg. If the normalized sales data for Bad range is 7 and for OK Range is 15 then,

We will highlight first 7 cells lighter shade of gray, next 8 cells gray and last 5 cells with darker shade of gray.

I have shown the conditional formatting applied to these cells below:

When we are done, a sample row looks like this:

We have our cell grids ready now, lets shoot some bullets.

Step 3: Plot bullets on our graph canvas

Our final step involves print a bullet symbol (either – or + or | ) in each cell depending on one of the following conditions:

1. If the cell position (1,2,3 … 20) is equal to Year ago value and cell position is less than YTD value print a + symbol
2. If the cell position is equal to Year ago value and cell position is more than YTD value print a | symbol
3. If the cell position is less than YTD value print a –
4. Else print a blank

See the formula below:

Download the excel template for bullet graphs to understand this formula better

Step 4: Show off your bullet graphs, awe your boss or colleagues, bask in your Ninja glory

Unfortunately, I cannot tell you how to do this. I can only teach you to be a Ninja, but you have to be one to charm people with your tactics.

Shown below is another variation you can try. Also, you can experiment with the symbols printed (instead of + - | you can try other ASCII characters, for more download the excel sheet containing bullet graph templates)

Also try: Partition charts, Incell Graphs and much more.

In response to Making Incell charts better article, reader Tony mailed me an excel sheet in which he has modified the charts to display colors and negative values. This is pretty cool. Take a look at the chart below:

MS Excel Incell charts with colors and negative values

The trick lies in displaying the charts in 2 rows, one for positive values and another for negative values and then coloring positive row with green and negative row with red. See aside.

Download this example sheet to experiment with incell charts.

Thanks Tony for sharing this with our readers

What is your favorite charting trick?

Environmental Graffiti should get the award for “worst possible bar chart ever” for this unbelievable piece of art…

Image (c) of Environmental Graffiti

Who said bar charts are only for serious data interpretation, they can be used to have such fun

Also read Garr Reynold’s comments on this as well. Happy Thursday..

I can never get tired of in-cell charts, whenever I get sometime, I try to experiment something on them. Here is an idea to design true incell column charts without using any add-ins or installing fonts. These charts can be fun to have on your project report or annual news letter or memos, they take as much time as munching a handful of M&Ms.

  Download excel chart containing incell bar chart example

1. First we will create in-cell chart using rept() the usual way

This is the simple step. For a sample data, we can use rept() excel function to repetitively print a symbol. Thus, by repetitively printing “|” (pipe) we can create incell bar charts.

REPT() example is shown below:

=rept("|",10) will print ||||||||||

2. Next combine several incell charts to create a column chart

We can combine various incell charts using excel’s concatenate() function or & operator. Better still you can use the concat() UDF. The trick here is we will delimit each chart with an ENTER key code by using char(10) as the delimiter. When used along with “wrap text” cell format this key code will force cell contents to a newline where the char code 10 is inserted.

3. Finally rotate the cell contents by 90⁰ to make the charts vertical

Just select the cell contents, hit ALT+1, and set the alignment formats so that the text is vertically aligned in the cell, and turn on wrap text.

That is all, you will now have an incell bar chart that is vertical like the one below:

Pretty cool eh?

Jon @ Peltiertech has taken a critical look at the partition charts suggested yesterday. You can read his review of the partition charts here. One of the commenters on his site said,

Jon, the partition chart is not that bad. It just needs to be defragmented…

So I de-fragmented my partition chart, this time it no longer provides any spatial trend cues or anything, instead its a cool little replacement for a pie chart, more so when you have just 3-4 pies and got bored of looking at pies.

Here is how in just 3 steps you can do this type of de-fragmented partition chart

1. First create a grid where the chart can appear

This is the easy step, select a range of cells (preferably in multiples of 100, I took 300 cells, spaced over 25 columns, 12 rows) and adjust the row-height and column-width till the range looks fine enough. Take a look at this one on the right.

2. Now set the Conditional Format over the range

Lets say you want to show 3 pies, one for 30%, another for 48% and the last one for 32% (well, just kidding, you can only 110% of your work, not 110% of your pies) so 22%. The conditional formatting formulas can look like this:

Your formulas should look like this:


if cell's row-number * width + column-number is with in pie-1's range, turn the cell on to color-1
else if cell's rownumber * width + column-number is with in pie-2's range, turn the cell on to color-2
else turn the color-3 on


3. Remember to trash your pie charts

Well, not really, save the good old pies. There is no step 3. So I saved you … umm… 33% of work. So why dont you leave a comment and tell me what you think about this ?

Next time you had to create a pie chart, consider building a partition chart as these charts can reveal trend information along with how much each pie is contributing. What more? They are as easy to create as eating a chocolate chip cookie dough ice cream

I made a sample partition chart based on some random data, the trick lies in using conditional formatting to change the color of a rectangular range of cells based on our data. Just follow these 3 steps to create a neat looking partition chart.

1. First get the data you want to show in partition chart

The data should be in a table format. A sample dataset can be, your top selling product in each of the 100 cities for the last 48 months.

2. Create a grid where you can plat your partition chart

Now create an identical grid beneath the data table where we can plot our data chart. You can adjust column / row heights for this range until it looks like a blank canvas where you can plot the partition colors, something that looks like this.

Remember: the grid dimensions should be same as our data table in Step 1

3. Finally apply conditional formatting to the grid

This should be easy step, even if you are not a rock star of conditional formatting,

Specify conditions for each partition, Sample this:

That is all, you have a partition chart in front of you using which you can probe and analyze spatial trends.

Also: Art with charts, 73 FREE Downloadable Excel Chart templates

I haven’t noticed this before, but google maps provides 3d building outlines. I think this is pretty cool.
You can see them in action here.

Earlier on google : Google Park bench in Russia