All the Google Sheets functions don’t have optional arguments. But there are functions with optional arguments. Let’s see how to skip optional arguments in such functions in Google Sheets.

If you check a function syntax, you can find optional parameters/arguments, if any, placed within square brackets.

Skipping optional parameter depends on it’s positioning in the syntax and also it’s function specific.

There are hundreds of functions in Google Sheets. So it’s not possible for me to explain to you how to skip optional arguments in all these functions.

Keeping that in mind, here I am trying to give you a general idea of skipping optional parameters in the functions in Google Sheets.

I’ve carefully selected a few of the functions in Google Sheets for the explanation purpose.

Optional Arguments at the End or Middle of a Function Syntax

If you want to exclude an optional parameter from the end of a function, normally you can skip using it. But you must be aware of the outcome of it!

Because when you exclude the last optional argument, the function will consider the default value assigned to it. So you must understand the default value first.

Further, when you want to exclude a few of them, in most of the functions, the default value will be assigned, but there are exceptions.

Skip All the Optional Arguments in a Function in Google Sheets

Example # 1 – Single Parameter

Let’s consider the all time popular function Vlookup here. The syntax of this function is as follows.

Syntax: VLOOKUP(search_key, range, index, [is_sorted])

In this function, the parameter is_sorted is optional as it’s within the square brackets. The is_sorted default value is TRUE or 1 (data is sorted). Specify FALSE or 0 if the data is not sorted.

Here is one example.

In my sheet, as per the below table, I have vehicle categories in column A and the corresponding order quantities in column B

Assume the data range is A2:B5 excluding the first header row.

The search_key is “sedan” and the index number (result column’s relative position number in the table) is 2.

That means we want the formula to find the search key “sedan” in column 1 (column A) and return the corresponding total order from column 2 (column B), i.e. 5.

Here is the Vlookup formula for that.

=vlookup("sedan",A2:B5,2,false)

Here in this Vlookup, I’ve used the optional argument is_sorted as FALSE because the data (search_key column) is not sorted.

If the table is sorted as below, then we can exclude the last argument because the default value of is_sorted is TRUE. No need to specify it.

=vlookup("sedan",A2:B5,2)

Note: If the data is not sorted, then you can follow this approach too.

=vlookup("sedan",sort(A1:B5),2)

That means before skipping an optional argument in a function, you must understand its purpose, or we can say the default value assigned to it.

Example # 2 – Multiple Parameters

In some of the functions, you can simply ignore all the optional arguments. For example, see the syntax of the function Growth and Trend.

Growth: GROWTH(known_data_y, [known_data_x], [new_data_x], [b])

Trend: TREND(known_data_y, [known_data_x], [new_data_x], [b])

Both have similar arguments.

If you want to skip all the optional arguments in the functions Growth and Trend, I mean the arguments known_data_x, new_data_x, and b, just exclude using them in your formula.

See the cells B1 and C1 for the formulas in the cells B2 and C2 respectively.

But if you want to use the last parameter and skip the middle two, then things are quite different. I’ll explain that below.

Skip Only a Few of the Optional Parameters in Functions in Google Sheets

Let’s start again with the Growth and Trend two of the most confusing functions in optional arguments use.

Replacing known_data_x and new_data_x by Dummy Values in Growth and Trend

The ‘b’ stands for the ‘curve fit’ in Trend and Growth. By default, the value is 1 or you can say Boolean TRUE.

I want to skip the middle two arguments, i.e. known_data_x and new_data_x, which has no default values, and to use b as 0 or FALSE.

How to skip these two optional parameters from the middle of the functions in Google Sheets?

See the above image for the formulas Growth and Trend without any optional arguments. As you can see there are values in 6 rows (A2:A7).

To skip optional arguments in Growth and Trend, as per the above data, virtually generate sequence numbers 1 to 6 as the data is present in 6 rows.

This Sequence formula will do that.

=sequence(6,1)

Just use this formula as the known_data_x and new_data_x (as default values) in the formula and use b as TRUE or FALSE.

GROWTH FORMULA

=GROWTH(A2:A7,sequence(6,1),sequence(6,1),true)

TREND FORMULA

=TREND(A2:A7,sequence(6,1),sequence(6,1),true)

The output of the above two formulas will be equal to the formula output in B2:B7 and C2:C7 as per the image above.

Simply change the last argument TRUE to FALSE. This way we can ‘exclude’ the middle two arguments in the said two functions.

Placing a Comma to Leave an Optional Parameter in Financial Functions

In some functions, mostly in financial functions, we can leave an optional argument in the middle of a function by placing a comma. Here is one example using the PMT function.

Syntax: PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning])

INPUT

rate= 1%
number_of_periods = 12
present_value= 30000
future_value= want to skip this argument
end_or_beginning= 1

=pmt(C2,C3,C4,,1)

Conclusion

In some functions we can even skip required arguments. For example see the IF formula below.

Syntax: IF(logical_expression, value_if_true, value_if_false)

EXAMPLE

=if(A1="",,A1*5.5)

I can give you one more example using Query. Read that here – The Flexible Array Formula to Join Columns in Google Sheets.

In concise understand the default value before skipping an optional argument in functions in Google Sheets.

That’s all. Enjoy!