Hide #DIV/0! From Excel Pivot Table

Hide #DIV/0! From Excel Pivot Table

Conditional formatting #DIV/0! within Excel is easy enough, but that doesn’t work in Pivot Tables.

Luckily, there’s an option within Pivot Table options that allows you to configure what appears when an error value is raised.

Hide Div 0

Hide Div 0

Simply check For error values show: and, in my case, leave the entry blank. Or set it to 0. Whatever works best for you. Divide by zero be gone with you.

Hope this helps!

Mike250

Australian. Sport. Passionate Cricket Fan. Go Pro. Abseiling. Snorkeling. Travel. Golf R. SQL Server Developer. Three sons. One daughter. Last Trip: New York.

Related Posts
3 Comments
    • S.L.
    • On: April 3, 2015

    Sounds like such an easy solution. I’ve been using pivot tables for years, and I never noticed that little check box!!! Thanks!!!

    Reply
  1. Yeah mate, easier than it should be! 🙂 Or something like that. A hidden in plain sight gem that one.

    Reply
    • Philip Hinton
    • On: October 13, 2021

    Good tip, Mike.
    Error values are an indication of something wrong, so it’s often good to check the source data to see why it’s happening. If the error is the result of an otherwise good formula, then IFERROR can hide them at source and the pivot table will show a blank.

    Reply
Leave a comment

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