My name's Gerard Kelly, welcome. Welcome to this session, brought to you by Financial Edge training.

My email address is on screen.

If you have any questions after the session in today's Felix live, we're going to cover pivot tables.

These allow you to reading off the screen quickly, reorganize and aggregate data to reveal patterns.

And we're gonna spend about 40 minutes doing this. Okay? The first thing that we're going to need though is we're going to need you guys to download some material, but if you scroll to the bottom, these downloads, it's this file that we want.

Pivot tables, workout, empty pivot tables, work at empty.

The answer file is above it, but obviously you want me to take you through it and explain how it's happening.

Now I like to give you guys an idea of what we're going to do.

This session is not about slides and it's not about me teaching you pivot tables.

It's about us doing pivot tables. Okay? We're gonna do, do do, we're gonna answer lots of like our bosses requests and we're gonna create loads. We're gonna create, I think at least eight. Okay? So let's open up that file.

Here we are, I want to go to the workouts a tab.

So let's click on that.

Let me zoom myself in a bit so we can see it. There we go.

And it starts off by saying workout one, using the data on the data a sheet that's the next sheet along create a pivot table that sums how much is spent per category.

So we've got spending and category, but let's have a look at this dataset and see if we can make any sense of it without a pivot table.

So let's go to dataset A.

And what we see is we've got lots of dates, all of them from 20, 23, lots of months.

Ah, and we've got lots of categories of expenditure.

There we go for different households.

See if I have a quick look, I can see, ah, I've got household one here.

They had some dining out and then we had some more household one dining out. Awesome. And it'd be really lovely.

It's gonna add all of household ones, dining out, um, uh, expenditures or just add up all the dining out expenditures for everybody.

How many rows have we got? We've got, oh my gosh, over 400 rows down the bottom here.

So pivot tables are going to help us out.

Let's go back to workout A and it says, using the data, create a pivot table.

Can I get you guys please to click into C six? And let's do it together.

I'm gonna go up to the insert ribbon and I click on that.

And on the left hand side we've got this pivot table dropdown.

And we're gonna do this loads and loads and loads of times.

I click on that and it says from table range.

That's the one we're going to use every time.

So I now get this popup.

Now mine's self populated so I'll get rid of that.

And it asks, do you want to select a table or range? And we do. So I click into this white box, make sure my cursor is blinking in there, and I'm now going to go to dataset A.

I need to select all of this data.

Now I could do it with my mouse by kind of clicking and dragging like this, but it'll take a long time because I've got 500 rows.

So instead I'm going to click on B four and I'm going to press and hold on my keyboard Ctrl and shift.

So control shift. And now I'll press the down arrow and it's now selected all of that column down to both 504.

I want to select to the side as well.

So I'll press control and shift and now the right arrow and it's now selected the whole table.

Great. It then says, choose where you want the pivot table to be placed.

We want it in the existing worksheet where we were.

So press okay cool.

And we've got this brand new kinda field that's popped up.

Now, generally you get a different number every time I'm on pivot table 20, you'll be on something else.

When you have a cell selected within this, you get this pivot table.

Fields appear on the right hand side.

If I select a different cell, ah, that thing on the right hand side disappears.

So just make sure you've clicked somewhere inside.

Now the pivot table that we've been asked to do has to sum how much is spent per category.

Now I always like to think if you can work out what you're trying to do, expenditure per category, that is nine tenths of a pivot table.

So on the right hand side here, I want to look for expenditure and category.

And I see I've got expenditure here.

Wait, and I've got category here.

What I'll do, I'll click on category and I'm gonna drag, drag, drag, drag, drag.

I'm still clicking drag and I drag it down into row.

And what Excel does, it now works out all of the different labels from the dataset A and there's dining outs.

Ah, that was one of the labels. Great.

It doesn't replicate them, them, it doesn't have thousands of dinings out, it just goes for one.

So we've done, let's do this correctly, the category. So I can tick that off, but I still need to work out how much is spent.

So I'm gonna take the expenditure on the right hand side and I'm going to click and I'm gonna drag, drag, drag, drag drag, drag drag, drag it down into values and watch what happens in the pivot table.

Amazing Excel has taken all of the dinings out from the dataset and it has summed them together. And that's what it says at the top sum of expenditure.

So rather than me having to come in here to dataset A and go, okay, that was dining out, right? I'll add that to, oh, there's another one. Well, I'll have to add that and then I'll have to add, Excel does it all for us.

There are tables, oh, they've saved my life.

We've even been asked a question.

So imagine your boss has now asked you, Hey, can you just do some analysis on the dataset? Which category has the highest expenditure? Well, I'm going to use just a little formula to help me out.

I use these formulas all the time in um, pivot tables.

I'm going to use the max function.

So I'm gonna type equals max and I want it to just find the maximum of these.

How many is that? 1, 2, 3, 5, 6, 7.

I won't include the total.

So I select those seven cells and I don't want to have a look with my naked eye. I just want to to excel to work it out.

Ah, the max is 8 3 8 2 and I can now see that's utilities.

Great. Of course I could have used something a bit Jazzier could have used an X lookup index match.

That's for another day. So workout one, we've done our first pivot table, guys, amazing.

Pack yourselves on the back. You've done a really good job job. Let's go do another one and let's get some repetition going on.

Okay, so workout two, oh, I should say, are there any questions guys, jump on the chat if you've got any questions at all.

Now is your time. I'll give you five seconds of complete silence.

No worries guys. Silence is golden goldens. Let's carry on.

Work out two using the same data on the data A sheet.

Create a pivot tape. So guys, let's go stop doing that.

I'm going to start my table down here.

I'm gonna go four C 29.

Put it in this big area here.

So how do we start the pivot table? We go to insert pivot table dropdown from table range.

The drop the uh, dialogue box appears.

I'm going to get rid of that workout A.

And now that my cur is blinking, we go to the dataset A, I select B four.

There we go. So there's my, I'm just blinking around in B four there.

And I press control and shift and down, arrow down and then control shift and right arrow and press enter.

And the beginning of my data pivot table is here.

Now let's have a read of what it's asking us to do.

If you can understand what you're being asked to do.

It's nine tenths, I promise it is.

So it says create a pivot table to show how much is spent rate per category.

Ah, that's the same as the previous workouts.

Ah, per month, right? So we've now got a new thing we've been added fast to do.

So let's do the same as before.

We're gonna have category, that's one of them.

I'm going to put that into rows.

So click drag it into rows spent expenditure.

Let's click, let's drag that, put that into values.

Excel has summed up all the expenditure. Great, but we we're not done it now said per month.

So we've got a choice of how to do this.

Okay, I think it would be handy to have my months appear across the top here, across the columns.

So I'm going to make sure I click into the table, I'm going to grab that month and I'm going to put it into this columns box in the bottom right hand corner.

So click drag it down into columns, unclick.

Ooh. And now we've got something looking good.

I can now see in January all of the expenditure that happened.

I can look up February, the expenditure, et cetera, and we can do some analysis. So guys, let's answer the questions.

Which month sees the lowest expenditure? Let's just use a min function.

I'm gonna keep it as min and max. Gonna keep the functions really simple.

Okay, I wanna take a min function and I'll select these grand totals at the bottom, gonna select all those grand totals, not the final column 'cause that wouldn't really help.

That's just a grand total of a grand total.

And let's find the minimum of them.

Close bracket into 2, 4, 3. Oh.

Now I just have a quick look with my eye.

Ah, that's February. Great.

And I could have used text lookup or an index match or something else.

Which month is the highest expenditure? This is so much easier with pivot tape, isn't it? So I use the max function, select those cells along the bottom, but don't include that grand total at the end.

It's 5 7 2, 3 0.70 that's January. Oh, that's weird.

So already that's come for weird pattern that I'm seeing here.

January has the highest expenditure, but February is the lowest.

Feels like everyone's out spending in January.

And then February everyone pulls themselves back.

So already I'm seeing some analysis that I wouldn't have got from the dataset on its own.

Next one, which category sees no expenditure in a particular month? This is a relatively easy one 'cause I can just look for the blank month.

And that's December and we can see that there's no expenditure on healthcare.

Oh, strange. So December, healthcare, what do you notice about the highs and lows in the dining outs category? Oh, okay, let's have a look at dining outs.

Well, if we grab the high figure, oh, August looks like it's the high.

We've got the second highest in December and the lows kind of in between.

Yeah, all those lows are in between the two highs.

Guys, what am I seeing? I think I'm seeing people going on vacation in August and then people having their winter holidays.

So Christmas holidays, vacations, et cetera, in between.

Maybe people are a little bit struggling for cash, so already I'm seeing more analysis I would've seen otherwise.

What do I see? What do you notice about the highs and lows in the dining out category? Dining out more around the holidays and by holidays I'm including August, summer holiday.

Which category has the highest expenditure in July? Oh, it's transport people traveling to their holidays.

But then that doesn't seem to link up with the dining out in August. Maybe people are traveling at the end of July and then they're actually eating out in August.

I'm not entirely sure on this one.

Or maybe people are booking, actually paying for their holiday in July and actually going on in August.

We'd need to look further to find more analysis.

But you see already we're coming up with some ideas that we can then test.

So which category is the highest Expendables transport.

So that's work out too guys, any questions for me? Any issues, anyone not working? I'll give you five seconds of silence, jump on the chat, ask me a question, everyone's happy.

Okay, let's keep going then let's get some more done.

Ooh, what if you have more than three variables? Great question. That's come in from the chat here.

Okay, you can start adding them in here as well.

Let me show, show you just another way we could have done what we've already done.

Just to help you out with the idea, I'm gonna get rid of month.

I'm gonna instead move month into rows.

I now what I get is my dining out by month, education by month, entertainment by month.

And I could still put another item in the columns. I could even put multiple items in the columns.

Great question in the chat on that one. Thank you very much.

I love it. Cool. Uh, I'm going to undo that if that's okay.

Otherwise it'll be affecting all the other workouts.

Cool. Workout three, copy the results from workout two and paste below.

So I'm going to put it back to what it was.

Okay, now copying a pivot table is a bit of a shortcut to get a pivot te to get lots of pivot tables.

So I need to select all of the cells, which you can do like this.

Or if you just hover over the top left cell, can you see I get this kind of black arrow.

If you click that black arrow, it selects the whole thing.

So I'm going to copy it with control C down below here I'm going to paste with control V.

So the question asks us to sort the results from Z to A.

Now, normally we'd start thinking filters and we'd go up to data and start doing things here.

Pivot tables have their own filters and we're gonna make them even better.

We've got these little dropdowns already for the rows and for the columns.

Let's sort these results from Z to A by clicking on the row labels dropdown.

And at the top here we can see sort Z to A.

Now it goes from utilities down to dny.

Next says filter the results to show the dining out and education categories in just January and May.

All right, so dining out education January and May.

And it then says, what do you notice? Hmm? So let's do dining out.

So I click on this filter again and I'm going to deselect all and I'll just go for dining out and educate. Was it education, dining out and education? Indeed it was.

So dining out education press.

Okay, And now I need to do it for January and May.

So in the column labels, click the dropdown deselect.

I just want January and May. So I click on those two press.

Okay. Ooh, what do we notice? I notice January big month for expenditure.

We actually saw that earlier in one of the other workouts.

Big month for expenditure.

Certainly a very big one for education.

But by may, hmm, maybe there's some kind of spring break, a little bit late for spring break.

But there's something happening there that's stopping any expenditure happening on education.

Maybe people come back after, you know, December, want to do some training courses.

They want to ramp up before they hit the the maybe the busy part of the year for their work.

So expenditure goes down on education, dining out instead goes up.

So again, we're seeing more analysis than we would've otherwise.

Guys, let's keep going. Let's go to work Out four.

It says copy the results from work out two and paste below.

Do you know what? I'd love to, I would love us to build it again.

Okay, I think practice, practice, practice is important.

So I'm going to click into C 71.

Let's build it again. Let's go for inserts.

Pivot table from table range, the dialogue box pops up and make sure I'm clicked into that white box.

Go to dataset A.

Now I'm at the bottom of dataset A, I need to select everything. So I'll start in the bottom right and I'll press control and shift and left arrow.

Ah, that selected the bottom row.

And now control and shift and up arrow, I've selected everything.

Great press. Okay.

Now remember understanding what you're after is nine tenths of it.

Your boss wants to understand right, expenditure by category and wants to shift the months included.

We then need to insert a timeline and show expenditure for just the quarter from March year 23 to June 20.

Oh wow. That should actually be April, shouldn't it? If it's gonna be q2. Okay, so the way we're gonna do this is we're going to have to fill in our items.

So expenditure and category. So let's go do that.

So category, I'll put that into rows and expenditure.

I'll put that into the values. Great.

And then we need it by month.

So grab a month, put that into columns.

Perfect, but the boss only wants it for Q2.

Okay, so how we gonna do this? It says insert a timeline.

Timeline are gonna save you so much time and they're really easy for beginners.

So click somewhere in the pivot table and in the ribbon we get these new two, we get two new ribbons, appear, pivot table analyzes the one we want.

And in the filter section, I want to insert a timeline.

Now the timeline asks you what do you want to timeline it by? I want to do it by date rates.

And I'm going to just drop that.

I'm gonna pick it up, drop it underneath.

Now it asks me, do you want to do this by month? I could do it by month.

But if you click the dropdown you can filter by years, quarters or dates.

So this is really filtering, okay? But it gives you a handy thing to use on a dashboard.

So I'm going to click on quarters, great.

And it asks me just for Q2.

So if I click in here, I can deselect all the quarters I don't want and watch what happened to the table.

The table is now just April, may and June.

I can move this around now it includes the next quarter, now it's just quarter three and it just makes life that little bit easier for shifting my filters around.

It's really easy for beginners to use. Love a timeline.

Really good guys, any more questions for me? Five seconds of silence. Get thinking on your questions. Go.

Okay, I'll keep an eye on it just in case anyone anyone's questions come in.

Let's keep going down to workout five.

Workout five says using the data on the data a sheet to create a pivot table.

Great, let's start out, let's insert a new one.

I'm going to C 97.

Let's go to insert pivot table from table range.

Cursors clicking. I now click into the data table.

Control shift down arrow control shift, right arrow enter.

Okay, what are we being asked to do? It says you wish to compare spending patterns. Okay, so spending again across different households this time and categories before starting, try to work out which items are gonna, that's what we always do.

Okay, so I need households and categories this time rather than months and categories.

Let's, let's shake things up, let's change, let's change it around the way, way it's organized.

Let's have our categories across the columns. This time just just for trying something different, let's just try something different.

So categories, I'm going to put them down here into columns.

Okay, there they are. They've come across my columns. That's awesome. And this time we'll have households in our rows.

So go up, click on households, drag it into rows.

Awesome, we'll fix that household 10 in a second. That's a really annoying thing, but pivot tables can sort it out easily.

The last thing we were after was spending to expenditure.

So I click and drag on expenditure, take that down to values.

Oh, got my numbers. Great.

Now the annoying thing is household 10, household 10 ends up under household one that is the default for pivot tables, but you can change it really quickly.

The way to do it is I go up to house of 10, I wait for that black arrow so I can then select that line and then if you just grab the edge of it, so you get those four arrows, you see how I've gone to the edge of it and I got those four arrows, just click drag and you can just drag it to the bottom.

Pivot tables allow you to move your data around and reorganize.

Now we've been asked some questions, which household spends the least in total? And for that same household, which category is their lowest expenditure? Let's use them in function. Love them in function.

So min, I'm going to choose the grand total for the households but not the bottom one because that's not a household.

And the min expenditure was 4, 5, 11 0.6.

Ah, it's household two. Okay cool.

So let's write household two in there. Excuse me.

And then it says for that same household, which category is their lowest? Again, I want to use the min function.

I'm now going to select just household two's expenditures, not their grand total this time.

And the min is one five. Five, great.

I can now see that's dining out household.

Two dining out's, not their thing. Great.

I've now got a bit more analysis guys. Let's carry on. We've, we've got about 11 minutes left.

So let, let's carry on.

Workout six says copy the pivot table from the previous workout.

Let's do that. Let's go, let's go up again.

Let's remind ourselves how to do that at.

If I hover over the top left, that black arrow appears click and it selects all of the pivot table.

I can now copy control C.

I'm gonna take it down to C 1 27 and I'm going to paste here.

Great, there we go. So copy the pivot table and use slices as alternatives to filters.

Well we already used timelines. That was pretty cool.

So what slices, make sure you've clicked somewhere in the pivot table.

Then in the ribbon, these two new ribbons appear and we want to click on pivots table, analyze.

And in the filter section near the middle of my screen or my ribbon, we've got this insert slicer, excuse me, click on insert slicer.

And it asks you for various filters. Do you want to like filter or slice by date and month category? So let's have a quick look.

It asks us to slice by category, household at the bottom and month rates.

And it then says some colors.

We'll do that if we've got time. So I press, okay, now they appear over the top.

So let's just scroll down, let's put them all underneath, move them all down a bit.

So they're underneath our pivot table.

So category, what was the order that we were given? We were given category, household month.

Let's do it in that order. Category, household and month.

There we go there.

What are we being asked to do here? What we need to do, it says once the slices are created, select the following items from them.

Category should be dining out and groceries.

So let me show you this. You can just click on dining out here and watch what happens to the table.

It filters just down to category.

However, if we then decide, oh okay, yeah, let, let's do the second one that we were asked for.

Uh, let's go for groceries as well.

It only gives you one of them.

You get groceries, all dining apps.

So what you need to do is just press and hold control and then you can click on the second item and they both get selected.

These slices again, make it much easier for you to give to a boss and maybe your boss isn't so good at pivot tables.

And that allows your boss just to quickly filter the data.

Excuse me, it then asks for household one and two.

So I'm going to click on household one.

I want to select household two as well.

Uh, but it just selects one or the others. That's really annoying. Press the hold control household one and two.

Great stuff. And then lastly, the month.

It asks us for January and February.

So I click on January, press control and February.

Great stuff. And now what do you notice about household one and two? Household one, they love the expenditure.

I get the feeling they've got a bit more money.

Or at least they like spending money on dining out and groceries. Look how much they're spending. Household two, much lower, no dining out, lower groceries, probably a smaller household.

Awesome stuff. Awesome, awesome stuff. Cool.

So that's my pivot. Ta uh, pivot tables, inserting my slices, love and slices.

Let's carry on down to workout seven. Workout seven.

Using the pivot tape, using the data on the data a sheet.

Create a pivot table. Okay, I'm gonna click C 1 62.

Same again. Insert pivot table dropdown from table range.

I go to dataset A, select the bottom corner or the top corner.

Control shift left and control shift up press.

Ah, got my pivot table greats show the average expenditure for each category.

Okay, so we're definitely looking at expenditure and categories and that's our starting point.

Come to the the average in a second.

Always, always, always try and work out what's going on on the right hand side first.

So category, let's put category back into rows.

So I click drag the category down into rows.

Expenditure was the other item.

So I click and drag expenditure down into values.

That'll sum up all of the values. Great.

So that's my starting point. That's great.

I've got the sum of expenditure, but we're now being asked for the average expenditure.

So how do we do this? Make sure you've clicked somewhere in the numbers.

And we go up to pivot table, analyze again.

And on the left hand side, in this active field section, it even says some of expenditure.

Oh, awesome. And we can change that.

So let's click the bottom underneath it. Field settings.

And where it says custom name sump.

If I, excuse me, if we go down a little bit, summarize value fields by, we've got the sum there and change it to average.

So click on average the custom name changes press. Okay.

Oh, after our average expenditure where asked the question, which category has the highest average expenditure? Well can use a max function for that. Can't we? So equals max, select those expenditures, close brackets and the highest, ah, the highest, what average was groceries? Great. However, I'm gonna move that up a bit.

So it says that my way where then asked another question, then add a separate column for the sum next to the column for the average.

So we want average here. We want the sum here.

Now I get asked this all the time, Gerard, I just had the sum.

I liked the sum, I want them both. I want average and sum.

So how do we do it? It's actually so simple, but it's kind of hidden.

It's in plain sight. You just can't see it.

On the right hand side here where it says expenditure that's already ticked and it's already in the bottom right hand corner and it makes you feel like, oh, I can't do anything more with this guys.

Click and drag it again into values.

Click drag, put it into values again, sum of expenditure appears winning. Winning at power. Uh, power tables. Winning at pivot tables.

Cool. Awesome guys, we're into the final seconds.

So let's, let's do one more. Let's see if we can sneak one more.

In workout eight, it says recreate the same pivot table as in the previous workouts.

Let's copy and paste, shall we? So I go up to the top left, wait for that big black arrow to appear.

Oh, it won't work for me.

So I'm just going to click on the first cell and then select all of the cells.

Copy come down here and paste.

Now it says here, your boss has asked for a column chart to help visualize.

Oh, I've got a question. Why doesn't control shift shortcut not work on the pivot table? Oh no. Oh, I'm so sorry to hear that.

So I'm hitting control shift and then down arrow.

And it's working for me. It's working for me.

I'm so sorry that's not working for you. That's sad.

But let's see if we can finish this one. We've only got 90 seconds left, but let's see what what we can do.

We're asked for a column chart to help visualize just the average expenditure.

Okay, so click somewhere in.

And again, I go up to pivot table, analyze at the top, I then, excuse me, click on pivot table, analyze.

And in the tool section on the right hand side here, we've got pivot chart, click on pivot charts.

And Excel has already worked out that I probably want a column, but I could choose others.

There are various others that we could do.

I'm gonna go with a column, so I press okay, as my column has appeared. That's awesome. Now the only thing is at the moment it's got both the average and the sum.

So I want to make sure I don't have that sum in it anymore. So I'm just gonna go to the bottom right here where it says values.

I'm going to drag out the sum.

Brilliant guys, look at the numbers. Let's do some analysis.

What is my highest on average expenditure category? And its groceries. Most people in these households are spending money on their groceries or at least that's their highest average expenditure.

After that it's just utilities, just pipping out healthcare.

After that, the others are a bit lower.

So education's lower entertainment, dining out, and the very bottom is transport.

Cool. Couple of other things we can do.

You could then just click into the total if you wanted, click the total, you could press equals, you could then link up to a cell.

So I'm just gonna link up to workout eight. Workout a, Kate.

That title is now linked to a cell. That's awesome.

If you want to do that, could get rid of the legend.

Just click and then just delete and you've got yourself an amazing chance.

Cool. Fantastic guys, I hope you have found that useful.

And that was a whistle stop tour of 40 minutes.

If there are any questions, do stick around.

Otherwise, I'm gonna have to say, have a lovely rest of your day guys, and I'll see you guys on another Felix Live soon.

Bye-bye. See you guys. Bye-bye.

Excuse me. Cool.

Any questions guys, feel free to jump in the chat.

Okay guys, have a great rest of your day. See you. Another Felix loves you. Bye-bye.