Home » Free Excel Tips » Replace Zeros in Excel Worksheet

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:

highlight-data-before-running-replace-zeros-with-blanks

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.

replace zeros in Excel

Click on Replace All to change all zero cells to blanks.

Replace zeros in Excel Worksheet example

Excel's Paste Link Function Turns Blanks into Zeros

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.

Learn More...

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

Stop trying to make your spreadsheets easy to read, because nobody wants to read your spreadsheet.
And stop using primitive line, bar and pie charts. Isn't it time to graduate to smart charts:
Control charts, Pareto charts and Histograms?