Vba excel validating data in cells

Use Data Validation to allow specific entries in cells on a worksheet.

For example, show a list, limit the cells to whole numbers, or text of specific length.

See the examples and instructions here: Data Validation If you allow Dates, you can set or exclude a range of dates, or specify a minimum date or maximum date.

This may not be the most glamorous of the advanced Excel skills to learn, but it will improve the quality of your spreadsheets, saving time and errors down the line.

Create dependent drop down lists Validate entry of email addresses Prevent duplicate values in Excel As an Excel user you will often receive spreadsheets from clients, import data from a database, or download it from a website.

Even an Excel superhero will produce wrong results if the data being used is incorrect.

It is therefore a useful skill to do what you can to aid data entry, and then validate the data to prevent inaccurate data getting onto your spreadsheets.

These spreadsheets can be poorly structured or contain data in the wrong format.

Excel contains many formulas and features that allow us to work with this data and clean it ready for us to work with.

Keep reading, to see some examples of this technique, and how it works.

There is also a video that shows the multiple selection drop down list.

Go to Insert | Name | Define and type a name Tax Rate, for example.

In the Refers to area, type the constant value and click on OK. For example, enter the formula =5000*Tax Rate and Excel will use the constant value defined for Tax Rate and return 450.

They would have a commonly used list of functions that they know well, but also the skills and confidence to adapt to any situation requiring formulas.


  1. She currently has 68 gallery links and 6 videos in her own Free Ones section.

Comments are closed.