Home » Free Excel Tips » Replace Zeros in Excel Worksheet

Replace Zeros With Blank Cells in Excel

Step by Step Instructions

A QI Macros customer had zeros in his data caused by a machine transfer error. When he ran a histogram, his capability indicies were off because of the zeros. He asked, "Is there any way to disregard zeros or do we have to physically remove them from a data set?"

The short answer: No, he had to remove the zeros because Excel treats zeros as data points not as blanks.

Use Excel's Find/Replace Function to Replace Zeros

The easy way to find and fix zeros or any other value is with Excel's Find/Replace function.

Open your worksheet and either 1) select the data range to be changed or 2) select a single cell to change the entire worksheet.
Choose Find/Replace (CTRL-H).
Use 0 for Find what and leave the Replace with field blank (see below).
Check “Match entire cell contents” or Excel will replace every zero, even the ones within values.
In cell C2, for example, 205 would become 25 and so on if this box is not checked.
Click on Replace All to change all zero cells to blanks.

Get the Idea?

It's easy to find and replace zeros with blank cells or any value in a worksheet using Excel's Find and Replace function.

Learn More...

Ready to Take the Next Step and Up Your Excel and Data Analysis Skills?

Stop trying to make your spreadsheets easy to read. And stop using primitive line, pie and bar charts.
Instead, graduate to the power tools of quality: control charts, Pareto charts and histograms.