Introducing Excel Formula Helper
One of the most common comments we get here is “how does xxx formula works?” Even though there is vast documentation on excel formulas including the built-in help in MS Office, often it is difficult for us to learn a formula quickly and use it in a snap. So to help new users of excel […]
Generating Random Phone Numbers
Often when you are writing code or testing an application, it might be helpful to have random phone numbers generated for you. Here is an excel formula that I use: =value(left(substitute(rand(),”0.”,””),10)) try these 2 instead (thanks to John) =RAND()*(9999999999-1000000000)+1000000000 =RANDBETWEEN(1000000000,9999999999) [needs analysis toolpack add-in or excel 2007] Even though it works most of the time […]
Extracting Unique, Duplicate and Missing Items using Formulas [spreadcheats]
Often I wish Microsoft had spent the effort and time on a data genie (and a set of powerful formulas) that can automate common data cleanup tasks like extracting duplicates, makings lists unique, find missing items, remove spaces etc. Alas, instead they have provided features like clippy which are intrusive to say the least. So […]
Best of Pointy Haired Dilbert – Oct 2008
October has been another fantastic month since I stared this blog. We had 100k page views, the RSS subscriber count nearly touched 1500. There were 33 posts and 280 comments (WOW). Even our Facebook fan club reached the milestone of 10 members. For a loooong time myself and a friend whom I persuaded to join […]
Adding Box Plots to Show Data Distribution in Dashboards [Part 6 of 6]
This is a Guest Post by Robert on Visualization Techniques for Excel KPI Dashboards. This 6 Part Tutorial on Management Dashboards Teaches YOU: Creating a Scrollable List View in Dashboard Add Ability to Sort on Any KPI to the Dashboard Highlight KPIs Based on Percentile Add Microcharts to KPI Dashboards Compare 2 KPIs in the […]
Sorting Text in Excel using Formulas
Sorting text is such a day to day activity that it always surprises me why Excel hasn’t provided a simple spreadsheet formula for doing it. Of course you can use the sorting menu command (menu > data > sort) but this requires manual steps (or VBA). Most of the times we get raw textual data […]
Sports Dashboards in Excel – A Tutorial
One of my favorite cricket player, the GOD – Sachin Tendulkar has become highest test run scorer. What do I get if Sachin becomes highest scorer, you may ask. In order to celebrate this occasion I have created a cool sports dashboard in excel with some of the top test cricket players’ statistics. And, you […]