Solved

Show blanks counts as well in pivot table

Posted on 2014-10-18
6
17 Views
Last Modified: 2016-05-25
I need to show count of blanks for each category in pivot table. i am able to get the counts for each category,but the empty records should be show as blank and then the counts of blank values. I have attached excel file for your reference. The output is right there, but In addition to counts of non blanks, I need counts of blank as well.
0
Comment
Question by:techEverest
  • 2
  • 2
6 Comments
 
LVL 5

Expert Comment

by:C. Blaise Mitsutama
ID: 40389565
techEverest,

I don't see the attached Excel file.
0
 

Author Comment

by:techEverest
ID: 40389612
Here is the file.
pivot-table-example.xlsx
0
 
LVL 5

Accepted Solution

by:
C. Blaise Mitsutama earned 500 total points
ID: 40389654
Pivot tables don't count blanks. In order to count the blanks, you need to use a field that has data in every row. In the attached revised Excel file,

- I put the Category field as the first item in the pivot table row to group the data by Category.
- I put the Data field as the second item in the pivot table row to show the individual Data items.
- I used a count of the Category field in  the Values area to count the items in both Category 1 and 2, including blanks.

Let me know if you have any questions.
pivot-table-example-rev1.xlsx
0
 

Author Comment

by:techEverest
ID: 40391204
Thanks for your help.Actually
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will show you how to use shortcut menus in the Access run-time environment.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question