The Ultimate Spreadsheet Guide by RadASO

Our experts at RadASO use Google Sheets every single day. It's a tool that can help you work faster, more efficiently, and structure information better.

We love tables so much that we use them outside of work, too! A table can help you find and compare different accommodation options in any city you like, build a personal investment portfolio or even create a list of movies worth watching.

To make working with spreadsheets easier, we compiled a set of rules and standards for Google Sheets. Initially, we wanted to create a complete manual for internal use. But it didn't take us long to realize that a manual could be useful to everyone, from an ASO specialist to an accountant or business owner.

Google Sheets is a useful tool for any marketer because it allows you to structure and organize information. We are convinced that structured information presented in an uncluttered way is easier to understand.

In this article, we will talk about the basics of Google Sheets, how to start working on each document, which plugins are a must for ASO specialists, as well as the most popular formulas we use (which will make your life a lot easier). Let's begin!

Steps we take before we start working with Google Docs

#1 Document title

Let's start with the most important thing: the title.

The document must have a title so that you and your colleagues can easily find it, even years after its creation. A good title will allow you to locate the document whenever you need it.

The title should clearly describe the contents of the document. You probably won't be able to recall what the document titled «Tables123» contains, but a title like «Guide article on working with Google Sheets: basic rules, features, plugins, life hacks | RadASO» will definitely help you remember. Be specific!

By getting into the habit of giving your documents descriptive titles, you accomplish two goals at once: first, you show your professionalism, and second, you can easily find any document by doing a simple keyword search.

#2 A uniform font

It's advisable to use the same font in all the cells. In fact, if you can stick to the same font for all your documents, go for it! For example, we at RadASO have one main font that we use in all our documents: Montserrat.

#3 Text styles

We often work with large amounts of information. By using one formatting style with basic text styles such as Heading and Subheading, you solve two problems at once. All subheadings have the same consistent style throughout the document, and they make jumping to specific paragraphs quick and easy.

Plus, you can conveniently navigate to the selected heading or subheading using the left side panel.

Working with Google Sheets

Google Sheets is appreciated by scores of internet marketers worldwide, as its simple tables can save you hours of searching for information. If you know how to use Google Sheets, you can find what you need in no time.

Next, I will tell you about the main Google Sheets work features that our specialists like to use. We start with the three steps mentioned in the previous section and build on them with the following steps.

#4 Tabulating information

Remember: text should be left-aligned, and numbers and amounts should be right-aligned. Aligning this way looks good and makes the data easier to comprehend.

#5 Freezing certain columns and rows

If your Google Sheet has too many columns and rows, you may need to scroll a long time to find the row you're looking for. And by the time you get there, you've probably already forgotten what information each column actually contains. That can get annoying. Avoid this by using the «Freeze» function and fixing the important parts of the table.

There are two ways of doing this:

• You can drag the area you want to freeze to the desired anchor point, as shown in the screenshots below.

• Or, you can use the context menu.

Check out our table for more information.

#6 Filters

Let's say you have loads of information in front of you, and you need to find all the entries with a value above 20 in the SAP column (see image below). You could manually sort through the data and highlight each corresponding entry with a different color or font. Not only would this make a rainbow out of your table, but it would also take considerable time. Filters can do all the heavy lifting. Find what you're looking for via the «Data» menu or the «Filter» icon in the work bar.

#7 Colors in tables

Do you know what it feels like when your eyes hurt?

If you want to find out, imagine these colors in the cells of the table you are working with.

Now, compare them to these. Much better, right? If you want to keep a stable vision and good mental health, we recommend you only use pastel colors in a document.

#8 Link to a particular cell

If you need to provide a link, not to the entire document, but a specific column, row, or cell, right-click the context menu and look for the following option:

#9 Pivot tables

A pivot table is a powerful tool for analyzing large datasets. But using the full functionality of pivot tables is a rather complicated task. And for our line of work, it is not necessary. However, this Google Sheets tool can be useful when creating a report or when you need to summarize some key metrics to facilitate further analysis or hypotheses. Let's say we have a table with the percentage of iOS and Android users by country, each belonging to a certain region. Suppose we need to find out the average indicators by region. This is an ideal task for a pivot table:

Google Sheet Formulas/Functions

Since the work of almost any internet marketer, including an ASO specialist, involves numbers, we recommend using at least basic Google Sheets formulas that make the work process much easier.

Below you'll find the formulas regularly used by our specialists at RadASO.

#10 VLOOKUP function

VLOOKUP allows you to find the value of a particular cell in the required row and column. VLOOKUP will find the required row, and you need to specify the column.

Suppose we have a table with search terms (first column) and apps' rankings relative to the search terms (second column). Your table may look something like this:

VLOOKUP allows you to find a position by indicating a specific query.

The function is in cell C9, and it takes the following values into account:

• B9 – search text;
• A2:B6 – search range;
• 2 – sequence column number from which you need to take the value corresponding to the row with the search text;
• false – indicates whether the column is sorted for text search.

Judging by our example, this function may seem completely useless. You can find the desired value by simply looking at the table, right? Well, that’s only true when the table has five rows. Now imagine a table with 100 rows. What about a table with 1,000 rows? VLOOKUP can make your work process a lot easier.

#11 LEN function

All ASO specialists know that the title and subtitle of an app in the App Store cannot exceed 30 characters.

You can use the LEN formula to control the number of characters in a cell:

Using the Title value example, the formula is in cell B4 and has only one component – the cell with the text whose length we want to determine.

#12 SUMIF function

Let's assume we have a table with three columns: a search term, its SAP and the number of downloads. We need to add up the number of downloads for all the popular search queries (with a SAP value of 6 or higher). That's very easy to do with the SUMIF function:

The formula is in cell B8 and takes into account the following components:

• B2:B6 – the data range to which the criterion will apply;
• ">6" – criterion for determining which rows to summarize;
• C2:C6 – the range of summarized values.

#13 TRANSPOSE function

Imagine you've just spent hours creating the perfect table and populating it with data, only to realize that you need to flip the columns with the rows. You could start from scratch and remake the whole table, but that would take a long time. Alternatively, the TRANSPOSE function can fix the table in just a few clicks. The only element included in this function is the range to be transposed.

Here is an example:

Our formula is in cell A7 and covers the entire range from A7 to C12.

#14 COUNTIF function

This function counts the number of cells that match the given condition and are in the specified range.

Suppose you have a table with many rows and two columns – «Search Term» and «SAP». You need to count how many search queries have a SAP value greater than or equal to 30. To do this, you need the COUNTIF function.

The function accepts two arguments: the data range and the counting criterion. Let's analyze its use in the following example:

The formula is in cell B9, the data range is B2:B6 and the criterion is «=30». The result is the number of search terms that have a SAP of 30 or above.

#15 SPARKLINE function

Let's consider a case where you have a table with a large number of rows and columns. For example, it's a spreadsheet with SAP values for different keywords over a certain number of days. You want to see the progress of SAP for each row (i.e., for each keyword) week by week, but you don't want to create many individual charts. Sparklines are ideal for this task; they are handy mini-charts.

You only need to specify two pieces of the equation: data and parameters (options). Data are what the chart is based on, and parameters (options) are a range or array containing data about the various parameters of the sparkline.

Let's take a look at an example:

Our formula is in cell G3, where the <<remind>> query is the data, and B3:F3 are the cell parameters.

If you want to apply this formula to each key query in the table, you don't need to write a separate formula every time. Simply drag the arrow along (as shown below), and the formula will automatically be applied to all cells.

Plugins/Life Hacks

There are various ways to optimize your experience with Google Docs Editors and Google Sheets in particular. Some helpful Google Sheets tips are built into the default document editors, while others require plugins (aka extensions) that you can add. Below is a description of a few that our team uses.

#16 Numeric cell format for numeric data

If you are working with large numbers, you should enter them without spaces. Otherwise, the algorithm will treat them as text, and you won't be able to add up individual numbers.

To make reading large numbers easy, select the number format for the cell in the quick bar.

#17 Row Grouping in a Google Table

If you need to create a table with a large number of rows, you can group them according to certain criteria and shrink unnecessary rows.

To do this, just select the necessary rows, right-click, and select «group rows».

#18 Google Spreadsheets shortcuts

Using hotkeys significantly speeds up work in many applications, and Google Docs Editors is no exception. The complete list of combinations is quite extensive, but here are some of the most useful ones:

#19 Double-click life hack

Many people don't know about this simple life hack: double-clicking on the left edge of a column automatically sets it to the ideal width, depending on the cell with the longest value in this column. This is more convenient than manually adjusting the width of each column.

#20 Crop Sheet plugin

Our RadASO team has a simple rule: we always delete all empty columns and rows to the right and at the bottom of the sheet. The document should be compact and convenient, and not contain any blank spaces.

For this, we use an awesome plugin — Crop Sheet.

Alternatively, you can use the manual method of deleting empty cells:

ctrl + shift + ➡️ – select all columns to the right; ctrl + minus – delete.

Do the same if you need to select the cells at the bottom of the document.

#21 DocSecrets plugin

Use the DocSecrets plugin to hide parts of the document from other users.It's very easy to use: set a password in the sidebar that will be displayed after the plugin is activated. Then, in the «Insert» field, enter the text you want to add to the document in hidden form.

#22 Free icons, photos & illustrations plugins

Photos, icons, and illustrations can be very useful in documents. For instance, you can add illustrations to explain a difficult concept or replace country names with flags in a table to save space.

When people need to implement visuals in their documents, they usually use Google Image Search, download the images they need, and paste them into the document. There's nothing wrong with that if you only use one or two images.

But what if you need to add multiple images of the same size without disrupting the document's formatting and visual harmony?

Well, you can use the Free icons, photos & illustrations plugin. It's a free library of cool graphics that you can use to insert icons, illustrations, and photos directly into your documents. This works with different types of Google Docs, including Google Sheets.

To summarize, Google Docs Editors contains incredibly powerful tools that can make your life as an ASO specialist much easier. In this article, we've shared some Google Sheets tips and tricks that we use at RadASO.

Let me remind you that our agency RadASO works in the field of App Store Optimization (ASO) and Apple Search Ads (ASA). We provide analysis and develop solutions for mobile application advertising.

We stay up to date with trends in the world of ASO, conduct relevant experiments, and share our expertise through case studies. Read more about us in our Netpeak Journal.

Text localization: Kateryna Kalnova, RadASO.