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 this data or do we have to physically remove it from the data set?" Short answer: You have to remove it because Excel treats zeros as data and QI Macros doesn't automatically identify and remove outliers. It is possible to remove points that are more than four or five standard deviations from the mean, but it may not give you a true picture of your data.
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 the worksheet and either 1) select the data 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.
- 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.