Ever since writing the create in-cell pie charts in excel, I have been itching to find a simple enough method to do incell bar graphs. An in-cell bar would probably be more useful and cuter than an in-cell pie as it can instantly provide trending details. What more, these would probably look gorgeous when printed out.

My first challenge was that there was no font readily available for bar graphs. Thankfully this is when I noticed Font Struct, an online font creator / editor. I quickly created a bar graph font using their wonderful editor. When you use bargraph font, 0 through 9 will show bars of increasing heights, as shown below.

You should download bargraph font if you want to use incell bar graphing technique mentioned here.

Once you have downloaded the font, the rest is simple process. For this example, lets use fictitious sales data of various beverages over the last 12 months. The initial data table is something like this:

1. First we will insert a column next to the total sales column and call it “last 12 months”. We will use this column to fill up the in-cell bar graph for the last 12 month sales.
2. Next we need to normalize the sales values for each month to a value between 0 to 9. I have used linear normalization, ie the maximum value across the 12 months would be 9 and everything else will be normalized according this. The formula for one month looked something like this: =ROUND(jan_sales/MAX(all_sales)*9,0). Remember, we need to round the data make it one digit instead of a decimal.
3. Once we have normalized values for each of 12 sales figures for each row, we just need to concatenate them to create a 12 character long string of numbers and place this values in our newly inserted column
4. Finally, we will change the font of this column to “bargraph” (just select all the cells under the “last 12 months” and change the font)

   When we are finally done, our table should look something like this:
   

5. Thats all, we now have a charming data table with cute little incell bar graphs to insert your project report / sales memo or news letter.

Remember, you need to have the bargraph font installed first.

Do you like this? share your feedback.

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.

I was toying with the idea of creating a tag cloud in excel - as a form of new visualization, this could be useful when you have medium amounts of data (eg: 50-300 rows) and you want to emphasize on what is important and what is not. I would imagine using a tag-cloud,

• When you are listing features of your software
• When you are listing your sales figures across top 1000 cities of your country
• When you are analyzing visitor data to your web start up

My goal is to generate a tag cloud from a selected data table (with just 2 columns, one with text to display, the other with any number on it) as shown below:

Since anything related to changing cell formats is not possible using functions, I had to write a VBA Macro (a subroutine that you would write in your excel sheet to achieve a task). The logic is simple:

1. Read the selected table and create 2 arrays, 1 with tags and another with the numeric data
2. Select an empty cell in the work book (I choose E10)
3. For each item in tags array:
- Add text to the selected cell
- set its font size based on normalized value between 6 and 20

The code is shown below:

Sub createCloud()
‘ this subroutine creates a tag cloud based on the list format tagname, tag importance
‘ the tag importance can have any value, it will be normalized to a value between 8 and 20

On Error GoTo tackle_this

Dim size As Integer

size = Selection.Count / 2

Dim tags() As String
Dim importance()

ReDim tags(1 To size) As String
ReDim importance(1 To size)

Dim minImp As Integer
Dim maxImp As Integer

cntr = 1
i = 1

For Each cell In Excel.Selection

If cntr Mod 2 = 1 Then
taglist = taglist & cell.Value & “, ”
tags(i) = cell.Value
Else
importance(i) = Val(cell.Value)
If importance(i) > maxImp Then
maxImp = importance(i)
End If
If importance(i) < minImp Then
minImp = importance(i)
End If
i = i + 1
End If
cntr = cntr + 1
Next cell

' paste values in cell e10
Range("e10").Select
ActiveCell.Value = taglist
ActiveCell.Font.size = 8

strt = 1

For i = 1 To size

With ActiveCell.Characters(Start:=strt, Length:=Len(tags(i))).Font
.size = 6 + Math.Round((importance(i) - minImp) / (maxImp - minImp) * 14, 0)
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
strt = strt + Len(tags(i)) + 2
Next i
Exit Sub
tackle_this:
' errors handled here
'MsgBox "You need to select a table so that I can create a tag cloud", vbCritical + vbOKOnly, "Wow, looks like there is an error!"
End Sub

This code is totally reusable. Just right click on the sheet name at bottom & select “view code”. In the VBA Editor create a new module (Menu > Insert > module) and Paste the above code there. Go back to your excel sheet and select a 2 columned data table and run the createCloud macro. The cloud will be created and pasted in cell E10. You can change this by modifying the line Range(”e10″).Select.

Happy charting

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.

Do you know that you can use excel find - replace dialog to change cell formats?

Often we prepare a report or project plan and our boss or someone else in the team feels that all the red color cells need to change to blue or something similar, worry not, you can use find-replace (ctrl+h) to do this for you.

I have created a small video demo on how to do this, check it out yourself

If you are not able to see the video:

1. Press Ctrl+h
2. click on “Format…” button on “find what:” area and specify the source format
3. click on “Format…” button on “replace with:” area and specify the target format
4. Select “replace all” or “replace” depending on your need
5. Smile, you have just automated a mechanical and boring task

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

1. F2 - Edit a cell, takes the cursor to the end of the cell
2. F4 - Repeat last action (for eg. if you have inserted a row, repeats that action again) - doent really work with paste through.
3. Ctrl+1 - Open “Format cell dialog”

What are your 3 most favorite excel key board shortcuts?