As far as I know, we don’t have any menu commands to delete and shift cells to the left in Google Sheets. Then what is the solution?

We are required to use a formula, and the output will be in a new range (please scroll down to see the image).

In Excel, we can do it with two main steps using menu commands under the Home tab. Here is how.

First and foremost, select the range.

Under the Home tab Editing group, select Find and Select > Go to Special > Blanks > OK.

It will select the blank cells in the range.

Then, under the Home tab Cells group, select Delete > Delete Cells > Shift Cells Left > OK, and voila!

Unfortunately, we don’t have this or a similar feature in Google Sheets. So we can use formulas for the same.

The drawback of using a formula to delete and shift cells left is that the output will be in a new range.

So you may additionally require to copy the result and apply paste value over the source.

But the formula has its advantage if you don’t copy and apply paste value. What’s that?

It is set up and forget type. So any new entry will be under the formula’s cover.

I usually delete and shift cells left in Google Sheets to organize Form responses which often come with some blank cells in columns between data.

Here is an example.

Formulas to Delete and Shift Cells Left in Google Sheets

Here, we can use two different formulas for deleting and shifting cells to the left – an array formula or a copy-paste formula.

E.g., let’s take the above sample data (A1:D).

In cell F2, insert the following FILTER formula and copy-paste it down as far as you want.

Non-Array Formula

=ifna(filter(A2:D2,A2:D2<>""))

Logic:- Filters out blank cells in rows.

Then optionally, select F2:I11, right-click and copy the data.

Go to cell A2 (the very first cell in the source range), right-click and select Paste Special > Values only. Delete the formulas.

We can also use an array formula to delete cells and shift data to the left in Google Sheets.

Empty F2:I and insert the following array formula in cell F2.

Array Formula

=ArrayFormula(substitute(split(transpose(query(transpose(A2:D)&"|",,9^9)),"| ",false),"|",""))

Logic:- Combine columns and split.

Here also, you can follow the above optional steps.

Array Formula Explanation

We can use QUERY to join columns in Google Sheets. The Transpose > Query > Transpose (read from right to left of the bold part of the formula) does that.

Additionally, to facilitate split, we have added a pipe ( | ) delimiter with each cell in the range.

The bold part in the array formula is almost similar to the below one.

A2:A&"| "&B2:B&"| "&C2:C&"| "&D2:D

So, if you want, you can replace the Transpose > Query > Transpose with the above part.

Note:- When we test the formula parts, use ArrayFormula with them.

When we SPLIT the combined columns, blank cells will disappear. The SUBSTITUTE removes any left pipe delimiters.

That’s all about how to delete and shift cells left in Google Sheets.

Thanks for the stay. Enjoy!

Resources

1. Google Sheets: Find All Duplicates in a Column and Delete the Row.
2. How to Filter Duplicates in Google Sheets and Delete.
3. Delete Every Nth Row in Google Sheets Using Filters.
4. Remove Extra Blank Cells in ArrayFormula Output at the Bottom.
5. How to Skip Blank Cells in Vlookup in Google Sheets.
6. How to Get Rows Excluding Rows with Any Blank Cells in Google Sheets.
7. Array Formula to Fill Blank Cells With the Values Above in Google Sheets.
8. How to Subtotal Up to the First Blank Cell in a Column in Google Sheets.