How To Remove (blank) Values in Your Excel Pivot Table

You might call it perfectionism, but I hate when my reports don’t look their best. One of the most irritating elements is the “(blank)” text you may have on your Excel pivot tables.

I’ve tried some pivot table options to eliminate that word, “blank,” but nothing seems to work properly.

So I’ve come up with another way to remove the blank from a pivot table. It requires playing with conditional formatting.

Select the cells you want to remove that show (blank) text. It could be a single cell, a column, a row, a full sheet or a pivot table.
On the Home tab, go on Conditional Formatting, and click on New rule…

Select Format only cells that contain.
Set that rule type to Cell Value | equal to | and input (blank) text.

Click on Format…
On the Number tab, select Custom category, then type three semi-colons: ;;;

I must admit, it’s a kind of magic.

A version of this article first appeared on Jeremy Cottino’s blog.

Image Source

Learn how an MPUG Membership helps individuals and teams become better project managers and Microsoft Project users through Microsoft Project Training.

Join MPUG to attend live training webinars, access 500+ hours of on-demand sessions, receive certificates of completion and earn the Project Management Institute (PMI)® Professional Development Units (PDUs) that you need.

Watch an MPUG training webinar for free and improve your Microsoft Project skills in less than 1 hour.

Articles:
Microsoft® Excel Keyboard Shortcuts
A Free Project Pipeline Tracker for Excel
Effectively Using Excel for Analyzing MS Project Data
Importing Data from Excel to MS Project

You might call it perfectionism, but I hate when my reports don’t look their best. One of the most irritating elements is the “(blank)” text you may have on your Excel pivot tables.

I’ve tried some pivot table options to eliminate that word, “blank,” but nothing seems to work properly.

So I’ve come up with another way to remove the blank from a pivot table. It requires playing with conditional formatting.

Select the cells you want to remove that show (blank) text. It could be a single cell, a column, a row, a full sheet or a pivot table.
On the Home tab, go on Conditional Formatting, and click on New rule…

Select Format only cells that contain.
Set that rule type to Cell Value | equal to | and input (blank) text.

Click on Format…
On the Number tab, select Custom category, then type three semi-colons: ;;;

I must admit, it’s a kind of magic.

A version of this article first appeared on Jeremy Cottino’s blog.

Image Source

Learn how an MPUG Membership helps individuals and teams become better project managers and Microsoft Project users through Microsoft Project Training.

Watch an MPUG training webinar for free and improve your Microsoft Project skills in less than 1 hour.

Learn how an MPUG Membership helps individuals and teams become better project managers and Microsoft Project users through Microsoft Project Training.

Continue Reading

The Compass vs. The Clock: Why Direction Beats Deadlines in Project Management

The Scripts That Save Projects: What to Say When Everything Goes Wrong

How Reserves Keep Projects Alive

Get Weekly PM Insights

Follow Us

Learn how an MPUG Membership helps individuals and teams become better project managers and Microsoft Project users through Microsoft Project Training.

Get Weekly PM Insights

Follow Us

How To Remove (blank) Values in Your Excel Pivot Table

Continue Reading

The Compass vs. The Clock: Why Direction Beats Deadlines in Project Management

The Scripts That Save Projects: What to Say When Everything Goes Wrong

How Reserves Keep Projects Alive