It’s hard to imagine an online marketer’s work without spreadsheets. Previously, the main tool was Excel from MS Office, but now more and more specialists are switching to "Google Spreadsheets". And they can be understood:
- Google's online service is free.
- Conveniently implemented collaboration - no need to send files to each other a thousand times.
- Changes are saved automatically.
- There is a version history - if necessary, you can roll back to the moment from which everything went wrong.
- You can set up automatic import of data from third-party sources - analytics services, advertising cabinets, count tracking, etc.
Google Spreadsheets is a very versatile and functional tool with a bunch of features and usage scenarios. By the formulas alone, you can write a separate book. In order not to turn the material into a monstrous longrid, which no one can read to the middle, I will not consider advanced features, such as working with scripts, go into minor details and make voluminous instructions for each function.
This guide will show you what you can do in Google Spreadsheets, help you find ways to apply in your work and understand which direction to dig in to learn how to work in the program effectively.
- How to open Excel file in "Google Spreadsheets"
- Google Sheets Toolbar
- Cell, row, and column operations
- How to fix the line
- How to move table elements
- Change History in Google Spreadsheets
- How to delete and restore the table
- Working with data in Google Spreadsheets
- How to edit Google Sheets
- How to protect data from editing
- Comments and notes
- Data formats
- Conditional data formatting
- Filters and sorting
- Data checking
- Summary Tables
- Charts and graphs in Google Spreadsheets
- Work with functions
- Integration with other Google tools
- Interaction with Google Forms
- Google Analytics Integration
- Useful add-ons for Google Sheets
- Shortcuts and hotkeys in Google Spreadsheets
Getting started with Google Sheets
You can create new and open previously created tables on the main page of the service and through "Google Drive".
On the main Google Sheets displays all the tables that you have ever opened. By default, they are sorted by viewing date. To open an existing table, click on it once. A new file can be created by clicking the plus in the lower right corner.
From the "Google Drive" tables are opened by double clicking. To create a new file, click "Create" in the upper left corner or right-click the context menu. Here you can also start working with an empty table or select one of the Google templates.
There is no need to save the tables - everything is automatically saved on the “Google Drive” in the process. When finished, you can simply close the file - the data will not be lost. By default, files created through the Google Sheets service are saved to the root of the Disk. To move it to a folder, click on the icon next to the table name, and select a destination. Before moving the newly created table, it must be renamed.
You can also transfer files on Google Drive - using drag & drop or right-clicking on it and selecting "Move to ...".
How to open Excel file in "Google Spreadsheets"
Google Sheets supports Excel spreadsheets. You can open such a file in the service in two ways:
- Upload the file to "Google Drive" via the "Create" button and open it as a regular Google table.
- While in any table, go to the "File" menu and select "Open" or use the combination Ctrl + O. In the window that opens, go to the "Download" tab.
This also works in the opposite direction - Google Sheets files are downloaded to a computer in formats that support Excel: XLSX and CSV. To do this, go to the menu "File" → "Download as" and select the format. You can also save the table in ODT format to open in the Open Office program.
Complex tables with many formulas, graphs and charts may not open correctly when transferred to another format. After uploading the file to Google Sheets or downloading it to Excel, make sure everything is displayed and working correctly.
Google Sheets Toolbar
This service looks simpler in Google than in Excel. However, if you are used to working with MS Office, take some time to get used to.
Briefly go through the tools on the panel. We will talk about some of them in more detail later in the relevant sections.
As in Excel, the toolbar in Google Spreadsheets is divided into blocks:
- Undo and redo the last action, print the document, copy the formatting. The latter tool is useful when you need to apply the same formatting to multiple cells. Select a cell, click on the tool in the panel, and then click on the cell to be formatted.
- Zoom out. Makes the table larger or smaller, the range is from 50 to 200%.
- Change data format in cells - choose monetary or percentage, increase and decrease the number of decimal places in the numeric one, select other formats in the drop-down menu.
- Font size.
- Text formatting - typeface, text color and background.
- Cell Formatting - fill color, borders, union.
- Text Alignment - horizontal, vertical, shift and rotate settings.
- Other tools:
- add link;
- Add a comment;
- insert chart;
- create a filter;
- use functions.
- Sheet display and input direction.
- Input Method - Here you can turn on the on-screen keyboard and handwriting input field.
- Hide menu - by clicking on the arrow on the right, the main menu along with the table header, access settings and account icon is removed. Only the toolbar remains.
In the lower part there is a panel for switching between sheets and operations with them. To create a new page in the current file, click the "+" in the lower left corner. Clicking on the arrow next to the sheet title will open a menu where you can rename, delete, move, copy and hide the sheet, as well as protect it from editing and change the label color.
The label of the hidden sheet will not be displayed on the common panel. To view and return it, go to the menu "View" → "Hidden sheets" and find the desired one in the list.
Cell, row, and column operations
Table elements — rows, columns, and cells — can be added, removed, and hidden through the Edit menu or the context menu that opens by right-clicking on the selected item. For some actions, you need to choose what happens to the surrounding elements after deleting or inserting — where the data, rows and columns will move.
How to fix the line
When working with large data arrays it is useful to always keep in front of the headers of rows and columns. For this they can be fixed. Place the cursor in the cell of the desired row or column, open the menu "View" → "Pin" and select one of the options.
How to move table elements
Rows, columns and cells in the "Google Spreadsheets" can be swapped and moved across the entire sheet.
To move a row or column, select them by clicking on the header, open the "Edit" menu and select "Move row / column" to the left, right, up or down.
In addition, rows, columns and cells can be moved by dragging and dropping. To do this, move the cursor to the edge of the selected elements so that it looks like a “hand”, grab the element with the left click and drag to the right place without releasing the mouse button.
Change History in Google Spreadsheets
Not only the current version is automatically saved in Google Sheets, but also the entire history of changes. To open it, click on the "All changes saved on Disk" line to the right of the Main menu.
In the history mode, you cannot edit the table, but on the right you can select, view and, if desired, restore any of the previous versions.
It happens that you cannot restore the previous version - it is not your table and you did not edit it. If you click Restore, all changes made by another person after a certain point will be deleted. Working with the previous version in the history mode is also difficult - it is impossible even to select and copy data normally, let alone use filters and formulas. In this case, Google Sheets has the ability to copy an earlier version.
By clicking the program will create a copy of the table in the form in which you need.
One of the indisputable advantages of Google Spreadsheets over Excel is the ability to work together. To open your file to colleagues, click the blue "Access Settings" button in the upper right corner and specify the settings.
You can give the file access to individual users, those who have a link, or the entire Internet. Also there are different levels: "Editing", "Commenting", "Viewing".
Life hacking: if in the tables you refer to other sites and you do not want other people to see the contents of the file, do not open the access by reference. Owners of sites that you link to can get a link to the file from reports on referring sources, and open it.
How to delete and restore the table
To delete "Google Spreadsheet", open the "File" menu and select "Delete."
You can delete tables in the Google Drive interface and on the Google Sheets home page without opening them. In the first case, select the unnecessary file and click the basket icon on the top panel, in the second - open the file menu and find the item "Delete".
Working with data in Google Spreadsheets
How to edit Google Sheets
Data can be entered into the table from the keyboard, added via "copy" → "paste" and imported from different sources.
To enter data into a table, simply place the cursor on a cell and start typing from the keyboard. You can navigate the sheet using the mouse, arrows and hotkeys - more about them at the end of the article. To edit the data in a cell, double click on it, press Enter or place the cursor in the right place in the formula bar.
How to protect data from editing
You can prevent colleagues from editing data in the entire table, on separate sheets, ranges and even cells. The prohibition of editing the entire table is configured through access levels, and for cells and ranges there is a separate tool.
Call the right-click context menu and select the "Protect range" item or open the "Data" menu and click "Protected sheets and ranges". In the form that opens to the right, click "Add sheet or range". Enter a description. If you want to close the range from editing, click the table icon on the corresponding tab and select the desired cells on the sheet. To protect a whole sheet, go to another tab and select it in the list. Click "Set Permissions" and configure access.
You can register a limited list of users who have the right to edit the specified sheets and cells, or restrict themselves to a warning. In the latter case, when you try to edit a protected range, the program will display a warning message.
Comments and notes
In Google Spreadsheets, you can mark cells with comments and notes. Cells with notes are marked with a black triangle in the upper right corner, with a comment - yellow. The main difference is that other users can respond to a comment, but not to a comment.
Comments can be closed by clicking "The issue is resolved", a note - just delete it through the "Edit" menu, or by calling the context menu with a right click.
To add a note, select the desired cell, right-click and find the item "Insert Note". There is also a function "Insert comment", but through the icon on the toolbar will be faster.
Data - the text and numbers that you enter into the table can be displayed in different formats. There are several dozen of data presentation options, but all of them can be reduced to 7 main ones:
All other formats are derived from the main ones. Currency can be displayed with tenths or rounded to integers, replace "p." before numbers for another currency. The date is displayed as "2.12.2018" or "Sunday, December 2, 2018" - and in ten different variations. The same with other formats.
To change the data format in a cell, row, column or range, select it, open the "Format" → "Numbers" menu and select the desired one.
The “Numbers” section contains frequently used ones, the rest are hidden in the “Other formats” section.
Conditional data formatting
In Google Spreadsheets, you can change the appearance of text and cells — the font, size, typeface, font and fill color, alignment, border color and style, text wrapping rules. All the necessary tools are on the panel above the workspace, which we talked about above.
Sometimes it is necessary that the formatting of cells changes automatically depending on what data they contain. To do this, Google Tables uses conditional formatting rules. They can be set for the entire table, individual range, row, column, and even a single cell.
To set up rules, go to "Format" → "Conditional Formatting". A dialog box will appear on the right. Clicking on the "Add Rule" will open the settings. Depending on the task, you can customize the rule according to which the cells will be repainted in one color, or create a gradient. The rule for formatting cells in one color is configured in 3 steps:
- Specify the range.
- Set the formatting conditions.
- Adjust the format - color and text style, cell fill.
In total, Google Sheets has 18 formatting conditions for text data, numbers and dates. So, you can select cells that contain any data, certain words, numbers less than a specified value, etc.
To create more complex rules, you need to select the "Your formula" condition and use functions. This topic pulls into a separate large article, so we will not stop here. Let us consider using standard conditions as an example.
You are conducting contextual advertising and uploading indicators in tables for performance analysis. To calculate ineffective ads, you need to find lines with a CTR of less than 1%. Create a rule for the corresponding column, according to which cells with a value less than one will be highlighted in color.
The gradient is useful when you need, for example, to visualize the deviation of the data in the higher and lower side from the optimal value.
Filters and sorting
Tools for sorting, filtering, checking and grouping the contents of the table are located in the "Data" item of the main menu.
Sorting. At the top of the drop-down list are buttons for quickly sorting a sheet in direct or reverse alphabetical order by a column with a selected cell. Numeric values are sorted from lowest to highest and vice versa, respectively.
If there is a header row in the table, they will also shift during quick sorting. To save them in place, select "Sort Range" in the "Data" menu. It will become active when you select the desired range. By clicking a dialog box opens with a choice of column and sorting order - direct or reverse. To keep the title bar in place, put a checkmark next to "Data with a title bar."
Filters hide data from the table that is not needed right now. This is convenient when working with large arrays - to see publications on a separate site or type of content in a voluminous content plan, to analyze data on the achievement of one goal in an analytical report.
In the "Data" menu there are two tools for filtering the contents of the table: "Create a filter" and "Filters ..." If one user filters the contents of the table using the first tool, the others will also sort them. Therefore, if you need to hide part of the data only for yourself, so as not to interfere with your colleagues, select "Data" → "Filters ..." → "Create a new filter".
At the click, the filtering mode will turn on - all settings that you make will be saved. You can create several filtering modes, all of them will appear in the "Filters ..." drop-down menu. To make it easier to navigate, give everyone a clear name. The default names are "Filter 1", "Filter 2", and so on.
Life hacking: If you need to show the other user the data in the form in which you have filtered it using the filtering mode, simply turn it on and send the copied link.
In tables with access mode "View only" the function "Create filter" is inactive. To display the data as you need, select "Data" → "Filters ..." → "Create a new temporary filter".
The tool checks if the data in the cell matches the specified parameters. For example, only dates should be in one column. To check if there are any errors, select "Data Verification" in the "Data" menu and specify the settings:
- Range of cells → click on the field and select the desired column.
- Rules → select in the drop-down list "Date", then - "is a valid date"
- Click "Save".
Red labels will appear in the upper right corner of the cells with incorrect data. When you hover over them, a window appears with explanations.
If the data in the checked range is often edited, you can prevent errors for the future. Выберите в разделе "Для неверных данных" пункт "запрещать ввод данных", и когда кто-то попытается ввести в столбец что-то кроме допустимой даты, система выведет предупреждение и не даст отредактировать ячейку. По умолчанию сервис показывает стандартный текст предупреждения, но вы можете задать свой. Для этого поставьте в настройках галочку рядом с "Показывать текст справки для проверки данных" и введите свой вариант в поле.
Помимо соответствия данных формату даты можно задать десятки разных правил для числовых, текстовых и других данных. Например, вы можете убедиться, что числа в диапазоне меньше, больше либо равны определенному значению. As in the case of conditional formatting, the data verification function, in addition to the rules provided by the system, supports inputting its formulas.
Pivot tables are a popular data analysis tool. It is useful when it is necessary to structure and visualize large amounts of information so that it is more convenient to draw conclusions.
I will give an example. You are conducting end-to-end analytics for a beauty salon - meticulously fix all the data on orders up to the source of advertising from which the lead came. For the year it turns out a long table with thousands of rows. Objective: to find out which channel brought the most profit over the past year in order to correctly redistribute the advertising budget. With the help of pivot tables, we get a simple and visual report in just a couple of clicks.
To create such a report, select the source table, go to the "Data" menu and select "Pivot Table". The program will create a new blank sheet and open the editor on the right. In the line section, click "Add" and select a column with advertising channels. In the "Values" section, add a column with data on the profit for each order. The program will add up the profit for each channel and tabulate it. If in the "Columns" section you select the "Services" parameter, the table will show how much profit each channel has brought for each service. You can also filter data on any column from the source table, for example, hide data on a particular service from the pivot table. To do this, in the "Filters" section, you need to add the "Services" column, open the list and uncheck those services for which we do not want to take into account the data.
Charts and graphs in Google Spreadsheets
For data visualization, there are more functional and convenient tools - Google Data Studio, Power BI and others. However, it is sometimes useful to add a chart or graph directly to the table in order to visualize the data.
Consider the process of creating charts and setting up this tool on the example of a pivot table, which we did in the previous paragraph. Go to the desired tab, go to the "Insert" menu and select "Chart". By default, the program creates such a schedule. On the right, instead of the Pivot Table Editor, the Chart Editor will open.
The line "Total" hit the visual presentation - the amount of profit across all channels. Since its value is several times larger than for each channel separately, the remaining columns of the diagram in the background look too small - it is inconvenient to analyze and compare. Therefore, to begin with, we will remove this line from the graph by editing the range - click on the table icon in the "Data Range" row of the "Chart Editor" and select the desired one.
Let's see what else we can do with the diagram:
- Change type. "Google Spreadsheets "support graphs, columnar, linear, point, circular, tree, geographic, cascading, radar, and some other chart types. Explanations for which tasks specific types are better suited to go to Google Tables Help.
- Change Accumulation Type - standard or normalized. This option is useful when a single column of a chart displays data based on several criteria. It is not active for all chart types.
- Change the range of values.
- Add, remove and change axes and parameters.
The main manipulations with the chart are collected on the "Data" tab. On the "Advanced" tab, you can change the appearance of the graph:
- repaint columns, lines, segments in one or different colors;
- change the font, color, style of individual elements or the entire text;
- change the background of the diagram;
- edit the name of the chart and axes, etc.
There are many settings, but they are all intuitive.
Charts created in Google Sheets can be saved as an image and published on the site by embedding code in the page. To do this, open the drop-down menu in the upper right corner of the chart and select the corresponding items.
Work with functions
In Google Tables, as well as in Excel, there are functions — formulas that process the data in a table — they summarize, compare, check for compliance with conditions, and so on.
All functions are introduced on the same principle:
- Enter the "=" sign to make the program understand what we want from it.
- Then we start to enter the name of the function. The system will display a list of prompts from which you can choose the one you need.
- In parentheses, we indicate the data to be used for calculations, and enter additional arguments, if necessary.
Data for calculations can be entered manually from the keyboard or provide links to the cells and ranges from which they need to be taken. You can also enter the names of the named ranges, but you must first create them. This is useful when you often use the same data ranges in your calculations. To create a named range, go to the "Data" menu or right-click the context menu and select the appropriate item. The named range editor will open on the right. Click "+ Add Range" and enter a name. If you have not selected the necessary cells before, click on the table icon and do it, then click "Finish".
Consider the example of a simple function that is often used by SEO-Schniki and contextual advertising specialists - DLSTR - calculates the length of the string.
We will not describe each function in detail - there are about 400 of them. We just say that with the help of formulas you can do everything the same as in Excel. Of course, there are formulas out there that are not in the Google service, and vice versa, but there are not many of them. For example, there is no GOOGLETRANSLATE function that translates text from one language to another.
We have already written about the useful functions of MS Office spreadsheets for internet marketers, they all work fine on Google. The principle of operation of these functions is the same, but the syntax may differ. A list of all Google Sheets formulas with description and syntax is in the directory.
Integration with other Google tools
A distinct advantage of Google Spreadsheets for internet marketers is that they can interact and share data with other Google services. Let's look at an example of two products - Google Forms and Google Analytics.
Interaction with Google Forms
Data exchange between "Forms" and "Tables" does not even need to be additionally configured - this feature is available in the default services.
To create a new "Google Form" from the "Tables" interface, go to "Tools" → "Create Form". Clicking on a new tab opens the Google Forms editor. Create a simple form with three questions with a free answer and go back to the table. There appeared a new sheet, “Answers to form (1)”, in which 4 columns have already been created. Three of them correspond to the questions of the form - "FULL NAME", "Telephone" and "Address", the fourth - "Time stamp" - the system will enter the date and time of filling in the form into it. The first line is fixed so that when viewing a large number of responses the column headings are always visible. Everything, nothing else needs to be set up - when filling in the form, the answers will be saved in the table automatically.
If you have not created a table for answers in advance, you can still upload them to the “Table”. To do this, in the form editing mode, go to the "Answers" tab and click the Google Sheets icon in the upper right corner.
By clicking create a new table with the same name as the form.
This simple feature has a ton of ways to use in internet marketing. It is convenient to view the information that you collect through the forms, whether responses of job seekers for a job, a client for a project or a target audience for a product, in the “Tables”.
Google Analytics Integration
Data exchange with Google Analytics in Tables is implemented through an add-on. To connect it, open the "Add-ons" menu, select "Install Add-ons" and find it in the Analytics window that opens. If you do not see it on the first screen, it will be faster through the search, because there are a lot of addons.
Hover over GA and click on the "Free +" button that appears.
In the pop-up window, select the Google account that has access to the necessary projects in Analytics, and confirm the permission to access. After that, the “Analytics” item will appear in the “Add-ons” menu.
Let's try to upload data from GA to the table. Go to the “Add-ons” → “Google Analytics” → “Create new report”. The report editor window will open on the right. Fill in the report name (1), select the account (2), the resource (3) and the presentation (4), then the metrics (5) and the parameters (6) that we want to display in the report. Suppose we need to upload data on visits by pages and traffic sources to a table. Enter the metrics, parameters and click “Create report”.
By clicking in the table, a new “Report Configuration” sheet is automatically created with the report parameters. To create the report itself, go back to “Add-ons” → “Google Analytics” and click “Run reports”. The program will create a new sheet and upload the requested data.
You can work with the obtained data - sort, filter, process using formulas and display in pivot tables.
The next level of mastery is automated reports. You can first create a report in the "Tables" - set up columns and lines, set up formulas depending on the specific task, then set up automatic data upload. This process can be attributed to the advanced capabilities of the tables, so we will not describe it in detail here.
Useful add-ons for Google Sheets
In addition to integration with Google Analytics, the tables have other useful additions. We already wrote about some of them in the article "30 plug-ins for Google Docs, Spreadsheets and Presentations that will speed up your work." We also recommend paying attention to a few more addons:
- Remove Duplicates - finds cells with the same data.
- Sort by color - sorts the table by text or cell color.
- Crop sheet - deletes extra rows and columns, leaving the selected range or cells that have data.
- Power tools - a set of tools with which you can find duplicates, delete spaces, characters, empty columns and rows and perform other actions with the table and its contents.
- Template Gallery - ready-made table templates for different purposes: plans, financial reports, menus, etc.
- Supermetrics - helps to upload and merge data from 50+ sources in one table, including Google Analytics, Google Ads, Yandex.Metrica, Yandex.Direct and others.
All add-ons are installed in the same way: the "Add-ons" → "Install Add-ons" menu.
Shortcuts and hotkeys in Google Spreadsheets
If you learn and automate the use of at least a third of the hotkeys, the speed of work in the "Google Tables" can be increased 1.5 times.
Total service supports more than a hundred hot keys. Here are some of the most useful for Windows:
- Ctrl + space - select a column;
- Shift + space - select the line;
- Ctrl + Enter / D / R - fill the range / down / right;
- Ctrl + K - insert link;
- Home - go to the beginning of the line;
- Ctrl + Home - go to the top of the sheet;
- Ctrl + Backspace - go to active cell.
Also in the Tables work common for Windows and Mac combinations for standard actions - copy, paste, cut, print, find, etc. The full list of hot keys is in the help for "Tables".
So we reviewed the key features of Google Sheets.
Do you actively use Spreadsheets and know tricks and lifehacks that speed up and simplify work? It will be great if you share them in the comments and help others work more efficiently ;-)