How to display blank if sum is zero in Excel?
When working with data in Excel, you may encounter situations where you want the result of a summation to appear as a blank cell rather than a visible zero. This is particularly useful for reports or dashboards where showing blank values can make results easier to read and understand, or when you want to avoid confusion caused by a lot of zero values cluttering your worksheet. This article introduces several practical methods for achieving this in Excel, explaining not only how to implement each solution but also their suitable scenarios, advantages, and things to watch out for.
Display blank cell if sum is zero with Format Cell function
Display blank cell if sum is zero with formula
Display blank cell if sum is zero with Conditional Formatting
Display blank cell if sum is zero with VBA code
Display blank cell if sum is zero with Format Cell function
Using Excel's Format Cells function, you can format cells with a sum of zero to display as blank. This is a simple formatting approach that doesn't change your actual cell values, only how the results are displayed. It's especially useful if you want to retain the value 0 for calculation purposes, but hide zeros from visual display. This method works well for static tables but requires some additional attention if formulas or data ranges change frequently.
1. Select the cells that contain the sum results you wish to appear blank when zero. These are usually the cells where you have placed your SUM formulas and where the result currently displays as 0. Right-click the selection, then choose Format Cells from the context menu. See the screenshot below:
2. In the Format Cells dialog box, go to the Number tab and select Custom from the Category list. Enter the custom format code General;General;;@ in the Type box, then click OK. Refer to the screenshot:
3. After clicking OK, all the cells that previously showed a sum of zero will appear blank, but the underlying value is still 0. This is useful for keeping underlying calculations intact while presenting a cleaner worksheet. See how the display changes in the image below:
This approach is straightforward and preserves the integrity of your data. However, be aware that the zeros are only hidden visually—the actual value remains in the cell. If you use functions that check for blank cells (such as ISBLANK()), those cells will not be considered empty.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Display blank cell if sum is zero with formula
For situations where you require a truly blank cell (i.e., the result is an empty string rather than a zero value), you can use an IF statement with the SUM function. This is especially convenient if you are preparing data for print, export, or subsequent calculations that specifically recognize blank values. It's an efficient solution suitable for dynamic datasets, especially when copying or dragging formulas to adjacent cells.
1. In the cell where you want to calculate the sum and display as blank if the result is zero, enter the following formula:
=IF(SUM(A1:A3)=0,"",SUM(A1:A3))Replace A1:A3 with your actual data range. This formula checks if the total sum is zero; if it is, it returns a blank cell (empty string), otherwise it displays the sum result.
2. Press Enter to confirm the formula. To apply this logic to other rows or columns, drag the fill handle horizontally or vertically as needed. The cells will display blank when their sum is zero, making your results much clearer for review or printing.
Note: With this formula, the cell will contain an empty string ("") if the sum is zero. In most Excel calculations, this is treated as a blank. However, some functions and data validations may differentiate between an actual blank cell and one with an empty string, so consider downstream uses when applying this method.
A practical tip is to use this method when you want to generate reports that should not display zeros, especially when presenting data to others or importing results into other software systems.
Display blank cell if sum is zero with Conditional Formatting
Another way to visually hide zeros is by using Excel's conditional formatting feature. While this method doesn't make a cell truly blank or change its value, it allows you to set the cell's font color to match the background when the value is zero, giving the appearance of a blank cell. This approach is especially useful in dashboards and presentations where you want to keep the cells' contents but not display zeros to end users.
Here is how you can set this up:
1. Select the cells containing sums you want to conditionally hide when zero.
2. Go to the Home tab, click on Conditional Formatting, and choose New Rule.
3. In the dialog, select Format only cells that contain. Set the rule to Cell Value equal to 0.
4. Click the Format button, choose a font color that matches the cell's fill color (typically white for a white background), and press OK to confirm and apply.
This method doesn't actually make the cell blank—zeros are still present for calculations—but zeros become invisible to users, resulting in a cleaner look. If you later change the background color, remember to update the font color in the conditional formatting rule accordingly.
Display blank cell if sum is zero with VBA code
For advanced users looking to automate this process, a VBA macro can set cells to truly appear blank when their sum is zero. This is particularly useful for large or frequently updated datasets, or when you want a reusable solution that can be applied with one click. Please note, using VBA will directly modify cell content, so make sure to back up your data before running the macro.
1. Go to Developer tab, click Visual Basic to open the Microsoft Visual Basic for Applications window. Click Insert > Module, and paste the following code into the new module:
Sub ClearZeroSumCells()
Dim cell As Range
Dim rng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.InputBox("Select sum result range", xTitleId, Type:=8)
If rng Is Nothing Then Exit Sub
For Each cell In rng
If cell.Value = 0 Then
cell.ClearContents
End If
Next
End Sub2. Press the 
button (Run), or press F5 to execute. A dialog will appear for you to select the target cell range containing your sum results. When you confirm, the macro will clear the contents of the cells with a value of zero, making them truly empty.
Tip: After clearing the contents, formulas in those cells (if any) will be deleted as well, so use with caution if you want to retain your calculation formulas. It's best used on static result ranges.
If you encounter problems such as unexpected cell contents being deleted, double-check your selection range and whether the cells contain formulas or values. Always save your workbook beforehand if running VBA.
In summary, these methods offer flexible options depending on your needs—whether you want to keep the underlying data, visually hide zeros, or truly clear them. For dynamic and formula-driven workbooks, the formula method is often the safest; for presentation purposes, conditional formatting may suffice; and if you need full automation, VBA offers a bulk solution. If you want even more convenient ways to handle zero values and control cell display in bulk, consider exploring dedicated Excel add-ins such as Kutools, which provide user-friendly interfaces for zero hiding and related features—helpful especially for users with large datasets or repetitive formatting needs.
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