How to not summarize values in pivot table
Two Reasons Your PivotTable Counts Instead of Sums Values
Timecodes:
hello and welcome to this video tutorial
from computerguard.com
in this video we are going to look at
why your pivot table
is counting your values instead of
summing them
now your pivot table will always default
to summon
numeric values in your pivot table the
only reason to
count them is when it believes they are
text
and we're going to look at the two key
reasons why it may
think that now first of all
what i think is the the main reason
are blank cells and i've got two
examples of what i mean here
this first example i've got but for us
is quite a clear blank cell
in the totals value common bear in mind
that your table may be very large
and that blank cell could be anywhere
and let me just demonstrate first of all
i'll click anywhere in this table i'll
insert a pivot table
i'm going to try and keep this bit brief
because i want to focus on the reasons
why
and as i build up this pivot table
quickly country and total sales value
we notice it immediately comes into the
rows area
and if i was to drag it into values it
is counting
those values and that is because of that
blank cell
so total sales value that blank cell was
in there then it thinks it's text and
it's chosen to count them rather than
sum them
now if i change the calculation to a sum
it will work just fine
it's just a default behavior that is
different
and that's why it's defaulting to that
now let me show you another example
another very typical thing
is that people will highlight the entire
columns along the top
so if i highlight maybe a across to k
there see entire columns
now that will mean that i've grabbed all
these blank cells as well
i select all the columns i've grabbed
all these two which is all
you know there are some advantages maybe
to doing that but it's all a little bit
unnecessary
and this will mean that when i create a
pivot table
and i'll look at building this up and
let me choose a different column this
time to show it's nothing to do
with that blank that i've been put in
the range let me choose unit price
notice it treats it as text it throws
into rows
i chuck it in values still reacting as
text
and i would have to change the
calculation myself each time which can
be done
you know but we're looking at why the
default reaction is to count them
now the other reason this may be
happening so i think blank cells
is definitely the most common scenario
it's probably one of those two i just
mentioned which is the reason behind
yours the other one
is that somebody has accidentally
actually typed text
so what i've done here in the cell the
same cell actually
i've pretended to type 950 pounds
but i've typed an o and not a zero
and you know they're right next to each
other on the keyboard this is a very
easy thing to do
i see people do it in my classes at
times
and you know it's quite recognizable on
the spreadsheet to be fair
but you know these things slip by
they're honest mistakes
and that will have its effect so if i go
to create my pivot table
again here i'm going to choose the same
stuff you know let's put country in
it's obviously a text field total sales
value
and it's reacting as text so those are
the main reasons it's going to be
a mistype in some way and a text
character
has creeps in there or it is a blank
cell
and i i personally believe the most
common situation of all
is people highlighting the entire
columns here very common thing to people
to do when
when you're writing formulas and you can
get away with that to a degree
pivot tables will not appreciate it as
much and and
same goes for some other excel features
i hope you found this video tutorial
useful please check out some of our
other videos on our youtube channel
and check us out computerguard.com
Related queries:
how to not sum values in pivot table
do not summarize values in pivot table
how to change summarize values in pivot table
pivot table summarize values by
pivot table keeps summing values
