Free Excel Calendar Template for year 2009 (and all years up to 9999)

on 4 Dec, 2008 in Excel Tips | 3 Comments

Free Excel Calendar Template for year 2009 (and all years up to 9999)

Free excel calendar template for year 2009 (well, it works for any year all the way up to 9999)

Go ahead and download it, change the year number in the first sheet to 1981 or something and see the magic.

Read More >>

Introducing Excel Formula Helper

on 24 Nov, 2008 in Excel Tips, blogging | 7 Comments

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 [...]

Read More >>

Generating Random Phone Numbers

on 20 Nov, 2008 in Analytics, Excel Tips | 5 Comments

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 as excel rand() formula generates [...]

Read More >>

vlookup(), match() and offset() - explained in plain english [spreadcheats]

on 19 Nov, 2008 in Excel Tips, Featured | 12 Comments

vlookup(), match() and offset() - explained in plain english [spreadcheats]

VLOOKUP may not make you tall, rich and famous, but learning it can certainly give you wings. It makes you to connect two different tabular lists and saves a ton of time. In my opinion understanding VLOOKUP, OFFSET and MATCH worksheet formulas can transform you from normal excel user to a data processing beast.
In our [...]

Read More >>

Extracting Unique, Duplicate and Missing Items using Formulas [spreadcheats]

on 6 Nov, 2008 in Analytics, Excel Tips | 1 Comment

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 as [...]

Read More >>

Best of Pointy Haired Dilbert - Oct 2008

on 3 Nov, 2008 in Excel Tips, blogging | 0 Comments

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 [...]

Read More >>

Sorting Text in Excel using Formulas

on 22 Oct, 2008 in Analytics, Excel Tips, Featured | 13 Comments

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 [...]

Read More >>
Page 1 of 5[1]2345»