Okay, guys, welcome along.

It's fantastic to have you virtually in the room. Thanks so much for joining. My name is Jonathan.

We're gonna be talking about pivot tables today.

So hopefully you can hear me. Hopefully you can see me and hopefully you can see my screen share.

So my colleague Agata has just posted a link into the chat.

So if you wanna jump onto the chat, that will give you a link whereby you can download the file that we're gonna use today.

So the file is called database analysis empty, and if you look at the very kind of bottom right of my screen share, you should be able to see that.

So if you wanna grab the empty version, that would be fantastic.

We also have a full version.

We're gonna, not gonna download that now, but after the session, if you wanted to go and have a look at a completed version of the file, then you can, you can grab that.

So if you wanna click on that, that should download.

And I have that open already, so it should open on a file that looks like this.

What I'll highlight to you is actually there's a bunch of other content in the file.

And so this isn't restricted to pivot tables, so there's more stuff you could look at and work through if you wanted to.

And the full file, the answer file has all the solutions, for the other sections as well that we're, that were falling outside the scope of pivot tables.

Okay. So, we're gonna start now.

I see that there potentially are a few other people joining as I'm talking.

So if a load of extra people join, then I might just slightly repeat myself.

And just give a very brief introduction.

So if you've just listened to me, do that then apologies and thanks for your patience listening to me. Maybe given a bridge version of that.

Again, I should probably tell you that the session is also being recorded.

If you've got any questions, please feel free to ask questions.

It would be fantastic. Download the file.

So let's just go back, if you look at my screen share, if you can download the file, bottom right, and then we can just mess around with some of the numbers together, that would be fantastic.

Okay, I'm gonna go back to the file.

We're gonna endeavor to use keyboard shortcuts.

Where, and there are some instances, certainly with pivot tables, where it's useful for us to use the mouse or the track pad.

But what I'd love to do is to take you guys to a sheet called pivot data.

And so you can use your mouse to click on that sheet, but ideally you'd hold down control and tap page down, hold down control and tap page down a few times to get to that pivot data sheet.

And there's a load of data in here and in its own right, it isn't terribly remarkable.

We have some transactions, transaction numbers, we've got some names of, I guess they're probably salespeople.

We've got some dates, we've got some products sold, and the number of units that were sold, I guess within that transaction, the sales amount within that transaction and the location.

And what I wanna do is, I mean, I can stare at that data and there's, there's loads of it.

So if I go into column A and I hold down control and tap page down, there's like 1,000, well 1,900, sorry 1,900 transaction numbers apparently.

And I wanna get into a format that is just more, more usable or more useful.

So we're gonna use a pivot table now.

We'll use kit shortcuts, and the shortcut I'm gonna use is I'm gonna scribble it to the side ALT N V.

Okay. So the shortcut I'm interested in is ALT N V.

I'm quite purposefully gonna go and place my active cell somewhere. It doesn't really matter where, but somewhere within the data that we're interested in.

Now, we don't have to do that.

But if we place our cursor or active cell within the data, when we say to Excel, Hey, I'd love to create a pivot table, it'll say, aha, you work in the middle of some data, I'm gonna expand, and I, I guess I'm gonna use this data.

So it just kind of saves us having to select the data.

We lose that shortcut. So if I tap alt, that brings up the hot keys or accelerator keys in Excel.

So all the hotkeys for the ribbons.

And if you think about pivot, you know where a pivot table might be or might sit. You've got the home ribbon, the insert ribbon, I've got the draw ribbon, et cetera.

Actually, we want the insert ribbon.

We're gonna tap N and you can use the mouse to do this, of course.

I'll just wave my mouse around because we've got a sea of shortcuts. Now, if you check over on the very far left, you've got pivot table.

It has got little down arrows. It's gonna open up a little dialogue box, a little kind of further contextual menu.

If we do that shortcuts over to the side here or, alt n and v and says, Hey Jonathan, you wanna create a pivot table? Do you wanna do that from a table or a range? Or do you want some external data? I'm not pulling in external data here.

I'm gonna tap T for range.

Or you can just hit enter, okay, if you want to, because that's automatically selected.

So shortcut that alt n v, just tap enter and the Excel says, ah, right.

So I reckon, and you can see we've got this dotted line running around the data.

Jonathan, I'm gonna expand.

I reckon this is the data you want. Are you good with that? And, and absolutely.

I'm, so really all, all the time I do this, I just click, okay.

I don't really find any occasion to change Excel selection, but you can do that if you want to.

So you can change the selection. It says here, right? You wanna create a pivot table.

Where do you want that to go? I'm not a big fan of putting it on the existing worksheet.

Because if we want to kind of add things and change things around, etcetera, it's kind of usually a bit messy if we do that alongside our data.

And so much bigger fan of doing that on a new worksheet. And that's excel's default setting.

So I'm just gonna hit enter or just click okay.

And nothing very remarkable happens.

But, uh, Excel will create new sheet.

You can kind of kind of see that there.

So there's a sheet one it's created, which you can subsequently name if you wanna, and it says pivot table one, but it hasn't really done anything we're do to get this dialogue box on the right hand side.

And you'll notice that the different headings here, transaction number, name, date, product unit, sales amount and location, they very much correspond to the headings that we had in our original data.

And then we've got columns, we've got rows, we've got values, and we've got filters.

Now I'm gonna use the mouse here.

I don't use use the mouse, but it's intuitive to, to do.

So what I'm gonna do is I'm gonna go and grab name.

I'm gonna double click on that and sort of drag that down.

And I'm gonna chuck name into rows, okay? And you can see what Excel has done is along the rows. You've got those names.

Okay, that sounds great, but it's not very interesting because Excel's not really showing me any data, it's just showing me some headings.

So I'm gonna grab the sales amount.

I'm just gonna like drag that into values.

And we've created a really, really simple pivot table.

It looks like we've got a summary of the different sales people, and the sum says there's sum of the sales amount. You might notice that in values, there's a dropdown here and it says sum of the sales amount.

You can click that and value field settings and you can kind of change various things. But we're not gonna do any of that, necessarily right now.

I'm quite happy with this. But actually, you know what, I might not like the number format.

So I can go to select those cells and I could say, right, let's change the number format.

Now, what's the classic way that we might change the number format in Excel? Well, we might say something like, um, h for home and J cell style.

So Alt h and j is the kind of classic way that I would change, the format of something.

And I don't think that's necessarily a very good approach if you are using a pivot table, because if you end up kind of in increasing the number of rows or the number of columns, that doesn't really flow very nicely and consistently.

So rather than do that, what I'm gonna do is where it says sum of sale amount, click the the down arrow value field settings and number format, I'll do that again.

So click the down arrow sum of sale amount, value field settings, and number format.

And it's kind of here that you could say, oh, I want it to them all to look like percentages, which maybe doesn't make much sense.

Or, I want to use currency for example.

So I want, pounds or I want dollars or various other currency values in front of these.

So if you set up the number format in that way, when you mess around with the data and you end up kind of expanding or contracting the number of rows and columns in the pivot table, then that number format will kind of flow to all those extra lines.

So it's, it's definitely a better way of doing that.

I'm just gonna escape that because I actually, I really don't wanna change the number format particularly at the moment, quite happy with the setup.

What about formatting text settings? Well, if you, maybe if I place my active cell just kind of, you can see I'm moving it around somewhere outta the data table.

Now I want you to have a look at my ribbons along the top.

And if I go left into the data table, suddenly we get a couple of contextual ribbons, we get pivot table, table analyze, and we get design as well.

So what you can do if you click on click on design and you can change the format for the pivot table, and it's probable well, no, maybe it's possible that your organization would have some kind of predetermined corporate branding maybe that you would wanna apply certainly at Financial Edge.

We do. And so you may wanna select that if that isn't the case, I would recommend maybe you select a style that is the closest to your corporate branding.

If you right click on it and you go for duplicate, and you could call it like custom under sort one or something having duplicated it, it'll create a new style and you go change stuff.

So for example, the, I dunno, the the first row stripe you can click format and fill and you could change that to, whatever your kind of corporate color is.

It might be green, for example.

And your first maybe your header row for example, you could click format and you could change that to something really garish and horrible. Like red for example.

You can see a kind of impression of what you're gonna arrive at and then click okay.

And then if you said design and you click the the down arrow there, then you've got custom and you can go and select that if you want to.

So in terms of really simple pivot table, that's kind of how we might change the, the formatting and change the way that it looks.

There are other options that we might look at.

So for example if we go on to analyze, say, pivot table, analyze, perhaps I don't sort of like the grand total there.

So on the very bottom left, of the ribbon, you've got options and I can click on options and click on options again.

And, there's a couple of things that I want to change.

I'm actually probably only gonna change one of them at the moment, and we'll, it'll cause the problem.

And we'll as we'll do some more work on this, we'll kind of double back and realize this is an issue.

So for this pivot table, maybe like totals and filters, show grand totals for columns and rows.

I'm gonna deselect that, for no reason other, other than just demonstrate it really, but perhaps I don't want that.

If I go back to layout and format, it says Autofit column widths on update.

Now that sounds like a really fantastic option to have selected.

So if we change maybe some of the data, the thing that we're looking at, then the columns will automatically resize, but we are gonna have an issue there.

Because sometimes we don't want it to be kind of automatically resizing because other data that might sit below or, you know, around that pivot table might kind of get obscured if the column becomes too small.

I'm gonna leave that selected at the moment, so that's probably a bit suboptimal for me.

Let's click okay.

And as I click okay, you see that grand total has disappeared.

Now what I would like to do is maybe to look at perhaps not just the, some of the sales amount, but I might not wanna look at other things like the number of transactions, for example, that each individual has undertaken.

So you could do that on the single pivot table, but it does tend to get a bit confusing.

So what I would tend to do really, really simple, grab the select the pivot table, and I'm gonna copy it, CTRL C and I'm gonna go below a little bit and leave a bit of space and say Control V.

So it's kind of nice copy and paste, pivot table, any sort of formatting that you put in place automatically gets carried over.

Now, if I click in each of these pivot tables, or if I just move my active sale up and down to each of these pivot tables, they just look the same, right? So there's nothing kind of like too radical going on there.

If I maybe go down to the second pivot table, I might say, do you know what? I don't wanna look at the sum of the sales amount.

So I'm in the second pivot table in the values field.

I'm gonna just drag that using the left mouse button away.

Okay? So that's been removed from the pivot table, and perhaps I'm gonna go for the transaction number.

So I'm gonna drag down the transaction number there.

Now, when I drag that down, by default it says, Hey Jonathan, you want the sum of the transaction number? I dunno if that really makes sense.

You know, if you've got it, like transaction one, transaction two, transaction three, why would you really wanna sum those that really makes sense at all? I think maybe counting them would be a better thing to do.

So what I'm gonna do is click the sum of transaction number dropdown, and I'm gonna go to value field settings.

So click on the sum of sales down, click on the value field settings, and then rather than go for some, I'm gonna go for count.

And if we click okay, then we've got something much more sensible. So I can see that, for example, Ashley's total sales amount was 25947.2, and the number of transactions that Ashley was involved in in that period was 192 transactions, which is kind of a useful thing, to have at our fingertips.

Let's keep going.

Let's have a think about filtering data and slices.

So there are two different things that achieve similar results, but work in slightly different ways.

So we're gonna look at filters and slicers.

So if I go onto the first pivot table, so I'm gonna click on the first pivot table, and I'm gonna remind you that we've got this filters field here, which we haven't used for anything.

Now, if I go back to the pivot data, we've got these transactions, these individuals and they're based on various locations.

So I think I'm gonna go back to my pivot table and I'm gonna go and grab location, and I'm gonna drop that into the filter.

Now I've done that.

You may have noticed that at the top of the pivot table we've got this new element that's appeared.

So we've got location, it says all, if I click on the dropdown, I could select for example, Midwest and click okay.

And now it's only showing the, some of the sales to the Midwest.

You might notice that it's had absolutely no impact whatsoever on the second pivot table.

So when you are applying filters, and if I click into the second pivot table, check out the filters field, there's nothing in there.

The first pivot table, check out the filters field, there's a location in there.

So when you're using filters, they're specific to a particular pivot table.

And that can be quite a useful thing, you know, to just always maintain something separate.

So I could go for location again, I could drop that into the filters and it brings a new element here.

I'm gonna click on the dropdown and maybe go for West or something like that and click okay.

Now it shows me the number of transactions for West.

So that's how filters work.

What I'd quite like to do is look at slices.

So I'm gonna go into each of these pivot tables.

I'm just gonna drag the filters out of the filters, um, out of the respective field.

So we've removed that.

Now I'm gonna click on, it doesn't really matter, I'm gonna click on the first pivot table.

And remember, we've got these contextual menus that appear when we're clicking on these, respective pivot tables.

What I'm gonna do is I'm gonna click on pivot table, analyze, and can you see here it says insert slicer, use a slicer, still filtering to filter data visually, slicers, make it faster and easier to filter tables, pivot tables, pivot charts, et cetera.

So I'm gonna click on that.

I'm gonna click on insert Slicer. Okay? So it says, Hey Jonathan.

So if we're gonna create a slicer, what do you wanna slice the data on? Or really, what do you wanna filter on? Let's go for location, because that's what you've done previously.

So you can click okay, now it shows you something much more visual.

I can resize this if I want to and I can move it around as well.

So I can kind of drag it around.

As with many things in Excel, if you're dragging it around and resizing it and you wanna snap it to the grid, you can hold down alt.

So if you hold your left mouse button down to drag the object around and you hold down the left alt button at the same time, then you can resize and move it and it'll snap it to grid. And that can be quite a useful thing.

Now, I've created that slicer.

I can click on a region before we'd use Midwest.

So I can click on Midwest or South or West, and you can see the data changing as I do. So if you hold down control then, and you left click, then you can select multiple options.

So if you just left clicking, it selects a single option, you hold down control and left click, you can select multiple options, and then you can clear those if you want to.

So it selects everything.

Now by default, as you've seen, as we, the filtering that we looked at previously, this is only really working on that first table, but it would be kind of a core thing if we could get it to apply to all pivot tables.

If I, and this is quite a useful function on slices, if I right click somewhere on the slicer, and there's a section here that says Report Connections.

If I click on report connections, and then it says, okay, well I'm just connected to the first pivot table because that was what you were selected when you inserted the slicer.

Do you want me to apply these filters to other pivot tables? Absolutely, I do. I'm gonna click on pivot table two and click on.

Okay. And now if I select, say Midwest and you can see the numbers, then it's gonna change, both the pivot tables, which is kind of useful.

Okay, what else can, we do, what else can we look at? Well, I'm gonna get rid of the, I'm gonna right click and I'm gonna get rid of the slicer. In fact, I can just delete, delete the slicer, delete to get rid of that.

The other thing I think I'd quite like to do, if I click on maybe select the pivot table here, I'm gonna delete that pivot table.

Click on the first pivot table.

So bring up this kind of contextual menu.

At the moment down the side, I've got the name, but perhaps I don't wanna look at the name.

So I'm gonna get rid of name in rows and I'm gonna go for units.

So I'm gonna drag units.

Now this is gonna give me something nuts, okay? So this doesn't really make a great deal of sense.

We've got the sales announced and we've got the units that were sold.

So you can see there was an incident here where I guess you had a return of 10 units, and that meant we had a negative sales value of 503.

We just down there was a transaction.

We sold 63 items, 63 units, and we made 3629.3 of revenue for that.

So this isn't really very meaningful.

Final thing that we're gonna have a look at on pivot tables is grouping.

So it would be useful if that column kind of was grouped into, I don't know, like Every like unit, zero to 10 units 11 to 20 for example.

So when you sold a certain amount of units, what, what was the sales value? If I right click on that column and I select group, okay, and then someone says, aha, well, looking at that data, you've got a, like a range of minus 10 to 95, and I could group by every 10, and that seems reasonable, but actually I'd like it to kind of end on a complete 10.

So I'm gonna take that to a hundred, and if I click okay on that, and now you get the sales amount for different buckets, which is much more friendly way of summarizing that data.

Okay guys, so I think in terms of the content I was intending to cover, because we only had a short session on pivot tables, that was as far as, I was gonna take it.

When we end the call there will be a, a prompt, for a survey.

And I just see someone said, Hey, can you just do that again? Absolutely. Let me kind of go back.

So if I click on the pivot table, let me right click on the row labels and I'm gonna ungroup.

So we're kind of like back to where we were.

And in fact the rows, I'm gonna get rid of the rows.

I'm gonna chuck the name in there.

So the way we had the data previously, we had different names of different individuals and we had the sales amount and I said, you know, wouldn't it be kind of useful maybe to look at the sales value based on a certain number of units, a certain number of units sold? So I thought, let's get rid of the name, Jonathan.

So we're gonna ditch the name in my rose heading and let's go for units.

I'm gonna drag units in there.

So that's actually not massively useful because you've got how many entries you got? You've got like, I don't know, it goes down to row 109, okay? So it goes down to row hundred nine.

You can see that we'd sold 95 units and we had a sales value of 4,304.

Let's pick another one.

So we sold 50 units and we've got 2421.7.

So rather than just show all the kind of discrete individual unit numbers, it would be nice to group them and I'm gonna place my cursor directly in that column.

And if I right click on that, it brings up a contextual menu.

And within that contextual menu, I'm gonna select group.

Okay, so if we scroll down, I'm just gonna hover over that.

You can see it says, Hey, you are looking at that first column.

Why don't we group that data? So I'm gonna left click on that, and by default it kind of knows what the lowest and the top value is within that array.

And it says I'm gonna start at minus 10, I'm gonna go to 95 and I'm gonna cut this up based on like chunks of 10.

And my response to that is I'm broadly okay with that, but rather than go to 95, why don't we just make it a nice round 100 and click okay, and then it will group that data from minus 10 to minus one from zero to nine, 10 to 19.

It'll show you the sum of the, sales amount within that.

Now, I suppose you could take that further and you could say, rather than have the sum of the sales amount value field settings, maybe the average would be useful, or you might even wanna look at the max, for example.

Okay. And as we've done before, you can kind of play around with that.

So you can see, you know, on average the sales amount from 90 to a hundred was 279.

If we sold 20, between 20 and 29 it was 76.1, for example. Okay.

I hope that's we had a question on that and I hope that's Useful. And that makes sense.

Feel free to ask if you've got, we've got a couple more minutes left, so feel free To ask, you've got any other questions? Okay, I'm not seeing anything coming through then.

All I think that remains to be said is thanks so much for, dialing into the call. It was a brief session, but I hope that brief session was useful for you and I very much look forward to seeing you on other calls.

Thanks very much guys. Cheers. Have a fantastic afternoon.

Take care.