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.

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

Let us learn a simple charting hack to create a thermo-meter chart in excel. This type of charts can be effective in communicating one data point, they can make excellent presentation slide or dashboard widget. What more, they are as simple to do as adding whipped cream to your latte. So lets begin:

1. First we will draw thermometer outline using excel’s drawing tools

This is the simplest part. We will create a thermometer outline by drawing a circle and a rounded rectangle. See the illustration to the right to understand. Next we will fill the circle with our favorite color. Not that excel presents us with may choices, but I choose the light green, the kind that you see on the Starbucks small size cups. Oh btw, learn how to tweak excel chart color limitation to add your own colors.

2. Create a one column bar chart to fit inside thermometer

Now we will create a one column bar to fit snugly inside our thermometer outline (see below illustration). We will start by creating a default bar chart for a single cell containing temperature (or customer experience index or sales actual vs. target % or no. of cats you have), Next we will remove grid lines, plot area backgrounds, x-axis, column borders, now it should look like just a bar. Then we will adjust gap width to 0 (select the bar, right click and goto format data series, click on the options tab and adjust gap width to 0), this will make sure that our one column occupies the entire plot area.

Then we will adjust the scale of y-axis so that whenever the temperature (or the number of cats) changes our bar height changes (instead of excel default behavior of adjusting plot area and thus often retaining the bar heights). Now we will remove the y-axis as well. Finally, change the bar color to light yellow, remove chart area fill color, border. That is all, we now have a shiny little bar that changes its height when you change the cell containing temperature.

3. Finally fit the chart inside the thermometer outline

This is simple drag and drop game where in we will drag our chart and drop it inside our thermometer outline created in step1. And we are done. Go ahead, celebrate, show it off, print shiny little thermometers on a paper and hang it in your cubicle.

If you have difficulty creating or understanding this trick download thermometer chart templates I have created and play with it.

Like this? Also learn how to create artistic grid charts as an alternative to pie charts, beautify your charts with these 73 designer quality templates, put together in-cell pie charts, bar charts and much more.

One of the most frequent tasks for any manager is “planning”, be it putting together a hiring schedule or designing a jumbo jet, it all starts with a simple project plan and gantt chart is simple and intuitive representation of the same. But how to make a gantt chart in excel without writing too many formulas or adding conditional formats? Do not worry! with the simple trick we are going to learn today, you will be able to “gantt in 60 seconds”

For our example purposes, we will look at a fictitious project plan shown below:

Even though you can use this trick to pretty much any data format, it works better when the project plan is structured around how I represented it above.

Now lets build a gantt chart in 60 seconds, get your stopwatches out and get, set …. GO!

1. Create a bar graph

Select the data part of your plan (ie all the cells except header row in the above table) and click on chart icon in excel. Select “bar chart” as chart type and “stacked bar 2d” as sub-type (2nd left on the top row) as shown here.

Click finish. At this point your gantt chart should look like this:

2. Transform your bar graph to gantt chart

Now we will convert this stacked bar graph to a gantt chart by using chart formatting options.

• First we will reverse the way data is charted, so that it looks like a gantt chart. For this we need to right click anywhere on the “y-axis” of the graph and select “format” option. Go to “scale” tab and check “Categories in reverse order” option.
  

  Click ok, now out gantt chart should look like this:

  

  btw, what is the time on that stop clock, 34 seconds, well, thats just fine, we have got plenty of time to spruce this up.

• Now, lets get rid of first data series so that our graph looks more like gantt chart. Select the first data series of your chart (should be in violet blue color ), right click and go to “format data series” option.
  

  Select “none” for “border” and “area” options in the “patterns” tab. This will make sure that the first series is invisible, so we see second data series floating on the chart, thus making it look almost like a gantt chart.

  

  Go to “data labels” tab and check “category name” option. This will make sure our gantt chart will show labels (but on the now invisible first data series)

  

  Click ok, at this point our gantt chart should look like this:

  

• Finally we will adjust labels and backgrounds to convert this to a perfect gantt chart
  1. First lets remove the legend box on the right by selecting it and hitting “del” key.
  2. Now, lets also remove the y-axis since labels are visible on data-series 1. Just click on the y-axis (or category axis) and hit “del” again.
  3. Lets adjust the alignment of the data labels on series 1 so that they are properly visible. Right-click on the data labels and select “format” option. Go to “alignment” tab in the dialog and select “Right” for horizontal alignment and “inside end” for label position. This will ensure that our data labels are right aligned and shown at the end of data series 1, ie closer to the actual series 2 (duration of the task). See the below screen cap for more help.

     

  4. Lets also remove the plot back ground to remove the annoying grey color from our gantt chart. Just click on the grey color anywhere and hit “del”. While we are at it, you can also change the line color of x-axis (the days) to white or transparent to reduce the eye sore.
  5. Finally, lets adjust the plot area size so that we can read all the data labels and everything looks normal.

  At this point our gantt chart should look something like this:

  

  If you still have few seconds left, you can tweak the chart format to make it look better. I had 3 more seconds left, so I tried this

  

Bonus tips for enthusiastic excel experimenters:
1. Adjust the grid line format to make them more subtle
2. Select a particular task’s data point and change its color to emphasize progress / stalled statuses
3. Enhance this to add another column with no. of resources (or difficulty etc.), add this to the stacked chart and make it invisible just like series 1, but show the data labels.
4. You get the picture… so start gantting…

Also read:
Learn how to create project plans / gantt charts using conditional formatting
Create art grade excel charts with these 73 designer templates
Other uber cool excel tricks to make your colleagues zealous and your boss happy

Here is a ridiculously easy trick to do nice incell pie charts in excel, what more, they will make you look like a charting wizard.

1. Download free pie chart font from here (direct download link). The font has 21 pie chart codes starting with empty circle (character ‘a’) and ending with full circle (character ‘u’)
2. Install the font (just extract the zip file, copy the font file to your fonts folder, click here to learn how to install a font in windows)
3. Open excel, go to your data table and insert a column where you want incell pie charts
4. Enter a formula like =CHAR(CODE("a") + ROUND(data cell*21,1)), just replace the data cell with actual cell code.
5. Finally change the font to “pie charts for maps” from font dialog.
6. There is no step 6, so go ahead and show off this cool table to your colleagues, make their jaws drop in awe.

You can add some conditional formatting to the pie charts like I did to make it nice.

Here is a simple to trick to beat the 56 color limitation in excel when you are designing a chart: use picture files (jpg, gif, png etc.) to fill the chart area.

You will no longer have to worry about limiting your project report / website / annual report etc. colors to the 56 that excel has.

1 + 3 steps to get more colors in excel charts:

1. First create a 1*1 pixel sized image of the color you want to use in excel. You can use any software like MS Paintbrush to do this, all you need is the hex code or rgb of the color you want. Visit colour lovers if you are looking for cool color ideas.
2. Right click on any of your chart data points and select format data point
   
3. In the dialog select “fill effects”
   
4. Finally, go to “Picture” tab in fill effects and specify your picture file path.
   
   OK your changes. Thats all, now your charts have any color your want.

Also: Download 73 beautiful excel chart templates | Art of excel charting | Become a conditional formatting pro

add to del.icio.us | add to Stumble

How many times you created a chart in Microsoft excel and formatted it for minutes (and sometimes hours) to reduce the eye-sore?

Well, I will tell you my answer, its 293049430493 times

Worry not! for you can become a charting superman (or elastigirl) by using these 73 free designer quality chart templates in literally no time (well, almost)

These templates will take care of typical formatting activities like,

• Remove that ugly Grey color background from the chart
• Change the default grid line format from intrusive solid black to a duller shade of dotted Grey
• Adjust the fonts (to verdana in this case), remove annoying chart auto-font-scaling
• Move the legend to a meaningful location and adjust its size
• And, ofcouse, fix the colors

so that you, the user can focus on your data and not on “why in the world anyone would design a default format like this…”, so go ahead and unleash the charting pro in you.

Download the free MS Excel chart / graph templates

(I have put them in 3 separate excel sheets):

or, since you are so good, download one zip file, quick and easy!

If you are wondering how to use these templates, scroll all the way down the post

1. Bar / Column Chart Templates:

(29 of them)

2. Stacked Bar / Column Chart Templates:

(22 of them)

3. Pie Chart Templates:

(22 of them)
Even though I seldom use pie-charts (since they hide more than they show and all that) I know a lot of people do use them and hence here they are,

How to use these templates?

• Method 1 - Easy and Quick:
  • Download the chart templates (download links at top and bottom of this post)
  • Copy both the chart you wanted and the “data used” portion
  • Paste in your workbook
  • Change the values, remove columns (or add them if you wish)
  • Modify formatting if needed
  • Be careful now, as your boss may feel zealous for your charting skills
• Method 2: Slightly geeky but works like a charm!
  • Download the chart templates (download links at top and bottom of this post)
  • Select the chart you want, right click and select “Chart type” from the context menu
    [note: for more detailed steps & how-to, look in the excel worksheets you have downloaded
  • In the dailog, go to “custom types” tab and select “User-defined” radio button (towards bottom left)
  • Click on “Add…” button, and give your chart-template a name that you can remember
  • When you are done, click ok, and the chart is now added to your user-defined-charts library
  • In future, when you want to use the chart, simply click on charts icon on tool bar, and select the chart type as custom -> user defined ->your chart name
  • Now, watch out as your charts start stealing eyeballs in the boardroom!

Finally we can say good bye to default chart formats and all the associated eyesore

Download the free MS Excel chart / graph templates

(I have put them in 3 separate excel sheets):

or, since you are so good, download one zip file, quick and easy!

Thats all, feel free to share this content with your friends by bookmarking it on del.icio.us | Stumble upon