How to Hide, Replace, Empty, Format (blank) values with an empty field in an Excel Pivot Table without using filters –Step-By-Step


I have been struggling with this for a while.  Excel puts the word (blank) on  a pivot table field if it does not have any data.  I want the field displayed without data.  I see in Excel 2013 there is a checkbox under Pivot Table options but I could not get it to work Sad smile

image 

I did however finally figure it out.  I could solve it using conditional formatting.  I many workarounds online in the forums but unfortunately, after a data refresh, the pivot would revert.  Using conditional formatting we do not have this problem.

  1. Select the cells that you want to conditionally format [have a (blank) value] (you could select the entire column or even the table too if you want to do multiple columns [even if they do not have (blank) values)
  2. On the Home Tab of the Ribbon Select Conditional Formatting
  3. From the Dropdown, select New Rule

    SNAGHTML44b65bd

  4. Select Format only cells that contain
  5. In the drop down box box select Cell Value
  6. in the second drop down box select equal to
  7. In the third box type in “(blank)” without the quotes
  8. Click the Format… Button

image

  • On the Number tab; select Custom
  • Then to the right, under the word Type: just type in 3 semi-colons “;;;”  without the quotes
  • Click OK

    image

  • If you want to change anything later (like the range to select the entire column or entire table) you can click Conditional Formatting then Manage Rules
  • Change the range to whatever you want the range to be and click OK
  • If you want to delete the rule; click Delete Rule

    image

  • The final result will look like the following:
    image

Comments (40)

  1. Debra Dalgleish says:

    You can also select one of the cells that contains "(Blank)" and type a space character to replace the text.

    The option "For empty cells" only affects cells in the Values area, not the row or column labels.

  2. Roxanne says:

    Thanks for the help! 🙂

  3. Mathew Thomas says:

    this is the only solution that actually works ..read atleast 5 other links and all of them fail on refresh..thanks a lot!!

  4. Edwin says:

    It works!!!! Love the 3 semi-colons “;;;”

  5. SSP says:

    Debra Dalgleish is the queen.

  6. twhit says:

    I do something that is much simpler, that I think provides the same result; it definitely looks the same. Click on the "Row Label" filter and uncheck Blanks. Then they don’t show up, aren’t counted in the totals and remain hidden upon refresh. Is there
    a reason to do it the way outlined here? If this method will make using my data easier to use I’m all for it but I can’t see a difference in the result.

  7. DT in NC says:

    Awesome!

  8. DT in NC says:

    Awesome!

  9. crop1645 says:

    perfect – thanks so much

  10. nick says:

    Problem with this solution, is it hides the word but it breaks the clean formatting of your Pivot Table by adding a pointless row. Try this, in the Pivot Table, click the drop-down carrot for the field you want to remove blanks from, click "label filters,"
    then "does not equal," and type in the box "(blank)." [without the quotes] This will filter out any (blank) records, but does not seem to restrict new entries or require clicking the "(blank)" box the same way directly filtering it does.

  11. Your example was easy to follow and works perfectly. Thanks so much for sharing

  12. kate spade says:

    hollister clothing, http://www.hollisterclothing-store.in.net/
    insanity workout, http://www.insanityworkout.net.co/
    iphone cases, http://www.iphone-cases.us/
    jimmy choo, http://www.jimmychoo.net.co/
    jordan retro, http://www.jordanretro.org/
    jordan shoes, http://www.jordan-shoes.com.co/
    juicy couture outlet, http://www.juicycouture.com.co/
    juicy couture handbags, http://www.juicycoutureoutlet.net.co/
    kate spade, http://www.kate-spade.com.co/
    kate spade handbags, http://www.katespade-outlet.com.co/
    kate spade outlet online, http://www.kate-spades.com/
    longchamp, http://www.longchamp.us.org/
    longchamp handbags, http://www.longchamp.com.co/
    louboutin, http://www.louboutin.jp.net/
    louis vuitton canada, http://www.louis–vuitton.ca/
    louis vuitton bags, http://www.louisvuitton.jp.net/
    louis vuitton, http://www.louis–vuitton.org.uk/
    vuitton handbags, http://www.louisvuitton.so/
    louis vuitton outlet online, http://www.louisvuittonas.com/
    louis vuitton, http://www.louisvuitton-outlet.com.co/
    louis vuitton, http://www.louisvuitton-outlets.us/
    marc jacobs handbags, http://www.marcjacobsonsale.com/
    mcm bags, http://www.mcm-bags.net/
    mcm backpack, http://www.mcmhandbags.com.co/
    michael kors, http://www.michaelkors.so/
    cheap michael kors, http://www.michael–kors.us.com/
    michael kors bags sale, http://www.michaelkorsbags.us.org/
    michael kors, http://www.michaelkorshandbags.org.uk/
    michael kors outlet online, http://www.michael-kors-handbags.us.com/
    michael kors outlet, http://www.michaelkorsoutlet.ar.com/
    michael kors outlet, http://www.michael-kors-outlet.us.org/
    michael kors outlet online,
    http://www.michaelkorsoutlet-online.ar.com/
    michael kors outlet online sale,
    http://www.michael-kors-outlet-online.us.org/
    michael kors outlet store,
    http://www.michaelkorsoutletonline-sale.us.com/
    michael kors uk, http://www.michaelkors-uk.org.uk/
    mont blanc pens, http://www.montblanc–pens.net/
    new balance, http://www.newbalance-outlet.org/
    nike air max, http://www.nike-air-max.us/
    nike factory, http://www.nikefactory.org/
    nike free, http://www.nikefree5.net/
    nike free run, http://www.nikefree-run.net/
    nike free, http://www.nikefree-run.org.uk/
    air jordans, http://www.nike-jordanshoes.com/

  13. sunriseebc says:

    TY…TY…TY…!!!!

  14. JofelAd says:

    is it same with the slicer?
    , it also have {Blanks}….haven’t tried it yet

  15. DL says:

    Great, finally a solution that works. Thanks.

  16. crf123 says:

    http://www.cheapmichaelkors.us.org/
    http://www.cheap-nikeshoes.net/
    http://www.cheapoakley.us.org/
    http://www.cheapoakleysunglasses.ar.com/
    http://www.cheap-oakleysunglasses.in.net/
    http://www.cheaprayban.com.co/
    http://www.cheapshoes.com.co/
    http://www.cheap-uggboots.in.net/
    http://www.chiflatiron.net.co/
    http://www.christian–louboutin.in.net/
    http://www.christianlouboutin.org.uk/
    http://www.christianlouboutinshoes.jp.net/
    http://www.coachfactory-outlet.co.com/
    http://www.burberry–outlet.in.net/
    http://www.burberryoutlet.me.uk/
    http://www.burberryoutlet.club/
    http://www.burberryoutlet-online.in.net/
    http://www.calvinklein.co.com/
    http://www.celinebags.org/
    http://www.chanelhandbags.net.in/
    http://www.cheap-baseballbats.net/
    http://www.christianlouboutinshoes.ar.com/
    http://www.cheap-jerseys.us.org/
    http://www.cheap-jordans.net/
    http://www.coachfactory-outlet-online.in.net/
    http://www.coach-handbags.com.co/
    http://www.handbagsoutlet.com.co/
    http://www.hilfigeroutlet.in.net/
    http://www.edhardy.in.net/
    http://www.levisjeans.com.co/
    http://www.bcbgdresses.net/
    http://www.bebeclothing.net/
    http://www.harrods-london.co.uk/
    http://www.guccishoes.com.co/
    http://www.ralphlaurenoutletonline.us.org/
    http://www.lululemoncanada.ca/
    http://www.yoga-pants.ca/
    http://www.nike-shoes-canada.ca/
    http://www.pandora-charms-canada.ca/
    http://www.rolex-watches-canada.ca/
    http://www.michael-kors-canada-outlet.ca/
    http://www.adidas–canada.ca/
    http://www.tiffanyandco-canada.ca/
    http://www.hollistercanada.ca/
    http://www.abercrombie-and-fitch.ca/
    http://www.oakley-sunglasses-canada.ca/
    http://www.wedding–dresses.ca/
    http://www.swarovskicanada.ca/
    http://www.tommy-hilfiger-canada.ca/
    http://www.new-balance.ca/
    http://www.burberrycanada.ca/
    http://www.the-north-face.ca/
    http://www.ralph-lauren.ca/
    http://www.ray–ban.ca/
    http://www.coachoutlet-online.com.co/
    http://www.coachoutletstore.net.co/
    http://www.coachoutletstore-online.com.co/
    http://www.coco-chanelbags.com.co/
    http://www.converse.net.co/
    http://www.designerhandbagsoutlet.com.co/
    http://www.eyeglassesonline.us.org/
    http://www.eyeglassframes.us.org/
    http://www.fashion-clothing.us.org/
    http://www.ferragamo.com.co/
    http://burberry.outletnow.org/
    http://christianlouboutin.outlet-shoes.net/
    http://toryburch.salesandals.net/
    http://www.abercrombieand-fitch.com.co/
    http://www.abercrombiefitch.us.com/
    http://www.abercrombie-kids.us.com/
    http://www.air-huarache.co.uk/
    http://www.airjordans.us/
    http://www.airjordanshoes2015.com/
    http://www.airmax-2015.org/
    http://www.airmax-90.org/
    http://www.babylisspro.in.net/
    http://www.basketballshoes.com.co/
    http://www.beatsbydrdre.co.com/
    http://www.beatsbydre.com.co/
    http://www.beats-by-dre.com.co/
    http://www.beatsheadphones.in.net/
    http://www.bottega.us/
    http://www.burberry-handbags.in.net/
    http://www.ferragamoshoes.net/
    http://www.ghdhairstraightener.com.co/

  17. Frits says:

    Searched all internet but and finally an easy solution that really works. Thanks!!

  18. Auto & Motor says:

    http://www.topautomag.net/category/auto-motor Motor car insurance is quite necessary to suit your needs and your car or truck. It can be as critical in your protection since having couch belts
    or high quality tires on your own motor car.

  19. Golf Travel & Resorts says:

    http://www.penguintravel.net/category/golf-travel-resorts Many of the finest golf instructors in the us are inside Arizona! Among the better resorts come in Arizona! Appear, stay,
    and study on the finest!

  20. Automobile says:

    http://www.directoryautomotive.net/category/auto-repairautomobile In case you might have an automobile accident or your car or truck gets ripped off, it can be quite a big
    damage. Until and until you are covered by insurance or your car or truck is covered by insurance.

  21. Auto Repair says:

    http://www.automotivedeals.net/category/auto-repair An unforeseen auto fix bill may be stressful news on this unstable economic system. If that is true available for you, here are usually
    some techniques you could possibly save money on your own next automobile repair costs.

  22. Auto & Motor says:

    http://www.caraudiomanual.net/category/auto-motor Do you understand how generator controls perform? These can control the particular movement regarding motor. It is designed for total generator
    control. There are numerous types. Read this informative article to comprehend motor handles.

  23. admin says:

    The particular strategic benefit of logos and the way to evaluate the brand, plus ways you could increase the worth of the brand. Just about the most commonly overlooked reasons for competitive edge is brand name. Branding is not only advertising, nor
    can it be simply any catchy name to get a company or perhaps product.
    http://www.brand-line.net

  24. pascal1957 says:

    great suggestions found here !! thanks to all.
    another interesting method I found at
    http://blog.contextures.com/archives/2008/12/11/change-blank-labels-in-a-pivot-table/
    quite handy to quickly replace empty – ie. "(blank)" – occurrences in your pivot, without affecting the original table

  25. Trevor says:

    This helped greatly. Thank you!

  26. Excel User says:

    This is great. Instruction is clear and it works. Thanks a lot

  27. wardie says:

    This works!! Thank you 🙂

  28. J G says:

    Can you replace it with anything else (i.e. ‘N/A’) using this method?

  29. Dr Sylvester Benson says:

    GET YOUR PROBLEM SOLVE TODAY WITH MY PROFESSION IN ANY SPIRITUAL SPELL OR ANY KIND OF PHYSICAL BATTLE THAT NEED, MY NAME IS DR SYLVESTER AND THIS IS MY EMAIL FOR CONTACT (stbenson391@gmail.com) OR YOU CAN FOLLOW HIM UP ON FACEBOOK BY MY NAME (SYLVESTER E BENSON)
    ON FACEBOOK OR CALL ME ON MY MOBILE NUMBER +2348136090988, AM ALWAYS AVAILABLE TO RENDER YOU HELP WITH EXPERIENCE OF 32 YEARS IN SPELL CASTING AND HERBAL MEDICURE TO CURE ANY KIND OF DISEASE THAT YOU MAY HAVE, CONTACT ME ON ANY KIND OF ISSUES.

  30. Amsakanna says:

    Thank you Debra Dalgleish. Your comment helped.

  31. karlh says:

    You can also just add the measure value as a filter and filter out blanks…

  32. karlh says:

    You can also just add the measure value as a filter and filter out blanks…

  33. mansoor says:

    Great – Thanks for this 🙂

  34. Chillnz says:

    Thanks for this tip. It is the only won that worked to clear blanks in Rows/Columns fields for a tabular style table. All built in options in Excel only work for the Values Fields.

  35. Lise says:

    Very very useful. Thanks you

  36. Fabio says:

    Appreciate bro

  37. Fabio says:

    Appreciate bro

  38. TK says:

    If you do not want to display blank row or column, you can just right click on the header (for example blank), then filter and click "hide selected item). Even if you refresh, it will not show up

Skip to main content
Real Time Web Analytics