How to Show Zeros as Blank Cells in Excel in 60 Seconds
When you’re working in Excel and have big data sheets, it’s totally possible that many of your values could be zero.
This is common, but it can make your spreadsheet difficult to read and navigate. We don’t have to change all of these zeros and delete them from the data to clean up our spreadsheet.
Instead we can just change how they appear. Instead of showing all these zeros, we could blank them with one simple option in Excel. Let’s learn how.
How to Quickly Show Excel Cells as Blank if Zero
Note: Watch this short tutorial screencast or follow the quick steps below, that compliment this video.
1. Open Your Excel File That Has Zero Cells
We start with an Excel file with numerous 0 cells, which we want to turn blank.
2. How to Turn Off Show Zeros in Excel
Now go to the File tab here in Excel. Now find the Options button here on the left side, and go ahead and click on it.
Now, change to the Advanced option on the left side. Let’s scroll down. And in the midst of these options, go ahead and find the box that says, Show a zero in cells that have zero value. This option controls the setting for the entire workbook. We need to go ahead and uncheck that box and now let’s press OK.
3. Now Excel Will Show Zero Cells as Blank
When we return to the workbook, you can see that all of the zeros are now hidden. The zero value is still inside the cell, but Excel changes how it shows it and it blanks those cells out. This might also be useful in a pivot table view, for example.
Now you know how to change an Excel if cell to 0 then make it blank when needed. This option of is helpful to keep spreadsheets more readable and focused on your meaningful data.