Weekly Computer Tip # 65
8 May 2004

Restricting cell entries to specific entries in a list (Microsoft Excel)

Although I don't like filling the weekly tips with PR, I want to tell you about my exciting new service offering hands-off seminars for those of you who want to learn but who are put off by the very thought of spending a day in front of a computer in a classroom. Join me at the Møller Centre in Cambridge on 15 September for a day packed with information about how to use Microsoft Excel's underutilized features to make you more productive with less work. Sharing the best of what I know this seminar will motivate, enthuse and drive you to get a buzz out of using Excel.

You can reserve your seat for the seminar right now for GBP134.00 - but there?s more; as a weekly tip subscriber you're entitled to an additional discount of 10% bringing the price down to GBP120.50, a saving of GBP28.50. Places are limited so register today to reserve your place! (After 15th July your investment will be GBP149.00) For more information and a booking form see http://www.roem.co.uk/events.html

Now on with the tip, a sneak preview of one of the many features I will demystify on 15 September ... data validation.

When we have to remember a list of values to enter in an Excel worksheet we sometimes get it wrong; after all, we're only human! However,

Did you know ...

In Microsoft Excel you can use data validation to create a drop-down list of possible entries, which - in many cases - will make it quicker and easier as well as ensure accurate data entry.

Here's how:

1. Type the list of valid entries down a single column or across a single row in a separate range of cells in your worksheet.
2. Select the cell(s) that you want to restrict. This can be either a single cell or a larger range of cells, or even an entire column.
3. From the Data menu, choose Validation.
4. On the Settings tab, in the Allow dropdown list, select List.
5. Click the Collapse Dialog button in the right corner of the Source text box and drag to select your list of valid data.
6. Press [ENTER]
7. Select OK.

When you select a cell from the area you selected in step 2 a drop-down arrow appears. If you try to manually enter anything other than these values, a stop message appears and will not allow the cell to keep the invalid entry. The only options will be to Retry or Cancel.

If the list of valid entries is short, you can type them directly in the Source box on the Data Validation dialog box, instead of typing the entries on a worksheet, separated by commas. For example, you could type Miss, Mrs, Ms, Mr, Dr, Prof in the Source box instead of entering the six short entries on the worksheet, as described in step 1.

Until next week.

PS I’m committed to sharing the best of what I know with others so please don’t keep me a secret. If you enjoyed today’s tip, please forward it to anyone you feel may benefit. Alternatively, feel free to reprint it (with full copyright and subscription information) in your newsletters and message boards.

Home   Training   Support   Clients   Contact Details   News   Disclaimer