We released new book"Social media content marketing: How to get into the head of subscribers and make them fall in love with your brand."

Subscribe

I decided to write an article that will help in mastering the simplest functions of Google Spreadsheets. I used to be filled with hatred for everything related to spreadsheets, and now work in them seems very convenient and functional. I’ll make a reservation right away, in the example there is a small list of requests and absolutely random values.To begin with, since tables often contain a lot of data in the form of columns and rows, in order not to get confused, I will tell you how to fix a column or line in Google tables, which will be navigational.

Highlight the line:

1. Click "View"
2. We direct "fix"
3. Click "1 row" (or column, depending on what you need)

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

Sorting in Google Sheets

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

In Google Sheets, and in Excel too, you can change the color of a cell containing a specific stop word. Next, sort the table by color and all the cells to be deleted will line up in front of you in a row. Suppose I want to remove all requests with the word "judgment" from the column with requests.

Instruction:

1. Select the entire column by clicking on the cell with the letter, in our case "A".
2. Right-click on the highlighted column and select Conditional Formatting.

A small menu appears on the right, in which we can create a rule.

In the menu:

1. Click on the field under the inscription "Cell Formatting" and look for the line "Text contains" there.
2. We enter the stop word in the hole that appears. Preferably without an ending, since everything happens in Russian.
3. Using the usual menu, select the color of the cell and whatever we wish.
4. We click "Finish".

Now let's install an add-on for our table. For this in top menu find the button of the same name "Add-ons"↣ "Install add-on". We drive in search string"Sort by color" and install the extension, as in the picture:

I do not insist on this supplement, it is simply 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!

The second method involves installing the next add-on called.

*Instead of customize you will have blue button+Free

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

• Copy/move duplicates to a separate column
• Copy/move duplicates to a separate table
• Mercilessly remove duplicates
• Highlight them with color

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

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

We see:

Here we can specify the range of interest manually or by pre-selecting desired area, the range will be set automatically. Check the box next to Create a backup we will create a copy of the modified table. Click "Next".

We have a choice before us. We can find either duplicates or, conversely, unique cells. We select duplicates and we face one more choice. If we choose with1st occurences, 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 the range "more than", while affecting the empty columns - be sure to check the Skip empty cells checkbox. Generally, this step designed to exclude capital cells. We have a heading in the example, so we leave the checkboxes. Next!

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

Padding is whitespace sensitive!

We can now sort our duplicates by color using the first method and assess whether we need to remove them.

Conditional Formatting in Google Sheets

In our example, there are several columns by which you can somehow evaluate the effectiveness of the query. For example, we do not want to use requests whose frequency is less than 100. We can highlight all unsuitable cells with a color.

1. Highlight a column with values
2. Right click ↣ conditional formatting
3. Cell Format: Less Than
4. Enter the required amount into the hole
5. Choose a color/weight/background and click "Finish"

Now, using the already native method of 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, using cell formatting with the "text contains" rule, you can highlight and sort all geo-independent queries and remove them. Well, and so on. Further, only fantasy, and if it plays out strongly, select the value “Your formula” in “Cell Formatting” and create!

Charts in Google Sheets

For sweet. To present everything beautifully, use the intuitive Google Sheets feature - insert a chart. Explanations are superfluous here, just try it!

Conclusion

In general, I didn’t have a big love for Google products and I don’t. But for the docs.google.com series, I am very grateful to them. Once I sit down and dig into these products, I come to the conclusion that I can no longer live without tables and docks, I just had to figure it out. A significant plus is the add-ons for Google Spreadsheets, which are replenished and significantly increase the capabilities of the service. It is a pity that there are few clearly useful articles on this topic. I will try in the foreseeable future to write the second part, where more complicated schemes will be disassembled. If ideally, it will be possible to launch a line of useful articles on the topic of how to work in Google spreadsheets, add-ons, formulas and turnkey solutions for everyday tasks.

Data is taking over the world. And often, we have so many of them that it is enough difficult task- understand what they mean and evaluate.

Spreadsheets are one of better ways analyze the data structure and start using them. But often the datasets are too big to start using easily. To do this, you need tools that will help you find the right data, and will reduce their volume.

In this tutorial, you will learn how to use the Google Spreadsheets filter to limit the data set that you consider. And then, you'll learn the secrets to using the Google Sheets sorting feature to help you organize your data the way you want.

Below, in a video tutorial, I'm going to walk you through the sorting and filtering features in Google Sheets. You will see options for filtering and sorting data in Sheets.

View the illustrated material, which explains the techniques for working with Google Sheets features: sorting and filtering.

Apply in practice

As you work through this lesson, make examples using the example data. I have prepared a set of simple data that you can use to practice sorting and filtering data in Google Sheets.

Upload them to Google Sheets and use them to work through the techniques I describe in this tutorial.

1. Sorting in Google Sheets

Sorting Data in Tables is all about dealing with the order in which the data will appear in your tables.

When I go about sorting, I take into account two factors:

1. Based on which column will the data be sorted? For example, you might want to sort sales based on total sales.
2. How will the data be sorted? For example, data can be sorted from large values to the smaller ones.

One of important points in sorting in is that you will sort the whole table. Typically, each line of data can be considered as a record, or data element. Each row of data contains one data element, and each column contains different attributes, or ways of describing the data.

For example, each row in your table could be a record of purchases that customers make in your online store. While each column (attribute) will contain key information on this purchase, such as the time of purchase, the address of the buyer, the volume of the purchase.

In this example, for this lesson, each row is a record of the employees being hired, and each column contains attributes that describe the candidate.

When you sort data, it is very important that the data stays grouped together. If you sort only the column that contains the number of sales, then you will end up with data confusion! A $100 sale may now match the wrong buyer.

That's why it's so important to sort data correctly. Let's look at a few examples below.

How to Sort Data in Google Sheets

One of the basic sorting methods in Google Sheets is to sort the data in alphabetical order from A to Z (or from A-Z). After that, the data is usually easier to read, and then it's easier to find rows that you might need.

Before I start doing any sorting, my first step is to isolate whole table. This minimizes the chance that I will accidentally sort the wrong column, or select only part of the data.

Once you have selected the data set, go to the menu Data > Sort Range as shown in the picture below.

After you have selected the data, go to Data > Sort Range to sort your data using google tools Tables.

While there are built-in options for sorting with A-Z or Z-A, it's much easier to use the built-in tool in Spreadsheets for sorting. In the screenshot below you can see a typical example of sorting. You can simply select the column you want to sort on.

Also, make sure you sort in the correct "direction". For text data, you can sort alphabetically (A-Z) or reverse alphabetically (Z-A).

Select the column you want to sort the data based on, as well as the sort order, alphabetically or reverse alphabetically.

After you do this, you will see that your data is sorted and safe and sound. You may notice that in the figure below, we have sorted the data based on the name of the company the candidate works for.

Now that we have sorted the Company column, you can see that the company names are listed in alphabetical order.

It's a good habit to review the data you've sorted. Worst case, you will only sort one column while the others remain unchanged. In the screenshot above, by the mixed ID numbers, which are no longer in ascending order (1,2, 3, 4..), we can conclude that all columns were sorted at the same time, in a related way.

2. Sorting Google Sheets according to two rules.

What if you want to sort by two categories? For example, what if you want to sort data with employees, by their interest (but rather by attractiveness to the employer) and company names. Let's try and do that.

With data selected, go to the menu Data > Sort Range. Now click on the button Add another sort range to add a second criterion for sorting the data. Now you can sort based on a few rules.

In this menu, order matters. Note to yourself that there is a first rule, "Sort by" and then the second rule "then by". In the example below, I've set the menu to sort interest alphabetically, and then sort companies alphabetically.

Click on Add another column for sorting, which will allow you to set several rules for sorting your data.

Use this multi-level sort to define how the data will look. You can add as many rules as you need to present the data in the order you want.

3. Use Filters in Google Sheets

If sorting affects the order in which data is presented in a table, filtration affects what data will be displayed in the table. Let's learn how to use Google Sheets filtering to reduce the amount of data displayed.

To add filters, highlight your data. Then go to Data > Create Filter in the Tables menu.

Now that you've enabled filtering, you'll see a small icon in the top right corner of the column name. Click on this icon and you will see the list of values ​​available in this column in the dropdown menu as in the screenshot below.

Click the icon in the column heading to select how you want to restrict the data set.

In this pop-up menu, switch to the criteria you want your data to meet. When you press OK, there will be less data in your table, and only those that satisfy the rules of your table will be displayed. google filter Tables.

As with sorting, you can set several levels of filtering. In the example below, I have added a filter to the column " Salary level"(Salary Grade).

This is a great example of how useful filtering is when working with data. Combining the "interest" filter with the "salary level" filter, I got a list of two candidates out of 100 possible.

It is important to remember that filtering does not remove data. They just stay behind the scenes, ready to reappear when you change the filter conditions.

Finding missing data

There's something you need to keep in mind when you're working with data filtering: you need to be careful about what data you leave behind the scenes. I won't tell you exactly how many times I've worked with tables and missed the fact that data is missing because it was removed from Google Sheets filtering.

I always keep an eye on two things if the data doesn't make sense:

1. Filter icon at the top of a column
2. Line numbers in sheets on the left

Notice in this screenshot that the line numbers (at the far left) are now out of sequence, and there is also a filter icon above column F.

These two factors allow us to be sure that you have not missed data in your table.

We summarize and continue learning.

Filtering and sorting are critical tools for data reduction and selection. Check out the other lessons below to learn more about using Sheets to view and analyze data.

You just learned how to filter and sort data in Google Sheets. How do you work with large amounts of data? Do you use sorting and filtering to simplify your data, or do you use other analytical tools?

Algorithms

• tutorial

In this article we will talk about a few very useful features Google Spreadsheets not in Excel (SORT, array concatenation, 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 considered closer in Google Document goo.gl/cOQAd9 (file-> make a copy to copy the file to yourself on Google Drive and be able to edit).

Table of contents:

If the formula result spans more than one cell

First about important feature display formula results in Google Sheets. If your formula returns more than one cell, then that entire array will be displayed at once and take up as many cells and columns as it needs (in Excel, this would require you to enter an array formula in all these cells). On the following example let's see how it works.

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 sort; sort_column; ascending; [sort_column_2, ascending_2; ...])

An 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 the Russian regional settings of the table, reg. settings are changed in the menu file → table settings)

How to add table headers to SORT?

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

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

Let's look at how ranges can be combined 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 VLOOKUP or MATCH.

Who read the previous example already guessed what to do: open curly brace and collect the arrays for concatenation, separating them from each other with a semicolon and closing 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, it is necessary to observe only the same number of columns in all fragments (we have two columns everywhere).

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

(semicolon and backslash are array element separators in Russian regional settings, if the examples do not work for you, then through the file - table settings, make sure that you have them)

Well, now let's return 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 keep the same number of columns for a vertical join or rows for a horizontal join.

All analyzed examples can be viewed more closely in
Google Document.

FILTER

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

Function syntax:

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

One condition

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

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, like SUMMESLIN, where the range of the condition and the condition itself would be separated with a semicolon.

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

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

Two conditions and work with date

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

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

=FILTER(A3:C7;B3:B7="Natalia 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)

Interactive Graphing with FILTER and SPARKLINE

Do you know how else you can use FILTER function? We can not display the result of the function on the 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, the sparkline has many settings, such as the type of graph, the color of the 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 validation;
• rules: Value from the range and in the range, select a column with employees from the source data, do not worry that the names are repeated, only unique values ​​\u200b\u200bwill 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 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 how it looks in dynamics:

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

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 in order to process or view them.

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

The formula syntax is as follows:

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

spreadsheet_key (key) - the sequence of characters of the "key=" (key) attribute in the spreadsheet reference (after "spreadsheets/…/").

An example of a formula with a key:

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

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

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

If in source file the number of columns or rows can change, enter an open range in the second argument of the function (see also subsection “Ranges of the form A2:A”), for example:

Sheet1!A1:CM (if lines 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 insert any data manually into columns A:D in the file where the IMPORTRANGE formula is located (that is, in the final one where the data is loaded). They seem to be “reserved” for the entire open range - after all, its dimension is unknown in advance.

Import formatting from a source table

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

After pressing the button Copy to… select the book to which you will import the data. Usually desired table is on the tab Recent(if you really recently worked with her).

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 original sheet - both in the data part and in the format part:

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 value of sales from a range located in another document.

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

First we import this range:

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


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

IMAGE: add images to cells

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

The function has the following syntax:

IMAGE(URL, , , )

URL is the only required argument. This is a link to an image. The reference can be specified directly in the formula by putting it in quotation marks:

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

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:

The mode argument can take four values ​​(if omitted, it defaults to the first):

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 image dimensions in the third and fourth arguments of the and function. , , respectively, are needed only when the mode argument value is 4. They are specified in pixels.

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

The fourth mode can be handy if you need to pick up Exact size pixel images by changing height parameters(height) and width (width). The picture will be updated immediately.
Please note that in all modes, except for the second, there may be unfilled areas in the cell, and they can be filled with color:

GOOGLETRANSLATE and DETECTLANGUAGE: translate 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 to be translated. You can take the text in quotation marks 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;
is the language we are translating into.

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 they can be taken from the cell, and the language source code and can be determined automatically.

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?
formulas

google sheets

Add tags

Sorting data is a useful and fairly common operation. After all, when you work with large amounts of information in a table, organizing the data in it becomes a very important task. Sorting data in a Google spreadsheet helps to put things in order, to identify patterns in them. Exist various tricks sorting on a Google sheet sheet or in a selected range.

We will look at how table sorting differs from range sorting, how to correctly describe the sorting criteria, how you can sort by several columns of a Google spreadsheet at once. We will also focus on ways automatic sorting data if it changes.

In this article, you'll learn how to sort data for easy viewing and organizing table content.

• how to sort data in google sheets
• sorting the entire sheet
• sorting an individual range
• sorting by multiple columns at the same time
• automatic sorting

How to sort data in Google Sheets

Google Sheets uses two types of sorting:

1. Sort by columns
2. Range sort.

Now we will talk about each of them in more detail.

So let's sort our table by column.

Sheet sorting

With this sorting method, all the data on the sheet will be sorted. Be careful if you have multiple tables on your sheet!

Attention! Before sorting the entire table by columns, be sure to freeze the column headers - otherwise they will also be sorted and end up in the middle of your data!

So, in order to make it convenient for us to view the sorted data, let's first fix the column headers so that they are constantly visible and not hidden when viewing the table down.

To do this, place the cursor on any cell of the table header and select View - Freeze from the menu and then specify the required number of rows.

Then choose which column we will sort our table by.

By clicking the triangle next to the letter of the column, you can choose 2 sorting options - ascending or descending. This will sort the entire table.

Alternatively, you can select from the menu Data - Sort sheet by column... and then - the desired sort option.

It is important to note here that if you have selected the entire column, then you will be able to sort the data range (which we will talk about below).

But if you choose "Sort range by column...", then only the selected column will be sorted, and the rest of the data will remain unchanged. In this way, you will break the structure of your data, and there is no point in such sorting. Therefore, be careful!

Range sort

Let's consider the second way - range sorting.

This is useful if your Google Sheets sheet contains several small data tables and only one of them needs to be sorted.

Sorting a range will not affect the data in any other tables on the worksheet.

Use your mouse to select the range of cells you want to sort.

You then have 2 options:

1. Click right button Mouse and in the menu that appears, select "Sort Range".
2. In the menu, select Data - Range Sort.

An additional window with sorting settings will open.

Here you can indicate that the data range you selected contains the column headings. In our example, we did not highlight the column headers, so we leave the checkbox empty. That is, we will sort the entire selected range. If you check this box, then the first line of the selected range will not be sorted.

Also, by clicking on the drop-down list icon, you can choose which column to sort by. We chose column B.

Here, by default, the sort order is "ascending" from, A to Z. If necessary, you can change it to the opposite - from Z to A.

Sorting data in a Google spreadsheet by multiple columns

If you need to sort by several parameters, then you can add one more or even several columns in the sort settings.

To do this, use the "Add another column for sorting" item in the sorting settings. Then select the desired column from the dropdown list.

For each column, you can choose the sort order - ascending or descending.

As a result, sorting will be performed sequentially - first by the first column, then by the second, and so on (in our case, first by B, then by D).

Here, in our example, we have sorted orders first by customer and then by product. As a result, we see the orders of each customer, sorted by product.

Google Spreadsheet Automatic Sorting

Suppose we have a table with sorted and ordered information. But what if new data is added to the table? After all, they will no longer be ordered, and we will again have to repeat the sorting?

1. Using the SORT function.
2. Using a pivot table.
3. Using Google Scripts (we will not consider here, since it is available only to developers).

For those who do not have programming skills, using the SORT function will be the easiest solution.

To do this, we will create another sheet, which we can call "Sort". On this sheet we will place our sorted data.

Our data will be located on the "Data" sheet.

Let's copy the column headings from the "Data" sheet into a row, A of the "Sort" sheet.

In cell A2 of the "Sort" sheet, write the function formula:

SORT("Data"!A2:G , 2 , 2 )

Here "Data"!A2:G is the coordinates of the range from which the data will be taken for sorting.

Note that we are only specifying the top left cell and the rightmost column. This gives us the ability to add new data to our original table without changing the sort formula.

2 - serial number column in the specified range by which we will sort. In our case, we will sort sales by customer name.

2 - sort order. 2 means sorting in ascending order, from smallest to largest. 1 means sorting in descending order, from largest to smallest.

As a result, on the “Sort” sheet, we received sales data sorted by customers.

To test the automatic sorting capabilities in Google Sheets, let's add another row with sales data to the original table on the "Data" sheet.

This entry will immediately automatically appear in the table on the "Sort" sheet.

You can also use a pivot table to sort. It also does not affect existing data and is placed on a separate sheet.

Select with the mouse the columns of the table that you want to include in the PivotTable, and then from the menu click Data - PivotTable. More about working with pivot tables and their capabilities you can read in our material.

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 supply, or programs included in branded shells. So to begin with, let's consider the general situation using one of the examples.

Android contacts are in the application of the same name. As a rule, developers of shells for Google OS make it the first home screen so it's not difficult to find it.

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

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 choose which contacts to show in the application. There are options: SIM, Skype and other instant messengers, accounting google records and the actual phone book of the device. The user can check or uncheck the boxes next to each source. Useful advice: if it annoys you that Android pulls up email addresses, nicknames from Skype and turns phone book into the mess, then just turn off the corresponding sources.

The search for contacts in the application has auto-selection. To find right person, it is not necessary to type his full name. It is enough to specify the first letters and the application will select all matches.

When creating a contact, the user selects its location, then indicates the number (you can choose from 6 options), e-mail. A contact can be added to a group. In addition, for each contact, you can specify additional fields: from a birthday or other significant events to an arbitrary 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 app. You can also set up synchronization with accounts, view the capacity of the SIM card, and select the tabs that will show when you scroll left and right.

How to Transfer Contacts from Android to Android

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

Every Android smartphone is tied to account Google. To transfer contacts from Google, you just need to make sure that the old smartphone is enabled to sync contacts with Google profile and Gmail. To do this, go to Settings - Account and Sync - Google - Gmail. Next, click on the account name and select Contacts there. Depending on the settings made earlier, they will be synchronized automatically or when clicked. Further, on the new device, it is enough to do the same for the contacts to appear on it. It is important that the gadgets are connected to the Internet.

You can also transfer contacts from Android to Android using a SIM card or VCard file. To do this, in the Contacts application, go to Menu - Manage Contacts - Import / Export Contacts. Here you can export contacts to the 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 drives, either send by e-mail to yourself (but here it is more logical to use the synchronization via Gmail described above), or transfer via Bluetooth. Then, on the new phone, you need to repeat the steps already described for importing contacts.

You can also transfer contacts from phone to phone using via Bluetooth. True, this method is only suitable for a small number of entries - 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 desired 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, syncing with Gmail and using a VCard file. Both will require some effort.

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

Now we need to assign Gmail account the default entry for iPhone Contacts. The setting is done in the Settings - Mail, Addresses, Calendars section. In the Contacts sub-item, one of the last ones will be the Default account. You need to select the item and mark Gmail. After a while, the contacts will be synced with Gmail. If it already has contacts, then they can be duplicated.

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

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

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

We have already described the next steps. In the Contacts application, you need to go to Menu - Manage contacts - Import and export contacts, select Import contacts, and then the location of the VCard file - in the phone's memory or on an SD card. The Contacts application will copy all the data from the file to the device's memory.

Instead of writing a VCard file to an Android phone, you can import contacts from it into Gmail and then sync it with your gadget. To do this, in Gmail you need to select Contacts, and there - the Import item. In the pop-up window, select the last item. It remains only 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 to Android. You can use applications that allow you to make a VCard file. Since the contacts Windows users Phone are synchronized with the account Microsoft record, 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 by syncing with Outlook.com, you need to add an account to your phone. To do this, go to Settings - Accounts and sync and click +. From the proposed selection, click on Outlook, then enter your account information and select sync contacts. At this method there are some disadvantages. In some cases, Outlook does not allow you to edit contacts on third party devices, so it's better to make contacts "native" to Android by transferring them via a file

On Outlook.com, select People from the menu. Then click on the Manage button, and in the new menu - Export for Outlook.com and other services. 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 CSV file or VCard. It remains to select a file on your hard drive, contacts will be uploaded to Gmail, and then synchronized with Android.

How to Transfer Contacts from Android to Computer

It is very easy to transfer contacts from Android to computer. It is enough to generate 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 item you need: Export to SD card or Export to phone memory.

All contacts will be saved to VCard file. Then it can be written to the computer's memory and opened in the appropriate program for working with contacts. The file can be stored as backup on the computer. However, it will not sync with Android. Therefore, it is advisable to sync 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 to a VCard file.

At opening VCard in Windows programs there may be problems with the display of the Cyrillic alphabet. VCard is saved with UTF-8 encoding by default, while Windows uses Windows-1251, however, in latest versions Microsoft OS has extended support for Unicode. However, if the problem still exists, 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, VCard is a plain text file.

Sync Android Contacts with Google

Android contacts are synced with Gmail and Google by default if the corresponding account is added to the phone. And without an account, it is impossible to fully use Android.

To add an account, you need to go to Settings - Accounts and Sync. Here, click on the Plus and select from the list of 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, then there will be a name mailbox"[email protected]". By selecting an account, you can manually start synchronization of 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, then all accounts will be synchronized automatically. If it is disabled, then synchronization must be started manually, as described in the paragraph above.

Import Contacts to Android

Importing contacts on Android is organized quite simply.

In the Contacts application, you need to go to Menu - Manage contacts - Import and export contacts.

A menu will open with three options for importing contacts. Import from SIM. It must be selected in order to transfer contacts from the SIM card to the phone's memory. Import sSD card and Import from phone memory must be selected to overwrite contacts from contact files, such as VCard. Android does not need to specify the location of the contacts file. The system itself will find all supported files and prompt you to select the one from which the data will be imported.

If it is not physically possible to connect the phone to a computer in order to write a VCard file to the phone, it can be sent by mail, via Bluetooth, uploaded to the cloud and then downloaded to the device.

Importing contacts to Android can also be done through the application Google Contacts. You can access it from Gmail. In the Extras menu item, select Import contacts. Further, to transfer contacts to Android, it is enough to synchronize contacts with Gmail.

AT new version In Gmail, the Import item is in the More section, not Advanced. It also supports importing contacts from yahoo mail, Outlook, AOL and more. Therefore, if contacts are synchronized with others postal services, you can pick them up from there directly. It is enough to enter the access data to the mail. True, Google warns that for a number of services, the contact import function is implemented at the expense of third-party developers, so it is up to everyone to decide whether to trust their credentials to third-party service providers.

Where are contacts stored in Android

Contacts in Android are stored in the contacts.db or contacts2.db file. It represents the base SQLite data. It is easy to find software on the Internet that can 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 given file, he will not see the folder in which the database is located. Only if you have root rights, you can 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 app.

In the application menu, select Manage contacts and then Import and export contacts. There are three options for exporting: 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, both on Android and iOS, mail services, cloud, desktop applications.

How to Recover Android Contacts

The solution to this problem depends on whether the contacts were synchronized with Gmail, whether a backup copy of the VCard was made.

If the contacts have been synced with Gmail, then just sync them to your phone. However, it may happen that the contacts have been accidentally deleted from the Android smartphone. It synced with Gmail and the contacts there also disappeared. It's not a problem.

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

If a backup was made to VCard, then to restore it, it is enough to load the file into the device's 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.

To delete one contact, you can open the Contacts application, select desired contact and press it while holding your finger at one point. will appear context menu which has a Delete option.

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

The Delete contacts item is also in the menu of the Contacts application itself. When clicking on Delete contacts, the user will be offered a list of contacts in which he 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 back up contacts at once

The first method is simple and reliable, but it will be difficult to keep your contacts backup up to date. It's about about exporting contacts to a VCard file. See Exporting Android Contacts above for how to do this.

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

Choosing an account for Android backup

The third way is to enable backup for all phone Google servers. This is done by Settings - Backup and Reset. When activating the backup copy android will make a copy of the phone on Google servers, not only contacts will be stored there, but also all personal data, application data, etc.

Android contacts apps

The Contacts app for Android has good functionality and is quite convenient, so it makes a lot of sense to third party solutions no, but still we will consider some applications

Contacts

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

This application is an alternative for standard Contacts from android. It does not differ in extended functionality, but rather interesting in a different approach to storing the phone book. If Google Contacts stores all information in a database, then Contacts– in a vCard (vcf) file. It is this file that can be obtained as a result of export.

When you install the app, it either bases on an existing vCard file or creates one based on your phone number. android books. As a result, the user always has actual file vCard. This can literally save in the event of a sudden system failure.

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

Contacts+

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

Makes contacts more fun. All contacts are presented with avatars. This representation simplifies the call desired subscriber– choose by faces faster. There are advanced search functions: you can search not only by name or phone, but also by mail, SMS, etc.

Supported themes and various options phone book design. Contacts+ also have a call log and SMS.

Contacts are stored in encrypted form.

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

Save contacts to TXT

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

AT Android Contacts can be exported in vCard format. This application allows you to save them to txt. You can save to text file All contacts, separate entries or several.

It also allows you to customize which fields will be saved in the file.

In the future, the file can be used at the discretion of the user. 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 the dialer, Caller ID, work with SMS. However, the application has several interesting features to work with contacts. The most important one is backup. You can make both a local copy of contacts and save it in the application cloud. You can set up automatic backups. The search for duplicates in contacts is carried out by the application constantly in automatic mode.

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

Also can be used for application interface various themes.

SA Contact Lite

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

Applications for exporting and importing contacts to Excel files. Excel is the 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 a 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, 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. By means of Android everything can be done possible actions with contacts.

However, if you want to decorate and diversify the dialer or phone book, then in Play market enough apps. True, you need to be careful when choosing them. It's all about personal data.