Report support: Keep your data clean with the Data Validation function

Filter By Topic


We understand the importance of clean data (see Tactics to keep your data clean), especially when you’re collaborating with teams across your organisation.

When you’re creating a spreadsheet for others to use, consider restricting what data users can input to ensure data entered is valid. Use Data Validation to restrict the type of data or the values that users enter into a cell. You can even create the error message they'll see.

One of the most common data validation uses is to create a drop-down list:

1. Highlight the cell(s) you want to create a rule for.

2. Go to the Data tab > Data Validation OR use Alt > D > L keyed separately to open the Data Validation dialog box.

3. On the Settings tab, under Allow, select List for drop-down menu. Note that there are other options that allow you to restrict to whole number, decimal, date, time, or text length.

4.Click in the Source box, then either type a list, with commas between the options OR click the button next to the Source field and then select your list range. You can also insert custom formulas, for example ISTEXT or ISNUMBER to restrict the type of data.

Tip:  Having your list items in an Excel table means that as you add or remove items from the list, any drop-downs you based on that table will automatically update.

5. Check the In-cell dropdown box.

6. Click the Input Message if you want a message to pop up when the cell is clicked. Check the Show input message when cell is selected box, and insert a title and message.

7. Click the Error Alert if you want a message to pop up when someone enters something that's not in your list. Check the Show error alert after invalid data is entered box, pick an option from the Style box, and insert a title and message.

8. Click OK.

For other tips on keeping your data clean, read our blog about Tactics to keep your data clean.