Working On Me

Here's a very handy Excel tip. You might want to limit with data validation the entry a user makes in a range of cells so that only a name appearing in a lookup list can be entered. But you also want to prevent any of the names in that list being entered more than once.

Here's how to do it.

Assuming A1:A10 contains the list of allowable names and the names are keyed in by the user in Column D, then select D1:D10 and from the Data menu, choose Validation to display this dialogue below. Make sure you change the 'Allow' pulldown to 'Custom':

In the 'Formula' field, enter the following:

=AND(COUNTIF($A$1:$A$10,D1)=1,COUNTIF($D$1:$D$10,D1)=1)

Done.

Most of us these days have a scroll mouse. To see it do something very clever, open up either Word or Excel and hold down the CTRL key. With that key held down move the central scroll wheel on your mouse and see what happens.

Since I learned how to do this, I must have saved hours of clicking on the view controls.

And as a bonus, it will perform a similar function in most browsers with most websites, too.

You might be trying to determine the difference between 2 dates to determine who old a person is. The easiest way to do this is with the DATEDIF function, which although entirely undocumented is present as a built-in function in all recent versions of Excel.

Assuming A1 contains the date of birth and B1 contains =TODAY(), then input in C1:

=DATEDIF(A1,B1,"y")

Other entries in place of the "y" will help you be more precise:

- use "ym" to tell you how many spare months

- use "md" to tell you how many spare days

This formula will tell you an age in Years, months and days:

=DATEDIF(A1,B1,"y") & " years, " & DATEDIF(A1,B1,"ym") & " months," & DATEDIF(A1,B1,"md") & " days"

Excel lets you hide worksheets within a workbook, which can be handy if – for instance – you want to conceal the workings of your spreadsheets, or want to keep the user interface clean and simple.

Fortunately, you can also unhide these sheets, but ONLY ONE AT A TIME. This can be a major frustration if you need to do this a lot, particularly since the command is buried a couple of levels down in the menu structure.

One of the most popular offerings on my the business website I run with my partner at grbps is an Excel add-in that rectifies this problem and allows you to hide and unhide multiple sheets with ease from a simple form.

You can download the add-in here. Save it wherever you like (preferably in the add-ins folder, but it doesn't really matter) and then browse to the file from Tools-Addins in Excel and your new toolbar will be available.

There is also an article you can read containing the source code which makes the add-in work. You can read it for free here.

At http://www.grbps.com I maintain a website for the business I run with my friend, Graham. Over time, I've posted quite a few little mini articles there on popular software, featuring the kinds of little tricks that have really made a difference to me. They're all based on questions people have actually asked on web forums or face-to-face. Here is one of the best ones.

Q: In Excel I have a large table of data I print out regularly. I would like to have every other row shaded like on old printout paper to make it easier to read along lines. I've tried doing it manually but when I move data around it spoils the shading. Is there an easy way to do this which won't get messed up when I move data.

A: Yes, there is. I use it all the time. You need to select the whole worksheet, then from the 'Format' menu, select 'Conditional Formatting'.

Change the pulldown which reads 'Cell value is' so it reads 'Formula is' and then enter this formula in the empty field:

=MOD(ROW(A1),2)=0

Next, hit the 'Format' button and on the 'patterns' tab choose a colour. The lighter ones on the bottom row of the palette work best.