Download Google Sheets for Windows 10. Create an online presentation from Google. Good visualization = better comprehension

Hello everyone, this is the very first article of this blog and in it I will cover the main points of using Google Sheets. From it you will learn how to create a new Google spreadsheet, how to edit it, save it, add new users, copy it, delete it from Google Drive and other information.

For your convenience, in each article I will post quick links to sections; by clicking on them, you will quickly go to the question you need:

Creating a google account

So, in order to start using Google Sheets, we need to have an account in Google services. If you have a Google account, then you don’t need this section. And if you haven’t started it yet for some reason, then you need to go to the page Google and in the upper right corner click on the button To come in:

The Google account login page will open, but we don’t need to log in, but register.
To do this, click on the link at the bottom of the login form Create an account:

Later, I will write a separate post where we will take a closer look at the capabilities of a Google account. Let's try to understand the hidden powers of Google services that you can manage from your account.

Google Sheets Login

Now we have reached an important point, now we will figure out how to enter Google tables.

After we have already created an account with you in Google, we can open tables in several ways:

1. The first and easiest way is to follow this link Google Spreadsheet
2. The second method is intuitive - just google: Google Sheets

Now you can start working in Google Sheets.

How to Create a Google Sheet

So, we have opened the Google Sheets start page. Let's now make a new table.

In order to create a new table, on the main page you need to click on the floating red button with a plus sign at the bottom right:

How to name a Google Sheet

To do this, we just need to place the cursor in a special field at the top left of the table, where by default it says New table and enter your name:

After pressing Enter or simply placing the cursor anywhere in the table, your name will be saved. Now a line with the name of our table will appear on the Google Sheets start page. And every time you go to this page you will see all your saved tables:

Very convenient, isn't it?

How to Add Additional Pages and Hide Them in a Google Sheet

Now let's create additional pages in our table and then try to hide them.

Let's go back to our table and at the bottom of the monitor screen we will find a button with a + (plus) symbol:

By clicking on it we will add a new page. As a result, we will see two tabs named Sheet1 and Sheet2. It doesn’t sound very good, in my opinion, a little later we will call them by a clear name.

But before that, let's try to hide the pages from prying eyes. To do this, right-click on the tab or on the triangle on the tab and select the item in the pop-up context menu Hide sheet, as a result we get this picture:

Hurray, the sheet is hidden! But how can I restore it now?
Very simple - you need to go to the control panel and follow the path to the menu - View -> hidden sheets -> your hidden sheet:

The leaf will return to its original place.

How to title and color pages in Google Sheets

Let's give our pages names. This can be done in at least two ways:

1. Right-click on the tab and select from the context menu Rename…
2. Left-click on the triangle icon on the tab and also select the command in the context menu Rename…

After entering the name, as well as after naming the entire table, you can click Enter on the keyboard or simply place the cursor in some cell and the name will be saved.

One day you may have so many pages in one table that they will not all fit on one screen, and in order to quickly move through the pages, you can click on a button resembling a menu symbol on mobile devices, it is located next to the add pages button. By clicking on it, a list of all available pages in this table will pop up. The active table will be marked with a checkmark; all you have to do is select the one you want and click on it in the menu.

In addition to renaming pages, they can be painted in the color we need.
Let’s say the deadline schedule can be colored red and we will always instantly find such an important page among the many pages!

To do this, you need to perform similar actions as when renaming, just select in the context menu Rename, A Change color:

And choose the desired color. By selecting Custom color, we will be asked to enter a custom color number.

How to Edit Google Sheets

Now we’ve reached the point when it’s time to edit the table and add data to it: text, numbers, formulas, functions, etc.

In essence, Google Sheet is an analogue of Excel, but there are, of course, differences between them. We'll talk about the differences between Google Sheets and Excel in another article. For now, let's start editing the Google Sheet.

Working with data in a Google Sheet - adding, copying and deleting

In this chapter, we will learn how to work with Google sheets and the data in them, namely, add, copy, delete data, insert comments and notes, and manage them.

Adding Data to Google Sheets

Of course, I won’t open Newton’s binomial if I say that adding text, numbers and formulas to a Google spreadsheet is as easy as adding it to Excel. But just in case, I’ll remind you.

In order to add any data to a table, you need to open it, insert the cursor into the desired cell and start entering text or a number. In addition, you can enter data into the formula bar, which is located in the same place as the formula bar in Excel.

Numbers, as in Excel, after entering, are aligned to the right side, and text is aligned to the left side of the cell. But the alignment can be changed using the alignment tool.
To do this, select the cell or range of cells in which you want to change the alignment and click on the following icon on the toolbar:

In the drop-down menu, select how you want to align your data - left, center or right.

How to copy data into a Google Sheet

Everyone knows how to copy and paste data inside a Google spreadsheet, I think: select the desired range of cells, press the keyboard shortcuts Ctrl+C, place the cursor in the desired cell and click Ctrl+V- this is the easiest and fastest way, it works in all Office products, not just Google.

In the same way, data from Excel is copied and pasted into Google spreadsheets, but there is a significant nuance:
Formulas are not transferred from Excel to Google Sheets, but only the result of their work. Therefore, in order for all data to work as in Excel, it is necessary to open the file in Google Sheets. I wrote how to do this below.

Deleting data in Google Sheets

Everything is as simple as in Excel: select a cell or range, press on the keyboard Delete. If your cells are formatted in some way, for example by color, then pressing Delete alone will not be enough.

To remove formatting from a cell or range in Google Sheets, you need to select the desired range of cells and select from the menu bar: Format -> Clear Formatting or press the keyboard shortcut Ctrl+\ on your keyboard.

Comments and Notes in Google Sheets: Adding and Deleting

In Google Sheets, unlike Excel, you can add notes and comments to cells.
In Excel you can only make notes. Let's see how to add one and the other and try to understand the difference between a comment and a note.

How to Add a Note in Google Sheets

To add a note, you need to select the cell to which you want to add a note and do one of the manipulations to choose from:

1. Insert Note
2. Select the item on the menu bar Insert -> Note
3. Press keyboard shortcut Shift + F2

How to Add a Comment in Google Sheets

You also need to select the cell and perform one of the manipulations below to choose from:

1. Right-click on the cell and select from the context menu that appears Insert comment
2. Select the item on the menu bar Insert -> Comment
3. Press keyboard shortcut Ctrl + Alt + M

In both cases, after adding a note or comment, a small triangle icon will appear in the upper right corner of the cell to let you know that there is something there.

In addition, on the page tab, if there is a comment, a hint will appear in the form of a number indicating the number of cells on the page that contain comments. And when you hover over this tooltip, all existing comments will be “highlighted” in this way:

So what is the difference between a comment and a note in Google Sheets?

This is where the beauty of working with online tables is revealed. In addition to the above-described function with comment highlights on page tabs, you can get a hyperlink to a comment, which you can then give to colleagues:

And they, in turn, can respond to your comment by following your link.

In addition, participants in such a “conversation” will receive notifications of new replies by email. Of course, this requires that participants be logged into Google accounts and have general access to this table.

In my opinion, such features are one of the main reasons why you should use online spreadsheet services and, first of all, Google Spreadsheets.

Delete notes and comments in Google Sheets

Everything is simple here, if we are talking about a note, then select the cell where there is a note, right-click the mouse, call up the context menu and select the item Remove notes.

Deleting a comment is also not difficult, you need to hover your mouse over the cell with the comment, all your correspondence with colleagues will pop up and you need to click on the button Issue resolved:

After which the entire chain of comments will be safely deleted from the cell. But at the same time, the correspondence will be saved just in case. You can find it at the top right of the screen next to the button Access Settings:

At the click of a button Comments you can view the entire history of correspondence and even continue the conversation or return the comment to its original place, if necessary.
And there you can set up alerts:

An absolutely amazing tool for collaborating on Google Sheets.

Protecting Data in Google Sheets

There is often a need to protect data in tables; you can protect the entire table, a page in your workbook, or a specific range of cells.

Why is this being done? It happens that by chance one of the employees edits data on some sheet or range; this can happen completely by accident and the one who did it will not even know about it. Digging into the history of changes later is not an option, to be honest. This is why it is possible to protect data in Google Sheets. Let's see how this can be done with examples.

Protect an entire workbook in a Google Sheet

In order to protect the entire workbook from editing, you need to click on the button at the top right of the screen Access Settings and then specify people (gmail account) and access level - Editing, Commenting or Reading:


In this case, we are interested in Reading - this means that people who have access to your table will only be able to view it.

Protecting a Google Sheet Page

Let's say you want to protect not the entire table, but only one of the pages, in which case you need to take a couple of simple steps.

Select the sheet that you want to protect, click on the triangle on the tab and select the item from the pop-up context menu Protect sheet...:.

The following sidebar will open on the right side of the screen:

This is where we will configure the protection.

Field Enter a description is not mandatory, this is for your convenience, so that you understand what data you are protecting, so to speak, a kind of mark for you.

Now we are asked to select which sheet to protect, we have already selected Sheet2, because it was on its tab that we clicked when we selected the item Protect sheet.

Below we are asked to mark the cells that you want to leave unprotected (very convenient). If we activate this checkbox, we will be asked to select a range of cells; by default, the active cell or selected range in our table will be indicated. To select another range, you need to click on the icon in the form of a small table and select the desired range on the working page:

Or you can manually specify the desired range to the left of the table icon.

Click Ok.

Click on the button below Set permissions and let's get down to the most important thing - protecting our sheet.

A modal window will appear with settings for accessing the allowed range:

If you select the first radio button, then anyone who has access to the entire table as a whole will have access, but they will see a warning that they are trying to edit a protected range and they will have to confirm their actions each time. And you will receive notifications by email about new actions in the protected range.

If we choose the second option, we will be asked to add editors. By default, the drop-down list contains the value Specify… and below there is a field for entering the email of the future editor. In addition to the Specify option, there are other options:

• Only you
• Import list of editors...

In order to remove protection, you need to click on the desired range on the right in the sidebar:

All settings will open again, click on the trash icon:

Protecting a specific range of cells in a Google Sheet

Range protection is practically no different from sheet protection, the only difference is in the first steps.

You need to select the range that you want to protect, right-click and select from the pop-up context menu Protect range:

And repeat the steps described in the previous subsection.

One day you need to cancel range protection, but you completely forgot where that range is.

In this case, go to the following menu path - Data ->Protected sheets and ranges… and a sidebar will open on the right with all the protected ranges available on this page:

All you have to do is select the one you want and click on the icon Basket.

Undoing an Action in Google Sheets

Undoing actions in Google Sheets works the same as in any Office application, the only difference is in the interface design. In Google Sheets it looks like this:

Or you can cancel the action in the classic way using the keyboard: Ctrl+Z

How to save a Google Sheet

There is no need to save data for today in Google Sheets. The service automatically saves data after any action you take. But if we talk about saving the entire table on your Google Drive, then this is a different matter.

To save your Google spreadsheet to some folder on your Google drive, you need to click the folder icon at the top above the control panel:

A small window will open where you will be asked to select the Disk and folder to move this table. Alternatively, you can create a new folder directly from here. Let's try:

Click the Move object button:

Click on the arrow to the right of My Drive:

Click the new folder icon (if you need to create a new one):

Enter the name of the new folder and click on the blue checkmark:

This is actually the entire procedure for saving a Google spreadsheet to the desired existing or new folder on our Google Drive.

How to change page zoom in Google Sheets

Changing the scale was added relatively recently to Google Sheets and you can do it as follows:


We go through the menu: View -> Scale.

In addition, you can select the table display mode to be full screen. To do this, select the menu item on the control panel View -> Full Screen. After this action, the tool control panel will collapse at the top. Simply put, the top menu bar will disappear.

In order to return the viewing mode to its previous form, simply press the key on the keyboard Esc .

How to Import an Excel File into Google Sheets and Edit It

Very often there is a need to transfer all data from Excel to Google spreadsheets. The reasons are different for everyone, but they all have approximately one main reason - online tables allow you to carry out group work in real time, with the ability to add email notifications about various actions in the tables and many other reasons.

You can, of course, simply copy the table using simple steps Ctrl+C And Ctrl+V, but as I wrote above, this method is not very good - if your table contains important formulas, they simply will not be copied into an online table on Google.

Therefore, let's look at the option of importing an Excel table into your Google Sheets account.

There are two ways to import xlsx files into Google Sheets - quick, simple and longer, let's consider both:

That's all! The Excel file has been imported into your Google Sheets and can now be found on the Google Sheets home page and in your drive. It can be moved to other folders on Google Drive and downloaded to your computer again. I’ll tell you how to do this below.

Export Google Sheets

Of course, Google could not help but provide the ability to download Google Sheets to your computer. And this can be done easily and in different formats!

To upload your Google Sheet to your computer, click on the control panel sequence of menu items - File -> Download as and select the format you need:

As you can see, the formats that Google offers should satisfy almost any user requirement for any purpose.

View change history in Google Sheets

Google Sheets has a function for viewing the history of changes, in case someone from the team or yourself messed up, you can view the changes and restore the desired version.
Let's figure out how to do this.

To view your history of changes you don’t need to perform many actions, just make a few clicks in the control panel, namely follow this path - File -> View change history or press the keyboard shortcut Ctrl + ALT + Shift + H :

Now let's see what the history viewing interface looks like and figure out how to restore the version we need.

After we have entered the Google Sheets change history, we will see this page:

At the top left we will see that the control panel has been collapsed and instead of it the inscription History of changes with an arrow, by clicking on which we will return back to our current table.

A sidebar opened on the right with controls for the story itself. The first item will be active, it indicates that this is our current version of the table. Below are the actual steps of the story in reverse order, but this is not the most detailed story.

At the very bottom you can see a button In details, if you click on it, a list with a more detailed history will open.

In addition to the button, we see a checkbox Show changes– from the name itself it is clear what role it plays.

By clicking on history items, we will see the changes that our table has undergone, and there we will see a link Restore this version:

A very important point! If you have several pages in the table, then be careful when “walking” through the history, one of these “points” in the history may unexpectedly switch to another page (tab) in your table, which can be confusing.

In order to see the history of a page you are interested in, just go to it if suddenly a page you don’t need opens.

How to change the language in Google Sheets to Russian

It happens that you registered, but the Google Sheets interface is not Russian, but English, for example. If you are faced with such a problem, then this chapter is just for you!

This problem is solved quite simply; I specifically translated the language of my interface in Google Sheets into English to show you how to make it Russian.

We close this page, return to the previous one with our table and simply update it by pressing F5 on the keyboard.

Ready! Enjoying the Google Sheets interface in Russian.

How to Share a Google Sheet

We have already partially discussed above how to give collective access to our Google Sheets. Let's now understand once and for all all the intricacies of accessing Google Sheets.

After creating and editing the table, we may need to grant access to colleagues, partners or clients. Let's consider two options - quick simple and advanced.

1. Simple and fast option:
   Click the button at the top right of the screen Access Settings, a modal window will open in which we will be asked to enter the name or email of the people to whom we want to give access. Just to the right of the field for entering this data there is a drop-down list with access levels:
   
   

   By default, it has editing access; by clicking on the pencil icon, you can select the access level.
   Editing is the highest level of access.
   

2. The second option for providing access to Google Sheets is more advanced.
   Click on the text at the bottom of the modal window Advanced:
   
   

   You can change the owner of the table right here (if there are already added people):
   
   

   You can also invite new users by indicating their emails.
   And at the end there are two checkboxes:

   • Prevent editors from adding users and changing access settings;
   • Prevent commenters and readers from downloading, printing and copying files. If you previously indicated that the table is available via a link, then you will not be able to change the access level in the advanced settings, but if you indicated that it is by invitation only, then you can change the access level by clicking on the link Change.

How to Email a Google Spreadsheet as a File

Another very convenient feature that is only available in online spreadsheets such as Google is sending a document by mail as a file. The file can currently only be of two types: PDF and XLSX.

Let's send our table to our colleagues or clients.

In order to send our table as a file, you need to select the following items on the control panel - File -> Attach to email. Posts:

A modal window with sending settings will open:

• Attach as– Here we select PDF or XLSX format
• To whom– indicate the recipient’s email
• Subject– the name of our table is automatically inserted
• Message– you can add a message, for example, explaining what this table contains.
• Checkbox send me a copy- everything is clear here.
• Click send.

There is one small drawback to this function - such letters are sent only to Google accounts with gmail mail; in any case, I was unable to send them to Yandex mail.

How to delete a Google Sheet

Well, we have almost reached the end of the article, now we will learn how to delete our google table.

Over time, you will accumulate a fair number of tables and some will no longer be relevant, and if you no longer need them at all, then why take up precious space on Google Drive with them? In addition, the space is not rubber and has very specific boundaries - 5GB in a free account.

You can delete a Google spreadsheet in at least two ways: delete it while you are in the spreadsheet and delete it from the Google Sheets main page.

Let's consider both options:

In the drop-down menu, click Delete.

Note. Only the owner can delete a table; co-authors of the table will also lose access to the file.

By the way, co-authors who are not owners of the table also have such a button, and you can even click it, but the table will not be deleted, it will only disappear for a while (until the next visit to the main page of Google Sheets), then it will be displayed again. In this case, Google has provided the ability to filter files.
You can filter files according to the principle: Yours - Others - All in a row.

To filter tables, go to the main page of Google Sheets, find the drop-down list at the top marked Owner:

The default will be Anyone, click on this button and select filtering conditions.

In my opinion, it’s not very convenient that I cannot remove tables that I don’t need from my list, but only filter them.

How to recover Google Sheet

Surely many people have had this situation when they either accidentally deleted a file or deleted a table, thinking that it would no longer be useful, but then it turned out that you needed it again for one reason or another. But you don’t know how to recover a Google spreadsheet or other document file.

In this chapter, we will work together to recover seemingly lost Google Sheets files.

First, go to your Google Drive page:

In the left menu, find the trash icon and click on it:

A page with deleted files will open, where we need to right-click on the file of interest and select Restore:

This article has come to an end, I hope it was useful. If I missed something, write your questions and suggestions in the comments, I will definitely add to the article.

• Tutorial

This article will talk about several very useful functions of Google Sheets that Excel does not have (SORT, array joining, 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 Document goo.gl/cOQAd9 (file-> create a copy to copy the file to your Google Drive and be able to edit).

Table of contents:

If the 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.

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

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, 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)

! 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 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):

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):

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 . , 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 syntax of the function 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 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 Add tags

We've released a new book, Social Media Content Marketing: How to Get Inside Your Followers' Heads 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 Sheets. I used to hate everything related to spreadsheets, but now working with 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:

1. Click "View"
2. We point to “pin”
3. 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 keywords. 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:

1. Select the entire column by clicking on the cell with the letter, in our case “A”.
2. Right-click on the selected column and select “Conditional formatting.

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

In the menu:

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

Now let's install the addition for our table. To do this, in the top menu we find the button of the same name “Add-ons”↣ “Install add-on.” Type “Sort by color” into the search bar 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 is extremely case and space sensitive!

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. The program is in English, but with the help of Google translator and a couple of tests you will definitely understand it.

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. In general, this step was created to exclude 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 by which we can somehow evaluate the effectiveness of the query. For example, we do not want to use queries whose frequency is below 100. We can highlight all inappropriate cells with color.

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

Now, using the native method of sorting by color, we analyze queries 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, I didn’t have and don’t have much love for Google products. 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 advantage is the add-ons for Google Sheets, which are replenished and significantly increase 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 devoted to the topic of how to work in Google Sheets, additions, formulas and ready-made solutions for everyday tasks.

Google Drive gives users access to a range of useful tools that allow them to create and edit a variety of items, including documents, spreadsheets and presentations.

Thanks to the functionality of the Google Drive service, you can create and edit tables.

There are five types of files that can be created using this service:

• Documentation. For creating letters, leaflets, reports and other text components (the same as Microsoft Word documents).
• Tables. For storing and organizing information (same as Microsoft Excel documents).
• Presentations. To create slide shows (like Microsoft PowerPoint presentations).
• Forms. To collect and organize data.
• Blueprints. For creating simple vector graphics or diagrams.

The process for creating new files is the same for all types presented. Let's find out how it's done.

Work in Google Drive is carried out through your own, so you must have it available. Once logged into your account, open this service (to do this, type drive.google.com in the address bar of your browser) and click on the New button. It's a blue button in the top left corner of the window. After that, select the type of element you want to create.

If you need to create a table, select Google Sheets.

A new component will appear in a new tab in your browser. Look for Untitled spreadsheet in the upper left corner of the window. Click on this label to change the name of the newly created element. Enter a new name and press Enter to confirm the changes.

Now the table is in your Google account and you can access it at any time. To open a document, just go to the main section of the service and double-click on the desired component. That's all, the document has been created and you can work with it, adding the necessary data to the table cells.

Using ready-made templates

A template is pre-structured markup that you can use to quickly create new documents. It usually contains formatting and styling so you can save a lot of time when creating a new project. Most templates are designed specifically to help you work with specific types of components. For example, you can use this feature to quickly create a resume or newsletter.

You can find a large number of templates in the Google library called Google Drive Template Gallery. It is located at drive.google.com/templates. To use the template, do the following.

1. Visit the above address.
2. You will see templates divided into categories.
3. Choose the one that suits you or that you want to take a closer look at. Click on it.
4. A new component will be created containing this template. You can start working with it right away, filling it with your own information while maintaining the formatting set for this file.
5. Don't forget to change the element name to your own.

Making a copy of the required file

You can make a copy of any file to use its data or formatting for another piece of information. For this:

Downloading a copy of the file to your computer

Google Docs File Settings

You can customize a number of features for each Google Docs component, including tables. The following settings are possible.

Greetings, dear readers and guests of my blog. Today I will continue to talk about search engine services and tell you how to simplify your life and learn to work with useful tools. Ready to learn some cool instructions? Then let's move on to reading!!!

Most people who use the Internet are not aware of such a great application as Google Dox, but in vain. After all, it is no worse than the popular office suites OpenOffice and Microsoft Office.

What is Google Docs and its purpose

The developer company takes care of its users and constantly releases the latest products. One of them is Google Docs with an integrated word processor and programs for creating and editing spreadsheets and online presentations. The application is free and can be used both online and offline. Created documents can be instantly sent by email, and links to them can be provided for other users to view, edit, and comment on.

In cloud storage You can place countless objects. Google Drive provides 1 gigabyte of free storage for any files, which can later be increased up to 15 gigabytes. You can work with files both online and offline. The latter requires the Chrome browser, in which you need to enable offline mode for working with files. The application has many advantages, here are some of them:

• automatic saving ensures that your work is never wasted, even despite PC crashes and shutdowns. All changes can be viewed in history, which is very convenient and makes it possible to roll back to earlier versions;
• documents can be downloaded in all popular formats. For example, you can create a text file in Word on your own computer, save it on Google Drive, and edit or modify it elsewhere from another device;
• you can open access to anyone you need to make changes, view or comment.

Google Dox Sheets online: how to create and edit a document

In order to be able to work with objects offline, you will need to enable the offline mode feature. This will allow you to correct existing documents and create new ones. The next time you connect to the network, all data is synchronized. To enable the function, you need to open Google Drive and enter the settings by clicking on the gear in the top right. ( All images below are CLICKABLE )

On the screen that appears, check the “Offline access” box and agree to the requirement to install the “Google Documents Offline” extension.

Unfortunately, this feature is not available in all browsers. Therefore, the best solution for working with the application is to use the Chrome browser, which allows you to use it fully, with all the available features.

To create and edit a Google spreadsheet online, you need to go to Drive of the same name. Then click on “Create” in the top left. Select Google Sheets from the pop-up menu.

After this, the next tab will open in Explorer. Let's start creating the document.

First let's rename it, since by default it is called "New Table". To do this, click on the name and enter your name. Next, click on the “Borders” button, and in the menu that opens, select what you need. For example, “All borders.” As a result, we have a completed table.

The screenshot shows that the control panel is quite simple and intuitive. It is possible to change fonts, fill, color, etc. For those who have worked in Excel, everything is elementary. After entering all the data, you need to determine who can access the file. To do this, click on the page of our document “Access Settings” and select the desired item.

After that, when you hover the mouse over this button, a pop-up message about the accessibility mode appears. Thus, you can see what value is set in these settings.

Now, in order for other users to view this file, it is enough to send them the appropriate link.

There is nothing complicated in drawing up documents in this service. When creating new files, the Google Docs page will become cluttered, so it is better to immediately sort all objects into folders. To do this, click on the “Create” checkbox on Disk and select “New Folder” in the drop-down window. Give it a name and move your object into it with the mouse.

A little about Dox and browser compatibility. This application works perfectly in Chrome, Mozilla, Safari and Internet Explorer above version 8. Dox may not work correctly in other browsers. The presentation can also be made in it, as well as working with pictures in PNG, JPEG formats, as well as with PDF files.

How to make dropdown lists in a table

In the application, you can add text from a ready-made list. I will show you how to create a Google Sheet with such functions using the following example. In addition to the already compiled “Progress”, we will make a list of students on the second sheet.

On the first sheet, select a column for selecting surnames from the list. Right-click the mouse to open the menu, click on “Data check”, in the menu that appears, select the items “Rules”, “Value from range” one by one and click on the barred rectangle icon to select the data range. A list of surnames can be made on the first sheet.

Then we go to " Sheet2", select all the data and click OK.

Now you can easily enter the desired surnames from the drop-down list by clicking on the arrow at the bottom of the cell.

Similarly, you can create a list in the dialog box itself. To do this, you just need to select “Value from the list” and manually enter the last names separated by commas.

How to fix speakers

Freezing columns is a very convenient feature when creating a Google Docs table into which a lot of information is entered. To do this, open the file we need.

It can be seen that some of the data will not be visible when scrolling. Here are detailed instructions for securing a specific area:

• Select the desired columns with the mouse.

• At the top, select “View”, “Pin” and click on the required indicator. We select "1 line". To do this, by the way, it is not necessary to select a column. The screenshot shows that there are many options here. You can highlight the column you want and select Up to Current Column.

• That's it, the consolidation is done. Now the first columns will always remain in the top position when scrolling down.

To unfreeze, go back to the “View” menu and click on “Do not freeze rows” in the drop-down menu.

Create additional sheets and fields

It is very easy to add an additional sheet in this application. There are two ways:

• You need to click on the “+” icon in the lower left corner and a new sheet will appear.
• Click on the “Insert” tab at the top, select “New Sheet”.

You can add and remove various fields to analyze data in a PivotTable. To do this, create a document by clicking on the “Data” and “Pivot Table...” tab.

Left, in sheet3, there will be a menu where you can select the parameters of interest, based on which you can make a report on all the information provided.

What are Google Forms

They are needed to create online surveys. This is a highly requested feature that the developer has built into this app. They can solve many problems. For example:

• collect customer opinions about a new product;
• create a questionnaire for testing job applicants;
• embed feedback on the website;
• create a test in the text for a creative competition.

This is an indispensable thing for collecting information on the Internet. Using this application, you can create a survey that will collect data for free, without unnecessary intellectual effort. All you need is an Internet connection and a Google account.

The developer took care of additions that expand the capabilities of this processor; completely free at the same time. For example:

• After filling out the surveys, a message is sent to your email;
• you can insert formulas into the questionnaire;
• the number of respondents may be limited;
• at the end of the survey, a message is sent to Slack;
• All email addresses in surveys are confirmed.

Therefore, there is no need to look for an alternative if you did not find something in the forms - it may be in the add-ons.

How to create a registration form

Let's see how to make an online registration form using the following example:

• On Drive, select the “Create” button and then “Google Forms”.

• In the window that appears with a new document, we see the editor.

Enter the name and fill out the question line.

• In the “Answer option” item, click on “Text (line)”. We will make the question mandatory, which will not allow the respondent to submit data without filling out this field.

• Fill out the field for the second question and set the value to “Date”. Turn off the “Required Answer” button.

• Click on the “+” icon to add a new question. Fill in the field that opens. This time we select “One from the list”, enter the options – “Male” and “Female”. Click on the cross to delete the “Respondent’s Answer”.

• In the next question, select “Several from the list” and write down the options.

• In the fifth paragraph, add “Drop-down list” and fill in all the options.

• We formulate the next question as in the screenshot, selecting the “Scale” value.

• Add a new item “Grid”, enter everything that is required as in the screenshot.

• To select a design, click on the palette icon and select the theme you like.

• To view, click on the image of the eye, and if you like everything, then click on the three dots in the right corner and configure access.

If desired, the file can be published on social networks - Facebook, Twitter, etc., and also sent by email. You can also get a code to embed into a blog or website.

On the “Responses” tab, you can view them in a table. And on the icon with vertical dots you can configure the mode for receiving them. The screenshot shows several options that you can choose from.

You can see the answers both in the form of a summary and an individual user.

You can edit the form at any time by clicking on the desired object with the mouse.

How to create a survey in Google Form

First, to create a survey, you will need to sign in to Drive. As usual, click on “Create”, “Google Forms”. The window with the questionnaire will open in a new tab. We enter the name and explanation for the people who will take the survey (this field is not required to be filled in).

Let's take an example of compiling a questionnaire for parents of schoolchildren. Let's fill in the first paragraph. By clicking the mouse, instead of “Question without title” we type the required text. Specify the question type as “One from the list” and add options. Move the slider to the “Required answer” option.

If you need to delete any values, then click on the cross next to the unnecessary option. Answers can be swapped by dragging the three-dot icon with the mouse. There is no need to save anything specially, autosave works.

To enter the next item, click on the “+” icon on the right side of the screen.

To change the text, you just need to click on the desired line. Answers can be shuffled by clicking on the three dots icon and selecting the appropriate item. This is very convenient, since many people often choose only the top answers, and shuffling gives a more objective assessment.

There are several types of questions in Google Forms. Here they are shown in the screenshot.

They were discussed in more detail above. They can be copied if they are repeated in one way or another.

In the settings above the text you can edit the values. When “Show survey progress” is turned on, the respondent can see how much of the questionnaire he has filled out and how much more he needs to complete. If “Only one answer per person” is set, then there can only be one respondent from one account.

In the confirmation text, you can enter wording that a person will read after filling out the questionnaire.

After finishing working with the form, the questionnaire can be published on a blog and social networks. You just need to click on the “Submit” box in the upper right corner. In the dialog box that opens, fill in the required fields and click OK.

You can not only provide links to forms, but also embed them into your own website. You can edit the questionnaire at any time by left-clicking on the value to be corrected. You can find the survey and answers to it in the form of a Google Docs table on your Drive. But before that, you need to go to the “Answers” ​​menu and click on the green icon at the top.

How to set permissions on Google Docs

As mentioned, you can give other people access to Google objects to edit them, view them, and leave comments. To open rights to other people, you need to take the following steps:

• On Disk, select the desired object, right-click on it and select “Share...”.

• Select the required value or enter the user name and click OK.

To grant rights to other users to several documents at once, select the necessary objects and click “Open access to files.”

In the menu that appears, set the required values. Access to the folder is provided in the same way.

You can also open rights to people who have a Google account. To do this, select the desired object on Disk and click on the “Open access” icon. In the menu that opens, enter the email address or Google group.

Having looked at Docs' features, we can say that, despite some limitations, the application copes with its tasks and can be useful as a lightweight cloud editor. And its undoubted advantages are its simplicity and accessibility.

I hope the article was useful and you will use it as a cheat sheet when working. In order not to forget the article and always return to it, I would advise you to add it to your bookmarks. I will be grateful for reposting the article. All the best and see you soon -))).

Sincerely, Galiulin Ruslan.