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.

I am fascinated by board games. They provide immense fun, anyone can enjoy them, they are unpredictable and best of all they are great value for money. That is why whenever I get sometime I experiment with simulating games to know them better [read Why Monopoly board game is not as random as it appears]. So, out of curiosity I have created an excel sheet that can generate bingo / housie (housey) tickets - 24 of them at a time. To get new set of tickets you would hit F9 (recalculate).

  Download Bingo / Housie ticket maker excel sheet

Note that these are Bingo UK / India / Australia variant I am talking about, not the US 5*5 type of bingo tickets.

Read on if you want to know how this is done:

According to Wikipedia:

A typical housie/bingo ticket .., contains fifteen numbers, arranged in nine columns by three rows. Each row contains five numbers and four blank spaces. Each column contains either one, two, or very rarely three, numbers:

* The first column contains numbers from 1 to 9,
* The second column numbers from 10 to 19,
* The third 20 to 29 and so on up until the last column, which contains numbers from 80 to 90.

I have removed number “90″ from the list in order to reduce some complexity in generating the tickets.

The problem is now to “generate 15 random number between 1 to 89 and fill them in 15 random spots in a grid of 3 rows by 9 columns such that each row has exactly 5 numbers”

Now I could write a function in VBA to do this, but I wanted to do this only using formulas. So I started breaking the problem.

The first challenge is to select any random 5 cells in a 9 cell row

Once we select any random 5 cells in a 9 cell row, we will fill them with bingo numbers. Now, excel has a function to generate random numbers between 1 to 9 (=round(rand()*9,0)), but this is not good for us since each time we call this function we will get a random number between 1 to 9, where as we need a 5 random numbers without repetition between 1 to 9. The function is memoryless and could repeat numbers when called 5 times.

Instead we can list all the possible “5 cells with numbers and others are empty” combinations of a 9 cells region and select a random combination every time. There are essentially ⁹C₅ i.e. 126 ways in which you can select any 5 cells out of 9 cells (without repetition of course).

So I listed all these combinations in a table and then randomly selected one of the combinations. You can see the first five such combinations in the image below:

Selecting any five cells out of nine cells

Now I created a 3*9 region and filled the cells with 1s or 0s, “1″ when the cell in bingo ticket is supposed to have a value and “0″ if the cell is empty as shown below:

Next challenge is to show random values in each cell

The trick here is that first column in our 3*9 bingo ticket has any number(s) from 1 to 9, second column has any number(s) from 10 to 19 …

Again, the challenge is the numbers should not repeat, otherwise we could simply use rand()*10, rand()*10+10, rand()*10+20 ... to generate the numbers.

This time it gets even more trickier because each column can have either no values, or 1 value or 2 values or 3 values.

The ticket generation logic now looks like:

• If the column has no values in it, then we will leave all the cells in that column of bingo ticket empty
• If the column has 1 value, we will generate any random number from that column’s range of possible values (1-9, 10-19,20-29,…80-89) and place it in the cell that is supposed to have a value and leave other cells empty.
• If the column has 2 values, we will generate 2 random numbers without repetition from that column’s range of possible values and place them in cells that are supposed to have them
• If the column has 3 values, we will generate 3 random numbers without repetition from that column’s range of possible values and place them in cells that are supposed to have them

As you can see, it is easy when the column has no or 1 value in it. But when the column has 2 or 3 I used the combinations trick described earlier.

First I created all 2 number combinations and 3 number combinations. Since the numbers on Bingo ticket are always sorted from top to bottom in a column, I just had to list down 45 combinations (¹⁰C₂) for 2 numbers and 120 combinations (¹⁰C₃) for 3 numbers.

The rest of the details are small enough that I can leave them to your imagination. So when the ticket is generated, it looks like this:

Remember to download housie / bingo ticket generator excel sheet and print your tickets at home. Just F9 to generate new set of tickets. Un-hide the rows from 43 if you want to see how this is done.

Like this post: Consider digging it or bookmarking it on del.icio.us or better still subscribing to my feed, its yummy

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 was doing some weird analysis on corporate mission statements and I had to count the number of words in each cell. That is when I realized there is no formula to calculate the number of words in a cell, I was too lazy to write an UDF for that, so I figured out a nearly perfect way to calculate no. of words in a cell using existing formulas.

If you want to calculate the no. of words in cell a1, then use the formula:
=len(a1)-len(substitute(a1," ",""))+1

What this does is, it calculates the number of spaces in a cell and adds 1 to it, thus almost finding the number of words in a cell. I say almost because, if a cell has “this blog rocks,really!”, this formula will calculate the words as 3, where as there are 4 words in there

But that is for you to think

Also: Concatenate a bunch of cells using simple formula, Generate tag clouds in excel using vba, Master your IFs and BUTs

Time for another look at print ads, this time for the most bizarre of them all. Mind you, these are not awful or plain lousy ads, for any column-centimeter worth its area has a fair share of lousy ads, these are simply bizarre ads, the kind that will make you go - WTF?

The first one is from Lenevo slim monitors, they are so slim that you can cut a head off with them, who comes up with this stuff anyway?

This is even more bizarre, kanika digital printers that can print almost life like - really tasteless

Now comes itch guard, subtle and yucky

This classic wtfness displayed by Sahara global, I think they mean that you can visit the place, but.. why create such visually revolting ad for that?

I had post another lenevo ad for it displays intense lack of common sense on the ad designers part

Images Source: AgencyFaqs.

Also see: Best of Indian Outdoor Advertising, Few good Indian Print Ads

Like this? Feel free to share / copy / exploit / save the cartoon. See other powerpoint cartoons as well

Excel conditional formatting is a hidden and powerful gem that when used well, can change the outlook of your project report / sales budget / project plan or analytical outputs from bunch of raw data in default fonts to something truly professional and good looking. Better still, you dont even need to be a guru or excel pro to achieve dramatic results. All you need is some coffee and this post to learn some cool conditional formatting tricks.

So you got your coffee mug? well, lets start!

The 5 tricks we are going to learn are,

1. Highlighting alternative rows / columns in tables
2. No-nonsense project plans / gantt charts
3. Extreme Incell graphs
4. Highlight mistakes, errors, omissions, repetitions
5. Create intuitive dashboards

I have created an excel sheet containing all these examples. Feel free to download the excel and be a conditional formatting rock star

1. Highlighting alternative rows / columns in tables:

Often when you present data in a large table it looks monotonous and is difficult to read. This is because your eyes start interpreting the data as grid instead of some important numbers. To break this you try highlighting or changing the background color of alternative rows / columns. But how would you do this if you have rather large table and it keeps changing. The trick lies in Conditional Formatting. (Of course you can use the built-in auto format feature, but we all know how the default settings of various Microsoft products are like).

• First select data part of the table you want to format.
• Go to Conditional formatting dialog (Menu > Format > Conditional Formatting)
• Change the “cell value is” to “formula is” (YES, you can base your formatting outcome on formulas instead of cell values)
• Now, if you want to highlight alternative rows, the formula can go something like this,
  =MOD(ROW(),2)=0
which means, whenever row() of the current cell is even, to change the coloring to odd rows, you just need to put =MOD(ROW(),2)=1 as formula
Also, if you want to highlight alternative columns instead of rows you can use the column() formula.
What if you want to change background color of every 3rd row instead, just use =MOD(ROW(),3)=0 instead. Just use your imagination.

• Set the format as you like, in my case I have used yellow color. When you are done, the dialog should look something like this:
  
• Click OK.
• Congratulations, you have mastered a conditional formatting trick now

2. Creating a quick project plan / gantt chart using conditional formatting:

Project plans / gantt charts are everyday activity in most of our lives. Creating a simple and snazzy project plan template in excel is not a difficult job, using conditional formatting a bit of formulas you can do it no time.

• First create a table structure like shown above, with columns like Activity, start and end day, day 1, 2,3, etc…
• Now, whenever a day falls between start and end day for a corresponding activity, we need to highlight that row. For that we need to identify whether a day falls between start and end. We can do that with the below formulas,
  =IF(AND(F$8>=$D9, F$8<=$E9),"1","")
Which means, whenever, the day number represented on the top row is between start and end we will in 1 in the corresponding cell.

• Next, whenever the cell value is 1, we will just fill the cell with a favorite color and change the font to same color, so that we dont see anything but a highlighted cell, better still, whenever you change the start or end dates, the color will change automatically. This will be done by conditional formatting like below:
  
• Congratulations, you have mastered the art of creating excel gantt charts now

3. Extreme In-cell Graphs:

Incell graphing is a nifty trick that basically uses REPT() function (used to repeat a string, character given number of times) to generate bar-charts with in a cell. You can apply conditional formatting on top of them to give the charts a good effect. Here is a sample:

The above is a table of visits to Pointy Haried Dilbert in the month of January 2008. As you can see I have highlighted (by changing the font color to red and making it bold) for the cells that have more than average number of visits in the month. I am not going to tell you how to do it, it is your home work

4. Highlight mistakes / errors / omissions / repetitions using conditional formatting:

Often we will do highly monotonous job like typing data in a sheet. Since the work is monotonous you tend to make mistakes, omit a few or repeat something etc. This can be avoided by conditional formatting. I use this trick whenever I am typing something or pasting a formula over a rather large range of cells (for eg. vlookup on annual revenue data of all your accounts, could run in to thousands of rows across multiple states /regions etc.).

Lets see how you can highlight a cell when it has an error:

• First select the cells that you want to search for errors
• Next go to menu > format > conditional formatting and mention the formula as: =iserror() (see below)
  
• In the same way you track repetitions, a simple countif() would do the magic for you, or Omissions (again a countif())
• Thats it, you have learned how to save tons of time by letting excel do the job for you. Sit back and sip that coffee before it gets cold.

5. Creating dash boards using excel conditional formatting:

As I said before you can use conditional formatting to create intuitive sales reports or analytics outputs. Like the one shown here,

Here is how you can do it:

• Copy your data table to a new table.
• Empty the data part and replace it with formula that can go like this (I am using the above table format to write these formulas, may change for your data)
  =ROUND(C10,0) & " " & IF(C9 Essentially, what we are doing is, whenever the cell value is more than its predecessor in the data table we are appending the symbol â–² (go to menu > insert > symbols and look for the above one) etc.

• Next, conditionally change the color of cell to red / green / blue or pink (if you want ) and you are done
• Show it to your boss, bask in the glory

I have created an excel sheet containing all these examples. Feel free to download the excel and be a conditional formatting rock star.

Also read : Create dash boards in Excel | How to draw impressive charts in Excel | Changing the default chart settings to impress everyone