Category Archives: Excel

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 Hints and Tips Tips Visual Basic For Applications (VBA)

Excel tip: Display the name of the last user to save a file

A colleague came to me today with a request.  She wanted a cell on her worksheet to display the user name of the last person to save her Excel workbook, along with the time it was saved, and asked me if there was some kind of function in Excel that did this.  I told her unfortunately not, but that I could write a VBA routine that would update a particular cell whenever the file was saved.

This, she said, was fine, but she’d prefer that there was a function or some way of doing it which didn’t necessarily link to a hard-coded cell or worksheet in the file.

At first I thought about creating a UDF to do this, but after a while pondering I decided a more elegant way might be to create a Name in the workbook which would be updated every time the file was saved.

Here’s how to do it:-

First of all, create a name in your workbook called ‘lastupdated’.  In the ‘Refers to:’ section of the dialog, create a reference to a text string such as “Last updated by MRichardson on 15/11/2011”, then click ‘Add’ (Don’t forget the quotes in your text string so that Excel recognises it as such).  The completed dialog should look like this:-


Now, to test this, create a reference to your Name in any cell in the workbook by typing in ‘=lastupdated’.  Press enter and your text should appear in the cell.  Magic!

It’s worth pointing out at this stage that names don’t always have to refer to cell or range references.  They can also be used as in-workbook constants of a kind.  I often use this technique when I want to use often occuring values in formulas and functions (such as VAT, for example).

Right.  We’ve done that.  All we now need to do is update the value of the ‘lastupdated’ name whenever the workbook is saved.

Press Alt-F11 to get to the VBA IDE and double-click on the ‘ThisWorkbook’ module.  We want to update everytime the workbook is saved, so we’re going to add our code to the ‘BeforeSave’ event.

The code itself is really quite straightforward and only takes up one line.  Here it is:-

ThisWorkbook.Names("lastupdated").Value = "Last updated by " & Environ("USERNAME") & " on " & Format(Now, "DD/MM/YYYY")

The code updates the value of our Name with the text, concatenated with the username (which is provided by Environ(“USERNAME”) and the ‘Format’ function to display today’s date in the ‘DD/MM/YYYY’ format.

Your code should look like this in the IDE:-

And that’s it.  Compile the project, close the IDE and save your file.  The cell containing the reference to our name should update with the correct details and will every time somebody saves changes to it.  Whenever you or your users open the file they will always know who last modified it.

The beauty of using a Name is that you can put the reference to it in any cell in the workbook, so you don’t have to deal with hard-coded cells which might be overwritten.  If you need to change the cell the details are displayed in, just add the reference to ‘lastupdated’ to somewhere else in your workbook.  Simple!

Comments on this process, as always, are more than welcome.

Excel

No, Microsoft, No!!!!

3d bars?? explosions??  Is this how Microsoft really want us to present data??  Or am I just being (as usual) a tad over-picky?  It is an advert, after all, but if I’m being honest the original chart wasn’t anywhere near as bad as the boy’s redesign..

 (The UK version of the advert, by the way, is much, much worse – it has a 3d pie in it as well!!!  Unfortunately I couldn’t find it on YouTube)

Excel Functions and Formulas

Excel quick tip: Return the cell address of the maximum value in a column

This relatively simple Excel formula returns the address of a cell containing the maximum value in a row of data:-

=ADDRESS(MATCH(MAX(A:A),A:A,0),COLUMN(A:A))

Where the A:A parts of the formula denote the column which contains your data.

Note: Make sure that your formula is entered in a cell in a different column to the data you are searching in otherwise Excel will throw a circular reference error at you.

Note 2: If the maximum value appears twice in your data, the formula will return the cell address of the first instance of the value in the data, from top to bottom.

Note 3:  Replace ‘MAX’ with ‘MIN’ to find the address of the lowest value in your data.