Kick off the new school year with lessons from our Back to School 2022 collection to help students express themselves, build relationships, and stay organized.

3. Rate Locations with Data Validation

Playback Speed:

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

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

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

To use data validation, click on the area guide tab in your spreadsheet.

Select the rating column by clicking the column header.

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, open data validation.

You’ve already selected the cell range.

These are the cells 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.

Make sure "List from a range" is selected.

Then, select the range.

Click the grid symbol.

Then, click the Rating tab.

Highlight the column containing your rating scale.

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

Save your changes.

Then, 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.

Nice work!

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

Go back to your guide.

The change appears automatically in all of the dropdown menus in the range.

That’s much easier than typing in a change over and over in the whole column.

Right now, the first cell in the column shows an error message because it doesn’t contain text from your ratings list.

To fix this, open the data validation menu.

Then, remove data validation from this cell.



  1. Select the “rating” column.
  2. From the Data menu, choose “validation.” Then select “list from a range.”
  3. Open the range criteria grid and click the “rating” tab.
  4. Highlight the column containing the rating scale.
  5. Save your changes.
  6. Rate your locations.
  • "Happy Alley by Kevin MacLeod" by ( -- Licensed under Creative Commons: By Attribution 3.0 License (