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:
- Select the cells containing zero values or select one cell only if you want to change the entire worksheet or workbook.
- 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.
- Enter 0 in the Find what box.
- Ensure the Replace with box is empty.
- Check or select the Match entire cell contents check box or Excel will replace all zeros (including those within larger numbers or dates).
- From the Within drop-down menu, select Sheet or Workbook if you want to replace zeros across the entire worksheet or workbook.
- 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:
- Select the cell where you want to return blanks instead of zeros.
- Type an equal sign (=).
- 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.
- 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:
- Select the cells that contain the zero (0) values that you want to display as blanks.
- Press Ctrl + 1 or right-click and select Format Cells. The Format Cells dialog box appears.
- Click the Number tab.
- 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.
- 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.
- 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:
- Select the cells that contain zeros. These cells should have no fill or a white fill.
- Click the Home tab in the Ribbon.
- Click Conditional Formatting in the Styles group. A drop-down menu appears.
- Select New Rule. A dialog box appears.
- Click Format only cells that contain.
- To the right of Cell value, select Equal to from the drop-down menu.
- Enter 0 in the box to the right.
- Click Format. A dialog box appears.
- Click the Font tab.
- From the Color drop-down menu, select white.
- 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: 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)
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
What is Power Query in Excel?
Power Query in Excel is a powerful data transformation tool that allows you to import data from many different sources and then extract, clean, and transform the data. You will then be able to load the data into Excel or Power BI and perform further data analysis. With Power Query (also known as Get & Transform), you can set up a query once and then refresh it when new data is added. Power Query can import and clean millions of rows of data.
How to Freeze Rows in Excel (One or Multiple Rows)
You can freeze one or more rows in an Excel worksheet using the Freeze Panes command. If you freeze rows containing headings, the headings will appear when you scroll down. You can freeze columns as well so when you scroll to the right columns will be frozen.
How to Show or Hide Gridlines in Excel
You can remove or hide gridlines in Excel worksheets to simplify worksheet design. By default, gridlines are displayed but do not print. Gridlines are applied to entire worksheets or workbooks, not to specific cells. If you hide gridlines on one worksheet, it doesn't affect other sheets in the same workbook.
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