Instructions to delete 0, hide 0 in Excel

There are many cases when working with data statistics in Excel, users are forced to hide or delete 0, so that they can see and view the whole table data more accurately. If you delete manually, it will take a lot of time, especially for multi-cell statistical tables with a value of 0, then manual removal is not feasible. Some cells with the formula delete the number 0 manually, users must re-create later when changing the calculation.

However, on the Excel table, there is a hidden value of 0 that you do not need to use external tools, or find each cell in the table to press delete 0. In the following article, we will guide you to read how to hide zeros in Excel spreadsheets quickly.

1. 10 ways to recover corrupted Excel files
2. Forgot password protected Excel file, what should you do?
3. How to combine multiple cells into 1 in Excel does not lose data

Ways to hide 0 in Excel

In the table below, I will take the example of a transcript and hide the value by 0.

Instructions to delete 0, hide 0 in Excel Picture 1

Method 1:

This will apply to versions of Excel 2007 and above.

Step 1:

At the spreadsheet interface, we will click on the Office icon above and then select to Excel Options .

Instructions to delete 0, hide 0 in Excel Picture 2

Step 2:

In the new interface, users click on the Advanced item in the options list to the left of the interface. Look at the content on the right of Display options for this worksheet , uncheck the box at Show a zero in cells that has zero value .

Instructions to delete 0, hide 0 in Excel Picture 3

As a result, the data table will hide all cells that have zero values ​​as shown below. To re-display the number 0, simply select the Show again with a zero in cells that has zero value.

Instructions to delete 0, hide 0 in Excel Picture 4

Method 2:

We will adjust directly in the data table on Excel via Format Cells.

Step 1:

The user chooses the area to hide 0, but in the article is the entire data table. Then right click and choose Format Cells .

Instructions to delete 0, hide 0 in Excel Picture 5

Step 2:

In the Categorical Format Cells dialog box, select the Customs item. Next, type Type in the box below the range 0; -0 ;; @ . Then click OK to save.

Instructions to delete 0, hide 0 in Excel Picture 6

When returning to the data table, all 0 values ​​are hidden.

Instructions to delete 0, hide 0 in Excel Picture 7

If you want to show 0 again, just press Delete to delete the range 0; -0 ;; @ in the Type section of the Format Cells table.

Instructions to delete 0, hide 0 in Excel Picture 8

Method 3:

With this method, we will use the Format feature to hide hidden statistics table cells with a value of 0.

Step 1:

First we need to black out the entire statistics table, then click on the Home tab and then select Conditional Formatting> New Rule .

Instructions to delete 0, hide 0 in Excel Picture 9

Step 2:

In the New Formating Rule dialog box, navigate to the line Format only cells that contain . Then select Equal to big , then enter 0 in the box next to it and select Format to reformat the cell.

Instructions to delete 0, hide 0 in Excel Picture 10

Step 3:

Switch to Format Cells panel. In the Font tab, go to Color to change it to white , or you can choose any color that matches the Excel background color. Finally click OK to save.

Instructions to delete 0, hide 0 in Excel Picture 11

Step 4:

Thus, the statistics table has also been hidden to zero. This way will convert the color value 0 to match the color of the Excel interface.

Instructions to delete 0, hide 0 in Excel Picture 12

When we black out the entire table, we see a zero as shown below.

Instructions to delete 0, hide 0 in Excel Picture 13

To redo the number 0, you do the same as the above steps also black out the whole table but move the value in 0 to black, or the color created for the previous zero.

Above are 3 ways to handle statistics table, hide all 0 in Excel table. Depending on the version of Excel you are using, as well as the convenience to use, we choose one of the above ways to hide all the 0 in the data table.

Video tutorial hiding 0 in Excel

I wish you all success!