Replace Zeros With Blanks in Excel
Go Deeper: 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 the "Replace Zeros with Blanks" Option in QI Macros
This functionality is designed to analyze your data set and remove any zeros and/or decimal zeros, replacing them with blank cells.
To use this macro, simply highlight your data:
Select "Replace Zeros with Blanks" from the "Restacking" menu:
And the zeros in your data set will automatically be turned into blank cells!
NOTE: This functionality is included in the January 2023 release.
Use Excel's Find/Replace Function to Replace Zeros
Another easy way to find and fix zeros or any other value using Excel's built-in functionality is with the Find/Replace tool.
- 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.
Excel's Paste Link Function Turns Blanks into Zeros
Or you can use QI Macros' Paste Link Function to keep blanks, blanks!
To use the Paste Link function within QI Macros, highlight your data and select "Paste Link" from the "Restacking" drop-down:
Your data set will then be copied and pasted onto a separate tab within your spreadsheet!
Get the Idea?
It's easy to find and replace zeros with blank cells or any value in a worksheet using QI Macros! Or you can use Excel's Find and Replace function, which is more cumbersome.
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.