When you enter a formula into one of the cells in your Microsoft Excel worksheet you will see the result of that formula by default.
You can then filter and sort your cells based on the formula results.
But you may have found that some of the cells that display formula results are showing a zero because that is the value once the formula is executed.
If you don’t want to display a zero when this is the formula result, then our guide below will show you a setting that you can change which will prevent the display of zero values in Excel.
How to Hide Zero Values in an Excel Spreadsheet
- Open your spreadsheet.
- Select the File tab.
- Click the Options button.
- Choose the Advanced tab.
- Uncheck the Show a zero in cells that have zero value option.
- Click OK.
Our guide continues below with additional information on how to hide zero values in Excel, including pictures of these steps.
Formulas in Microsoft Excel 2010 are incredibly useful. You can use them to make all sorts of calculations, and you can even copy and paste formulas into other cells, and the formulas will adjust to use values that are relative to the cell in which the formula has been pasted.
Excel will then display the value that results from executing your formula, allowing you to see the answer. By default, this action will display the number “0” if that is the result of an executed formula.
While this is perfectly valid in many situations, some users might find that they do not want to display anything in a cell if the value is zero.
Fortunately, this is a setting that can be adjusted within the program, so it is possible to stop showing zeroes in cells in Excel 2010.
How to Hide Zeroes in Excel 2010 (Guide with Pictures)
This action is not restricted to formulas, however. By making this adjustment in Excel, you will effectively stop Excel from displaying any cell value that is “0.
This can be very helpful if you are attempting to make a spreadsheet look a certain way, or if there are so many zero values in the worksheet that it becomes distracting.
Step 1: Click the File tab at the top of the window, then click Options at the bottom of the column on the left.
Step 2: Click the Advanced tab at the left side of the Excel Options window.
Step 3: Scroll to the Display options for this worksheet section, then click the box to the left of Show a zero in cells that have zero value to remove the check mark.
Step 4: Click the OK button at the bottom of the window.
Note that this action will only stop displaying values of zero for the sheet that is currently active in your workbook.
If you want to make this change for other sheets as well, then you will need to click the drop-down menu to the right of Display options for this worksheet, then choose either a different sheet or the entire workbook.
Matthew Burleigh has been writing tech tutorials since 2008. His writing has appeared on dozens of different websites and been read over 50 million times.
After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites.
His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well.