The INDIRECT function in Google Sheets is used to convert text strings into valid cell or range references.
For example, the INDIRECT function will convert the text string “A1” into the cell reference A1. The formula is:
=INDIRECT("A1")which is equivalent to this formula:
= A1It gives the answer 10 in the following example because that’s the value in cell A1:
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.
In this tutorial, you’ll see how to use IF formulas in Google Sheets to make decisions with your data.
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( 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(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.
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.
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 )
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" )
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.
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"))
If your nested IF formulas get too complicated, you might want to try the SWITCH function.
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." )
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.
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.
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:
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 )
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.
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:
=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.
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.
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.
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.
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:
The formula in column C is:
=IFERROR( VLOOKUP( A2 , data!$B$1:$H$21 , 2 , false ), "Person not found" )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.
The SUBTOTAL function in Google Sheets is a powerful function for working with data.
There are three principal uses for the SUBTOTAL function:
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.
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.
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:
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:
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:
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:
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!
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>
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.