Transcripts
1. Pivot tables intro: Knowing how to work
with pivot tables and pivot charts in Excel
is a game changer. These tools let you
analyze data more quickly, more easily, and build interactive dashboards
in just a few minutes. Hi everyone. I'm biased. I'm a trainer and consultant
for XL Power BI and Tableau. I run my own company,
data training. And I'm also a YouTuber. I've built this complete on an actual training to
help you master axon in the quickest way without wasting time learning things that
you want use in practice. This training is part four of the axle bootcamp where you will learn how to get the most out of pivot tables and pivot charts.
2. Why pivot tables?: If you would ask me,
what is the tool that gives you the most
value and axial versus how long
it takes to learn that I probably would
choose Pivot Tables. Now, in this section we're going to cover everything
you need to know about pivot tables and
pivot charts and how you can use it to build
effective reports. Now the files for this section, you'll find a folder 05
quicker with pivot tables. So let's go there. And here we have one file
for each main topic. We're going to first
answer the question, why do we need PivotTables
in the first place? And what are the
main advantages? Then, once that is covered, we need to make sure that
the data that we use as a source for the pivot table isn't the right structure. And then we can explore all of the main functionalities of pivot tables from how to
structure them to filtering, sorting, grouping, and doing calculations
within pivot table. And at the very end, we
will see how we can use pivot tables and pivot charts to create effective dashboards. Now, let's get started
with the first section. Why do we need PivotTables
in the first place? So I'm going to open the
very first actual workbook. And the main example
datasets that we're going to use is on
the very first sheet, 01 data DV mark. But in this dataset, we
have information about how much we spend
on TV advertising. Now, you see we have the date, the month dying off the
TV spot that we bought. And what the weekday was, what the channel was the
position in the break, how long the break was to
advertisers and break, and the price that we
paid for the TV spot. Now let's say we want to create a small summary table
on the next sheet. So I'm gonna go here to 0 to summary report with
our pivot table. And just imagine your colleague comes and asks you to create a small summary table that shows the average price
that we paid for it, TV spot for each month. The average spotlight, and the average increase of visits that we got from ITV Sport. And without using pivot tables, we could do the following. We could go back to
the dataset and create a table first so that our formulas are a little
bit easier to write. So I'm going to select one
cell inside the dataset, go to Insert and choose table. Now if you'd like
shortcut Control D, Now our dataset
does have headers, so let's leave that checkbox selected. Let's click on Okay. Now, all the way at the top
there you see Table Design. And if we go all the way
to the left-hand side, we can give our table a name. Let's call this one data. Alright, then we go back to Z12, where we want to create
that summary overview. Let's then go to cell
C6 where we want to find the average extra
visits that we got for us. But for that month, which we can do within
average IF function, because we don't want to have the average for
the whole dataset, but only when we have
the month January. So I'm going to start with
the equal sign type in Av, the average if
function Bobs up there at the bottom and select
it by pressing W. Now the first argument is the colon over which we want
to calculate the average, which is the incremental visits. To refer to that column, we can either go to the sheet
and select it manually. Or if you work with tables, it's probably easier to just
write the name of the table, data, square bracket open. And then we can choose the column of which we
wanted to take the average, which is in this case
the incremental visits. Click on it or press
Tab to select it. Then you can close the square bracket and then a comma to go to
the next argument, not the criteria range is
done also in the same table, data, square bracket open. And the criteria range is
going to be the month gotten. Now, we only want to
take the average for those rows where the theoria is. John. Alright? Now I can just type in Jan
in-between quotation marks. Then close the brackets for average as
function press Enter. And we have here 2634. So this is the average
extra visits that I got activist bought
for the month January. Alright, so that took
quite a little bit of time to write the
function, but it works. But now I need to do it also for the price and
for the spotlight. And then for all of the months, Let's make this a
little bit easier. We could go over here
and not hardcore Jan, but just refer to the
cell that contains job. Alright. Then I take that cell and just drag it down until
we reach December. And over here, I do not
want to add the formatting, so fill without formatting. So now we have the
incremental visits column and we need to repeat it for the price and the
spotlight of color. So I'm gonna go back here to C6, just going to copy it
over here to D6 and see now it doesn't work
anymore because my reference to Jan changed. It also moved to the right. Now, I could have
fixed that by using dollar signs or simply I can now drag this
one to the left. Then we need to also change the column over which we want
to calculate the average. So instead of
incremental visits, we want to have the price. So in this case we're
gonna go up price. Press Enter. Now we have the
average price that we paid for the V spot in
the month of January. Then I can drag it down again, all the way down and then we can repeat it once more
for their spot length. So I'm gonna go here to
D6, take the formula, then go to E6, base it inside of
the formula bar, and that update the price. We have the spotlight. Alright, so over here, press enter and drag it down
to the month, December. There you go. We have
our summary table and then you give it
to your colleague. And your colleague
says, I made a mistake. I actually don't want to
have a breakdown by month, but a breakdown by
the TV channel. So again, you have to go back to Excel workbook and then we get all of these
AVERAGEIFS functions. But now based on that new
bacteria colon TV channel, which is of course going to take another five
to ten minutes. No, this is not very flexible way of creating
this summary overview. Now with a pivot table, this would be much, much easier. Now
let me show you. Let's go back to the first sheet where
we have the dataset. Now, it is already a
table with the name data. I select just one cell inside
of the table and then go here to insert all
the way on the left, you find the pivot
table functionality. So I'm going to
click on that icon. I see we have there as a
source for this pivot table, the data table that you
can choose where to place it if you
want to have it on a new worksheet or
an existing one. But now let's go and place
it on a new worksheet. Then click Okay.
And there you go. We have an new sheet with
a new pivot table that we can now structure using the money that we have here
on the right-hand side. That's the fields back. Now here we want to analyze first of all, the
incremental visits. So take that field and I just drag and drop
it onto values. And then I can do the same for the price and the spotlight. Then we want to have a breakdown
by the different months. I'm going to look here for the field and drag it onto rows. Now, here at the moment, we have the sum for each month and I want
to have the averages. So right-click on
that first column for the incremental visits. Summarize values by, I'm
going to choose the average. And the same thing I
do on the next column, where we have the
prices that we bet. And here we have the
average Spotlight. Okay? Now let's take these fields, let's update the formatting. And then we have our
summary overview. That was much quicker, but also much easier
and much more flexible. And on top of that, there's also a last chance that
you make an error. So what if we now give
this to a colleague? And the colleague
says, You know what, I actually need that
break down by TV channel. Well, no problem. You just select the
Pivot Table and then replace month with the journal. And there you go. We have a new summary overview where we show the average of incremental visits
prices spotlight now by channel
instead of Monday. So you see how flexible
pivot tables are. What if we need breakdown
by dv channel and by month? No problem, then we can just bring back the month as well. But it also on the roads. Now you see with PivotTables, much quicker and much more flex. Now let's see what shape
the data needs to be in to actually be able to use pivot tables in
the first place.
3. Preparing the data: Before we dive into the
topic of how to structure a PivotTable and go over all of the functionalities is
important that you know, what structure the
data source should be in to be able to use
pivot tables properly. And that is where often a
lot of people go wrong. They have seen how
useful pivot tables are and what the advantages are. But as soon as they
start using it, they run into troubles
that this happens often because the dataset is
in the wrong structure. Now the example for this part, you find in the next workbook
0 to preparing data. Now here we have a
small dataset where we see the different
channels at the top. And we have here the different months on
the left-hand side. Now let's say we want to use that dataset for
PivotTable, not done. We could select the data, go to Insert PivotTable
and click on Okay, but then we get the
following error. The pivot table field
name is not valid. To create a PivotTable report, you must use data that's organized as a list
with labeled columns. And if you are changing the
name of a PivotTable Field, you must dive new
name for the field. Now what's going on over here is that the data
that we want to use? Well, it's not structured
in the right way. Now one of the problems
here is we have a completely empty column
in-between the journals. Now, this is not allowed if
we would delete that column. So let me delete
it and try again. Insert PivotTable. Click. Okay. You see, now it seems as
if there's no problem. And that is dangerous because we do have a PivotTable and we can start draining the fields to values, rows and columns. However, you will
run into trouble. Now let me show you here
on the right-hand side, you see we have one
field for each gallon. And let's say that
we want to analyze the average prices that
we paid for Channel four. Then I could take general form. Let's put general
for on the rows. What do we have here? Well, this is just a list of
all of the unique values in the column channel
for you see it just corresponds to all of the unique values that
we got over here. Okay, so not very helpful. What if we take channel for
it and put it on values? Then you see we have
over here the sum of these values inside of
the channel for gotten. This gets us already
a little bit closer because we could say, instead of the sum,
right-click Summarize value, I could go for the
average, okay? We have now the average
for the channel. And then we could do the
same for the next one for discovery and for the
next one for flux, then change all of these summary aggregations,
the average. But just imagine we
would have hundreds of different channels and we'd
have to do it a 100 times. And therefore there
would be a new channel, would have to go back
to the PivotTable, drag it onto values, change it to Average
every single time, then we're not really better
off than using functions. Okay? So what does Then wrong with the
underlying dataset? Let's have another look. Now, inside of this dataset,
we have inflammation, but the month, we have information about the
different channels. And we have information
about the prices that we picked, three fields. And the number of
columns should always correspond to the number
of fields that you have. So in this case, we should
have actually three gods. How can we turn this
into three columns? And why do we have it like
this in the first place? Well, this is a
common data structure that people use in
XL file, right? So year to have one separate column for each
channel is easy to read. So we should have
a month column, gentle column, the price column. However, here, for
the field journal, for each item inside
of the fields, we have a separate column, and inside of it Gonen we
have the prices that we bet. So what we need to do is don't
these columns into rows. But this is relatively
a small dataset, which I can show you how
this transformation works. I'm going to go to
a new sheet where I copy this dataset to copy it, answered a new
worksheet and paste it over here right next to it. I'm going to create the
correct data structure. So we need three columns
because we have three fields. The first column is going
to be my month column. Then we're going to
have one column for the channel and one
column for the price. Now here I'm going to copy over all of the months
that women of color. So I'm just going to
copy over values. And then we're going to start with the very first general, let's call it general form. Going to copy it down and
holding the Control key. You see, then it just chooses copy cells
instead of fill series. Okay? Then I want to
have the prices that we bid for Channel four
over all of the month. So I'm going to
copy that over to the third column where
we have the price. Now, I repeat this process
for the next general. So I'm going to copy
over all of the months. Then the next channel which we want to do
it is this discovery. And then we can copy
over all of the values. I'm going to copy that
over here as values. And then also here discovery. We want to drag down until
the month of December. Okay, So just like this, we repeat it for all of the
remaining channels as well. Alright, so here I also did it for all of the
remaining columns. So all of the
remaining channels, I took the columns and
turn them into rows. This process is called pivoting. Now, once you have it in
the correct structure, then we can create
a pivot table just by selecting one cell
inside this dataset. Go to Insert pivot table. Now I want to have it on
their existing worksheet. Now we have to
choose the location. I select over here, that arrow up on the
right-hand side. And then select where
I want to have it. So I'm just going to
get it right next to the dataset that
I just created. Press Enter, press Enter again. And there we have a
empty PivotTable. On the right-hand
side. You see in the fields panel it looks
quite a bit different. We have now one
field, each column. We have the month,
gentle, and price. Now, if we start
structuring the PivotTable, I usually start with
the value field. So we want to analyse the bright so I can take the brightness
but unknown values. Now I want to have a breakdown
by the different channels. So take the channel, but the journal on rows. And what if I wanted to
have the average price than I just right-click
on the values, summarize values by
and take the average. And then we can also update the formatting to a number
formatting, and that's it. So this is how the pivot
table should work. Now, you see this is quite a bit different from
what we had before, where it was draining all of the channels one
by one on values. And if there would
be a new journal, then I'm going to
have to go back to the PivotTable and manually update or with this structure that would not be necessary. We could just take
the channel put in a rows or columns to
create the breakdown. And it doesn't matter
if in the future there are a 100 channels
or 1000 channels, it would automatically
update when the PivotTable
would be refreshed. But probably at this point
you might think, okay, but you have just 123456
different channels. And this transformation, well, took about maybe five minutes, but just imagine you have one separate column
for each amply. You have one separate column for each month over multiple years. And you need to do the same
transformation as I just did. Well, that would take
you easily a few hours. Now, is there an
easier way to do this? And yes, of course, deaths. And that brings us to a new topic which we
didn't cover just yet, which is Power Query. Now, let me give you a little bit of a quick
introduction so that you can already use it for this particular
transformation. Here we're going to select the table or one cell
inside of the table. Then you go to the Data tab. And here what you find on
the get and transform data, this is where Power
Query starts. Now, here we want to go to
Power Query from this table. So we use that as a source. We connect on from
table range and it gives that data range
is not a table yet, then it will ask you, do you want to turn
it into a table? Click on Okay,
because it has to be. And then a new window pops up, and this is the query window. Now, important to note is
that when this window's open, then you cannot do any transformations in
your normal workbook. Okay? So the normal workbook is blocked until you
close Power Query. Let me maximize
this window again. Now let me give you
a quick overview of the main sections that we
have here in Power Query. In the middle, you see
the data or if you have a larger dataset that
goes over a thousand rolls, you see a preview of the data. Now, on the left-hand side, you see the query. Now here we have one query
which has got devo free. So just the name of
the table that we used as a source for this query. And then where it
gets interesting is on the right-hand side, because there you see all
of the transformations that this query that forms through
the original dataset. Now here, it's kind of easy because we have
only two steps. That one, Let's click on it. Click on Source tab. Here. It connects to the table. Then gave datatypes. Here it looks at each column and checks what kind of data
is inside of that column. Is a text, is it a date, or do we have whole
numbers or decimal values? But in this case, you see
that for the first column, the datatype ABC, that's text. And for all of the other ones, we have decimal values, the 1.2. Okay? Now this happens automatically. But then we can add
more transformations to the screaming yourselves by using those buttons that
you find there at the top. Now for our example, we want to take dose gentle columns and
turn them into rows, just like I was doing
manually before. Now, we can select
these columns, can afford up this guy
holding the Shift key. Then we go to transform
in the Top Ramen. And then here we find
and pivot columns. That's the name of
this transformation. Click on it. And that's it. We have three columns
corresponding to the three fields, the month, the channel,
and the price. Here. We can then also update
the names of the columns. So first one is the month, then I double-click on the
next one, that's the channel. And then the last one,
that's the price. Now, every time when
we make a change, then on the right-hand side you see we have extra
step that gets, gets added to the query. So here we have
an pivot columns. Now let's click on
it and you see how the data set looks like at
that point in the query. And if I go one step up, then you see the original
dataset where we started off. The last step, rename columns. That is where we assign
new names for each gotten. They see that Power Query
is a very helpful tool that lets you clean up data in
a very quick and easy way. And the big benefit is
that when new data comes, we can just rerun the query. And it's automatically
then applies all of these steps
against the new data. So it is a perfect
tool for automation. Alright, but now we have the
dataset as it should be. But how can I now load
the data to a worksheet? Because now the
moment we are just looking at a preview of the
data inside of Power Query. That's a load that
data to ishit. We go here to the Home tab
and then Close and Load, Close and Load to. Click on Close and Load to. I can choose where I
want to know the two. I want to have it as a table. Where do I want
to have it? Here? We can choose maybe an
existing worksheet to come to place it right next to
the PivotTable from before. And then here at the bottom, add this data to the data model. That is PowerPivot does a
topic for another time. Now I'm going to de-select
that box and press Okay. And that green table
that we have over here, that's the output
of a query that we can then use for pivot table. And you see that was actually much easier and much
less manual work than having to do this
transformation on your own. Plus, if in the future
we would get new data, only thing that we would
have to do is go into data and then refresh. And then as we apply all
of these cleaning steps. Now before we end this section, let's quickly go back to 01, wrong data structures
here in columns J to L, Then we have awesome dataset. But here the structure
is also not correct. Even though we have
only three columns. You cannot have empty
rows in-between here, the different months, okay, So delete and three rows. If the cell is up and after you deleted
all of them zeros, you also want to get
rid of any merged cell. So here in this column where
we have the month there, I would emerge the cells
that we've got there. Okay. And then instead of having
these empty cells right below, and we'll just drag it down, holding the Control key and
copying the month names down. And that would then be the
right structure that we could use for a pivot table after you did this for
all of the months. Then you can also
fix the headers. Of course, the need of yet They wrote it was still
there and fix the header. So here we have four. Let me change the
font color to white. And here we have
the Month column. So this would then be the correct structure
that we could use. A pivot table.
4. Pivot tables 101: Now that you know why we need PivotTables in the
first place and how the data source should
be structured for the pivot table to work
as it's supposed to work. Now it's time to actually build some pivot tables and go over some of the main
functionalities. Now, follow me to
the next workbook, which is Pivot
Table Fundamentals. So workbook number three. Now here we're going to
use the same dataset as before about Divi, marketing the soda spots that we bought on television
for different channels. Now before I create
a PivotTable, I always format the underlying
data source as a table. Now why? Because later on when we get new data and refresh
the pivot table, then new roles and new columns
automatically are part of the table and gets reflected
in the pivot table. So this is very important. So let's take one of the
cells inside of this dataset, go to Insert and choose stable, but the shortcut is Control T. My table has headers.
Yes, so click Okay, and now we see Table Design. Then we can go all the way
on the left-hand side. And let's call this one data, then the V marketing spend. Okay, Now that's the
name of the table. Let's now create than the pivot. We go here to Insert. And then all the way
on the left-hand side, we can either press the PivotTable button
or there's a drop-down. Here, we have four
different options. It can be that you don't
see all four of them. That probably means you're
an older version of Excel. But as soon as you would upgrade to one of the newer ones, then you have four
different options. Now here the very first
option is the one that we need from table or range, because we want to use
this table for table. However, what are
the other three we have from
external data source that one lets you connect to the data that's
outside of the workbook. Then we have from data model, that is when you use PowerPivot. Now here we first have to go to PivotTables before we can
talk about Power Pivot. However, Dad add some
extra functionality where you can build what's
called a data model, which can consist
from data coming from different
sources that you then can use inside of your pivot table
and you can work with much larger datasets. So that's interesting, but still a little bit too early
to talk about that. Then we have from Power BI, which uses a similar technology. Also there you can
build a data model. However, it's a separate
software outside of axon. And they're, the
visualizations are very nice and add some extra functionality
is when you want to, for example, share
your workbooks, okay? Now here for us, at this point, we want to use from
table or range, or just press the button itself. And then we get the next one. Now here, we want to connect to the table data to
V markings brands. That's good. And then we can choose where we want to
put the pivot table. Now, I want to have it
on a new worksheet. We'll leave it like this. And then here at the bottom, and this data to the data model. Well, that has to do with bowel. Vivid. Interesting, because then
we can use multiple tables, but for now, it's
still a bit too early. Now let's click on, Okay, There's insert new sheets. And then here we can
structure the PivotTable. Not we did this
before very quickly. Now, let's go a little bit
more slowly, step-by-step. Now, here on the
right-hand side, this is called the fields panel, and we have two main sections. The fields that we have here at the top
that we can choose from. Then at the bottom we
have four drop zones. The first one is fulfilled us, if we want to filter the
values on the pivot table, then we have columns
and the rows. Columns is to create a
horizontal breakdown, roses to create a
vertical breakdown. And lastly, the values, the values that we want to show inside of the pivot table. What we want to analyze. That is usually where I start. What do we want to
analyse now in this case, the prices that we
paid for the TV spot. So here at the top,
I look for price. If you cannot find it,
there's also a search box where you can just type
in price and bobs up. And once you found
it, and you can also click on the X, okay? Now the price we want
to put on values, so we can just click on the
checkbox goes to values. Why does it go
automatically to values? Because it only sees values
in the price column. Then in the pivot table, you see we have there
the sum of price. Now let me just format it
a little bit differently. The number, and you
see we have 7,091,736, which is the sum of
all of those values. Now, just to show you, we can take the sum of what of the data to V marketing
spend, square bracket open. We want to have
the price column, right bracket, close, bracket, close for some function, but under same value. Okay? Now let's delete that again. That was just to prove the
point that is the sum. And now we want to
create a breakdown. Where did the field panel goal? We have to first click on the pivot table to see the
PivotTables field panel. Then we want to have, let's say, breakdown for
the different channels. So I take the channel field. Now, I don't put it in values, but put it here on
rows or columns. If you put it on columns, you
get a horizontal breakdown. If you drag it onto the rows, you get a vertical breakdown. So what does that
very first value that we have in the pivot table? 2,030,315. That is the sum of the
prices that we paid for all of the TV spots that were
aired on channel for. Now, how could we
get to that value using a function?
Now let me show you. We could go here to
C4 right next to it. And it could type in sum
F are some Fs function. Now what is my sum range? Well, we have the data to
the marketing spend bracket open and we want to
have some the prices. So the price column, then the criteria range
is on their channel. So I can go to my table, data TV marketing expense, square bracket open and we have over the edit
channels, okay? Then the criteria
Materials channel for close brackets for some F's. And there we go. We have 2,030,315, same value. Now you see it's quiet. A lot of effort to
write that function. It's much easier to do
it with pivot table. So if you can go
for pivot tables, pivot tables, it's much easier, quicker, less error prone, and much more flexible. Okay, so let me delete that formula and go
back to the PivotTable. Remove that yellow
color, the background. Now let's add another breakdown. Let's say we also want to have a breakdown by the weekday. Then we can take
the weekday field and drag it onto columns. Now we also have a
horizontal breakdown. So the value that you see in the top left corner,
the 276,736. That is the sum of the prices
that we paid for all of the TV spots that were on
Channel four on a Monday. So how could we get to
that value as well? Again, with a sum f function, but then with two
criteria range, one criteria on the journal and then another
criterion, the Weekday. Okay, now it's much easier to use the pivot table instead. Now, here we have a
lot of flexibility. We could also put the
weekday onto rows than me. You see we have first the
breakdown by the channel, then a breakdown by the Weekday. Now, if I go into the rows and drag them
the other way around, then you first have
a breakdown by the weekday and then a
breakdown by the channel. Okay? Now here, I think is great okay, to have the weekday on columns. And then maybe we want to have another break down
by the spotlight, spotlight and adhere onto rows. Now what if I want to have the spotlight but
also as a filter? Well, let's try. I'm going to take the
spotlight and put it on film. Now you see it
removes it from rows. So if you have already a
field on rows or columns, then you cannot also
have it all filled. Okay, So you have to choose
now what actually happened? Well, not so much. We have just a filter box at
the top of our PivotTable, which lets us put
a filter in place. So at the moment, nothing
has failed to just yet. By having spotlight on filters, we can put a filter in place on the spotlight, but
we still have to do it. So click on the drop-down
like multiple items. And let's say I
only want to have the 30-second spots make okay? Now the values are
of course smaller. And because that
129,449 is the sum of the prices that were
paid for all of the spots that were aired on
Channel four on a Monday. But only the 30-second
spots because we have that extra filter over there, which you see in B1. Now instead of creating
that pivot table from scratch like we just did,
Here's another option. If we go back to data
to V marketing spend and just select one cell in the table and then go to Insert. Then you also see
Recommended Pivot. Now when you hover over
it, it says I want us to recommend pivot tables that
summarize your complex data. Click this button to
get a customized set of pivot tables that we think
will best suit your data. Alright, let's give it a try. Click on it. And then we cannot recommend any PivotTables for the
selected data because there are too many
blank cells are duplicate values are not
enough numerical columns. Now we don't get any recommendation because
it complains about too many blank cells or duplicate values or not
enough numerical columns. Now, not very helpful, but even if it wouldn't
have recommended you some alternative structures
for your pivot table? It's probably not
something I would use because before we
create the PivotTable, you already have an idea
of what you want to analyze and then
you're much better off doing it manually yourself. Recommended tables. It's not something I
would recommend now, so therefore, let's
close it again. And let's have a look at some
of the other functionality. I'm gonna go back to Sheet1
where we have the PivotTable and select it by just selecting one cell
inside of the table. And now we're going to
have a look at slices. Now. What does slices, slices? I just good-looking filters. Now here we have already filter. We have a filter based
on the spotlight. And as soon as you have
place the filter on a field, See you in the field section.
It has a filter button. Now, we could add more
filters over here. We could, for example,
put another filter. On, let's say the month. Okay, So we could take the
month gravity on filters. You see we get another
bucks a month. Polygon drop-down,
select multiple items, then choose the month
that you want to have. For example, January, Okay? And now you only see
the January valleys. So we can just keep on
adding extra filters. And every time we place a
different filter on it, it gets this filter icon right next to it, not too clear it. You can either go here and
then select all of them. We can also go over here and
then select all of them. Or you click on the pivot table, go into the top PivotTable, Analyze and then clear, we are filled. Okay? Now, this is okay, but it's not maybe the most user-friendly
way of adding filters. These filters only apply to this pivot table that
we built over year. In the future, you might have more PivotTables and you
might have multiple charts, pivot charts that you
want to add to report. That all needs to be
filtered at the same time. Now, this is where
slices comment. If you take pivot table and you go here to the
top PivotTable Analyze. Then here there's
a filter section. We have Insert Slicer,
insert timeline. And these are basically just good-looking filters that are a little bit more user-friendly and we can make them
look much better. So let's insert a slicer. Here we see all of the fields
we can use for this slide. So let's go over here for maybe the channel
that's big over here, the spot length and click. Okay, now here we
have two slices. Now, let's put them
below one another. You can resize them
in any way you like. And if it doesn't fit,
you get this scroll bar. Okay? Now I'm going to
put them over here, doesn't have to be so
pretty at this point. Now, when we make a selection
here for the journals, then you see it filters the PivotTable and some of
the generals disappear. To make a selection,
you can just click. You can click and drag, or you hold the Control key and choose the ones that
you want to fill it on. Now for the channel is
kind of interesting that we can use
that as a slicer. Because here we could not use gentle and on rows
and unfiltered. You can only have it. I
don't filters are onwards. But now with that we
have channel and rows. We can have a slicer filter that filters the pivot
table that is possible. Now let's try out now
the spotlight slicer. So if I click on Done, then we only have the values for the sports that were
ten seconds in length. Now what happens if we add another pivot table
to this page? Now let's see. I'm gonna go back
to my source cell. Insert. Now, let's choose
PivotTable again. I'm going to place it on
an existing worksheet, which is over here, Sheet1. And before you click Okay, you still have to click
on a specific cell That's plays a
little bit more down from the other one
and click. Okay. And here I want to analyse, let's say the
incremental visits. Let's put in the values. And I would like to
break down by weekday. And let's put the
journal on rows. Here, you see these hashtags
that just means doesn't fit. We have to resize the columns
and That's looks better. Okay? Now what happens when we change the selection that we
have on the slides here? So over here, I'm going to only select the first
three channels. Now, the very first bit
of tape on this filter, but the other one is not. And when this would be
both on one report, you probably want
to filter both. So how can we make sure that that slicer connects
to both PivotTables? Two options we can either
go to the slides and right-click and then
report connections. We can also click on the slicer. And then here at the top
we have Slicer Tools. And then from here, just
report connections. Or we can go to the PivotTable, go to PivotTable Analyze. And then from here,
delta connections, and then we see two which
slices it is connected. Now let's first try
option number one. So I go here to the channel slicer, right-click
report connections. And here we have a window that shows us the
two pivot tables. And you see only the
first one is selected. Now, which one is which? Well, the PivotTable
adopt was the first one. So that is probably the one that's called
Pivot Table one. We can also see this
sheet and so Sheet1, now over here we only have
PivotTables on the sheet, so I had nothing complex yet. However, you can
lose oversight very quickly when you have a lot of pivot tables and pivot charts. So how can we make this
a little bit better? Is we can first select the Pivot Table A3
so that both are selected. So now you see when I change
over here selection that both of the pivot tables are
filtered by that slicer. But the other thing
that I want to do is give these pivot tables a better name than pivot
table one and table three. Now, very easy, you just select the pivot table or a cell
inside of the pivot table, go to PivotTable Analyze, and then on the left hand
side, then we have the name. And here we can change, for example, in the first one. Do price pivot table
and the other one, that's going to be my
visit pivot table. Okay, so just give it a name so that you know which one's which. And then we can do
the same thing. Go to the slicer,
right-click that report connections you see now only pop up with their name. So if you have a lot
of visualizations, lot of pivot charts, pivot tables, then you definitely want to give every
pivot table pivot chart. And this way everything
stays nicely organized. Okay, now we have two options. The other option was to go to the PivotTable,
PivotTable Analyze. Then from here we can also
say felt a connection. Now here you see the slices with their name pop up in the
window and you can see two. Which one? It is currently linked. Now, it is not linked to
the spotlight slices, so I'm going to select
the second one as well. Click on Okay. You see now when we changed it selection
for the spotlight slicer, then both the PivotTables
change their values. Okay, so it's filtering both. Perfect. So this is
how you can work with slices, just
good-looking filters. Now, there are a lot of different options
for this license. For example, if you select one, then go here to the
slicer options. Here on the right-hand side, we have options to change
the height and the width. Now you can also do it manually, but by using these boxes there, you can be a little
bit more exact. Then we have the
number of columns. So when you increase the number, you can also place these
buttons from left to right, maybe at the top of your report. Then the buttons, the size of
the buttons you can change. Then we here, here we have the
arrangement of the slices. So this is the same as in PowerPoint and we have
the slicer styles, okay, now here we can go
for a different style or create our own custom slice. It stopped, which we will do at the end of the
PivotTable section. Okay, now, here we have
report connections already covered and
the slicer caption. Now there's also a button
for slicer settings. Now here you see the
name of the slicer. So general one, but we could
also just call it channel. And you see the caption channel. Then below it, we can change the order in which the
button show on the slicer. You can use custom
lists for sorting. We can hide items with no data, which is actually quite helpful, especially if you want to make slices dependent on one another. For example, if you have a country slides
and state slicer, that if you select a certain
boundary, for example, let's say the US, and you want to have only the
states within the US. Then for the second Slicer, make sure to hide
items with no data so that non-state show
for different countries. Then just click on
Okay, and that's it. Okay, so now we
have seen two ways in which we can
filter pivot tables. We can put over here on
the filter drop zone, the fields that we want to
use as a filter so that user can pick the filter
from this drop-down. Option number two
is to use slices. Slices that also just
failed as birds, you can make them look
nice and they are maybe a little bit
more user-friendly. Now the third option is doing, just go to one of these fields that we have here on
the right hand side, for example, the
length of break. And then here we can just
pick those breaks that were, for example, exactly
300 seconds long. And that also puts
a filter in place, even though we don't have
it on the filter section, we don't have it on a slicer, but we just put that filter in place using that pivot
table fields section. Okay, so we will get back to the filtering topic
a little bit later. Let's now continue with the next thing that you need
to know about pivot tables, which is really important. And that is refreshing
the pivot table. For that. Follow me
to the sheet that is called 06 datasource change. Now here we have
two small datasets. The one that we see here at
the top, that is a table. And you see over here it's
called data source changes. Then here at the bottom,
that is not the table. Now, let's built to PivotTables. One where we use this one and the other one
where we use that one. Now, I'm going to first build a pivot table on the one
that's here at the bottom. Then go here to
Insert Pivot Table. I'm going to place the pivot table right
here, right next to it. Okay? And now I want to
show the price by channel. So I take the price
put in values, take the general polynomials. Then we can update
the formatting, select these values,
and change the format. Now here we have
the same values and an almost the same structure as the underlying data source. So what is the point
of a PivotTable? Well, in this case,
there is no point. However, here I want to
give you an example of what happens when new data
gets added to the source. So when we get over here, a new television channel, let's say new channel. Then. Let's say that the price
is 5 thousand miles. And I go to the PivotTable,
nothing happens. The new role doesn't
automatically pop up. That is because we need to
refresh the pivot table. So when I right-click on
the PivotTable, refresh. Whereas the new channel
still nothing, nothing. Shut that down and get at it. Okay, so new roles don't automatically get added
to the pivot table. And what if one of these
values would just change? So let's say the
first one changes. The price is 7,500,
right leg brush. Well, that clearly
does get reflected. So changes only show up when
you refresh the pivot table, but new rows, they don't
show up automatically. Now why is that? Because if I go to
the pivot table, pivot table analyze,
change the data source, then you see it only
goes until row 27. But we have the new role. And then we'd have to
re-select the datasource. Not ideal. So let's click on Cancel. And instead of that, let's
go here to the top dataset. It is the same data. However, over here, this one
is formatted as a table. You can see when I select
the cell Table Design, here we have the table. And now I'm going
to create a pivot. So insert pivot table. Now let's, let's place it
on an existing worksheet. And I'm going to place it
right next to the other one. And then click on Okay. And here we want to have the price on values,
channel or roars. Everything is the same. I'm just going to update
the formatting again. And now what if one of
the values changes? Well, that's the same. And so only shows up when
I refresh the pivot table. Even though it's a table, the underlying source changes don't automatically
get reflected. We still have to refresh. What happens when we
get a new TV channel. So new channel where
we have, let's say, a price of 5 thousand, then still nothing happens. We still need to right-click
on the PivotTable refresh. However, the big difference is that now because
this is a table, that new role inside of a dataset shows up in
the pivot table. So it doesn't take
away from refreshing. You still need to do
that. He probably wondering what is the point? Why doesn't it go automatically? Well, that is because otherwise
it would be super slow. What happens with
the PivotTable? It's basically takes a picture of your dataset, uses that. And then when new data comes, you have to refresh
it so that it makes a new picture and can work with that picture very efficiently. That's basically
what's going on. Well, if you think, oh, this
is a little bit dangerous because if I share my
workbook with a colleague, a colleague gets into data and forgets the
refresh birthday. But we have a
problem because you might be looking at old data. Now, how can you make
this a little bit better? Which I could do is you
could go to the PivotTable, PivotTable, Analyze and then
options or right-click, and then go to Pivot
Table Options. And then from here you
go to the data section. There's one checkbox for refreshed data when
opening the file. This refreshes all
of the pivot tables that are inside of the workbook. When this way you are working with the latest data in
case there's new data. However, keep in
mind then opening the workbook might take a
little bit longer because it has to refresh the pivot day. So let's click on, Okay. Okay, so another way to refresh the pivot table is just to
click on the PivotTable, Analyze and then
refresh the current one or refresh all
of the pivot day. Alright, then a few
more small details. I'm going to go here to the pivot table that we
created before and she won. And let's say we want
to move one of them. Then to move one of them, you cannot just take one cell
and move it somewhere else. Doesn't actually, you need to select the whole Pivot Table. And only then you can go to the border line and drag
it to wherever you like. Or what I think is a
little bit easier, is when you select the whole
PivotTable Control X to cut. And then you click on the
sound way you want to have it. This can be the same shape,
but can also be a new sheet. And then Control V to
paste it over there. Okay? Now what is important
is that when you have a pivot table
with builders, for example, let's
say length of break, right there we have
the filter section. I cannot just take this part of the demo and move
it somewhere else. We have to select that filter section there
at the top as well. Control X to cut, Control V to paste. Another thing that you
really need to know when you start working with
David Davis is what if that panel here on the right-hand side
doesn't show up, well, that can happen where you
didn't select the pivot table, but sometimes also happens when the pivot
table is selected, because here we can click it
away by clicking on the X. How can we get it
back? While not by just clicking on the PivotTable,
you collect it away. So you have to go
to the PivotTable, Analyze, and there on the
field is to bring it back. Now another thing
that's worth knowing is that the pivot
table field list, you can also structure
a little bit differently instead of having these main sections stacked, we can also go for side-by-side. And often I have it
actually like this. Because then you see all
of the fields over here, all of the drop zones over here. And this gives you a little
bit more working space. Then we also have
field section only, or areas two-by-two,
or areas one by four. So few different variations. And we can sort a to Z if you find that
a little bit easier. So maybe you want to go for a to Z and then peels section, an area section side-by-side. For me, this is probably
the easiest setup. Okay, so that was better
Tables one-on-one. Now in the next section
we're going to dive a bit deeper and look
at different ways in which we can customize the pivot table that
covers not only designed, but also different
ways in which we can summarize and show the values
inside of the pivot table.
5. Customizing pivot tables: So we know already quite
a bit about pivot tables, but now it's time to dive
a little bit deeper. Let's have a look at how we can customize the
pivot table now. But as we go through
the next workbook is 0 for customizing PivotTables. Let's open it up. And for simplicity,
let's stick to the same dataset which you
find on the first sheet. And here we want
to use a dataset again for a pivot table. Now, it is already
formatted as a table. As you can see when you
select Excel table design, observed table, it's got data. Alright? Then we can go to
Insert pivot table, and we're going to place
it on a new worksheet. Now on the right-hand
side, you see there we have our pivot table field list. And here we can say what
we want to analyze, not just like before. Let's put price on the
values and your channel. Let's put that one on two rows, we get a vertical break. Now by default, it always takes the sum of the field
that you put on values. However, sometimes
you want to have the average or sometimes you
want to have the max or Min. Well, then you can change
that by either going there to one of these
values that belong to the sum of price
fields, right-click. And then here we have
summarize values. By now you see all the common
different aggregations like sum, count,
average, max-min. Now, let's say we want to have an average is. Click on it. You see here we have
the average values. Let me just get rid of
the decimals like this. And there you go. Now, option number
two is to go to the section where we have the sum of price
and average of price. Click on the drop-down
Value Field Settings. And then over here you see
we have that summarize value field by section where we can choose the
summary aggregation. Okay? Now, what if we want to have the sum and right next to it another column
for the average of price. Here, let's stick to
the average of Brice. Click Okay. And what you can do is just
take the price is second time and then added a second time
on values that you see. Now we have the average right next to the door, does the sun. Okay. Now, just like this,
I can keep on going. Added a third time. And then maybe you want
to have the max, right? So over here again, go to Value Field Settings or right-click on
that third column. Summarize values by hand,
maybe take the max. Now the names that we
have in the headers, you can just override. For example, here
we have the max, there at the beginning,
we have the average. And here for some Earthrise
we have the totals, okay? But you can override it. You can, of course, also go again to Value Field Settings. And then you have also the
customer name of that gone up. So you could do
it there as well. Alright, now let's
close that box and go to the next step. Now you might want
to not only change the aggregation to
sum max average, but maybe you want to have, let's say the
percentage of Dorado, or maybe you want
to have a rank, or maybe you want to have the difference from one
channel to the other. Well, in that case we
can use what is called Show Values As and let's remove the fields for the
average and max. So here we can just
drag the max out, the average out so that
only the totals hustling. Some laughed. Now I'm going to drag
the price again, right next to tolls
on the values, okay, now it's two times
the same value, right next to one another. But, but the second one, Let's right-click on that column and then go here to
show that exactly. Now here we have a lot
of different options to show these values
in a different way. So we can take the sum of the prices for each
of the channels and then do a second calculation
with those summed balance. Now, here in this case, I want to have the percentage
of the Golden Dawn. Okay, Now let's click on it and see the column totals
and the present and all of the other absolute values get expressed as a
percentage of the blood. Just like this, we can
do some more examples. We could also take the
price but unknown values. And maybe we want
to have the Mac. Right-click on that Goldman. Go here to Show Values As. And then over here we
also have the rank, rank smallest to largest or largest to smallest,
however you like. And then we have to choose
what is the base field. What do we want to make
about the channels? Now, here, there's
no other option because we only have
one field on rows. Click Okay. And you see we have
the highest value, gets the highest number. If you wanted to have it
the other way around. Right-click. Show Values As
and rank largest to smallest. Then the highest value
gets the rank number one. So you see Show Values
As is really important. Now, let's look
at more examples. If we now take the
length of their spots, it's pocketknife and
put that awesome rows. Now you see the
percentages nicely update. So that's all good. The rank of a year is based
on the channel, okay? Which is now maybe a little bit weird if you see two to two. However, that is because
of the base that we chose. Now, if I go back
here to that column, right-click, Show Values As and then rank
largest to smallest. Now you see, we could
also choose spotlight it. Okay, now we are
assigning and rank for each spot length
with him that journal. So over here for general, for the spot length than
that it has the second rank. But over here, if we go
down for free TV there, that Dan second spot
of the first rank. So it is always with him that
pain within that section. Now, what about the
percentage column? Now, here, this is the
percentage of the column tonal. However, what if
we want to have, let's say the subtotal done, right-click, Show Values As. And then we could
say, for example, percentage of the
parent daughter. Now what is the
balanced based field? Channel? Click. Okay. Now you see each time and present a 100%
because that's donor. And all of these
values in-between for the spotlights get expressed as a percentage of the parent. This up don't. Okay,
one more example. But for this one
we're going to have a totally different structure. So let's take the pivot table, go to PivotTable, Analyze
and just cleared it. All right, Now, here
we wanted to have a breakdown by dead on rows. Now what happens is
that baseline data, it creates groups for a month or wherever
you can change that. That's so you can
right-click and group. So let's just see all the dates. You can group it by
right-clicking on it again. Group, and then choose
how you want to group, maybe want to have by month. Like both the month in here. Click Okay. Now you see for 2020, all of the months, okay? Now we have only data
for and yet 2020. And now I want to show
right next to it the price. So price on values. Now let's say we wanted
to have a running total. Then we take the price once
more, put in our values. For the second one, we
right-click Show Values As. And then from here we can
choose now the running total, running total or percentage running total, however you like. Click on it. And what is the base fields? Well, over here we have
the date or the years now we want to have the
date, month break down. Click on it. Click Okay. Now here you see
the running total. So for February is the
year to date value, that is 689,303, plus the
580,325 gives us the 1,207,628. And this accumulates until
the end of the year. If there would be 21, then
it would restart for that. Okay, so we know how to
summarize the values. We know how to show our values as a writing
course percentage. Now the third part
as formatting. Now with formatting, you
have to be a bit careful. Like so far, what I was doing was I was just
taking the cells for which I want to apply different formatting and then used here under the Home tab, all of the formatting options. Now, this is okay if the pivot table structure
is not going to change. However, you have to be
a bit careful because what if I would change the structure
and what will happen? Now? I'm going to remove what
we have here on rows. And now I'm going to have maybe a breakdown
by the weekdays. And over here you see
the sum of price do, well, there's an error. Well, why is there era? Because of the base field that disappeared to calculate
the running total. So I'm going to remove that one. What happens to
their formatting and number formatting for our
values also disappeared. But it's just the
same field that this happened because we apply
the formatting to the cells. In the meantime, the structure
of the PivotTable changed. I see formatting is gone. Now, how can we do
it in a proper way by applying it to
the field itself? Now, you can do this either
by right-clicking on the field number
format or you go here to Value Field Settings,
then number format. Now, this should look familiar because it is a
normal number formatting. And we can say from here
that we want to have, let's say a number, 0 decimals. But we do want to have
the thousand separator. And the advantage is that when
our structure no changes, for example, we add now
Journal of columns. Then you see we have still
the same number for nothing. If we change it to something
totally different, maybe here we have the spot
lengths and then we have, let's say the weekdays below it. You see still the same number formatting because we applied it to the sum of price field. Okay, so basically for your values, there
are three steps. Step one is how do you
want to summarize values? Sum count, average, maximum. Now, once you have decided on that, you go
to the next step. Or do you want to
show you, if you want to show it absolute values, then you don't have
to do anything. But do you want to have a percentage rank or a
difference from well done? You need to make a
choice over here. Otherwise, just leave
it on note calculation done step number three As to go, do the number formatting and then set up the
number formatting. And that's it. Now, all three steps
you also find, if you go here to
Value Field Settings, you find all of these steps
over here in one box. Because here we have
summarize values by Show Values As we
have number formatting, and also the customer name. So the name of the column, as it will show in the
pivot, and that's it. So now that we know how to get the right values inside
of a better demo, let's talk a little
bit about design, but also very important
topic because. To get the message across, you want this pivot table
and later on pivot chart to be as readable as possible so that the insights
that you want to show the user come across
in the most efficient way. Now, how can we
change the design? Now it's not very difficult, but you need to
know where to look. Now, I'm just going to
restructure the PivotTable, what we had before, which
was weekday on columns. And then over a year
for the spotlight with leave it on roles. But we're going to add
gentle and right above it. And if some of rice
can stay where it is, not, let me zoom out a bit. And now we're going to
go to the Design tab with the pivot table selected on oneself in the pivot table, you see here, designer. Here we can choose
different colors. Now, just open it up and you
see different variations. You can hover over it and
see how it would look like. These options that you see here, they are connected to what
you have on the page layout. So if you change the theme now, so let's say we go
for the next week and we go back to Design, then you see the options
over here also change. So if you have a company
theme that you can use an axon than all of
the PivotTable options, the design options, they
relate to that theme. Now for our purposes,
I go back here to Page Layout and dated back
to the standard of care. Now, that's done,
go back to design. Then here you also find that the bottom
new pivot table style, now you could create your
own PivotTable style. However, it takes
quite a bit of time. Here. You have to then go element by element and set up
the formatting. Now, if you haven't,
that's okay. But usually I don't
have that much time. And I look for a
more efficient way, which would be the following. Let's click on Cancel. Could also say, okay, I want to have something that looks like
this one over here. But it's not exactly the
way I want it to be. So let's use that as a starting point and then
just make a few adjustments. So this we can do by
right-clicking on that style. And then we can duplicate it. Then you can give it name. So let's say my style, my pivot table style. And then we can go to each element and
change the formatting. So we could, for example,
go for a background color, maybe we want to gray. You can make it a default
PivotTable stuff, what the document click. Okay, and now we can assign that new custom
style over there. Now with the next
workbook that we open up, that pivot table style
will not be there. Okay, so it is a lot of work. However, if you have a Pivot Table Style
and you want to reuse it in another workbook, you could copy that
over to that workbook, then that Stan will also
be available there. Okay. Now, then the options that you see
right next to it, these pivot table style options. You can click on it
and see what it does. So this is all formatting, okay, So I'll just try it
out and then it's clear. And what changes in the pivot
table now where it gets very interesting is here
under the layout section. Now first of all, we
have granddaughters. Grand totals are the totals that you see here on the
side or at the bottom. So we can turn them off. Then we have no grand totals. Or we can go back to Design. Grant donors. Turn
them on for rows only on the right-hand side
than on four columns zoning. So here at the bottom
or rows and columns. Grant Donald's, nothing special. Now, what about subtotals? Sub Donald's can be
shown at the bottom of a group over here
for each channel. Or we can show them at
the top of the group. Or we can do not show
subtotals at all. Now here let's say we want
to have them at the bottom. But by default, it takes the same aggregation
as what you have. Therefore, the field sum of price can be that it might be interesting
to show the sum, the average, the maximum MIP to add more subtotals besides. So you can go here to
one of the channels, doesn't matter which one
subtotal rows, right-click. And then here we can
go to Field Settings. Here you see subtotals
is loved on automatic, but we could also go for custom. And here we can choose
the subtotals that we want to show you one, okay? Now I see all of
those subtotals. You want to undo it,
you can go back or I simply go to the Home
tab and click on Undo. I expected this to
be on the design, but somehow you have
to go to the field for the subtotals right-click field settings and change
it over there. Now, the next thing that we
have is here, report layout. There are three layers
that you can choose from. We have the compact form, which is what we have
been using so far. And you see, even though
we have on roads, the channel and the spotlight, they pop up in the
same first column. Now here this, well, maybe it looks nice. However, it's not
that easy to use because then we have to
go here to row labels. And later on for
sorting and filtering, we first have to
choose the field to which we want to apply the
filter or the sorting. Now with the outline form, we don't have that problem. Then we have one separate column for each field that
we place on rows. So then you see we can just
go to that column header. And we don't have to
choose which one it is because it's clear that this is the channel and that is
the spotlight column. Alright, so what is done? The difference between
outline and dabbler. Now let's choose
tabular as well. I see, well, what changed is that here we have
first the channel, then on the same row, it continues with the
breakdown of the spotlight. We have the first bought knife. We go back to Outline. Then you see, well, here we have the
channel and then jumps to the next row with the breakdown for the spotlight. Okay, so we have the compact
online and the tabular form. Then here at the bottom, we have two remaining options, repeat item labels and do
not repeat item neighbors. If we go for repeat
all item labels, then you see here it
repeats channel name. And otherwise it doesn't. Over here. That very last option, blank rows can add to the
readability because then it adds another blank row in-between the
different channels, which might make it a little
bit easier to read the day. Okay, so now you have
basically seen all of the different options that
you find in the design. Now, in the next
section we're going to have a look at grouping, filtering and sorting
in more detail.
6. Grouping sorting and filtering: In the previous section,
we talked about how to customize a pivot table using the different
calculations that you can apply to your measures. Now, first of all, we
could do this with, summarize by and Show Values As then the second
part was more how to customize the design
of the pivot table using all of the features
and the design tab. Then this part, we're
going to have a look at three common
analytical tasks, which are sorting,
filtering, and grouping. Now that might sound easy, but inside of a PivotTable, it all works just a
little bit different. Let's have a look and open up
the workbook for this part, which is 05, grouping,
sorting and filtering. Just like before, we have the same dataset that we're going to use for
our pivot table. Now, let's go over here to
Insert and choose pivot table. And we're going to replace
it on a new worksheet. And let's analyze
the price field. So let's put price on values and let's break it down by
the different channels. So gentle on rows, Let's first talk about
sorting the fields. Now here we have
two main options. The first option is to use the
row labels and the labels. Now here we only have
a vertical breakdown, so therefore we only
have Row Labels. Let's go here. Let's click on the drop-down. And from here you see we can
sort from a to Z or Z to a, so in ascending or
descending order. Now, let's choose
the first option. I see we have everything
in alphabetical order. Now of course we can also
do this the other way around from z to a. Then we have it in
alphabetical order, but starting from the back. And what if we want
to sort the journal's not on the basis of
the channel name, but on the total cost, the sum of the prices that
we paid for advertisements. Well, then we can
go back again to the row labels,
more sort options. And then we get this window. Now here we can choose also ascending or
descending order. But here we also have a drop-down to choose the
fields that we want to sort by. So instead of sorting
the channel by channel, we're going to choose over
here does some price. Alright? Now let's click on Okay. Now you see we have our
general store in it in descending order on the
basis of the dollar cost, the sum of the prices we pay. Okay? Now we can, of course do this
also in ascending order. Just go back. And then here choose acid. So this is basically
option number one to go to the row labels, column labels, and
then sort from there. But there's another
option, option number two. Let's have a look. Let's close this window. And over here we can also go to the Data tab and use the Sort buttons that
you find in the ribbon. Now here, it is important
which pay attention to which cell is selected
inside of the pivot table. So if I would select
one of the channels, then go to data and sort
in ascending order a to Z. You see we are
sorting channels on the basis of the channel
name in ascending order. However, if I would
select one of the values in the price
column and then do. Let's say that now
we are sorting the channels on the basis
of the price field. Okay? So the sound that
is selected when you apply the sorting
is now important. Now, this becomes extra tricky when we have
another breakdown. So let's make this pivot table
a little bit more complex. I'm going to add over
here another breakdown by taking the spotlight and
putting that also on rows. And then we're going to
have another breakdown by weekday by placing it on guns. Now, here, we can now go to the grand total column and
let's say we select I5. Notice this is wrong, which I have one
of the channels. So the grand total
for RTL, I see, I'm over here in column I
wear the grand total is, alright, let me
highlight that cell. Now. I'm going to go to
data and say a to Z. And you see the sorting changed. But what changed exactly? Now we're sorting the
channels on the basis of their granddaughter
because we are on a roll where we have the totals for the channel and we are inside of the column
with the grand total. So you see over here, I 5913. Here these values are
in ascending order. Now, if we would have
selected, Let's say I6. So over here, one
cell lower than, let me clear that
background color and go over here to I6. Now, if I would now go here to data and sort in
ascending order, now we are sorting
not the journals, but we're sorting the spotlight. And we're sorting by the grand total
because we are here at the intersection of one of their spotlights with column where we have the granddaughter. They see here we have the
values in ascending order. Here, we have them
in ascending order. And here the sorting of the
channels didn't change. So that also means that if we would go somewhere
in the middle, and then let's say that
we select E5 where we are on the Thursday values
on the general level. And we can go to data
sort in ascending order. We are sorting the channels on the basis of the
values that we have, all the channels on Thursday. Now then there's also
still the option to apply a manual sorts night. Yeah, I've seen before
when we went to row labels or maybe column
labels this time. And we go to more sort options. There we can say that we can manually drag the items
to rearrange them now. So here, if I go back then
and I could for example, take the Monday field, just like the header, go to the border line and you
see the cursor changes. And now I can drag it to
where I very like so I can place it all the way on
the right and drop it. You see, now we change the sorting order and the
same for the channels. I can go over here to one
of the channel names, go to the boiler, and then
just drag it up, drag it down. And when you see the green bar, drop it, and that is
where you position it. Now an alternative to this
is to not drag and drop, but just override one
of these headers. Just say I want to have, let's say two channel sky
here at the beginning, I just type in your sky, enter. And now it takes all of the values sky and
places them at the top. So this only works
within a pivot table, and so you are not
overwriting the adder. You are changing
the sorting order and the same over here. So if I now want to have Monday back here
at the beginning, I just go here to
B4, type in Monday. I say, I'm not
overwriting Tuesday, not chosen, it
jumps to the right. And here we have now Monday
as the first column, okay, So this is what you need
to know about sorting. Let's now have a
look at filtering. Now here we have a couple
of different options. The first option is to
use to fail to drop zone. So let's say we
want to filter on a certain month and we
can take the minefield, drop it here onto filters, and we get the filter box. Now at this point, no filter
has been applied just yet. However, we can go
here to the drop-down, select multiple items, and
make a selection from here. Okay, and as soon as we choose a certain amount of them
would have felt in place. Then here in the fields list, there you see a little filter
icon you want to clear it can go over here
like all of the items. Or you go here to PivotTable, Analyze, and clear all filters. Okay? So this is one option. Second option is to just go directly here to
the month field. Click on drop-down. And over here, choose the month on which you want
to filter, let's say January. Click Okay. And for this, you
don't necessarily need month on filters, okay? So this just allows the end-user to put a filter in place using that box at the top
of the PivotTable. That the big downside of using
that filter drop zone is that you cannot
have something on filters as well as on rows. So here the sport length, I cannot have it as well
and failed as you see, it will remove it from the row. So what can you then
do if you still want to have the ability to filter on the spot names
and everything on rows. Now I'm going to place it back. Now instead of having
spotlight for filters could also make use of slices. Now we have seen them before. So if you take the PivotTable, PivotTable Analyze, and here
we can insert a slicer. We can choose to have it on
the spot length over here. Click Okay. And now here I can
place it wherever you like and choose the spot length
that I want to build on. You see, now we have a filter
on the spot length as well as having it as a breakdown
for a pivot table. Now there's another option
to put filters in place, and that is by using the
row or the column labels. For example, we could go here to column names and say that we only want to have the weekdays. So over here, we can
de-select Saturday, Sunday. Click. Okay, I see we have now only
the week they're showing all of the values for Saturday
and Sunday disappeared. Now this works in
the same way for the row labels that can go into the row labels and then choose which channels
or would like to show. So let's say we don't
want to show free TV. Click Okay. And that channel disappears. Now every time
there's a filter in place on the fields that you're
using on rows or columns. You will see there's
a little filter icon right next to the
drop-down arrow. And let's have
another close look what options we actually have when we go back
to the filter section. Now here, first of all, we have the option to choose the items that we want to show. So manually select those
items that we want to show. If you have a long list, then you can make use
of the search box. Search, for example, for all of the TV channels that contains c. Now here is each
channel for discovery. Now here, what is important
is whether you want to override the filter or add to current selection to the field. Okay, so just keep in
mind that over here, if you don't want
to overwrite it, you have to check that box
and only then click Okay. Then we also have the option
to use the labor filters and the value filters
to belt and a channel. Now, the labor filters are based on the names of
the channels that so over here, we
can, for example, say that it should begin
with the latter or should contain a certain
word in the TV channel name. Now the value filters there. You can feel that the channels
on the basis of values. For example, if we only want to have the
top three channels, we could go here to talk data and then say
how many we want. So we want to have the top
three items based on the Sum of price. Now here
there's only one option at the moment because I only
have one field on values. Here I can click on okay. And the top three channels
or channel for free to be an idea that a
filter options for the labor filter
are there because the field that we haven't
rose is a text field. However, it looks a little bit different when you
have a date field. Now, I'm going to remove
spotlight with Jonathan Rose. And instead of that, I'm
going to take over year to date and drag it onto rows. They see it automatically
creates a grouping by month, which we'll talk
about in a second. What is very important
here is that now we don't have the labor
filter option, we have now the
date field option. And here you see it adjust the filter that
we can put in place. Because now we can,
for example, say, show me the values of this
year or last year, next year. Okay? So the filter
options that we see here are all date field options. But this is very nice
because let's say you always want to have the values
of this month showing. Now, I'm not going to
select it over here because there's all the dataset. But just imagine you would put a filter in
place on this month. Then you could right-click on the PivotTable,
Pivot Table Options. And then here on the data, refresh the dynamin
opening the file so that we always
have the latest data. And because there is a
filter on this month, we only see the
values or decimal. Alright, so we have a
lot of different options for sorting filtering. And now we're going to
have a look at grouping. Because when we placed a
date field onto the rose, you can see that automatically grouped all of the
days by month. And I can click on the
plus icon and then I see the individual dates
for that month. Now, this is because the pivot table created
the grouping for us. But we can also
do this manually. If you right-click
on date fields, you can also choose Ungroup. Now here we see all of the individual dance
and our dataset. Now, then we can right-click on that column
and then choose Group. From here, we can say
that we want to group by the month and the year, okay? Now, maybe we also want
to have the quarters, then just click on
quarters as well. And let's click on, Okay. Now, you see we have the year, the quarter, and the month. And on Ross, you see
we have the years, the quarters and the last field. They're just always asked date. Now at any point, if you
want to make an adjustment, then you can just go
back right-click group. And then let's say we don't
want to have the corners. Get rid of the corners. Click Okay. And you see over
here, just remove that Quarter field that
we have had on rows. Okay, So this is how
it works for dates. And what about dy? Well, for
diamonds exactly the same, but then we have seconds,
minutes, and hours. Now, it is a little
bit different when you have a value field. Now let's say that we remove again the data in
the years from Ross. But this time we're
going to take the price and put the
price on the rows. I'm just going to
remove it first from values, take the price. But now what happens is that here we have a breakdown
of all of the prices. So basically a list of all of the distinct values that we
have in the price column. Now, this on its own
is not very harmful. However, we can create now groups bucket that go
from 0 to a thousand, thousand to two thousand, two
thousand, three thousand. So that we create buckets for the prices that we
paid for our TV spot. Now, how does that work? What kind of in the same way, we right-click on the
values, go to group. And here we can start at, let's say one and add 15 thousand and grew up by a thousand. Then
click on Okay. And here you see we don't have, we have now these pockets. Then what do we want
to show on values? We could, for example, showed the average incremental visits. So let's take the incremental
visits, put in our values, change the sum to
an average. Okay? Then we can also
update the formatting. And then here go for number
with 0 decimal places. Okay? And here we have average incremental visits to the average extra visits that
we've got for each spot. Now this is actually helpful
because now we could make conclusions for
different pricing brackets. So for example, here for the pricing bracket from
five to 6 thousand, there on Monday, we add
extra visits of 38, okay? And with the help of some conditional formatting
we could have, for example, highlight IS
point and the lowest point. Or we could use
conditional formatting to highlight the highest ones in green and the lower ones in red. Now we could take all of these
values and then go here to conditional formatting and apply a color scale just like this. And you see the best results. We add over here,
the prize bucket. But after 13 thousand
on a Monday, the worst one over here, we actually had a
negative result on Monday in the pricing bracket of done through 11 thousand, I see placing a value field, rows or columns usually
doesn't make sense unless you make use of
the grouping feature. Now, what about
normal text fields? And let's have a look
at that as well. Now, here I'm just
going to undo what we did before. Alright? And now I will remove
the price of rows. And let's now create a
breakdown by channel. Okay, Now over here, let me update the
number formatting. Now I want to create two
groups for my channels, the main channels and
the other channels. Now let's say your journal for RTL are the main channels
than I hold the Control key. Select both of them, then right-click on one of them, and then choose Group. Now you see we have
over here group one channel for an audio. The same thing I can do
for the remaining ones, because all of the
remaining ones, they are placed in
their own group. So I select over your sky, discovery fox free TV, holding the Control key, right-click and
create another group. So we have group one,
the main channels group to the other channels. Now we can give these
groups at different name. Just go over here
to group two and then overwrite it
with other journals. And the same thing
for group one there. We can override it
with main channels. Alright, so now we have over
here the groups channels. And if you look here on rows, we have channel two, that's the group and
original channel field. That's it for grouping. So now you know
how sorting works are filtering works,
are grouping. The next section we're going
to have a look how we can do custom calculations with
in the pivot table.
7. Custom calculations: Previously we have seen how
to do calculations using, summarize values by and Show Values As now
in this section, we're going to
focus on how to do custom calculations using calculated fields
and Calculated Item. Now we're going to use
the workbook that's called 06 performing
calculations. Now, let's open it up. Now on the very first sheet, you find the datasets that
we have been using so far, which is about TV advertising. Now, let's create a pivot table that uses this as its source. Now, it is already
formatted as a table, as you can see when you
select one of the cells inside of the dataset
table design. And then here on
the left-hand side, the name of the table. Alright, then we'd go to Insert, insert a pivot table. Let's place it on
a new worksheet. And here we take the
price and put it on values that we want them
to break down by channel. So let's take the journal
and put it on rows. Now let's say right next to it, we would like to show the
average spotlight for all of the spots that were aired on these
different channels. So not only ours,
but all of them. Alright, now, this
field is not there just yet because we
have here spotlight, but that is the
spotlight for our spots. Alright, so how can we calculate this?
We need two things. We need the length of the break. We need the advertisers
in the break. So now we can take the total length of all
of the breaks and divide that by total number of advertisers that we are
doing all of the breaks. So we can do this calculation right next to the pivot table. So over here, we can go to
the next slide and start typing a formula where we refer to the length of the break. And you see in the formula bar, there's a function
get pivot data. Now what does this function do? It just grabs the sum of the length of break
for the pivot table that starts in a three for the channel, channel for, okay? Even if the structure of the
pivot table will change, as long as channel
four and the sum of language break is visible in the pivot table and
extracts that value. Now, this is kind of nice
because now we get that value. And then we can divide that by the total number of advertisers that we had during the breaks. Okay, now let's press Enter, and that gives us 90.6. Now let's copy it down and say, well, it returns the same
value for the audit journals. And that is because over here the reference inside of the get pivot data
function didn't change. So we will get back to the get pivot data function because it can be very harmful. But for our purposes here, we don't want this Get
Data function to show. What you could do is just
delete all of these formulas, go to the PivotTable,
David, David analyze. And then here on the left-hand side and
the options that we can de-select,
generate GETPIVOTDATA. Now, if we do the same formula, so C four divided by d For, you see, the get
pivot data function doesn't get generated. Press Enter same value. However, now the references do change when we copy it down. Alright, this is what we want. These values are correct. However, the downside is that if the PivotTable
changes its structure, for example, we bring in
weekday and put it on rows. Then. Well, that calculation
doesn't automatically extend down because it's not
part of the pivot table. This is an approach that
a lot of people take, but it's not so good. So we have to look for a better alternative to make this calculation part
of the pivot table. No one thought that
you might have is to add an extra column with the calculation inside
of the datasource, update the pivot table,
and then use that. Now let's, let's give that a
try and see if that works. Okay, so I'm gonna
go one step back. And over here, these
values which are correct. However, I don't want to do the calculation right
next to the pivot table. So I'm just going
to drag that right. Now. We're gonna go to the source. Now over here we can add a
new column right next to it. We can call this Column the
average overall spotlight. Alright, let me make
this column a little bit wider and a
little bit less I. And then we can
write the formula. The formula is lying
for break divided by the number of
advertisers in the breaks. Okay, now, Santa copies or down, you can update the
formatting just like this and then go back
to the PivotTable. Now, here, you don't see that
new column. Now Why not? Because we need to refresh
the pivot table by right clicking on
the beverage table and then choose refresh. Now you see it is possible to select it from the fields panel. Then we can drag it onto values. Here. It sums up all of these values that we
calculated row by row. That is not very helpful. So instead of that, you might think, you know what? That's just take the average. Now, let's do that.
However, you see that these values are little bit different from the values
that we calculated before. Now, why is that? Because here, row by
row, this is correct. However, if we then take the average of all
of these values, then we are taking the
average of the averages, which is not something that
you want to do because then you get also
an equal weight to. Each single row. Now what we did before, our first approach that
gave us the correct values. But here, these values
are not correct. And that is because
first you have to sum up all of the break lengths. Danielle have to sum up all of the advertisers that we
have during the breaks, then you can divide
one by the other. Then you have a
weighted average. Otherwise, you're given equal
weight to every single row, which is not correct. Well, adding a new
calculated column to the underlying data
source, It's not ideal. Also, it is easy to make
a mistake when you write the formulas and all of these formulas needs
to be maintained. Now, instead of that, there's
still a third option, which is the preferred
option to do the calculation inside of the PivotTable using calculated. Now we've calculated fields, we can use the values from the other columns to
do a calculation. Now, let's see how that works. Let's go to the pivot table, select one of the cell
inside of the pivot table, then PivotTable
Analyze, and then here we have fields,
items and sets. Then we can create
a calculated field. Let's give it a name.
So we want to have the average overall spot. Okay? Now this is the
calculated fields, so just name it CF.
Then the formula. For formula, we need a break and divide that by the
advertisers and the break. Okay? And now we just have to
update the formatting. Now, I'm just gonna do it
quick using Home tab. Alright? Alright, so over here is say, these values are the
same as approach one, but not the same as
approach to the names. Okay, that is actually the correct value of
the weighted average. Alright, so what is the
advantage of the last approach? Why would the last
approach we don't have to add a new column to the
underlying data source. And when the structure of
the PivotTable changes, then it calculates the
correct values for all of the breakdowns that
you have in your pivot table. Now let me show you this. I'm just going to remove the
ones that are on this one. I'm going to take out this one. Alright, let's just
clear the background. Now I'm going to change the
structure of the pivot table, so I'm going to take the
weekday put in rows. See, it still works. Now, do we still need
the sum of length of break and some of
appetizers and break, No, We can kick those out. Let's remove those two. It still works even though
they're not visible. And we've approached one. Well, you need to have these values visible to be
able to do the calculation. So you say doing
calculations, discussed them. Calculations inside
of the PivotTable using calculated fields
is very powerful. So we've calculated fields, it's important to understand
that you're using the values from different columns
for your calculation. But sometimes you want
to do calculations with items that are inside
of one column, and then you need to
use calculated items. Now, let's have a look at
how calculated items work. Here. Let's just put the
weekday onto columns. And I'm going to take out
average overall, but length. And here we have a breakdown now by the journal
and the weekdays. Let me just update over
here the number formatting. And the next thing that I
want to do is add here Golan, that sums up Saturday and Sunday so that we have the
total for the weekend. That is a calculated
item because here we have all items that
belong to the colon, to the field weekday. And now I want to do
a calculation with two items that are inside of that golden side
of that field. So you need to select
one of these items. Then go to PivotTable, Analyze. And now we can do
a calculated item. Now, it is important that one
of these items is selected because if I would go one cell below it and you see
it's grayed out. Alright? So one of the retakes selected, I can go here to
Calculated Item. Now we can, for example, do the weekend calculation. So I can say weekend. I want to calculate
the Saturday values plus suddenly values
sets that Sunday. Click Okay. Now you see over there we
have Saturday, Sunday, the 340 thousand
plus 361 thousand, and that gives us there
then 701 thousand. Okay, perfect. So that is a calculated item. Now, what if we then
also want to sum up, let's say, some of the main channels and some
of the other channels. Now, how could we do that? Again, Calculated Item. Make sure that one of the
items within the field selected then go to Analyze. And then here we can choose
again Calculated Item. Alright, so we could say
we have the main channels, which is then gentle, let's say channel for plants. What is the other one? I'd say, alright, yeah. Click on Add. And then we have
the other channels, other channels for
the other channels and wanted to have discovery. Plus box plus v d, v plus sky. Alright, that looks good. Let's click on Add. You see now we have
these two items. Alright, so once you have
all of the calculated items, you can close this box by
clicking the X, okay, or close. And now you see here for the genital fluids we have two new items is
calculated items, main channels, and other jumps. Up to this point. It's not very
confusing just yet. But what happens if you have two calculated items that
affect the same cell? Now, let me give you an example. I'm gonna go here to channels,
then Calculated Item, and let's add another
one where I divide the main journals by
the other channels. Now, how can we do that? Formula over here is
mean divided by other. Alright? Now once you have this,
let's click on Okay, I see we have here new role. And here we could change this maybe to, let's say percentages. Alright. Now, there's one cell with two calculations
overlap here, main divided by other. Now, if we take this Saturday value
plus the Sunday value, that would not be 375%, but the big calculation
would be Saturday or Sunday doesn't work. So what is that? 375%? Well, then 1, 7 million divided by 454
thousand and that is 375%. Okay, now, why does
that mean divided by other calculation show and
not the Saturday, Sunday. Here we have to have a look
at the calculation order. Alright, so if we go back over here to
one of the channels, analyze Fields, items, and Sets. And then here we have
the solve order. And you see we have all
of the calculated items. And they get calculated
in this order. And the last one is main
divided by o there. But if I would move
that one up to the top. So before the
weekend calculation, then click on Close and you see that value changes because
now we have Saturday, Sunday gives us that
value over here, that this is only
relevant if you have two calculated
items that affect the same cell and have a
different result, alright, so once you have all the calculated items
and calculated fields, you probably want to have an overview of all of
your calculations. Now, this is super easy. You just go to the PivotTable,
PivotTable Analyze. And then here on the fields, items and sets there we can list all of the formulas and it generates a new sheet and see all of the calculations
that we created so far. So this is all you
need to know about calculated fields and
calculated items. So a calculated field is
accustomed calculation that you can do using the values between
different columns. A calculated item is a
calculation that you can do with the values for
items within the colon. The next section,
we're going to switch focus a little bit because we are going to switch from
pivot tables, pivot charts. Now let's have a look.
8. Pivot charts: Alright, it's time to switch gears a little bit
because so far we have just been talking
about pivot tables. But there are also PivotChart, but you will see it is actually very easy
to use pivot charts. Once you know pivot tables. For this section, we have the
workbook 07 pivot charts. Let's open it up. And of course here we
have our dataset again. But now we're going to use this dataset to
build a pivot joint. Now, how can we do that? We select one of
the cells inside of the devo, go to answer it. And then we are not going
to go to PivotTable. But here too, the Charts section there on the
right-hand side, then we have pivot
joint. Click on it. Now this looks more
or less the same. The only difference really is, is that over here we have
create a pivot chart. Now, here we have
the table that we're connecting to and we're going to place it
on a new worksheet. Alright, now let's
click on Okay, now this insert a
new sheet and you actually see two placeholders, one-foot pivot devo and
one for the pivot chart. A pivot chart is always
connected to a pivot table. Now, let's see what happens when we start structuring
the pivot joint. Now here on the right-hand side, you see more or less the
same as for the pivot table. Now, let's say we want to
analyze it rises again. So let's take the price
put in values and then we have the sum of all of
the prices that we pay it. Now you see we have
the chart as well as the pivot table and both
changing at the same time. That means also I could go to the pivot table and
do it from here. Now to show you, I have the Pivot Table selected. Take the price, but if
values, same thing. Alright, now, let me go back
to the pivot chart selected. Now here on the right-hand
side in the fields panel, you see that columns
is now called logit and what was rose
is now called Access. So if we take the weekday
and put it on the axis, we have a breakdown
horizontally by the weekdays. Weekday is on the axis and in
PivotTable is on the roads. So then we could take the channel and let's put
the channel on the ledger. Now. Here you see we have now
a legend for the channels. So each bar corresponds
to a channel. Now I could also take the
spot knife and put decimal, maybe also on the axis, wherever, then you see it
becomes very difficult to read. Now, just make sure
that you don't put too many fields on the
axis or on the light. Now, if I take spotlight
and put it on the legend, then you see we have
OVA to field panels. That's possible, but it
becomes very confusing. Okay, so keep it simple. I'm going to remove
the spot length. And over here we
have another chart which we can move
anywhere on this. Now, how can we
improve the chart? Now for this, we have
all of the doors that we find in ribbon PivotChart
Analyze design, we're format. Now let's go and change. First of all, the design
because we have very many bars, but maybe it would
be better to go for a stacked bar chart or
stacked column chart. So you can go here to Change Chart Type and then
stacked columns. Click Okay, now that already
looks much better than we have now just one
column for each weekday. And for the channels we
have done here the legend that shows the portion for each channel in
a different color. Now, what is next? Well here you see we have all of this extra functionality that the pivot table also has that. So we can sort, we can filter, we have slices that
we can make use of their PivotChart Analyze. So that is all there. But you also still have the chart tools,
design and format. So you have the best
of both worlds. Now, how can we make this
look a little bit better? Just like we learned before. So we could take OVA, the chart, and let's say we want to decrease the space
between the bars. Then make sure that you
select one of these series. Then control one. To open up the formatting menu, you could also
double-click also works. Use over here the gap between the fields list and the
formatting manual for the chart. Now here we can change
the series overlap, just like we did before. Now, a 100 per cent is
good and the gap width, Let's put it to 50%. Now what if we want
to change the colors? We can go to the
Filling Line Bucket. Or if we want to be quick, we can go into design, change the colors, and go
for one of these pallets. Now what other
changes can we make, but just formatting, right, so we can add over
here chart title. Let's call this one. Caused by weekday and gentle that we can place
it in the top-left corner. Let's maybe get rid of
some zeros and the y-axis. So select the y-axis, go into the three
little columns and the formatting menu acts as
options and display units. We can change, though, in this case a million. Alright, Now this all
looks quite okay. However, what still bothers
me a bit is that we have this gray buttons
and these way buttons, it cannot form a difference. Now, they're not that pretty. And later on when we
actually create a dashboard, you want to work with
slicers that then build all of the
pivot tables and pivot charts that you
have in your report. Now, how can we then get
rid of these gray button? So yes, they are functional
because they let you. Change the sorting
and filtering. However, I don't want to show them because
I want to build, let's say a slice of panel later that lets us fail to ditch the jars to hide the fields that we have
here in this gray buttons, we can select the chart, go to bevel chart, analyze, and here we have the field buttons and
to hide all of them. Well, we go for the last option. I see they're gone. Now we can just take charge
and we presented it. That looks alright. Then make the plot area
little bit smaller. And then over here, the legend, we can put it wherever you like. Alright, so let's maybe put
it at the top over here. And let's make it a
little bit wider. Let they pop up right
next to one another. Then I place it right
below the dido. Okay, now because this
is a pivot chart, we can make use of all of the pivot table functionality is like slices and timelines. So we can take the
chart, go to analyse, and we can insert a
slicer on the channel. And let's say also the weekday. Let's click on, Okay. Now you see we have two slices. Alright, now let's try them out. Put them right next
to one another. I'm going to select the
days that I want to show. It filters the chart. Now I'm going to
just a few channels. And you see also here, it filters the chart and
using these slices in combination with
multiple pivot charts and pivot tables on a sheet, you can create amazing reports. Now, we're going to do
that in the next section, where we're going to create a dashboard using all
of this functionality. But before we go there, let's also have a
look how we can use conditional formatting
inside of a pivot table. Now let's see how
conditional formatting works using that pivot
table over here. Now, we could select the cells to which we want to apply
conditional formatting. Then go here to the Home
tab Conditional Formatting, and choose the conditional formatting that
we want to apply. For example, here, we can
go for top bottom rules. And let's say we want
to have ten items. Then let's go for the top three. And I'd go for a green
fill with dark green text. And click on Okay. Now here, this little formatting
options box that you see in the bottom right corner is very important. And here we have three
different options. We can apply
conditional formatting to the sounds that are selected. Now, this is a bit dangerous
because if the pivot table structure changes while
the conditional formatting still gets applied to the cells. Than we can also go
for some prize values or sum of price values
per week, the n-channel. That's probably the one that
we need for this example, because now you
see it highlights the three highest values over are not considering
the totals here. Alright? Now, also, if we go here to Conditional Formatting
Manage Rules, then you see we have
here the top three applied to some
off-price channel, not to a certain message, note to the fields
of the pivot table. Now let's close this
and let's go to the demo and then
the field section. And then over here, Let's
add another breakdown. Let's add the spotlight
onto the rows. Let me move this chart a
little bit to the right. Here we have now again the three I's values
highlighted here, the conditional formatting is applied to, well, let's open. This is applied to some of
price weekday in channel, not considering the spotlights. Alright, so if I delete
this row and click on Okay, and we apply it. And to apply it, you don't have to select all of the cells. One cell would be enough. Go over here, the top bottom
rules, dropdown items. Here we want to have
the top three again. And then greenfield
with dark green text. Click Okay, click on
that little option box. And here we can do
the same again. However, now values force
per length and channel. Alright, we see this gives
them a different result. And if I go back to Conditional
Formatting Manage Rules, now you see here sum
of price, spotlight. And so now you know
how to work with different visualization tools
inside of pivot tables. So conditional formatting
and how pivot charts work. Now, in the next section, we're going to put
this into practice and build a dashboard
using pivot charts, slices, PivotTables. Let's go.
9. Creating a dashboard: Now it's time to take everything that you learned about
pivot tables and pivot charts and put it into practice by building
a dashboard. For this, we're going
to use the workbook 08 dashboards. Let's open it up. And here we have our usual
dataset about Divi marketing. Alright, now when I
created dashboards, I first start thinking
about the structure. Here. We're going to keep
it very simple. We're going to have
three main sections. We're going to have
the section at the top where we have
the header information. So think about the logo, the title, last update. Then on the left-hand side, we're going to build
a slicer panel, a panel that contains all of the slices of the
filters and timelines. And then in the
main section there, we're going to put the
pivot charts tables. Alright, now let's head
over here to a new sheet. And here we can create outlines
for these three sections. So let me take the first row. I'm going to make it
a little bit higher, around a 100 pixels. Then we take here
the first column, and let's make this one
a little bit wider. Now. Then in the first row we're going to have
a title and a logo. So I'm going to take over
here that first row and do not select the entire row and then put a border
line on underneath it. Instead of them, only take those sounds that you
need for your dashboard. So in our case, we can take
cell A1 and then go more or less to the right-hand side of where our dashboard
variance, okay, now let's say column and
then control one to go to formatting options or
right-click Format Cells. Or you can just go
here to the top. And then from here,
select that you want to have more borders. And here we can choose a style. So I'm going to go for a
little bit thicker style, and let's go for a blue color. Then we can say where we
want to have them line. So we want to add the
border line and the NIF, the first row and click
Okay, and there you go. We have deadline. Then the same thing we can do
here on the left-hand side, so that we have the line that separates the slicer panel
from the main visuals. So I'm going to take your A2, then go all the way
down till the very end of where your dashboard
will more or less at. So for us that's going to be 50, okay, that control one
to go to more options. And now we're going to put the border on the
right-hand side. And then we can click
Okay, and that's it. So now we have the
three sections. We have the part in
the middle where we will put the pivot
tables and pivot charts. This section at the top,
that's going to be the header. And then here on
the left-hand side, this is where we put the
slicers and timelines. I like it when the background of the dashboard is clean white. Now, what you shouldn't do
is select all of the cells, then go here and
choose a white color. Now, instead of that,
just go here too. Page Layout. And then from here we see red lines and
you can turn them off. Now, alternatively, you
can also go here to view. And then here you also have
grid lines in the show. Alright, uh, for
the left-hand side, I slice a ban on there. I do want to have a
different background color. So I'm going to take
all of these cells, then either control one or go to the Home tab and choose
the color that you like. Now, for our purposes, we go for little
lighter blue color selected, and that's it. Now we can put in the title. Now let's say we are looking
here at sales performance. Then we can make the font color blue and make the font
size a little bit bigger. Okay, and then we
can align it in the middle to the
left. Now here in A1. Later on, we can put in a logo. Alright, now that we have the main structure
of the report, we can start adding on
visualizations to it. Now, let's head over
to our dataset. From here. Select the dataset and just
double-check it's a table. Yes, it's stable gold data. Alright, then we can go into
Insert and we're going to choose a pivot chart.
Let's click on it. We are connected to
the table and we want to place it on an
existing worksheet. That existing worksheet
is over here on Sheet1. And this doesn't
have to be exact. It just to see an empty cell, press Enter, click. Okay. Now a normal pivot chart comes from the Connected Pivot Table. However, the PivotTable we take later to a different sheet, okay, and then we
hide that sheet. Now, let's start to
give it a structure. Now let's say we are interested
in the prices again. Take the price but
it onto values, then we want to
have a breakdown by the different weekdays, weekday. Put it on the axis. And for the legend, let's say that we want
to have the channels. Let's put that one
on to the legend. And there you go. We
have our pivot chart. However, it's not so pretty yet, but we're going to make
it look a lot better. Now. First, before we
go on to do that, I'm going to take
that pivot table. Are there Control X to cut it. And then we go to new sheet. Here we can paste it. Okay, Now that is going
to be my datasheets. So let's rename
this sheet to data. Then we go back and this is going to be
my dashboard sheets. So let's rename this
one to dashboard. Alright, here we are only going to show than
the pivot chart. Now, how can we improve
this one are first of all, we can change the
chart type from. Clustered column chart to
a stacked column chart. So right-click. And then here we can
choose Change Chart Type. And here we switched to a stacked column chart
and click, Okay. Alright, and now we
just have to play around with formatting to make it look a
little bit better. Now what can we do?
We can, for example, change the width of the
gaps in-between the gods. So I'm going to select one
of the series, control one. And then from the
right-hand side, we see all of the formatting
options for giants. And then here we can choose these three little columns that show us the Series Options. So at the formatting options specific to that
giant elements, okay? Now the gap width, we can change here to,
let's say 50 per cent. And the next element that we
can improve as the y-axis. So let's go over here and
then format to access. Go to the free little columns. And then here we can change the display units to millions. Then if we want to
change the step size, we just have to go here two
units action and increase. Over here the unit size
maybe to 500 thousand. That's slowly starts
to look better. Now, let's update the colors, which we can do here from
the film online bucket. And then here, choose a different feeling for
each one of these series. Or if you want to be quick, you can just go into design it. Stop, change colors, choose
the palette that you like. Now, I just want to go
for different types of blues and done, alright, so now we just take the whole charts then going
into special effects. And then we can also
add a little bit of shadow in the bottom
right corner. Let's make it a
little bit lighter. And then we can go to
fill a line border. And then all the
way at the bottom, then we have rounded
corners and that's it. Then you see it starts
to look better. Now the only thing that
really matters is the title. So let's add also here title. And let's place that title
here in the top-left corner. Okay, now, that's updated
and say This is mine. Sales by weekday. And gentle. Finish this chart. We can update the sorting order, not on the axis, the
weekdays, it's fine. And we go from Monday to Sunday. However, here for the journals, I would like to have them sorted based on their sales desk. We can do with these gray
buttons, the field buttons. Now we click here on Channel. More sort options. What do we want to sort
and in which order? Ascending or descending. And we want to sort
by sum of price. And then click Okay, I see If you leave it like this, you have all of
these small slices, the bottom, I would like
that the other way around. So I go back over here to more sort options and then
choose here descending order. And you see that the colors are maybe a little bit dark now. So I can go back
here to the chart, design and change
the colors back. So now we just go over
here to the same ballot, but went the other way around. Okay, maybe it looks just
a little bit cleaner. That is, field
labels are of course very helpful when
you want to change the sort order or
you want to put filters in place on the chart. However, it only
affects discharge and not other visualizations that we're going to
place on the report. And also, well,
they're not pretty. So what are we going to do
instead is we're going to hide the field labels and work
with slices and timelines. Okay, now let's hide them first. So let's take the chart, go to analyze and
then field buttons. We can turn them off. Now you probably have to
take them the title and shifted a little bit
up again, like this. And the plot area
again move down a bit. Okay, so now that the
field neighbors are gone, we of course need a replacement, which is going to be done,
the slicers and the diamonds. Okay, now I'm going to
take the chart again. And then here the
PivotChart Analyze, then we have to
fill this action. Now you can choose
to insert a slicer. Let's say we wanted to have
a slicer for channel and for the weekday or
weekend, clicking on Okay. And that gives us two slices. Now we can just drag them
here to the left-hand side. And now you just
have to make sure that either the slices
are a little bit smaller or the first
column that we have over here is
little bit wider. And then we just do the
same thing for the channel. So let's take a channel
and slide it right below the weekday slicer
and adjust the size. Now of course,
double-check if it works. So I'm going to
choose Monday through Friday and nicely
update the chart. Then the same thing for
the channel's going to select the fox
free TV and IDL. And you see it nicely
updates the chart. Perfect. Okay, Now these slices are
of course not so pretty yet, but we're going to make them
prettier in just a bit. So now we have our slices that let us filter the pivot chart. I just see that the title of the pivot chart should
not be sales by weekday, but it should be caused
by weekday in general. Okay, now we're
ready to add this second visual to our dashboard. So let's go back to our
dataset and over here, insert a new chart. And then place it also on an existing worksheet,
which is the dashboard. I'm going to place it right
next to the other one. Santo. Click. Okay, now, then we have
our empty Pivot Chart. Let's place it below the
other one over here. And we want to
analyze the process. So let's take that one
and put it on values. And we want to have
a breakdown over dy. So date on the axis. Let's say we want three lines. So one line for each spotlight. So I take that one and put it on the legend, and that's it. Now we just have to take
it a pivot table Control X to cut and then go to the data sheet and then over here and look for an empty spot. So I'm going to
place it not exactly right next to the
other one because that one might change in size, then it overlaps with this one and you get an error message. Make sure that you place
them a little bit further from each other so that they never will be able to overlap. And then you go back
here to the dashboard. Now, that data sheet
we can already hide, right-click, hide that one. Okay, so now let's take that second chart and put it
right next to the first one. Now, here we want to do a similar formatting changes
as for the first one. Maybe to save a little time, copy the formatting
from the first one. The second one. Not
a major change, but I might save a few steps. And here we want to have
a different chart type because we are showing the
development over time. Maybe it makes more sense
to go for in line charts. So right-click,
Change Chart Type, and choose the line chart. Okay, so now we
have three lines, one for each spotlight that
show that cost developed. So also here, let's add a title. And that's called
this one, costs, development costs over dy. And that's placed it here
in the top-left corner. Here for the sorting, we
don't have to make changes. So let's take the chart, go to analyse and turn
off the field buttons, and then I have to slide the title a little bit
higher, just like this. Okay, now here we
have font size 14. So let's go for the same home, and that's increased to 14. And then the plot area, I'm going to make it
a little bit smaller that we have more space
afforded legends. I'm going to drag it to
the top of the chart. Then I'm going to
make it horizontal. So here, Let's resize it. Then they pop up horizontally
next to one another. Okay? Then position it exactly the
way you'd like it to be. Now we're getting closer. Then the next thing that
I would like to do is to take the y-axis and
showing in thousands, okay, so double-click
or control one. And then here display units, we're going to put 2 thousand. Alright, so let me just resize
the plot area a bit more. And then we take
one of the lines, go to the field line bucket, and here on the line
all the way down, Let's make it smooth lines, and we do that for
every single light. So click on the orange one. And lastly on the blue one. Then we also want to
have a bit of shadow. So let's take the chart again, go here to shadow. And let's put the shadow in
the bottom right corner. And then here also, I wanted to have a
lighter shade of gray. The Finland line bucket
there at the bottom, we can go for around the
coordinates or the chart area. Now you see our dashboard
gets a little bit wider, so I would have to drag this border line a little
bit more to the right, then we get our second
line of visualizations. Or maybe you want to
have a table and then if it's okay before the
positioning of this chart, I always go to the giants
and choose an exact, with an exact height. So the format. And then here for
the web, we can, for example, go for 7.5. Then here for the width, we can choose 12. You see at the moment
they are linked. So if I change one,
the other one changes. So you have to change that
at this you can do here from the menu and then Size
and Properties, size. You see here we
also have the size. And here we can
choose whether at the aspects should
be locked or not, then you would directly
get there also by clicking on that little arrow
in the bottom right corner. So if I go here and click there, you see it brings me
to the right place. Now, I can also make
these changes and decide whether the aspect
ratio should be locked. So just make sure
it's not locked and then choose the
sizes that you like. The same thing I would do then for the other child as well, and choose the same dimensions. So that later on looks
nice little light. So I'm going to take
the second one. And also here makes sure that lock aspect ratio,
it's turned off. And then here for the height, we go for 7.5 and
then a width of two. So once the dimensions are set, then you can take both
of these visuals, go here to Shape Format, and then use that live in
functionalities to align them. For example, at the top. And that's it. I see I have
to drag the border line here at the top and little
bit more to the right. Alright, now we
have to also check, do our slicer filters. Actually felt that that
second visual as well. Now let's drag this. I'm going to choose
different channels. So let's say I want to have, for the first one gets filtered, but the second one,
they can change. Okay, now, how can
we make sure that, that slice, it also
filters the second visual. To do that, we have to either
right-click on the slicer, then go to report connections, and then choose that second underlying
pivot table from here. Or you can also go
to the pivot chart. Then you go to pivot joint
analyze filter connections. Then from here we can
say which will be connected to both
of these slices. They're like, Okay, I
see it directly updates. Now, let's undo the
filter like this and you see both of the
visualizations are now unfilled. It, okay. Both of the visuals are now
connected to both slices. So this is something
you need to check every time you add
a new birthday, a little bit of charge
to your dashboard, okay, Now the next thing
that we need to do is update the formatting
of the slides, because now we just
have the default formatting which is
maybe not too pretty. So let's make some changes. So I'm going to take
one of the slices, go to slicer options,
It's like the dose. Then here we have slices styles and we have the default status, but we can also
create our own one. Now here, my
recommendation would be to choose a style that's kind of close to what
you're looking for. However, usually you
don't find perfectly that style that exactly the
way that you want it to be. So it just choose one
that's kind of clause. Now let's say that orange
one here at the beginning. So I'm going to
right-click on it and then duplicate
that style so that you do not have to set
the formatting for each single element that
we can give it a name. So here we can say
company Slicer Style. And then we can start to adjust all of the
formatting elements. So here for the whole
slides so we can format it. And then we can, for example, say, we don't want
to have a border. And for felon colon,
I want f is same fill color as what I have
for that slicer panel. Click Okay. And just like this, I would make changes to
each element of the slicer. Then we can set it as
default and click Okay, now it doesn't apply
it straightaway, but you do see it now
here on this slide. So statics, we have
our own custom style. And this one we can apply to each single slicer that
already starts to look better. So now we just have
to align them. So let's take both of them, but then nicely
in the middle and then resize them so
that they exactly fit. And if you want to
update the captions, right-click on the
slicer, slicer settings. And then here you can
update the caption. And also if you want to have different sorting
order for the buttons, and you can do that from here. And also if you want to make
the balances between slides and then you can set
either items with no data. This is all fine. We
can leave it as it is and click Okay, now we do not only have slices, then let us filter
multiple visuals that we put on their dashboard. We also have timelines, which is basically just a
slicer filter for dates. So we're going to go back
to one of the visuals. It doesn't matter
which one you choose. Pivot Chart, Analyze. And then here we can
insert timeline. Choose your date. Click Okay. And there you go. We have a timeline that lands us filter the two visuals
that we placed there. So let's put it also here
on the left-hand side. So I'm going to drag
these two down a bit, then take the timeline. I put this one right
above the other ones. Now here for the slicers
and how you align them, you can use a similar trick
is for the visualization. So you take the slicer and then here Choose an exact
height and exact wave. Okay, so here for this one, I could say I want to have
an exact knife height of 6.5 and then an exact
wave of, let's say 4.5. And then I do the same
for the next wave of 45. And here the height, height, we can choose
exactly the same, or maybe you want to have it as high as many
buttons you have. So in this case, I'd probably
would put it at 5.5. Then we get a scroll bar. Let's go for six and
stat then E for diamond. And we can also choose
a different width, so we add 4.5. There you go. Now we can select all three of the slides
isn't timelines. And then here use the
alignment functionalities. And then distributed vertically, again, equal space in between. And then a line to the
left or to the right here. Once I have them
more in the middle, just like this, okay. Also for the timeline,
we can update the formatting by
just selecting it. Just download. And then here I don't want
to have the matching orange. So right-click Duplicate,
and then again, give it a name, its
own line, style. And then here we can
format each element as we want it to be the same
fill color and no border. Then make it the default click. Okay? And then you can apply
it to the timeline. Now it starts to look better. Then we can choose the period for which we want
to show it data, let's say Q1, 2020,
nicely updated. However, the second chart, you see it's not filtered. And again, you would have to
go in here to the slicer, right-click report
connections and then make sure that it's connected to
the underlying pivot table. Now you see Pivot Table,
one pivot table three. We could do better
with the naming of art jars and the pivot tables. So let's update that and go
here to the underlying data. Then select the table,
Pivot Chart Analyze. Then here on the
left-hand side, we can give it a different name. So for the first one, I would say this is then the
cost by that same channel. And then for the
second one there we have the cost development. Then we go back
to our dashboard. And when we done, right-click on the timeline
report connections, you see the pop-up with
their corresponding nodes. Okay, so that's much better. Now let's see if it's not
connected to the second one. Yes, it is. A nicely updates. Perfect. Okay. Now, if you also want to update the names of all
of the other objects, then you just go
to the Home tab. And then here we have fine
and select, selection pane. And then you see all of
the other objects which you can give it different names. You can group them. And if you'd like, you
can also show an IBM. And that's basically it. We have a dynamic
reports where we have the visuals in the
middle that you can filter using this slice
of panel on the left and all of the summary
information we put at the top. Of course, we can
still add more visuals to it and make it look
a little bit better. But that's going to be the
exercise for this section.
Bas Dohmen, Founder + YouTuber
