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.

Would you like to spend next 5 minutes learning how to create an excel sheet to track your mutual fund portfolio?

NOTE: I have updated the sheet to fix a formula error, download it again if you need to.

We will use 2 simple excel features to achieve this - web queries and vlookup()
[click here to learn more about web queries in excel]

1. First, lets put a tabular format for our portfolio: We can have fund name, # of units, purchase NAV (Net Asset Value, the cost of unit for your when you bought it), purchase date, total value at purchase (units * purchase NAV), current NAV (we will pull this data from internet), value as of now (units * current NAV), Profit / loss amount and profit / loss % as our table columns. Once you learn how to do this, you can add more columns depending on what / how you want to track your MF portfolio.

   When you finish creating the table, it would look something like this:
   

2. Next, we will use web-queries to load the fund-names and the corresponding latest NAVs in a separate sheet. I have queried Association of Mutual Funds India [AMFI] - Latest Mutual fund NAV page since all my investments are in India. If you are in US or some other country you can query corresponding fund house / financial info aggregator sites (like google finance) to get the data. Remember to set “Refresh data on file open” on to get fresh data whenever you open the your tracker excel sheet.

   Since AMFI returns data in a text file with ; as delimiter, I had to parse the fund names and navs out of it using a combination of search(), left() and mid(). I will not get in to the details of how its done since you may have to process your data differently depending on source.

   Finally when the processing is done, we will have a table in the second sheet with all fund names and latest navs.

3. Now, all we have to do is create lookup formulas (well just vlookup()) to get the latest NAV to our tracker table based on the entered fund name.
   • Assuming the fund name in which you invested is in cell “c1″,
   • Assuming the fund data is in table “sheet1!c1:d6000″ with “column c” containing the fund name and “column d” containing latest NAV,

   The formula for latest NAV can look like this:
   
=vlookup(c1,sheet1!c1:d6000,2,false)

   Remember to use false for last parameter since fund names may not be sorted in alphabetical order on your source web page.

   Now we will repeat this formula for all the rows in latest nav column. I have built my portfolio tracker to track 20 funds at a time. Also, you can simplify formulas using named ranges.

4. Finally we will write formulas for,

   current value = latest nav * units held
   profit/loss = current value - purchase value
   profit/loss % = “profit/loss” / purchase value

   You can add some conditional formatting to beautify the table (like turning text blue for profits and red for losses etc.)

5. Thats all, you have now created a real-time mf portfolio tracker. It would look something like this when done:
   
   You can do the same for stock portfolios, commodities etc. You just need a web source that gives you latest data and five minutes of free time

Feel free to download mutual fund portfolio tracker excel sheet I have created and play with it.

Few ideas on how you can enhance this:

• Add graphs to see visually how the funds are doing
• Build some VBA to store previous NAV values of your funds so that you can see historical dates
• Instead of doing plain % of profit / loss, compute realistic growth of your funds using date of purchase, risk free rate of return etc.

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

Yesterday we were calculating our Washington trip expenses and it occurred to me that if there is an excel template where I could enter the trip expenses and who paid what to find out how much we need to pay up / collect, it would be great. I looked around for few seconds, couldn’t find anything. So I went ahead and built expense sharing worksheet on google docs. Feel free to use it for sharing your trip / party / picnic / apartment expenses.

Here is how I have done it.

• First I have created a list of people who need to share. Just for simplicity I have limited the no to 10. You can edit the excel and change it if you want.
• Then I have created an expense table like the one shown below. The requirements for this are simple, (1) should be able to capture descriptions of each spending (2) should be able to specify who footed the bill (3) should be able to mention the amount (4) should be able to exclude people from sharing a particular expense. Again, for simplicity sake I have limited the number of people who can be excluded from sharing an expense to 4. You can always edit this and change the formula accordingly.

  

• Now next to the list of people I have added 3 columns to show (1) how much that person has already paid (2) how much is his/her share (3) what is the difference.
• The formula for (1) how much already paid is straight forward sumif(), the formula for a particular person’s share is something like this,
  

  If the row has a person
  (total expenses / no.of people) - (total expenses excluded for this person / no. of people)
  else “0″

  The actual formula involved sumif() over the four columns since each expense can be excluded for maximum of 4 people.

• Once I have entered data the output looked something like this,
  
  Hence both Yerry Jang and Dointy Paired Hilbert need to collect 237 $ and 90 cents from Barren Wuffet, Gill Bates and Beff Jezos put together. Not bad

How to use it?
Just save the expense sharing excel sheet from google doc [or download the expense sharing excel sheet] to your machine / account. Enter the expenses in columns B,C and D. Enter the people names in column L. Incase you need to exclude any one from a particular line item, just enter their number (it will be next to the person’s name in column K]. As you enter the expense details, the Column O displays the actual dues.

Feel free to comment / extend / share / suggest. Your turn…