3 ways to delete zeros and hide zeros on Excel quickly and easily
3 ways to delete zeros and hide zeros on Excel quickly and easily: Sometimes when creating a statistical table, you may want to hide or delete the zeros to view/search for more accurate data. If you manually delete them, it can take a lot of time, especially for tables with many cells containing zero values. If some cells contain formulas, manually deleting zeros may require users to recreate them later when changes are made to the calculations. The article below will guide you on how to quickly hide zeros in the data table on Excel.
1. Hide zeros using Format Cells
To hide zeros, first, you need to select the entire data range containing zeros that need to be hidden. Then right-click and select Format Cells. Alternatively, you can also use the shortcut key Ctrl + 1 to quickly open the Format Cells dialog box.
Now the Format Cells dialog box appears. Select the Number tab => Custom. Next, enter the code 0;-0;;@ in the Type section. Finally, click OK to complete.
That’s all it takes for all Excel cells containing only zeros to be hidden quickly.
2. Hide zeros using Excel Options
First, select the File tab on the toolbar. Then select More => Options.
Now the Excel Options window appears. Select the Advanced tab on the left sidebar. Then scroll down to the Display options for this worksheet section and uncheck the Show a zero in cells that have zero value option.
That’s all it takes for all Excel cells containing only zeros to be hidden quickly.
3. Hide zeros using Conditional Formatting
First, select the entire data range containing zeros that need to be hidden. Then select the Home tab=>Conditional Formatting => New Rule.
Now the New Formatting Rule dialog box appears. Select the Format only cells that contain option. Then select equal to and enter 0 in the adjacent cell. Next, click the Format button.
Now the Format Cells dialog box appears. Select white in the Color section and click OK to complete.
That’s all it takes for all Excel cells containing only zeros to be hidden quickly.
So, the article above has shared with you the ways to delete zeros and hide zeros on Excel. Hopefully, this article will be helpful to you in your work. Wish you success!
