Google Sheets and Apps Script Blog Posts From Ben Collins

IF Function in Google Sheets

The IF function in Google Sheets is used to make decisions with your data. It’s from the Logical family of functions in Google Sheets.

IF function Google Sheets

In this tutorial, you’ll see how to use IF formulas in Google Sheets to make decisions with your data.

What does the IF Function do?

At its heart, the IF function is a test that evaluates to a true or false value with a defined behavior if the outcome is true and a different behavior when the outcome is false.

Let’s see an example.

Suppose you have two columns of numbers in A and B and you want to compare each row to see which column has the larger number.

You would use the IF function in Google Sheets to do this!

IF function Google Sheets

=IF( A2 > B2 , "Column 1 is larger" , "Column 1 is less than or equal to column 2" )

Inside the IF formula, the first expression A2 > B2 checks whether the value in cell A2 is greater than the value in cell B2.

The outcome of this test is either a TRUE or a FALSE value.

The IF function requires a TRUE or FALSE value for this first argument.

Next up, you specify what you want to happen when the result is true. In this example the output of the function in the cell is “Column 1 is larger”

The final argument is the value you want to show if the result is false. In this example, that means the number in column A was smaller (or equal to) the value in column B. In this case, the output of the function is “Column 1 is less than or equal to column 2”.

IF Function in Google Sheets: Syntax

=IF(logical_expression, value_if_true, value_if_false)

You might hear it referred to as an IF function, an IF formula or even an IF statement, but they all mean the same thing.

It takes 3 arguments:

logical_expression

An expression that gives a TRUE or FALSE answer, or a cell that contains a TRUE or FALSE value.

value_if_true

The value displayed by the IF function if the logical expression has the TRUE value.

value_if_false

The value displayed by the IF function if the logical expression has the FALSE value.

IF function template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open in an Incognito window you’ll be able to see it.

The IF function is also covered in the Day 2 lesson of my free Advanced Formulas 30 Day Challenge course.

You can also read about it in the Google documentation.

Calculations with the IF Function in Google Sheets

IF functions can be combined with other functions to perform calculations on values above a certain threshold for example.

When the logical expression is true, do this calculation, otherwise leave the value as it is.

Perhaps your business has a performance bonus structure that pays out a 20% bonus above a certain threshold of client revenue. Use an IF statement to determine if the threshold has been met and then put the calculation in the true field:

=IF( A1 > 100000 , A1 * 0.2 , 0 )

IF formula Calculation

Using The IF Formula for Classification

Another example use case for the IF function is to classify items.

For example, you might want to segment your customers into long-term customers and new customers, based on whether they’ve been a client for 12 months or longer.

Suppose column A was a column containing the number of months a client has been with you, the following IF formula would classify them into long-term or new clients:

=IF( A1 >= 12 , "Long-term client" , "New client" )

IF formula Classification

This kind of segmentation is useful in lots of different ways.

It lets you compare the retention and churn metrics of the two groups. You can run different marketing campaigns to different segments of your data. Or, maybe you just want to send a “Thank you, you’re awesome!” card to your long-term clients.

Nested IF Formula in Google Sheets

Sometimes one IF function alone isn’t enough.

Inside the TRUE or the FALSE arguments you can nest another IF function.

It looks complex but if you apply the onion method for working with formulas and work in layers, you’ll see it’s not difficult.

Working from the outside in, it checks if the value is greater than 50. If that is true, then the formula is done and it displays “Greater than 50”.

But if that’s false, it must mean the value is either less than 50 or equal to 50, so we use a second, nested IF to check for that:

=IF(A2 > 50,"Greater than 50",IF(A2 < 50,"Less than 50","Equal to 50"))

nested IF formula in Google Sheets

If your nested IF formulas get too complicated, you might want to try the SWITCH function.

IF Formulas and Checkboxes

Combining checkboxes with IF formulas is a powerful and versatile technique to use in your Sheets.

Checkboxes are actually just TRUE or FALSE values in disguise!

With a checkbox in cell A2, it can only ever have a TRUE or FALSE value. So it can be plugged into the IF function’s first argument directly!

= IF( A2 , "It's checked!" , "It's not checked." )

IF function checkbox

This sort of logic is useful in many settings, for example a dashboard where you want to show/hide additional context for specific numbers or charts.

Direct TRUE or FALSE Input

In the example above, you had a formula already in column A that contained a TRUE or FALSE value.

Since the first argument of the IF statement is looking for a TRUE of FALSE, you only need to reference that cell directly without testing for TRUE / FALSE.

Your IF formula would look like this:

=IF( A1 , "Something true" , "Something false" )

There is NO need to test for TRUE or FALSE again. So you should not see an IF function like this:

=IF( A1 = TRUE , "Something true" , "Something false" )

In fact, you should never see an “= TRUE” or “= FALSE” in an IF statement because they’re redundant.

Replace numerical IF formulas with MIN or MAX functions

Suppose you have a column of values that you want to cap at a certain level, so that everything above a threshold value, e.g. 200, gets set to that value.

Most of us would approach this by writing an IF formula that checks whether the value is above 200 and then set it to 200 if TRUE, or the actual value if FALSE, like so:

IF formula in Google Sheets example

However, you can replace the whole IF function with a much more succinct MIN function, which chooses the value 200 if the actual value is larger, since 200 is the minimum:

=MIN( A2 , 200 )

Min Replace If function

Similarly, we can use the MAX function to replace IF statements when we’re looking at a threshold on the low side.

It’s good practice to write efficient formulas because it’s quicker and you’re less likely to make mistakes.

IFERROR Function In Google Sheets

The IFERROR function in Google Sheets is a useful function to have in your Google Sheets toolbox. And it’s easy to learn.

In this tutorial you’ll learn how it works and see common use cases.

There are three principal uses for the IFERROR function:

Replacing error values with a fallback value
Catching errors and replacing default error messages with custom error messages
In Array Formulas

IFERROR Function Syntax

=IFERROR(value, [value_if_error])

It takes two arguments.

The first argument is some value that you want to check for errors (see here for different types of errors in Google Sheets).

The second argument, which is optional, is the value you want to show if an error is detected. If this argument is omitted then a blank value is returned instead of an error.

When there is no error, the original value (set by the first value) is shown.

Let’s see an example of an IFERROR formula.

Suppose you have this formula in cell B1:

=IFERROR(A1,0)

This formula checks the value of cell A1.

If cell A1 does not contain an error (e.g. it has a value like 10, or the word “Ben” or a date etc.) then it just returns that value.

However, if it detects an error in that cell (e.g. #N/A or #REF! etc.) then it returns the value 0.

IFERROR function in Google Sheets

IFERROR function example template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open it in an Incognito window you’ll be able to see it.

Using The IFERROR Function To “Fix” Errors In Data

Suppose you have a column of calculated values in your Google Sheet, based off data elsewhere in the Sheet perhaps.

You want to change any errors to 0 (or whatever value you want) so that the column doesn’t contain any errors like #NA(). The #NA() error prevents you calculating a total for that column for example.

IFERROR Formula in Google Sheets

The formula in column B of this example is:

=IFERROR(A2,0)

As you can see, the SUM function only calculates a total for column B where we replaced the #N/A errors with 0’s.

Using The IFERROR Function With Nested Formulas

Another common use case for IFERROR is as a wrapper around existing formulas that might output an error.

For example, if you’re using a VLOOKUP function and the search value is not found, it’ll return a #N/A error, which you may not want in your dataset.

Use IFERROR in front of the VLOOKUP and you can specify a custom error message e.g. “Search value not found”.

In this example we’re using VLOOKUP to search for a name in another table and return the property type that person lives in. In this case “Mr Ben Collins” was not found in the dataset. Compare columns B and C to see the different outputs with or without the IFERROR:

IFERROR Function with Vlookup

The formula in column C is:

=IFERROR( VLOOKUP( A2 , data!$B$1:$H$21 , 2 , false ), "Person not found" )

Using the IFERROR Function With Array Formulas in Google Sheets

The IFERROR is useful when working with Array Formulas in Google Sheets.

Consider this Array Formula in cell B2:

=ArrayFormula(IF(A2:A <> "",A2:A * 100,IFERROR(1/0)))

It works by looking down column A and wherever there’s a value in column A, it will multiple it by 100 and put the answer in column B.

Where cells in column A are empty, it returns a blank cell in column B.

The 1/0 gives a #DIV/0! error. The IFERROR(1/0) returns a blank cell.

It’s superior to just using a blank string "" because formulas will count the empty strings "", e.g. COUNTA() will count the blank cells if you use “” but not if you use IFERROR(1/0). In other words, it gives you a truly blank cell.

It’s the type of Array Formula commonly used in combination with Google Form data, where the calculations will be automatically calculated as new Form data arrives.

You can also read about it in the Google documentation.

SUBTOTAL Function in Google Sheets

The SUBTOTAL function in Google Sheets is a powerful function for working with data.

There are three principal uses for the SUBTOTAL function:

Calculating Subtotals for lists of data
Calculating metrics with filtered and/or hidden rows of data
As a dynamic function selector

It’s versatile, as you’ll see in this tutorial.

However, it’s not well known and I suspect it’s vastly under utilized. It’s not an easy function for beginners to use, because it involves using a function code to control how it operates, as you’ll see below.

Continue reading SUBTOTAL Function in Google Sheets

Sheet Sizer! Build A Tool To Measure Your Google Sheets Size With Apps Script

In this tutorial, you’ll use Apps Script to build a tool called Sheet Sizer, to measure the size of your Google Sheets!

Google Sheets has a limit of 10 million cells, but it’s hard to know how much of this space you’ve used.

Sheet Sizer will calculate the size of your Sheet and compare it to the 10 million cell limit in Google Sheets.

Sheet Sizer: Build a sidebar to display information

Step 1:
Open a blank sheet and rename it to “Sheet Sizer”

Step 2:
Open the IDE. Go to Tools > Script editor

Step 3:
Rename your script file “Sheet Sizer Code”

Step 4:
In the Code.gs file, delete the existing “myFunction” code and copy in this code:

/**
* Add custom menu to sheet
*/
function onOpen() {

  SpreadsheetApp.getUi()
    .createMenu('Sheet Sizer')
    .addItem('Open Sheet Sizer', 'showSidebar')
    .addToUi();
}

/**
* function to show sidebar
*/
function showSidebar() {
 
  // create sidebar with HTML Service
  const html = HtmlService.createHtmlOutputFromFile('Sidebar').setTitle('Sheet Sizer');
 
// add sidebar to spreadsheet UI
  SpreadsheetApp.getUi().showSidebar(html);
}

There are two functions: onOpen, which will add the custom menu to your Sheet, and showSidebar, which will open a sidebar.

The text between the /* ... */ or lines starting with // are comments.

Step 5:
Click the + next to Files in the left menu, just above the Code.gs filename.

Add an HTML file and call it “Sidebar”. It should look like this:

Apps Script HTML files

Step 6:
In the Sidebar file, on line 7, between the two BODY tags of the existing code, copy in the following code:

<input type="button" value="Close" onclick="google.script.host.close()" />

This code adds a “Close” button to the sidebar.

Your Sidebar file should now look like this:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>

    <input type="button" value="Close" onclick="google.script.host.close()" />

  </body>
</html>

Step 7:
Don’t forget to hit Save

Step 8:
Select the Code.gs file, then select the onOpen function in the menu bar (from the drop down next to the word Debug). Then hit Run.

Step 9:
When you run for the first time, you have to accept the script permissions. If you see an “App isn’t verified” screen, click on Advanced, then “Go to…” and follow the prompts. (More info here.)

Step 10:
After authorizing the app in step 8, jump back to your Google Sheet. You should see a new custom menu “Sheet Sizer” in the menu bar, to the right of the Help menu.

Click the menu to open the sidebar.

Step 11:
Close the menu using the button.

Here’s what you’ve built so far:

Google Sheets sidebar with Apps Script

Sheet Sizer: Add a new button and functionality to the sidebar

Step 12:
In the Sidebar file, after the first BODY tag, on line 6, and before the INPUT tag on line 7, add a new line.

Paste in the new button code:

<input type="button" value="Get Sheet Size" onclick="getSheetSize()" />

When clicked, this will run a function called getSheetSize.

Step 13:
Add the getSheetSize function into the Sidebar file with the following code.

Copy and paste this after the two INPUT tags but before the final BODY tag, on line 9.

<script>
function getSheetSize() {
  
  google.script.run.auditSheet();

}
</script>

When the button is clicked to run the getSheetSize function (client side, in the sidebar), it will now run a function called auditSheet in our Apps Script (server side).

Step 14:
Go to the Code.gs file

Step 15:
Copy and paste this new function underneath the rest of your code:

/**
* Get size data for a given sheet url
*/
function auditSheet() {
  // get Sheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
 
  // get sheet name
  const name = sheet.getName();

  // get current sheet dimensions
  const maxRows = sheet.getMaxRows();
  const maxCols = sheet.getMaxColumns();
  const totalCells = maxRows * maxCols;

  // output
  SpreadsheetApp.getUi().alert(totalCells);
}

This gets the active Sheet of your spreadsheet and calculates the total number of cells as max rows multiplied by max columns.

Finally, the last line displays an alert popup to show the total number.

Step 16:
Back in your Google Sheet, run Sheet Sizer from the custom Sheet Sizer menu.

When you click on the “Get Sheet Size” button, you should see a popup that shows the number of cells in your Sheet:

Sheet Sizer: Display the Sheet size in the sidebar

Step 17:
Delete this line of code in the auditSheet function:

SpreadsheetApp.getUi().alert(totalCells);

Step 18:
Paste in this new code, to replace the code you deleted in Step 4:

// put variables into object
const sheetSize = 'Sheet: ' + name +
  '<br>Row count: ' + maxRows +
  '<br>Column count: ' + maxCols +
  '<br>Total cells: ' + totalCells +
  '<br><br>You have used ' + ((totalCells / 5000000)*100).toFixed(2) + '% of your 10 million cell limit.';

return sheetSize;

Now, instead of showing the total number of cells in an alert popup, it sends the result back to the sidebar.

Let’s see how to display this result in your sidebar.

Step 19:
Go to the Sidebar file and copy this code after the two INPUT tags but before the first SCRIPT tag:

<div id="results"></div>

This is a DIV tag that we’ll use to display the output.

Step 20:
Staying in the Sidebar file, replace this line of code:

google.script.run.auditSheet();

with this:

google.script.run.withSuccessHandler(displayResults).auditSheet();

This uses the withSuccessHandler callback function, which we’ve called: displayResults

It runs when the Apps Script function auditSheet successfully executes on the server side. The return value of that auditSheet function is passed to a new function called displayResults, which we’ll create now.

Step 21:
Underneath the getSheetSize function, add this function:

function displayResults(results) {
  // display results in sidebar
  document.getElementById("results").innerHTML = results;
}

When this function runs, it adds the results value (our total cells count) to that DIV tag of the sidebar you added in step 7.

Step 22:
Back in your Google Sheet, run Sheet Sizer from the custom Sheet Sizer menu.

When you click on the “Get Sheet Size” button, you should see a popup that shows the number of cells in your Sheet:

Apps Script to measure Sheet Size

Sheet Sizer: Handle multiple sheets

Step 23:
Modify your auditSheet code to this:

/**
* Get size data for a given sheet url
*/
function auditSheet(sheet) {

  // get spreadsheet object
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // get sheet name
  const name = sheet.getName();

  // get current sheet dimensions
  const maxRows = sheet.getMaxRows();
  const maxCols = sheet.getMaxColumns();
  const totalCells = maxRows * maxCols;

  // put variables into object
  const sheetSize = {
    name: name,
    rows: maxRows,
    cols: maxCols,
    total: totalCells
  }

  // return object to function that called it
  return sheetSize;

}

Step 24:
In your Code.gs file, copy and paste the following code underneath the existing code:

/**
* Audits all Sheets and passes full data back to sidebar
*/
function auditAllSheets() {

  // get spreadsheet object
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();

  // declare variables
  let output = '';
  let grandTotal = 0;

  // loop over sheets and get data for each
  sheets.forEach(sheet => {

    // get sheet results for the sheet
    const results = auditSheet(sheet);
    
    // create output string from results
    output = output + '<br><hr><br>Sheet: ' + results.name +
      '<br>Row count: ' + results.rows + 
      '<br>Column count: ' + results.cols +
      '<br>Total cells: ' + results.total + '<br>';

    // add results to grand total
    grandTotal = grandTotal + results.total;

  });

  // add grand total calculation to the output string
  output = output + '<br><hr><br>' + 
    'You have used ' + ((grandTotal / 5000000)*100).toFixed(2) + '% of your 10 million cell limit.';

  // pass results back to sidebar
  return output;

}

This adds a new function, auditAllSheets, which loops over all the sheets in your Google Sheet and calls the auditSheet function for each one. The results for each Sheet are joined together into a result string, called output.

Your Code.gs file should now look like this.

Step 25:
Jump back to your Sidebar file and replace this line of code:

google.script.run.withSuccessHandler(displayResults).auditSheet();

with this:

google.script.run.withSuccessHandler(displayResults).auditAllSheets();

The callback function is the general auditAllSheets function, not the specific individual sheet function.

Step 26:
Back in your Google Sheet, add another sheet to your Google Sheet (if you haven’t already) and run Sheet Sizer.

It will now display the results for all the sheets within your Google Sheet!

Sheet Sizer: Add CSS styles

This step is purely cosmetic to make the sidebar more aesthetically pleasing.

Step 27:

Add these CSS lines inside the HEAD tags of the sidebar file:

<!-- Add CSS code to format the sidebar from google stylesheet -->
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">

<style>
  body {
    padding: 20px;
  }
</style>

Step 28:

Add DIV BLOCKS to the elements of the sidebar:

<div class="block">
  <input type="button" class="create" value="Get Sheet Size" onclick="getSheetSize()" /> 
</div>

<div class="block">
  <input type="button" value="Close" onclick="google.script.host.close()" />
</div>

<div class="block">
  <div id="results"></div> 
</div>

Sheet Sizer: Global Variables

Step 29:

One final improvement from me is to move the 10 million cell limit number into a global variable.

Currently, it’s buried in your script so it’s difficult to find and update if and when Google updates the Sheets cell limit.

It’s not good practice to hard-code variables in your code for this reason.

The solution is to move it into a global variable at the top of your Code.gs file, with the following code:

/**
 * Global Variable
 */
const MAX_SHEET_CELLS = 10000000;

And then change the output line by replacing the 5000000 number with the new global variable MAX_SHEET_CELLS:

// add grand total calculation to the output string
output = output + '<br><hr><br>' + 
  'You have used ' + ((grandTotal / MAX_SHEET_CELLS)*100).toFixed(2) + '% of your 10 million cell limit.';

Here is your final Sheet Sizer tool in action:

Click here to see the full code for the Sheet Sizer tool on GitHub.

Next steps: have a go at formatting the numbers shown in the sidebar, by adding thousand separators so that 1000 shows as 1,000 for example.

I Left My Corporate Accounting Job 7 Years Ago. Here’s How I Built A Career As An Educational Entrepreneur

Ben Collins Journey to course creator

I remember handing in my notice seven years ago.

My palms were sweaty.

Several times I walked towards the corner office but turned around, on the pretense of needing to do something else first.

Finally, there was nothing left but to do it.

As I walked, robotically, towards my boss’s office, my pulse quickened and heat rose through my head.

I was so focused on knocking on the door, on willing myself past that point of no return, that when I entered the boss’s office, my carefully rehearsed words spilled out my mouth in a nervous jumble. Words I’d confidently spoken a hundred times in my head.

In my head I had pictured a triumphant scene – the “I quit!” scene – but the reality was a somewhat awkward, anticlimactic conversation.

The news came as a surprise to my boss. He wanted to understand.

“No, it’s not the work…”

“That’s a generous offer to stay, but I’ve made up my mind…”

“I want to focus on getting a job in the tech industry…”

That last statement sounded hollow and vague, even to me.

Did I believe it myself? What did it even mean? I felt the heat rising in my head again.

What will I do next?

Truthfully, I didn’t know.

News traveled fast along the corridors and soon the whole office knew.

Most were incredulous that I would leave a job without a new job to go to. I projected confidence and recited my spiel about wanting to break into the tech industry because it’s my passion.

But in reality, I felt like a vertigo sufferer on a cliff edge, my head swimming.

I was saved by my innermost kernel: my determination to succeed and make a dent in the world.

It’s my most powerful ally, always there to fight my cause. All I had to do was learn how to channel it.

Two weeks later, the change was real and permanent. I woke up and didn’t go to the office. It was a disorientating feeling that took me years to acclimate to.

I spent that summer cycling around the US with my brother, then I got married in the fall. Yet through those joyous events, I carried anxiety, not knowing exactly where my career would go next.

The Wilderness Years: 2014 – 2015

Act 1: A Forensic Accounting Newsletter

My first entrepreneurial idea was to start a paid newsletter for the Forensic Accounting industry, the industry where I’d worked for 8 years and just left.

It didn’t exist, it was specialist knowledge that people would pay for, and my experience qualified me to deliver it.

Paid newsletters are in vogue right now, but not back in 2014. But that was attractive. It added a technology angle to this project: I had to figure out how to deliver and charge for the newsletter.

I spent a month working hard on this idea.

I interviewed people in the industry. I wrote many draft editions of the email. I created a business plan and put together a list of 100 contacts in the industry who would receive the first newsletter.

I was just a few days shy of sending the first email when I knew something wasn’t right.

I wasn’t excited by this idea. At all.

In fact, I wanted to get away from the corporate accounting world and blaze my own trail. This wasn’t the solution I was looking for.

I clearly remember the conversation with my wife:

“I can’t do this. I’m not passionate about this industry so I won’t be successful. I’m passionate about the technology around the newsletter and the design of it, but not the contents.”

(By the way, I still think this newsletter is a good idea. Just not a good idea for me.)

It was back to the drawing board.

Act 2: Wannabe Web Developer

I didn’t have another entrepreneurial dream at this stage. But I loved the VBA and SQL coding parts of my old job, so I knew what I wanted to do next.

I wanted to become a developer.

I signed up for a part-time web development course with the education startup General Assembly.

I poured my heart and soul into coding for the next six months.

I built a social learning application called UpLearn:

UpLearn Rails App

Crucially during this time, I did what every tech job seeker is advised to do and started this blog to share my knowledge.

I started the blog as a way to showcase my skills to future employers, not expecting it to become a business in its own right.

The very first post was about building a dashboard in Google Sheets, published on 31 October 2014.

I started applying for junior web developer roles in the spring of 2015. I applied to 10 positions but didn’t hear back from a single one. Nada. Zip. Not even an acknowledgment of my application.

It was hard to take. I was frustrated. I knew I could do it. Why couldn’t they see this?

I became more and more despondent with each passing day. Act 2 of my own career had yielded nothing either.

Looking back now, this period was full of doubt, as strong as any other time in my life.

Maybe I wasn’t cut out for this. Maybe my destiny was to be a lifetime accountant.

Maybe I could find a way to be happy doing that.

Maybe I should call up my old work again.

But before that happened, the universe sent a lifeline my way…

I got my first inbound client request.

Gaining Traction: 2016 – 2017

Act 3: Freelance Data Analyst

My first client messaged me after reading my Google Sheets dashboard post. He asked if I could build something similar for his real estate business. “Yes, of course!” I said.

Soon a trickle of inbound client work was coming from my website. Projects were all across the board: from Google Sheets dashboards to SQL data analysis to corporate training with Tableau.

It was just enough work to postpone the job hunt and call myself a freelancer.

When General Assembly announced they were looking for instructors for their new Data Analysis course, I applied.

I taught lots of different data analysis courses for General Assembly in 2015 and 2016, from 10-week part-time courses to 2-hour intensives.

Act 4: Freelance Google Sheets Developer

In 2017 I narrowed my focus to data analysis with Google Sheets and Apps Script.

No more Excel, Ruby on Rails, SQL, or Tableau.

I doubled down on the Google Sheets niche. My website traffic increased and the client requests kept rolling in.

Career Venn diagram

Hardly anyone else was writing about Google Sheets at the time, so it was a huge opportunity.

I was intimately familiar with the cottage industry built on the back of Microsoft Excel – consultants, course creators, bloggers – so I reasoned there was a good chance something similar would exist for Google Sheets years from now.

And I intended to be at the forefront of that industry.

Act 5: Course Creator

For years, I’d contemplated the idea of creating an online course, inspired by the Excel gurus like Chandoo, who made their living teaching spreadsheets.

In mid-2016 I committed to creating a Google Sheets course. Just one. I would give it a try and see what happened. If it was successful, then I could do it again. If not, then no problem, I could keep going with my freelance consulting.

I chose Dashboards as the first-course topic. I’d seen good traction on those posts on my website so I knew it was something people wanted to learn.

It took me about six months – alongside my existing consulting work – to create the dashboards and record the lessons, making many mistakes along the way.

I finally launched the Google Sheets Dashboard course in February 2017:

Version 1 of the Google Sheets Dashboard course

I made $7,000 in the week I launched the course, to an email list of around 2,000.

To me, it was a huge success. Certainly enough to convince me to create a second course.

I recorded and launched a Data Cleaning course – now part of the Data Analysis course – between March and May 2017. I was incentivized to finish this one because my second son was born at the end of May.

Building a Business: 2018 – 2020

By the end of 2017, I was making a modest salary from my business. But it was still some way short of my old corporate earnings.

I still struggled with the process of working for myself. In my previous jobs, I’d always had a boss (or drill sergeant) telling me what to do next.

Now I was on my own.

And I clearly needed help building good work habits so I could scale my business. I felt like I started from scratch with each new client project. I had no systems in place. I didn’t have a consistent pricing methodology.

After much encouragement from my wife, who has her own entrepreneurial experience, I hired a business coach.

My new business coach was immediately helpful.

She brought discipline to my work.

She taught me how to evaluate opportunities. How to sell my work. How to write convincing marketing copy.

And she helped me start a weekly Google Sheets tips newsletter in April 2018, which has grown to be one of the fundamental pillars of my business.

If you’re thinking about hiring a business coach, do it. It’s one of the best investments you can make for your business and yourself.

In 2019, Google invited me to be part of the Google Developer Expert program, as recognition of my work on Google Sheets and Apps Script. This has been an amazing community to be a part of. I’ve enjoyed working closely with other members and Google team members.

Act 6: Growing The Online School

Between 2018 and 2020, I released a whole series of online courses. They sold well.

My email list had grown to around 30,000 by mid-2020, largely because of people signing up to my free Advanced Formulas course and my Introduction to Apps Script course.

Most importantly, I’ve kept up my weekly newsletter habit, started in 2017, so that my audience is engaged and familiar with me and my course offerings.

But my courses looked amateur because I’d created all the graphics myself. And I knew the recording quality could be improved.

So my big goal for 2020 was to update the entire catalog.

At the beginning of 2020 I hired a design firm to create a new brand for this site and my courses.

I then embarked on a journey to overhaul every course in my catalog. I thought it would take me six months.

But the pandemic hit…

Work hours were reduced as I shared child care with my wife, who also has a career to sustain.

In the end, it took me a year to complete the course updates (not full-time). I uploaded the last video for the Automation With Apps Script course in late February 2021, complete with professional branding.

The Future: 2021 onwards

Now, in March 2021, my business is at an inflection point.

These are always fascinating times for an entrepreneur.

You’re reinventing yourself again. Starting over. Shedding your skin.

My existing online courses generate a six-figure profit annually, more than I used to make at my corporate job at a law firm.

But it’s time for me to grow again. Online education is evolving and some students crave more than on-demand courses alone can provide.

With the shift to zoom-first work culture in the past year, live training courses, often called Cohort-Based-Courses (CBCs), have exploded in popularity.

It’s not hard to understand why. Traditional on-demand online courses only work for the most motivated students, ones who have the discipline (and time!) to sit down and work through the content.

What the on-demand courses lack is accountability and community, two aspects that deliver huge benefits to students.

And that’s where these next-gen cohort courses come in. They offer you that accountability and community, as well as direct access to the expert teachers behind the videos. They’re a step closer to in-person training, combined with the convenience of remote learning.

I’m working on a new cohort course, called Pro Sheets Accelerator. It’s a five-week course, meeting twice a week for a 90-minute live session, plus office hours, community and a capstone project.

I’m really excited to bring this to life and share more details over the coming weeks.

My online, on-demand course library, now fully updated, remains available for students who prefer to learn that way. But this new live format course offers more opportunities for students to learn, grow and support each other.

Shifting my energy to this live cohort course model feels like a tipping point that will propel me forward to bigger things.

It took me years to get to this point, to find that sweet spot where my skills and experience matches what the market is looking for. Along the way, I’ve built an engaged audience of folks who can benefit from my work.

The first cohort of my live training course, Pro Sheets Accelerator, is just the beginning of this next Act.

My mission is to create a world-class online school, teaching productivity, data analysis, and workflow automation, using Google tools.

Onward and upward!

Ben Collins, March 2021