Last time we talked about different options for validating data in Google Sheets. You’ve already learned how to create simple drop-down lists using Data Validation. Today, we’re going to dive deeper. You’ll discover how to build business metrics monitors using Data Validation, the FILTER formula, and dependent drop-down lists. Also, you’ll learn a few formatting tips and tricks. Show
If you prefer watching to reading, check out the YouTube version of this tutorial. Why you need data filteringWhen you need to filter data in Google Sheets, it means you have to set rules to display certain information. This function is rather useful when you work with large datasets. Filtering lets you focus on relevant data and increase your performance. That’s it for the boring theory stuff. Let’s try hands-on. For this, you can get a personal copy of the Google Sheets practice file with Data Validation task for exercising. As a dataset, we’ll use the payments database we often use in other Google Sheets videos. How to create an income monitorImagine you want to have an income monitor based on the locations. There are clients from different regions, countries, and cities. You need the results to change depending on the exact location you select. Moreover, you can connect the income monitor to your CRM app and get it updated automatically using Coupler.io. Check out the blog post, How to Build Sales Tracker with Google Sheets, to learn details. Dynamic drop-down listsFirst, create a dynamic drop-down list. For this, get a separate tab called “Income Monitor”. We recommend you store data sources and calculations on separate tabs. Now, mark the monitor and freeze the first row, as it will contain headers. You can do this by pulling the marker. Then, mark where you’ll have Region, Country, and City in the monitor. Now, let’s apply the standard data validation to the region. To do this, click on the cell next to it, use Control+Option+D (Ctrl+Alt+D for Windows) and then press V. Or you may right-click on the cell and select Data Validation in the bottom of the list. In the menu, set the List from a range criteria and select the Regions column in the Payments Tab. Don’t forget to specify the whole column in the Select a data range box, so that you don’t miss anything when the new data is entered. For this, enter ‘Payments’!C2:C. Google Sheets automatically pulls unique values to the dropdown list, so that you don’t see duplicates. Here is what you should get in your Income Monitor tab. Also, let’s pull unique regions to the list on the right, so it will be visible. It will be easier to validate the region based on the list in the same tab. So, let’s use these values for data validation in the monitor. Select a cell and use braces to add a header right in the formula. Start from the header and then write down the formula: The unique formula pulls unique values from the Regions column in the Payments tab. You may split the formula into few lines to make it more readable. Press Enter. Dependent drop-down listsIt will be easier to see the list of cities only from the selected regions and countries. Besides, you won’t have to remind yourself whether Bergen is in Sweden or Norway every time. So let’s use the dependent drop-down lists. For this, you need to create a few helpers. First, use the Filter formula to list the countries from the selected region. You can learn more about how to use Filter formulas in
our tutorial. Select a cell and write down:
Ok, so now you have the whole list of countries from the selected region filtered out here. To get rid of the duplicates, you can apply UNIQUE. Wrap the FILTER in parents and put UNIQUE before it. This will pull the unique values from the filtered list.
Now, if you select a different region, the countries list will change as well. Let’s apply data validation to the Country in our monitor. Control+Option+D (Ctrl+Alt+D for Windows), then V, or right-click on the cell and select Data Validation in the bottom of the list. As a data range, we select Countries (L2:L). Now, if the region changes, the country might get marked as invalid. Do the same to validate City. But here you need two conditions in the FILTER formula – for both Region and Country to match. So add one more condition.
Hit enter. If for some reason, Region and Country don’t match, the Cities list won’t show. It may happen when you change Region but not Country. This is an additional reminder for you to select the matching region and country. Now, add the Cities range as a criteria to validate City in our monitor. Perform the regular operation to apply data validation and select a data range (N2:N). You can also mark Reject input on invalid data and add a note (mark Show validation help text) “Select a city from the list”. Color coding to format a spreadsheetIf you want to visually mark the structure, color coding can help you with this. It is also very helpful when multiple users work with your spreadsheet. You can color the cells with formulas, as well as the cells with dynamic data, to make sure nobody re-writes them. Use different colors for outputs and calculations. You can also delete the unneeded rows. To make datasets visually independent from each other, add color boundaries between them (like in columns K and M). Calculating incomeNow, it’s time to calculate values you want to see. As an example, let’s find out the total income from the clients in a particular city. This will be Bergen.
You should get the following: To trap and manage errors, apply IFERROR formula to the Filter. If an error occurs, add a message “Check if Region, Country, and City match”. Here how it looks:
And here it is – the sum of the payments based on the multiple criteria. You can apply this approach to different contexts:
How do I add Data Validation to Gsheet?Change or delete a drop-down list. Open a spreadsheet in Google Sheets.. Select the cell or cells you'd like to change.. Click Data. Data validation.. To change the options listed, edit the items next to "Criteria.". To delete a list, click Remove validation.. Click Save.. Does Google Sheets have Data Validation?Both Google Sheets and Microsoft Excel support a built-in mechanism called data validation rules. Data validation rules allow you to constrain the values that can be entered into a worksheet cell. You can define one or more data validation rules for your worksheet.
How do I create a Data Validation formula in Google Sheets?How to Use Data Validation in Google Sheets. Select a cell or cell range.. Click Data on the menu bar.. Select Data Validation.. Click the Criteria list arrow. ... . Select a criterion.. Configure the selected criteria options. ... . Choose how to handle invalid data.. (Optional) Add validation help text.. Can you do Data Validation in Google Docs?Choose "Validation..." (or, you can replace step two and three by right clicking on the cell that you want to become your drop down cell, and choose "Data Validation". Type in the Cell Range. This is the Sheet and Cell where the drop down box will be.
|