Hiding zero values in Excel is an essential feature when working with numerical data. In some cases, you may need to remove zero values from the screen to analyze numbers that contain data. Learn how to hide zero values in Excel to improve data readability.
How to Hide All Zero Values at Once
To explain how to hide zero values in Excel, the image below shows the benefits of deleting or hiding zeros. The data on the left is harder to read because zero values are displayed in the table. In contrast, the data on the right does not display zero values, so only meaningful numbers are visible, making the data easier to understand at a glance.
There are countless ways to modify data. Today, I will explain two methods that can be widely used and applied.
Use the Excel CTRL + 1 Shortcut to Format Cells (Hide Zero Values in Excel)
The first way to hide zero values in Excel is by using a keyboard shortcut. Learning Excel shortcuts is one of the most useful ways to improve work efficiency. If you are new to Excel, one of the first shortcuts you may learn is pressing CTRL and 1 at the same time to open the Format Cells dialog box shown below.
- Windows Excel shortcut: CTRL + 1
- Mac Excel shortcut: Control + 1, Command + 1
After pressing the shortcut, the following screen appears on a Mac.
The feature used to prevent zero values from displaying in Excel is CUSTOM. Follow these steps to hide zero values in bulk.
First, select the entire data table. There are two ways to select all data.
The first is to drag and select the range with your mouse.
The second is to select any cell in the table and use “CTRL A” on Windows or “COMMAND A” or “Control A” on a Mac to select all connected data.
For reference, the CTRL + A Select All shortcut is useful not only for tables but also for selecting multiple separate images.
After selecting all the data, press CTRL + 1, select CUSTOM in the Format Cells dialog box above, and enter the following in the box below.
Hide zero values in Excel = ##
After entering it, select OK. All zero values in the Excel data will no longer be displayed, and the screen will change as shown below.
How to Find Zeroes and Specific Characters and Replace Them with a Desired Value
Hiding zero values in Excel also includes changing zero values. Next, let’s look at another method for replacing zero values in bulk. This method uses the REPLACE function, which replaces specific characters with the desired value, and the FIND function, which returns the numeric position of a specific character, to set an IF function condition.
Excel FIND Function Definition (Hide Zero Values in Excel)
The FIND function finds the position of a specific character or text string. It returns a position number that starts at 1 from the left and increases by 1 as it moves to the right. Note that if the text string cannot be found, it returns #VALUE!.
=FIND (“value to find”,”cell reference”)
The FIND function can also specify the character position in the text string where Excel should begin searching. An explanation and links to additional uses will be added later.
Excel REPLACE Function Definition
The Excel REPLACE function is one of the functions that can convert specific characters in a text value to the desired characters. Other Excel text functions include the following.
REPLACE function can be used with these features to select repeated characters in Excel and replace them with the desired characters. Adding absolute references to these features can further improve productivity.
In summary, the Excel REPLACE function specifies a position in a text cell value and replaces a specified number of characters from that position with different characters.
=REPLACE (cell reference, starting position, number of characters to replace (including spaces),”replacement text”)
Excel REPLACE & FIND Functions (Hide Zero Values in Excel)
Let’s look at how to hide zero values in Excel or convert them to the desired format.
We will use the FIND function within the REPLACE function to replace zero values in Excel in bulk.
First, in the REPLACE function syntax, use the FIND function for the starting position to find the position of the zero value.
=REPLACE (cell reference, FIND(value to find, cell reference), number of characters to replace (including spaces),”replacement text”)
We will use an IF function condition with the REPLACE & FIND function syntax above.
There are two reasons for adding the IF function.
- To prevent the REPLACE function from changing every zero value in the Excel table.
- To set up the function as shown below so that only actual standalone zero values can be modified.
L5 cell =IF(FIND(0,D5)=1,REPLACE(D5,FIND(0,D5),1,””),D5)
When the FIND function returns 1, meaning the zero comes first, use the Excel IF function and double quotation marks “” in the REPLACE function to return a blank.
Conclusion
We have looked at ways to hide zero values in Excel. There are many methods besides using CTRL + 1 or the IF, REPLACE, and FIND functions. However, if you can confidently apply the two methods explained here, you can use Excel data to communicate more efficiently with your intended audience.