A common technique used in Excel is to format zero values as blanks. This can help improve the readability and comprehensibility of the spreadsheet. It’s difficult to quickly identify which cells contain zero values when you have a lot of data. When you signify zero values as blanks, it gives you a visual indication and makes it simpler to spot patterns in the data. A zero value in your data might also mean that no data exists. If this is the case, then displaying a blank would be a better option. Read on to see all the methods for displaying zeros as blanks in Excel. Get your copy of the example workbook used in this post to follow along.
Show Zero as Blank with Custom Format
The best method for showing your zero values as a blank is to use a custom number format. This will allow you to choose exactly how to display your positive, negative, and zero values. Follow these steps to apply a custom format that shows zeros as blanks. This will open the Format Cells dialog box which will allow you to adjust a variety of cell formatting options including any number format. 💡 Tip: You can also open the Format Cells menu by using the Ctrl + 1 keyboard shortcut. The format string used in the custom number format consists of three parts separated by a ; character. The first part determines the format to use for positive numbers, the second part determines the format to use for negative numbers, and the third part determines the format used on zero values. Notice in this format string the last part after the second ; character is empty? This results in the zero values showing as blanks! This will result in the zeros of your select range appearing blank in the cells. If you select any of these blank cells, you will notice the formula bar still shows the zero value. The cells will still contain their zero value, but the formatting causes them to display as blanks in the grid.
Show Zero as Blank with Excel Options
There is an Excel setting that will allow you to hide zeros for your entire sheet. This way you don’t need to select a specific range to apply format, all the zero values in your sheet will be hidden and appear as blanks. Follow these steps to hide zero values for an entire sheet. This will open the Excel Options menu which contains a variety of customizable settings for your Excel app. 📝 Note: This option is checked by default for any new sheet in your workbook. You will have to update this setting for each sheet if you want hidden zeros across your entire workbook. All your zero values are now hidden across the entire sheet! If you want to show a few zeros in such a sheet, it is still possible. You can use this #,##0;-#,##0;0 custom format to override the setting on individual cells.
Show Zero as Blank with the TEXT Function
You can also use the same custom format strings with an Excel function. The TEXT function allows you to format numbers as text and apply any special format. The above formula will format the content of cell C3 based on the supplied format where zero values are hidden. The “$#,##0;-$#,##0;” format string is the exact same as the custom format solution. The results from this formula will be text values, so this may not be the ideal solution depending on what you need to do with the formatted values. You won’t be able to use the results in any further numerical calculations since they are text.
Show Zero as Blank with the IF Function
You can also use the IF function to hide any zero values. You can test if a cell has a zero value and show a blank when it does. The above formula will test if the value in cell C3 is zero and return the empty string "" if it is. Otherwise, it will return the original value. This will keep the non-zero values as numbers, but the blank cells will actually be text values.
Show Zero as Blank with Find and Replace
You might decide you want to remove the zero values from your data entirely. This is possible to do with a simple find and replace. You can search for the zeros and then replace them with a blank. This way you will have truly blank cells instead of zeros. Follow these steps to replace your zero values from any range. This will essentially remove all the zeros from your values because you will find all the 0 cells and replace them with nothing! This is a key part of the process. Matching the entire cell contents means you won’t inadvertently change a value such as 520 to 52. Excel will replace all the zero values in your selection with a blank.
Show Zero as Blank in Power Query
Power Query is an amazing tool in Excel for any sort of data import or transformation task. You will easily be able to remove any zeros and replace them with blanks in your queries. You will first need to get your data into the Power Query editor. This will open up the Power Query editor with your data. This will open the Replace Values menu where you will be able to define what to find and what to replace it with. 📝 Note: The null value is Power Query’s equivalent to a blank in Excel. It represents the absence of a value. You will notice data now shows null values instead of any zeros. Power Query has performed this by automatically creating an M code formula like the above. You can now load the data back into Excel. Go to the Home tab and click on Close and Load. The data can be loaded into a table in a location of your choice and the null values will show as blanks. 📝 Note: This is a great option for any data that will be continually updated, as you can refresh the query and the results for the new data will appear! Go to the Data tab and use the Refresh All command to rerun the query.
Show Zero as Blank with VBA
VBA is a great way to automate most tasks in Excel. You can use VBA to remove zeros as well in a similar fashion to the Find and Replace feature. But this way you won’t need to input any values to find, or update settings to get the job done. The code will take care of that! You can create a macro that will loop through the selected range and identify any cells with a zero and remove them. Follow these steps to open the visual basic editor. You might not see the Developer tab, as it’s hidden by default. You can enable the Developer tab in your ribbon if you don’t see it already. You can also use the Alt + F11 keyboard shortcut to directly open the visual basic editor. This will create a new module where you can copy and paste the above code. This code will loop through each cell in the selected range and check if its value is zero. If the value is zero then the cell content is cleared. This will remove the zero entirely and not just hide it from view. Now you can select any range in the workbook and run the VBA code to remove the zeros!
Show Zero as Blank with Office Scripts
VBA isn’t the only way to automate things in Excel. You can also use Office Scripts if you’re using Excel online with a Microsoft 365 business plan. Open your Excel workbook in the web browser and go to the Automate tab then select the New Script option. This will open the Code Editor on the right side of the workbook. You can then copy and paste the above code into the Code Editor and press the Save script button. This code will loop through the selected range and clear the content of any cell that contains a zero. Select the range with zeros and press the Run button in the Code Editor and your zeros will be removed leaving you with blank cells in their place.
Conclusions
There are many reasons why you might want to hide the zeros in your Excel workbooks. Displaying your zeros as blanks can help visually declutter your data and improve readability. Excel has several great ways to hide zeros. You can apply custom formats, use the Excel Options menu, or use functions such as TEXT and IF. Another option might be to remove any zero values from your data. This can be achieved quickly with Find and Replace, VBA, or Office Scripts. If you are importing or transforming your data on a frequent basis, then Power Query should be your go-to method and you will also be able to remove any zero values in your query. Do you hide the zeros in your workbook? What methods do you use? Let me know in the comments below!
