• Around The HomeToggle Menu

    • Entertainment
    • Productivity
    • Smart Home
  • FamilyToggle Menu

    • Parenting
    • Toys
    • Pets
    • Travel
  • ReviewsToggle Menu

    • Phones
    • Tablets
    • Laptops
    • Desktops
    • Wearables
    • Audio
    • Cameras
    • Headphones
    • Printers
    • Smart Home
    • TVs
    • Gaming and Video
  • One Cool ThingToggle Menu

    • Frugal Tech
    • Kickstarters
    • Videos
  1. Home
  2. Around The Home
  3. Productivity
  4. How to Remove Blanks From a Pivot Table in Excel

How to Remove Blanks From a Pivot Table in Excel

By: Zicheng Ren
  • Share
  • Share on Facebook

Pivot tables are used in Microsoft Excel as tools for data analysis, summation and exploration. Using a pivot table allows you to sort though a large amount of data so that you can obtain summations and averages of the data that are analyzed, for example. In order to not leave blanks in the pivot table, you can remove and replace the blank entries with other data by setting up the values of empty cells in the "PivotTable Options...."

...
All the blanks are replaced with the content you set up.
credit: Courtesy of Microsoft
...
Open the pivot table report worksheet in Excel.
credit: Courtesy of Microsoft

Open your XLS file and locate the pivot table report worksheet which you have derived from your source data.

...
Choose "PivotTable Options...."
credit: Courtesy of Microsoft

Right-click one of the entries in the pivot table and choose "PivotTable Options..." from among the drop-down list. (see reference 2)

Video of the Day

...
Check the "For empty cells show:." check box.
credit: Courtesy of Microsoft

Check the "For empty cells show:" check-box in the Format section of the Layout & Format tab.

...
Type your content in the text field and click "OK."
credit: Courtesy of Microsoft

Type the content that you want to to appear in blank cells in the "For empty cells show:" text field and click "OK" to apply to the changes. For example, type "N/A" without the quotation marks to replace all the blank cells in the table with "N/A" without the quotation marks.

...
Check the result.
credit: Courtesy of Microsoft

Check the entries in the pivot table. All the blanks have been removed and replaced with the new content.

Show Comments

Related Articles

How to Automatically Exclude Blanks in a Pivot Table in Excel

How to Automatically Exclude Blanks in a Pivot Table in Excel

Around The Home
Productivity
By: Tamara Runzel
How to Split a Column of Data in MS Excel

How to Split a Column of Data in MS Excel

Around The Home
Productivity
By: Contributing Writer
How to Format Numbers in a Word Table

How to Format Numbers in a Word Table

Around The Home
Productivity
By: Tricia Goss
How Do I Sort Microsoft Access Records Using Multiple Criteria?

How Do I Sort Microsoft Access Records Using Multiple Criteria?

Around The Home
Productivity
By: Steve McDonnell
How to Remove Leading Zeroes in Excel

How to Remove Leading Zeroes in Excel

Around The Home
Productivity
By: DanH
How to Make a 2 Variable Graph in Excel

How to Make a 2 Variable Graph in Excel

Around The Home
Productivity
By: Emily Ediger
  • HOW WE SCORE
  • ABOUT US
  • CONTACT US
  • TERMS
  • PRIVACY POLICY
  • COPYRIGHT POLICY
  • Advertise

An error occurred. Try again later.

Thanks for signing up!
© 2019 Leaf Group Ltd. Leaf Group Media

Get great tech advice delivered to your inbox.

Keep your family productive, connected, entertained, and safe.

Please enter a valid email.