How can I replace blank cells with zeros in Google Spreadsheet?

Question

I have a spreadsheet where someone else entered the data. They left blank cells instead of recording 0 values. How can I replace the blank cells with 0s?

I tried find and replace with regular expressions using ^$, but it did not work.

Answer 1

I assume you only want to do this in a certain part of the spreadsheet, and that it's a one-off problem which you need to fix, not something you need to repeat.

For this, the simplest approach is to find another area the same shape and size (perhaps in another worksheet), and not currently used.

In each cell in that area, set the forumula to something like this (depending on the cells you use):

=if(isblank(A3),"0",A3)

The select the data from the 2nd area, copy it, and paste it back to the original using Edit > Paste Special > Paste values only

You may need to re-apply some of the formatting (eg date formats), but the values will be there.

fyi, I have put a worked example of this approach here: https://docs.google.com/spreadsheets/d/1T6owfGtpKt3NLl5hq1AZpeWA1ILWUwBQ44bdlAmekF0/edit?usp=sharing

Answer 2

Use regular expression in Find and Replace:

^\s*$

Answer 3

The following script will add a 0 to every empty cell in the range.

Code V8

const onOpen = () => {
  SpreadsheetApp.getUi().createMenu("Zeros")
    .addItem("Sheet", "zeroSheet")
    .addItem("Data range", "zeroDatarange")
    .addItem("Selection", "zeroSelection")
    .addItem("Manual Override", "zeroManual")
  .addToUi()
}

const zeroSheet = () => zero(sheet = SpreadsheetApp.getActiveSheet(), 'sheet')
const zeroDatarange = () => zero(range = SpreadsheetApp.getActiveSheet().getDataRange(), 'datarange')
const zeroSelection = () => zero(range = SpreadsheetApp.getActiveSheet().getActiveRange(), 'activerange')
const zeroManual = () =>  zero(spreadsheet = SpreadsheetApp.getActive(), 'manual')

const zero = (source, type) => {
  switch(type) {
    case 'sheet':
      range = source.getRange(1, 1, source.getMaxRows(), source.getMaxColumns())
      break
    case 'manual':
      range = source.getSheetByName('Sheetname').getRange(2, 4, 3, 6)    
      break
    case 'datarange':
    case 'activerange':
      range = source
      break
  }
  range.setValues(range.getValues().map(row => row.map(cell => !cell ? 0 : cell)))
}

Note

When the file is opened, a menu item called Zeros is created, having four sub items. If you want to run the manual function upon opening as well, then simply add it like so:

Example

I've created an example file for you: Replace blank cell with zeros.

1. Create a copy of the file or paste the code under Tools>Script editor.
2. Press the clock bug button to authenticate the script.
3. Reopen your document, you now have the new menu !

Answer 4

For a limited number of columns you might filter to select Blanks, then insert and copy down 0.

Answer 5

One way that has worked for me in similar situations on Google spreadsheets was to SUM the empty column with 0 and store the result in a new column.

If column A has the empty cells you want to be replaced and, say, you have data in B, then use the next empty column C to store the updated values like so:

C1=SUM(A1, 0) and so on for the entire column C.

In my case, I needed to multiply values and empty cells worked like 1s instead of 0s with PRODUCT(), so I did:

=PRODUCT(SUM(A,0), multiplier)

which gave me expected results for empty or non-number cells in A.

Answer 6

You can use "Find and Replace" (Ctrl+F → More options).

• Find: ^$
• Replace with: 0
• Search: Your range
• Select: Search using regular expressions

Answer 7

Use regular expression, which requires plain text formatting:

1. Select cells that include empty cells
2. Format > Number > Plain Text
3. Edit > Find and Replace
   1. Find: ^$
   2. Replace with: 0 (or whatever you want)
   3. Check "Search using regular expressions"
   4. Click "Replace all"

Answer 8

Simpler Formula

Since you are dealing with numbers and blanks, the accepted answer =IF(ISBLANK(A3),0,A3) is more complex than necessary.

Simply adding zero to the cells will achieve the same result:

=A3+0

Examples

     1.  A3 is a blank cell

=A3+0    |   =IF(ISBLANK(A3), 0, A3) 
=0       |   =0

     2.  A3 is a number

=A3+0    |   =IF(ISBLANK(A3), 0, A3)   // A3=1
=1       |   =1

=A3+0    |   =IF(ISBLANK(A3), 0, A3)   // A3=0
=0       |   =0

=A3+0    |   =IF(ISBLANK(A3), 0, A3)   // A3=-1
=-1      |   =-1

Why change blanks to zeros?

Other approaches might be offered if we knew "why" you need zeros instead of blanks (XY Problem.

For example, the typo (since corrected) in the accepted answer prompted you to comment that the SMALL function was failing when zero was a text string instead of a number.

Was your question prompted because you were trying to ensure the SMALL function treated blank cells as zeros rather than ignoring them?

If so you can add that behavior directly in the formula by wrapping the SMALL function in ARRAYFORMULA and adding zero to all the values.

Blank Handling in Formula

The simplicity of adding 0 to our values to eliminate blanks is combined with the ARRAYFORMULA function which apply this to all the values in the range.

Where range rng contains cells that are blank or contain numbers:

Instead of this formula

=SMALL(rng, 1)

use this formula

=ARRAYFORMULA(SMALL(rng+0, 1))

There are other ways to achieve this and the XY of it will indicate the best approach.

---

Not Number & Not Blank

If it is possible that you have values other than blanks and numbers, for example, empty strings "" or space(s) " ", or anything other than a number or a truly blank cell, you can trigger an error for all but non-zero numbers and then replace the errors with a 0

Single Cell Formula

=IFERROR(1/(1/A3),0)

Array Version

=ARRAYFORMULA(
   IFERROR(1/(1/A3:A),0))

Answer 9

I had a huge data with blanks randomly set inside it, so using if(isblank()) was a bad idea for me as it would have been super labor intensive.

Instead, I downloaded my sheet as a csv, and opened it in TextWrangler (any text editor with find and replace will work). Then I found ',,' and replaced with ',0,'. I had to do that twice. Then when I reimported, I used if(isblank()) only on the first and last columns of my data set. Everything else was zeroed out. Only took about 5-10 mins.

Answer 10

Select an empty cell -> "filter by cell value" (it will only display empty cells in the column) -> fill in the value you need in the first cell -> drag it down all the way to the bottom.