How do you add a tab in Google Sheets mobile?

Merging multiple tabs to one tab requires the use of filter function, but before we dive into that, let us first understand how a filter function works and the importance of curly brackets {} in google sheets. The filter function is a powerful yet very simple function to retrieve a set of data that matches one or more criteria. If you are already aware of the two functions you can directly skip to the merge section below.

Source: Pinterest

How does a filter function work?

Similar to what we do for other functions, we need to pass arguments to a filter function as well. The function takes two arguments, first the dataset or the range of values we need to filter from, and second the criteria for filtering the data. A typical filter function looks like =filter(Dataset, Filter_citeria). The output is a dataset that matches the criteria we passed as the second argument. Let's see a few examples to understand the function and the syntax better. Consider a hypothetical dataset that shows the average earning and the average working age for a country.

Hypothetical Dataset

The dataset to pass in the filter function: (A2:D11). Consider the following filter criteria and the respective arguments to pass:

Filter Function examples

Things to note:

1. The dataset passed is excluding the header.
2. The number of rows of the dataset and the rows of the filter criteria should match.

Curly Brackets are used to create arrays or tables in google sheets. We use curly brackets to combine a set of Rows and columns which are not continuous. For example, say, we need data from columns A1 to C5 and then stack the dataset of G1 to H5 below it, we would use the following formula:

={A1:C5;G1:H5}.

Please note the semicolon used in between the two datasets and do keep in mind that G1 to H5 would be stacked below A1 to C5. Also, Data inside curly brackets cannot be open-ended. Formulas such as ={A:C;F:G} would throw #REF error.

An example demonstrating data tack using { }Error for open-ended datasets

Merge: So how do we leverage the above two formulas to merge tabs in Google sheets? (Finally!)

Consider the following database of date wise COVID positive cases for Australia, India, and Japan in each of their respective tabs with names: Aus, Ind, and Jpn. Each dataset contains iso_code(Area code) in column A followed by location(country name), date, and total cases on that date. (data credit: ourworldindata.org) We subsequently want to merge the data of all the countries in one single tab. (Mastermerge)

Let us create a tab and name it MasterMerge and locate it to the cell where we want to start merging the data from other sheets. Now, to bring the data only from the Aus sheet we would use the filter function to fetch the database and put our criteria to add all the non-blank rows to our merge sheet:

=FILTER(Aus!A1:D,Aus!A1:A<>)

Argument1: Data from Aussheet spanning from cell A1 to the last cell of column D. Covering Column A to Column D.

Argument2: This is the filter criteria. Assuming we always have area code filled for entry, well consider only the non-blank entries for column A for data transfer. Likewise, only those rows would be added in the main sheet which has a non-blank entry in column A. This is done through adding the not-equal to a blank (<>) sign in the formula.

Now, you know what to do to add the data from tabs Ind and Jpn below that of Aus, yes, use curly brackets and add semi-colon between each filter formula.

{FILTER(Aus!A1:D,Aus!A1:A<>);FILTER(Ind!A1:D,Ind!A1:A<>);FILTER(Jpn!A1:D,Jpn!A1:A<>)}

To add data from more rows just keep adding filter formula for each tab and separate the same from others using a semi-colon.

What if we have more no. of tabs and not able to keep an account for the tab names?

In such cases, please use the app script to automatically read the tab names and subsequently merge the sheets to a master tab. For those not familiar with app script coding. I have put a code in the following GitHub link. Just navigate to tools-> Script editor on your google sheet and paste the code to your google sheet console. The code would first ask for permission to run, do not worry, after giving the permission, the code would create a Merge Sheets menu. Upon clicking the drop-down, the code would create a tab MasterMerge and combine all the tabs onto that.

In case, you want to have an add-on to conduct this task. Please keep checking the products section, the link to the add-on will be updated once approved by google.

Goodbye tip: Use GOOGLETRANSLATE() function to translate sentences from one language to another in google sheet.

=GOOGLETRANSLATE(text, [source_language], [target_language])