Hello, my name's Gerard. I'm going to be your trainer today.

We're going to be looking at pivot tables.

That's going to be our focus. On screen, you can see my name and my email address. If you have any questions, please feel free to come back to me, ask me any questions you wish.

We're going to be looking at pivot tables.

We're going to be showing you how to set them up.

We're going to be doing filters, slicers, timelines, pivot charts, and formatting. We've got loads to show you guys in terms of pivot tables today.

And if you scroll to the bottom, we're going to use just one file. We're going to be using this one I'm currently highlighting, Pivot Tables Workout Empty.

If you're someone who likes to have the solutions as well, for after the course, then the solution file is just underneath Pivot Tables Workout Full.

Fantastic.

Let's open up that Pivot Tables Workout Empty file.

Click on that, open it up. Welcome.

Click the link in the chat, please, and open up this Pivot Tables Workout Empty file.

Fantastic.

Hi.

So guys, let's get ourselves into the Pivot Tables Workout Empty. I'm going to start. "Please can you resend the link?" Of course I can.

Yeah, let me stick it into the chat one more time.

You guys are so interactive today. This is lovely.

Cool. It's in the chat right now.

Now, where are we going to be working? We're going to be working in this Workout A tab, but we'll also be referring to the Data Set A.

Let's just have a quick look at Data Set A.

In Data Set A, we've got lots of dates, and then months, and we've got things that people have spent money on.

Maybe they've had entertainment expense, dining out expense, healthcare expense, and then we've had the expenditure.

And then we've got the households, and there are 10.

What we want to do is understand a bit more about those households, how much they're spending in total on maybe dining out.

Which household is spending the most on dining out? Are there times of year, months, when healthcare is more important or dining out is more important or transport is more important? We want to understand a bit more.

The problem we've got is that this is, oi, 504 rows long. So pivot tables are going to help us to take that data, squash it together, and give us some value add.

It enables us to analyze the data.

So I'm going to go to the Workout A tab, and I'm going to take you through as if you know nothing about pivots, but we're going to cover quite a lot in about the next half an hour to 45 minutes.

So let's start on Workout 1.

It says, "Using the data on the Data A sheet," that's it, that third one there, "create a pivot table that sums," here we go, so we want to sum, "how much is spent per category, and which category has the highest expenditure." Right.

Can I get all of you guys, please, to go to cell C7? Please don't do it in B. If you do it in B, your answer will end up deleting all of these questions underneath.

So let's do it in C7. All right.

So we need to set up a pivot table.

In order to do this, can we go to the Insert ribbon? And we're going to create, like, nine or 10 pivot tables today. Right.

On the Insert ribbon, we've got this Pivot Table button on the left-hand side.

I'm going to click on that drop-down, and it asks me, where's your data? And I'm going to get my data from a table or a range.

Let's click on that.

And it tells me to select the table or range, and it then says, "Where is it?" You might notice my mouse is blinking inside here. It's very difficult to see.

I'm going to click on this up arrow, and then I can go looking for my data.

So I'm going to click on that up arrow, then I'm going to go to Data Set A.

Now, I don't want us to start at the top, because most of you guys are probably at the top here. So can I get you guys, please, to press and hold the Control key and then the down arrow? So Control, down arrow, and it jumps us all the way down to the bottom.

So I want to be able to select this data really quickly. We're going to have to do this nine or 10 times.

That's why I want to get us doing it properly.

So if you listen to what I'm going to do, I'm going to press Control and Shift. I'm going to hold the Control, hold the Shift, and now I'm going to hit the left arrow. So you might notice I'm here.

I'm going to hit the left arrow, and watch what happens.

It quickly selects all those cells.

I'm still holding Control and Shift. Control, Shift.

And now hit the up arrow, and it's now selected all 500 rows.

So I'm going to do that one more time, just to make sure everyone's there.

Going to get rid of all of that that was in there. Hit the up arrow.

Down at the bottom.

So I'm starting in the bottom right or wherever, doesn't matter.

Control and Shift. Control and Shift.

And I'm going to press the left arrow And then the up arrow. And now all of my data is selected.

Okay? Now if I go back to that up arrow, I can click it down again.

Choose where you want the pivot table to be placed.

In the existing sheet we were on, press OK.

And this is the beginnings of my pivot table. Okay? So it's just this great big kind of like white empty area at the moment.

Now, when we click somewhere within it, we get this pivot table fields on the right-hand side.

That's really important. We're going to need that.

But if I click somewhere outside of this pivot table box, I lose the fields. So if you lose those fields, just click back into the box. Great.

So I want to find out how much is spent per category.

Okay. Well, we can do that. In order to do that, I want to find my category. There it is.

And I want that category to go down here into the rows section. We'll come back to columns later.

So I'm going to click on category, and then I'm going to click and hold, hold, hold, drag it into rows.

Oh.

Justin, let me sort you out.

It's in the chat now. And Justin, keep an eye on the screen.

We're using this pivot tables Workout Empty here. There you go.

No worries.

Okay.

So guys, what it's done, just quickly, it's taken all of these dining out, dining out. Is there another dining out that I can see? I can't see another dining out, isn't there? Another dining out, another dining out, and it's worked out.

Oh, let's just have one of them, one dining out, and we'll sum up all of the expenditure.

We just haven't done the expenditure yet.

So on the right-hand side, it said how much is spent per category. So we've done the category, tick, but now I need how much.

So for that, I need this expenditure.

I'm going to click, and I'm going to drag that into values.

So I click on the expenditure. Click hold.

Hold, hold, hold, drag it down. Oh.

I drop it into values, and look at that.

Absolutely amazing. Pivot tables has really quickly found all of the dining out line items, and it sums them up, and now I've got some analysis that I can hand to my boss or whoever.

My boss has asked me a question.

My boss has asked me which category has the highest expenditure.

Well, that's great. I can see utilities.

Utilities is the highest out of all of these.

It's the only one that goes above 8,000.

If you were looking for a slightly more Excel way to work that out, I could press equals max, open brackets.

And now it's going to find the maximum or the highest out of the cells that I select, and I'm just going to select these cells, not the total.

And I get 8,382.4.

Utilities. We just type in utilities then.

You've just done your first pivot table. Amazing. Cool.

Popped your pivot tables cherry. Now, if that's okay, we can carry on to workout two. But are there any questions? Workout two's going to be very similar.

So if you found workout one hard, don't worry, we're going to do the same thing again, again, again, again, again.

Mm.

So Jamal's asked a question.

Jamal's asked a question, "Why would inserting the pivot at B7 delete the questions?" So because if this had been any longer, it would have deleted all of this text here, and we wouldn't have had many workouts to do. And actually, as you go down into the rest of the questions, it gets worse and worse and worse as you go. So Jamal, that was more for my benefit than for your benefit.

No worries. Stephanie, great question.

"How do we get two decimal places?" Okay, so what I'm going to do is I'm going to select them, select the numbers that I want to be two decimal places, and then I just go up to my ribbon, and it's this little guy here gives me two decimal places. You'll note I didn't do it for the grand total.

Maybe I want four decimal places there.

Handy.

Cool. Guys, great questions. You guys are full of it today. This is brilliant.

Okay.

If that's all, let's have a look at workout two. We'll do a bit more.

So I'll click out of the pivot table.

Fields will disappear.

So workout two, using the data on the data A sheet, that's it there, create a pivot table. So that's going to be our starting point. All right? If I could get you guys, please, down into C30.

All right? Let's just start the pivot table. Before we read all the rest of this, let's just start the pivot table in C30.

How do we do that? We go to the insert ribbon.

On the left-hand side, I've got the pivot table button.

Click that drop-down.

We then click from table range.

We're going to do this loads of times.

It then asks me for the table range, and it's already got my cursor blinking inside there Click the up arrow, go to Data Set A.

And I'm starting in the bottom right-hand.

So how am I jumping up and down so quickly? I'm just pressing the control button, control down arrow, down arrow, and now I'm at the bottom.

Yeah, so assuming you're at the bottom, I hit control and shift, left, and then control and shift and up.

Same as before.

Now, we could click this little drop-down and then check everything.

I know that everything else is correct, so we can just press enter.

Done. And that's the start of Pivot Table 2. Brilliant. You might notice it's just creeping into Workout 3.

So let's now have a look at what it was asking us to do. It asks us to work out how much is spent per category. So we just did that. We worked out how much was spent, and we worked out how much per category. So we've done this already.

So what's the difference? Ah, per month.

So I don't just want total for the year, I want to see January, February, March, April, May, et cetera.

So it's going to start very similar.

I need my category to go down into the rows. So let's click category, rows.

And my categories have all ended up in individual rows. That's great.

I then want expenditure to go down to values. So I click, and I drag it into values.

Same as Workout 1.

But now I'm going to try something a little different here, and it's probably not going to work. Let's just see what happens.

I can see that there's month.

I'd like to just tick it. Let's just tick, and let's see what happens if we just tick Month. If we tick it.

Ah, cool. Dining out, I've got January, February, March, April.

And then education, January, February, March, April.

And then entertainment, January, Feb-- But it's given me this very tall table, and it's actually going down to row 121.

So that might work for some people, but instead, I'd like to have January, February, March in individual columns.

So I'm going to take that month, and I'm going to click on it, hold it, hold, hold, hold, hold, hold, hold, hold, and then move it up to the columns.

That looks so much better. That looks so much better.

I can understand what's going on now.

So let's start answering some of these questions because we've now got some information we can do analysis with.

Which month sees the lowest expenditure? I'm going to use that min function.

It's opposite for max.

I'm going to use the min function to search in the month grand totals.

So I've selected from D39 to O39.

What was the minimum expenditure? It was 2,430.5. That's February. So let's put that answer there.

February, very nice.

Which month saw the highest expenditure? Okay, let's use the max function.

Select, again, those same cells.

Don't include the grand total at the end.

So the max of them, the month with the highest expenditure, had expenditure of 5,723. Where's my formula? 5,723. Where's that? January. Ooh, that's interesting.

Maybe there's a trend we can get from that.

If January is the most expensive month, kids are going back to school, everyone's going back to work.

Maybe it's winter. Maybe there's lots of fuel costs.

I think that's a pretty big one.

Maybe that's why February is so cheap.

Has the lowest expenditure.

Everyone spends too much in January, they spend less in February.

I'm making this up, but at least now I've got some data to back that up with. Yeah.

Which category saw no expenditure in a particular month? That's that blank cell that...

Let me move that across. In December, we had no healthcare spending.

Interesting.

December healthcare.

What do you notice about the highs and lows in the dining out category? Let's see what we see. Dining out, we have some highs with January, February, March. Yeah, kind of high.

April's a bit low. Oh, May is very high. June is very high.

August is very high.

Ah, summer seems to be quite high, apart from July.

That's a bit random.

So summer high, apart from July.

Do we have any other highs? Oh, December as well.

So maybe it's holiday months. Maybe people dine out in December, Christmas, holidays. Maybe it's something to do with that.

Summer high apart from July and December. So I could put holidays, question mark.

And it's something for me to go and find out a bit more on.

Maybe I could give some advice to people who are dining out, or maybe some retailers and restaurateurs about how to make more money, when to spend on advertising, that kind of thing.

Last one, which category has the highest in- July. So I'm going to use max, select those July numbers. Although it's pretty obvious, it's transport.

Transport in July.

Feels like people going on holiday.

So spending lots of money on holiday or maybe going home from university or whatever it may be. It's a bit weird, though, that dining out is so low.

So if people are going on holiday, you think dining out would be quite high.

Okay.

Guys, any questions for me? Any questions, jump on that chat if anything's not working for you, but you're starting to see how pivot tables can be useful.

Okay.

If any questions come up, I will keep an eye on them.

But let's carry on to workout three.

Workout three says, "Copy the results from workout two and paste below." So let's give you a little shortcut to do that.

You could just obviously select, yeah. You could Control + Shift and do that.

But a handy way to do it with your mouse is if you go to the top left, get that little arrow, and just click. So as you see, when I go to the top left-hand corner, and just as I click into it, I get the black arrow.

That selects the whole table.

So I'm going to copy.

For copy, I'll hit Control + C.

And then to paste, come down here, Control + V.

So I'd like to change the order of some of these things.

So that's sorting. It says, "Sort the results from Z to A." Interesting.

That is in this drop-down arrow here.

So row labels, click on that.

And at the top, sort A to Z.

That's not what we want. Sort Z to A, that's what we do.

So click on that.

Brilliant. Utilities are at the top, dining out at the bottom.

Now it says, "Filter the results." So we've done sorting already, tick, but now we need to filter the results to show dining out and education categories. Oh, wow. Hang on.

In January and May only.

Let's see what we find.

So I'm going to go to the same drop-down, and I can select the categories I want.

Now, the first thing I'm going to do is turn off select all.

So now I can click on dining out. Where's dining out gone? There it is, and education.

Brilliant. But what about the months January and May? Let's go find them. Press Okay.

So just dining out and education's here then.

I need just January and May. I need this drop-down next to the column labels.

So I click on that.

Turn off select all, and then choose January and May.

So what do we notice? There's a question here.

What do you notice about these months? In January, it's all about education.

Everyone is spending loads and loads and loads on education in January.

New year, New Year's resolutions.

Maybe people are coming up towards exams, so they need to go on training courses like this one.

But by May, very different.

By May, maybe people are actually doing their exams.

They don't need to spend money on education anymore.

All that expenditure's already happened.

So we can definitely see some seasonality in education.

What do we notice about dining out? Well, as the year goes on, people spend more on dining out.

And it would appear that when education spending decreases, dining out spending increases a little bit. I can see a trend there.

Cool. So guys, that's sorting and filtering, okay? Just hit those drop-downs. Fantastic.

One thing to point out, if you want to undo all of your filters, you want to remove them all, this little button here, Clear filter from category. Okay.

I'll keep an eye on the chat. If you have any questions, do stick them in there.

Otherwise, I'm going to carry on down into workout four.

So workout four says, "Copy the results from workout two and paste below." Let's scroll ourselves back up. So scroll up. Scroll up.

Oh, what was that shortcut again? It was use the arrow.

Use your mouse to create the arrow in the top left, click, and then we can copy, Control + C.

Let's put it into C71.

I need to paste.

I'll do that in C71. Control + V to paste. Great.

Now, you might notice some of these numbers aren't showing.

Now, that's not particularly a pivot table thing, it's just that the number is too big for the columns.

So what I'm going to do is I'm going to select all of these columns, okay? And I'm just going to take column N, I'm just going to make it a little bit wider, and they all go wider. Great.

So I can see my numbers. Let's have a read.

"Your boss wants to understand expenditure by category." So luckily, that's what we've been doing.

But wants the ability to shift the months included easily. Hmm.

So we're going to use a timeline to do this.

And it actually says, can we do it for just the quarter from April year '23 to June year '23, i.e., quarter two.

Timelines are really handy. They're really handy because at the moment, if I wanted to just see that, I'd have to go up and do some filtering.

Filtering is great. Nothing wrong with filtering at all.

But timelines allow you to do it easily.

So let's go do a timeline.

Guys, up into the ribbon. Oh, make sure you just click on the pivot table first, excuse me.

And if we go up into the ribbon, there's this Pivot Table Analyze ribbon available to us.

Click on that.

Now, in there, in the Filter section, we've got something called a timeline or Insert Timeline.

I want to click on that.

Now, my timeline here is pretty simple.

It's going to be done on date.

So we tick the date and press OK.

And this weird box appears. I'm going to put it underneath just so I can see what I'm doing. So just pick it up by the top, drag it around to somewhere sensible.

Now, let me zoom in on it a bit better so we can see what's happening.

And you guys might notice, what we've got is this scroll bar at the bottom, which shows us all of the months that are being included.

I'd like to change it from months. I'd like to change it to quarters because we were asked to do this for quarter two.

The timeline provides us with a quick way to do that.

In the top right, I've got months.

I'm going to click on that.

I'm going to change it to quarters.

Ah, little bit easier for me to see all of them now.

So which quarters did we want to look at? It was April '23 to June '23, i.e., quarter two of '23.

So if you go to the end of the timeline, you can move the quarters that are included.

And if I go to the beginning, I can move that as well, and watch what happens.

Ah, just April, May and June are included.

So handy.

Okay. And you can even move it around at will, and the pivot table above is automatically flexing as we move it around. So handy.

Really good if you're doing dashboards, really good if you're doing this for someone who's not quite as familiar with pivot tables and they just want something to play with.

Got a question from Juan. "Is the timeline able to convert months to quarters, or the data also reports quarters on top of just months?" We don't have that data in here. If we have a look at data set A, we've just got months, we've got dates, but we didn't report whether it was quarter one, quarter two or quarter three. So the timeline has done that itself. Really nice, eh? Really, really nice.

Thanks for your question, Juan. Great question.

So timelines, really good if you want a dashboard, and particularly good for, I'm gonna say for beginners who want something to play around with, but they're a bit nervous about going into filters and working with great big lists like this.

Okay.

Guys, any questions for me? I'm gonna give five seconds of silence.

Gives you guys a chance to jump in the chat, ask the question. Let me give you five seconds of silence.

More questions. Okay. "Are there any sweet spots for how many categories you can include on columns and rows?" No. No, not at all.

Personally, I always like to be able to understand everything in my head.

So if you said to me, "I've got a million lines of data, and that reduces to 100,000 categories," I can't hold that information in my head. So what I would like to see on top of that, a million line items, 100,000 categories, but of those categories, they reduce down to 1,000 sub-categories and 100 categories. Do you understand what I mean? So if you can get it into less and less and less categories, I think that helps the human head understand what they're looking at. Okay.

"What if you want to look at different quarters? Like, not only quarter two, but also quarter four on that same table." Ah, little bit harder, unfortunately.

It's only giving us between X and Y.

In which case, you'd have to go up to your filters instead, choose one, two, three, but then, so quarter two and then quarter four. That's how-- oh, but I have to turn off my timeline as well if that's gonna work. Oh.

We have found, unfortunately, when you do a timeline, you can't then jump back to the original. So let me just see if I can turn that off.

Here it is. So if you hit the top right-hand corner to turn off your timeline filter, it's now given me April, May, June, October, November, December.

Juan, thanks for your question. Justin, thanks for your question as well.

Cool.

Guys, great questions.

Let's carry on Workout five, using the data on the data sheet, create a pivot table.

Let's go do that bit first, shall we? Let's just create the start pivot table.

So C97.

Okay. We go to the insert ribbon, left-hand side, pivot table drop-down, from table range.

Table range, I hit the up arrow, go to data set A.

I always like to go to the bottom right-hand corner. It's just me.

Control down arrow, and then to select the row, control and shift left arrow.

To select up, control and shift and up arrow. Great.

And then I always like to go to the top because I can just make sure that I've selected my headings.

Press enter.

Okay.

We've started it.

Let's have a read.

Aid with household budgeting. Great.

You wish to compare spending habits across different households and categories. Nice.

Before starting, try to work out which items should go in rows, columns, and values. So if you guys were in a physical classroom, or even a classroom where I could move you into breakout rooms, I would. Don't matter. Unfortunately, can't.

So what am I going to have to do here? Well, I know it's household budgeting, so I want to look at the various different households. Okay.

Now, it says budgeting, so that means their expenditure, so that's going to be the second thing we're going to do.

And we want to work out their spending patterns.

So it says across households and categories.

That's my third thing we're going to have here.

So how could we do this? Certainly, your value or values is going to be expenditure.

But are we going to have categories in rows, or are we going to have households in rows? Up till now, we have had categories in rows.

It really doesn't matter which way around you do it.

I'd like to change it just to show you guys that we can change it, and we're not stuck and we're inability to flex.

So let's flip it around. I'd like to have households in rows.

That means we'll have categories in columns.

But it doesn't actually matter.

So let's click in.

Pivot Table Fields appears on the right-hand side.

My values is going to be expenditure, so I click the expenditure, drag it into values.

My households, we said that they were going to be our rows. Click back into this.

So households go into rows. I'll show you something that's gone wrong there, and we can fix it.

And categories go into columns.

Nice.

Now, you might have noticed something that's just a little bit annoying. It's not the end of the world, but is a bit annoying.

It's this household 10.

At the moment, the order is not correct. It's not sorting to what I would like to see.

I'd like to see 10 at the bottom.

Luckily, you can really quickly fix that with your mouse.

If I click on, ooh, household 10, you might notice that black arrow appeared again, and it selected the row.

And watch what happens. If I have the black arrow showing, and I click and I drag and I hold and I dr-- Oh, hang on. Didn't quite work there.

Oh, what am I doing wrong? Why won't it work? There we go. Guys, apologies. You don't want the black arrow. You want to go to the right-hand side a bit more.

Just click on household 10, but not the numbers to the right.

And what you can then do, just go to the bottom of that cell, where you get your... Oh, I can't get it to work now.

Where you get your crosshair, and then you just click and then just drag that down, and you see how it's got a line going across.

Drag it underneath household nine. Perfect.

Excuse me, just one second.

Okay, cool. So we've worked out how to fix that slight annoyance with the sorting.

It then says which household spends the least in total, and for that same household, which category is their lowest expenditure? Well, we could use some min functions to help us out here.

If we go to the right-hand side.

Okay.

Look at our grand totals. I'm going to use a little min function on the grand totals. Let's find the min.

4551.

4551. Household two.

Household two, they're spending the least.

In fact, I should have moved this a bit further down, shouldn't I? Got loads of room underneath it.

Household two spending the least, and then which is the lowest category for them? Well, that means I need to go to household two and do another min function.

And I'll do the min across their category numbers, but not the grand total.

Their min expenditure was 155.3 dining out.

Household two, they're being very restrained.

Maybe they're doing more cooking at home.

You could have a look at their groceries. Ah, their groceries.

Yeah. So the groceries certainly a lot higher than their dining out number. If you look at some of the other households, their dining out is much higher than their groceries.

So household two, they seem to prefer groceries at home.

Cool. So that was workout five. Okay.

And it enabled us to see that we could have our categories in rows, households in columns, and we could reorder them.

Let's have a go at workout six.

Workout six, copy the pivot table from the previous workout and use slices, slices are awesome, as alternatives to filters.

So let's go back up, top left-hand corner. Let's get that black arrow to appear.

I select the whole table, click copy, Control + C, paste, Control + V.

It now says, "Add slices for the following items: category, household, and month." Okay.

So how do I find slices? We find them where we found the timelines.

Let's click in the pivot table, and once we click in, we get that pivot table analyze up in the ribbon. So click on pivot table analyze.

And just to show you where the timeline was, it was in this filter section.

We now want to do slices. Let's click on that.

Now I'll explain what slices are. Slices are really cool.

It basically enables you to filter, but just by clicking buttons, and everyone can see them all the time.

So we want three. We want category, category, household, household, and month. So let's click those three and press okay.

And here they are. Now I'm just going to drop them underneath so we can see all three of them.

There we go.

Now really quickly, it said that category should be blue and month should be orange, and household should be green.

So if I just click on... Which one? Let's do category first.

Click on the category slicer, and if you go up to the slicer ribbon, I can then just choose this slicer style blue, and that's a bit nicer.

And household was green.

Household, slicer, green.

And last one was month, orange. So month, slicer, orange.

More of a peach, isn't it? Well, we'll go with peach.

Okay.

"So what the hell are slices, Gerard? Gerard, that was like the longest ever setup, but tell us what a slicer is." Okay. Slicers enable you to filter, but without having to go into these kind of hidden away filters here, that people either don't know about or they get a bit scared about.

People are scared of hidden things like that.

But when you see very pretty slices like this, it makes it a little less intimidating.

So for instance, let's do category, dining out, and groceries.

If we go to the category, let's just click on dining out to start with.

Oh, and the pivot table reduces. Really nice.

What if I try to do groceries as well? If I just click on groceries. Let's try it now. Let's click on groceries.

Ah.

Dining out disappears.

So how do you get two or more at the same time? Press and hold control and then click on another item, and now I've got dining out and groceries, and they both appear in the table.

Yes.

Guys, let's try some of the others. So household one and two.

So I'll click on two. Let's go find household one. There it is.

One and two, very nice.

One and two, dining out and groceries.

And now month, just January and February.

Okay, so January, press and hold control, February, and there you got it, household one and two. Dining out.

Household two didn't do any dining out in January or February.

And its groceries, very low as well.

So we're starting to understand a little bit more about each household.

Slices.

Little bit easier, little bit less intimidating for people who want to start filtering.

Guys, you're really good at asking questions.

I'm going to give another five seconds of silence.

Jump on the chat if you've got a question for me.

This will give you just a little chance to have a think about it. So five seconds.

Bye.

I'll keep an eye on the chat, see if anything comes in.

So let's keep going down. Workout seven.

Using the data on data A sheet, create a pivot...

Okay, so we're going to have to do the same thing again.

Guys, let's click into C162.

Let's do it again.

So insert ribbon Pivot Table, From Table Range.

Table range, click the up arrow. Data set A.

Go to the bottom. Control + Shift + left, Control + Shift + up, Enter.

Let's give it a second to make sure you guys are with me.

You're probably miles ahead of me by now.

Okay.

So it now says, oh, a new thing.

It says, "Show the average expenditure for each category." Hmm.

How do we do that? Okay. Well, let's start off by getting our categories.

So expenditure and category. They're the two things we need to.

Let's go do them. So category, drag it to Rows.

Expenditure, drag it into Values.

I've got my basic pivot table set up.

But you might notice at the bottom right-hand corner, let's go have a look.

It says Values, Sum of Expenditure, and that's the default.

Okay. Whenever it sees expenditure or numbers or anything, it always defaults to sum them up.

But in this one, we're looking for the average, so we need to go and find the average.

Couple of places you can find it.

First place, the hidden away place.

If you go to the bottom right-hand corner, there's this little drop-down, and it's in Value Field Settings. Little bit difficult to kind of guess that that's where Average would be, and you could change it to Average. Don't do it this time.

But if you clicked Average, it would then change it. Don't do it this time.

Instead, what we could do, instead of going to the bottom right-hand corner, is we could go up to the Pivot Table Analyze, and it's very similar.

Top left here, it's... Ah, sorry. Need to make sure I'm clicking into Numbers before I do it.

So make sure you've clicked into one of the numbers, and in the Pivot Table Analyze ribbon, in this Active Field, it tells you what's happening, and it says it's the Sum of Expenditure.

Now, just underneath that is Field Settings.

And if I just show you again, here it was Value Field Settings. Here it's Field Settings.

It's the same thing, just got to via two different places.

Let's change it to Average this time. Up, Okay.

And the average expenditure gives us different numbers.

Really handy.

So which category has the highest average expenditure? I'm gonna use a little max function.

Excuse me. Little max function for that.

Select all the numbers.

111.1. That was grocery.

Groceries had the highest average.

Okay.

"But Gerard, I don't just want the average.

I want to see the average and the total.

I want to see them next to each other." And that's what it says here.

It says, "Then add a separate column for the sum next to the column for average." Oh.

So how do I do that? Well, luckily, this one makes much more sense, rather than kind of being hidden away in a little drop-down that you'd never guess to click on.

To get another column with the sum of expenditure, what you do is you go to Expenditure here and you drag it into Values again. And watch what happens.

It defaults to the sum of Expenditure, and you get another column.

Love pivot tables. Love the pivot tables.

Jamal asked a really good question: "Is there a way to pull the max category instead of the amount?" Totally get what you're saying there.

So we've got the 111, but I'd really like the word Groceries to appear. So what you could do is you could use some kind of data extraction function, such as a lookup or an index match, offset.

So I'm gonna use an X lookup. If you're not familiar with X lookups or you've got no interest in X lookups, cover your ears for 20 seconds.

So I'm gonna do an X lookup.

So this asks me what do you want to look up.

I want to look up 111.1.

I'm gonna press a comma. Depending where you are in the world, you might need to do a semicolon. Okay. If your language requires semicolon, you do semicolon.

And the next thing it asks me is for the lookup array.

It's asking me where will the 111 be found.

I'm gonna get it to look in those items there.

Okay.

Comma.

But then it says, "Okay.

Oh, okay, I found that one. It's in the one, two, three, fourth.

It's in the fourth one down. Gerard, I found it in the fourth one down.

Number four." But I don't want it to return that.

I want it to return the fourth one down in the return array, which is these items here.

We won't bother with the if not found, et cetera.

And it returns the word groceries.

Jamal, great question. Love that question.

Love a bit of Excel. We love a bit of Excel.

Okay, cool. Great questions, guys. Let's keep going. We're almost out of time.

We're not going to do all of them. We're going to do almost all of them.

Now, workout eight, it says recreate the same pivot table.

Let's not recreate it. Let's just do it in workout seven. Let's be a bit quicker.

I'd like to turn off sum of expenditure. Okay? So I'm going to go down to the bottom right-hand corner, sum of expenditure, I'm going to pick it up and put it back into this top item here.

You might notice it gets a cross, a red cross on it.

That means it's going to be excluded.

So we get rid of the sum of expenditure.

So what I'd like to do now is I'd like to get a chart.

Your boss has asked for a column chart to help visualize the average expenditure per transaction.

Brilliant.

So guys, to do that, we select a cell in the pivot table.

We then go up to the pivot table analyze ribbon, and in the tools section underneath, we've got pivot chart. It's just sitting there ready to go.

If we click on it, it comes up with all the different charts we could have, but it's already defaulted to a column chart.

Luckily, that's exactly what it said here.

"Your boss has asked for a column chart." So I can just press Okay.

Amazing.

That looks really good.

If I want to look at just some categories, not all of them, in the bottom left-hand corner, this is very handy drop-down.

Maybe I just want to look at dining out, education, entertainment.

And it very quickly changes the chart.

So good. Excel is so amazing. So that little drop down in the bottom left-hand corner, there, it just automatically updates.

So good.

Great. That was workout eight.

I'm aware, I can feel myself speeding up because we're running out of time.

So I'm going to slow myself down for a second, and I'm going to give you guys five seconds of silence. Is there a question that anyone would like to ask me? Okay. I'll keep an eye on the chat in case anyone's still typing.

I'm going to skip workout nine. It's not the greatest one.

Workout nine involves you hard coding numbers. I don't like hard coding numbers.

So let's go straight to workout 10.

Let's have a read.

"Your boss would like you to understand category expenditure better.

She's asked if you can create a pivot table that shows category expenditure and also show it as a percentage of total." Ugh. What are we going to do? So I need my total, and I need my category as a percentage of that, like 50%, 20%.

So two separate columns.

Okay.

So let's start. We'll start in C239.

As usual, let's create our pivot table.

So Insert, Pivot Table drop-down.

From Table/Range, up arrow, Data Set. You guys know how to do it now.

Give you a few seconds just in case I was too fast for you.

Now, the two items we need, we need category, and we need expenditure.

So let's get those going on. I'm going to have category in my row.

So put category in row, please, and then expenditure in values.

So it sums them up.

The first column, I'm happy. I've got my sum of expenditure.

I'm very happy with that. Column number one, great.

But what about percentage of? I want to show it as a percentage.

So what I want you guys to do is I want you to click on one of the numbers, and then right-click that.

And just kind of halfway down, it says Show Values As.

And there are loads of pre-built things here that you can drop in.

I'd like to show it as percentage of column total.

Let's tick that one.

Fantastic. I get my percentages. They all add up to 100%.

That's amazing.

But oh, no, we lost our dollar column.

And the boss said to me explicitly it should be in two separate columns.

Hmm. So that means I need my sum of expenditure again.

But we know how to do that.

To get our sum of expenditure again, we go to expenditure, and we drag it again down to values. So drag, drag, drag, drag down to values.

Pivot Tables. Oh, Pivot Tables are here to save the day. Love them.

Okay.

That was Workout 10.

Final minutes. The final minutes are available, and we've still got Workout 11 to do. It says, "Using the data on the DataA sheet, create a Pivot Table." Let's go do that. So C262, Insert, Pivot Table, From Table/Range, up arrow, select DataA.

Give you guys a quick 10 seconds to catch up in case I went a bit too quick.

Okay. Let's have a quick read.

"Using the data on the DataA sheet, create a Pivot Table.

You want to see category, expenditure, household per month." Quite a lot of things there. It's got four items going on.

"Your boss wants you to change the table design." So formatting. So let's do those four underlined things first.

Then we'll do the formatting.

So we want to see category.

I'm going to put category in-- Let's put it in columns this time.

Let's do things a bit different. Category in the Columns, Expenditure in the Values.

And what were the other two? Household and Month.

Let's put them both to Rows.

So we've got a really large table this time, but it's going to show us loads of good stuff.

So just make sure your four items match my four.

Now, the slight problem we've got is that because there's so much information, formatting, at the moment, doesn't look great.

We'd like to do a few changes to make it a little easier to understand, and the boss has given us explicit items.

The boss has said, "Do not show subtotals." So how do we do all this? We're going to go up to the right. Make sure you click into the Pivot Table somewhere.

And we're going to go to the top right, Design ribbon, and all the things we want are along the top here.

So first of all, do not show subtotals.

There's a Subtotals button, and we change it to Do not show.

Just have a quick look at that Household1 number there, that big Household1 number. If I click it off, that disappears. Okay, nice.

The boss says, "Do not show grand totals." So we go to the Design ribbon, and there's a Grand Totals button.

Off for Rows and Columns.

Nice.

Next one. "For report layout, choose between compact and outline." Report Layout, Outline expands everything out.

Compact squishes it down again.

Next one. "For blank rows, insert a blank line after each item." Blank Rows, Insert blank line.

Now, personally, I really like this one.

Watch what happens to the table when we click on this one.

Just gives us that extra blank line and shows us Household1 a bit more clearly.

Cool. Last one, then. "Select banded columns." Depends if you like this one. If I click on Banded Columns, some people love that, some people hate it. Banded Rows, some people love it, some people hate it.

So it's entirely up to you.

And then lastly, "Change the Pivot Table style to a medium color." So let's go with a medium, medium, medium red.

Cool.

And there we go.

Guys, we went one minute over the hour.

That is pretty good. I'm pretty happy with that.

Guys, I hope you found that useful.

Just as a quick reminder, we always have more Felix Lives happening. Every single Friday, we run them three times.

Here, we've got everything coming up.

Introduction to M&A Process next week, 3 Variable Data Tables after that, et cetera. But of course, you've always got these Learn items that you can be looking in. You've got Pathways if you're wondering, "Oh, I don't even know where to start." If you want to get into investment banking, click on Investment Banking. Maybe you start with the Intern column, and you get all the different playlists for intern accounting or analysts. I know AI, everyone's watching the AI stuff at the moment.

Okay, some really good stuff here.

If you've got Rogo, intro to Rogo, that's really good.

If you prefer Claude or Endex or Shortcuts or loads of good stuff.

Guys, enjoy.

Guys, I hope you found that useful. Hope to see you on another Felix Live soon.

Rogo is another AI. Rogo is just another AI. It's used by a couple of banks.

See you, guys. Bye-bye.