in this video we're going to build a pivot table with bars and kpi indicators so notice that these
two columns are a part of our pivot table they're not separate formulas and all of this is based on
our data here right in this tab and also we have our goal for each salesperson here right now it's
set to hundred and fifty thousand dollars as you can see and here we can see which people reached
that goal right here now i could go back and change that amount so if i change that to 900 000
and go back and look at our pivot table we'll see that
it will update and you can see the only person that reached this goal
is this person right here and then we can also see this progress bar that shows
how far the rest of our sales people are from reaching the actual goal so let's get right to it
so i'm going to get started with my data as you can see we have some sales amounts here in
this column g and we have some sales reps here that's pretty much all the data i'm gonna use
for my pivot table so what i'll do i'll just create a pivot table from this
new worksheet is fine and i'm gonna add sales in our values
and i'll do sales rep in rows so so far i got this so what i'm gonna do
for now i'm gonna build some formulas next to my pivot table so let's start with a sparkline
so here if i open the help see there's this nice option list for chart type bar and max 50.
that's exactly what i'm going to need in a second so i'm going to copy that for data i'm going to
click on this amount comma then i'm gonna paste those options that i just copied from here close
parentheses hit enter so right now it makes a bar and what it does as a size for this bar it's going
to use this 50 max now for that 50 i'm going to use our goal so let's say that would be 850 000.
i'm going to hit enter and now if i take this and drag it down
you'll see the ones that reached that number they will basically just have the whole bar full
and the other ones that didn't it will basically just show how much of it is done so the problem
with this formula is going to be that we want this formula to also work here so i don't want this
in this line so i need to do some sort of handling to make sure that when we're in this grand total
line we don't do this graph so the way we're going to handle that we're going to go up here
and we're going to add an if statement so i'm going to go ahead and cut this formula
without the equal sign so i can paste it in just a second i'm going to start with an if statement
and what i'm going to do i'm going to check if this amount equals our grand total and the way
i'm going to get that grand total is by simply doing a sum from our source data sales amounts
so i'll go sum go to my source data select this whole column g that's what i'm going to sum close
parentheses so i'm saying if that amount equals to the total comma then i want to leave it blank
so i'm going to do double quotes comma otherwise in all the other cases i want to actually do that
sparkline so i'm going to paste that sparkline formula here close parenthesis again and hit
enter and it should look the same only now if i drag this formula like this see this cell remains
empty so one last modification i want to do to this i want to make sure that i don't hard code
my number here so instead what i'll do i'll create another tab and i'm going to call this inputs
and here i'll just do our goal 850 000. maybe we'll just format this too and now i'll go back
to my formula here and instead of typing this 850 000 here we'll replace it with a
reference to that cell in this inputs tab so i'm going to go to inputs and click on this 850 000
right there i'm going to hit enter if i want this formula to work when i drag this down i want to
make sure this reference is absolute so it doesn't move so i'm going to select that and press f4
to add those dollar signs hit enter and now if i drag this down we should have
a working formula that's basically based on this amount so one thing we might want to change about
this is the color of this so maybe you don't like this orange color so what we can do here we can
go to this options list inside of these brackets i'm going to add another semicolon and provide
color 1 comma and then give it a value after the comma like for example red see that would color
code this in red now if you want a particular color you can just go here and pick a color you
can pick one of these colors or maybe you have some custom color in mind you can go under this
custom and basically just pick whatever color you want once you got the color just grab this
hex number copy that and we're gonna go back and replace this red with that particular
hex color like that and now if i drag this down that should use that color let's get rid of this
so this is now our sparkline formula so i'm just going to copy that formula the whole
thing now the only thing we want to remember in this formula is right now we refer to this
b2 cell which is the cell to the left so i'm going to clear this and i'm going to go back to my pivot
table now i'm going to add another value field and this time we're going to create a calculated field
and in this calculated field we're going to choose custom and we're going to paste
our formula we just did now if you remember we got this b2s here b2 is used to refer to the
cell what we need to do we need to replace those b2s with the actual calculation here and this
total amount it's the sum of this column right here in our data it's called sales
so i'm going to go here instead of b2 i'm going to do sum open close parentheses and in parenthesis
i'm gonna do sales now if your field has spaces you need to put that in single quotations like
this mine doesn't so i'm just gonna do sales now the same thing we need to do the sum
sales i'm going to copy that with our second b2 that's over here so i'm going to select that b2
paste that sum sales hit enter let's try to zoom in a little bit and now we should have
this formula that gets us what we need we don't have here anything because it checks against
this total and they match so it keeps it blank like it should and in all the other cases we
do this sparkline formula right here so that's the same formula except that instead of that b2
which was just a reference i replace it with a sum of sales which is this field right there now
we need another one that's basically gonna give us a checkbox if that person reached the goal
so for that we're going to do another if formula so we're going to need actually a couple of if
formulas on this because for one of them we still want to check if it's grand total we don't want to
put anything in here so for that reason i'm going to use ifs function here and i'm going to say
if this equals again sum of our sales amounts from our original data from here g column close
parenthesis comma then i want to leave this blank so double quotes comma then we need our
second condition so for my second condition i want to check let's go to that pivot table i
want to check if this amount is greater than or equal to our goal so i'm going to check if this
is greater than or equal the goal we have our goal is located on our input tab so i'm going to go to
input click on this amount and again i'm going to press f4 to make this absolute reference so
if that has reached that goal comma then for now i'm just going to put an x here in quotes comma
and finally in all the other cases i'm just going to leave this blank so i'm going to say true which
is all the other cases comma then you could put a different thing here right you could say like no
or something so if i do this close parenthesis and hit enter so i can show you what happens
see we got an x here and if i drag this formula down until here basically we have our axis
in all these cases and knows for the rest now i don't want this nose so i'm gonna just do
that replace that no with nothing and now if i drag this down it's basically just gonna have
axes now finally i don't want an axe i want something that will give me like a check so for
that what i'll do i'll go to google docs an empty document and i'm going to go under insert and do
special characters and in this special characters list we're gonna go find something for this so
i'll just do check or something let's see so see we can use this this this any of these is fine
i'm gonna use this particular checkbox on this but again you could grab anything you like if i click
on that see that puts that box here i'm gonna copy that character ctrl c command c go back here
and i'm going to go back to my formula and instead of this x i'm just going to paste that character
and if i hit enter and drag it down we'll see how this works basically we got our character here
so again now that we got this formula we're gonna basically place this inside of our pivot table
so i'm gonna go ahead and copy that formula erase all of this click on this pivot table and we're
going to basically add another value field i'm going to click add and do another calculated field
and again we're going to use custom i'm going to again paste that formula here and one more
time all of these b2s should be now referring to this amount right here which is gonna be again sum
of that field which in my case the field is sales so that's what i'm gonna do now i have
two of these so i'm going to copy the sum of sales control c command c go right until i find
this other b2 and i'm going to paste that one too so i'm going to overwrite both of these hit enter
now i should have that built in to my pivot table so at this point i can rename these column names
simply just type right over the column
and then for these i'm just going to select this column and we'll just center
like that maybe we'll make this column smaller and select this headers go on top here and use
wrap text maybe a little bigger for this column name if i want to redo the way my pivot table
looks overall i could go to format and do this first option here and choose one of these maybe
i'm gonna leave this up to you you can customize it make it look nicer if you
wanted to now for me what i'm going to do i'm just going to make this headers a little smaller
and align them vertically and horizontally
and finally if you want you can also add a little bit of color coding to
these cells too i could select this column here go to format and do conditional formatting
and for these i'm gonna go here and choose
text is exactly and as a value i'm basically just going to go back to that same
character and copy it and come back and paste it here
so now i can choose some format for these
maybe make it bold press done
and there we go now we have this entire thing built into our pivot table
and that should do it thanks for watching please subscribe and i'll see in the next video