Rate Locations with Data Validation

In this video, you will add a dropdown menu for location ratings using data validation.

Data validation will use the data from the rating sheet you made to create a dropdown list in the rating column of your guide.

That way, you can select the rating rather than typing it in every time.

To begin, open the area guide tab in your spreadsheet.

Next, select the rating column.

This group of selected cells is called a range.

Data validation and formulas in Google Sheets often use data from a range to populate cells with correct information.

With the range still highlighted, add a data validation rule.

The range you selected is where the dropdown menu will appear.

Next, choose how you will populate the dropdown list.

In this case, you will use the list you made in your Ratings sheet.

Then, select the column that contains the list of ratings you created.

This is the range of values that will appear in the dropdown list.

Then, save your data validation rule and return to your area guide to try it out.

The rating column now contains dropdown menus with your rating scale.

Use the dropdown menus to rate each of the locations in your guide.

If you want to add or change an item in your rating scale, just return to the rating sheet and make your changes.

The change automatically appears in the dropdown menus of all cells that the data validation rule applies to.

So you can just select the new option in the menu rather than typing the change in each cell.

If necessary, adjust the rating column's width.

Finally, check that no error messages appear in cells with data validation.

If column headings are included in your data validation rule, they will produce an error message because it doesn't contain text from your ratings list.

To fix this, open the data validation menu and remove this cell from your data validation rule.

The range currently starts in cell one of your rating column.

Adjust the range in your data validation rule to begin after the column heading.

Now, it's your turn: Select the "Rating" column and open the data validation menu, create a data validation rule that uses your list of ratings, rate all of your locations, and remove the column heading from your data validation rule.