Archive for April, 2009

Jarad asks me in an email “how word frequency can be generated from a range of cells using excel formulas?” This got me thinking and lead to this post, where we learn how to calculate word frequency using array formulas and use it to analyze a bunch of search keywords.

As part of our spreadcheats, today we will learn how to calculate moving average using excel formulas. As a bonus, you will also learn how to calculate moving average of latest 3 months alone using the count, average and offset formulas. Once you finish the tutorial, you have some home work to do as well. So go ahead and learn how to calculate moving averages.

In this week’s excel links learn how to lookup in 2 columns, a cool charting hack using which you can depict uncertainty in predictions, an excel template that can be used to visualize social networks and much more.

Do you have a nosy colleague who would like to see what you are up to? Then use the hide workbook feature in the ribbon.

The winner for our first visualization contest is decided. Curious? Read on…

In this installment of spreadcheats we will learn how to understand excel formula errors and fix them. The #DIV/0!, #NAME?, #N/A, #NULL!, #NUM!, #REF!, #VALUE! errors and how to fix them.

Go ahead and find it..,

Do you know that it is not so straight forward to use credit card numbers in excel. Yes, excel uses a precision of 15 digits and thus when you enter a credit card number (16 digits) it is converted in to scientific format and the details are lost. Read on to learn the work around.

Do you want to make a budget vs. actual performance chart but not sure what option to use? Check out these 14 excel charting alternatives and find the one that tells your story.