How to Hide Zero Values in Pivot Table in Excel?
Last Updated :
14 May, 2024
One of Microsoft Excel’s most important features is the pivot table. You might be aware of this if you have worked with it. It provides us with a thorough view and insight into the dataset. You can do a lot with it. The pivot table might include zero values.
In this lesson, you will learn how to hide zero values in the pivot table using relevant examples and visuals. Hide the rows in a pivot table that have zero values. After concealing the zero-value rows, your pivot table will seem neater and the data will be suppressed.

How to Hide Zero Values in Pivot Table in Excel?
Implementation
Let’s take a dataset as an example to understand How to Hide Zero Values in a Pivot Table in Excel. This is the dataset that we are going to use in this scenario.
The Excel table has some zero values, as you can see. First, you must transform an excel table into a pivot table. You may need to hide them at times to have a clear view of the pivot table.
Methods for Hiding Zero-Values
There are three different methods for hiding zero values in an Excel pivot table.
How to Create a Filter Field to Hide Rows with Zero Values
Please follow the procedures below to prevent the zero-value rows from being displayed in the pivot table:
Step 1: Choosing Pivot Table from the Dataset
First, choose the pivot table you built from the dataset. The pivot table fields pane will now appear on the right side.
Step 2: Fill the Filters Field
Now drag the score and medal count field from the pivot tables field to the filters field.
Step 3: Filter Choices will Appear
Following that, two filter choices will appear above the pivot table.
Step 4: Click OK after Selecting Mulitple Items
Then, in the score field, click the drop-down arrow and select the Select Multiple Items boxes, then uncheck the value 0 and then click on OK.
Step 5: Result
As you can see, there are no zero values in the pivot table’s score column. Repeat the process for the Medal count column. Our pivot table will then look like this:
Finally, the pivot table shows that there are no zero values. So we were successful in using the pivot table’s filter field technique.
Step 1: Select the table
First you need to select the entire table then press Ctrl+1.
Step 2: Select Custom Category in Number Section
Now the format cell dialog box appears. Here select on the custom category.
Step 3: Enter in the type field and then Click OK
After that clear the general text from the type field and write ‘0;-0;;@’ in the text field and then click OK.
Step 4: Result
As you can see, there are no zero values in the pivot table.
Finally, the pivot table shows that there are no zero values. So we were successful in using the format cells command.
How to Hide Zero Value Rows in Pivot Tables by Using Filter Function
Step 1: Choose value filters for the desired Element
To begin, choose any element from the Row Labels. Then right-click on your mouse and then, from the menu, choose Filter and then choose Value filters.
Step 2: Select Sum of Score in Value Filter box
Then the Value filter dialog box appears. Now first select the column from the drop-down, here we are selecting the sum of the score.
Step 3: Select “does not equal” and “0” and then Click OK
Now in the second field select does not equal from the drop-down and then in the third field write 0. Click OK.
Step 4: Result
As you can see, the pivot table’s score column has no zero values. Repeat the procedure for the Medal count column. Then our pivot table will look like this:
Finally, the pivot table shows that there are no zero values. So we were successful in using the filter function.
Summary
In conclusion, learning how to hide zero values in Excel pivot tables makes your data look cleaner and more professional. We’ve covered several easy methods, like adjusting settings, using filters, and applying custom formulas. With these tools, you can improve how you present your data in Excel. Practice these techniques to manage and display your data more effectively in your pivot tables.
Similar Reads
How to Hide Zero Values in Excel
Microsoft Excel enables us to format, organize and calculate data in a spreadsheet. It's a great tool for preparing datasets and it makes those datasets easier for users and analysts to analyze the data. Sometimes these datasets contain zero values that are not required to be seen. Excel provides an
6 min read
How to Flatten Data in Excel Pivot Table?
Flattening a pivot table in Excel can make data analysis and extraction much easier. In order to make the format more usable, it's possible to "flatten" the pivot table in Excel. To do this, click anyplace on the turn table to actuate the PivotTable Tools menu. Click Design, then Report Layout, and
7 min read
How to Create Pivot Table in Excel using Java?
A pivot table is needed to quickly analyze data of a table with very little effort (and no formulas) and sometimes not everyone has time to look at the data in the table and see whatâs going on and use it to build good-looking reports for large data sets in an Excel worksheet. Let's discuss a step-b
5 min read
How to Sort a Pivot Table in Excel
Sorting a Pivot Table in Excel is a powerful way to organize and analyze data effectively. Whether you want to sort alphabetically, numerically, or apply a custom sort in Excel, mastering this feature allows you to extract meaningful insights quickly. This guide walks you through various Pivot Table
7 min read
How to Delete a Pivot Table in Excel?
A pivot table is a tool in Excel that allows you to quickly summarize data in the spreadsheet. When it comes to deleting a Pivot Table, there are a few different ways you can do this. The method you choose will depend on how you want to delete the Pivot Table. 1.Delete the Pivot Table and the Result
3 min read
Pivot Tables in Excel - Step by Step Guide
Pivot tables are one of the most powerful features in Excel, allowing you to quickly summarize, analyze, and visualize large sets of data. Whether youâre dealing with sales data, financial reports, or any other type of complex dataset, knowing how to create pivot tables in Excel can make your data a
7 min read
How to Create Pivot Tables in R?
In this article, we will discuss how to create the pivot table in the R Programming Language. The Pivot table is one of Microsoft Excel's most powerful features that let us extract the significance from a large and detailed data set. A Pivot Table often shows some statistical value about the dataset
2 min read
How to Ignore 0(zero) Values in SQL Query?
In SQL, many times we are required to display all the rows of a table in which the entries having 0(zero) as an entry/value are ignored. This is achieved through REPLACE command in SQL. In this article, we will discuss how to ignore 0(zero) and replace them with an empty space in SQL. For this artic
2 min read
How to Create a Power PivotTable in Excel?
When we have to compare the data (such as name/product/items, etc.) between any of the columns in excel then we can easily do with the help of Pivot table and pivot charts. But it fails when it comes to comparing those data which are in two different datasets, at that time Power Pivot comes into rol
4 min read
How to Troubleshoot and Fix Excel Pivot Table Errors?
Power Pivot is an Excel add-in, a data modeling technology that helps the user to create data models, establish relationships, and create calculations. However, it is possible to encounter a few errors during the process. here in this article, we will discuss some of the Excel Pivot Table Errors tha
7 min read