Excel QuickBytes# 8: Hiding Zeros

QuickBytes is a series of articles for simple and easy to implement techniques with exponential benefits

Usually, we start designing a report, dashboard, or a presentation slide with a blank white space. Each element, color, or texts we add to the page competes for attention from our viewers. It implies that we should be careful about what we put on that space. Limit the ink we use to convey our message.

Let us consider the following example—a simple table with sales details by manager and order priority.

Each element in the table is equally competing for our attention.

Compare it with the following table:

In the above example,

  • Numbers are more apparent as the borders have taken a step back and not competing with the numbers.

  • Cells with zeros appear as blanks. It makes our table cleaner and the viewers can pay more attention to the sales numbers.

  • Numbers have a thousand-separator and a different format for negative numbers.

This article explains how to hide zeros in an Excel table.

Step 1: Select a base format

Select the cells > CTRL+1 (for Format Cell) > Select an appropriate format

In the above example, I have selected Number format with a negative number format and a select Use 1000 separator(,).

Step 2: Custom Format

Go to Custom Format > Under type, we find the structure of the format we have selected.

In Excel, the number format has a separator - semicolon (;).

In the example, Excel has applied format for positive and negative separated by a semicolon(;). All we require is to add the second semicolon (;) after the negative format structure. Any structure defined after the second semicolon is for the zeros.

And if we leave it blank after the second semicolon, then that is how Excel is going to display zeros: blank.

And that is it.

Clutter is a distraction. Please get rid of it!


Drop Me a Line, Let Me Know What You Think



+91 9871-641-146

Join WhatsApp group: BI Simplified