My name is Jonathan. Really great to have you in the room. Um, we're gonna have a look at some pivot table materials together, and, uh, it would be useful. It's not absolutely essential, but it would be useful if you could download them those materials and follow along with me. And in order to do that, you're gonna need to access the web page. Okay? If you click the link, uh, then you should land, uh, uh, open the file and la uh, uh, open the file and land on a page that looks like this. We've got a, a welcome sheet. Um, the, the file itself is, um, is about database analysis, uh, database analysis, and there's a whole load of different stuff in there. But from the welcome sheet, we are gonna have a look at the pivot data sheet. So it'd be good if you can go, if you can click onto that. I'm just gonna say that there, uh, there are exercises on sorting, filtering, some ifs, excuse me, uh, Trisha, uh, uh, conditional formatting, defunction defunction data tables. So there's, um, there, there are a load of other exercises, but we've got a, a kind of punchy 30 minute session focusing only on pivot data. If you wanted to look at the other exercises, then the answers to those are available. Um, uh, if you download the, um, the full file, and if you did go onto Felix, you jump onto Felix and we go onto, um, topics and Microsoft Office and Excel. Then, um, there are some, uh, recordings for the Excel content. There we go. Database analysis. There is a playlist as well. So, beyond the scope of this session, if you are interested in grabbing a look at some of the other topics in the file, then, you know, uh, I'll direct you towards that. Okay. We're gonna have a look at pivot table data. So I guess I could, um, uh, click with my, uh, mouse to go to that sheet, or I can hold down control and tap page down a few times. Big fan of using shortcuts. Take me to the data. Uh, we've got, uh, several people that have just arrived. If you've just arrived, welcome along. There's a link to enable you to download the files. Okay? Um, so when you land on pivot data, let's just have a look at, uh, let's have a look at what we have here.

Um, let's have a look. There we go at what we have here. Um, so we got a, a data set. Now that data, uh, could, uh, come through from, uh, an external source. So a lot of times on the desk, I'm using data that comes through maybe from, uh, another database. And it's a bit of a, it's a bit of a mess and I've gotta organize it in Excel, or perhaps it's data that's somehow been generated in Excel, and I'm manipulating that. But we're often in a situation where we've got actually maybe like an overwhelming amount of data, and we wanna make some sense of it. And that's really what a, a pivot table would do For us. So if I just show you this dummy data, we've got, we've got some transaction numbers, we've got some client data, and then we've got stuff on products and units and sale amounts. And, and if you kind of, you know, cast your eye down that, you know, my goodness, there's just loads of it. You know, there's just like hundreds and hundreds of rows. In fact, if I hold down control and tap arrow down, uh, then I get right to the bottom. Apparently it's nine 1900, uh, entries here. And so I wanna find a really efficient way of being able to make sense of that and slice and dice it and manipulate it. And that's really what pivot tables are gonna do for us. So if I go back up, um, to, to, to that data, I, I'm just gonna show you, uh, we, we, we, we need to, we need to figure out where pivot tables are. So in Excel, we've got all the ribbons at the top, we've got file home insert, et cetera. Now, if you look on the insert ribbon on the left, we've got PI pivot table, and we've got recommended pivot tables. Both are useful. So, um, recommended pivot tables will take a bit of a shortcut into building the pivot table. I guess I, in this session, I don't really wanna do that. I wanted to kind of a, you know, show you from the beginning how to create these pivot tables. So we're gonna, we're gonna click on that, but before we do that, there is a capable shortcut for that. And it is alt n and VI guess V for pivot table, maybe n for insert. I, I'm not quite sure of the, of the Microsoft logic on that. Um, but nevertheless, that's our shortcut. So, um, quite purposefully, okay, I'm gonna place my cursor, my active cell, somewhere within the data. Now, um, if we don't do that, if we start somewhere outside of the data, we can go and select it. But it's quite useful to know that if you place your active cell within the data and then you go and create a pivot table, Microsoft will or Excel will say, well, hang on a minute. I think I know what you wanna do here. I'm gonna expand the selection to meet the boundaries of this array or this range, and we're gonna build the pivot table from that. So it just saves you a step. So the, the cell is pretty random, but I've selected a cell somewhere in the middle of the data, and then I'm gonna say Alt N for insert and v for pivot table. Now, at this point, Excel says, Jonathan, uh, do you want to maybe take some data from, uh, a table or a range in Excel? And the answer is, yeah, actually, I, I do. Uh, or Excel says, do you wanna go and take data from an external source? So that could be quite useful to take it from an external source, but I think the issue is that if I was gonna do that now, you wouldn't have access to that external data and it just wouldn't work. Well, as an example, I'll let you play around with that outside of this session. I'm gonna hit t now. Now just check out what's about to happen. I, my active cell is sitting in the middle here. If I hit T on the keyboard, then it, it selects all of that data. You, you don't have to accept that. You can see in the middle it's, it says, uh, select a table or range, and it's pre-selected that you can override that. But actually, that's exactly what I wanted it to do. Now, um, in terms of creating an actual pivot table, you've got a choice to make here. You could have the pivot table created on this sheet alongside the data. I'm not a big fan of that. I like to keep 'em separate. And the reason I like to do that is, uh, if we, uh, if we, uh, create the pivot table on this sheet and we subsequently wanna, you know, insert rows and columns and mess around with things, then that can interfere with either the pivot table or the data and it, and it can be a, you know, actually a nuisance. So I'd usually keep them separately. And so the default setting is to create a pivot table on a new workbook book. And I'm gonna go with that. I'm gonna click, okay, lemme just jump into the chat box. 'cause I see we've got, we've actually got loads of people on this call, but we've got a load more people that have joined. If you've just joined, welcome along. Um, and, uh, I've just posted a link to grab the materials if you want them. Okay, right. So I've, um, I clicked, okay, let's get back into Excel. And you might notice it's created a new sheet. It's not a very imaginative name. You might wanna, you know, call it pivot table analysis or something, but we're gonna leave it at sheet one. It's a bit underwhelming, but when I've, you know, when I've clicked, okay, it's created this sort of pivot table one, but it's not redoing anything. And if you look on the right hand side, what we have is you have some areas, we've got filters, columns, rows and values and above, we've got some fields you can click on the check boxes and Excel. Guess probably where you'd wanna put those. Or for a bit more control, you can drag and drop those. Um, I'm gonna use my mouse here. So what I think I wanna do is create a pivot table and, uh, down the side with the kind of row headings, I think I'm gonna have, um, client account. So can you see, we've got client account here, and I'm gonna double click my left mouse button. I'm gonna drag that down, keep dragging it down to Rose. I've not released it yet, but have a look at my pivot table, one on the left hand side of my screen when I release this and I'm gonna release it. Now you get all those headings in. So thinking back from the raw data, we had about 1900 rows. And this suggests that we've got, I dunno who we've got there, about 10 or something. This suggests that this account number appears multiple times. So for each account, uh, client account number, what I, what I wanna show, I guess what I'd quite like to show would be the sales. So I'm gonna go and grab the sales amount again, I'm gonna double click and just hold down my left mouse button. And I'm gonna, I'm gonna kind of drag that down to values.

And if you, I haven't released it yet, but if you think about what I want to show, I, I guess, um, I'd like to show like the sum of the sales amount per, uh, per account. And if I let go now that's actually what Excel does. So it makes a reasonable guess. And it says probably Jonathan would like to show the sum of these values. And so summarize them for me. You don't have to use the sum if you don't want to, you can click the dropdown here and, uh, you can go to value field settings. So if you click the dropdown, it brings up a contextual menu. And if you click on value field settings, then it brings up a dialogue box. And uh, it's pretty intuitive. We can see we're summing stuff here, we don't have to do that. We could count or we could look at the average, and maybe you wanna look at the max. So maybe for each account we wanna see the highest sale that's happened. Um, perhaps if we're awarding bonuses or something like that, that could be, uh, could be useful. So I can click okay and it'll show me the max sale amount. And actually, if you look at that data, uh, that isn't that useful because the maximum sale amount is all pretty similar for each of these. Bit of a shame, isn't it? The format. Look at all the decimal places that data's been given to. It's maybe not very user friendly. Now, the classic thing we could do to change the format would be to select the cells and maybe apply a cell style and the keyboard shortcut there is alt H and j. Let's just highlight that just to draw our attention to it. So if I select these numbers and I say alt h and j, I could select the hardcoded cell style, I guess I could select percentage that don't think that would be useful, or I could select normal. And normal gives the data to one decimal place, and that is good, good, but it's a bit limited because if you choose to format the cells in that way, if you change the way the data table is working, you insert new rows and columns, then the formatting won't apply to the new cells that get pulled through. So I feel like I want something that's a bit more consistent there. I'm gonna control z that to undo that. A better way to format the numbers would be to go back to the values area at the bottom where it's this max of sale amount, click the dropdown, go back into value field settings, which you looked at before, and now have a look at the number format. So if you change the number format here, you're changing the number format for that whole field, irrespective of what cells get added to it. Um, as I said, I don't think maybe formatting it as a percentage is very useful, but perhaps if we format as a currency to two decimal places using pound sterling, uh, as the currency symbol, let's click okay. And okay again, and actually now the data's much, much easier to read. It looks a lot nicer. So we've talked about formatting the numbers, but what about formatting the, uh, the headings for the table and the text for the table? You know, what I think is pretty likely or certainly possible is that your company might have some corporate branding, and maybe if this is gonna come into a PowerPoint slide, you'd want include that corporate branding. Um, or you might just have some formatting that you want to apply for a presentation. So check this out. If I click off of the, um, the, the, the pivot table, and then I'm gonna use my arrow key just to go onto it. So I'm gonna kind of toggle, I'm looking at the data table. Now my active says off the data table, I'm looking at it, I'm not looking at it, I'm looking at it, I'm not looking at it. And you might think, what's Jonathan all about? Well, what I'm trying to show you, look at the, look at the ribbons here. Look at the top right of the ribbon when I go onto the data table, we get some contextual ribbons in green that relates specifically to that data table. And there's loads of useful stuff in there. There's probably more stuff in there that we could possibly hope to cover in 30 minutes, but we're gonna look at some important stuff. So if I, if I kind of looking at the table, we get this design ribbon. If I click on the design ribbon, then you can select some kind of predetermined designs for your data table. And it might be that for your Excel, your, uh, your company already has a kind of built in corporate style, but if it doesn't, you could create one. If you click on one of these styles, right click on it, you can duplicate it. You could call it something like Jonathan Jonathan's style and hit enter and it doesn't look like much has happened, right? But if I click the drop down here, you can see there's a custom style that's been created. I can write and click on it and I could modify it. The good thing about this is if I create a style for a data table, I can then use that across all of my, uh, pivot table. Apologies, I can use it across all of my pivot tables. And then if I'm bringing 'em into a PowerPoint, everything looks really nice and consistent. So, um, I, I dunno what we're gonna change, but we're looking at the whole table. Um, maybe I'm gonna, uh, uh, maybe we're gonna look at the first column, Stripe and format that. And we could go for, I don't know, like a color. So we could color it in like a, a, a red orange color that might look horrible. I don't know. Uh, we could look at the, uh, the first row stripe and we could format that. And Financial Edge, I work for Financial Edge and they're a big fan of blue. So we can go for blue. Now this might not look very nice, but if I click okay and okay, again, well it hasn't done anything, so that's a bit disappointing. But what we need to do is click on these styles and select my custom. Oh, actually I did, I ended up not doing anything very interesting at all. Let me, let me actually try and do something a bit more, a bit more radical. I thought I was gonna create something that was truly horrible, um, But I managed not to do that. Lemme just try this again. So let's, uh, I dunno, I said I, I said we like blue, let's pick blue or something. Um, yeah. Oh, that's the font I'm changing. Let's change the fill. There we go. Let's change the filter blue there. And let's go to the, what did I say, the first row stripe format. Let's look at the fill there and, uh, change that to, I dunno, purple or something like that. If we click okay, okay again, and then there we go. And we, we, we've got that selected so that, that's actually not very nice, not very u it's not very usable, but we're gonna roll with that, uh, for, uh, uh, for, for the time being. Okay? So we've been able to change the number format and we've been able to change the format for, um, the text as well and the headings and the way that's set up. What I, what I might do is say, um, I'd quite like to duplicate that and um, I wanna make sure that I duplicate the, the style and presentation for that as well so I can simply grab it, you know, really easy. I can control C to copy it and I can control V to paste it further down. Let's paste it. So now I've got two pivot tables, one, two, and all the kind of formatting is carried across. Maybe on the second pivot table. I don't wanna look at the sale amount, maybe I wanna look at something else. If you check the areas below and these areas are specific to the pivot table, I'm looking at the max of sale amount, I can just drag that to the side as soon as I drag it away from those areas, you get the red X, if I let go, then we dump that and I could say, well, rather than look at, um, the sale amount, maybe I wanna look at like the number of units that they've sold. So I can drag in the units that have been sold. Uh, it gives us the sum of those units and we could change it to the average units or the max as we said before. You know what, I don't really like the cell style. It's actually kind of getting on my nerves a bit. I find it difficult to see, so I'm gonna go and revert back to a much nicer cell style. You see how quick it is to just change the formatting straight away and make it consistent. And that looks a lot better, doesn't it? I, I'm gonna actually just expand that out a little bit, I think as well. Um, I want that to be a little bit, little bit wider, um, and get the formatting right. Okay, so the next thing I wanna talk about is filtering. So if we click on the first pivot table, um, of the two that I've got, I mentioned this at the beginning, there's a filters area. So I could say what I want to do is I wanna filter based on location. So if I drag location down to the filters and let go of that, then what I get at the top is location all. And you kind of think, oh, maybe this applies to everything. It's at the very top here, let's check it out. If I click on that, just look at the numbers here, look at the, uh, um, we are on max at the moment. Let's have something else. Let's have field settings. Let's Change it to sum. There we go. Click okay. Okay, so check out the big number there. 2 3, 9, 9 1 2. And for the, um, sub of units, I've got 7, 8, 7, 0 7. If I change the, uh, filter to say Midwest and click okay, it's only working unfortunately, um, unfortunately it's only working on this one table. So filters are quite useful, but they're only specific to that table. And in fact, if you look at the areas here, and I click on the second, uh, table, you can see that there's, there's no filter set there. Be really nice if maybe we could have a filter that was a bit more visual and something we could connect to different, uh, uh, uh, different pivot tables. What I'm gonna do is I'm gonna go back to that first pivot table and I'm gonna drag the location filter out to the side and dump it. And I'm gonna do something else. If we click onto the pivot table, you can see that I've got this contextual ribbon for pivot table, uh, analyze. If I click on that, then right in the middle we've got something called insert slicer. Now if you look at the diagram, Microsoft have put a kind of funnel on there and what they're trying to say is that this is a way of filtering something as well. So let's click on insert slicer and uh, it will say, okay, it's effectively a, a filter. What do you wanna filter on? Let's go back to location. That worked quite nicely. If I click okay, then you get this, uh, you get this, uh, uh, this new box and uh, check out the, uh, table here. If I select east, I can filter on East or Midwest or south. You might say, well, I'd like to filter on a number of items. You can click this, which is multi-select, and you can select more than one if you want to, or you can hold down control and do that and get rid of that filter and then include everything. Um, I quite like this. I think this is quite visual. It works quite nicely. I'd like to sort of drag it around and try and like get it in a nice position and resize it. And I'm doing a terrible job of that. I'm just not very good at doing that. But if I hold down the left alt key at the same time as moving this around, I can snap it to the grid and I can resize it. As I resize it, I can also snap it to the grid and that's kind of nice to be able to do that. So that's the left alt key. The other thing I really wanted to do is if you look at both of these pivot tables as I'm clicking on them, let's deselect these. I really wish it was, uh, able to, uh, filter on both of them. And it's not, but it could be. So I can right click on the slice up and, um, you can select, uh, report connections. So if you click on report connections, it'll say, Hey Jonathan, we've got this slicer. And when you in, when you built the slicer, you were looking at pivot table one. So naturally it's attached to pivot table one, but we could also connect it to pivot table two if you wanted to. And that means if we click okay that now when we start to select different items, it only filters on those. Did you see what it did that fan out? Quite annoying actually. Hey, I, I don't want it to be, I want it to be this width over here, but unfortunately as we're, it just keeps auto sizing. So that could be annoying because further down on this sheet you might have other things happening. You might not want it to quite resizing the, the column widths for you. I wish we could maybe make it not do that. Let's click on um, our, uh, let's click on our pivot table. Perhaps we're gonna get rid of this pivot table. I'm just going to get rid of it. I'm just gonna delete it. We'll, we'll only have one pivot table. If I go back to pivot table, analyze, and can you see over the left, we've got options. If we click on options there, there's actually some useful stuff in here, but one of the things I wanted to look at is it says autofit column widths on update. So you don't have to have that selected. I mean, you might think it's quite useful, but it can screw up your column widths for other things that are happening on the sheet. So you can deselect that if you want to. And now when you start selecting new things, it won't autos size. So that's kind of a useful thing to have. Now in terms of the, uh, data table itself, I'm looking at the, uh, client accounts and I might say, well, I'd rather look at something else. I'm gonna drag those over to the side. I'm gonna dump those. Let's get rid of those and instead I'm gonna pull the units in. I think, and I might, this might be a bad idea, okay, I think units could be a really cool thing to look at in the rows. And actually as it turns out, it isn't that good, it's not that useful because when you drag units in, you've just got loads and loads and loads of rows and I just, you can see the first row here, it looks like maybe they've had some returns of 10 units and uh, we've got that, that refund, that negative sale amount. And you, you know, you scroll down, you just got so much data, it's just not very useful. So I wonder if there's a way to make that more useful. Again, what I'm gonna do here is I'm gonna click on pivot table, analyze that ribbon, and then back to look at uh, uh, um, in, in fact we, we, we don't have to do it through options. We can do it even faster, uh, because we've got about four minutes left. So I can right click on the, on the column and if you right click on the column, I can click on group and then Excel will say, okay, look, so you've got a load of data here, which goes from minus 10 to positive 95. Um, so I could break that up into increments of 10 for you if you want. I like that as an idea. I think rather than 95, it might be if it goes to a hundred and if you click okay, then you've got much more manageable data, right? So what are we looking at the sum of the sale amount for, uh, unit categories 10 to 19? So when sales for, for, um, I guess sales of very small units, so you know, when we sold like n to nine units, um, we only generated in total 3048 in terms of sales. So that's quite interesting 'cause you could say as a business it's very inefficient to do that. And if you're looking at customer account profitability, it doesn't really make sense to make lots of small sales. It never really generates much revenue. I mean, it doesn't have to be like that for any given business. You might find that most of their sales are very kind of small unit numbers and that's a big proportion of their, of their total sales, but it isn't here. So if you're maybe looking to make the business more efficient, that was something that maybe you'd uh, uh, you'd consider change in the way that the business works. Now we haven't got a huge amount of time left. I know I've got something in the, in the chat box and I'm just gonna read that to myself in a minute. And while I'll answer that, before I do that, I'm just gonna say that when you look, uh, at the design ribbon and particularly the pivot table analyze ribbon, there is a whole load of extra stuff in there, more than we could possibly hope to cover in 30 minutes. Um, but hopefully that's just given you enough of a kind of, um, a springboard to play around with Pivot tables a bit more. You can see some of the power in them. So there's some really, really useful stuff in there. Uh, I've just got a question on the chat. I'm just gonna read through that. If a pivot table design format has already been created, is there a way to export or download that template and then import it into another Excel file for reuse? Um, yes, I believe there is. So what you can do is, uh, if you, if you, if you have the, uh, the, the file and you save the, uh, you save this file as an Excel template and your standard template location, which will differ a little bit depending on your organization. So if you, um, if you ask your IT department or if you have a look in the backend of Excel, figure out where your standard Excel template is and save this as a template file in there, then it will carry forward a whole load of stuff. For example, it will also carry forward things like your sell style. And so that is a useful thing to have. Okay, we pushed right up against the edge of the session there, so, uh, we crammed quite a lot in uh, um, thanks ever so much for being dialed in. I really do appreciate that. I hope you found the session to be useful and I really look forward to seeing you guys in another session. Thanks so much. Um, have an incredible weekend. It's Friday. Cheers guys. Take care.