The Ultimate Spreadsheet Guide by RadASO

App Store Optimization
Oct 19, 2022

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.

Join the open ASO & User Acquisition community on Discord - ASO Busters! Here, engage in insider discussions, share insights, and collaborate with ASO and UA experts. Our channels cover the App Store, Google Play, visual ASO, ASA, UAC, Facebook, and TikTok.

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.

Attribute
Value
Format
PNG
Color space
Display P3 (wide-gamut color), sRGB (color), or Gray Gamma 2.2 (grayscale).
See Color Management.
Layers
Flattened with no transparency
Resolution
Shape
Square with no rounded corners
Attribute
Value
Format
PNG
Color space
Display P3 (wide-gamut color), sRGB (color), or Gray Gamma 2.2 (grayscale).
See Color Management.
Layers
Flattened with no transparency
Resolution
Shape
Square with no rounded corners
Word
Frequency in service
Qimai(China)*
AppTweak
Mobileaction
Appfollow
课程 (course)
5387 (high)
56 (mid)
5 (low)
34 (mid)
外语 (foreign language)
5699 (high)
35 (mid)
5 (low)
34(mid)
播放 (play)
5496 (high)
35 (mid)
5 (low)
35 (mid)
*Search index in China provided by Qimai and based on multidimensional calculations such as search results, downloads and relatable keywords.
App Store
Google Play
1. Icon impact on user choice
Important, but not more than a video with screenshots, as they take up most of the screen
Icon is displayed with several other icons in search results, and therefore it has the greatest impact on the user's choice
2. Icon localization
1 icon for all locales
Can be localized for each country
3. Screenshots quantity, orientation
From 1 to 10, any orientation
From 2 to 8, any orientation
4. Screenshots the most important are the ones that are visible before you start scrolling
  • Vertical screenshots: 3 are visible
  • Horizontal screenshots: only the first one is visible
  • Vertical screenshots: 4 are visible
  • Horizontal screenshots: only the first one is visible
5. Screenshots size
Large — the content is legible
Much smaller — the captions are quite illegible
6. Video quantity
Up to 3
One video that must be uploaded to YouTube
7. Video orientation
Any orientation
Horizontal orientation prioritized
8. Video autoplay
Plays automatically for 30 seconds without sound
You need to click on it to play
9. Application cover
Displayed on the application and developer pages
There is no cover
10. Graphics update
After release only
Whenever

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:

Device or context
Icon size
iPhone
60x60 pt (180x180 px @3x)
60x60 pt (120x120 px @2x)
iPad Pro
83.5x83.5 pt (167x167 px @2x)
iPad, iPad mini
76x76 pt (152x152 px @2x)
App Store
1024x1024 pt (1024x1024 px @1x)
Source language
Target language
How much longer (+) or shorter (–) the text in the target language is
English
French
21.18%
English
Spanish
19.52%
English
Italian
17.91%
English
Deutsch
16.67%
English
Dutch
13.80%
English
Portuguese (Portugal)
14.29%
English
Portuguese (Brazil)
12.96%
English
Polish
9.33%
English
Russian
9.11%
English
Czech
3.70%
English
Arab
–6.25%
English
Japanese
–39.68%
English
Korean
–44.04%
English
Chinese (Simplified)
–61.97%
English
Chinese (Traditional)
–63.80%
*Search Ads Popularity (SAP)shows the popularity of the search term from 5 to 99.

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.

Device
Spotlight icon size
Settings icon size
Notification icon size
iPhone
40x40 pt (120x120 px @3x)
29x29 pt (87x87 px @3x)
38x38 pt (114x114 px @3x)
40x40 pt (80x80 px @2x)
29x29 pt (58x58 px @2x)
38x38 pt (76x76 px @2x)
iPad Pro, iPad, iPad mini
40x40 pt (80x80 px @2x)
29x29 pt (58x58 px @2x)
38x38 pt (76x76 px @2x)
Search term
Translation
SAP* English (U.S.)
SAP* English (U.K.)
truck games
games with trucks
62
39
lorry games
games with trucks
32
jail games
prison games
29
8
prison games
prison games
32
24
*Search Ads Popularity (SAP)shows the popularity of the search term from 5 to 99.
App Store
Google Play
What can be tested?
  • Short description
  • Long description
  • Icon
  • Feature graphics
  • Screenshots
  • Videos
  • Screenshots
  • Videos
  • Icon (has to be uploaded to the build*)
Number of simultaneously running tests
5 tests (each test is valid within a single country.

You can choose a default country test (details below): then it will run in all countries where there are no localized graphical or textual materials.
1 test (the test can be immediately extended to all countries where the application is available or opt for specific countries as needed)
The number of test variants that can be tested with the current version in the store
Compared with a maximum of 3 new variants
Can a test be launched while another item is under review?
Yes
No
Mandatory formats for screenshots uploaded to the store
6.5
  • 6.5
  • 5.5
  • 12.9 (if there is an iPad version)
*Build – is a new version of the application. Updating the icon is only possible when updating the application version in the store. In other words, the term "build" refers to a specific version or variant of the application that is ready to be downloaded and installed on the users' devices. It contains all the necessary files and data for users to install and use the application.
More about optimizing graphic elements in the App Store and Google Play can be found in the article 'Graphics in Mobile App Promotion in the App Store and Google Play (ASO) – How to Optimize Graphic Elements.'

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.

Device size or platform
Screenshot size
Requirement
Screenshot source
6.5 inch (iPhone 13 Pro Max, iPhone 12 Pro Max, iPhone 11 Pro Max, iPhone 11, iPhone XS Max, iPhone XR)
1284 x 2778 pixels (portrait)2778 x 1284 pixels (landscape)1242 x 2688 pixels (portrait)2688 x 1242 pixels (landscape)
Required if app runs on iPhone
Upload 6.5-inch screenshots
5.8 inch (iPhone 13 Pro, iPhone 13, iPhone 13 mini, iPhone 12 Pro, iPhone 12, iPhone 12 mini, iPhone 11 Pro, iPhone XS, iPhone X)
1170 x 2532 pixels (portrait)2532 x 1170 pixels (landscape)1125 x 2436 pixels (portrait)2436 x 1125 pixels (landscape)1080 x 2340 (portrait)2340 x 1080 (landscape)
Required if app runs on iPhone and 6.5 inch screenshots are not provided
Default: scaled 6.5-inch screenshotsAlternative: upload 5.8-inch screenshots
5.5 inch (iPhone 8 Plus, iPhone 7 Plus, iPhone 6s Plus)
1242 x 2208 pixels (portrait)2208 x 1242 pixels (landscape)
Required if app runs on iPhone
Upload 5.5-inch screenshots
5.5 inch (iPhone 8 Plus, iPhone 7 Plus, iPhone 6s Plus)
2048 x 2732 pixels (portrait)2732 x 2048 pixels (landscape)
Required if app runs on iPad
Upload 12.9-inch iPad Pro (3rd generation) screenshots
5.5 inch (iPhone 8 Plus, iPhone 7 Plus, iPhone 6s Plus)
2048 x 2732 pixels (portrait)2732 x 2048 pixels (landscape)
Required if app runs on iPad
Upload 12.9-inch iPad Pro (2nd generation) screenshots
Price
Traffic Source (Self Reporting Networks)
Facebook
Google
X (Twitter)
Apple Search Ads
Cohort reports
Impression tracking
Audience segmentation
At extra charge
Custom Dashboards
Custom Reports
At extra charge
Advertisement Cost
DAU/MAU (Stickiness)
Raw Data Export
At extra charge
At extra charge
At extra charge
API Reporting
Search term
Translation
SAP* French
SAP* French (Canada)
soldes
sale
25
5
aubainerie
sale
14
*Search Ads Popularity (SAP)shows the popularity of the search term from 5 to 99.
Device
Icon size
iPhone
180px × 180px (60pt × 60pt @3x)
120px × 120px (60pt × 60pt @2x)
iPad Pro
167px × 167px (83.5pt × 83.5pt @2x)
iPad, iPad mini
152px × 152px (76pt × 76pt @2x)
App Store
1024px × 1024px (1024pt × 1024pt @1x)
Icon format
PNG
Color models
sRGB or P3 (see "Color Management")
Layouts
Aligned without transparency
Sizes
Shape
Square without rounded corners
All the current requirements for icons are specified in the specification.
Icon format
32-bit PNG (with alpha channel)
Color models
512 х 512 pixels
Layouts
1024 КБ
Sizes
square, without rounding and shadows (Google automatically rounds the corners and adds shadows)
All the current requirements are specified in the specification.

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.

Home
Up