Data Validation in Google Sheets​


Data validation is that feature of Google Sheets that comes into picture when you need to collaborate with people who will be entitled to make changes in your sheet. Sometimes when you share your google sheet with someone giving them the editor rights, they may enter wrong information below the wrong column and this can make your work hard. Thus to specify and validate what kind of information can be entered under a particular range can be fixed to prevent confusions. For example, under the date column if you want only numeric to be entered then you can validate only numbers and no other person will be able to enter alphabets into the same. Performing this task includes few steps and these can be learned through this article.

Setting Up Named Ranges

When we use a formula, to simplify our work, we can name our ranges according to our understanding to put them in formulas instead of putting them as A1, B5 or C6. To do this –

  1. Make a range for all the data you want to display.
  2. For the item you wish to sort by, create a range. Foe example, for a range of a specific information like D.O.B.
  3. Make a range that only covers a cell you wish to use as the drop down menu’s placement. This will be done on a separate sheet.

Validation

This step will create a cell with a drop down menu for sorting through your data. For this –

  1. Select the cell that will serve as your drop-down menu box. It needs to be near the top of the page.
  2. Click on data from the menu bar and further from pop-up choose data validation option. OR, right click on the cell you wish to be your drop-down cell and select data validation to skip step two and three.
  3. Fill in the cell range field. This is the sheet and cell that will house the drop down box.
  4. Choose ‘List from a range’ as the criteria and then type in your range. ‘sheet’range! will be used to represent the range. The sheet is the one from which you’ll be pulling your data. And the range is the column that contains all of the options in the drop-down menu that you desire. Make sure your range begins with the second cell for ex. A2:A. Because if you’ll choose column heads or names, it’ll not work properly.
  5. To see the list in your cell, check the option to show the ‘ in cell button’.
  6. To assist someone in navigating the drop-down, type in some help text.

Setting Up Filter Formula

Now we will create a formula that will bring all of the data to this page and filter it using the validation drop-down.

  1. Choose a cell where you want your data to be filtered in.
  2. And now fill in the formula. The syntax for the formula will be – =filter(source array, array condition) here, source array, means all the data from your sheet will be bought into this new sheet. array condition, implies that all of the information from your form replies sheet will be transferred to the new sheet.
  3. This indicates it will search for what’s mentioned in the range named range, for example if we name it as devices, the cell where your drop down validation is located is in the “DeviceName” range. As a result, when you select a device from the drop-down menu, it will only show information about that device.

Harshita Mathur

My name is Harshita Mathur. I come from Jaipur, Rajasthan. I am a law student. Apart from this I am an amateur writer.

Recent Content