2011 Challenge: 30 Excel Functions in 30 Days

Your great suggestions in the Improve Your Excel Skills comments got me thinking. What skills would I like to improve in 2011?

It’s hard to pick just one thing, but I’ll start with Excel functions. Even the functions that you use every day can have hidden talents, and pitfalls that you aren’t aware of. And there are so many functions, that you probably only use a fraction of them.

So, in January, let’s explore 30 Excel functions in 30 days. Yes, you’re right — there are 31 days in January. However, I’m being kind, and will give you the first day off, to recover from your hangover, and/or post-holiday exhaustion. ;-)

CHOOSE Your Functions

For this challenge, we’ll stick to functions in the Text, Information and Lookup and Reference functions, listed below. There are about 60 functions in the list, and we can only cover 30, so please vote for your favourites. If you can’t see the list below, click here to go to the form.

Deadline for voting is Wednesday, December 29, 2010, at 5 PM (Toronto time).

Share Your INFO

We’ll start the challenge on January 2nd, and go till January 31st. Please check the blog every day during the challenge, and add your tips and comments, or even a HYPERLINK. There’s no SUBSTITUTE for team work, to ensure we ADDRESS all AREAS of each function.

My mind ISBLANK now, and I can’t FIND any more puns, so I’ll sign off now, and let you CHOOSE your functions. Thanks!

Update: Thanks for voting before the December 29th deadline — votes are no longer being accepted.

To my mind, you left off the “biggie” from your list… SUMPRODUCT… this is a true workhorse function that everyone should be familiar with. It is way more useful than either CELL or INFO (which should be treated in the same blog article as there are really two sides of the same coin to my way of thinking).

I have to apologize Deb, I completely read right over your “For this challenge, we’ll stick to functions in the Text, Information and Lookup and Reference functions” statement when I posted my SUMPRODUCT suggestion and, on top of that, it just never dawned on me that things like COUNT, SUM, etc. were not on your list either (I can be so oblivious sometimes). I would note, though, that when properly structured, SUMPRODUCT can be used as a replacement for MATCH and the various LOOKUP functions when multiple criteria are involved… so it *almost* fits your category.[grin]

I really love vlookup, it’s simple and was maybe the reason I really fell in love with excel, it was a love-hate relationship before! I’m looking forward for your work on describing them! Merry Christmas!!!!

All you have to do, is look at your spreadsheet, emmk? And then squint real close. And then you just fold time and space and smash your face against the corner of the monitor.

That’s how you do the indirect function!

Seriously, indirect by itself isn’t that tricky if you try to fit it in with stuff you already know. Try using it with a sum function. Just make yourself two columns of dummy data–say a1:a10 and b1:b10. Fill in 2 cells with $a$1:$a$10 and $b$1:$b$10. then just do a sum with =sum(indirect(yourreferencecell)).

If you can manage that simple example, you’ll find occasional but huge utility from the function. It will basically let you make ranges conditional. You can get real creative, real quick.

@dan I like the part about folding time and space, it seems appropriate. The only INDIRECT function I’ve seen that made any sense was using named ranges and the Intersection operator for a 2 Way Lookup.

In the fall of 2000 I was on the phone with Excel support for four hours to convince them the HLOOKUP function had a bug, which they finally conceded. I haven’t used that function since. I wonder how many votes it’ll get?

[…] to quickly learn more about Excel from one of the best? If so, please be sure to check out the 2011 Challenge: 30 Excel Functions in 30 Days being presented by Debra Dalgleish. Debra is the author of Contextures.com, the best Excel site on […]