How to hide zero percent in Excel?
When working with data in Excel, especially those formatted as percentages, displaying unnecessary zeros such as "0%" can sometimes clutter your worksheets, making them less visually appealing or harder to interpret. There are situations, such as preparing reports for presentations or sharing data with stakeholders, where hiding these zero percent values can help with clarity. This tutorial provides practical solutions to selectively hide0% values in Excel while ensuring the integrity of your data remains unchanged.
Hide zero percent | Excel Formula – Use IF to hide zero percent | VBA Code – Conditionally format or hide zero percent
Hide zero percent
If you want to conceal zero percent values in your table, Excel’s custom cell formatting provides an efficient way to visually hide them. This method is best suited to cases where you want the data values to remain in the cell, but not be displayed as "0%" in your worksheet output.
Follow these instructions:
1. Begin by selecting the range of cells containing your percentage data. Then, right-click on the selected cells and choose Format Cells from the context menu. This opens the Format Cells dialog box, where you can customize how the data is displayed.
2. Within the dialog, navigate to Number > Custom on the left pane. In the Type textbox, enter the following format code:
0%;-0%;" "
This custom format is designed so that:
- Positive percentages will display as usual.
- Negative percentages will show with a minus sign.
- Zero percentages will appear as blank cells, hiding the "0%".
3. Click OK to apply the changes. You should now see that every cell that previously showed "0%" is now empty, while other values remain visible as percentages.
 |
 |
 |
Tip: If your percentage data includes decimal values (for example,63.25%), you can adjust the format code. Enter 0.00%;-0.00%;" " in the Type box. This will keep decimal places where appropriate and still hide zero percent values.
Note: This formatting approach only affects how the values are displayed. The actual cell value remains unchanged, and calculations based on these cells will still reference the real data (including the zeros). If you change the formatting back to something else, such as General or Number, the zeros will reappear. Also, this method is purely cosmetic and does not prevent Excel functions from accessing or using zero values in computation.
Applicable scenarios for this technique include financial analysis, progress tracking, and reporting, where you want to emphasize meaningful data only. The main advantage is that it’s non-destructive, meaning your underlying data stays intact. However, if you copy the formatted data to another workbook or cell without custom formatting, zeros may show again.
Excel Formula – Use IF to hide zero percent
For situations where you need the displayed result to be something other than the actual cell value, or if you want zero percent values to appear blank without changing cell formats, you can use an Excel formula with the IF function. This approach is particularly useful when you want to generate results in new cells based on existing data.
1. Click on the cell where you want the result (for example, cell B1) and enter the following formula:
=IF(A1=0,"",A1)2. Press Enter to confirm the formula. If the percentage in A1 is zero, this cell will appear blank; otherwise, it will show the original value from A1.
3. To apply this formula to a range, copy cell B1 as needed (drag the fill handle down or use Ctrl+C and paste to the desired range). You can then format the result cells as percentages if you want the values to display with a percent sign. If you are referencing a range (A2, A3, etc.), ensure the cell reference remains relative as you copy the formula. This method is preferable when you want to create a clean output table for reporting or sharing, separate from your source data.
Note that when using this formula, the result cells may look blank when the value is zero, but the underlying formula is still present. If you need those cells to be truly empty for further calculations, consider using additional functions such as IF(A1=0,NA(),A1) to display #N/A instead of blank.
This solution is best for dynamic tables, dashboards, or input forms where the source data may change and you want the output to smartly hide zeros. One minor drawback is that you must maintain separate result columns; editing the original values will require adjusting the referenced formula range accordingly.
VBA Code – Conditionally format or hide zero percent
For advanced control, especially with large tables or repeat tasks, VBA code allows you to programmatically format or even clear cells that display zero percent. This solution is suitable if you want to automate the process or customize cell formatting depending on your specific needs. You can either hide zeros by removing values, changing font color, or applying other formatting options.
1. Click Developer Tools > Visual Basic. When the Microsoft Visual Basic for Applications window appears, go to Insert > Module and paste the following code into the module:
Sub HideZeroPercent()
Dim rng As Range
Dim cell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select percent cells to process", xTitleId, rng.Address, Type:=8)
For Each cell In rng
If cell.Value = 0 Or cell.Value = 0# Then
cell.NumberFormat = ";;;"""""
ElseIf cell.Value <> "" Then
cell.NumberFormat = "0%"
End If
Next cell
End Sub2. After entering the code, click the 
Run button to execute the macro. A dialog box will prompt you to select the range of percent cells you want to process. The macro will set the cell formatting so that any zero-value cells within the selected range become invisible (effectively hidden), while all other cells retain standard percentage formatting.
Tip: The VBA approach gives you flexibility for batch operations across multiple sheets or workbooks. You can modify the code to, for example, clear cell contents if a zero is found, or to change the font color rather than hiding the value. Always back up your data before running macros, as automation may affect your worksheet more broadly than manual methods.
If the macro does not run, check that macros are enabled in your Excel settings and that you have selected valid percent-formatted cells. If cells are set as text or other formats, adjust as necessary. For troubleshooting errors, ensure all cells in the selected range are numeric values; otherwise, you may need to add error checks for text or blank cells.
This code is most useful for repeated tasks and for cleaning up data prior to reporting or sharing. It speeds up large-scale formatting and allows for easy updates if your data changes frequently.
Relative Article:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in