Automatically Insert a Blank Row Below Each Group in Google Sheets

GiGiKing·March 27, 2024·0 comments

Do you want to automatically insert a blank row below each group in Google Sheets? While there is no built-in command to do this, I’ve got you covered with a formula that will make it happen. In this post, I’ll walk you through step by step on how to achieve this.

Example of how to insert a blank row below each group of data in Google Sheets.

The Old-School Approach

Let’s start with the old-school approach, which involves a slightly more complex formula. First, we need to identify the ending row of each group. We can do this by using the COUNTIFS formula to return a running count of the values in the group column.

=ARRAYFORMULA(COUNTIFS($A$2:$A,$A$2:$A,ROW($A$2:$A),"<="&ROW($A$2:$A)))

By using the above result, we can find the ending row of each group. The row above the number 1 is the ending row of that group. To mark the ending row, we can replace the numbers with a hyphen and the rest with blanks using the following formula:

=ARRAYFORMULA(IF(COUNTIFS($A$2:$A,$A$2:$A,ROW($A$2:$A),"<="&ROW($A$2:$A))=1,"-",))

Next, we need to flatten the group column (column A) and the hyphen column (column D) using the FLATTEN function. It’s important to use a limited range for the FLATTEN function to avoid doubling the number of rows. Once we have verified the formula, we can change it to an open range.

=FLATTEN({$D$2:$D13,A2:A13})

Now, to automatically insert a blank row below each group, we need to complete a few more steps. These steps include removing the blank rows, making the range open, removing the first hyphen character, replacing the hyphens with space characters, and replacing the range with the formula itself. We can achieve all of this with the following formula:

=QUERY(FILTER(FLATTEN({ARRAYFORMULA(IF(COUNTIFS($A$2:$A,$A$2:$A,ROW($A$2:$A),"<="&ROW($A$2:$A))=1," ",)),A2:A}),FLATTEN({ARRAYFORMULA(IF(COUNTIFS($A$2:$A,$A$2:$A,ROW($A$2:$A),"<="&ROW($A$2:$A))=1," ",)),A2:A})<>""),"offset 1",0)

And that’s it! You’ve successfully learned how to use a formula to automatically insert a blank row below each group in Google Sheets.

The Modern Approach

If you prefer a more modern approach, Google Sheets has introduced new features like LAMBDA functions and REDUCE. We can leverage these features to insert blank rows below each group.

For example, to insert one blank row below each group, you can use the following formula:

=REDUCE(A1:C1,TOCOL(UNIQUE(A2:A),1),LAMBDA(a,v,IFNA(VSTACK(a,FILTER(A2:C,A2:A=v),))))

Where:

  • A2:A is the category column.
  • A1:C1 is the header range.
  • A2:C is the table range.

You can modify the formula to insert multiple blank rows by adding commas after the last comma in the formula.

Now you can automatically insert blank rows below each group in Google Sheets without any hassle.

For more helpful resources and tips on using Google Sheets, be sure to check out Crawlan.com.

Resources:

  • How to Merge Two Columns Into One Column in Google Sheets.
  • Formula to Insert Group Total Rows in Google Sheets.
  • Insert Blank Rows to Separate Week Starts/Ends in Google Sheets.

GiGiKing

Related posts