Google Sheets: 10 formulas to squeeze your data

Google sheets or Google spreadsheets have become today an essential tool for many companies. And it’s that they offer most of the features of traditional spreadsheets with the advantage of being in the cloud. In this way, we can work by teams making changes individually on the same file that is saved in real time.

Once we have created our Google Drive Environment With A Google account, we can begin to work our data for analysis. Because It depends on the nature and complexity of our data but, in most cases, it will be necessary to perform some transformations (regroup, reorder, select data, etc.) and then draw conclusions with our analysis and/or Graphic representation.

1. CONCATENATE

This formula can be used to unite data that we have been provided separately in columns but we are interested in having them together. In this way we link values of various columns, but we can also append a constant value that we want to introduce:

CONCATENATE(value1; value2)

In The example we also use the blank space to delimit, but we can use any other value if we want to separate.

2. IF

Returns a value if an expression is true and another if the expression is false.

IF(logic_expression; value_if_true; value_if_false)

The second value is optional (if omitted, a false will appear when it is fulfilled) but it is important to make sure that the order of values is correct, always including first the value if the expression is true.

Here is a very simple example of the function IF to categorize a numeric variable:

3. SUM.IF

A variant of the previous formula, but in this case we will add the values of a range only if they meet the condition that we indicate.

SUM.IF(interval; criterion; [interval_sum])

It is useful to obtain the summation of a group of products based on a text, as in the following example (when it contains the text “Tomato”), but we can also establish a numerical criterion, add when a value is positive, for example.

4. COUNT.IF

Useful also to know information about a certain subset of the data that we have. This formula returns a simple count of the records that meet a certain condition.

COUNT.IF(interval; criterion)

In the following example, it allows us to know how many students are of age. But we could also count with more than one criterion and for them we would use the formula COUNT.IF. Set

5. VLOOKUP

Allows us to search vertically for a value within a column or range and returns the value of a particular cell in the found row.

VLOOKUP(search_value; interval; index; [is_ordered])

It is recommended to specify FALSE in the discussion Está_ordenado For most of the cases because, by default it is indicated as TRUE, unless we have ordered the first column of the interval.

In this example, we want to look for an ID in a list of products and know what specific product it is (column 2 of the range).

6. IF.ERROR

This formula of Google Sheets can be combined with other formulas to avoid that we get an error message, for example, the # DIV/0! By dividing an amount between zero. Specifically we return the first argument if the value does not offer any error and returns the second if there is error.

IF.ERROR(value; [value_if_error])

For example, we want to indicate that you have to review an operation where units have not been sold but have generated cost.

7. IMPORTRANGE

Sometimes it happens that we need to insert a data range or a complete sheet of another document into a new Google Sheets document.

IMPORTRANGE(calculation_sheet_key; interval_string)

Once we have given the necessary permission to access the source document, this link will be dynamic, that is, any changes we make to the source data will be replicated to the destination sheet.

8. INDEX

It returns us the contents of a cell, specified by row and column number.

INDEX(reference; [row]; [column])

Where reference is the matrix of cells to move through, and row and column (optional), to indicate the position of each of them if we want specific positions. 

On the other hand, Google Sheets does not have a LAST () function to show the last value of a range, but then we can see how by combining the simple formula INDEX () we can achieve it.

And sometimes the data sets that we have in Google Sheets are not fixed, there are updates that alter the rows and / or columns, periodically adding new values. This would not be a problem if we use the data in a pivot table or give it any other output, but if we have referenced formulas, they may stop working with these periodic updates. 

Therefore, by combining the formulas INDEX () and COUNTER () we can get the last value of a data interval, or, for example, also obtain the difference between the last row and the immediately previous one. 

=INDEX(A:A; COUNTER(A:A); 1)

We observe that, without modifying the formula, when adding a new row, the value that returns us has been updated:

9. SPLIT

It allows us to divide the text of a cell based on a specific character or string. We can consider it the inverse formula to concatenate that we have commented at the beginning of the post.

SPLIT(text; delimiter; [divide_by_character]; [remove_empty_text])

Where text represents the text to be divided and the delimiter the character or characters to be used for the division of the text. And you have to keep in mind that this character or characters are not going to be included in the final result.

With  divide_by_character (TRUE, by default), the formula considers each delimiter character individually, while if we indicate it as FALSE, the formula considers the delimiter as a whole.

While with remove_empty_text (TRUE, by default), we indicate the formula to eliminate empty values ​​from the results obtained. 

While if we add FALSE, the formula will treat the delimiter @. as a whole and will not make any separation, since in the text we do not find the delimiter together.

10. DYNAMIC TABLE

The dynamic tables of Google Sheets are an essential complementary to the formulas if we are working with a certain volume of data. They represent in an aggregate way the data we have and we can sort or filter them to have a synthesized view. 

To open a dynamic table of our data, simply select the set that we want to display and click following the path Data> Dynamic Table

For the previous data set, a simple example would be to order the fruits and classify them according to the season at that correspond, showing the sum of sales. 

We also have the option to show average values ​​or other statistical values, instead of the summation. In addition to modifying the order of rows-columns. And another of the options that can help us most are Filters, allowing us to show only part of our data.

Therefore, we have multiple options and, as we have more dimensions and metrics, we can go across different tables to have a better knowledge of our data.

And these are some of the Google Sheets formulas to work with data, although here is the Google Worksheet Feature List complete.

The interesting thing is that these formulas can be nested and get more advanced operations, as we have seen. It is not uncommon to work with 3 or 4 combined formulas for more specific uses.

Did you know these formulas from Google Sheets? What other formulas do you use in Google Sheets to transform your data?