Data Validation in Google Sheets

How to Track Income in Google Sheets: Data Validation + Filter Formula

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.

If you prefer watching to reading, check out the YouTube version of this tutorial.

Why you need data filtering

When 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.

Data Validation in Google Sheets

How to create an income monitor

Imagine 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.

Dynamic drop-down lists

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

Google Sheets data validation

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.

Gsheets data validation

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.

Spreadsheet data validation

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:
={"Regions"; Unique(Payments!C2:C)}

Data Validation in Google Sheets

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.

Google Sheets data validation

Dependent drop-down lists

It 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:
={"Countries";
FILTER(Payments!D2:D,Payments!C2:C=B2)
}

"Countries" refers to the header.
Payments!D2:D denotes the range to filter (the countries column in the Payments tab).
Payments!C2:C=B2 denotes the criteria to match (the region column in Payments) to match the region selected in the monitor.
FILTER(Payments!D2:D,Payments!C2:C=B2) refers to filtering the countries where the region is equal to the one selected in our monitor. Make sure you’ve formatted your formula and hit Enter.

Gsheets data validation

Ok, so now you have the whole list of countries from the selected region filtered out here.

Spreadsheet data validation

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.

={"Countries";
Unique(
FILTER(Payments!D2:D,Payments!C2:C=B2)
)
}

Data Validation in Google Sheets

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).

Google Sheets data validation

Now, if the region changes, the country might get marked as invalid.

Gsheets data validation

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.

={"Cities";
Unique(
FILTER(Payments!E2:E,Payments!D2:D=B3,Payments!C2:C=B2)
)
}

Payments!E2:E – set to filter
Payments!D2:D=B3 – Countries to match the one in the monitor
Payments!C2:C=B2 – Region to match the one in the monitor

Hit enter.

Spreadsheet data validation

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”.

Data Validation in Google Sheets

Color coding to format a spreadsheet

If 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).

Google Sheets data validation

Calculating income

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

  • Use SUM(FILTER
  • Select the Payment Amount column from the datasource to be filtered – Payments!G2:G
  • Select criteria to match the values above
    • Region in the datasource to match the Region selected in the monitor – Payments!C2:C=B2
    • Country in the datasource to match the Country selected in the monitor – Payments!D2:D=B3
    • City in the datasource to match the City selected in the monitor – Payments!E2:E=B4

You should get the following:
=SUM(
Filter(Payments!G2:G,Payments!C2:C=B2,Payments!D2:D=B3,Payments!E2:E=B4)
)

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:

=IFERROR(
SUM(
Filter(Payments!G2:G,Payments!C2:C=B2,Payments!D2:D=B3,Payments!E2:E=B4)
),
"check if Region, Country, and City match")

And here it is – the sum of the payments based on the multiple criteria.

Gsheets data validation

You can apply this approach to different contexts:

  • to see the list of clients in the region
  • to track unpaid invoices, etc.

    Using other formulas, like FILTER, provides more capabilities to track data. FILTER lets you pull information about clients and their payments based on the Region, Country, and City you select. For this:

    • add headers first – open braces and pull all the headers from the datasource (from A1 to H1) – =Payments!A1:H1;
    • type IFERROR(FILTER( and specify values from A2:H and the filter criteria (Region, Country, and City) to match the values from the monitor – Payments!A2:H,Payments!C2:C=B2,Payments!D2:D=B3,Payments!E2:E=B4

    Here how it looks: ={Payments!A1:H1;
    IFERROR(
    Filter(Payments!A2:H,Payments!C2:C=B2,Payments!D2:D=B3,Payments!E2:E=B4)
    )
    }

    If all criteria match, you’ll see a list of payments from different clients.

    Spreadsheet data validation

    If criteria don’t match, the list will be empty. You can discover more tips and tricks with Filter formula in our video.

    That’s it, you income monitor is ready to use. Google Sheets still has many quirks, and the Railsware team will be demystifying them for its readers and viewers in upcoming posts. So, read our blog and subscribe to our YouTube channel to get more interesting insights.