One of the ten dos and don’ts I always recommend to anyone who uses Excel as a database is “use data validation”. Whereas it can be used to reject invalid dates or to restrict the values that you can enter, I’ll kick this off describing a quick way to create a drop-down list containing specific text entries.
That way you can benefit from Excel’s powerful built-in tools to quickly summarise information, saving you countless hours consolidating, for example, inconsistent county entries such as Cambridgeshire, Cambs, CB etcetera.
Here’s how:
1. Select the cell(s) or column in which the restriction is to apply.
2. Click on the Data Validation button in the Data Tools group on the Data tab. (No need to click on its drop-down arrow.)
3. Expand the Allow box and select the List option.
4. Click in the Source box and type (or copy and paste) a list of entries, separated by commas. (For example: Bedfordshire, Berkshire, Buckinghamshire, Cambridgeshire, Cheshire, Cornwall, etcetera.)
5. Press ENTER.
In future, click on the drop-down arrow or use the ALT + down arrow trick I wrote about in May to select the appropriate entry from the drop-down list.
Finally, if you were wondering what the other nine of the ten dos and don’ts are …
1. Avoid empty rows and columns.
2. Do not merge cells.
3. Put your headings across columns, horizontally, rather than vertically.
4. Use separate columns for first name and last name.
5. Use dd/mm/yyyy date formats.
6. Do not immediately worry about sorting and formatting.
7. Put all raw data in one sheet instead of a sheet per month.
8. Use Excel Tables and pivot table reports and/or charts.
9. Add totals to a table.
By the way, if you want to know more about managing and summarising your data why not book an hour of virtual training? I’m using Zoom, which is easy to set up. If you’ve never used Zoom and are a bit worried we could run a quick, free test.
Alternatively, why not book yourself on the next half-day face-to-face session, planned for 7 August? See http://roem.co.uk/msexctables.php
Related tips
- Enter text from a list of previous entries
- Find and select data validation
- Total the data in a table
- Quickly format and enable filtering of your data
10 June 2020
Karen Roem offers software training and support through her company Roem Ltd. Contact her by email (Karen@roem.co.uk) or visit her website at www.roem.co.uk