How to sort in Google Sheets. Contacts apps for Android. Simpler Contacts Dialing

(file-> create a copy to copy the file to yourself Google Drive and be able to edit).

If the formula result occupies more than one cell

First about important feature displaying formula results in Google Sheets. If your formula returns more than one cell, then the entire array will be displayed at once and will occupy as many cells and columns as it requires (in Excel, this would require entering an array formula in all of these cells). Let's see how this works in the following example.

SORT

It will help you sort a range of data by one or more columns and immediately display the result.

Function syntax:

=SORT(data to be sorted; column_to_sort; ascending; [column_to_sort_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, registration settings can be changed in the file menu > table settings)

How to add table headers to SORT?

Using curly braces (), we create an array of two elements, the table header A1:B1 and the SORT function, separating the elements from each other using a semicolon.

How to combine several data ranges and sort (and more)?

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

Those who read the previous example have already guessed what to do: open the curly brace and collect the arrays to be combined, separating them from each other with a semicolon and close the curly brace.

You can combine arrays and not use them in a formula, but simply display them on a sheet, say, by collecting data from several sheets of your workbook. For vertical merging, you only need to maintain the same number of columns in all fragments (we have two columns everywhere).

And in the screenshot below there is an example of a horizontal join; instead of a semicolon, it uses backslash and the number of rows in the fragments must match, otherwise the formula will return an error instead of the combined range.

(semicolons and backslashes are array element separators in Russian regional settings, if the examples do not work for you, then through the table settings file, make sure that these are the ones you have)

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.

A join can be used in any function, the main thing is to maintain the same number of columns for a vertical join or rows for a horizontal join.

FILTER

With FILTER we can filter data by one or more conditions and display the result in a worksheet or use the result in another function as a data range.

Function syntax:

FILTER(range; condition_1; [condition_2; ...])

One condition

For example, we have a table with sales of our employees, let’s display data for one employee from it.

Let's enter the following formula in cell E3:

=FILTER(A3:C7;B3:B7=“Natalia Chistyakova”)

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

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

In addition to the equal sign (=), you can also use >, >=,<>(not equal),<, <=. Для текстовых условий подходят только = и <>, and for numbers or dates you can use all these signs.

Two conditions and working with a date

Let's complicate the formula and add one more condition to it, based on the date of sales, leaving all sales starting from 02/01/17

This is what the formula will look like if you enter the condition arguments directly into it, pay attention to the conversion of the date text entry using DATEVALUE:

=FILTER(A3:C7;B3:B7=“Natalia Chistyakova”,A3:A7>=DATEVALUE(“01.02.17”))

Or like this, if you refer to cells with arguments:

=FILTER(A3:C7;B3:B7=I6;A3:A7>=J6)

Interactive graph using FILTER and SPARKLINE

Do you know how else you can use the FILTER function? Instead of outputting the function result to a worksheet, we can use it as data for another function, such as a sparkline. Sparkline is a function that builds a graph in a cell based on our data. Sparkline has many settings, such as the type of graph, the color of the elements, but now we will not dwell on them and will 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 make the list like this:

select cell E2;
menu Data > Data Check;
rules: Value from the range and in the range select the 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;

The cell with the drop-down list will become a condition for the FILTER formula, let's write it.

And we will insert this formula into the SPARKLINE function, which, based on the received data, will draw a graph in the cell.

=sparkline(FILTER(C3:C7,B3:B7=E2))

This is what it looks like in dynamics:

But how elegant SPARKLINE can look with additional settings, V real work, the chart displays the results of activity for one day, green bars are positive values, pink bars are negative.

IMPORTRANGE

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

In what cases can it be useful?

You need up-to-date data from your colleagues' file.
You want to process data from a file to which you have "View Only" access.
You want to collect tables from several documents in one place so you can process or view them.

This formula allows you to get a copy of a range from another Google Sheets. Formatting is not transferred in this case - only data (we will tell you what to do with formatting below).

The formula syntax is as follows:

IMPORTRANGE(spreadsheet key; range string)
IMPORTRANGE(key; range)

spreadsheet_key (key) - sequence of characters of the “key=” attribute in the link to the table (after “spreadsheets/…/”).

Example of a formula with a key:

=IMPORTRANGE("abcd123abcd123"; "sheet1!A1:C10")

Instead of table key you can use full link to document:

Your file will display the range A1:CM500 from Sheet1 from the file located at the corresponding link.

If in source file the number of columns or rows may change, enter an 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 are loading an open range (for example, A1:D), then you will not be able to manually insert any data into columns A:D in the file where the IMPORTRANGE formula is located (that is, the final location where the data is loaded). They are, as it were, “reserved” for the entire open range - after all, its dimension is unknown in advance.

Import formatting from a source table

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

After pressing the button Copy to… select the workbook into which you will import data. Usually required table available on the tab Recent(if you've actually worked with her recently).

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

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

IMPORTRANGE as an argument to another function

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

Let's consider a simple example - the average sales value from a range located in another document.

This is the original document. Let the data be added and we need the average for sales in 2016 (that is, from cell D2 all the way down).

First we import this range:

And then we use this as an argument to the AVERAGE function:

We get the result, which will be updated when new rows are added in the source file in column D.

IMAGE: adding images to cells

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

The function has the following syntax:

IMAGE(URL, , , )

The URL is the only required argument. This is a link to the image. The link can be specified directly in the formula, using quotes:

=IMAGE(“http://shagabutdinov.ru/wp-content/uploads/2015/12/Run-or-Die.jpg”)

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

The mode argument can take four values (if omitted, the default will be the first):

the image is stretched to the size of the cell while maintaining the aspect ratio;
the image is stretched without maintaining the aspect ratio, completely filling
the image is inserted at its original size;
you specify the image dimensions in the third and fourth arguments of the and . , respectively, are needed only when the argument mode = 4. They are specified in pixels.

Let's see how images with four different meanings mode argument:

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

GOOGLETRANSLATE and DETECTLANGUAGE: translating text in cells

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

The function syntax is as follows:

GOOGLETRANSLATE (text,, )

text is the text that needs to be translated. You can put the text in quotes and write it directly into the formula, but it is more convenient to refer to the cell in which the text is written.
– the language from which we translate;
– the language into which we translate.

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

But what if we want to translate into different languages? And at the same time, we don’t want to specify the source language manually each time?

This is where the DETECTLANGUAGE function comes in handy. It has only one argument – text, the language of which needs to be determined:

As with any feature, the beauty here is in the 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 a text from an online translator - the quality will be appropriate.

Evgeniy Namokonov and Renat Shagabutdinov, and we also run a telegram channel where we analyze different cases with Google Sheets, if you are interested, come visit, the link can be found in my profile.

In general, working with contacts on Android is both easy and difficult at the same time. The thing is that most people use applications installed by the manufacturer, and these can be either standard ones from the Android package or programs included in branded shells. So, first, let's look at the general situation using one example.

Android contacts are in application of the same name. As a rule, developers of shells for Google OS bring it to the first home screen, so finding it is not difficult.

The interface of the Contacts application is simple and clear. At the top there is a menu of Phone Book sources, icons for searching and creating a new contact, as well as a menu for the application itself.

By swiping left and right you can quickly go to contact groups, screen speed dial and even the call log.

The phone book sources menu allows you to select contacts from which to show in the application. There are options: SIM, Skype and other instant messengers, account Google posts and the phone book of the device itself. The user can check or uncheck the boxes next to each source. Helpful advice: if you're annoyed that Android is pulling up email addresses, nicknames from Skype and turns phone book into a mess, then just turn off the relevant sources.

The contact search in the application has automatic selection. To find the right person, it is not necessary to type his full name. It is enough to indicate the first letters and the application will select all matches.

When creating a contact, the user selects its location, then indicates a number (you can choose from 6 options) and email. You can add a contact to a group. In addition, for each contact you can specify additional fields: from birthday or other significant events to a custom note.

The application menu includes eight items. We will dwell on some of them later. The settings mainly concern the way contacts are displayed in the application. You can also set up synchronization with accounts, view the capacity of the SIM card, and select the tabs that will be shown when swiping left and right.

How to Transfer Contacts from Android to Android

Unlike iOS, Android offers big choice native options for transferring contacts from one device to another.

Every Android smartphone is linked to account Google. To transfer contacts from using Google, just make sure that synchronization of contacts with your old smartphone is enabled Google profile and Gmail. To do this, go to Settings - Account and Sync - Google - Gmail. Next, click on the account name and select Contacts. Depending on the settings made earlier, they will sync automatically or when clicked. Then on the new device it is enough to do the same thing so that the contacts appear on it. It is important that gadgets are connected to the Internet.

You can also transfer contacts from Android to Android using SIM card or VCard file. To do this, in the Contacts application, go to Menu – Contact Management – Import/Export Contacts. Here you can export contacts to a SIM card, memory card or phone memory.

The SIM is then inserted into new phone, you can also move the memory card there. On the new device, the same actions are performed, only instead of Export you need to select Import from the appropriate location.

If the user has exported contacts to the phone's memory, then he will then need to transfer the VCard file from there. This can be done by connecting phones to a computer - most Android phones are defined as removable disks, either sent by e-mail to yourself (but here it is more logical to use synchronization via Gmail, described above), or transmitted via Bluetooth. Then on the new phone you need to repeat the steps already described for importing contacts.

You can transfer contacts from phone to phone using using Bluetooth. True, this method is only suitable for small quantity records - and once you can send no more than 10 pieces. To do this, go to the Contacts application menu and select the Send contacts option. Then check the box necessary records and then select the sending method – Bluetooth. You must first establish a Bluetooth connection between the phones.

Also, the Send contacts item allows you to send them by e-mail, With via SMS or MMS, but these methods are not relevant when transferring contacts from phone to phone.

How to Transfer Contacts from iPhone to Android

There are two ways to transfer contacts from iPhone to Android: synchronizing with Gmail and using a VCard file. Both will require some effort.

Default to iPhone contacts synced with iCloud. You need to switch synchronization to Gmail without losing your phone book. To do this, go to Settings on your iPhone - Mail, addresses, calendars - Accounts/iCloud and turn off Contacts synchronization. A pop-up window will appear. In it you need to select the “Keep on phone” option. This is how we will save contacts on iPhone. If you choose another option, your contacts will be deleted from your iPhone.

Now you need to set Gmail as the default account for Contacts on your iPhone. The setting is done in the Settings section - Mail, addresses, calendars. In the Contacts sub-item, one of the last will be the Default Account. You need to select an item and check Gmail. After some time, your contacts will be synced with Gmail. If it already has contacts, they can be duplicated.

Next, you need to activate your Gmail account on Android. After some time, the contacts will appear on the phone. If this does not happen, then you need to look in the sources menu in the Contacts application to see which sources are activated. Possibly with an account Gmail entries the checkbox is unchecked. You may also need to force synchronization. This is done in Settings - Account and Sync - Google - Gmail.

To transfer contacts from iPhone to Android, you can use a VCard file. You can only create a file in iCloud. Therefore, first of all, you need to make sure that your contacts are synchronized with the cloud.

To do vCard file, you need to select all contacts using ctrl-AWindows or Command-A on Mac, in the settings menu of the iCLoud web application there is an Export vCard option. Save the file to your hard drive or flash drive. Any in a convenient way write it to the memory of your Android device.

We have already described further actions. In the Contacts application, you need to go to Menu - Contact Management - Import and Export Contacts, select Import Contacts, and then the location of the VCard file - in the phone memory or on the SD card. The Contacts application will copy all data from the file into the device memory.

Instead of recording the VCard file on your Android phone, you can import contacts from it into Gmail and then synchronize it with the gadget. To do this, in Gmail you need to select Contacts, and then select Import. In the pop-up window, select the last item. All that remains is to synchronize the device with Gmail.

How to Transfer Contacts from Windows Phone to Android

There are several ways to transfer contacts from Windows Phone on Android. You can use applications that allow you to make a VCard file. Since contacts Windows users Phone is synchronized with the account Microsoft entry, then they will be located in Outoook.com by default, you can add an account Outlook entry in Android. Finally, you can transfer contacts from Outlook using a csv file.

To transfer contacts from Windows Phone to Android via Outlook.com sync, you need to add an account to your phone. To do this, go to Settings – Accounts and synchronization and press +. From the proposed selection, click on Outlook, then enter your account information and select contact synchronization. U this method there are some disadvantages. In some cases, Outlook prevents you from editing contacts on third party devices, so it’s better to make contacts “native” to Android by transferring them through a file

On Outlook.com, you need to select People from the menu. Then click on the Manage button, and in the new menu - Export for Outlook.com and other services. The contacts will be saved to a CSV file. It needs to be saved to your computer.

Next, in Gmail, as already described above, you need to select Contacts. There is an Import item in the menu. If you click it, a pop-up menu will appear in which you need to select Import from a CSV or VCard file. All you have to do is select a file on your hard drive, the contacts will be uploaded to Gmail and then synchronized with Android.

How to transfer contacts from Android to computer

Transferring contacts from Android to computer is very easy. It is enough to create a VCard file. Programs can work with it Microsoft Outlook or Windows Contacts, as well as other applications.

To export contacts to a VCard file on your phone, you need to go to the Contacts application: Menu - Manage contacts - Import and Export contacts and select the option: Export to SD card or Export to phone memory.

All contacts will be saved to a VCard file. Then you can save it to the computer’s memory and open it in the appropriate program for working with contacts. The file can be stored as backup copy on the computer. However, it will not sync with Android. Therefore, it is advisable to synchronize Android contacts with Gmail.

By the way, you can also export contacts from Gmail. To do this, select the export contacts item in the menu. They can also be saved in a VCard file.

When opening a VCard in Windows programs There may be problems with displaying the Cyrillic alphabet. VCard is saved with UTF-8 encoding by default, and Windows uses Windows-1251, however, latest versions Microsoft OS has expanded support for Unicode. However, if there is still a problem, then you need to open the file in any text editor, for example, Notepad 2, which supports changing the encoding. Change it and save. In fact, a VCard is a regular text file.

Sync Android contacts with Google

Android contacts sync with Gmail and Google by default if you have an account added to your phone. And without an account it is impossible to fully use Android.

To add an account, you need to go to Settings - Accounts and Synchronization. Here, click on Plus and select from the Google list. Next, enter your account information or create one.

Now in the list of added accounts will appear Google item. If you go into it, the name will be there mailbox"[email protected]". By selecting an account, you can manually start synchronizing various data and Google services. Contacts are at the very bottom of the list.

In the menu item Settings - Accounts and Synchronization at the very top there is an Auto-sync switch. If you activate it, all accounts will be synchronized automatically. If it is turned off, then synchronization must be started manually, as described in the paragraph above.

Importing contacts to Android

Importing contacts to Android is quite simple.

In the Contacts application, you need to go to Menu - Contact Management - Import and Export Contacts.

A menu will open with three options for importing contacts. Import from SIM. You need to select it to copy contacts from the SIM card to the phone's memory. Import sSD-fold and Import from phone memory must be selected to overwrite contacts from contact files, for example, VCard. Android does not need to specify the location of the contacts file. The system itself will find all supported files and offer to select the one from which data will be imported.

If it is not physically possible to connect your phone to a computer in order to write a VCard file to the phone, you can send it by mail, via Bluetooth, upload it to the cloud and then download it to your device.

You can also import contacts to Android through the application Google Contacts. You can access it from Gmail. In the Advanced menu item, select Import contacts. Next, to transfer contacts to Android, you just need to synchronize your contacts with Gmail.

In the new Gmail versions The Import item is in the More section, not the Advanced section. It also supports importing contacts from Yahoo mail, Outlook, AOL and others. Therefore, if your contacts are synced with other email services, you can retrieve them from there directly. It is enough to enter your mail access data. However, Google warns that for a number of services, the contact import function is implemented by third-party developers, so everyone must decide for themselves whether to trust their credentials to third-party service providers.

Where are contacts stored in Android?

Contacts in Android are stored in the file contacts.db or contacts2.db. It represents the base SQLite data. It is easy to find software on the Internet that can be used to read such files. However, the files themselves are not so easy to find.

To access them, you must have root rights. If they are not there, the user simply will not see this file, he will not see the folder in which the database is located. Only if you have root rights can you go to Android/data/data/com.android.providers.contacts/databases/, where the contact files are located.

Export contacts from Android

You can export contacts from Android using the built-in functionality of the Contacts application.

In the application menu, select Manage contacts and then Import and export contacts. There are three options for export: save contacts to SIM, save to memory card or phone memory. When saved to a memory card or phone memory, contacts will be exported to a VCard file, which can then be used to import contacts on both Android and iOS. postal services, cloud, desktop applications.

How to Recover Android Contacts

The solution to this problem depends on whether contacts have been synchronized with Gmail and whether a VCard backup has been made.

If your contacts were synced with Gmail, then you can simply sync them with your phone. However, it may happen that contacts were accidentally deleted from your Android smartphone. It synced with Gmail and the contacts there also disappeared. It's no problem.

Google stores contacts for up to 30 days. In Gmail, in the Advanced/More section, select Recover Contacts. A menu will appear in which you select the date for restoring contacts. Once they are restored to Gmail, they can be synced with your phone.

If a backup was made to a VCard, then to restore it, simply load the file into the device memory or onto a memory card and import contacts from it, as described in the previous sections.

How to delete contacts on Android

There are several ways to delete contacts on Android, depending on how many contacts you need to delete and how securely you want to delete them.

To delete one contact, you can open the Contacts application, select required Contact and press it while holding your finger at one point. A context menu will appear containing the Delete item.

You can also go into contact and click on the three dots on the right top corner. This is the contact menu. There is also a delete option here.

The Delete contacts option is also available in the menu of the Contacts application itself. When you click on Delete contacts, the user will be presented with a list of contacts in which they can mark contacts for deletion or select all. Next, click on the Delete button.

You can simply reset all phone data. This is done in Settings – Backup and reset.

Backup Contacts on Android

Android offers several ways to backup contacts

The first method is simple and reliable, but keeping your contact backup up to date will be difficult. It's about about exporting contacts to a VCard file. How to do this, see above in the section Exporting Android contacts.

The second method is to synchronize contacts with Gmail. It is often enabled by default. How to manage synchronization is described above.

Selecting an account for Android backup

The third way is to enable backup for the entire phone on Google servers. This is done through Settings – Backup and Reset. When you activate backup, Android will make a copy of the phone on Google servers, not only contacts will be saved there, but also all personal data, application data, etc.

Contacts apps on Android

The Contacts application for Android has good functionality and is quite convenient, so there is not much point in third-party solutions, but we will still look at some applications

VCF Contacts

Developer: AndloRD.
Grade: 4,1.
Number of installations: 1 million.

This application is an alternative for standard Contacts from Android. It is not distinguished by advanced functionality, but is rather interesting because of its different approach to storing the phone book. If Contacts from Google stores all information in a database, then VCF Contacts– in the vCard (vcf) file. This is the file that can be obtained as a result of export.

When installing the application, it takes an existing vCard file as a basis or creates one based on the phone number books android. As a result, the user always has an up-to-date vCard file on his phone. This can literally save you in the event of a sudden system failure.

Contacts can be sorted, sent by mail, via instant messengers. Unfortunately, the functions to add to Android and to another vCard file are only available in paid version.

Contacts+

Developer: Contacts Plus team
Grade: 4,1.
Number of installations: 50 million.

Makes contacts more fun. All contacts are presented with avatars. This view makes it easier to call the right person - selecting by person faster. There are advanced search functions: you can search not only by name or phone number, but also by mail, SMS, etc.

Themes and various options phone book design. Contacts+ also has call and SMS log.

Contacts are stored in encrypted form.

The application has a separate backup function. True, in free version You can only save up to 2000 contacts, calls or SMS. Copying is configured separately for each category of information.

Save contacts to TXT

Developer:Viked.
Grade: 4,3.
Number of installations: 15 thousand.

IN Android contacts can be exported to vCard format. This application allows you to save them in txt.You can save them in text file all contacts, individual entries or several.

You can also configure which fields will be saved in the file.

The file can be used in the future at the user's discretion. You can also send it by email.

Simpler Contacts Dialing

Developer: SimplerApps
Grade: 4,3.
Number of installations: 5 million.

First of all, it is an alternative to the standard Android phone implementation. Not only the phone book is supported, but also a dialer, Caller ID, and work with SMS. However, the application has several interesting features for working with contacts. The most important thing is backup. You can do it like local copy contacts and save it in the application cloud. You can set up automatic backup generation. The application constantly searches for duplicates in contacts automatically.

Another one interesting opportunity- search for photos on Facebook. The application prompts the user to compare his friends on the social network with his contact list and, if there are matches, use Facebook profile photos for the Caller ID.

Also can be used for application interface various topics.

SA Contacts Lite

Developer: samapp.
Grade: 4,0.
Number of installations: 1 million.

Applications for exporting and importing contacts to Excel files. Excel is best source to import into Outlook.

The application allows you to customize which fields will be exported to Excel. You can transfer all contact fields, including the photo. The only exception is personal melody assigned to the contact.

The application also automatically converts encodings according to Windows requirements And Microsoft applications so that names in contacts are displayed correctly.

The exported file can be stored on a memory card, sent by mail, or uploaded to cloud services.

The limitation of the free version is that you can only export the first 300 contacts. You will have to pay for a larger phone book.

Conclusion

Working with contacts in Android is well organized. There is practically no need to contact third party applications if you read our guide. Using Android you can do everything possible actions with contacts.

However, if you want to decorate and diversify your dialer or phone book, then Play Market enough applications. True, you need to be careful when choosing them. We are still talking about personal data.

We released new book"Content marketing in in social networks: How to get into your subscribers’ heads and make them fall in love with your brand.”

I decided to write an article that will help in mastering the simplest functions of Google Sheets. I used to be filled with hatred for everything related to spreadsheets, and now working in them seems very convenient and functional. Let me make a reservation right away: the example contains a small list of queries and absolutely random values.To begin with, since tables often contain a lot of data in the form of columns and rows, so as not to get confused, I’ll tell you how to assign a column or row in Google Sheets, which will be navigation.

Select the line:

Click "View"
We point to “pin”
Click “1 row” (or column, depending on what you need)

As a result, we get a line accompanying us, which helps us not to get confused in a large table.

Sorting in Google Sheets

You won’t surprise anyone with regular sorting, so I’ll tell you about tricky sorting. Let's say we have a column with key queries. But one of the services has lost its relevance and we need to get rid of the corresponding requests. But what if the requests are different, there are many of them and they are mixed together?

In Google Sheets, and in Excel too, you can change the color of a cell containing a specific stop word. Next, we’ll sort the table by color and all the cells to be deleted will be lined up in a row in front of you. Let's say I need to remove all queries with the word "court" from the queries column.

Instructions:

Select the entire column by clicking on the cell with the letter, in our case “A”.
Click right click mouse over the selected column and select “Conditional formatting.

A small menu appears on the right where we can create a rule.

In the menu:

Click on the field under the words “Formatting cells” and look for the line “Text contains” there.
Enter the safe word into the hole that appears. Preferably without an ending, since anything can happen in Russian.
Using the usual menu, we select the color of the cell and whatever we want.
Click “Finish”.

Now let's install the addition for our table. To do this in top menu We find the button of the same name “Add-ons”↣ “Install add-on.” We drive into search bar“Sort by color” and install the extension, as in the figure:

I don’t insist on this particular addition, it’s just tested and works. Now our collection has an extension that sorts values by cell color.

Click “Add-ons” ↣ “Sort by color” and select any from the list. Voila. Can be deleted.

Find and remove duplicates in Google Sheets

Often, duplicate values can be quite annoying. There are two ways to find duplicates. The first one is very simple.

In an empty cell, enter the function =(UNIQUE (A2:A1000)and get a list of cells with unique content:

This method extremely sensitive to case and spaces!

Method two involves installing another add-on called.

*Instead of configure you will have a blue button +Free

The application is quite powerful. It can search for duplicates in a certain range or compare columns and immediately perform any actions on them:

Copy/move duplicates to a separate column
Copy/transfer duplicates to a separate table
Ruthlessly remove duplicates
Highlight them with color

We'll focus on highlighting with color. Program for English language, but with the help Google translator and a couple of tests you'll definitely figure it out.

So, select the column, go to Add-ons ↣ Remove Duplicates ↣ Find duplicates or uniques.

We see:

Here we can specify the range of interest manually, or by first selecting the desired area, the range will be set automatically. By checking the box next to Create a backup, we will create a copy of the table being modified. Click “Next”.

A choice appears before us. We can find either duplicates or, conversely, unique cells. We select duplicates and we are faced with another choice. If we choose with1st occurrences, then we will find duplicates, INCLUDING their first mention, roughly speaking, the original. We will choose to simply search for duplicates, excluding the first mention, since we need to leave one cell. Click next.

If we select a range “with interest”, while also affecting empty columns, be sure to check the Skip empty cells checkbox. At all, this step created to eliminate header cells. In our example we have a title, so we leave the checkboxes. Next!

Final slide. We won’t dwell too much here, but will immediately select Fill with color. Click Finish and see the number of duplicates found in the selected range.

The padding is space sensitive!

Now we can sort our duplicates by color using the first method and evaluate the need for removal.

Conditional Formatting in Google Sheets

In our example, there are several columns that can be used to somehow evaluate the effectiveness of the query. For example, we don't want to use queries whose frequency is below 100. We can highlight all inappropriate cells with color.

Select the column with values
Right click ↣ conditional formatting
Cell format: "Less than"
Insert the required amount into the hole
Select color/weight/background and click “Finish”

Now with the help already native way sorting by color, we analyze requests with insufficient frequency and, if necessary, delete them.

Using the same principle, you can do a lot of things with the resulting lists. For example, by formatting cells with the “text contains” rule, you can highlight and sort all geo-independent queries and remove them. And so on. The rest is just your imagination, and if it gets too wild, select “Your formula” in “Cell Formatting” and get creative!

Charts in Google Sheets

For dessert. To present everything beautifully, use the intuitive function of Google Sheets - inserting a chart. No explanation needed here, just try it!

Conclusion

In general, great love to Google products I didn't and don't have one. But I am very grateful to them for the docs.google.com series. Once I sat down and rummaged through these products, I came to the conclusion that I could no longer live without tables and documents, I just needed to figure it out. A significant plus is an add-on for Google Sheets, which is updated and significantly increases the capabilities of the service. It’s a pity that there are few clearly useful articles on this topic. I will try to write a second part in the foreseeable future, where more complex circuits will be analyzed. Ideally, you will be able to launch a line of useful articles on the topic of how to work in Google Sheets, add-ons, formulas and ready-made solutions for everyday tasks.

Algorithms

Tutorial

This article will discuss several very useful Google features Tables that are not in Excel (SORT, array join, FILTER, IMPORTRANGE, IMAGE, GOOGLETRANSLATE, DETECTLANGUAGE)

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

Table of contents:

If the formula result occupies more than one cell

First, about an important feature of displaying formula results in Google Sheets. If your formula returns more than one cell, then the entire array will be displayed at once and will occupy as many cells and columns as it requires (in Excel, this would require entering an array formula in all of these cells). Let's see how this works in the following example.

SORT

It will help you sort a range of data by one or more columns and immediately display the result.

Function syntax:

=SORT(data to be sorted; column_to_sort; ascending; [column_to_sort_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, registration settings can be changed in the menu file → table settings)

How to add table headers to SORT?

Using curly braces (), we create an array of two elements, the table header A1:B1 and the SORT function, separating the elements from each other using a semicolon.

How to combine several data ranges and sort (and more)?

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

And in the screenshot below is an example of a horizontal union, it uses a backslash instead of a semicolon and the number of rows in the fragments must match, otherwise the formula will return an error instead of the combined range.

(semicolons and backslashes are array element separators in Russian regional settings, if the examples do not work for you, then through the table settings file, make sure that these are the ones you have)

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.

A join can be used in any function, the main thing is to maintain the same number of columns for a vertical join or rows for a horizontal join.

All the examples discussed can be taken a closer look at
Google Doc.

FILTER

With FILTER we can filter data by one or more conditions and display the result in a worksheet or use the result in another function as a data range.

Function syntax:

FILTER(range; condition_1; [condition_2; ...])

One condition

For example, we have a table with sales of our employees, let’s display data for one employee from it.

Let's enter the following formula in cell E3:

=FILTER(A3:C7;B3:B7=“Natalia Chistyakova”)

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

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

In addition to the equal sign (=), you can also use >, >=,<>(not equal),<, <=. Для текстовых условий подходят только = и <>, and for numbers or dates you can use all these signs.

Two conditions and working with a date

Let's complicate the formula and add one more condition to it, based on the date of sales, leaving all sales starting from 02/01/17

This is what the formula will look like if you enter the condition arguments directly into it, pay attention to the conversion of the date text entry using DATEVALUE:

=FILTER(A3:C7;B3:B7=“Natalia Chistyakova”,A3:A7>=DATEVALUE(“01.02.17”))

Or like this, if you refer to cells with arguments:

=FILTER(A3:C7;B3:B7=I6;A3:A7>=J6)

Interactive graph using FILTER and SPARKLINE

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

select cell E2;
menu Data → Data check;
rules: Value from the range and in the range select the 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;

The cell with the drop-down list will become a condition for the FILTER formula, let's write it.

And we will insert this formula into the SPARKLINE function, which, based on the received data, will draw a graph in the cell.

=sparkline(FILTER(C3:C7,B3:B7=E2))

This is what it looks like in dynamics:

But how elegant SPARKLINE can look with additional settings, in real work, the diagram displays the results of activity for one day, green columns are positive values, pink columns are negative.

IMPORTRANGE

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

In what cases can it be useful?

You need up-to-date data from your colleagues' file.
You want to process data from a file to which you have "View Only" access.
You want to collect tables from several documents in one place so you can process or view them.

This formula allows you to get a copy of a range from another Google Sheets. Formatting is not transferred in this case - only data (we will tell you what to do with formatting below).

The formula syntax is as follows:

IMPORTRANGE(spreadsheet key; range string)
IMPORTRANGE(key; range)

spreadsheet_key (key) - sequence of characters of the “key=” attribute in the link to the table (after “spreadsheets/…/”).

Example of a formula with a key:

=IMPORTRANGE("abcd123abcd123"; "sheet1!A1:C10")

Instead of a table key, you can use a full document link:

Your file will display the range A1:CM500 from Sheet1 from the file located at the corresponding link.

If the number of columns or rows in the source file can change, enter an 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)

Import formatting from a source table

After pressing the button Copy to… select the workbook into which you will import data. Usually the required table is on the tab Recent(if you've actually worked with her recently).

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

IMPORTRANGE as an argument to another function

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

Let's consider a simple example - the average sales value from a range located in another document.

This is the original document. Let the data be added and we need the average for sales in 2016 (that is, from cell D2 all the way down).

First we import this range:

And then we use this as an argument to the AVERAGE function:

We get the result, which will be updated when new rows are added in the source file in column D.

IMAGE: adding images to cells

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

The function has the following syntax:

IMAGE(URL, , , )

The URL is the only required argument. This is a link to the image. The link can be specified directly in the formula, using quotes:

=IMAGE(“http://shagabutdinov.ru/wp-content/uploads/2015/12/Run-or-Die.jpg”)

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

The mode argument can take four values (if omitted, the default will be the first):

the image is stretched to the size of the cell while maintaining the aspect ratio;
the image is stretched without maintaining the aspect ratio, completely filling
the image is inserted at its original size;
you specify the image dimensions in the third and fourth arguments of the and . , respectively, are needed only when the argument mode = 4. They are specified in pixels.

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

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

GOOGLETRANSLATE and DETECTLANGUAGE: translating text in cells

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

The function syntax is as follows:

GOOGLETRANSLATE (text,, )

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

But what if we want to translate into different languages? And at the same time, we don’t want to specify the source language manually each time?
formulas

google sheets

Add tags

Hello, friends! Most often Google Sheets are filled with big amount data and, as a result, find required lines or columns, for example, with similar values, is very difficult. But the developers foresaw this and, just like in Excel, added everything necessary functions that will help solve this problem.

In this article we will look at this. Let's figure out how to sort rows in ascending or descending order, and if you need to leave only certain data visible, then it is better to use a filter. We will also talk about it and tell you how a regular filter differs from the filtering mode in Google Sheets.

How is sorting done?

Sorting should be used when you need to organize data in a table. For example, arrange numbers from highest to lowest value, or, depending on the first letter, place words in cells alphabetically.

Select the desired range, open the “Data” tab and select “Sort Range”.

If you have selected data from the table along with the header, then check the “Data with header line” checkbox. Next, select which column you want to sort by. For example, I’ll sort the table by Cost - this is column B, which means I select it. Then mark with a marker how everything needs to be done in ascending or descending order.

There is an option here to “Add another column”. This is necessary if you want to sort first by price, and then, for example, by availability in stock. Click on the “Add...” button, but select sorting by column C.

When you're done, click "Sort."

After this you will get the desired result. In the example, I sorted the vegetables by price.

To return everything to the way it was, click the “Undo Actions” arrow at the top or the “Ctrl+Z” key combination.

In order for the table to change correctly, I need to select all columns, and, for example, not only B, if I want to sort by Cost. Because if you select only B, then after sorting the data in columns A and C will not correspond to it, since the numbers will appear in ascending order, and the data in cells A and C will remain in place.

How to make a filter

Data filtering can be used when there is a lot of data entered, but leave it at this moment you only need certain ones. For example, to display those products whose cost does not exceed 130.

Select the blocks in the desired column along with the table header, open the “Data” tab and click “Create filter”.

A button with stripes will appear in the header cell - it is needed to configure the filter. Click on it to open a window for setting parameters. Let’s first look at the “Filter by condition” option.

If you have created big table and it would be more convenient for her hat to always remain in sight, you can.

In the first field, select the appropriate condition: display only empty cells whose text begins or ends with..., a number less than or equal to the specified one, etc.

In the second field you need to enter data for the selected condition. For example, let’s see how many vegetables we have whose price is greater than or equal to 150. Then click “OK” and see the result.

You can also sort the rows left after applying the filter. In the window for setting filter parameters at the top there are two necessary items.

If you select the “Filter by value” option, then uncheck the boxes next to the items that you want to hide and click “OK”.

The “Select All” button will allow you to check off everything, and the “Clear” button, on the contrary, will remove the checkmarks in front of all items.

Filter by color

If your table has cells with data shaded in specific color, and you are interested in how to make a filter by color in Google Sheets, then you first need to determine its code for each color, and you can already apply a filter to it.

There is no built-in formula that would suit us, so we need to add custom function. Open the “Tools” tab and select “Script Editor” from the list.

The following window will open in a new browser tab to create a new project. Delete all the written code - select it and press “Delete”.

Come up with a name for the project and click “OK”. After this, you can close the tab.

It is most convenient to display the color code in the column next to the shaded cells. Therefore, we place the cursor in D2 and write the following formula:

GetCellBackgroundCode(CELL(“address”,C2),$E$1)

Here C2 is the address of the cell filled with color, and E1 is needed to update the data (you can specify any other address, but do not remove the dollar sign), write any numbers.

Select D2 and drag the formula down by pulling the bottom right corner. “Loading” means that the calculation is in progress.

When the counting is finished, the cells will display a color code that matches the background in the adjacent block.

Now you can apply a filter to them. Select the entire range.

Open the Data tab. If you already have a filter in the table, then you need to disable it so that the “Create...” button appears, which we need.

In order to remove the filter in Google Sheets, you need to click the “Disable...” button on the mentioned tab.

When a button appears in the header, click on it. In the parameters window, select “By value” and leave checkboxes only opposite those codes that correspond to the desired colors. To apply, click “OK”.

Now we have only those rows left in the table whose cells are filled with green.

To keep the code column out of the way, you can group it. And when you need it, you just need to expand it by clicking on the “+” sign.

Now I’ll tell you why cell E1 is needed. It will be needed for recounting. For example, you have filled in the colors, you have written the formula and determined the codes. If you need to replace some colors, the code will not be calculated automatically. To do this, remove the number from E1 and press “Enter”. Now the formula will be recalculated. When you change the fill color again, write something in E1, for example. 123, and the codes will change again. And you need to do this constantly, just as you change the background of the blocks.

Creating a filter mode

We looked at how to create a filter, but this may not be suitable for everyone. The filter is good to use if you need:

So that the data remains filtered.
Other users who have access to the table also saw the filtering result.

If you just need to use it for work and after you finish everything needs to be turned off, then it is better to use the filtering mode. Advantages over regular filter he has the following:

You can create several modes.
Give each of them a name.
Opportunity different users view the table in different modes.
You can create a link to a filtered table and send it to one user, and then select a different filtering mode and send the link to another person.
Ability to create duplicate modes and slightly change their parameters.
If you do not have rights to edit the table, but need to filter the data, a temporary mode will be created.

So, filter the data in the table as needed and open “Data” – “Filters” – “Save as...”.

The margins at the left and top will become gray– this means we are in filtering mode. Let's give the table in this form a specific “Name”. The “Range” to which the filter is applied will also be indicated.

On the right there is a gear-shaped button - this is “Options”. “Create a copy” – duplication of the mode. It will be useful if you want to slightly change the current filter, but you also need to leave the old one. “Delete” – deleting the mode. To exit from this mode and return to normal table, just click on the cross on the right.

This way you can create as many filtering modes as you need, applying the filter to specific table data. To view any of the filtered values, open the “Data” tab and select the desired name from the saved list.

Sorting data in a table is not difficult. The filter will take some figuring out, especially if you need to filter by cell color or create several filtering modes. But spend 20 minutes on it and work with Google Sheet It will become much more convenient if only the necessary values remain in it.