I have noticed something totally strange while trying to get an auto insurance quote online at GEICO. See it for yourself.

They are showing your password in the URL… OMG

Make sure you don’t use the site for getting new quotes as it could be a potential privacy breach as anyone watching the URLs like network admins, ISPs owning the logs or other users of the computer can see your password and access your account to see your details like SSN, Date of Birth, Accident history.

• 0 Comments

Often my work involves processing web page data in excel sheets. This includes extracting the hyperlinks from cell contents. There is no formula for extracting hyperlinks though, you can right click on cell and choose “edit hyperlink” to see which address the cell is linking to. But that is a tedious process especially if you are planning on using the hyperlink for something.

Here is a handy user defined function in VBA for getting hyperlinks from a spreadsheet cell:


Function getURL(forThisCell As Range) As String
'VBA UDF for getting URLs from a cell if any
retVal = ""
If forThisCell.Hyperlinks(1).Address <> “” Then
retVal = forThisCell.Hyperlinks(1).Address
End If
getURL = retVal
End Function


Bonus tip: You can create hyperlink on a cell using “hyperlink()” spreadsheet function. The syntax is simple. =hyperlink("http://chandoo.org/wp","Pointy Haired Dilbert") will create a link in the cell to this blog.

• 0 Comments

change-sort-orientation-excel-columns

You can sort data across columns instead of rows by changing sort options. If you ever had to sort across columns now you know a simple way to do this

Follow these steps for changing sort orientation:

• Select the table you want to sort (just the data, not the headers as when you change sort orientation, you cannot set header rows)
• Go to Data > Sort
• Click on Options
• Set “Sort Orientation” to “left to right” instead of “top to bottom” as shown below:
  
• Click OK

Also see: How to count words in an excel cell, Fill only weekdays when auto-filling dates, Find unique items in a list

• 0 Comments

Google Maps Directions by Public Transport

Wow, this is awesome. Google maps has added directions by public transport. Sometime back when they integrated public transport timings along with bus-stop / train station icons, it was pretty cool, but adding directions based on bus / train routes is totally awesome. Try it for yourself, click here to see a sample direction.

• 2 Comments

We have been debating whether or not to buy iPhone. For one, we are not on any cellular contract and free to go. But the sheer cost of $400 one time (one for my wife and one of me) and a monthly bill of ~ $150 is holding us back. Our current mobile bill is roughly $80 per month and when we choose to go back home we can just takeout the SIM Cards and thrash it without having to bother about paying any contract breakage (incidentally for iPhone this could be as much as $175 per line). On the other hand, the iPhone is excellent value for money for the awesome features it provides. Our current phones were bought at Rs. 10,000 a piece (roughly $250), even though they were world class when we bought them 2 years back and still work well, their features are no where comparable to that of iPhone’s. Hmm…

Here are few excellent excel links from the last week around the web:

• Learn about excel category axis, find out how to use dates, numbers, text etc. on category axis for charts. See how you can tweak the axis to get the outcome you want (for ex: How to show weekdays on a date axis, how to configure axis for stock ticker charts etc.)
• If you are the kind who would install lots of add-ins to do your work, then you may be wondering how to find out what are the add-ins you have in place. Find out all the COM add-ins you have installed in Excel from Daily dose of Excel
• Winners of 2008 Excel Dashboard competition, see the beautiful and informative dashboards here 1, 2 and 3. The winner got an iPhone, alas, if only I knew about this earlier, I would have wondered “how to dashboard” instead of “to iPhone or not to iPhone”.

Got a hot excel tip to share, drop me an email or leave a comment.

• 0 Comments

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.

• 1 Comment

During my evening jogs I go past this place, its a patch vast open space in the midst of the urban land. I made a mental note to shoot here during early morning as I would get very good view of sky. Yesterday I woke up early and drove there to took some shots. The sunrise was beautiful.

PS: I have post processed the image to improve sharpness and contrast
PPS: Liked this photo, see the entire series of Photographic Fridays

• 0 Comments