Google Sheets is gradually becoming the main tool for conducting electronic calculations and creating tables, leaving Microsoft Excel behind. This is precisely due to the simple possibility of organizing shared access, free of charge and the absence of the need to download anything to your computer. The principle of interaction with this online service is similar to any program for working with electronic spreadsheets, but it has its own features, which we will talk about further.
Working with Google Sheets for dummies will be shown below, and the main functions of the service will also be analyzed. This will help you understand what advantages this tool has over desktop programs and whether it is worth paying attention to it in your case. However, before starting, I would like to highlight a few advantages:
Online work. This is the biggest advantage, because you can easily share links to tables, provide editing access, protect certain ranges, track tools and do everything to make teamwork with documents as comfortable as possible.
Access to unique features. Almost all of the features of Google Sheets have counterparts in Excel, but some are special and designed to interact with online tools with real-time updates, which we will talk about later.
Integration with other tools from Google. This will also be discussed in one of the sections below, but it is important to understand that such a feature allows you to combine several different services without any complicated settings, connect them and make work easier.
Cloud storage. You don’t need to download sheets and tables to your computer, everything will be stored on your cloud drive and available for editing or exporting as a file at any time.
In a word, Google Sheets is an extremely convenient online tool that replicates the functionality of programs for working with electronic spreadsheets and is able to replace them, providing access to unique features. Now let’s dwell in more detail on each of the main aspects of working with this service.
How to create a Google Sheet
If you’ve never worked with Google Spreadsheets before, the first task will be to open the site and create a blank workbook. Let’s figure out how to create a Google Sheet step by step. There is nothing complicated about this task, because you only need an account to authorize and synchronize documents. Go to the official Google Sheets website and click the button to go directly to the sheets.
If you don’t have your Google profile yet, the developers will suggest creating one, because without it you won’t be able to work with all the tools and save your changes in the cloud. To do this, click on the specially designated button on the page and follow the instructions from the developers.
After authorization, you are greeted by the main document management window, which already shows the created tables, templates and buttons for adding an empty file. You’ll even find a sorter to see when and which tables you’ve interacted with on that account.
You can create an empty book or read the full list of templates, which will also help to study the site’s functionality in more detail and learn how the developers approached the implementation of certain tasks, both technically and visually.
The community is now in Telegram
Subscribe and stay up to date with the latest IT news
Import files and data
Let’s immediately deal with the support of spreadsheets created in other programs and stored as files on your computer. On the create a new file page, you can click the folder button to go to upload an existing document. If you’ve already opened a blank Google Sheet, expand the menu “File” and select an item “Imports”. You can add XLSX and ODS files.
They will be downloaded with all functionality and visual formatting preserved, allowing you to start working with the spreadsheet as the creator intended. This is discussed in my other article, where, in addition to the method of importing the entire file, the possibility of adding only certain ranges from other documents created in Google Sheets is considered.
Read also: How to import data into Google Sheets
As for importing other data, the web tool allows you to add entire web pages, updating their content in real-time without having to track updates yourself. For this, a unique function is used, which is absent in desktop programs. Read more about it further.
The main elements of the toolbar
Now let’s figure out how to edit Google Sheets. This is done using the tools on the top panel. They almost exactly repeat those present in the same Excel and other text editors, but have their own characteristics. First, I would recommend looking at each clickable menu by clicking one by one on their names in the top bar. It is clear that by using the section “File” work with the document itself, creation, distribution and export is carried out. Pay the main attention to the tabs “Insert” and “Data”. There are buttons responsible for performing basic actions. With their help, charts, pictures, additional cells are inserted, data verification, cleaning and filtering are managed. This will be discussed in more detail below.
The panel under the drop-down menu is entirely reserved for buttons with visual operation. You can change the scale, choose new fonts, add semicolons, percentages, format the text, its color and insert links. All this is probably familiar to you if you have previously encountered Microsoft Excel or at least interacted with documents in one of the text editors.
We will not dwell on each button, as you can check their actions yourself in the test table or simply hover the cursor to read the description from the developers and understand what this or that tool is intended for.
Operations with cells
Google Sheets is primarily intended for calculations, bookkeeping, and other tasks related to math and data. The main work in this case takes place with cells that store data values. They are the main part of the document and it is necessary to use functions, further formation of a complete table and diagrams behind them. You can select any empty cell and create a formula on it by typing a sign = and the first function character after it. A menu will appear with the corresponding results. Most of the popular functions have their own description so that you understand the purpose and syntax.
Simple mathematical operations are often performed without additional functions. You only need to declare the action itself with a sign =, followed by the numbers of participating cells and the operation between them. It can be addition, multiplication, division, subtraction, work with percentages, determination of fractions and others.
Unfortunately, the volume of one general article is not enough to describe even the main functions of Google Sheets, as well as to tell about the syntax of each of them. Instead, I’d recommend going to Google’s support page to access help with all the features, a detailed description, and the ability to filter by various parameters.
The sections below will also provide instructions on how to use the popular features, so you can continue reading the guide to learn some of them and apply them to your own purposes.
View cell change history
Let’s consider this feature of Google Sheets. This will be a means of viewing the changes made, which is appreciated not only when editing a document alone, but also during joint work, to understand which of the users and when changed something in one specific or different cells. To do this, you can open the version history view menu or select a specific cell to track changes only in it.
More details: How to view cell change history in Google Sheets
This tool has only one drawback – it takes too long to process information in a table with a large amount of data. The version history window may even hang for a while. However, this shortcoming does not always manifest itself and is not critical. In most cases, the change tracker is extremely useful and effective.
Setting up shared access and email protection
Collaboration in Google Sheets was already mentioned, so let’s dwell on this tool in more detail. On the site, you can grant access to the table to other users. You choose yourself who can go to and view the document, comment on it or edit it. In this, access is granted to anyone with a link or only to a specific list of users created by specifying email addresses. Follow the link below for details on how to set up sharing for your situation. This article explains how to protect a sheet or range from editing when working together.
More details: Public access, protection of sheets and ranges in Google Sheets
Data sorting and filtering
Let’s consider the following useful function, which will be useful both for those who create a table in Google, and for those who only view existing documents. Sorting and filtering will allow you to display only the necessary values on the screen, removing the rest of the spreadsheet from view. If sorting is often done only alphabetically, then the filters can be different. There is even a separate setting for them, where each user himself sets the appropriate parameters.
More details: How to sort and filter data in Google Sheets
Creating a list
It will be logical to talk about the drop-down lists, since they also act as a kind of table optimizer, allowing to display only what is needed right now. Such lists are correctly placed under formulas that dynamically change depending on the selected value. They can also be used in summary tables. Creating such a list is not difficult, you only need to configure data validation and set the required number of values.
More details: How to create a dropdown list in Google Sheets
Full table formation
Above, you’ve learned about a few different features and settings that Google Sheets provides. Learning does not end and often the user is interested in creating a complete table with all visual designs. You already know about the toolbar, you can interact with cells, create lists and check data. Now it all depends directly on what type of table you want to get (I have a separate article that talks about creating a dynamic calendar).
It talks about links between different sheets, conditional formatting, and various functions. You can use this guide to learn something new about the capabilities of Google Sheets and to gain some additional knowledge that will help you when you work with other types of sheets in the future.
Creating a chart
If you already have a ready-made table, you can create a chart that will visually show the results, proportions or the number of values in the cells, which will greatly simplify the perception of the content. Let’s take a look at how to create the simplest chart with the right settings for you.
First, expand the menu “Insert” and select an item in it “Chart”.
It will be automatically inserted according to which table you have created. If there are several of them on the sheet, first highlight the required range and only then create a chart. You can change its type by expanding the list with all available options.
Below are options for changing the range and values, which will help you optimize the displayed elements if you accidentally grabbed extra ones or noticed them missing.
The chart itself is displayed as a separate table block. You can pinch it with the left mouse button to move it to any convenient position, and use the edge points for scaling.
Open the tab “Additional” changes in the appearance of the chart. Set its background, border color and font.
A little below, you can expand the block with the name, set a new title, text color and other parameters.
Setting up the chart depends directly on what your goal is. Maybe you need to show statistics or visual calculation results. This also applies to pivot tables.
I will note that the developers use the technology of intuitive data analysis, which includes the automatic creation of various auxiliary graphs based on the table itself. Read more about this in another article, if you are interested in the automatic formation of such graphs.
Freeze columns and rows
I want to tell you about a small function that allows you to freeze a row or columns so that they always remain in a prominent place when scrolling the table. This is necessary for those users who are engaged in reading data in a large document and do not want to ignore the content so as not to get lost in the names of rows and columns. You just need to open the menu “View” and choose the appropriate fastening option for you. You will find step-by-step instructions with screenshots in the article.
Working with links
Creating a link from text in Google Sheets is one of the simplest tasks that can be performed by clicking one button located on the already mentioned panel with the main tools. Therefore, I suggest immediately moving on to a more complex process – combining several links. For example, you have one main domain and several pages that you need to redirect to. A separate function allows you to concatenate addresses, creating a single line to jump to the page. This formula can then be stretched across the entire table, spending a minimal amount of time linking to multiple pages with the same primary domain.
More details: Merging links in Google Sheets
Remove empty columns, gaps and duplicates
When working on a spreadsheet, there will probably be a situation where a large number of rows will be displayed on the screen or there will be unnecessary duplicates, including spaces, among the values. Their removal will allow you not only to normalize the appearance of the document, but also to speed up its loading. Rows and columns are deleted through the context menu, and more details about this are written in another article.
In it, you will find information on how to get rid of empty lines and unnecessary gaps using filtering. This method of filtering and cleaning data is also suitable for finding unnecessary duplicates, so you can safely use it by simply replacing the range of displayed values.
Converting and displaying currency rates
We continue to understand how to work in Google Sheets on a computer. The next topic I would like to talk about is suitable for all those who perform various financial transactions in this online service and keep reports. Simple functions can be used to load the current exchange rates and convert them to the specified, which requires creating a small formula.
More details: How to convert currency in Google Sheets
Things are a little more complicated with the conclusion of the current rate, a certain cryptocurrency token. To do this, first you need to choose a site that updates quotes in real time, then find the value through the element code on the site and import it as XML. The data will automatically update every few minutes with changing values in the calculation functions, if any are used in the table.
Read also: How to import the current rate of cryptocurrency in Google Sheets
Verification of e-mail addresses
I suggest to move away from the topic of finances and numbers and figure out what other purposes Google Sheets can be used for, because it is not only a tool for mathematical calculations. With the help of such documents, you can collect statistics, keep records of data and check their correctness. In particular, this also applies to e-mail addresses, for which the site offers a special function that will automatically check the validity of the specified e-mail.
More details: How to check valid email addresses in Google Sheets
Translation of the text
Another means of working with text data is translation into other languages. For this, Google offers to use its own function integrated with the company translator. You just need to choose which language to translate from, using the code designations for each. In general, the formula is not complicated and somehow specific. Read about how to interact with it in another article.
Creating a macro with a button
Beginners may think that working with macros and various commands in Google Sheets is as complicated as in the same Excel, but it is not. Recording actions and their subsequent automatic execution is not something difficult in terms of implementation. In addition, the created macro can even be assigned to a visual button, so as not to constantly call up the menu to launch it.
Macros can be absolutely any, because the sequence of actions performed depends only on you. In the article below, I talk about one example that can be modified and changed in every way to suit yourself, changing even the button itself, through which the macro is launched.
More details: How to create a macro in Google Sheets and assign a button to it
Integration with other Google services
You’ve already learned that simple functions call other Google tools for currency conversion and text translation. However, this is not all that can be done in Google Sheets. Integration with other popular services is available, including Forms. You can collect data about users through them, then automatically display them in a table, form summaries or graphs according to the provided answers. The setting in this case is carried out using a specially designated import tool.
More details: Creating a Google Form with the output of results in a Google Sheet
Next come tasks and reminders, which can also be part of a spreadsheet if you’re using it as a diary or calendar. Simply link your Google account to your calendar or notes to add as many entries as you want. You will receive notifications and you can always go to view the saved entries through the buttons on the sidebar, which are responsible for opening these add-ons.
More details: Tasks and reminders in Google Sheets
In this article, the main and most interesting functions of Google Sheets were considered. Good luck!