Leave a Reply

Your email address will not be published. Required fields are marked *

How to Use Gray Dashes Instead of Zeros in Tables

Updated on: Dec 5th, 2022
Data Visualization
, , , ,
Two tables side by side. One table has zeros with black ink. The other table has zeros shown as dashes in gray ink.

Ready to fine-tune your tables?

Here’s one of my favorite table decluttering tips: Change the black zeros into gray dashes—with formulas and conditional formatting.

Before: Zeros in Black

Does your table have lots of zeros?

Sometimes my tables have huge values—numbers, percentages, or currency into the thousands, millions, or even billions.

Other times, my tables have small values—lots of single digit and double digit numbers, and even some zeros, like this one.

I recently worked on a dashboard project with a foundation where our n was just 11 grantees. It was the smallest n I’d worked with in a while!

This table is fictional, but you get the idea.

Here’s the old way to show zeros: In black text.

After: Manually Adding Dashes with the Single Quote “Finger Wag”

Let’s replace the 0s with dashes.

In Excel, here’s how:

  • If you type a dash, Excel will get confused. Excel thinks we’re creating a subtraction formula.
  • Instead, start with a single quote, a.k.a. the Finger Wag. We’re scolding Excel. The single quote means, “Don’t change my format! Leave whatever I type after the single quote alone! This isn’t supposed to be a subtraction formula! It’s just a dash!”
A GIF showing how to type a single quote and a dash into a cell.

The finished product would look like this (below).

Now, we can actually see the zeros better!

It’s easier to see that Grantee H had an entire row of zeros. Variables D and E also had more zeros than the other variables.

A screenshot from Excel showing all the zeros turned into dashes.

After: Manually Making the Dashes Light Gray

Let’s keep going.

I usually make the dashes gray (so they disappear even more).

You can simply change the font color.

A GIF showing how to change the font color from black to gray in Excel.

After: Automatically Adding Dashes with an IF Statement

But what if your tables values come from another source? (So there are formulas living behind the scenes?) In that case, we can’t simply type a dash by hand. It would remove the formula.

In this example, I’m pretending we’ve got a separate dataset on its own sheet.

The table is a sum of all those entries.

My tables often have sumifs, countifs, xlookups, and more living behind the scenes, like this:

A GIF showing that there are sumif formulas inside the Excel spreadsheet.

We can adjust our existing formulas just a tad.

I add IF statements so that if my formula results in a zero, then it shows a dash:

A GIF showing how to create an IF statement such that zeros are automatically shown as dashes.

After: Automatically Making Dashes Gray with Conditional Formatting

Finally, let’s add another time-saver.

Let’s use Conditional Formatting to automatically turn the dashes gray.

The fewer manual changes, the better!

Formulas and Conditional Formatting save our future selves a lot of time. If we add new entries to our dataset, then the formulas will automatically update.

Formulas and Conditional Formatting also reduce typos. We won’t accidentally forget about one of the zeros.

Here’s how:

  1. Highlight the table.
  2. Go to the Home tab.
  3. Click on the Conditional Formatting button.
  4. Choose Highlight Cells Rules.
  5. Choose Text That Contains.
  6. In the pop-up window, type a dash.
  7. Instead of “Light Red Fill with Dark Red Text,” go to the bottom of the drop-down list, and choose Custom Format.
  8. On the next pop-up window, change the font color to gray.

That sounds like a lot of steps, but it’s fast, I promise!

Once you get familiar with Conditional Formatting, it only takes this long:

A GIF showing how to apply Conditional Formatting in Excel.

Yes, You Can Transfer Formatted Tables from Excel into PowerPoint or Word

Here’s how you’ll transfer your table into Word:

  1. In Excel, finish formatting the table. I added gray lines between the rows and I “hid” the grid lines from the cells (View tab –> uncheck the Gridlines box. Sure, you can adjust the borders later in Word, but table formatting is generally a pain in Word. I try to do 99.9% of the formatting in Excel.
  2. In Excel, I highlighted and copied the table with good ol’ CTRL + C.
  3. In Word, I went to Paste –> the down-arrow–> Keep Source Formatting.
  4. Then, in Word, I re-sized the table to fit the page.

It looks like this:

A GIF showing how Paste Special the table from Excel into Word.

Here’s how you’ll transfer your table into PowerPoint:

  1. In Excel, finish formatting the table. I added gray lines between the rows; I “hid” the grid lines from the cells (View tab –> uncheck the Gridlines box); and I re-sized the columns (wider) and rows (taller). Sure, you can adjust these things in PowerPoint later… but it’s such a pain. I prefer formatting in Excel first, and then transferring to PowerPoint.
  2. In Excel, I highlighted and copied the table with good ol’ CTRL + C.
  3. In PowerPoint, I went to Paste –> the down-arrow –> Keep Source Formatting.
  4. Then, in PowerPoint, increase the font size. I recommend 18+ for presentations.

Please don’t take screenshots from Excel and paste them into other programs. Your table will get blurry.

Please don’t transfer tables and graphs as images. They’ll get blurry.

A GIF showing how Paste Special the table from Excel into PowerPoint.

Please don’t take screenshots from Excel and paste them into other programs. They’ll be blurry.

Please don’t transfer tables and graphs as images. They’ll be blurry.

Download the Spreadsheet

If you’re familiar with sumifs, if, and Conditional Formatting, then this will be fast and easy for you!

If you’re new to sumifs, if, and Conditional Formatting, then download my spreadsheet. You can click through the formulas yourself to see how everything is set up: https://depictdatastudio.gumroad.com/l/UseGrayDashesInsteadOfZeros

More about Ann K. Emery
Ann K. Emery is a sought-after speaker who is determined to get your data out of spreadsheets and into stakeholders’ hands. Each year, she leads more than 100 workshops, webinars, and keynotes for thousands of people around the globe. Her design consultancy also overhauls graphs, publications, and slideshows with the goal of making technical information easier to understand for non-technical audiences.

Leave a Reply

Your email address will not be published. Required fields are marked *

You Might Like

Our complimentary mini course for beginners to dataviz. Takes 45 minutes to complete.

Enroll

How to Fix Dense Maps (with Small Multiples)

In this 2-minute video, you’ll see how a map with 2 variables… should actually be 2 small maps.

More »

Inside our flagship dataviz course, you’ll learn software-agnostic skills that can (and should!) be applied to every software program. You’ll customize graphs for your audience, go beyond bar charts, and use accessible colors and text.

Enroll

Subscribe

Not another fluffy newsletter. Get actionable tips, videos and strategies from Ann in your inbox.