Category Archives: Hints and Tips

Excel Hints and Tips

Ten things I hate about your spreadsheet

It’s been a long day and I feel like having a whine.

Here are ten things I hate about your spreadsheet:-

  1. The fact that you’ve created a new workbook but not saved it with a meaningful name before emailing it to me.  No file should be named “Book1.xls”.  Ideally you should save it to your computer first and then email it to me.  It makes things better for all of us.
  2. Flashy Wordart to make it look ‘prettier’.  Don’t. do. it.
  3. Quickly thrown together charts with default settings, especially in Excel 2003…yuck.
  4. I hate that pie chart.  I really hate it.
  5. Please don’t use garish background colours to highlight cells/rows/columns – I’ve got a migraine and it’s not helping.. Also red text with a blue or green background (and vice versa) is bad!
  6. Broken links to your external workbooks.
  7. Spelling mistakes are unforgivable.  A kitten will die for every one I find, and it’s your fault.
  8. Every time I see a formula error (#VALUE!, #N/A, etc) I die a little inside.  Do something so they don’t appear, if you can, especially if your workbook is being seen by a few people.
  9. In-cell comments are great, but if they don’t add value to the work you’ve done (or just serve as a reminder to yourself to do something), get rid of them before you send me your file.
  10. Is that Comic Sans?  There goes another kitten..
Excel Hints and Tips

Custom lists in Excel

If you type ‘Monday’ into any cell in Excel and then click and drag the chunky square fill handle on it’s bottom right down, you’ll notice that Excel automatically recognises that you’re using weekdays and, when you let go, et voila, the days of the week are filled into the cells you highlighted:-

 

Handy stuff!  Excel has some default lists built in for days of the week and months of the year, which in most cases would be all you need, but what if you’ve got lists of your own that you use regularly?  You’re in luck!  Excel allows you to roll your own.

In this example I want a custom list of seasons which I will use in a gardening spreadsheet.  So, when I type in ‘Spring’ and use the fill handle I want ‘Summer’, ‘Autumn’ and ‘Winter’ (if in the UK) to follow.

Go to Tools>Options and select the ‘Custom Lists’ tab from the dialog:-

As you can see, the existing lists appear in the right hand list box.  At the top of the box is an entry called ‘NEW LIST’.  Select this and click in the ‘List entries’ box to its right, and enter your new list, pressing enter in between each entry until you get something looking like this:-

And now, if you click the ‘Add’ button on the right, your new list will be created and will appear in the ‘Custom lists’ box:-

Easy, isn’t it?  Now click OK and you can start using your new custom list as you did with the weekdays earlier:-

And that’s your new custom list created!

This is really useful if there are lists you regularly use in your daily business, and can really save you time.  I’m not sure if you noticed, but at the bottom of the Custom Lists dialog, there is also a control you can use to refer to a range of cells which contain a list, which you can then Import by clicking the button.  Give that a try as well -it makes things a lot quicker, especially if you already have a long list available and don’t want to type it in again.

PS – apologies for the lack of posts recently.  Things have been picking up at work lately and time hasn’t been readily available.  More stuff to come soon.

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.