Pivot tables in Microsoft Excel are an excellent way to view, summarize and analyze your data in the most efficient way. They are more like dynamic reports and they provide an interactive point of view to your usual report viewing. With pivot tables, you can break down complex chunks of data into meaningful pieces of reports. Thus pivot tables indeed make your life with Excel much easier, than it is without them.
Sometimes when you create pivot tables though, when there is no data to display for particular cells, blank cells do find their place and it could be a little annoying seeing blank cells in an official report. So is there a way to replace the blank cells in a pivot table with some meaningful value, like replace them with zeroes maybe? Well, of course, there is, and this article is all about that!
Read on, to learn how you can easily replace the blank cells in a pivot table with zeroes, or any other value of your preference. Hope you enjoy reading the article.
Note: You can learn all about pivot tables, how to create them, how to populate them with data etc., from our article on the topic How to Create a Pivot Table in Microsoft Excel.
Solution
Step 1: Firstly, open the Excel file that has the data using which you are going to create your pivot table.
Step 2: Now click anywhere inside your pivot table.
As next, click on the INSERT tab at the top ribbon. And then under the INSERT tab options, click on the button named PivotTable to insert the pivot table into your Excel sheet.
Step 3: When the Create PivotTable window opens up, by default, the radio button corresponding to the option Select a table or range will be selected under the section Choose the data that you want to analyze. If not, please select it.
As next, under the section Choose where you want the PivotTable report to be placed, choose the radio button corresponding to New Worksheet. You also have the option to choose Existing Worksheet. In that case, the pivot table will be created on the same sheet at the location you specify.
Once you are all done, hit the OK button to proceed with inserting the pivot table.
Step 4: A new sheet would now be open, with the structure of the pivot table all ready in front of you.
Step 5: You can generate the data in your pivot table as per your preference. In the example below, I have dragged and dropped the field Item under the COLUMNS section, the field Date under the ROWS section, and the Profit field under the VALUES section.
Note: If you need more information on how a pivot table works and how you can efficiently create a pivot table, please refer to our article How to Create a Pivot Table in Microsoft Excel.
So as per my selections, my pivot table got generated as shown in the screenshot below, making it the perfect summary report as I wanted.
However, if you look at the pivot table, you can see that there are some blank cells present in my pivot table. Now, let’s get them replaced with zeroes in the coming steps.
Step 6: To replace the blank cells with zeroes, right click somewhere on the pivot table first. From the list of options that appear on the right click context menu, click on the one that says PivotTable Options.
Step 7: On the PivotTable Options menu, make sure you are at the Layout & Format tab.
Now, under the section Format, check the checkbox corresponding to the option For empty cells show. Also, enter the value 0 (zero) on the text field associated with it. Please note that you can enter any value in this text field. All the blank cells in your pivot cell will be replaced with the value that you enter in this text field.
Hit the OK button once you are ready to proceed.
Step 8: That’s it. Your pivot table is now all cool, having replaced all the blank values with zeroes. Enjoy!
Please drop us a comment if you are stuck at any of the steps. Also, please do share the article with your friends if you found it helpful.
Stay tuned for more amazing articles!