Google sheets validation help text not showing

Google Sheets is built for teams. Collaboration is one of G Suite’s strong suits and differentiators when compared to similar softwares. Because it was built for the web from conception, it easily handles multiple users in a single spreadsheet, whether or not you’re working at the same time.

However, many users working on the same spreadsheet can more easily cause errors. When you don’t know who made an error or why they made it, your interpretation of the data is compromised. Errant data can also break complex formulas that take time and resources to fix. Moreover, if you enter wrong data that erases important information, you might not be able to recover it.

On the other hand, when everything works correctly, spreadsheets are a wonderful tool. For example, I have this monthly sales data for a wholesale company. On the side, I made a little chart that tracks total sales for each sales agent. Make a copy of the data here to follow along.

If you're looking to reduce the chances of error in your Google Sheets spreadsheet by only accepting valid data, then data validation is exactly what you're looking for. With data validation, you can instruct Google Sheets to accept only a specific sort or range of data for cells, or to warn the user when they input invalid data.

Data validation is simple to use, but it requires a grasp of the concepts to be used properly. Read on to find out what data validation is and how you can use it in Google Sheets to improve your spreadsheets.

What Is Data Validation in Google Sheets?

Data validation is a Google Sheets feature that lets you limit the sort of input that your users can enter in specific cells. You can either use data validation to give the user a warning when they input invalid data, or to reject the invalid data altogether.

With the help of data validation, you can improve spreadsheets that are intended for other people's use. You can give limits to the value they can enter, or for instance, create a drop-down list of items they can choose from. Ultimately, you can also input a custom formula as the data validation criteria. This way, Google Sheets will reject the value if it doesn't satisfy the custom equation.

How to Use Data Validation in Google Sheets

Data validation is simple to operate in Google Sheets. The basic steps for using data validation are as below:

1. Select the cell or cell range that you want to use data validation on.
2. Go to the Data menu and then select Data Validation.
3. Enter your criteria.
4. Click Save.

Before we move on to examples where we deploy data validation, let's have a finer look at the elements of data validation.

• Cell range: This is the range where the input data will go through the data validation criteria. This can be a single cell or a range of cells.
• Criteria: This is the criteria where you indicate what sort of data is valid. The valid data type can be number, text, date, list, checkbox, or custom formula.
• On invalid data: This indicates Google Sheets' reaction to invalid data. You can choose either to let the invalid data pass and display a warning, or to reject the invalid data and display a warning.
• Appearance: Here, you can input the validation help text. This text will show up when the user inputs invalid data in a cell.

Now that you know what setting up data validation is about, let's see data validation in action with a couple of examples.

Data Validation Example 1: Setting a Number Value Limit

In this example, we have a sample spreadsheet designed to gather information on which books the user liked the most. The spreadsheet includes a column containing the names of the books, and another column waiting for the user's input. The user is supposed to rate each book out of 10.

Since the rating is on a scale of 10, and we don't have time to check every input, the goal is to limit the values the user can input to 1-10. The user shouldn't be able to give a book a rating below 1, or above 10. This calls for data validation.

1. Select the cells where you want to apply data validation. That will be cells B2 to B7 in this example.
2. Go to the Data menu and select Data Validation. This will bring up the Data validation window.
3. For Criteria, select Number and between.
4. Enter the number limit of your choice. For this example, we're going to enter 1 and 10.
5. Check Reject input. This will force the user to enter valid data.
6. Check Show validation help text.
7. Enter a validation help text to inform the user what sort of data is expected.
8. Click Save.

Now your table is ready for the user data. As a test, try rating one of the books with a value higher than 10, or lesser than 1. You should get an error and your data should get rejected. Now, if you input a value between 1 and 10, the input will be accepted. It's like clockwork!

Data Validation Example 2: Making a Drop-Down List

One of the popular uses of data validation is creating drop-down lists in Google Sheets. You can create a drop-down list to give the user a choice between items that you have decided as valid data.

In this example, we have the name of some students in a spreadsheet and the goal is to create a drop-down list where the user can choose their major.

1. Select the cells where you want to have a drop-down list. That will be cells B2 to B7 in this example.
2. Go to the Data menu and select Data Validation. The Data validation menu will open.
3. In criteria, select List of items.
4. In the textbox, enter the list of items. You can separate the items with a comma (,). For this example, we've entered the items below:

   Medicine,Pharmacy,Biology,Applied Math

5. Check Show dropdown list in cell.
6. Check Reject input.
7. Check Show validation help text.
8. Enter a validation help text.
9. Click Save.

You'll notice a small arrow in each cell. You can now click the cells to choose from a dropdown list. If you try to type in a value that's not in the list, your value will be rejected. Though, you can still type the items from the list.

If you want to add bullet points to the items in your list, read our article on how to add bullet points in Google Sheets.

Data Validation Example 3: Setting a Text Length Limit

You can use data validation with custom formulas to achieve all sorts of things in Google Sheets. Custom formulas allow you to set criteria that aren't in any of the criteria types built into data validation in Google Sheets.

As an example, let's create a cell that only allows 5 characters or less in it. We're going to do this using data validation.

1. Select the cell. That will be D1 in this example.
2. Go to Data and then select Data Validation.
3. In criteria, select Custom formula.
4. For custom formula, enter the formula below:

   =LEN(D1)<5

   This formula uses the LEN function to count the characters in the cell and returns true if the length is less than 5 characters.
5. Check Reject input.
6. Click Save.

Now, if you enter a name that is more than 5 characters in length, Google Sheets will reject it, and you'll have to try again. You can use the autofill function to apply this data validation to other cells.

Validate Your Data With Google Sheets

Data validation is a tool that enables you to limit what you or another user can input in specific cells. In this way, data validation can be useful in creating surveys to accept only valid data, or in your own spreadsheets to increase accuracy.

There are many types of criteria you can set using data validation in Google Sheets, and if the data type you want isn't built into data validation in Google Sheets, you can set criteria with a custom formula. Now you know how to use data validation in Google Sheets. Be assured that less invalid data will make your analysis easier and more accurate!

How do I show validation help text in Google Sheets?

Why is my Data Validation not working in Google Sheets?

How do I add help text in Google Sheets?