How to Flip a Row in Google Sheets

Published on

You can use the following formula to flip a row in Google Sheets while retaining empty cells:

=CHOOSECOLS(row, SEQUENCE(COLUMNS(row), 1, COLUMNS(row), -1))

Replace row with the row reference you want to flip. If you want to remove empty cells, you can use the FILTER function to exclude blanks.

Flip a Row in Google Sheets Using a Formula

Let’s first see how to flip a row in Google Sheets, then proceed to filtering out blank cells.

Example of Flipping a Row in Google Sheets

Assume you have the following values in A1:E1:

GrapesAppleOrangeMangoBanana

You can apply this formula in A2 to flip this row:

=CHOOSECOLS(A1:E1, SEQUENCE(COLUMNS(A1:E1), 1, COLUMNS(A1:E1), -1))

Output:

BananaMangoOrangeAppleGrapes

How Does This Formula Work?

Normally, the CHOOSECOLS function is used to extract specific columns from a range. For example:

=CHOOSECOLS(A1:E1, 5)

This returns the fifth column value in the range A1:E1.

If you specify a sequence of numbers from 5 to 1 in descending order:

=CHOOSECOLS(A1:E1, {5, 4, 3, 2, 1})

It effectively flips the row.

In the formula, I’ve used the SEQUENCE function to generate these numbers dynamically:

SEQUENCE(COLUMNS(A1:E1), 1, COLUMNS(A1:E1), -1)

Breakdown of SEQUENCE:

  • COLUMNS(A1:E1) – Gets the total number of columns in the row.
  • 1 – Number of columns in the sequence.
  • COLUMNS(A1:E1) – The starting number (highest column index).
  • -1 – Step value ensures the sequence decreases.

How to Remove Empty Cells While Flipping a Row

When working with growing datasets, you might prefer using dynamic row references like A1:1 or 1:1 instead of fixed ranges (A1:E1). However, this can introduce empty columns before the flipped row.

You can handle this in two ways:

  1. Filtering out all empty cells (removes blanks anywhere in the row).
  2. Trimming only trailing empty columns (keeps internal blanks intact).

Option 1: Remove All Empty Cells

=LET(
   ftr, CHOOSECOLS(A1:E1, SEQUENCE(COLUMNS(A1:E1), 1, COLUMNS(A1:E1), -1)), 
   FILTER(ftr, ftr<>"")
)

How It Works:

  • The LET function stores the flipped row as ftr.
  • FILTER(ftr, ftr<>"") removes all empty cells.

Result:

If there were empty cells in between, this method shifts data left to fill gaps.

Option 2: Remove Only Trailing Empty Cells

=ArrayFormula(CHOOSECOLS(A1:E1, SEQUENCE(XMATCH(TRUE, A1:E1<>"", 0, -1), 1, XMATCH(TRUE, A1:E1<>"", 0, -1), -1)))

How It Works:

  • Instead of COLUMNS(A1:E1), we use: XMATCH(TRUE, A1:E1<>"", 0, -1)
    This finds the last non-empty column in the row.
  • The sequence now starts from this position, ignoring trailing blanks.
  • ARRAYFORMULA is applied since A1:E1<>"" is an array-based condition.

Key Difference from Option 1:

  • Keeps empty cells in the middle of the row.
  • Only removes trailing blanks.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Sort Each Row Individually in Excel Using a LAMBDA Formula

Sorting rows in Excel typically refers to rearranging entire datasets based on values in...

Sort by Field Labels Using the SORT and XMATCH Combo in Excel

Want to sort your Excel data by column names instead of column positions? Learn...

How to Sort Pie Slices in Google Sheets

To sort pie slices in a pie chart, you need to sort the data...

Filter Items Unique to Groups in Google Sheets

In this tutorial, we'll learn how to filter items unique to groups in Google...

More like this

How to Sort Pie Slices in Google Sheets

To sort pie slices in a pie chart, you need to sort the data...

Filter Items Unique to Groups in Google Sheets

In this tutorial, we'll learn how to filter items unique to groups in Google...

Find Common Items Across Multiple Columns in Google Sheets

This tutorial explains how to find common items across multiple columns in Google Sheets....

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.