How to Replace Zeros (0) with Blanks in Excel

Enter, Return or Display Blanks Instead of Zeros in Excel

by Avantix Learning Team | Updated January 4, 2024

Applies to: Microsoft^® Excel^® 2016, 2019, 2021 and 365 (Windows)

There are several strategies to replace zero values (0) with blanks in Excel. If you want to replace zero values in cells with blanks, you can use the Replace command or write a formula to return blanks. However, if you simply want to display blanks instead of zeros, you have two formatting options – create a custom number format or a conditional format.

Recommended article: How to Delete Blank Rows in Excel (5 Easy Ways with Shortcuts)

Do you want to learn more about Excel? Check out our virtual classroom or live (in-person) classroom Excel courses >

In this article, we'll review how to:

• Replace zeros with blanks using the Replace command
• Return blanks instead of zeros using a formula
• Replace or display blanks instead of zeros using a custom number format
• Replace or display blanks instead of zeros using a conditional format

Replace zeros with blanks using the Replace command

To replace zeros with blanks using the Replace command:

1. Select the cells containing zero values or select one cell only if you want to change the entire worksheet or workbook.
2. Press Ctrl + H. You can also click the Home tab in the Ribbon, click Find & Select in the Editing group and then select Replace from the drop-down menu. A dialog box appears.
3. Enter 0 in the Find what box.
4. Ensure the Replace with box is empty.
5. Check or select the Match entire cell contents check box or Excel will replace all zeros (including those within larger numbers or dates).
6. From the Within drop-down menu, select Sheet or Workbook if you want to replace zeros across the entire worksheet or workbook.
7. Click Replace All.

The Replace dialog box appears as follows in Excel:

This method works best with zero values entered in cells as opposed to formulas returning zeros.

Return blanks instead of zeros using a formula

Another option is to create a formula to return blanks instead of zeros. A common function to use in a formula of this type is the IF function using the syntax =IF(test, true, false).

To create a formula using the IF function to return blanks:

1. Select the cell where you want to return blanks instead of zeros.
2. Type an equal sign (=).
3. Enter a formula such as =IF(cell reference=0, "", cell reference). For example, in B1, you could enter =IF(A1=0, "", A1). In this example, if A1 is equal to 0, enter a blank, if not, enter the value in A1.
4. If you want to copy the formula down, drag the Fill Handle (bottom right corner handle), down to the cells below. You can also copy the formula to the right by dragging the Fill Handle to the right.

Replace or display blanks instead of zeros using a custom number format

If you want to display blanks, you can create a custom number format.

To display blanks instead of zeros for specific cells:

1. Select the cells that contain the zero (0) values that you want to display as blanks.
2. Press Ctrl + 1 or right-click and select Format Cells. The Format Cells dialog box appears.
3. Click the Number tab.
4. In the categories on the left, select Custom. Custom formats can have up to 4 parts separated by semi-colons (;). The first part affects positive numbers, the second part affects negative numbers, the third part affects zeros and the fourth part affects text.
5. In the Type box, enter a custom format such as #,##0;-#,##0;;"N/A". In this example, the third part is left blank so zeros would display as blank.
6. Click OK.

The Format Cells dialog box appears with custom number formats as follows:

Custom number formats are only saved in the current workbook. The contents of the cells containing zero values would still display in the Formula Bar.

Replace or display blanks instead of zeros using a conditional format

You can also create a conditional format to display zeros as blanks. This strategy involves changing font color (typically to white).

To create a conditional format to display blanks instead of zeros:

1. Select the cells that contain zeros. These cells should have no fill or a white fill.
2. Click the Home tab in the Ribbon.
3. Click Conditional Formatting in the Styles group. A drop-down menu appears.
4. Select New Rule. A dialog box appears.
5. Click Format only cells that contain.
6. To the right of Cell value, select Equal to from the drop-down menu.
7. Enter 0 in the box to the right.
8. Click Format. A dialog box appears.
9. Click the Font tab.
10. From the Color drop-down menu, select white.
11. Click OK twice.

The New Formatting Rule dialog box appears as follows:

Although you can also hide all zeros in a worksheet using File > Options, this method can be problematic as other users may not know that Options have been changed for the worksheet.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, JOIN our email list.

More resources

How to Use Flash Fill in Excel (4 Ways with Shortcuts)

3 Excel Strikethrough Shortcuts to Cross Out Text or Values in Cells

How to Replace Blank Cells in Excel with a Value from the Cell Above

How to Replace Blank Cells with Zeros (0), Dashes (-) or Other Values in Excel

10 Excel Flash Fill Examples (Extract, Combine, Clean and Format Data with Flash Fill)

Related courses

Microsoft Excel: Introduction

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel: Introduction to Power Query (Get & Transform)

Microsoft Excel: Introduction to Power Pivot

Microsoft Excel: New Features and Functions in Excel 365

Microsoft Excel: Introduction to Visual Basic for Applications (VBA)

VIEW MORE COURSES >

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.

Copyright 2024 Avantix^® Learning

You may also like

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca