This guide will explain how to skip optional arguments in functions in Google Sheets.
Some Google Sheets functions include optional arguments that default to a specific value if the user does not fill them out.
Many Google Sheets functions often require the user to fill up all of their arguments. For example, the SQRT function requires a numeric value as input. Failing to provide an input will lead to a #N/A error.
However, there are cases where Google Sheets functions have a mix of required and optional arguments. For example, the VLOOKUP function has four arguments: search_key, range, index, and is_sorted. The first three of these arguments are required, while the last one is optional.
There are also cases where the optional arguments are provided if the user needs to provide more than one of the required argument. The SUM function is a good example of this type of function. The syntax of the function is written like this:
=SUM(value1, [value2, ...])
This syntax indicates that the SUM function only needs a single argument but can accommodate more than one of the same type of input. In this case, the user can add an arbitrary amount of cell ranges together.
Let’s take a look at a sample use case where the user might need to skip an optional argument.
Suppose you want to use a financial function with six arguments. The first three arguments are required, and the last three are optional. How can we write our function to skip the first two optional arguments and fill out the last optional argument?
In the next section, we’ll provide a similar example and the methods you can use to skip one or more optional arguments in Google Sheets.
Now that we know when to skip optional arguments in functions, let’s take a look at a few methods we can use to do this in Google Sheets.
A Real Example of Skipping Optional Arguments in Functions
Let’s look at a few real examples that highlight different ways to skip optional arguments in functions.
Each example will indicate the name of the function, its proper syntax, and a sample formula that skips one or more optional arguments.
In the spreadsheet below, we used the CEILING.MATH function. This function rounds up a number to the nearest integer or multiple of significance. To make it easier for users to round up numbers to the nearest integer, Google Sheets performs the integer rounding by default if we skip the optional arguments.
Instead of using the formula =CEILING.MATH(2.456,1,0), we can simply use =CEILING.MATH(2.456) instead.
Next, we’ll take a look at how we can skip some optional arguments by leaving a blank space between commas.
Suppose you need to use the SPLIT function. The function allows you to parse a string of text and return text fragments into separate cells. The function has two required arguments: the text to split and the delimiter. SPLIT also includes two optional arguments: split_by_each and remove_empty_text.
If we want to leave split_by_each blank but fill out the remove_empty_text option, we can use the following formula:
=SPLIT("|1|2||4|5", "|",,TRUE)
In this final example, we’ll show a workaround allowing you to skip the optional arguments in the GROWTH function. This function accepts partial data about a growth trend and tries to fit an ideal exponential growth trend.
Only the first argument is required; however, you will encounter an error if you try to skip the second and third arguments to fill out the fourth. To handle this error, we can use the SEQUENCE function to place dummy values.
You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try skipping optional arguments in functions yourself, follow our guide in the next section!
How to Skip Optional Arguments in Functions in Google Sheets
This section will guide you through each step needed to skip optional arguments in Google Sheets.
Follow these steps to skip optional arguments in functions:
- First, write down the function name in the formula bar or active cell. Google Sheets will show a tooltip that will indicate which arguments are optional using square brackets.
- Next, write down all required arguments. In this example, the
SPLITfunction requires both the text and delimiter.
- If you want to skip all optional arguments, you can simply close the formula with a parenthesis.
- Hit the Enter key to evaluate the function.
- In case you would like to skip one optional argument and not the other, we can simply leave the argument empty. In the example below, we’ve left the first optional argument empty while filling out the second optional argument.
This step-by-step guide should be all you need to know to skip optional arguments in Google Sheets functions. We’ve shown how you can determine which arguments are optional and which are required. We’ve also shown several examples and methods detailing how to skip one or more optional arguments.
This guide can help you better understand how to use functions with one or more optional arguments. With so many other Google Sheets functions available, you can surely find one that suits your use case.
Are you interested in learning more about what Google Sheets can do? Subscribe to our newsletter to find out about the latest Google Sheets guides and tutorials from us.
