I have a Google Sheet with multiple columns and some of the columns have empty cells at arbitrary locations. I want to 'condense' the columns, that is, simply shift upwards all cells in the column so that the column only consists of cells with values inside. All of my Google searches provide results for removing entire blank rows/columns, but do not relate to dealing with empty cells on the row/column level.

A demo Sheet can be found here.

  • 1
    Please add a brief description of your search/research efforts as is suggested in How to Ask and a link to a demo spreadsheet Commented Mar 7, 2020 at 19:23

3 Answers 3





Props: Player0

  • As noted in the comments on Player0's answer, this solution a) doesn't work if the values in cells are more than 1 word b) it requires maintaining the data in two separate locations (the original data and the new data after the spaces are removed). Looks like we'll have to continue persisting to find a suitable solution.
    – ChaimKut
    Commented Mar 26, 2020 at 10:47
  • This answer works perfectly with your sample data. Have you left some important information out of your question?
    – Tedinoz
    Commented Mar 26, 2020 at 10:53

Interestingly enough, collapsing the rows first and then the columns will produce a different result as this illustrates

Your question is best and easiest done via a script. The full code is here:

The basic steps are:

  1. row collapse the grid, using:

var r = row.filter((a) => { return a && a != ''});

  1. column collapsing = transpose, row collapse it, and then transpose again

There are a bunch of nitty-gritty details that you are better consulting the code. The lack of commenting there is only because I write self-describing code :-))) ,, NOT


  • Tools - Code Editor
  • Copy and Paste the code into code.gs
  • Four custom functions are now available (*)
  • Example usage can be found when you do View - Show Formula

(*) You can change the names to your liking. As of now, they are:

  • rowCollapse()
  • colCollapse()
  • rowColCollapse()
  • colRowCollapse()

It does look like you only need colCollapse. I misread your question and that's why came up with 4 functions (though 2 of them as core tasks are required, as described above)

USAGE,, just like Sheet built-in functions, is case-insensitive, of course.


Use BYCOL to iterate across the columns one by one.

Use TOCOL to remove the blanks.

=BYCOL(A:C, LAMBDA(col, TOCOL(col, 1)))

FILTER can replace TOCOL but it's a longer formula and will error on empty columns which TOCOL won't. The errors can be handled but will make the formula even longer.

=BYCOL(A:C, LAMBDA(col, FILTER(col, LEN(col))))

# with error handling


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.