How to not count blank cells in excel pivot table
Count Blanks in Excel Pivot Table
Timecodes:
in this video we'll see how to create a
pivot table
that counts items for you correctly even
if there are blanks
in the data this is deborah dalgleich
from contextures.com
in this table we've got nine records for
product sales
we can see the product name here and the
quantities
one quantity is missing but we would
like to get a count
of orders for each of these products so
i'm going to create a pivot table
i'll select a cell in the table on the
insert tab click pivot table
and then here make sure that it selected
my table
as the range i would like this on a new
worksheet
and i'm not going to put it in the data
model click ok
and here's my empty pivot table now i
want to count
the products so i'll click here and that
automatically puts it in the rows area
and we want to do a count usually when
we want to do a count or a sum
we use a numeric field so we have
quantity
but if i put that in it goes in as a sum
automatically
i can right click summarize values by
count and that gives me a count but it's
only showing
one order for pens and if i go back and
look at my data
there's one here and one here one does
not
have a quantity so it's not counting
that blank
cell if i go back to my pivot table and
revise it
i'm going to take quantity out so i'll
uncheck that and i need to use one of my
fields
where there's data in every row so i
could use id
or product so i'll go back to the pivot
table
i'm going to use product i've already
got it checked because it's in the rows
area i'm going to drag it into values
and now it shows up and because that is
a text field it automatically comes in
as a count
so now i have two records
for pens which is what i wanted
we'll look at another example now this
is a slightly larger
table and i would like to get a count of
the districts and find out how many of
these
are blank i'll create a pivot table
insert pivot table it has the table name
new worksheet and i don't want it in the
data model
now i'll put district in and it goes
into rows automatically
and here are the records where the
district field is blank
if i put district into the values area
the blanks show up as having no records
and again like the last example it's
because
the pivot table can't count the blank
cells
i'll go back to the pivot table and take
district out of the values and i'm going
to look at my data
and here we can see orders that field
has something in every row
so i'm going to use that put it
into the values area it comes in as a
sum
right click summarize values by count
and now we can see that there are seven
blanks
and just to tidy things up instead of
count of
order just say count
and that makes your pivot table a little
easier to understand
thanks for watching this video you can
go to my website
contextures.com to get this workbook and
please subscribe to my contextures
youtube channel
so you can see the latest videos as i
post them
Related queries:
how to count non blank cells in excel pivot table
pivot table count non blank cells
excel pivot table stop counting blank cells
do not count blank in pivot table
