Tag Archives: functions

Excel Functions and Formulas Hints and Tips

10 (actually, 18) Excel functions you should learn in 2012

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.

3) LEN

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.

5) IF

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.

7) SUMPRODUCT

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.

9) FIND

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!)

 

Excel Functions and Formulas

Making some old code better – Clean and Trim

It pleases me to look back at stuff I did years back and to see that I’ve improved and learned more as time has passed.  Almost three years ago, on teachr I posted this article about a new UDF I’d created and how it could be a little clunky if a large range was selected.

Well, today I’ve fixed that problem.  Here’s some updated code:-

Sub CleanandTrim()
Application.ScreenUpdating = False
On Error Resume Next
Dim cell As Range
For Each cell In Selection
If cell <> “” Then
cell.Value = Application.WorksheetFunction.Clean(Application.WorksheetFunction.Trim(cell))
End If
Next
Application.ScreenUpdating = True
End Sub

The key difference in this code is that I’ve added an IF statement to check to see if anything exists in cells being checked.  If the cells are blank then the code will move to the next cell rather than cleaning and trimming every cell in the selection.

PS.  Apologies – I usually manage to get the code formatted correctly so it looks like VBA code should, but WordPress is acting like MS-Word today and not listening to any of my instructions about formatting.  Will get it fixed soon, hopefully.

Excel Functions and Formulas

A to Z of Excel Functions: COUNTIF

Today we’re going to get all clever and introduce one of the cool functions in Excel, COUNTIF.  COUNTIF works in much the same way as some of the other counting functions in Excel, except it has a little decision-making functionality built into it to make it all the more powerful.

Take a look at the table below.  How many times does Michael’s name appear?

How many did you get?  The answer is 6, but I bet it was a real pain in the backside counting manually through that list.  Imagine counting through a list a few screens in size.  Ouch.

COUNTIF lets us do this kind of thing easily and through a function.  It looks like this:-

=COUNTIF(Range, Criteria)

Where range contains the cells you want to count from, and the criteria entered are what you want to count within that range.  For our example above, the function would look like this:-

=COUNTIF(A1:D10, “Mike”)
=6

You might have noticed that the ‘criteria argument of the function in this case has been entered within double-quotes, as we are looking for text.

So far, so simple, yes?  Let’s try the same thing, except this time we want to count how many times the number 25 appears in the table below:-


It’s a little bit more of a pain doing it by eye, isn’t it?  COUNTIF makes life simple:-

=COUNTIF(A1:D10, 25)
=5

Simples!  Notice this time that the number 25 is typed without double-quotes as it is numeric in value.  Great stuff, isn’t it?  It’s worth noting here that, if you type 25 in double quotes, like this..

=COUNTIF(A1:D10, “25”)

…the function still returns 5.  Number values can be entered as actual numeric values, or as text.  It’s worth remembering this, as in the next part you have to combine both.  Using the same list we now want to count how many values are in the grid which are greater than 15.  Try entering this:-

=COUNTIF(A1:D10, >15)

..and you’ll get an error.  This is because if you want to do this kind of thing Excel expects that you enter it as text, like this:-

=COUNTIF(A1:D10, “>15”)

Which will return a value of  33.  You would use the same method if you were searching for values less that 20:-

=COUNTIF(A1:D10, “<20”)

Values greater than or equal to 21:-

=COUNTIF(A1:D10, “>=21”)

And so on.  Making sense?  You can also use cell references as criteria, which adds even more power to your formulas.

And that’s it for now.  The COUNTIF function is a simple yet extremely versatile addition to your toolkit and can come in extremely handy in adding some real power to your spreadsheets.  Come back for more functions in the series!

Excel Functions and Formulas

A to Z of Excel Functions: COUNTBLANK

Yesterday I mentioned that Excel has a function for counting empty cells.  It’s (wait for it..) the COUNTBLANK function!!  It works in much the same way as COUNT and COUNTA except that it only allows a range as its criteria, not individual comma-separated values, cells or ranges.

It looks like this:-

=COUNTBLANK(range to search for blanks)

Using the same example from yesterday, let’s have a look at it in action:-

=COUNTBLANK(A1:E15)
Pressing enter will tell us that there are 4 empty cells in the range – kids stuff, really isn’t it?  Come back tomorrow for another counting function!