Buzz
Previously, if you attempted manual deletion, it would require a significant amount of time, especially with spreadsheets containing numerous cells with zero values, making manual deletion impractical. Let's follow along and utilize the available tools with Mytour to remove zeroes in Excel quickly and easily.
How to Remove Zeroes in Excel
I. Guide to Removing Zeroes in Excel Using Excel Options
- 1. Quick Guide
- Step 1: Open the Excel spreadsheet.
Step 2: Go to Excel Options => Advanced => In the Display options for this worksheet, uncheck the box next to Show a zero in cells that have zero value.
2. Detailed Guide to Removing Zeroes in Excel Using Excel Options.
Step 1: Open your Excel sheet file.
- For example, we have the following sheet file:
Step 2: Go to Excel Options => Click on Advanced. In the Display options for this worksheet, find and uncheck the box next to Show a zero in cells that have zero value. Press OK to complete.
- For Excel 2007, 2010: Click on the Office icon => Excel Options.
- Upon completion, you will achieve the following result.
II. Guide to Removing Zeroes in Excel via Format Cells
- 1. Quick Guide
- Step 1: Open the Excel sheet.
Step 2: Select the area needing to hide zeroes => Right-click and select Format Cells .
Step 3: In Category, choose Custom. In the Type field, type the sequence: 0;-0;;@. Press OK to save settings.
2. Detailed Guide to Removing Zeroes in Excel via Format Cells
Step 1: Open your Excel sheet file.
Step 2: Select the area needing to hide zeroes. Right-click and choose Format Cells.
Step 3: In the Format Cells dialog, under Category, select Custom. Then, in the Type field, type the sequence: 0;-0;;@. Then press OK to save changes.
- Upon completion, the result is as follows:
III. Guide to Removing Zeroes in Excel with Format Feature
- 1. Quick Guide
- Step 1: Open the Excel sheet file.
Step 2: Select the entire data table => Go to Home => Conditional Formatting => New Rule....
Step 3: Choose Format only cells that contain => Under equal to, enter 0 => Click Format.
Step 4: Select a color matching Excel's background color under Color => Press OK.
2. Detailed Guide to Removing Zeroes in Excel with Format Feature.
Step 1: Open your Excel sheet file using Excel.
Step 2: Select the entire data range containing zeroes to hide. Then, go to the Home tab => Conditional Formatting => New Rule.
Step 3: The New Formatting Rule dialog appears. Click on Format only cells that contain. Then, choose equal to and enter 0 in the adjacent box => Click Format.
Step 4: Now, the Format Cells dialog box appears. Select white color under Color and then press OK to complete.
- With this method, zeros will be hidden because they match Excel's background color. When you select the table again, you will see zeros appearing as shown below.
So, there are numerous ways for you to delete and hide zeros in Excel. You can choose and use them in different situations. Additionally, you can refer to the article How to write zeros in Excel to enrich your document with more data.
