Loading...
Quick Links

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.
Jeremy_Cottino_Blank_values_figure_1

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

Jeremy_Cottino_Blank_values_figure_2

So I’ve come up with another way to get rid of those blank values in my tables. It requires playing with conditional formatting.

 

  1. 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.
  1. On the Home tab, go on Conditional Formatting, and click on New rule…

Jeremy_Cottino_Blank_values_figure_3

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

Jeremy_Cottino_Blank_values_figure_4

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

Jeremy_Cottino_Blank_values_figure_5

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

Jeremy_Cottino_Blank_values_figure_6

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

Image Source

 


Related Content

Webinars:
Project and Excel Integration – the application dream team!
Get rid of your last separate Excel sheets: Use your MS project schedule for project financials
Data Analysis Using Project with Excel
MrExcel’s Favorite Excel Tips and Tricks

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


Written by Jeremy Cottino

Jeremy Cottino, PMP, ITIL certified, is a senior project manager with eight years of experience in project planning/scheduling and management of large IS/IT projects for multinational companies. He’s also a technology enthusiast and Microsoft Project MVP. Visit Jeremy’s blog or email him at jcottino@hotmail.com.

Share This Post

Customer Reviews

5
0%
4
0%
3
0%
2
0%
1
0%
0
0%
    Showing 59 reviews
  1. It’s been kicking around the internet for a while, but your screenshots make it very easy to follow.

    https://projectserverpants.wordpress.com/2012/09/07/remove-those-blanking-blanks/

    You can also replace the values right in the cell with spaces to remove the blanks. (just type spaces right in the cell. You’d need to do it for each field that has blanks showing)

    Thanks!

    0

    0

    You have already voted!

    Reply
  2. I have found that the easiest way to get rid of blanks in the pivot table is by using the drop down arrow on the rows box(es), then removing the check box in front of the blanks value (or any other value that you want to exclude). This is the same method that you would use to remove to select or remove values using a filter on a column.
    0

    0

    You have already voted!

    Reply
  3. Hi There, very helpful tip.. may i also know how did you do the color bar on the %completion columns. that looks amazing!
    0

    0

    You have already voted!

    Reply
  4. it was super method to remove blank from Pivot….
    0

    0

    You have already voted!

    Reply
  5. OMG. This is fantastic! Thank you!! Been trying to find a solution to this problem in Excel for a while now.
    0

    0

    You have already voted!

    Reply
  6. There is a better way to deal with it. Simply click on the filter of the field you want to hide the blank from, select Label Filters, does not equal… , then type (blank). That’s it.
    0

    0

    You have already voted!

    Reply
  7. Just an FYI – for those suggesting to filter the individual fields to not show rows with (blank) – this is not the same solution. He wants to see the rows/cells that have blank values – he just doesn’t want to see the word “(blank)” in the field.
    0

    0

    You have already voted!

    Reply
  8. I love this! Thanks 🙂
    0

    0

    You have already voted!

    Reply
  9. Do you type in the (blank)? When I tried typing it in, it automatically corrected it by reading
    =”(blank)”.

    I also tried using the “blank” option instead of the “cell value” option that you have selected but had no luck with that either. Any suggestions?

    Thanks!

    0

    0

    You have already voted!

    Reply
  10. AMAZING!!!!! This was driving me nuts. GREAT solution!
    0

    0

    You have already voted!

    Reply
  11. Thank you, thank you, thank you!
    0

    0

    You have already voted!

    Reply
  12. thank you, this is great
    0

    0

    You have already voted!

    Reply
  13. thank you, this is great.
    0

    0

    You have already voted!

    Reply
  14. it did not work for me. still have the (blank)
    1

    0

    You have already voted!

    Reply
  15. This isn’t for a Pivot Table but a Data Table. Huge difference!
    0

    0

    You have already voted!

    Reply
  16. I did just figure out a solution though. For a pivottable you have to apply the conditional formatting to an area that includes cells outside the pivottable area.
    0

    0

    You have already voted!

    Reply
  17. Thanks Hatem .. It worked perfectly ..
    0

    0

    You have already voted!

    Reply
  18. It really is magic!!
    0

    0

    You have already voted!

    Reply
  19. or instead of numbering format, change the text color and fill to show nothing? for example white on white
    0

    0

    You have already voted!

    Reply
  20. Great solution. Thanks!
    It’s not perfectionism at all, just something that Excel should provide an option for in the first place.
    0

    0

    You have already voted!

    Reply
  21. THANK YOU!!
    0

    0

    You have already voted!

    Reply
  22. Excellent solution, thank you!
    0

    0

    You have already voted!

    Reply
  23. Thanks for the screen shots!!!! HUGE help!
    0

    0

    You have already voted!

    Reply
  24. awesome !! Thanks a lot !
    0

    0

    You have already voted!

    Reply
  25. Super idea! Thanks for sharing!
    0

    0

    You have already voted!

    Reply
  26. This was truly magical. THANK YOU!
    0

    0

    You have already voted!

    Reply
  27. This really is magic…..! Was super cynical but followed the clear instructions and it worked first time….as if by magic 😉 Thanks…..!!!
    0

    0

    You have already voted!

    Reply
  28. I’ve struggled with this for years and this was a simple easy to implement solution! THANKS!
    0

    0

    You have already voted!

    Reply
  29. Need help, if there are 2 columns and we need to get rid of all (blank) in display,

    Col1 Col2
    47 (blank)
    (blank) 1.2
    4.5 4.5
    3.2 (blank)
    (blank) 3.3
    125 141
    2 2

    0

    0

    You have already voted!

    Reply
  30. Select the whole worksheet or the 2 columns and do the same
    0

    0

    You have already voted!

    Reply
  31. Thank you! Thank you! Thank you!
    0

    0

    You have already voted!

    Reply
  32. brilliant! perfect solution
    0

    0

    You have already voted!

    Reply
  33. Thank you!!! <3
    0

    0

    You have already voted!

    Reply
  34. In WWF style….’This is AweSome’…’This is AweSome’
    0

    0

    You have already voted!

    Reply
  35. Dear “Comment by Miss P on 07/31/2017 at 2:34 pm”

    Thank you, couldn’t agree more 🙂

    Mr P

    0

    0

    You have already voted!

    Reply
  36. Brilliant
    0

    0

    You have already voted!

    Reply
  37. Sir. you are a wizardd
    0

    0

    You have already voted!

    Reply
  38. My god, this is EXACTLY what I was looking for. I’ve read in the comments that some people just filter out the “blank” but that “solution” did not fulfilled what I needed.

    Great solution to a “simple” problem.

    0

    0

    You have already voted!

    Reply
  39. Great!. Thanks.
    0

    0

    You have already voted!

    Reply
  40. PERFECT! Thank you so much!!

    #semicolonsaremagic

    0

    0

    You have already voted!

    Reply
  41. Use Office 365 version of Excel and this did not work.
    0

    0

    You have already voted!

    Reply
  42. Found the problem and now it works!!!!!!
    0

    0

    You have already voted!

    Reply
  43. It only hides. It wont remove them
    0

    0

    You have already voted!

    Reply
  44. Muito obrigado.
    0

    0

    You have already voted!

    Reply
  45. Thank you . Great
    0

    0

    You have already voted!

    Reply
  46. Top Answer, ignore the other suggestions, they filter.
    0

    0

    You have already voted!

    Reply
  47. Perhaps a simpler solution. Select all cells in range containing blanks you want to ignore on pivot then do find replace. Leave Find value blank and enter apostrophe [ ‘ ] as the replace value. Hit Find Next to check that it’s finding blank cells, then once you’re comfortable hit replace all. Refresh your pivot and, waalaa. It will take longer to read this than to do the actual steps 🙂
    0

    0

    You have already voted!

    Reply
  48. Probably shouldn’t have used the word ignore in my post above. No rows/columns are hidden or left out. It changes the values from (blank) to show as empty cells on pivot table just as the author intended.
    0

    0

    You have already voted!

    Reply
  49. Just want to say “thanks”. I had tried all of the obvious ways to remove the display of (blank) in my pivot table, and your step by step instructions were perfect.
    0

    0

    You have already voted!

    Reply
  50. This is amazing, just what I was looking for and worked like a charm. Thank you!!
    0

    0

    You have already voted!

    Reply
  51. Efficient. Thanks
    0

    0

    You have already voted!

    Reply
  52. Thanks, very helpful
    0

    0

    You have already voted!

    Reply
  53. This was excellent. It took three rounds of trying. 1/2 the time my conditional formatting doesn’t work properly.
    0

    0

    You have already voted!

    Reply
  54. I need help, trying to hide all the zeros from the excel pivot table
    0

    0

    You have already voted!

    Reply
  55. Thank you so much! This actually works!!! Unlike the other methods posted on the internet.
    0

    0

    You have already voted!

    Reply
  56. Why not simply check the box “Add this data to the Data Model” Under “Choose whether you want to analyze mutliple tables”.

    Then blank values will be hidden

    0

    0

    You have already voted!

    Reply
  57. Brilliant, Jeremy! Problem solved.
    0

    0

    You have already voted!

    Reply
  58. Use conditional format, but make the font white
    0

    0

    You have already voted!

    Reply
  59. A simple method to remove the word (Blank) in excel sheet – especially in Pivot Table. Click any cell having the world (Blank) – hit Space Bar and then hit ENTER. All (Blank) word would disappear.
    0

    0

    You have already voted!

    Reply

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Thanks for submitting your comment!