Google Sheets Custom Number Format

Introduction

Google Sheets custom number format rules are used to specify special formatting rules for numbers.

These custom rules control how numbers are displayed in your Sheet, without changing the number itself. They’re a visual layer added on top of the number. It’s a powerful technique to use because you can combine visual effects without altering your data.

Sheets already has many built-in formats (e.g. accounting, scientific, currency etc.) but you may want to go beyond and create a unique format for your situation.

Google Sheets Custom Number Format Usage

Access custom number formats through the menu:

Format > Number > More Formats > Custom number format

Google Sheets Custom Number Format Menu

The custom number format editor window looks like this:

Google Sheets Custom Number Format Editor

You type your rule in the top box and hit “Apply” to apply it to the cell or range you highlighted.

Under the input box you’ll see a preview of what the rule will do. It gives you a useful and pretty accurate indication of what your numbers will look like with this rule applied.

Previous rules are shown under the preview pane. You can click to restore and reuse any of these.

Google Sheets Custom Number Format Structure

You have four “rules” to play with, which are entered in the following order and separated by a semi-colon:

  1. Format for positive numbers
  2. Format for negative numbers
  3. Format for zeros
  4. Format for text

Google Sheets Custom Number Format Structure

1. Format for positive numbers

#,##0.00 ; [red](#,##0.00) ; 0.00 ; “some text “@

The first rule, which comes before the first semi-colon (;), tells Google Sheets how to display positive numbers.

2. Format for negative numbers

#,##0.00 ; [red](#,##0.00) ; 0.00 ; “some text “@

The second rule, which comes between the first and second semi-colons, tells Google Sheets how to display negative numbers.

3. Format for zeros

#,##0.00 ; [red](#,##0.00) ; 0.00 ; “some text “@

The third rule, which comes between the second and third semi-colons, tells Google Sheets how to display zero values.

Rule Before After
0;0;"Zero" 0 Zero

4. Format for text

#,##0.00 ; [red](#,##0.00) ; 0.00 ; “some text “@

The fourth rule, which comes after the third semi-colon, tells Google Sheets how to display text values.

Do You Have To Use All Four Rules?

No, you don’t have to specify them all everytime.

If you only specify one rule then it’s applied to all values.

If you specify a positive and negative rule only, any zero value takes on the positive value format.

Here are some examples of single- and multi-rule formats:

Rule Positive Negative Zero Text
0 1 -1 0 text
0;(0) 1 (1) 0 text
[red]0 1 -1 0 text
0;[red]-0 1 -1 0 text
0;[red]-0;[blue]0;[green]@ 1 -1 0 text

Google Sheets Custom Number Format Rules

Zero Digit Rule (0)

Zero (0) is used to force the display of a digit or zero, when the number has fewer digits than shown in the format rule. Use the zero digit rule (0) to force numbers to be a certain length and show leading zero(s).

For example:

Rule Before After
0.00 1.5 1.50
00000 721 00721

Pound Sign Rule (#)

The pound sign (#) is a placeholder for optional digits. If your value has fewer digits than # symbols in the format rule, the extra # won’t display anything.

Rule Before After
#### 15 15
#### 1589 1589
#.## 1.5 1.5

Thousands Separator (,)

The comma (,) is used to add thousand separators to your format rule. The rule #,##0 will work for thousands and millions numbers.

Rule Before After
#,##0 1495 1,495
#,##0.00 1234567.89 1,234,567.89

Period (.)

The period (.) is used to show a decimal point. When you include the period in your format rule, the decimal point will always show, regardless of whether there are any values after the decimal.

Rule Before After
0. 10 10.
0. 10.1 10.
0.00 10 10.00

Thousands (k or K) or Millions (m or M)

If you add thousand separators but don’t specify a format after the comma (e.g. 0,) then the hundreds will be chopped off the number. Combine this with a “k” or “K” to indicate the thousands and you have a nice way to showcase abbreviated numbers. To achieve this with millions, you need to specify two commas.

Rule Before After
0.0, 2500 2.5
0,"k" 2500 3k
0.0,"k" 2500 2.5k
0.0,,"M" 1234567 1.2M

Negative Number With Brackets ( )

Brackets can be added to the negative number rule to change the format from -100 to (100), which is often seen in accounting and financial scenarios.

Rule Before After
0;(0) -100 (100)

Asterisk (*)

The asterisk (*) is used to repeat digits in your format rule. The character that follows after the asterisk is repeated to fill the width of the cell.

In the following example, the dash is repeated to fill the width of the cell in Google Sheets:

Rule Before After
*-0 100 ——————100

Question Mark (?)

The question mark (?) is used to align values correctly by adding necessary space, even when the number of digits don’t match.

See this example:

Question mark spacing in custom number format

Underscore (_)

The underscore (_) also adds space to your number formats.

In this case, the character that follows the underscore determines the size of the space to add (but is not shown). So this rule allows you to add precise amounts of space.

For example #,##0.00_);(#,##0.00) adds a space after the positive sign that is the width of one bracket, so that the decimal point lines up with the negative numbers with brackets.

You can see this clearly in the following image, where the first line does NOT have the spacing but the second line does. The red highlight has been added to show the result of the spacing:

Underscore for spacing

Escape Character (\)

Suppose you want to actually show a pound sign in your format. If you simply add it into your format rule, then Sheets will interpret it as a placeholder for optional digits (see above).

To actually show the pound sign, precede it with a backslash (\) to ensure it shows.

This applies to any of the other special characters too.

Rule Before After
#0 10 10
\#0 10 #10

At (@)

The At symbol (@) is used as a placeholder for text, which means don’t change the text entered.

Rule Before After
0;0;0;"Special text value!" Some text Special text value!
0;0;0;@ Some text Some text

Fraction (/)

The forward slash (/) is used to denote fractions.

For example, the rule # ?/? will show numbers as fractions:

Rule Before After
# ?/? 2.3333333333 2 1/3

Percent (%)

The percent sign (%) is used to format values as %. As with the other rules, you first specify the digits and then use the % sign to change to a percent e.g. 0.00%

Rule Before After
0.00% 0.2829 28.29%

Exponent (E)

For very large (or very small) numbers, use the exponent format rule to show them more compactly.

The rule is: number * E+n, in which E (the exponent) multiplies the preceding number by 10 to the nth power.

Let’s see an example:

Rule Before After
0.00E+00 23976986 2.40E+07

Google Sheets Custom Number Format Conditional Rules

Adding conditions inside of square brackets replaces the default positive, negative and zero rules with conditional expressions.

For example:

Rule Before After
[<100]"Small" ; [>500]"Large" ; "Medium" 50 Small
[<100]"Small" ; [>500]"Large" ; "Medium" 300 Medium
[<100]"Small" ; [>500]"Large" ; "Medium" 800 Large

Conditional Rules

  • Conditions can only be specified in the first two rules
  • The third rule is used as the format for everything else that doesn’t satisfy the first two conditions
  • The fourth rule is always used for text, so cannot be used for conditional formatting

Meta instructions for conditional rules from the Google Sheets API documentation.

Colors In Google Sheets Custom Number Formats

Add colors to your rules with square brackets [ ].

There are 8 named colors you can use:
[black], [white], [red], [green], [blue], [yellow], [magenta], [cyan]

To get more colors, use the 2-digit color codes written:
[Color1], [Color2], [Color3], ..., [Color56]

For full rundown of the color palette for these 56 colors, click here.

Color Examples

Rule Before After
0;[red](0) -100 (100)

Here’s another example of using Google Sheets custom number format rules with colors: How To Make a Table in Google Sheets, and Make It Look Great

Google Sheets table

where the rule is:

Google Sheets custom number format

Meta instructions for color rules from the Google Sheets API documentation.

Google Sheets Custom Number Format Examples

Telephone

Turn any 11 digit number into a formatted telephone number with the zero digit rule and dashes:

Rule Before After
0 000-000-0000 18004567891 1 800-456-7891

Plural

Use conditional rules to pluralize words. Remember, these are still numbers under the hood so you can still do arithmetic with them. The formatting portion (“day” or “days”) is just added as a layer on top.

Rule Before After
[=1]0" day"; 0" days" 1 1 day
[=1]0" day"; 0" days" 2 2 days
[=1]0" day"; 0" days" 100 100 days

Conditional

Use conditionals to classify numbers directly:

Rule Before After
[<250]"Small"* 0 ; [>750]"Large"* 0 ; "Medium"* 0 70 Small 70
[<250]"Small"* 0 ; [>750]"Large"* 0 ; "Medium"* 0 656 Medium 656
[<250]"Small"* 0 ; [>750]"Large"* 0 ; "Medium"* 0 923 Large 923

Note: these are still numbers under the hood, so you can do arithmetic with them. Moreso, the “Small”, “Medium” and “Large” only exist in the format layer and cannot be accessed in formulas. For example, you can’t use a COUNTIF to count all the values with “Large”. To do that, you need to actually change the value so the word “Large” is in the cell, or add a helper column.

The “* ” part of the rule adds space between the word and the number so that it fills out the full width of the cell.

Conditional + Color

Add color scales to the conditional example:

Rule Before After
[color44][<250]"Small"* 0;[color46][>750]"Large"* 0;[color45]"Medium"* 0 70 Small 70
[color44][<250]"Small"* 0;[color46][>750]"Large"* 0;[color45]"Medium"* 0 656 Medium 656
[color44][<250]"Small"* 0;[color46][>750]"Large"* 0;[color45]"Medium"* 0 923 Large 923

Temperature Example

Combine conditionals with emojis to turn numbers into a emoji-scale, like this temperature example:

Rule Before After
[>90]🔥🔥🔥;[>50]🙂;❄️;"No data" 37 ❄️
[>90]🔥🔥🔥;[>50]🙂;❄️;"No data" 75 🙂
[>90]🔥🔥🔥;[>50]🙂;❄️;"No data" 110 🔥🔥🔥
[>90]🔥🔥🔥;[>50]🙂;❄️;"No data" N/a “No data”

Other Resources

Google documentation on how to format numbers in Sheets.

Custom Number Format Builder for Google Sheets and Excel.

Questions? Comments? Have you used custom number formats? Seen any interesting examples? Leave a comment below.

How to import social media statistics into Google Sheets: The Import Cookbook

Google Sheets has a powerful and versatile set of IMPORT formulas that can import social media statistics.

This article looks at importing social media statistics from popular social media channels into a Google sheet, for social network analysis or social media management. If you manage a lot of different channels then you could use these techniques to set up a master view (dashboard) to display all your metrics in one place.

The formulas below are generally set up to return the number of followers (or page likes) for a given channel, but you could adapt them to return other metrics (such as follows) with a little extra work.

Caveats: these formulas occasionally stop working when the underlying website changes, but I will try to keep this post updated with working versions for the major social media statistics.

Example workbooks: Each example has a link to an associated Google Sheet workbook, so feel free to make your own copy: File > Make a copy....

Contents

  1. Facebook
  2. Twitter
  3. Instagram
  4. Youtube
  5. Pinterest
  6. Alexa rank
  7. Quora
  8. Reddit
  9. Spotify
  10. Soundcloud
  11. GTmetrix
  12. Bitly
  13. Linkedin
  14. Sites that don’t work and why not
  15. Closing thoughts
  16. Resources

How to import social media statistics into Google Sheets with formulas


facebook icon Import Facebook data

November 2018 update: The Facebook formula is working again! The trick is to use the mobile URL 😉 Thanks to reader Mark O. for this discovery.

Start with the mobile Facebook page URL in cell A1, e.g. this url

https://mobile.facebook.com/benlcollinsData

or this variation of it:

https://m.facebook.com/benlcollinsData

Here is the formula to extract page likes:

=INDEX(REGEXEXTRACT(REGEXEXTRACT(LOWER(INDEX(IMPORTXML(A1,"//@content"),2)),"([0-9km,.]+)(?: likes)"),"([0-9,.]+)([km]?)"),,1) * SWITCH(INDEX(REGEXEXTRACT(REGEXEXTRACT(LOWER(INDEX(IMPORTXML(A1,"//@content"),2)),"([0-9km,.]+)(?: likes)"),"([0-9,.]+)([km]?)"),,2),"k",1000,"m",1000000,1)

The following screenshot shows these formulas:

Facebook Data

See the Facebook Import Sheet.

^ Back to Contents


twitter logo Import Twitter data

Start with the mobile Twitter handle URL in cell A1, e.g.

https://mobile.twitter.com/benlcollins

Here is the formula to extract follower count:

=VALUE(REGEXEXTRACT(INDEX(IMPORTXML(A1,"/"),1,3),"(?:Following)(.+)(?:Followers)"))

The following screenshot shows this formula:

Import twitter data

Note 1: This Twitter formula seems to be particularly volatile, working fine one minute, then not at all the next. I have two Sheets open where it’ll work in one, but not the other!

See the Twitter Import Sheet.

^ Back to Contents


Build Business Dashboards With Google Sheets and Data Studio

Digital marketing dashboard in Google Sheets
  • Learn how to build beautiful, interactive dashboards in my online course.
  • 9+ hours of video content, lifetime access and copies of all the finished dashboard templates.
  • Learn more

instagram logo Import Instagram data

Start with the Instagram page URL in cell A1:

https://www.instagram.com/benlcollins/

Then, this formula in cell B1 to extract the follower metadata (this may or may not work):

=IMPORTXML(A1,"//meta[@name='description']/@content")

This extracts the following info: “230 Followers, 259 Following, 465 Posts – See Instagram photos and videos from Ben Collins (@benlcollins)”

Next step is to combine this with REGEX to extract the followers for example. Here’s the formula to do that (still assuming url in cell A1):

=INDEX(REGEXEXTRACT(REGEXEXTRACT(LOWER(IMPORTXML(A1,"//meta[@name='description']/@content")),"([0-9km,.]+)( followers)"),"([0-9,.]+)([km]?)"),,1) * SWITCH(INDEX(REGEXEXTRACT(REGEXEXTRACT(LOWER(IMPORTXML(A1,"//meta[@name='description']/@content")),"([0-9km,.]+)( followers)"),"([0-9,.]+)([km]?)"),,2),"k",1000,"m",1000000,1)

This deals with any accounts that have abbreviated thousands (k) or millions (m) notations.

Alternative Approach

The following formulas to extract account metrics appear to only work for the instagram account when you are logged in.

Here’s the number of followers:

=REGEXEXTRACT(INDEX(SPLIT(QUERY(IMPORTDATA(A1),"select Col1 limit 1 offset 181"),""""),1,2),"[\d,]+")

Here’s the number following:

=REGEXEXTRACT(QUERY(IMPORTDATA(A1),"select Col2 limit 1 offset 181"),"[\d,]+")

Here’s the number of posts:

=REGEXEXTRACT(QUERY(IMPORTDATA(A1),"select Col3 limit 1 offset 181"),"[\d,]+")

The following screenshot shows these formulas:

instagram data

See the Instagram Import Sheet.

^ Back to Contents


youtube logo Import YouTube data

Start with the YouTube channel URL in cell A1:

https://www.youtube.com/benlcollins

To get the number of subscribers to a YouTube channel, use this formula in cell B1:

=VALUE(INDEX(REGEXEXTRACT(LOWER(INDEX(REGEXEXTRACT(INDEX(IMPORTXML(A1,"//div[@class='primary-header-actions']"),1,1),"(Unsubscribe)([0-9kmKM.]+)"),1,2)),"([0-9,.]+)([km]?)"),,1) * SWITCH(INDEX(REGEXEXTRACT(LOWER(INDEX(REGEXEXTRACT(INDEX(IMPORTXML(A1,"//div[@class='primary-header-actions']"),1,1),"(Unsubscribe)([0-9kmKM.]+)"),1,2)),"([0-9,.]+)([km]?)"),,2),"k",1000,"m",1000000,1))

See the YouTube Import Sheet.

^ Back to Contents


pinterest logo Import Pinterest data

In cell A1, enter the following URL, again replacing benlcollins with the profile you’re interested in:

https://www.pinterest.com/bencollins/

Then in the adjacent cell, B1, enter the following formula:

=IMPORTXML(A1,"//meta[@property='pinterestapp:followers']/@content")

to get the following output (screenshot shows older version of the formula, latest one is above and in the template file):

pinterest data

Note, you can also get hold of the profile metadata with the import formulas, as follows:

=IMPORTXML(A1,"//meta[@name='description']/@content")

See the Pinterest Import Sheet.

^ Back to Contents


alexa logo Import Alexa ranking data

Here there are two metrics I’m interested in – a site’s Global rank and a site’s US rank.

Global Rank

To get the Global rank for your site, enter your URL into cell A1 (replace benlcollins.com):

http://www.alexa.com/siteinfo/benlcollins.com/

and use the following helper formula in cell B1:

=QUERY(ArrayFormula(QUERY(IMPORTDATA(A1),"select Col1") & QUERY(IMPORTDATA(A1),"select Col2")),"select * limit 1 offset " & MATCH(FALSE,ArrayFormula(ISNA(REGEXEXTRACT(QUERY(IMPORTDATA(A1),"select Col1") & QUERY(IMPORTDATA(A1),"select Col2"),"Global rank icon.{10,}"))),0)+1)

and then extract the rank in cell C1:

=VALUE(REGEXEXTRACT(B1,"[\d]{3,}"))

US Rank

Assuming you have the Alexa URL in cell A1 again, then the US rank is extracted with this helper formula:

=QUERY(ArrayFormula(QUERY(IMPORTDATA(A1),"select Col1") & QUERY(IMPORTDATA(A1),"select Col2")),"select * limit 1 offset " & MATCH(FALSE,ArrayFormula(ISNA(REGEXEXTRACT(QUERY(IMPORTDATA(A1),"select Col1") & QUERY(IMPORTDATA(A1),"select Col2"),"title='United States Flag'.alt.{50,}"))),0))

and this formula to extract the actual rank value:

=VALUE(REGEXEXTRACT(B1,"[\d]{3,}"))

The following screenshot shows these formulas:

alexa data

See the Alexa Ranking Import Sheet.

^ Back to Contents


Build Business Dashboards With Google Sheets and Data Studio

Digital marketing dashboard in Google Sheets
  • Learn how to build beautiful, interactive dashboards in my online course.
  • 9+ hours of video content, lifetime access and copies of all the finished dashboard templates.
  • Learn more

quora logo Import Quora data

In this instance, I’ve imported the number followers Barack Obama has on Quora.

Quora is a little bit different because I need to use the URL and the profile name in my formula, so I’ve kept them in separate cells for that purpose. So in cell A1, add the generic Quora URL:

https://www.quora.com/profile/

And then in cell B1, add the profile name:

Barack-Obama-44

Then the formula in C1 to get the number of followers is:

=VALUE(QUERY(IMPORTXML(A1&B1,"//a[@href='/profile/"&B1&"/followers']"),"select Col2"))

The following screenshot shows this formula:

quora data

See the Quora Import Sheet.

^ Back to Contents


reddit logo Import Reddit data

Here, I’m using the funny subreddit as my example.

In A1:

https://www.reddit.com/r/funny/

To get the number of followers of this subreddit, use this formula in cell B1:

=IMPORTXML(A1,"//span[@class='subscribers']/span[@class='number']")

Bonus: To get the number of active viewers of this subreddit:

=IMPORTXML(A1,"//p[@class='users-online']/span[@class='number']")

The following screenshot shows these formulas:

reddit data

See the Reddit Import Sheet.

^ Back to Contents


spotify logo Import Spotify monthly listeners

Here’s a method for extracting the number of followers an artist has on the music streaming site Spotify.

First, find your favorite artist on Spotify: https://open.spotify.com/browse/featured

Copy the URL into cell A1 (it’ll look like this):

https://open.spotify.com/artist/2ye2Wgw4gimLv2eAKyk1NB

(This is Metallica, yeah 🤘)

Then put the following formula into cell A2 to extract the monthly listeners:

=N(INDEX(IMPORTXML(A1,"//h3"),2))

See the Spotify Import Sheet.

^ Back to Contents


soundcloud logo Import Soundcloud data

Start with the Soundcloud page URL in cell A1, e.g.

https://soundcloud.com/fleecemusic

Here is the formula to extract page likes:

=ArrayFormula(VALUE(REGEXEXTRACT(QUERY(SORT(IFERROR(REGEXEXTRACT( IMPORTXML(A1,"//script"),"followers_count..\d{1,}"),""),1,FALSE),"select * limit 1"),"\d{1,}")))

Alternative formula:

Here is an alternative formula to extract the page metadata, which includes the likes:

=IMPORTXML(A1,"//meta[@name='description']/@content")

the formula to extract likes is:

=VALUE(REGEXEXTRACT(REGEXEXTRACT(SUBSTITUTE( IMPORTXML(A1,"//meta[@name='description']/@content"),",",""),"\d{1,}.Followers"),"\d{1,}"))

and to extract the “talking about” number:

=VALUE(REGEXEXTRACT(REGEXEXTRACT(SUBSTITUTE( IMPORTXML(A1,"//meta[@name='description']/@content"),",",""),"\d{1,}.Tracks"),"\d{1,}"))

The following screenshot shows these formulas:

soundcloud data

See the Soundcloud Import Sheet.

^ Back to Contents


gtmetrix_logo Import GTmetrix data

GTmetrix is a website that analyzes website performance.

You need to grab the correct URL before you can start scraping the data. So navigate to the GTmetrix site and enter the URL and hit analyze. You’ll end up with a URL like this:

https://gtmetrix.com/reports/www.benlcollins.com/BcHv78bP

Those last 8 characters (“BcHv78bP”) appear to be unique each time you run an analysis, so you’ll have to do this step manually.

Then in column B, I use this formula to extract the Page Speed Score and YSlow Score, into cells B1 and B2:

=ArrayFormula(ABS(IMPORTXML(A1,"//span[@class='report-score-percent']")))

and this formula in cell B3, to get the page details (Fully Loaded Time, Total Page Size and Requests) in cells B3, B4 and B5:

=IMPORTXML(A1,"//span[@class='report-page-detail-value']")

The following screenshot shows these formulas:

gtmetrix data

See the GTmetrix Import Sheet.

^ Back to Contents


Bitly logoImport Bitly click data

Bitly is a service for shortening urls. They provide metrics for how many clicks you’ve had on each bitly link, e,g.

Bitly link metric data for import to Google Sheets

Taking a standard Bitly link (e.g. http://bitly.com/2mmW1lr) and appending a “+” to it will take you to the dashboard page, with the metrics. Then we can use the import data function, a query function and a REGEX function to extract the click metrics.

User clicks are:

=VALUE(REGEXEXTRACT(QUERY(IMPORTDATA(A9&"+"),"select Col1 limit 1"),"(?:user_clicks...)([0-9]+)"))

and global clicks are:

=VALUE(REGEXEXTRACT(QUERY(IMPORTDATA(A9&"+"),"select Col5"),"(?:global_clicks:.)([0-9]+)"))

Clicks from the Bitly network are then simply the user clicks subtracted from the global clicks.

The following screenshot shows these formulas:

Bitly data import in Google Sheets

See the Bitly Import Sheet.

^ Back to Contents


linkedin logo Import Linkedin data

This formula is no longer working for extracting Linkedin followers and I have not found an alternative.

In cell A1:

https://www.linkedin.com/in/benlcollins/

This formula used to work to get the number of Linkedin followers, but no longer:

=QUERY(IMPORTXML(A1,"//div[@class='member-connections']"),"select Col1")

and the output:

linkedin import

There is no example sheet for Linkedin since the formula is no longer working.

^ Back to Contents


cancel icon Sites that don’t work and why not

I’ve tried the following sites but the IMPORT formulas are unable to extract the social media statistics:

  • Linkedin (see above)
  • Similar Web
  • Twitch
  • Mobcrush
  • Crunchbase
  • Angel.co
  • Majestic SEO

These are all modern sites built using front-end, client-side Javascript frameworks, so the IMPORT formulas can’t extract any data because the page is built dynamically in browser as it’s loaded up. The IMPORT formulas work fine on sites built in the traditional fashion, with lots of well formed HTML tags, where the social media statistics are embedded into the site markup that is passed from the server.

Compare this screenshot of the source code for Mobcrush, built using Angular JS it looks like (click to enlarge):

Angular front end

versus what the source code looks for this page on my website (click to enlarge):

benlcollins code

You can see the code for my site has lots of tags which the IMPORT formulas can parse, whereas the other site’s code does not.

If anyone knows of any clever way to get around this, do share!

Otherwise, you’re next option is to venture down the API route. Yes, this involves coding, but it’s not as hard as you think.

I’ll be posting some API focussed articles soon. In the meantime, check out my post on how to get started with APIs, or for a peak at what’s coming, take a look at my Apps Script + API repo on GitHub.

Loading error

Also, even when these formulas are working, they can be temperamental. If you work with them a lot, sooner or later you’ll find yourself hitting this loading issue all the time, where the formulas stop displaying any results:

Loading error

^ Back to Contents


settings logo Closing thoughts

These formulas are unstable and will sometimes display an error message.

I’ve found that adding or removing the final “/” from the URL can sometimes get the formula working again (the issue is to do with caching).

I can make no guarantee that these will work for you or into the future. Whilst researching this article, I came across several older articles where many of the formulas no longer work. So things change!

To summarize: Caveat Emptor!

^ Back to Contents


link icon Resources

^ Back to Contents


As always, leave any comments, corrections or request other social media statistics below.

Icons from Freepik.

When Your Formula Doesn’t Work: Formula Parse Errors in Google Sheets

Whether you’re just starting out with Google Sheets or are a seasoned pro, sooner or later one of your formulas will give you a formula parse error message rather than the result you want.

It can be frustrating, especially if it’s a longer formula where the formula parse error may not be obvious.

In this post, I’ll explain what a Google Sheets formula parse error is, how to identify what’s causing the problem, and how to fix it.

What is a formula parse error?

Before we get into the different types of errors, you might be wondering what does formula parse error mean?

Essentially, it means Google Sheets can’t interpret your formula. It can’t fulfill the formula request so it returns an error message.

There are a variety of ways this can happen — everything from typos to mathematical impossibilities — and we’ll explore them all in detail below.

Understanding the meaning behind the error messages, and learning how to fix them, is a crucial step to becoming a formula pro in Google Sheets.

Auditing and Debugging Formula Parse Errors in Google Sheets

Match the error message in your Google Sheet to the sections below, and find out what might be causing your error.

  1. An formula parse error message popup prevents me entering my formula
  2. I’m getting an #N/A error message
  3. I’m getting an #DIV/0! error message
  4. I’m getting an #VALUE! error message
  5. I’m getting an #REF! error message
  6. I’m getting an #NAME? error message
  7. I’m getting an #NUM! error message
  8. I’m getting an #ERROR! error message
  9. I’m getting an #NULL! error message
  10. Other strategies for dealing with errors
  11. Functions to help deal with formula errors in Google Sheets
  12. Help! My formula is STILL not working

Here’s a Google Sheet with all these examples in.

1. A formula parse error message popup prevents me entering my formula

You think you’ve finished your formula, so you hit enter and boom! You get slapped with a popup message box "Houston, we have a problem" or similar:

Formula parse error in Google Sheets

It’s reasonably rare that you’ll experience this, and it usually points to some fundamental problem with your formula.

For example, imagine that as you hit the Enter key, you also accidentally struck the “\” key (which is right above the Enter key) and inadvertently added that to the end of your formula:

Unwanted character causes formula parse error

This will result in the popup error message. It’s easily corrected by removing the unwanted character.

How to correct this error?

Try to avoid these in the first place by checking your formula prior to hitting enter. Make sure you’re not missing a cell reference and you don’t have any unwanted characters lurking.

2. I’m getting an #N/A error message. How do I fix it?

The #N/A formula parse error signifies that a value is not available.

#N/A error in Google Sheets

It happens most frequently when you’re using a lookup formula (e.g. VLOOKUP) and the search term isn’t found. This is exactly what has happened in the exact match VLOOKUP in the image above. The search term A-051 is not in our data table so the formula returns #N/A.

This formula is not wrong or broken, so we don’t want to delete it. However, it would be cool if you could display a custom message, something like “Result not found”, instead of #N/A error message, especially if you have a lot of these errors showing. It gives the spreadsheet user much more information and reduces confusion.

Thankfully we can:

How to correct an #N/A error?

Well, there’s this super handy formula IFERROR:

=IFERROR(original formula, value to display if the original formula gives an error)

In this VLOOKUP example, the full formula would look like this:

=IFERROR(VLOOKUP(Search Term, Table, Column Index, FALSE),”Search term not found”)

as shown in this example:

iferror and vlookup Formula parse error example

Instead of showing the #N/A formula parse error when a value is not found, the formula will output our custom message instead “Search term not found”.

3. I’m getting an #DIV/0! error message

This formula parse error happens when a number is divided by zero, which can occur when you have a zero or a blank cell reference in the denominator.

In layman’s terms, what this means is that we trying to compute something like this:

= A / 0

which has no meaning (because there is no answer, which, when multiplied by 0, would return A).

Read more about division by 0 here, although it gets super technical super quickly.

Division by 0 error

Another example is using a formula like AVERAGE with a blank range.

So, = AVERAGE(A1:A10)   will cause a #DIV/0! error if the range A1:A10 contains no numerical values.

How to correct an #DIV/0! error?

Well the first thing to do is determine why your denominator is evaluating to zero.

You can select the denominator and see what it is evaluating to by highlighting it in the formula bar, and seeing what the result is in the little popup box, as shown in this image:

Divide by 0 error evaluation

In this case, the formula in the denominator SUM(A1:A7) evaluates to 0, which causes the error. So check whether your denominator result is 0.

Next, check whether you have linked to blank cells or a blank range in your denominator. Then you can either fill in the blank cell or range, or select a different cell or range for your formula.

If your formula is correct and your cell/ranges are not unintentionally blank, then you’ll want to handle the #DIV/0! error. It looks unsightly and makes your spreadsheet look unfinished if you leave these errors floating around.

As with the #N/A error example, use the IFERROR formula to wrap your current formula and specify a result for when a #DIV/0! error occurs. You might want to output an error message, e.g. “Division by 0 error”, or maybe a specific value, e.g. 0:

Iferror to handle div 0 error

4. I’m getting an #VALUE! error message

This formula parse error typically occurs when your formula is expecting a certain data type as an input but receives the wrong type, for example trying to do math operations on a text value instead of a numerical value.

Spaces in your cells can also cause this error message.

In this example, cell B1 contains a space, which is a string value and causes the #VALUE! error because Google Sheets can’t perform a math operation on it, as seen in this error message:

value error in google Sheets

In general, Google Sheets do a pretty good job of coercing text into numbers when needed. If you enter a value into a cell with some spaces, format it as text and then try to do math on it, Google Sheets will actually force the text into a number and still perform the calculation.

Another cause of #VALUE! errors is mixing US and Rest of World date formats.

US dates have the form MM/DD/YYYY whilst the Rest of the World goes for DD/MM/YYYY. If you have a mix of the two and try to subtract them to get the number of days between them for example, you’ll get the #VALUE! error.

(In fact, it’s the same text/number issue happening underneath the surface. Dates are stored as numbers, but if you’re date is in the wrong format for the country setting for your spreadsheet, it’ll be stored as a text string and Google won’t know it’s meant to be a date.)

Value error caused by dates

Here the correct answer should have been 59, the number of days between the 28 Feb 2017 and the 31st Dec 2016.

How to correct an #VALUE! error?

The error message should give you some information on which part of your formula is causing the problem.

Search for any possible text/number mismatches, or cells containing errant spaces. If you click into a cell and the flashing cursor has a gap between itself and the element it’s next to, then you’ll have a space there.

Cells can look empty but still contain spaces:

Value error explained

Dates with spaces in the middle won’t work either:

Date Value error explained

5. I’m getting a #REF! error message

The #REF! formula parse error occurs when you have an invalid reference.

Missing reference: For example when you reference a cell in your formula that has since been deleted (not the value inside the cell, but the whole cell has been deleted, typically when you’ve deleted a row or column in your worksheet).

In this example, the original formula was = A1 * B1, but when I deleted column A, the formula went haywire because of the missing reference:

Ref error message

Another way that a formula can refer to missing references is when you copy a formula with a relative range at the edge of your sheet. When you copy and paste, it’s possible the relative range moves as if it were outside the bounds of the sheet, which is not allowed and will cause a #REF! error.

In this example, the sum function adds the cells in the 3 rows above. When I try to copy-paste the sum function into a new cell with fewer than 3 rows above, it’ll give me the #REF! error:

Ref Formula parse error caused by copy

Lookup out of bounds: You’ve probably seen the #REF! error if you use lookup formulas frequently, when you’ve tried to return a value outside of ranges you’ve specified. In this VLOOKUP example, I’m trying to return an answer from the 3rd column of a search table that only has 2 columns:

Ref error message lookup out of bounds

Circular dependency: You’ll also get a #REF! error when a circular dependency is detected (when the formula refers to itself).

Ref error message circular dependence

In this example, I have numbers in the range A1 to A3, but the SUM formula in cell A4 tries to sum from A1 to A4, which includes itself. Hence, we have a circular argument where cell A4 is trying to be both an input and output cell, which is not allowed.

How to correct a #REF! error?

First of all, read the error message to determine what kind of #REF! error you’re dealing with. This should give you a big hint on how to correct the error.

For deleted references, look for the #REF! error is inside your formula, and replace the #REF! with the correct reference to a cell or range.

For out-of-bound lookup errors, look through your formula carefully and check your range sizes against any row or column indexes you’re using.

For circular dependencies, find the reference that’s causing the problem (i.e. where you refer to the current cell inside your formula too) and modify it.

6. I’m getting a #NAME? error message

The #NAME? formula parse error signifies a problem with your formula syntax.

The most common reason for this error is a misspelling in one of your function names.

In this example, I misspelt the SUM function as SUMM, which Google Sheets didn’t recognize, so returned an error:

Sum error from misspelling

Another reason for a #NAME? error is referencing a named range which doesn’t actually exist, or is misspelt.

So

=SUM(profit)

will give you a #NAME? error if the named range profit does not exist

Missing quotation marks around a text value, as shown in this simple formula, will also cause a #NAME? error:

=CONCAT(“First”,Second)

(The word Second is missing quotation marks.)

How to correct an #NAME? error?

Check your function names are correct. Use the function helper wizard to reduce the chances of errors happening, especially for the functions with longer names. As you start typing your formula, you’ll see a menu of functions, which you can select with the up and down arrows and Tab.

Check you have defined all named ranges before using them in your formulas and that they all have the correct spellings.

Check any text values are entered with the required quotation marks.

Lastly, have you missed the colon in your range references? It’ll be obvious because it won’t be highlighted correctly.

This formula =SUM(A1A10)

is missing the colon between A1 and A10 and will throw a #NAME? error.

It should of course read =SUM(A1:A10)

7. I’m getting an #NUM! error message

The #NUM! formula parse error is shown when your formula contains numeric values that aren’t valid.

The classic example is trying to find the square root of a negative number, which isn’t allowed:

Num error in google sheets

(For any math geeks out there, you’ll know that you can resolve square roots of negative numbers with imaginary numbers, but these are outside the realm of spreadsheets.)

Some other functions that can result in #NUM! error messages are the SMALL and LARGE functions. If you try to find the smallest n-th value in your dataset, where n is outside the count of values in your dataset, you’ll get a #NUM! error.

For example, you ask Google Sheets to find the 10th smallest number in a dataset that only has 5 values in it:

Num error caused by small function

(Why this doesn’t return a #REF! error like the VLOOKUP out of bounds example, I don’t know.)

How to correct a #NUM! error?

You need to check the numeric arguments in your formula. The error message should give you some hints about which part of the formula is causing the issue.

8. I’m getting an #ERROR! formula parse error message

This formula parse error message is unique to Google Sheets and doesn’t have a direct equivalent in Excel. It means that Google Sheets can’t understand the formula you’ve entered, because it can’t parse the formula to execute it.

For example, if you manually type in a $ symbol to refer to an amount, but Google Sheets thinks you’re referring to an absolute reference:

Error Formula parse error

or you’ve missed a “&” when concatenating text and numerical values:

Error error concatenation

In this case the formula should be: =”Total “&sum(A1:A3)

Another case, caused when we messed up the closing brackets of a formula:

Error Formula parse error

How to correct an #ERROR! error?

Carefully check your formula for accuracy.

You want to ensure you’ve got the correct number of brackets and correct join syntax between text and numerical values (e.g. using “&”).

When you want to show values with currency symbols or as percentages, don’t manually type in the “$” or the “%”. Instead enter a plain number and then use the formatting options to change it to the style you want.

9. I’m getting an #NULL! error message

I haven’t been able to recreate a #NULL! formula parse error in the wild but theoretically, it exists!

Null Formula parse error

(If you have one showing in your sheet, let me know! I’d love to update this article with an example here.)

10. Other strategies for dealing with a formula parse error

Look for red highlighting in your formula as this will help identify the source of your error e.g. in the case of too many brackets, the extra, superfluous ones will be highlighted in red.

Peeling back the onion: this is a technique to debug errors for long, complex formulas. Unwrap the outer functions in your formula one-by-one, until you get it working again. Then you can start to add them back one-by-one again, and see exactly which step is causing the issue and fix that.

Different syntax in different countries: Some European countries will use semi-colons “;” in place of commas “,” so this could be a cause of your error. Compare these two formula, which have identical inputs and outputs, but the syntax is different for users in different countries (locales).

=ArrayFormula(VLOOKUP(A1;Sheet2!A:I;{2\3\4\5\6\7\8};FALSE))

is the same formula as this:

=ArrayFormula(VLOOKUP(A1,Sheet2!A:I,{2,3,4,5,6,7,8};FALSE))

(This is an example of a VLOOKUP returning multiple values (an array) instead of just a single value.)

Pro tip:

Use apostrophe at the start of a formula to turn it into a text string, which won’t execute. This is sometimes useful for seeing your whole formula for debugging, keeping a copy of your formula so you can copy and paste bits of it elsewhere for testing.

11. Functions to help deal with formula parse errors in Google Sheets

A few other functions related to formula parse errors are worth knowing about.

In fact, there is even a function to generate #N/A errors. It’s of limited use, but can be helpful for doing data validation in more complex formulas.

=NA()    will output an #N/A error. (Google Docs Help on NA)

=ERROR.TYPE(value)    will return a number corresponding to the error type:

  • 1 for #NULL!
  • 2 for #DIV/0!
  • 3 for #VALUE!
  • 4 for #REF!
  • 5 for #NAME?
  • 6 for #NUM!
  • 7 for #N/A
  • 8 for all other errors

(Google Docs Help on ERROR.TYPE)

=ISNA(value)
checks whether a value is the error #N/A, and will give the output TRUE for a #N/A error and FALSE otherwise. (Google Docs Help on ISNA)

=ISERR(value)
checks whether a value is any error other than the #N/A error. (Google Docs Help on ISERR)

=ISERROR(value)
checks whether a value is an error, and will give the output TRUE for any error. (Google Docs Help on ISERROR)

These functions can be summarized in the following table:

#N/A error functions

13. Help! My formula is STILL not working

Take a deep breath, don’t panic! There’s an army of Google Sheets super users out there who would love to help you fix your issue, free of charge, in the active help forums.

Try posting your problem into the forum and someone will likely help you out.

To make it easier for people to help you, please share your Google Sheet (either view-only or create a redacted copy if sharing is a concern), what error message you’re getting and what you were expecting the correct answer to be.

Google Sheets Help Forum

The Complete Guide to Simple Automation using Google Sheets Macros

Google Sheets Macros are small programs you create inside of Google Sheets without needing to write any code.

They’re used to automate repetitive tasks. They work by recording your actions as you do something and saving these actions as a “recipe” that you can re-use again with a single click.

For example, you might apply the same formatting to your charts and tables. It’s tedious to do this manually each time. Instead you record a macro to apply the formatting at the click of a button.

In this article, you’ll learn how to use them, discover their limitations and also see how they’re a great segue into the wonderful world of Apps Script coding 😀

Contents

  1. What are Google Sheets macros?
  2. Why should you use macros?
  3. How to create your first macro
  4. Other options
  5. Best Practices for Google Sheets Macros
  6. Limitations of Google Sheets Macros
  7. A peek under the hood of Google Sheets Macros
  8. Example of Google Sheets Macros
  9. Resources

1. What are Google Sheets macros?

Think of a typical day at work with Google Sheets open. There are probably some tasks you perform repeatedly, such as formatting reports to look a certain way, or adding the same chart to new sales data, or creating that special formula unique to your business.

They all take time, right?

They’re repetitive. Boring too probably. You’re just going through the same motions as yesterday, or last week, or last month. And anything that’s repetitive is a great contender for automating.

This is where Google Sheets macros come in, and this is how they work:

  • Click a button to start recording a macro
  • Do your stuff
  • Click the button to stop recording the macro
  • Redo the process whenever you want at the click of a button

They really are that simple.

^ Back to Contents

2. Why should you use macros in Google Sheets?

There’s the obvious reason that macros in Google Sheets can save you heaps of time, allowing you to focus on higher value activity.

But there’s a host of other less obvious reasons like: avoiding mistakes, ensuring consistency in your work, decreased boredom at work (corollary: increased motivation!) and lastly, they’re a great doorway into the wonderful world of Apps Script coding, where you can really turbocharge your spreadsheets and G Suite work.

^ Back to Contents

3. Steps to record your first macro

Let’s run through the process of creating a super basic macro, in steps:

1) Open a new Google Sheet (pro-tip 1: type sheets.new into your browser to create a new Sheet instantly, or pro-tip 2: in your Drive folder hit Shift + s to create a new Sheet in that folder instantly).

Type some words in cell A1.

2) Go to the macro menu: Tools > Macros > Record macro

Google Sheets macro menu

3) You have a choice between Absolute or Relative references. For this first example, let’s choose relative references:

Macro with relative reference

Absolute references apply the formatting to the same range of cells each time (if you select A1:D10 for example, it’ll always apply the macro to these cells). It’s useful if you want to apply steps to a new batch of data each time, and it’s in the same range location each time.

Relative references apply the formatting based on where your cursor is (if you record your macro applied to cell A1, but then re-run the macro when you’ve selected cell D5, the macro steps will be applied to D5 now). It’s useful for things like formulas that you want to apply to different cells.

4) Apply some formatting to the text in cell A1 (e.g. make it bold, make it bigger, change the color, etc.). You’ll notice the macro recorder logging each step:

Macro logging step

5) When you’ve finished, click SAVE and give your Macro a name:

Save macro

(You can also add a shortcut key to allow quick access to run your macro in the future.)

Click SAVE again and Google Sheets will save your macro.

6) Your macro is now available to use and is accessed through the Tools > Macros menu:

select macro menu

7) The first time you run the macro, you’ll be prompted to grant it permission to run. This is a security measure to ensure you’re happy to run the code in the background. Since you’ve created it, it’s safe to proceed.

First, you’ll click Continue on the Authorization popup:

Macro authorization

Then select your Google account:

Macro choose Google account

Finally, review the permissions, and click Allow:

Macro grant permissions

8) The macro then runs and repeats the actions you recorded on the new cell you’ve selected!

You’ll see the following yellow status messages flash across the top of your Google Sheet:

Macro running script

Macro finished script

and then you’ll see the result:

Macro result

Woohoo!

Congratulations on your first Google Sheets macro! You see, it was easy!

Here’s a quick GIF showing the macro recording process in full:

Recording a macro

And here’s what it looks like when you run it:

Run your macro

^ Back to Contents

4. Other options

4.1 Macro Shortcuts

This is an optional feature when you save your macro in Google Sheets. They can also be added later via the Tools > Macros > Manage macros menu.

Shortcuts allow you to run your macros by pressing the specific combination of keys you’ve set, which saves you further time by not having to click through the menus.

Any macro shortcut keys must be unique and you’re limited to a maximum of 10 macro shortcut keys per Google Sheet.

Macro shortcut

In the above example, I could run this macro by pressing:

⌘ + option + shift + 1

keys at the same time (takes practice 😉). Will be a different key combo on PC/Chromebooks.

4.2 Deleting macros

You can remove Google Sheets macros from your Sheet through the manage macros menu: Tools > Macros > Manage macros

Under the list of your macros, find the one you want to delete. Click the three vertical dots on right side of macro and then choose Remove macro:

remove macro

4.3 Importing other macros

Lastly, you can add any functions you’ve created in your Apps Script file to the Macro menu, so you can run them without having to go to the script editor window. This is a more advanced option for users who are more comfortable with writing Apps Script code.

import function to macro menu

This option is only available if you have functions in your Apps Script file that are not already in the macro menu. Otherwise it will be greyed out.

^ Back to Contents

5. Best Practices for Google Sheets Macros

Use the minimum number of actions you can when you record your macros to keep them as performant as possible.

For macros that make changes to a single cell, you can apply those same changes to a range of cells by highlighting the range first and then running the macro. So it’s often not necessary to highlight entire ranges when you’re recording your macros.

^ Back to Contents

6. Limitations of Google Sheets Macros

Macros are bound to the Google Sheet in which they’re created and can’t be used outside of that Sheet. Similarly, macros written in standalone Apps Script files are simply ignored.

Macros are not available for other G Suite tools like Google Docs, Slides, etc. (At least, not yet.)

You can’t distribute macros as libraries or define them in Sheets Add-ons. I hope the distribution of macros is improved in the future, so you can create a catalog of macros that is available across any Sheets in your Drive folder.

^ Back to Contents

7. A peek under the hood of Google Sheets Macros

Behind the scenes, macros in Google Sheets converts your actions into Apps Script code, which is just a version of Javascript run in the Google Cloud.

If you’re new to Apps Script, you may want to check out my Google Apps Script: A Beginner’s Guide.

If you want to take a look at this code, you can see it by opening the script editor (Tools > Script editor or Tools > Macros > Manage macros).

You’ll see an Apps Script file with code similar to this:

/** @OnlyCurrentDoc */

function FormatText() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getActiveRangeList().setFontWeight('bold')
  .setFontStyle('italic')
  .setFontColor('#ff0000')
  .setFontSize(18)
  .setFontFamily('Montserrat');
};

Essentially, this code grabs the spreadsheet and then grabs the active range of cells I’ve selected.

The macro then makes this selection bold (line 5), italic (line 6), red (line 7, specified as a hex color), font size 18 (line 8) and finally changes the font family to Montserrat (line 9).

The video at the top of this page goes into a lot more detail about this Apps Script, what it means and how to modify it.

Macros in Google Sheets are a great first step into the world of Apps Script, so I’d encourage you to open up the editor for your different macros and check out what they look like.

(In case you’re wondering, the line /** @OnlyCurrentDoc */ ensures that the authorization procedure only asks for access to the current file where your macro lives.)

^ Back to Contents

8. Examples of Google Sheets Macros

8.1 Formatting tables

Record the steps as you format your reporting tables, so that you can quickly apply those same formatting steps to other tables. You’ll want to use Relative references so that you can apply the formatting wherever your table range is (if you used absolute then it will always apply the formatting to the same range of cells).

Check out the video at the top of the page to see this example in detail, including how to modify the Apps Script code to adjust for different sized tables.

8.2 Creating charts

If you find yourself creating the same chart over and over, say for new datasets each week, then maybe it’s time to encapsulate that in a macro.

Record your steps as you create the chart your first time so you have it for future use.

The video at the top of the page shows an example in detail.


The following macros are intended to be copied into your Script Editor and then imported to the macro menu and run from there.

8.3 Convert all formulas to values on current Sheet

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// convert all formulas to values in the active sheet
function formulasToValuesActiveSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  range.copyValuesToRange(sheet, 1, range.getLastColumn(), 1, range.getLastRow());
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will convert any formulas in the current sheet to values.

8.4 Convert all formulas to values in entire Google Sheet

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// convert all formulas to values in every sheet of the Google Sheet
function formulasToValuesGlobal() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    var range = sheet.getDataRange();
    range.copyValuesToRange(sheet, 1, range.getLastColumn(), 1, range.getLastRow());
  });
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will convert all the formulas in every sheet of your Google Sheet into values.

8.5 Sort all your sheets in a Google Sheet alphabetically

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// sort sheets alphabetically
function sortSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  var sheetNames = [];
  sheets.forEach(function(sheet,i) {
    sheetNames.push(sheet.getName());
  });
  sheetNames.sort().forEach(function(sheet,i) {
    spreadsheet.getSheetByName(sheet).activate();
    spreadsheet.moveActiveSheet(i + 1);
  });
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will sort all your sheets in a Google Sheet alphabetically.

8.6 Unhide all rows and columns in the current Sheet

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// unhide all rows and columns in current Sheet data range
function unhideRowsColumnsActiveSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  sheet.unhideRow(range);
  sheet.unhideColumn(range);
}

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will unhide any hidden rows and columns within the data range. (If you have hidden rows/columns outside of the data range, they will not be affected.)

8.7 Unhide all rows and columns in entire Google Sheet

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// unhide all rows and columns in data ranges of entire Google Sheet
function unhideRowsColumnsGlobal() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    var range = sheet.getDataRange();
    sheet.unhideRow(range);
    sheet.unhideColumn(range);
  });
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will unhide any hidden rows and columns within the data range in each sheet of your entire Google Sheet.

8.8 Set all Sheets to have a specific tab color

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// set all Sheets tabs to red
function setTabColor() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    sheet.setTabColor("ff0000");
  });
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will set all of the tab colors to red.

Want a different color? Just change the hex code on line 5 to whatever you want, e.g. cornflower blue would be 6495ed

Use this handy guide to find the hex values you want.

8.9 Remove any tab coloring from all Sheets

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// remove all Sheets tabs color
function resetTabColor() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    sheet.setTabColor(null);
  });
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will remove all of the tab colors from your Sheet (it sets them back to null, i.e. no value).

Here’s a GIF showing the tab colors being added and removed via Macros (check the bottom of the image):

color tabs with Macros

8.10 Hide all sheets apart from the active one

Copy and paste this code into your script editor and import the function into your Macro menu:

function hideAllSheetsExceptActive() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    if (sheet.getName() != SpreadsheetApp.getActiveSheet().getName()) 
      sheet.hideSheet();
  });
};

Running this macro will hide all the Sheets in your Google Sheet, except for the one you have selected (the active sheet).

8.11 Unhide all Sheets in your Sheet in one go

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

function unhideAllSheets() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    sheet.showSheet();
  });
};

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will show any hidden Sheets in your Sheet, to save you having to do it 1-by-1.

Here’s a GIF showing how the hide and unhide macros work:

hide unhide sheets with macros

You can see how Sheet6, the active Sheet, is the only one that isn’t hidden when the first macro is run.

8.12 Resetting Filters

Ok, saving the best to last, this is one of my favorite macros! 🙂

I use filters on my data tables all the time, and find it mildly annoying that there’s no way to clear all your filters in one go. You have to manually reset each filter in turn (time consuming, and sometimes hard to see which columns have filters when you have really big datasets) OR you can completely remove the filter and re-add from the menu.

Let’s create a macro in Google Sheets to do that! Then we can be super efficient by running it with a single menu click or even better, from a shortcut.

Open your script editor (Tools > Script editor). Copy and paste the following code onto a new line:

// reset all filters for a data range on current Sheet
function resetFilter() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  range.getFilter().remove();
  range.createFilter();
}

Back in your Google Sheet, use the Macro Import option to import this function as a macro.

When you run it, it will remove and then re-add filters to your data range in one go.

Here’s a GIF showing the problem and macro solution:

Macro to reset filters

^ Back to Contents

9. Resources

If you’re interested in taking things further, check out the following resources for getting started with Apps Script:

Macro reference guide in Google Docs help

Macro reference guide in the Google Developer documentation

And if you want to really start digging into the Apps Script code, you’ll want to bookmark the Google documentation for the Spreadsheet Service.

Finally, all of this macro code is available here on GitHub.

How to use Google Sheets: A Beginner’s Guide

This Google Sheets tutorial will help take you from an absolute beginner, or basic user, through to a confident, competent, intermediate-level user.

Google Sheets is a hugely powerful tool, for everything from digital marketing to finance modeling, from project management to statistical analysis, in fact, just about any activity involving the recording and analysis of data.

And if you’re (relatively) new, it really pays dividends to learn how to use Google Sheets correctly. This tutorial will help you transition from newbie to ninja in short order!

If you’re new to Google Sheets, then I recommend you start from the beginning of this article.

However, if you’ve used Sheets before, feel free to skip sections 1 and 2, and begin with the Data and basic formulas section.

A template is available for copying to your Drive, to accompany this tutorial:

Want a copy of the template from this tutorial?
Click here to create your own copy >>

In addition, various advanced resources are listed for you to take things a step further. Look for this logo: Advanced Resource

Contents

  1. How to use Google Sheets: Total Beginner
    • What is Google Sheets?
    • How is it different to Excel?
    • How to create your first Google Sheet
    • The Google Sheets editing window
    • Working with data in Google Sheets

     

  2. How to use Google Sheets: The editing window
    • Editing columns and rows
    • Creating new tabs
    • Formatting
    • Removing formatting

     

  3. How to use Google Sheets: Data and basic formulas
    • Different types of data
    • Doing math on numbers
    • Starter functions: COUNT, SUM, AVERAGE
    • Splitting data in cells
    • Combining data in cells

     

  4. How to use Google Sheets: Killer features
    • Adding comments and notes
    • Sharing your Sheet
    • Real-time Collaboration

     

  5. How to use Google Sheets: Intermediate techniques
    • Freezing panes for easy viewing
    • Understanding cell references
    • Basic conditional formatting
    • Sorting & filtering data
    • Adding Charts
    • Using the Explore feature
    • BONUS: The VLOOKUP function

     

  6. How to use Google Sheets: Next steps

 

How to use Google Sheets1. How to use Google Sheets

What is Google Sheets?

Google Sheets is a free, cloud-based spreadsheet application. That means you open it in your browser window like a regular webpage, but you have all the functionality of a full spreadsheet application for doing powerful data analysis. It really is the best of both worlds.

How is it different to Excel?

No doubt you’ve heard of Microsoft Excel, the long established heavyweight of the spreadsheet world. It’s an incredibly powerful, versatile piece of software, used by approximately 750 million – 1 billion people worldwide. So yeah, a tough act to follow.

Google Sheets is similar in many ways, but also distinctly different in other areas. It has (mostly) the same set of functions and tools for working with data. In fact, some people mistakenly call it “Google Excel” or “Google spreadsheets.”

With the risk of getting into an opinionated debate about the strengths/weaknesses of each platform, here a few key differences:

  • Google Sheets is cloud-based whereas Excel is a desktop program. With Sheets, you’ll no longer have versions of your work floating around. Everyone always sees the same, most up-to-date version of Sheets, showing the same spreadsheet data.
  • Collaboration is baked into Sheets, so it works extremely well. Excel is still trying to play catch up here.
  • Both have charting tools and Pivot Table tools for data analysis, although Excel’s are more powerful in both cases.
  • Excel can handle much bigger datasets than Sheets, which has a limit of 2 million cells.
  • Being a cloud-based program, Google Sheets integrates really well with other online Google services and third-party sites.
  • Both have scripting languages to extend their functionality and build custom tools. Google Sheets uses Apps Script (a variant of Javascript) and Excel uses VBA.

For the material we’ll cover in this article, there’s very little difference between the programs however.

For a deep-dive into the differences between Excel and Google Sheets, have a look at ExcelToSheets.com

Why use Google Sheets?

How’s this for starters:

  • It’s free!
  • It’s collaborative, so teams can all see and work with the same spreadsheet in real-time.
  • It has enough features to do complex analysis, but…
  • …it’s also really easy to use.

Need more convincing? Here’s 5 more reasons from Google themselves.

Can it still do advanced stuff?

Absolutely! You can build dashboards, write formulas that make your head spin and even build applications to automate your job. The sky’s the limit!

You’ll find lots of resources on this site for intermediate/advanced level users, as well as comprehensive online training courses.

Ok, where do I get it? How to create your first Google Sheet

If this is your first time with Sheets, head over to https://www.google.com/sheets/about/ which, as of January 2018, looks like this:

How to use Google Sheets: Start screen

Click on the Go To Google Sheets button in the middle of the screen. You’ll be prompted to login:

How to use Google Sheets: Login screen

And then you arrive at the Google Sheets home screen, which will show any previous spreadsheets you’ve created.

Click the huge green plus button to create a new Google Sheet:

How to use Google Sheets: Google Sheets home screen

Opening your first Google Sheet from Drive

You can create new Google Sheets from your Drive folder by clicking on the blue NEW button:

Create a Google Sheet from Drive

When you create a new Google Sheet, it’ll be created in your main Drive folder (your root folder):

New Google Sheet in Drive folder

(Note: Don’t panic if you don’t see the Sheet yet, it may not show up until you’ve renamed it. See next step on how to do this.)

Here you can drag it to a different folder if you wish (to keep things organized). Do this by clicking-and-holding the file, and dragging to where you want it to go:

Moving Google Sheet in Drive

The Google Sheet editing window

This is what your blank Google Sheet will look like:

Blank Google Sheet

You can rename your Sheet in the top left corner. Click on where it says Untitled spreadsheet and type in whatever name you want to give your Sheet, in this example “New Sheet”.

So let’s introduce some key terminology and the fundamental concept upon which spreadsheets work:

How to use Google Sheets: terminology

There are two menu rows above your Sheet, of which we’ll see more further on in this tutorial.

The main window consists of a grid of cells. An individual cell is a single rectangle, at the intersection of one column and one row, and it’ll hold a single piece of data.

The columns are vertical ranges of cells, labeled by letters running across the top of the Sheet.

Rows are horizontal ranges of cells, labeled by numbers running down the left side of your Sheet.

In the example above, I highlighted column E and row 10.

** The fundamental concept of spreadsheets: **

Column E and row 10 intersect at one cell, and one cell only. Thus we can combine the column letter and row number to create a unique reference to this cell, E10. Now when we want to refer to this cell, for example to access data in this cell, we use the address E10 to do that.

Understand this and you understand spreadsheets. The rest is just details!

Entering, selecting, deleting and moving data

Now the fun really starts! Let’s start using this new blank sheet we’ve created.

Click cell A1 (that’s the intersection of column A with row 1, the cell in the top left corner of the Sheet) and you’ll see a blue box around the cell, to indicate it’s highlighted:

Cell A1

Then you can simply start typing and you’ll see the data being entered into that cell:

Cell A1 typing

Hit enter when you’ve finished entering data and you’ll move down to the next cell, having completed your data entry. If you hit the Tab key instead, you’ll move across one cell to the right!

It’s worth pointing out an important nuance here:

Clicking ONCE on the cell highlights the whole cell. Clicking TWICE enters into the cell, so you can select or work with the data only.

If you find yourself stuck inside a cell, you can press the ESCAPE key to deselect the contents and go up a level, to just having the cell selected.

Try it for yourself and see how the cursor shows up inside the cell when you double-click, allowing you to edit the data.

To delete the data we just entered, either click the cell once and hit the delete key, or, click the cell twice and then press the delete key until all your data is cleared out.

Help! I made a mistake

First of all, don’t panic!

Google Sheets saves every step of your work so you can always go back a step (or two) if needed.

Press Cmd + Z if you’re on a Mac, or Ctrl + Z if you’re on a PC and you’ll undo your previous step. Keep pressing and you’ll simply go further back through your changes. (Pressing Cmd + Y on a Mac, or Ctrl + Y on a PC moves your forwards, to redo your last step.)

You can also undo using the Undo arrow on the menu:

Undo arrow

Creating a basic table

Right, with all that in mind, it’s time for a quick exercise.

See if you can create the following table for our fictitious gym membership site, by entering the data into the correct cells (there is no formatting or other tricks used at this stage):

Google Sheets raw table

Feel free to use your own data if you wish. Also note that the dates entered above are in US format, with the Month first, so don’t worry if your table has the Day first.

Want a copy of the template from this tutorial?
Click here to create your own copy >>

2. How to use Google Sheets: The working environment

Changing the size, inserting, deleting, hiding/unhiding of columns and rows

To select a row or column, click on the number (rows) or letter (columns) of the row or column you want to select. This will highlight the whole row or column blue, to indicate you have it selected.

To change the width of a column, or height of a row, hover your cursor over the grey line denoting the edge of the column or row, until your cursor changes to look like this:

Column width cursor

Then click and drag the cursor left or right to change the width of this column. It’s the same process to change the height of rows.

Pro-tip: To quickly change the column width to fit your cell contents, double-click when you’re hovered over the grey line.

How to add columns in Google Sheets: To insert additional columns or rows, click on the existing column or row next to where you’d like to insert a new column or row. With the column or row selected (highlighted blue), right click to bring up the options menu, then select Insert Before (or Insert After) for Columns, or Insert Above (or Insert Below) for Rows:

Editing rows and columns in Google Sheets

Adding extra rows and columns at end

If you reach the outer edges of a Google Sheet, you’ll notice the rows and/or columns stop. But don’t worry, you can add more.

If you’ve scrolled all the way to the bottom of your Sheet (or added that much data), you’ll notice that you’re given 1,000 rows by default. There’s a button to add more rows if you need, either 1,000 as shown, or any number you wish (up to a limit, more on that below).

Add more rows

If you reach the right edge of the Sheet, i.e. the last column, then you add more columns in the standard way. Right-click a cell in the last column to bring up the menu and then choose to add a column to the right.

Pro-tip: If you want to add more than one column, there’s a trick to do it in one go. As an example, say you wanted to add three new columns to the right side of your Sheet, begin by highlighting the last three columns that are there already, then right clicking and choosing to inser new columns. It’ll then insert three new columns for you!

Data Limit: Finally, keep in mind that each Google Sheet is limited to 2 million cells, which sounds like a lot but is 50,000 rows by 40 columns (a totally reasonable sized transaction database for an ecommerce store for example). Anyway, you’ll find Sheets slows down considerably before reaching that limit. Most people report a slight slow down with tens of thousands of rows of data and complex formulas and models.

Adding/removing multiple sheets, renaming them

Super easy!

Click the big plus button in the bottom left of your Google Sheet to add a new Sheet (also called a Tab).

How to add a new tab

Why use multiple tabs within your Google Sheet?

Well, like a book with chapters on different topics, it can help separate different data and keep your Sheet organized.

For example, you might have a Sheet solely to record your global settings (any variables like name, email, tax rate, headcount…) and another for transactional data, and yet another for the analysis and charts.

The button with the three bars, next to the plus, is your index button, listing all of the tabs in your Google Sheet. This is super useful when you start having a lot of different tabs to manage.

To rename a sheet, or delete a sheet, click the small arrow next to the name (e.g. Sheet1) to bring up the menu. Here you’ll see the option to rename, to delete, or even hide (and unhide) Sheets.

Rename a Sheet in Google Sheets

For naming, I try to indicate what’s in that tab, so use names like: Settings, Dashboard, Charts, Raw Data.

Formatting

You’ll find all of the formatting options on the top tool bar, so you can center your headings, make them bold, format numbers as currency etc. You may find them all on one single row, or you may find some under the More button, as shown in this image:

Google Sheets formatting

They’re similar to a word processor and pretty self-explanatory. You can always hit undo if you make a mistake (Cmd + Z on Mac, or Ctrl + Z on PC).

Try the following to format our basic table:

> Make the heading bold and size 14px
> Center the column headings and make them bold
> Center the tier column
> Change the date format to 01-Jan-2018 (Hint: the date format is found under the button that says 123.)
> Add a dollar sign, $, to the fee column
> Add a border around the whole table.

Here’s a GIF to guide you:

Formatting Google Sheets

(Note, you can also find the formatting options under the Format menu, between the Insert and Data menu options.)

Alternating colors

Let me show you the option to add alternating row colors (banding) to your tables.

Let’s apply it to our basic table, by highlighting the table and then from the menu:

Format > Alternating colors

as shown here:

Google Sheets alternating colors

Remember, a little bit of formatting goes a long way. If you Sheet is more readable and tidy, people will be more likely to understand it and absorb the information.

Removing formatting

This is my number 1 productivity tip in Google Sheets.

To remove all formatting from a cell (or range of cells), hit Cmd + \ on a Mac or Ctrl + \ on a PC.

This will save you so much time when you’re wanting to remove formatting that isn’t yours or that you no longer want or need.

Advanced Resource: Read more on formatting

Want a copy of the template from this tutorial?
Click here to create your own copy >>

3. How to use Google Sheets: Data and basic formulas

Different types of data

You’ve already seen different data types in Google Sheets in our basic table.

The key point to understand with spreadsheet data is that each cell contains the data itself, and a format applied to that data.

For example, suppose a cell contained:

2, or
2.00, or
$2, or
$2.00

In each case the underlying data is the number 2, but with a different format applied each time. If we add 2 to each of these cells we get back the number 4 in every case (with formatting applied).

How to use Google Sheets: Basic data types
Basic data types in Google Sheets

You’ll notice that currency data, percentage data and even dates are actually just numbers under the hood (dates? Really? Yes, they are, but that’s a discussion for another day). They’re all right-aligned, hanging out on the right edge of their cell.

Text is left-aligned by default.

If you want to force something to be stored as text, you can prepend a single quote, ' before the cell contents. So typing in '0123 will show as 0123 in your cell and be left-aligned. If you omit the single quote mark, then it’ll be stored as a number and show up as 123 without the 0.

Doing math on numbers

Easy-peasy, just like you do on a calculator.

You click the cell you want to do your calculation in, type an equals sign (=) to indicate you’re performing a calculation and then type in your formula, e.g.

Google Sheets calculation

Notice how calculation will show in the formula bar (1) as well as in the cell (2).

You’ll notice that you get a preview of the answer (in this case, 25) above the formula.

Starting with functions: COUNT, SUM, AVERAGE

Technically you’ve already written your first formula in the section above on math calculations, but really, your formula career begins when you start using the built-in functions (of which there are hundreds!).

Returning to our basic table, let’s count how many members we have, what the total monthly fees are and what the average monthly fees are.

COUNT

Click on cell B8, type an equals (=) and then start typing the word COUNT. You’ll notice an auto-complete menu comes up showing all of the functions beginning with C, like this:

Formula auto-complete

You can either keep typing COUNT in full, or find it and select in from the list (hover over it and click on it OR hit enter OR hit tab).

Next you’ll see the formula helper window show up, which tells you about the formula syntax and how to fill it in correctly:

How to use Google Sheets formula helper

In this case, the COUNT function is expecting a list of numeric values.

You have to select the range of cells you want to count. So click on B4, hold you mouse down and drag down to B7, so that the four cells are highlighted in orange and B4:B7 is showing up in your function:

Then, close the function with a closing bracket “)”:

=COUNT(B4:B7)

Note: COUNT is used to count numbers. If you want to count text (for example the names) then COUNT won’t work (it’ll give you a 0). Instead use COUNTA (with an A at the end), otherwise the method is the same.

SUM

Your turn! Try creating a total for the membership fees in cell D8. Follow the same process as the count function, except use SUM and highlight the values in column D.

=SUM(D4:D7)

AVERAGE

You’re on a roll, so go ahead and calculate the average of the membership fees. Use the AVERAGE function in cell D9.

=AVERAGE(D4:D7)

Psst, you’ll notice that Google even helps you out sometimes and suggests the exact formula you were after:

Google Sheets average

Here you go:

Google Sheets sum and average

If you make a mistake with your formula, you’ll see an errors message, probably something like #N/A, #REF!, #DIV/0 etc.

You’ll need to re-enter your formula and correct it before proceeding. These error messages do give a lot of context though, so they’re worth understanding.

Advanced Resource: Learn more about formula errors

What’s the difference between a function and a formula?

Well, both are used interchangeably and rather loosely so I wouldn’t get hung up on it.

For the pedantic, a function refers to the single method word (e.g. SUM) whereas a formula refers to the whole operation after the equals sign, often consisting of multiple functions.

Separating data with the Text to columns feature

Let’s suppose you wanted First Name and Last Name, rather than just simply Name as we have in our dead famous authors membership table. How do we go about doing that?

Well of course, as with everything in spreadsheets, there are lots of ways, but let me show you the easiest, using the Text to Columns feature.

Back to our basic table, create a new column to the right of Name before the Tier column, i.e. create a new, blank column B.

Highlight the four names and click:

Data > Split text to columns…

On the sub-menu that shows up choose SPACE and marvel at how Google Sheets separates the full name into a first and last name. Feel free to rename the columns First Name and Last Name too.

How to use google sheets split text

Combining cells

Oh, blast I hear you say! You meant to keep hold of that full Name column as well.

No problem, let’s learn how to combine text so we can rebuild it.

Insert a new blank column between B and C (between Last name and Tier) and call it Full Name, in cell C3.

Add this formula in cell C4:

= A4 & B4

That’s A4, Ampersand, B4.

What it does is combine the data in cell A4 with the data in cell B4 and output it in cell C4.

Hmm, but this gives an output like this:

CharlesDickens

That’s obviously not good enough! We need a space between the names!

Change the formula to this, by clicking right after the ampersand and adding double quote, space, double quote, ampersand:

= A4 & " " & B4

Here we’ve told Google Sheets to add a space into the mix, and the output now will be:

Charles Dickens

Voilà, that’s better!

Your formula is sitting pretty in cell C4, but how do you get it to work for the other rows?

Copy it!

You can either:
i) right-click, copy, move down to select the next cell, right click again, click paste, or
ii) Cmd + C (on Mac) or Ctrl + C (on PC), move down to select next cell, then Cmd + V (on Mac) or Ctrl + V (on PC), or
iii) drag the formula down by holding the little blue box at the bottom right corner of the blue highlighting around the original cell.

The neat thing is that as you copy this formula down, the cell references will change from row 4 to row 5, row 5 to row 6, etc., automatically! How cool is that!

(This is what’s known as relative references. More on that in section 5 below.)

Here’s a GIF to show this technique in action:

Google Sheets combine cells

Want a copy of the template from this tutorial?
Click here to create your own copy >>

4. How to use Google Sheets: Killer features

Let’s see some of the unique, powerful features that Google Sheets has, as a cloud-based piece of software.

Comments (and Notes)

Want to add some context to numbers in the cells of your Sheets, without having to add extra columns or mess up your formatting?

Add a comment to a cell!

You can tag people (via their email address) who you want to see the comment too. They can reply and mark it resolved once it’s been acted upon.

You can also add simple notes to cells as well if you wish.

Comments and Notes can also be deleted when not required anymore.

To add a comment to a cell, first select the cell, then right click to bring up the menu of options. Select “Insert comment” and then simply type in your comment.

To tag somebody in your comment, type the plus sign (+) and their name or email address (you’ll see auto-complete options from your contacts, so you shouldn’t have to type in the whole email address).

Google Sheets comments

You’ll notice a small orange triangle in the top right corner of the cell to indicate the comment. The comment will show up when you hover over this cell. If you click on the cell, it’ll also add orange shading to the cell background.

Comments can be edited, deleted, linked to, replied to and resolved (comment disappears from Sheet and is archived).

You can reach and control all the Comments in your Sheet from the big Comments button in the top right of the screen, next to the blue Share button.

(The first time you tag someone in a comment, you’ll be asked to share the Sheet with them. See more on this below.)

You can also add a note to cells in the same way (look for it in the menu next to Insert Comment). It’s like a pared down version of a comment, intended for your own reference.

Share your sheets

(If you just added a comment and tagged someone else, as shown above, then you may have already done this step!)

You can share your Google Sheets with other people. Since it’s on the cloud, they can access your Sheet and see the same, live Sheet that you’re in.

In other words if you make changes, they will show up automatically and in near real-time for everybody viewing the Sheet.

You can have multiple people viewing and working on the same Sheet.

Essentially you have three options to share you Sheet with:

  • View-only access, so that person can not change or comment on any data
  • Comment-only access, so that person can add comments but still not make any changes to the data in the Sheet
  • Editing access, so that person can make changes to the sheet (including comments)

The Sharing options are found by clicking on the big blue button in the top right corner, which will open up the Sharing settings:

Google Sheets sharing settings

You can grab the link (the URL) to the Sheet, choose the share setting (view/comment/edit) and then share that link with people you want to see the Sheet. (1)

Or, you can enter someone’s email address directly, choose the share setting (view/comment/edit) and then share the Sheet directly with the person. (2)

If you want to review the sharing settings or have even more control, click the Advanced options buttons. (3)

The Advanced sharing settings window:

Google Sheets sharing advanced settings

Here you can:

> Grab the sharing link (1)
> Review who has access (2)
> Change the access rights of anyone listed (3)
> Invite new people to access the Sheet (4)
> Change the advanced owner settings, to restrict who can control the sharing settings and specific view/comment rights (5)
> Confirm when you’re finished (6)

I’ve used the link from the sharing settings to share the template for this tutorial with you!

Real-time Collaboration

Ok, so you’ve shared your Sheet with someone. If they open it whilst you’re still working in the Sheet you’ll see their cursor show up on whatever cell (or range) they’ve selected. It’ll be a different color, for example green to your blue.

If they enter data or delete data you’ll see it happening in real-time!

Real time collaboration in Google Sheets

In this case my active cell is the blue-outlined cell. I see somebody else, denoted by the green-outlined cell, show up in this Sheet and enter data into a few cells before deleting it.

Magic!

Want a copy of the template from this tutorial?
Click here to create your own copy >>

5. How to use Google Sheets: Intermediate techniques

Freeze panes

This is one of the most useful tricks you can learn in Google Sheets, which is why I’m recommending you learn it today.

Sooner or later you’ll work with a table of data that continues beyond the area you see on the screen (right now for example, I can see as far as row 26, but it depends on your screen size and other factors).

When you scroll down to look at data further down in your table, you lose the column headings off the top of your screen, and therefore can’t see the context of your columns.

Of course, scrolling up and down to see what the column headings are makes no sense. It’s a sure path to spreadsheet errors and insanity!

Have a look at this data table showing the tallest buildings in the world, which extends below the bottom of what you can see on a single screen in Sheets. Scrolling results in the heading row disappearing, so you no longer know which columns are which:

Google Sheets scrolling insanity

What you need to do is freeze the heading rows.

Thankfully it’s super easy.

Click on the row number of the row with your column headings in (e.g. row 3), then from the menu choose:

View > Freeze > Up to current row (3)

Google Sheets Freeze panes

Now your headings will stay in place. Ah, that’s much better!

You can do the same with columns, if you wished to freeze names for example, so you can scroll horizontally across lots of columns of data.

Relative/Absolute references

This is arguably the hardest concept to grasp in this tutorial. If you understand it and can apply it, then you have a really good understanding of how spreadsheets work and you’re well on your way to being a skilled user.

Suppose you have some data in cell A1 and you enter the following formula into cell B1:

= A1

This formula will retrieve whatever data is in cell A1 and show it in cell B1.

Now copy the formula (Cmd + C on a Mac, or Ctrl + C on a PC) and paste it (Cmd + V on a Mac, or Ctrl + V on a PC) somewhere else on your Sheet, for example cell D5.

Nothing will show up in D5. In fact you may be wondering whether your copy-paste worked. Have a look in the formula bar and you should now see this however:

= C5

The formula is there, but it points to a different cell, not A1, so does not show the data from A1.

But it still points to the cell that is ONE TO THE LEFT AND ON THE SAME ROW as the original formula.

Ah ha! Eureka!

The formula copied perfectly, keeping the same structure, pointing to the cell on the left.

This amazing property is called a relative reference, meaning it’s in relation to the cell where the formula is (e.g. one to the left).

That’s why you can drag formulas down columns and they’ll change automatically to calculate with data from their row.

Got that?

Now then, if you want to fix your formula (for example so it always point to cell A1) then you’ll want to use what’s called Absolute Referencing.

We lock the cell reference in the formula, so Google Sheets knows to not move the reference when the formula is moved.

The syntax uses a dollar sign, $, in front of the column reference and in front of the row reference to lock them each respectively, like so:

= $A$1

Now, wherever you copy this formula, the output will always point to cell A1 and return you the data from cell A1.

Google Sheets Absolute and Relative references

Note, you can just lock the column or just lock the row reference, and leave the other part as a relative reference, but that is beyond the scope of this tutorial.

Working with formulas across sheets

Sticking with the topic of referencing other cells for the moment, how does one go about linking to data on a different Sheet?

Returning once again to our basic gym membership table for dead famous authors, in Sheet 1, let’s retrieve the table heading and print it out in Sheet 2 with this formula, entered into cell A1 on Sheet 2:

= Sheet1!A1

Note the exclamation point at the end of the reference to Sheet1, i.e. Sheet1!

Now let’s do a simple sum of data on Sheet 1, but show our answer on Sheet 2.

In cell A3 in Sheet 2, enter the following formula:

= SUM( Sheet1!F4:F7 )

This will return the sum of the range of cells F4 to F7 in Sheet 1 and print out the answer in Sheet 2.

Basic conditional formatting

Conditional formatting is a powerful technique to apply different formats (for example background shading) to cells based on some conditions.

Let’s see an example of conditional formatting, that is, formatting based on variable conditions.

For example, in a financial model, you might show positive asset growth with a green font color and a light green background, whilst negative growth might be shown with red lettering on a light red background. This gives extra context to your numbers, and pre-attentive attributes (the colors) help to convey the message more efficiently.

Taking the plain copy of the membership table (hit Cmd + Z on a Mac, or Ctrl + Z on a PC to go back if you need), highlight the final column of the membership fee, then from the menu:

Format > Conditional formatting

Here you can choose a rule, for example, values less than $100 and highlight them red:

How to use google sheets conditional formatting

Notice the range is shown (1), then a drop-down menu to choose a rule (2) and then the formatting option (3), which is default red in this case, although you can go completely custom if you choose.

The power of conditional formatting is to highlight data dynamically. The formatting is based on a rule, so if another value should drop below the threshold ($100 in this case), it will trigger the formatting rule and be highlighted red.

Advanced Resource: Conditional formatting to show % change

Sorting data

Sorting your data is a common request, for example to show transactions from highest revenue to lowest revenue, or customers with the greatest number to least number of purchases. Or to show suppliers in alphabetical order. You get the idea.

So let’s sort the dead famous authors gym membership table, from earliest members to most recent members, i.e. we’ll sort our table based on the date column.

Highlight the whole table, including the header row. Then from the menu:

Data > Sort range…

and be sure to check the “Data has header row” option. Then you can select the column you want to sort by, and sort option from A to Z, or Z to A.

Google Sheets sort range

This re-sorts the table, showing the earliest members first:

Google Sheets sorted table

Filtering data

The next step after sorting your data is to filter it to hide the stuff you don’t want to see. Then you can just look at the data that is relevant to the problem at hand.

Taking the world’s tallest buildings data again, let’s apply a filter to only show skyscrapers built before the 2000s.

Click somewhere inside the data table (click on a cell containing data in the table), and then add the filters from the menu:

Data > Filter

or by clicking this icon on the menu bar above your Sheet:

Google Sheets Filter

You’ll notice a light green shading applied to row and column headings of your filtered table, and also a green border around your table. Most importantly though, you’ll now have little green filter buttons in each of your heading cells.

To filter out all the buildings built in the year 2000 or after, click the little green triangle next to the column heading Built, to bring up the filter menu.

You’ll notice you can manually select or de-select items to show. Let’s create a rule this time though.

Under the “Filter by condition” section, choose “Less than” and enter 2000 into the value box:

Google Sheets filter

Hit OK.

Ta-da!

You’ll see a reduced table with just 9 results. The 9 skyscrapers built before the year 2000:

Google Sheets filtered table

To remove the filter, click the green triangle button again (now solid green) and under the “Filter by conditions” set the rule to “None”.

There’s also a native FILTER function, by which you can formulaically filter your data.

Advanced Resource: How to use the FILTER function

Adding Charts

As a final exercise with the tallest building data, let’s draw a chart to show the buildings built by year, so we can see the trend graphically.

First, let’s sort the table by the year Built column, from oldest to newest. We can do this using the sort function that is provided in the menu when you click on the green triangle (from the filter).

Then highlight this single column called Built and from the menu:

Insert > Chart

This creates a default chart in your window and opens the chart editing tool in a sidebar.

Here you can change your data range and chart type, as well as a multitude of chart custom formatting options (which are also generally accessible by clicking on the elements directly in the chart).

Insert chart

The chart is an object in your Sheet now. Click it to select it, so it has a blue border around it. You can resize it and drag it to move it, just as you would with an image.

Using the Explore feature

The robots are coming!

Soon we won’t have to create complex formulas or charts ourselves. We’ll simply ask our Sheet to do it for us. Sound far fetched?

Well, you can do that now! The future is here.

(Soon we won’t have to open Google Sheets at all, we’ll simply type, or more likely speak, our data questions into a dashboard console and out will pop the answers, but I digress.)

Google Sheets has a feature called Explore, powered by Machine Learning/AI/Deep Learning/Neural Network sorcery, that will ingest your data, analyze it and show you some common answers (like the SUM, COUNTs etc. we’ve seen so far) and basic charts:

Google Sheets explore button

Let’s see an example, using the data about the world’s tallest buildings. Highlighting the column showing the years when each skyscraper was built and then clicking on the Explore button (bottom right corner) leads to these insights:

Google Sheets Explore insights

52 buildings in my set. The earliest tower on the list was built in 1931 (the venerable Empire State Building!) and the most recent built in 2017. The average of all the years built is 2007.

Some interesting and useful data points, without having to do a lick of work!

I mentioned charts, so let’s see an example of that with the Height (ft) column. Google Sheets Explore creates a Histogram for us, showing the distribution of tower heights:

Google Sheets Explore chart insights

We also get this interesting and insightful summary:

“Ranges from 1,148 to 2,717, but 80% of values are less than or equal to 1,480.”

Wow! At a glance we have the min and max heights, but more impressive, we know that 80% are under 1,480ft tall.

Not all of the “insights” are useful, and I don’t use this feature much myself yet, but it shows a glimpse of how we’ll all work with Sheets in the future.

If you’re still not worried about AI taking over your job (whether that’s data analysis work or something else) in the next 10 to 50 years, then have a read of this article.

BONUS: VLOOKUP

No “How to use Google Sheets” article would be complete without at least a quick look at the VLOOKUP function.

Why?

Because it’s the most famous function. Tell anyone you work with data and spreadsheets and they’ll immediately ask, “yes, but do you know how to do a VLOOKUP?”

It’s a good bellwether for spreadsheet competency, even though there are ultimately better ways to work with data. It’s also a relatively advanced formula compared to what we’ve seen so far, so if you can understand it, it bodes well.

What does it do then?

It’s used to search for a term and return information about that term from a different table.

Generally it’s used when you have two tables that share some common attribute (e.g. a name, or ID number, or email), but otherwise store different information. Suppose you want to bring this information together though. Well you can, and you link the information via that common attribute.

One table might have details an employee’s name and address, and the other table might have their name and work details like title and salary. You can use the VLOOKUP function to bring these bits of data together in a single table.

The syntax is as follows:

= VLOOKUP( search_term, table_to_search, column_number, FALSE )

In words: you select a search term which you search for in the first column of the search table. If you find it, you return a piece of information from the search table that relates to the search term (because it’s on the same row). The column number refers to which column of the search table you return the data from (1 being the column you searched in, so typically this number is 2 or greater).

Let’s see an example, by adding addresses to the dead famous authors gym membership table.

I’ve added a second table to Sheet 1, showing the addresses of our dead famous authors. What I’d like to do is add that data to the original gym table.

I want to do it efficiently with the VLOOKUP formula, rather than adding them manually. Not only is it much, much faster with larger tables (imagine ten thousand rows of data), but it’s also less error prone.

The address table is in columns I and J, with the names in column I and addresses in column J.

So I’ll search for my author name in column I and return the address from column J, and print the output into whichever cell I created my formula in.

The formula is:

= VLOOKUP( C4, $I$3:$J$7, 2, FALSE )

And this is what’s happening:

Google Sheets VLOOKUP

I search for the name (1) in the search table (2) and return the data from column 2 of the search table (3).

I don’t expect you to understand all of this immediately (and there’s a lot more to this formula than what I’ve shown here), but if you try it out and persevere, you’ll get there and realize it’s actually not too difficult.

(The FALSE argument, the final piece of information in the VLOOKUP formula means you want to do an exact match. 99.9% of the time you use a VLOOKUP, you’ll want to use FALSE.)

Advanced Resource: Dynamic charts with VLOOKUP

Want a copy of the template from this tutorial?
Click here to create your own copy >>

6. Next steps

The old adage “Practice makes perfect” is as true for Google Sheets as anything else, so hop to it!

Keep up-to-date with new articles, course launches and exclusive offers, by signing up for my Google Sheets newsletter, and get my free 80-page ebook on Google Sheets tips.

When you’re ready, check out the intermediate and advanced Google Sheets tutorials on this site.

Check out my Google Sheets Essentials course for beginners.

Check out my free Advanced Formulas 30 Day Challenge course.

If all else fails, ask for help on the Google Sheets forum.

Wow, that’s it for this Google Sheets tutorial! Happy spreadsheeting!

This is an updated version of an article that was previously published. We update our tutorials to ensure they’re useful for our readers.