Happy new year 2torians! With the Olympics and pending end of the world upon us this year, what better time to start learning more about Excel? In this article I want to quickly breeze through a handful of the essential functions that I think you should take the time to learn about this year. Not only will they take your mind off the inevitable demise of the human race, they’ll no doubt make you better at Excel for the short time you have left
Over the coming weeks I will go through each of these functions and explain more about their workings, but there’s absolutely no reason at all to wait until then.. Why not Google search for them in the meantime and learn how to use them for yourself? You get such a warm and fuzzy feeling inside when you learn something for yourself. In fact, self-teaching is the way most Excel experts earned their stripes!
1) VLOOKUP and HLOOKUP
I know, there are two functions here. I’ve grouped them together because they operate in much the same way, only in a different direction. The lookup functions are more than invaluable in your wranglings with data. The ability to combine multiple sources of data into one alone is worth the time spent figuring out how they work. Whilst how they do work might confuse to start with, once you get to grips with them, they really are like magic (and will save you tons of time to boot).
2) LEFT, MID, and RIGHT
I’ve put these three together as one rather than individually as they all more or less operate in the same way with text data. These three useful functions allow you to pull out useful stuff from otherwise useless or meaningless strings.
How long is a (piece of) string? Used in conjunction with LEFT, MID and RIGHT, the LEN function can be an extremely powerful ally in your data manipulation efforts.
4) COUNT, COUNTA AND SUM
Again, I’ve lumped these together as they operate in very similar ways. SUM does exactly what it says on the tin, and adds together provided values or a range of values. COUNT and COUNTA do more or less the same thing (that is, counting the number of values provided), but in slightly different ways. I won’t explain how at the moment but you’re more than free to explore for yourselves – just make sure you use the right one for the results you want.
The ‘main man’ when it comes to adding real power to your spreadsheet work, the IF function allows you to add decision-making capabilities to your work, and used correctly can almost turn ordinary spreadsheets into applications in their own right. Learn this and you’ll go far.
6) COUNTIF and SUMIF
More than one function again here, but these two again operate in similar ways. Think of them as the love-children of COUNT, SUM and IF and you’ve got some idea of how they might work. Learn these and you’ll be conditionally adding and counting values in your sheets in no time.
A mathematical function at heart, this function is also wonderful when you want to conditionally add or count data in tables when you have multiple conditions. Want to find out how many blue Ferraris sold during February from a complete sales list? SUMPRODUCT is the function for you, and can really add some valuable power to your analyses.
8) OR and AND
Two logical functions here. Using these in combination with the conditional functions (such as IF, etc) can really boost your productivity and general Excel wizardry smugness.
More text manipulation here, the FIND function can be really useful in trying to get meaningful data out of complex text strings, especially if used with the other text manipulation functions mentioned earlier. Google it now!
10) CLEAN and TRIM
Dirty data (especially that copied and pasted from the web and other non-Excel sources) can be full of all kinds of non-printing, messy nasties. The CLEAN and TRIM functions can tidy these nasties away for you and leave your data gleaming, like some kind of data disinfectant.
Ok, I know this has been a rather informal and brief list of essentials, and I know I’ve probably missed out a handful of other useful functions, but I would say that these are more often than not the most regularly used weapons in my Excel data arsenal, and without them my spreadsheets wouldn’t be half as good/useful as they are.
Keep coming back for more tips. Comments, as always, are more than welcome (as long as they’re nice ones!)