In a column, set populated cells to 1 and empty cells to 0


In an as yet unanswered Q from early this year a user of Stack Overflow asked:

how to fill all non-empty cells of a column by 1 and the empty cells by 0. It's an xlsx file open with LibreOffice Calc.

Is this possible with a formula?

Why should this post be closed?


1 answer


An easy way (in LibreOffice Calc is not to attempt directly to overwrite what is already present but to create the desired results with a formula in a different column, then replace the contents of the original column with the values (not formulae) from the added column.

Assuming the data is in ColumnA, insert a new ColumnB and in B1 and copied down() to suit:


then select ColumnB, Edit > Copy, select ColumnA, Edit > Paste Only > Paste Only Numbers. Now ColumnB may be deleted.

The sheet may be saved with an xlsx extension.

Double-clicking the fill handle won't work here (because of the blank cells). Instead either drag down to suit or, if very many cells, go to the last cell to be populated and enter something there (say x) then return to B1, Copy, select B2, Ctrl+Shift+Down and Yes.


Sign up to answer this question »