Useful Google Sheets features not found in Excel

  • Tutorial
The article was co-written with Renat Shagabutdinov.

image

This article will focus on several very useful Google Sheets features that Excel does not have (SORT, array combining, FILTER, IMPORTRANGE, IMAGE, GOOGLETRANSLATE, DETECTLANGUAGE)

There are a lot of letters, but there are parses of interesting cases, all examples, by the way, can be considered closer in Google Doc goo.gl/cOQAd9 ( file- > create a copy to copy the file to your Google Drive and be able to edit).

Table of contents:

- If the result of the formula is several cells
- Combining several data ranges for use in formulas
- SORT
- How to add table headers to SORT?
- FILTER
- FILTER, two conditions and work with the date
- Interactive graph using FILTER and SPARKLINE
- IMPORTRANGE
- Import formatting from the source table
- IMPORTRANGE as an argument to another function
- IMAGE: add images to cells
- GOOGLETRANSLATE and DETECTLANGUAGE: translate text in cells


If the result of a formula occupies more than one cell

First, an important feature of displaying the results of formulas in Google Sheets. If your formula returns more than one cell, then this entire array will be displayed immediately and will occupy as many cells and columns as needed (in Excel, for this you would need to enter an array formula into all these cells). In the following example, let's see how this works.


SORT


It will help to sort the data range by one or several columns and immediately display the result.

Function Syntax:
= SORT (data to be sorted; column_for_sorting; ascending;; [column_for_sorting_2, ascending_2]; ...])

The example in the screenshot below, we entered the formula only in cell D2 and sort the data by the first column (instead of TRUE / FALSE, you can enter TRUE / FALSE).
(hereinafter - examples for Russian regional table settings, reg. settings are changed in the file menu → table settings)

image

How to add table headers in SORT?

Using curly braces {}, we create an array of two elements, the headings of table A1: B1 and the SORT function, and we separate the elements from each other using a semicolon.

image

How to combine several ranges of data and sort (and not only)?

Let's look at how you can combine ranges for use in functions. This applies not only to SORT, this technique can be used in any functions where it is possible, for example in VPR or SEARCH.

Who read the previous example already figured out what to do: open the curly brace and collect arrays for combining, separating them from each other with a semicolon and close the curly brace.

image

You can combine arrays and not use them in the formula, but simply display them on a sheet, for example, by collecting data from several sheets of your book. For vertical joining, it is necessary to observe only the same number of columns in all fragments (we have two columns everywhere).

image

And in the screenshot below - an example of horizontal union, it uses a backslash instead of a semicolon and it is necessary that the number of lines in the fragments matches, otherwise the formula will return an error instead of the combined range.
(semicolon and backslash are separators of the array elements in the Russian regional settings, if the examples do not work for you, then through the file are the table settings, make sure that you have them)

image

Well, now let's go back to the horizontal array and insert it into the SORT function. We will sort the data by the first column, in descending order.

image

Joining can be used in any functions, the main thing is to observe the same number of columns for vertical or rows for horizontal joining.

All of the parsed examples can be viewed closer in the
Google Doc .

Filter


Using FILTER, we can filter data according to one or several conditions and display the result on a worksheet or use the result in another function as a data range.

Function Syntax:
FILTER (range; condition_1; [condition_2; ...])

One condition

Example, we have a table with the sales of our employees, we derive from it data for one employee.

We introduce the following formula into cell E3:
= FILTER (A3: C7; B3: B7 = “Natalya Chistyakova”)

Please note that the syntax is slightly different from the usual formulas, like SUMMESLIN, where the range of the condition and the condition itself would be separated using a semicolon.

image

The formula entered into one cell returns an array of 9 cells with data, but after the examples with the SORT function we are not surprised by this.

In addition to the equal sign (=) in the conditions, you can also use>,> =, <> (not equal), <, <=. For text conditions, only = and <> are suitable, and for numbers or dates you can use all of these characters.

Two conditions and work with the date

Let's complicate the formula and add another condition to it, according to the date of sales, we will leave all sales from 02/01/17.

So the formula will look like, if you enter the condition arguments immediately into it, pay attention to the conversion of the date text entry when Help DATEVALUE:
= FILTER (A3: C7; B3: B7 = “Natalya Chistyakova”; A3: A7> = DATEVALUE (“02/01/17”))

Or like this, if you refer to cells with arguments:
= FILTER (A3: C7; B3: B7 = I6; A3: A7> = J6)

image

Interactive graph with FILTER and SPARKLINE

Do you know how else to use the FILTER function? We can not display the result of a function on a worksheet, but use it as data for another function, for example, a sparkline. Sparkline is a function that builds a graph in a cell based on our data, a sparkline has many settings, such as the type of graph, the color of elements, but now we will not dwell on them and use the function without additional settings. Let's move on to an example.

Drop-down list. Our schedule will change depending on the selected employee in the drop-down list, we do the list like this:

  • select cell E2;
  • menu Data → Data Check;
  • rules: value from the range and in the range we select a column with employees from the source data, do not worry that the names are repeated, only unique values ​​will remain in the drop-down list;

image

Click “Save” and get a drop-down list in the selected cell:
image

A cell with a drop-down list will become a condition for the FILTER formula, write it.
= FILTER (C3: C7; B3: B7 = E2)

And insert this formula into the SPARKLINE function, which, based on the data received, will draw a graph in the cell.
= sparkline (FILTER (C3: C7; B3: B7 = E2))

image

This is how it looks in dynamics:

gif

And here is how SPARKLINE with additional settings can look elegant, in real work, the chart displays the results of activity in one day, the green bars are positive values, the pink bars are negative.



IMPORTRANGE


Google Sheets uses the IMPORTRANGE function to transfer data from one file to another.

In what cases can it come in handy?

  • You need up-to-date data from your peers file.
  • You want to process data from a file that you have “View Only” access.
  • You want to collect tables from several documents in one place to process or view them.

This formula allows you to get a copy of a range from another Google Sheets. At the same time, formatting is not portable - only data (what to do with formatting - we will tell a bit below).

The syntax of the formula is as follows:
IMPORTRANGE (spreadsheet key; range string)
IMPORTRANGE (key; range)

spreadsheet_key (key) - a sequence of characters of the attribute "key =" (key) in the link to the table (after "spreadsheets / ... /").

An example of a formula with a key:
= IMPORTRANGE ("abcd123abcd123"; "sheet1! A1: C10")

Instead of the table key, you can use the full link to the document:
= IMPORTRANGE (" docs.google.com/a/company_site.ru/spreadsheet/ccc?key=0A601pBdE1zIzHRxcGZFVT3hyVyWc "; "Sheet1! A1: CM500")

Your file will display the range A1: CM500 from Sheet1 from the file that is located on the corresponding link.



If the number of columns or rows can change in the source file, enter the open range in the second argument of the function (see also the subsection “Ranges of the form A2: A”), for example:
Sheet1! A1: CM (if rows will be added)
Sheet1! A1: 1000 (if columns will be added)

! Keep in mind that if you load an open range (for example, A1: D), then you cannot manually insert any data into the A: D columns in the file where the IMPORTRANGE formula is located (that is, in the final place where the data is loaded). They seem to be “reserved” for the entire open range - because its dimension is not known in advance.

You can enter a link to a file and a link to a range not in the formula, but in the cells of your document and refer to them.

So, if in cell A1 you enter a link to a document (without quotes) from which you want to load data, and in cell B1 - a link to a sheet and range (also without quotes), then you can import data using the following formula:

= IMPORTRANGE (A1; B1)



The option with cell links is preferable in the sense that you can always easily go to the source file (by clicking on the link in the cell) and / or see which range and from which tab are imported.

Import formatting from source table


As we have already noticed, IMPORTRANGE only loads data, but not the formatting of the original table. How to deal with this? Prepare the soil in advance by copying the formatting from the source sheet. To do this, go to the source sheet and copy it into your book:

image

After clicking the Copy to ... button, select the book into which you will import the data. Usually, the table you need is on the Recent tab (if you really recently worked with it).

After copying the sheet, select all the data (by clicking on the upper left corner):

image

And press Delete . All data will disappear and formatting will remain. Now you can enter the IMPORTRANGE function and get the full correspondence of the source sheet - both in the data part and in the format part:

image

IMPORTRANGE as an argument to another function

IMPORTRANGE can be an argument to another function if the range you are importing fits this role.

Consider a simple example - the average value of sales from the range found in another document.

This is the source document. Let the data be added and we need the average of 2016 sales (that is, from cell D2 to the stop down).

image

First we import this range:
IMPORTRANGE (" docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4 "; "Books! D2: D")

And then use this as an argument to the AVERAGE function:
= AVERAGE (IMPORTRANGE ( « docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4 »; «! Books D2: D»))
= AVERAGE (IMPORTRANGE ( « docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4 » ; "Books! D2: D"))

image
We get a result that will be updated when new lines are added to the source file in column D.

IMAGE: add images to cells


The IMAGE function allows you to add images to Google Sheets.

The function has the following syntax:
IMAGE (URL, [mode], [height], [width])

URL is the only required argument. This is a link to an image. The link can be specified directly in the formula, using quotation marks:
= IMAGE (“http://shagabutdinov.ru/wp-content/uploads/2015/12/Run-or-Die.jpg”)

image

Or put a link to the cell in which the link is stored:
= IMAGE (B1)

image

The latter option is more convenient in most cases. So, if you have a list of books and links to covers, one formula is enough to display them all:

image

In practice, it happens that links to images are stored on a separate sheet, and you get them using the VLOOKUP function or somehow otherwise.

image

The mode argument can take four values ​​(if you skip it, it will be the first by default):

  1. the image is stretched to the size of the cell while maintaining the aspect ratio;
  2. the image is stretched without maintaining the aspect ratio, completely filling
  3. the image is inserted with the original size;
  4. you specify the size of the image in the third and fourth arguments to the [height] and [width] functions. [height], [width], respectively, are needed only if the argument value is mode = 4. They are set in pixels.

Let's see how images with four different values ​​of the mode argument look in practice:

image

The fourth mode can be convenient if you need to select the exact image size in pixels, changing the parameters height (height) and width (width). The picture will be updated immediately.
Please note that in all modes except the second one, there may be blank areas in the cell, and they can be filled with color:

image

GOOGLETRANSLATE and DETECTLANGUAGE: translate text in cells


Google Sheets has an entertaining GOOGLETRANSLATE function that allows you to translate text directly in cells:



The syntax of the function is as follows:
GOOGLETRANSLATE (text, [source_language], [target_language])

text is the text to be translated. You can quote the text and write directly to the formula, but it’s more convenient to refer to the cell in which the text is written.
[source_language] - language from which we translate;
[target_language] is the language into which we are translating.


The second and third arguments are given by a two-digit code: es, fr, en, ru. They can also be specified in the function itself, but it can be taken from a cell, and the language of the source text can be automatically determined at all.



But what if we want to translate into different languages? And at the same time we do not want to specify the source language manually each time?

The DETECTLANGUAGE function is useful here. She has only one argument - the text whose language must be defined:



As with any other function, the beauty here is in automation. You can quickly change the text or language; quickly translate one phrase into 10 languages ​​and so on. Of course, we understand that this is the text of an online translator - the quality will be appropriate.

Evgeny Namokonov and Renat Shagabutdinov, and we also conduct a channel in a telegram, where we parse different cases with Google Sheets, if you are interested - drop by a visit, the link can be found in my profile.

Also popular now: