10 Google Sheets Tips That Will Make Your Life Easier

Performance Marketing - 18 November 2022

Performance Marketing

Performance Marketing

Google Sheets (formerly Spreadsheets), a fully integrated product of Google, is used today in many fields and for many tasks. The widespread use of this tool is due to the usefulness it offers to its users, its compatibility with other tools and programs, and its many functions. In this article, we have given tips on how to use the Google Sheets tool more effectively and efficiently.

What Is Google Sheets?

Google Sheets is undoubtedly one of the most important programs that make our lives easier, especially if you work in a company that uses Google tools or in an industry where you are constantly interested in dynamic data. Sheets, which is also compatible with Microsoft Excel, is one of the most important parts of Google Docs. It is a type of Spreadsheet with numerous services such as editing complex data, and the possibility of common use thanks to its online-based nature.

History of Google Sheets

Google Sheets is from XL2Web, a web-based spreadsheet application developed by 2Web Technologies, founded by Jonathan Rochelle and Farzad "Fuzzy" Khosrowshahi. XL2Web was acquired by Google in 2006 and turned into Google Labs Spreadsheets. On June 6, 2006, a test version was released for a limited number of users on a first come, first served basis. The limited testing was later replaced with a beta version that was made available to all Google Account holders at about the same time as the release of an official announcement press release.

In March 2010, Google acquired the online document collaboration company DocVerse. DocVerse allowed multi-user online collaboration on Excel-compatible documents as well as other Microsoft Office formats such as Microsoft Word and Microsoft PowerPoint. Improvements based on DocVerse were announced and deployed in April 2010. In June 2012, Google acquired Quickoffice, a free proprietary productivity suite for mobile devices. In October 2012, Google Spreadsheets was renamed Google Sheets.

If that much general culture is enough, let's get to the tips.

10 Google Sheets Tips

Google Sheets, which allows performing many operations from simple to complex, has many different functions. While some of these functions are very often used and widely known, some very useful functions are not known to everyone. These tips will come in handy to help you use Google Sheets the way it works best for you.

1. Dynamic Reports

Undoubtedly, the biggest convenience and the reason why Google Sheets is preferred is that it provides multi-use at the same time thanks to its online-based nature, and dynamic reports and dashboards can be created with internet-connected data sources. Google Sheets is for you, especially if it is of great importance to constantly monitor metrics, such as digital marketing, hourly, maybe minute by minute.

If what you've read so far hasn't lit a light bulb in your mind, let's think together. If you have 3 different constantly updated data sources in your hand, if it is important for you to be able to follow these sources instantly and you have to keep track of these files that you have downloaded in different formats on your computer, you can combine these sources in one file thanks to Google Sheets and access their sources with the right methods (more on this one by one in a moment). You can get self-updated reports that you can follow forever, at once.

2. Add-ons

Add-ons... In their simplest form, they are user-created scripts that run within Google Sheets, Docs, Slides, and Forms. With these mini scripts, you can get some data you want from free APIs, you can easily do many tasks that you have difficulty doing manually with one click, and you can set up an automation that will rock developers without requiring any coding knowledge.

In order to access the add-ons, you can click on the Extensions menu in the top menu and then the Get add-ons section, and select the add-on that suits your needs.

Caution: Installing unreliable add-ons can be dangerous for the security of your data.

3. importrange, importxml, importhtml

Importrange, Importhtml, Importxml, and more. As their names suggest, these three functions and similar ones allow us to transfer any data we need to Sheet files, stock market data, prices on shopping sites, data in another file, and countless data you can think of. In this article, I will not explain the uses of these functions, but if you want, you can review them from the links below and perform your data import operations according to your needs.

• Importrange
• Importxml
• Importhtml

4. Apps Scripts

Apps scripts is a service where you can make all Google-based programs talk to each other, set up unlimited automation, create your own add-ons, and create functions with a little JS knowledge.

Again, Apps Scripts, which you can access from the same section with add-ons, reveal a world where we can handle the work that we have been dealing with for hours, maybe in a few seconds.

If you are interested, you can start developing excellent projects by reading Google's documentation right away.

A little tip: Google apps scripts have a limited run time of 6 minutes, with paid accounts it can go up to 30 minutes. You should pay attention to the working time and efficiency of the projects you create.

5. Co-editing

We can say that the most important feature of Google Sheets, which distinguishes it from Microsoft Excel, is co-editing. You can edit the fields that different employees are responsible for on different pages of a table at the same time, inform your teammates with the comments you add, and even solve your instant communication problem from the chat area offered by Google while in the file.

In addition, you can control who viewed the file you shared from the Document History section and how often, and you can set deadlines accordingly.

6. Google Tools

One of the biggest benefits of Google's providing alternatives to meet almost all of the most used programs in business life is to be able to make these applications talk to each other and automate many processes easily. For example, let's say you update data from the same tables to the same presentations every week. Thanks to the Google Sheets and Google Slide connection, you can automatically update your presentation when you just update the table, by connecting the tables you need to the appropriate Slide tables and get rid of extra workloads.

Or, you can send the data sheets that you regularly mail to as often as you want by connecting to Google Sheets - Gmail without having to deal with it over and over again every day.

In short, you can have all Google's tools talk to each other, facilitate the processes you deal with from place to place, and use your time much better.

7. Vlookup

Vlookup is perhaps one of the most important Google Sheets functions that allow us to match data in different rows in two different data tables. For example, you have user_id data and session metric in one table, and label information corresponding to these ids in the other table and you want to match them. For a file with thousands of rows, it may take days to do this manually! With vlookup it will take 15-20 seconds.

=VLOOKUP(search_key, range, index, [is_sorted])

For more detailed information: Vlookup

8. Query

Let's come to the most crucial point of the article and my clear favorite function: Query. With this function, with a syntax similar to the SQL language to organize and report your datasets, you can do things that are really complex and much easier than you can do using the & feature of Sheets 4-5.

Syntax

=QUERY(data, query, [headers])

As you can see, let's write a small example formula of how we can use this function with only 2 mandatory parameters.

Let's say the operation we want to do is as follows: By examining columns A, B, C on the "Data" page, we want to select the numbers in column A that are greater than 10 and also the cities in column C that are "Ankara", and the results of this query are larger than column B. Let's sort them in descending order.

=QUERY(Data!A:C,"SELECT A,B,C WHERE A>10 AND C<>'Ankara' ORDER BY B DESC",1)

9. IFS

If, sumif, countif, maxifs, minifs, dateif, iferror... As it can be understood from the if pattern, functions with ifs allow us to perform operations on most conditional situations. At the same time, it is very important to know what we need to be a good Sheet user and to practice using it. . That's why we need to know what functions with ifs are and how they're used.

Let's look at a small if example together: We want to distribute letter grades such as A,B,C,D according to the scales of grades received by students in a class, then simply:

=IFS(A3>89.99,"A",A3>79.99,"B",A3>69.99,"C",A3>59.99,"D",A3>0,"F")

Or, if we want to aggregate sessions in column B where the sources in column A are "google":

=SUMIF(A:A,"google",B:B)

For more detailed information, you can review the contents in the links below.

• Sumif
• If
• Countif
• Maxifs
• Minifs

10. Macros

Finally, Macros, one of the most important features of Sheets, which we can define as a lifesaver, especially for users who are tired of repeating the same manual operations every day.

Macros, which can also be accessed from the extensions section, is an application area where you can repeat the UI operations you will save as often as you want, and it works based on Scripts.

In fact, to summarize simply, it allows you to perform operations such as copy-paste and insert rows that you do every day in the format you specify, and has numerous uses.

From Google's developer document, you can examine the usage areas, record your macro according to your own needs, and automate the processes you spend hours every day in a few seconds.

You can access the Macros document here.

In addition, from the Google function list page, you can review all functions, find the ones you need, and expand their usage areas.