Solved

Show blanks counts as well in pivot table

Posted on 2014-10-18
6
9 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 4

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 4

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction Perhaps more familiar to developers who primarily use VBScript than to developers who tend to work only with Microsoft Office and Visual Basic for Applications (VBA), the Dictionary is a powerful and versatile class, and is useful …
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now