This is such as a good tip, originally from Lynda Morris, of NicLyn Consulting, that I have expanded on it a little and include it here to share it with others.

If you often type the same sequence of data into Excel you may have concerns over consistency of data, as it can be easy to mistype a phrase, or word when working with large lists. If you then utilise macros or other data valuation tools your calculations could be thrown out due to inconsistent entries.

Fortunately, this can be easily resolved by setting up a Data Validation function of type List. To get started create a list of each unique word you want to use in your list, for example take the following department list: Finance, Human Resources, Information Technology, Sales, Marketing, Customer Service, Quality Control, Warehouse, Despatch, Goods Inward, Logistics Support, and Production.

If you create a new worksheet in your document and list them then highlight the list, right click and select Define Name, then give the list a relevant name that you can remember and click OK.

Now, select the area where you want to regularly enter the information contained in this list.

Once the area is selected, head into the Data Validation option which can be found on the Data icon from the ribbon bar.

From the Data Validation dialog box, ensure you have the Settings tab active and change the pull down list under “Allow” to read “List”.

Next, in the Source field type an equal sign followed by the name you defined in the previous step, so in this case we would use =Departments.

Ensure the “In-cell dropdown” setting is checked and click OK – you should see a small drop down arrow appear next to the first cell in your highlighted list.

 

One of the best things about this list functionality is that as you add items to your master list, the data validation tool will automatically update the list – so your information should remain consistent.