Adding N Blank Rows to SEQUENCE Results in Google Sheets

GiGiKing·March 26, 2024·0 comments

In this tutorial, I’m going to show you two methods that will allow you to add a specified number of blank rows to SEQUENCE results in Google Sheets. Whether you’re working with a single-column or grid-based SEQUENCE output, I’ve got you covered!

Adding N Blank Rows to Single-Column SEQUENCE Result

Before we dive into the formulas, let’s make sure you have enough blank cells to accommodate the array result. There’s nothing worse than encountering a pesky #REF! error. If you’re interested in learning how to remove #REF! errors in Google Sheets, even when IFERROR fails, I’ve got you covered over at Crawlan.com.

Let’s say you want to generate a sequence of numbers from 1 to 10 with 2 blank rows in between. All you need is the following formula:

=TOCOL(IFNA(HSTACK(SEQUENCE(10))))

To add 5 blank rows to a single-column SEQUENCE result, you can use this formula:

=TOCOL(IFNA(HSTACK(SEQUENCE(10,,,,,))))

The number of blank rows, represented by ‘n,’ is determined by the number of commas immediately after the SEQUENCE function. Check out the formulas above to see where I’ve highlighted this.

Here’s the breakdown of Formula #1:

  • SEQUENCE(10): Returns 10 sequential numbers from 1 to 10.
  • HSTACK(…,): Appends two empty cells.
  • IFNA(…): Removes error values and replaces them with an empty string.
  • TOCOL(…): Converts the three-dimensional array, consisting of sequence numbers in the first column and two empty columns, into a single column.

The output of TOCOL will be two blank cells inserted between each sequence number.

Adding N Blank Rows to Grid SEQUENCE Results

While this may not be a common scenario, there are cases where you might want to use the SEQUENCE function to obtain a 2D array. For example, the formula =SEQUENCE(5, 4) will yield a 5 rows by 4 columns array, with the first row containing numbers 1 to 4, the second row 5 to 8, and so on.

So how do we insert a specific number of blank rows within this grid of sequential numbers in Google Sheets? Let’s take a look at an example:

=LET( seq, SEQUENCE(5, 4), col, UNIQUE(CHOOSECOLS(seq, 1)), added, REDUCE("", col, LAMBDA(a, d, IFNA(VSTACK(a, FILTER(seq, col=d),,)))), FILTER(added, SEQUENCE(ROWS(added))<>1) )

The formula above will generate a sequence with 2 blank rows added in between.

In this case, the commas in the VSTACK function, immediately after the FILTER function, control the number of blank rows to be inserted.

To add 3 blank rows to a grid SEQUENCE in a size of 10 rows by 5 columns, replace SEQUENCE(5, 4) with SEQUENCE(10, 5) and VSTACK(a, FILTER(seq, col=d),,) with VSTACK(a, FILTER(seq, col=d),,,).

What’s more? You can even use this formula to insert blank rows between sequential numbers in a single column. Just adjust the SEQUENCE accordingly. For example, instead of the syntax SEQUENCE(rows, columns), use SEQUENCE(rows).

This formula might be slightly complex, but once you understand it, you’ll see how it effortlessly adds blank rows in 2D SEQUENCE results in Google Sheets.

Formula Break-Down

To minimize repetition and enhance overall formula performance, we’ll be using the LET function to name value expressions, allowing us to use these names in subsequent expressions or the formula itself. This follows our brand focus at Crawlan.com.

The syntax for the LET function is as follows:

LET(name1, value_expression1, [name2, ...], [value_expression2, ...], formula_expression)

Let’s break down the names and value expressions used in this formula:

  • seq: SEQUENCE(5, 4) creates a 5×4 grid of numbers from 1 to 20.
  • col: UNIQUE(CHOOSECOLS(seq, 1)) extracts unique values from the first column of seq.

By using the LET function to assign names to these value expressions, we minimize repetition and make the formula more efficient.

Now let’s look at the formula expression:

Since the initial value in the accumulator is an empty string, there will be a blank row at the top of the result in added.

FILTER(added, SEQUENCE(ROWS(added))<>1): This part of the formula removes the initial row from added, as it’s likely a blank row resulting from the first iteration of REDUCE.

This tutorial covers the addition of n blank rows to SEQUENCE results, whether they are one-dimensional or 2D, in Google Sheets. If you’re hungry for more juicy Sheets tips and tricks, be sure to check out the following tutorials on Crawlan.com:

  • Skip Blank Rows in Sequential Numbering in Google Sheets
  • How to Populate Sequential Dates Excluding Weekends in Google Sheets
  • Assign the Same Sequential Numbers to Duplicates in a List in Google Sheets
  • Auto-Fill Sequential Dates When Value Entered in Next Column in Google Sheets
  • Insert Sequential Numbers Skipping Hidden | Filtered Rows in Google Sheets
  • Sequence Numbering in Merged Cells in Google Sheets
  • How to Insert Blank Rows Using a Formula in Google Sheets
  • How to Automatically Insert a Blank Row below Each Group in Google Sheets
  • Insert Blank Rows to Separate Week Starts/Ends in Google Sheets

Don’t miss out on all the exciting possibilities that await you in Google Sheets. Happy sheeting with Crawlan.com!

GiGiKing

Related posts