Microsoft Excel Beginner to Pro Tutorial 📊 2024 Edition [Full Course] ⚡

hello everyone welcome on in to another learn with the Nerds my name is Alison Gonzalez I'm going to be your nerd for the day and I am joined with another one of our trainers here at pragmatic Works yes me B she's going to be in the chat answering all of your questions as we go through the day today so we're going to be here we got a nice hour and a half I could talk about asell for way longer than that we get an hour and a half together so anytime we're going through something you got some questions go ahead put those in the chat me is going to be taking care of those we also have a new special session that is going to be happening because we always get so many good questions in the chat during these that we can't answer all the time so we are going to be going through next week I believe and we are going to have another live where we just answer questions we're going to go through those chat questions um that we didn't really get to answer maybe we a little too too complex for like a simple answer that we can give you in the chat so we're going to pull some of those and answer those in our session all right so let's go ahead now and get into it so my name is Allison I am a Microsoft certified trainer I normally live here in the Excel powerbi World love training on those topics I actually have over a decade of experience managing and training for tech companies my background is actually in art if you can believe it so I actually started out with a fine art degree taught myself different coding languages moved into web design moved into training and now we're here so we're gonna have a lot of fun talking about data and we're also going to round things out by talking about how we can show off our data at the end of our session today so I've got a 5-year-old and a femon now actually six-month-old at home so those parents know that life over there it is Lots going on all of the time but what's nice with Excel is we can keep nice order we can keep structure even though there's chaos going around um everywhere else so we're going to be getting into how we can easily structure things work in Excel it's going to be a lot of fun today so I love to connect with you all over on LinkedIn it's just my full name which is right there on the screen for you I normally am sharing things related to powerbi and of course all across the Power Platform just like we do here on our YouTube channel so hopefully I will connect and see you over there as well all right so this is what we're covering today we are going to start off with some Basics because so many times you get thrown into programs and you're just expected to know things and so you're you know everyone puts like Excel expert on their resume when they're getting hired for jobs and then when you actually get to the need to use it you're constantly Googling constantly looking up YouTube videos trying to figure out what you need to know so there could be some little holes here and there in what you actually know so we're going to be starting off with some good Basics to make sure that we have those fundamentals we're going to get into formula usage so if you're just kind of one of those this cell plus this cell and that's really the extent of it and you're a little bit scared to use some of those formulas we're going to break it down show you how easy it is to use all of these formulas there's really just a set structure and you just know how to follow that and we're going to cover that for you then we're going to get into my favorite aspect of excel really the Hidden Gem you could call it and that is power query so you may have heard of power query let me know in the chat if you have used power query before or if you've heard of it but you weren't really sure what it was or you open it and where it's immediately like no I don't know what's going on here let's get out of here but we're going to break it down because it is so easy and it's honestly my favorite thing to use in Excel and then we're rounding our session off with dashboard design so we're going to be taking some data and making a nice little dashboard with that at the end of our time together so we have a lot to cover and a little bit of time so let's get into it so first off there's class files if you want to follow along with me go into the chat grab those it's a zip file you'll need to unzip that to be able to use that there's a PDF of the slides I'm going over there is a starting file that we're going to work on and also a completed version of that so if you just want to look at that that's okay too there is also a nice handy little keyboard shortcut because keyboard shortcuts are going to be your life when you're in Excel they make things so much faster and there's a million not really a million but there are a few hundred of them so I made a cheat sheet with some common ones on there that way you can print that out stick that up on your wall um and have that close by to reference so let's get started first thing that I want to talk about is cell referencing and sell referencing is just essentially in Excel saying Hey I want you to get the data from over there the data that's in that cell over there I want you to get it put it in this cell right here instead so there's two different ways that you are able to ref refence things there is what is called relative referencing and absolute referencing now essentially you're just locking things down or you're going free and just letting things move around so let's talk about that so when we are referencing things we have this structure at the top of our Excel files we have our columns and we have our rows so the columns are normally named with letters and then the rows are with numbers so if we're looking at that very first cell the top left corner in Excel what is that called that would be your A1 cell now if you just are saying hey go grab the data from there and then you're trying to move it down over row pull it around that is going to change Excel is going to update it's TR to try to move with you but sometimes you don't want that to happen so that is where we start trying to lock things down where it's saying hey even though I want you to look at this spot right here this one cell for all of these other cells over here I don't want you to move with it so then we can lock that cell down so that we no matter where we're dragging pulling copying and pasting our reference it is always going to go to that one spot no matter where we put it so we have relative and we have absolute and then we also have mixed where we can have either just the row lock down and we can hop around to our column colums or we can lock down the column but not the row so you could be able to pull it down we've got lots of power and it's all about just knowing what we can do and how to do that so when we're looking at these relative is where it's not locked down relative is where if I copy the content of my A1 cell and I have that in another cell when I try to copy and paste that somewhere else or you know pull that down for a whole entire column well then it's going to update that and so instead of being A1 it would be A2 A3 A4 so anytime we want to block a column or a row down that's when we're getting into our absolutes with those dollar signs in there or a mixed that we can see so it can be really intimidating when you're starting or you're just at any time in Excel and you see those dollar signs pop up you know when I was newer to my Excel journey I would see that immediately freeze and be like well I'm not touching this I I don't know what's going on I don't know why they're there or how to change that but it's really simple and understanding just the process of hey do I want this to stay and freeze in place do I want it to move around just understanding our process there is really demystifying things for us right when we're more comfortable when we're not scared to use it that just makes our experience easier these tools are meant to work with us um and so that's what we're going to be going over next up what I want to talk about I metion how we have our columns with letters and we have our rows with numbers well that wasn't always the case back in the day it was actually numbers for both which honestly I think is a tad confusing so I personally am glad they switched over to the letters for the columns but we can see over here we have this R1 C1 and this is the old way this is actually how Excel still thinks about things on the back end so even though we are seeing the a the b in the front that R1 C1 mode is how they actually still think in the background and so it's kind of important to understand when we're copying and pasting and moving things over how that is working in the background for us so we are able to see hey I'm taking this cell and I want to move this cell somewhere else well with that r1c mode where we have the row one the column one instead of having our letters there this is how that process would go so if we want to move that cell you know one to the right well then we'd have row column one if we want to move that cell one column to the left it would' be RC minus one because we're going minus one for the column then if we want to go rows we're trying to go up and down well if we're trying to go up that's a negative row and then if we're trying to go down that is a positive so this is how actually Excel is moving things around in the background so if you ever get your Excel stuck in that mode sometimes I'll open a certain file and um if the person adding it previously had it in that mode a lot of times I'll open it that way and so you'll see those ones you can always go into your settings to change it but honestly if you understand how it works it's not too stressful to work in that mode so we can see you know moving from that A1 which is our topmost left cell to our very bottom cell so xfd is the very furthest column far your columns go over in Excel and then our row Max of course is 1, 14857 so that very very last cell um is our bottom one so when we're thinking about those references thinking about moving things over we are increasing as you copy to the right and down and then decreasing or kind of minimizing as we go to the left so we are going to be hopping in to excel make sure you get that starting file from your class files if you would like to follow along if you need a second you can always pause this and then just hit play and resoe you would be a few minutes behind us but that is totally okay before you do that though got a nice little diagram and don't forget you also have a copy of this in your slides we can see a little bit more clearly with that A1 mode versus the R1 C1 mode right here in our formula bar where we could see hey how do I add together this and this what does this look like in that formula well we are seeing it's going to be that B2 because we got our B right here there's our markers plus the C2 but in our R1 C1 mode we can see hey we're moving from this is our initial cell we're going to reference this cell right here and then this cell right here so if you're opening a file and it's in this mode and you're like this looks even more strange than normal just a little breakdown it's really simple we're starting what cell are we on and then where is the other content coming from in relation to where I am now so I've got some keyboard shortcuts for you all I have a whole print out for you you can of course there's like screenshot this page it's also in the slides for you as well but I have a whole little um nice handy dandy shortcut for you it's a nice little PDF in those class slides looks just like this so you can see some of the most common ones what they do and a little bit of a note on keyboard shortcuts sometimes it's not going to be just like mashing all of those keys down at the same time anytime that you see a comma showing up that means that you put some together and then some after the other so it's a little bit different structure because again there's so many shortcuts they had to come up with a bunch of combos to get all of these to work so if you see a comma in the structure of any of these I don't think I have any in this one those are a little bit more of the complicated ones where you're going to need to do this than that um structure but these are some of the basic ones of course we've got our normal like uh control C for copy um Control Plus is bull control I is italic some of those are Universal across programs that you're going through got some good workbook ones like contrl plus n to open a new workbook contrl plus s automatically saves it wherever you're at contrl T opens a table there's so many that we can get into but I tried to get some of the basic ones in here for you to have an easy handout so that is in the class files again grab those if you need it so let's go ahead now we are going to jump into Exel so let's go ahead now I have the starting file open there's a starting file and a completed file in those class files so if you're like you know what I'd rather just watch or I don't really have the space to follow along you can always have that completed file open see what gets accomplished and then you can try to do that practice on your own rewatch this it is of course going to be saved here on our pragmatic Works channel so if you're not subscribed make sure you do subscribe so you can easily find this again if you want to re-watch any of these sections so let's get into it so we have a nice little section of some data here we've got some coffee in here I hope you all have your coffee with you too to keep you um nice and energized as we go through today so I have a few products here a bag of coffee beans a mug a little coffee bean grinder you know want to espresso machine I've got the cost of each of those items if only I could get a really good espresso machine for 100 bucks that would be super handy and we can see the quantity purchase for each of those items so I just want to find out hey how much does this cost what is my total for each of those items so we're going to do is just a simple this cell times this cell I want to say hey this item's 10 bucks I want five of them so 5 time 10 I should want to see $50 showing up right here so it is so simple to do this when you are writing any kind of calculation any kind of formula in Excel you always start off with the equal sign because if I was saying hey you know I want to multiply this cell plus this cell well as soon as I click off of the cell where I want that information to go it's going to highlight another one putting that equal sign in clues in Excel to saying Hey I want you to now go reference other data from some other place so we always want to start off with that equal sign and that is going to now allow us to just go click over to whatever we want now I could see hey this is the cell right here this is C3 so it's in my C column my three row so this would be C3 so I could just type C3 right here but even easier I can just go click to select it because I put that equal sign in I stay in the cell that I need to be in and I'm just referencing that other cell and it's pulling that content over now for my new cell so it's great we've got our first part we've got our cost down now I just need to figure out all right I know how much it cost now I just want to see how many I got so we are going to use a nice little Aster to multiply this and then we are going to go ahead and click on what we want to multiply it by which is the quantity so I can see I'm referencing C3 and I'm referencing D3 now all you have to do is hit enter and we can see Tada we got 50 bucks there now a handy dandy little way so you don't have to rewrite this every time you also don't have to copy and paste I don't have to hit contrl C control V to copy and paste this down you can just grab right in the bottom right corner and just drag that down fills out your whole entire table now we can also see because we didn't lock it down we didn't have any of those dollar signs in here that this is just referencing exactly where it is and then as soon as I drag it down a row it has moved it down and is now pulling from this row right here so if I wanted to change that I could lock it down but if I don't want to so I'm happy pulling it over now if I try to pull this a cell over this way well it's not going to work right it's not going to work because now it is pulling data from a spot that doesn't matter saying hey multiply d and e together and then as we go over it would be like plus E and F so if we were trying to go horizontal or a different direction we would definitely need to change that and put those asteris in now a super easy way to do that is with your F4 key on your keyboard so all you have to do is in your formula bar you can hit the F4 key and as soon as you do that you can see it is going to put in those little dollar signs for you lock everything down if you keep hitting your F4 key it's going to cycle through all of your options so I can see all right my column can move but my row is locked in place or now um the vice versa my column is locked and my row can move around hit it again and you are back to normal so F4 helps you easily decide hey this needs to be locked in place this one doesn't and you can move around like that all right let's move in we got our basics in let's move into a little bit more complicated now I'm going to hit cancel to get out of this cell because I just kind of move some things around make sure that's working anytime if you're in a Cell you're looking at your formula and then you click to somewhere else notice that sometimes you can start messing things up so you want to make sure uh before you get out of your cell you hit that X you can also hit undo and that should help um fix anything you may have inadvertently messed up all right next thing up let's go in and we are going to look at this time card one so we got another example here where I have a rate people are getting paid 50 bucks and hour and I can see the amount of hours that they have worked so what do I want I want to multiply the hours by the rate so for the first one pretty simple right exactly how we did it last time we're going to start off with that equal sign in our pay column we are then going to select our cell so we will have C3 is our first one that's our Monday hours we're going to put in that Aster to multiply it and then I'm going to go ahead and select this pay rate cell which is G2 so C3 G2 fantastic we're going to hit our enter got our 600 bucks pretty good day on Monday now look what happens though when I move that down it is not working and I talked about that in L why is it not working because now look what it is pulling C4 fantastic but now it's looking at G3 next one down it would be at C7 and then G4 so as it's going down we need to lock down just this one I don't need anything in that pay column changing that's fine I just want to make sure that G2 stays locked in place so let's go ahead I'm going to click back on that $600 my first one because I want to make sure all of these are the same and then I'm just going to highlight G2 up in my formula bar and as that's highlighted I'm going to hit my F4 key lock this down so that way that G2 gets locked in place once I do that you can hit enter hit the check mark to get out of that cell and then let's just drag that back down again to make sure we are updating all of our others now we can see we get the right amount of days or the right amount of pay for each of these days because it is not accurately click um going over and getting this in place so super simple hopefully this for a lot of you is a review maybe some of you were a little bit timid in going into Excel um or just just doing something not understanding why it worked or elements of that hopefully you're just getting a little bit more confident as you go through now next thing that we are going to talk about we're going to dive in so we did some easy ones but we're going to dive in now and just immediately jump into a little bit more complicated formulas so let me show you the ones we are going to be looking at next so make sure that pops up there we go we are going to be looking at sum average along with xlookup we're going to end with a handy dandy one so when we are writing we started all of our formulas so far in the actual cell and I personally don't like that I like to write all of my formulas and functions in that formula bar now little tip a lot of times when you're hearing the terms formula functions and people will use those interchangeably they actually do mean two separate things functions are the predefined ones that are set they have a whole calculation in place for them formulas are ones where you're like I want this plus this and we'll do some of this divided by this you're making it up as you go you're adding your own flavor to it you're combining different functions so formulas those are more of the ones you are creating functions are the predetermined ones so in your formula bar this is where you can write your functions you it's could just be the sum it could just be the average that you go through or it could be a really complicated one that you are adding in now when you're getting started writing your formulas and your functions you start thinking um this seems really complicated I don't know how to get into this seems It's like its own language right well there's a lot of helper elements built into Excel to kind of take a lot of that stress away so when you are looking in here you are able to easily right at the left side of that formula bar there's this FX button if you click on this FX button is going to pop up this handy little helper window in this one you are able to select exactly what function you need if you don't know what function because sometimes you're like I just want to add stuff up well add is not a function but or multiply things up rather that's not necessarily a function but the sum where you want to say I all of this you know added in together is so if you're not sure what function you need or what it is named you can go into this function hper window you can go into this little category section right here open that up and it breaks it down into all of the ones like mathematical statistical date time all the different elements of different or categories that you want to see are in there and you can easily go through see the specific ones you want pick the one you want then you'll see a little breakdown for it which we can see a preview here where it's just showing hey you need this plus you need this plus you need this great so we're breaking it down we're taking it in small pieces and then you can go tackle one element at a time so you're like all right I need a range of dates I'm going to go over here I need this element that that's right here so you can easily put those pieces together functions are separated by commas so as soon as you grab one element one range one cell for the next thing put the comma and it's going to push you over to put that next section in so getting used to writing functions in that formula window it's really easy it does take a little bit of practice but we're going to kind of demystify that all right another spot you can always check out is the actual formula ribbon that you have there so it's like the fourth one over from home and you can see all the different ones like the financial logical date time Etc and that has the list of them there so some people like to use the formula ribbon some people like to use the window I want to show you both so that way you know you got some options all all righty so we are now going to Dive Right on in there are again hundreds and hundreds of these but we want to give you a little bit of a taste of some of them get used to writing in the formula bar and feeling confident to tackle some of those more complicated ones all right so hop on over in your starting file worksheet and we have this nice little sum and average tab so over here in our sumon average tab we got some nice plant store data so if you're a plant person like me so we have a plant name column we have the type we have the height we got watering frequency lot a really helpful info better than that though we've got our cost and our quantity so I already pre-built our total column which is showing hey if we bought to five roses that's $12.99 these are some really good plant prices y'all I hope this is a real plant store so what do I want to find out first well first thing is first I want to find out how much did all of these plants cost so I want to find the total so if I'm buying all of these plants how much is the total of my total column so I can see I have this total plant cost column um cell selected right here but unlike last time where we wrote in the cell I don't want to write in the cell I want to write up here in my formula bar because that's going to give you so much more space instead of getting cut off by your cell and having other text and being hard to see just like your legibility is not great we want to write it up in our formula bar so as long as you have the right cell selected it's going to put whatever you write in there it's just giving you more space so I'm a die hard use your formula bar person and if you don't get anything else out of this get that you know you definitely will save yourself some work if you do it in here all righty so let's go ahead now and we are going to make sure we have that K3 cell selected I can see it's kind of highlighted up there then we are going to write in our formula bar and we are going to use some so just like we did at our last examples we're going to still start with that equal sign and now though different from our equal sign we want to use the sum function so I can just type out the word some but I also want to pull up this little function helper window so you can see what that does so if you go ahead and click on that I can see I can sort from Recently used I can have all of them in alphabetical order and you can see all of the hundreds and hundreds of ones that are in here really hard to find what you need right especially if you don't know what you're looking for so that's why it's broken down we can see Financial specific ones or text specific ones if you needed to you know get the mid from a certain section or trim off your white space those are all in that text section so I'm going to go with sum and with that you are able to see the different structure so if you select it it is going to show you the next step in that helper argument it's going to show hey we need to have this section then we have that and it breaks down all the things the good thing about using this is you can see a preview of what your answer is going to be before you ever hit okay so if you're getting errors you're not getting the right answer you can keep working in here to generate the right answer before you ever hit okay so that way I know it can be intense sometimes when you start seeing errors and just nlls and just not the right answer in your cell it's nice to work in here so that way you can kind of keep things clean as you're going so for us what do we want we just want this entire contents of our H com column so all I'm going to do is I'm going to select what's called a range a range is just a section of this cell to this cell and I'm going to click and pull down to highlight H3 all the way down through h12 and I can see that what I've selected right here is reflected in my bar up here in that function argument so I can also see here's that total so I can see 600 bucks for all of the plants I think that is a really good deal so that's accurate so I'm going to hit okay and there we can see we've got that right amount going in there next up I want to know I'm getting curious now about this data I want to see what is the average cost for one plant just like the average of all of these obviously we've got a variety but what's our average plant cost going for so just like we do with the sum we can do that with average now this time I'm going to write write this just in the formula bar we're not going to pull up our helper window so you can see also how easy this goes so we're going to start off with our equal sign again you want to make sure you have that K5 cell selected anything we put in the formula bar is going to be in that we're just getting more space so with our equal sign in now we can just type in the name of the function that we want to use so I'm just going to start typing in average and I can see we've got a bunch of different average options here the cool thing is that everyone that you hover over and you can navigate through these with the arrows on your keyboard you can it gives you a little description so I can see what's different between each of those average functions I want to select the first one so you can hit tab to select it so the tab key on your keyboard again Excel will make you a keyboard person once you got all those shortcuts down and we will have that pop up now I can also I see I have another little helper window still here just like we had in our function window where it's showing hey you need a number to average we can also have a range in here so what do I want to average I want to average my cost quantity that original cost quantity because I want to see what is the average original cost for one plant so again with the range I'm just going to select the first cell and pull down so I have that highlighted I could also just manually type in F3 colon F12 that is also totally okay if you don't want to select the whole thing you can just manually type that in if you know it all right we're going to close that off with a parentheses because we started with one we want to close with one and we can hit enter or that little green check mark we can see 10 bucks it's the average cost of one plant to this economy I think it's pretty good deal final thing we want to find out on here is the count of our plant so how many plans do we have or have we purchased or planning to purchase here so that's going to be my quantity column I want to show you something we're going to start again I have that cell selected I'm going to start with my equal sign count is a function in Excel but if I tell it to hey we want to count the quantity cells what is going to be returned for me it is not going to be the quantity of plants that we have purchased instead and I just clicked off of there come on we want our noop quantity selected there we go we are getting the count of the cells so you have to think through what you're looking for and essentially what you need so yes I want to count of that but really what I'm looking for here is the sum I need to change this so I'm getting the sum of the contents of the cells not the count of the cellss so this is super easy I don't even have to delete this I can just double click on count and just write in the word sum and then select that boom super easy peasy as my FAL says to edit this now you can see 72 that is that accurate plant count that we have all righty we're slowly ramping up and now we've kind of just like Dove off the cliff here we're ending our function section with X lookup now you may have heard of V lookup and V lookup has been around for years X lookup is like the newer cooler cut it's also a little bit easier to use more helpful and just better all around so with X lookup the way that would it went is it was in like an L motion so you would go down a column find what you need and then it would go across that row till it hit the whatever else your other criteria was with X lookup it gives you a little bit more flexibility so it's not just moving um in the kind of X setup or or in the kind of L setup you have a lot lot more free range a lot of people they would end up using combinations of match with that and it was just a little bit more work so Microsoft always developing always changing things Excel itself even though it's been around for decades is still getting updates still getting new things added to it um next look up was a really handy function that got added in a few years ago that really just allows now for horizontal and vertical searches going looking up values in rows and columns all righty so let's go ahead now I have in here a search ID so this is just in this one you can change it to any employee ID that you want I just have that first one in there but if you want to look up Alice you can put in and see hey how much does she make so this would be handy ideally you're probably looking at more than four employees because if you got four employees you can just eyeball that right there but let's say you have 400 or 4,000 you have their ID you can easily plug their ID in and look up that information that you you need so what are we going to do we're going to select our employee salary cell that little blank space I have which is G4 and we're going to of course move up to that formula bar so we're going to start off with an equal sign and then we are going to pull in the X lookup formula so we're going to pull in X lookup and as I start typing it comes up immediately we can see X lookup searches a range or an array for a match and Returns the corresponding item all right so hit t tab to select that and now we can see the items that we can put in so we need a look up value a lookup array a return array anything in Brackets is not necessary but you can add it in if you would like it like um hey if that's not found what do we want to see instead it's not necessary but you could put it in so you're not showing up with a blank and then wondering well did I mess something up is it just wrong um you can get a little bit more clarity with that way so first up with our xlookup is our lookup value and the lookup value is our employee ID so we want to click on our G3 cell to say hey look up whatever is in this cell that way we can easily change that around we don't ever want to hard code into our functions because that makes them really like difficult to change so for longevity code it to the cell and then you can change the contents in the cell so we're going to say hey look up whatever is in G3 right now that's our employee ID 101 we're going to put a comma that's going to bump us to our next section which is going to be the lookup array which is going to be where are the employee IDs in this table so the employee IDs in this table are B3 through B6 so I can just highlight through of course type it in as well but I'm going to highlight through B3 to B6 that is my list of employees we are this could also you could lock this down you put those dollar signs there if you wanted to um because that column is not ever changing so if you didn't want to you could do that here put in a comma move to the second section and that is your return array and that is going to be the salaries so we're saying hey go to whatever the contents of G3 is which is 101 find G1 in that B3 column B B3 to B6 once you find it then go find me their salary that's what we're telling it to do so we are going to tell it the range now for the return array so anytime you see that array they're looking for just more than one cell technically so D3 through D6 is our return array now I could close this off right here but I'm going to add something else I'm going to add an if not found just for a little bit more clarity say if I pull put an employee ID I type it wrong and they can't find anything I'm going to say employee not found so in quotes because we're putting a string of text we're going to put employee not found show capitalizations are in place there let's go edit this come on there we go and then we can go ahead um and we can also put in our exact match for the look which is our match mode and for that I'm going to say I want to see an exact match so I don't want like the next smaller one or large ler or just kind of random I only want to find the exact match so we want to have that zero for our exact match in here and we're going to close that whole thing off of the parentheses hit our enter button or that green check mark and boom we can see for jondo search I employee ID 101 he's making 50 Grand so if we change this and let's say I want to look up Alice now I just changed my search ID well it is now going a date that's all you got to do I don't have to touch the formula again once I wrote it it's good all I have to do is edit my number and it is going to adjust and work and so I can see super easily what's going on and let's say I put in a number let's say I just do this one and I know I don't have any 202s so now it's going to tell me employee not found so that way I'm not finding an error or a line something else I can see oh I entered that ID incorrectly so that's all Weir going to talk about with functions it is just a little taste of them right but hopefully enough that you feel confident diving in exploring using these if this was something that kind of held you back in Excel before so using these references if those dollar signs canuse you before hopefully you're confident to you know hit that F4 key lock some things in place so I could talk about this for days we have such a limited time and I've already got to rain it in as we're going on this but if you want to join me talk more Excel we actually have an Excel boot camp going to be doing that in two weeks from now I think it is I think it's almost like a week and a half it's coming up really soon we have a few more spots available for that but we get four whole days 9 to5 talking about Excel going deeper through all the things work like mentioning today and so so much more like we're going to talk macros we're gonna we're GNA get into a lot it's going to be a ton of fun so if you are like I just want more give me more well come on over join me for the Excel boot camp it is going to be a lot of fun get a whole pretty much a whole week of Excel and it is just going to be a blast and hopefully really really informative no matter what Excel level you are on we normally keep these our boot camps to you know 10 to 12 people we try to keep those class sizes really short you know right now we're in thousands of people um in this and it can be so hard you know all I see the like chat on the side of it um and so many people are putting in great questions answering things and it can be a lot so having those really small class sizes in our boot camps is great because you can get immediate one-on-one answers you have bring something up we'll talk about it demo it out and we can all share and give our feedback on it so they're really awesome so I would love if you all join me over there um if you really want to dive in just like level up those Excel skills all righty so we are going to now dive into what is my favorite part of Excel and IT I would also say is probably one of the most kind of not known elements that even lives there so we are going to be getting into Power query now the power query editor in Excel was added to really increase its power you know we're going through people have to write macers you were doing all of these things to clean our data it can be a pain when you've got multiple sheets and all of this stuff going on and it can just be a lot so power query was created to have a space to clean your data get rid of all the stuff you don't need add in anything you do need super super easily now the reason why I think power quer is so great is there's a really good what's called user interface or UI that just means whatever you want to do you don't have to write a formula you can just click a button now there is a formula bar there is a coding language you can write things in but most people never need to get into that it's called the M language I think it's pretty interesting but it can be a little bit tricky which is why it's so great that there's just buttons for everything so we're going to hop on over now to our candle sales data so we have two years of candle sales here so I can see I've got order ID I've got information all across this um all different information on all of this data in here and what we're going to do is we're going to take this into Power query to clean this up so to get into Power query we are going to go ahead with our data um and we're going to go over to our data tab so in our data tab let may never even clicked on over to here our data tab we have this section over here this get and transform data section and we are going to be looking at this get data tab specifically now in your get data tab this is where you are able to tell Excel hey I want you to look at this data then take this data into Power query to bring that in literally get data go get your data from somewhere well you could open a blank file and then connect conect to a lot of different other files different sources you can see you can bring in things from an Excel workbook a text file a PDF even you could work from databases all these different sources well you can also just work from the data that you have in the file you're already looking at so we want to pull in data from a table or a range that's what we're looking at right now and I can see that option in my from other sources here in my data tab now you can also see these these will change kind of periodically based on like whatever you select a lot but you can also you may see that from table or range section here now I do want to say I notice this a lot I think my like government tenant friends or anyone that may be on like an earlier version your get data button may be closer towards like the middle-ish over here but you should still have it uh no matter what version of excel you're on it just might be slightly adjusted in that bar based on where you're coming from so we want to go with that but before I bring this in right now this is just a range of data if I go to my from table or range well then look what it does it has selected this data but also look what it did let me scroll on up here it has locked this in place we've got our kind of absolutes there we're locked in place so what happens if this data adjusts what happens if I get new data pasted in here well I would have to go back in here change my range because it wouldn't be right it wouldn't be fully in it so what do I need to do I need to make this a table that way any data that gets added to this table later on is going to automatically go through all of the Power query steps with me so super easy to make a range of data a table all you have to do is control T So control key n t and that is going to make it a table I'm going to say yes my table has headers and boom this is now a table so any time that you are working with data especially pulling it in I like i' say probably good 90% of the time you want to put that data into a table it's just going to make it easier to reference easier to use I can see I have table one right here so if I want to I can just change the name from table one up in this table design tab right here just going to change it to candle table does have to be one word just FYI there but we changed it so that way now anytime I know I want to reference this data I can say oh yeah pull it from the candle table because it's so much easier than trying to remember a range on a specific sheet so now that we've made this a table I'm going to help back on over to my data Tab and over here we're going to go to get data and again we're going to go with from that table or range so if that's not showing up for you right here then you can just go down to from other sources and go with from table or range also if yours are moving a little bit slower than mine you can always pause and hit resume when your stuff loads for you as well all right so we now have a new program that has loaded for us this is called the power query editor and this can be intimidating for a lot of people when they first start off because it is a fully new item and I already pulled this in which why you see it in here twice pulled this in um so you can just go click on up to that first one that's in here that candle table that was in here for y'all when I made your completed file so when we're on that other one and you can see that's the first part of our little tour here in power query so when you're in power query intimidating we got a lot of buttons but over here on the left hand side this is called your query pain so over over here in your query pane this would be a list of all of the different data sets you bring in because what's cool you can bring in all of those other different tables I can bring in lots of different data so if I want to merge data together I'm able to do that so easily so if you have sales data for 2020 2021 2022 2023 instead of having to manually copy and paste and move stuff over and then clean things up you're able to easily come here into Power query and merge this we're not going to talk that today but I do have a video on that topic here on our Excel um or on our YouTube channel whole series about power query because the cool thing if you are also a powerbi fan and have used powerbi power query also exists in powerbi power query in powerbi and in Excel is virtually the same there's maybe like a few teeny tiny differences but for the most part every single thing that you do in power Query in Excel you can also do in power query in powerbi and so I did Series last year I think where I went through and showed the same steps in both power queries and what those outputs would be in Excel as well as in powerbi so we've got our query pane over on the left it has a list of all of the data sets now what's cool is whatever one you have selected that's going to be the preview that is in the middle of your page now in this preview section I'm yes I'm calling it preview section because it only shows you essentially the first a thousand rows of data so if you're pulling in 200,000 rows of data well you're not going to see that it would run really really slow if you tried to do that power query Works incredibly fast because we're just doing all of our changes to essentially a sample of our data then when we close out power query it is going to run through all of the changes we made and apply that to our data so we can see whatever one we have selected in our query pane on the left we can see a sample of that data maxing out at that first thousand rows well talking about changes that we are able to make all the changes that you are able to make are stored in your applied steps so over in your applied steps you can see all of the changes that are made now generally when you pull something in you are going to see a few changes and I'm going to hop over to the second one the one we just connected because I want to show you the difference between this and our kind of finished version that I have for you here with this one we can see we pulled it in and for us power query was like all right here's our original first step here's the source of our data well for us it looked and it did a second step which is this change type step now what's really cool with power query is you can go through and essentially like going back in time you can see your data um for how it looked in the beginning and then you can see all of the changes so I made a bunch of changes I'm going to walk through talking about what those changes are with y'all in a second but I can see I can go back to that very first thing and here's how my data pulled in I can see the exact here in my little formula bar up here I can see it pulled in my candle table I can see it recognized that I had those headers because we made it a table but what is right here I have this little icon that is on the left side of each of my column headers that is telling you what type of data this is and you always want to make sure that your data type is accurate because that will influence a lot of things down the line like what type of filters you might have so you want to make sure hey if it's a number it's set to a number if it is text it's set to text if it's a date is set to a date type so most times power query will do that for you so we're kind of moving through our applied steps really moving through time to see what that did so it went through and we can now see hey instead of that random ABC one two three icon where it's like I don't know what this is it has specific ones for each table of our data which is great now one of the most common things that you will probably need to do with your data is remove things that you do not need so in our data looking at this what we have going on going to skip back a step for this I can see in my data I have a first name column and I also have a last name column now with this data that's great but I don't really need their first name I also have a full name column so I don't need to have their first name their last name and their full name I can fully just get rid of that first name and their last name and that is good to go now to remove columns you have a few methods that you can do you can just rightclick in the header of whatever column you need and you can see I can remove it I can remove others you can hold control and you can multi select so I can hold control just select the ones I don't want or want to keep and hit remove or remove others if you have a really wide data set so if I have you know 50 columns I don't want to have to hold control and then like select not select I know I'm going to mess that up so I always make sure instead of doing it that way I like to go up to this choose column button that is up here in my home ribbon so I go to this little choose column button and what's handy about this is when you click on that icon you're going to get a nice little window I'm going to hit insert because I'm going to show you this for a sec nice little window and this goes through and it shows you every single column that you have so again when you have a ton of columns this is so handy to be able to go through and just uncheck the ones you don't need and keep any of the ones that you do need so you go through see click on the ones you want to have want to keep it makes your life so much easier all right I'm going to cancel out because we already did this step for us and we can see that in the next one removed columns where I knocked out the customer first name and last name we kept their full name but see here what I wrote in that line of code that was written for me I didn't have to do it just clicked buttons and it wrote this for me I got rid of the first name column and I got rid of the last name column we got a full name column we don't need their you know first and last names next step is I trimmed text now this is something I was working with something someone in a mentoring session and we realized that as their data was getting entered there was a lot of spaces either before before the text or the numbers or even after and just random amounts and it was really throwing off kind of combining things and matches later on they're like why there's two items they look identical why isn't it telling me I putting them separate well it was because some had spaces and some didn't so I see that a lot and trimming is so easy to do in the powerquery editor so when you go to trim your text all you have to to do is select the columns that you would want you can maybe have just one column multiple columns how many ever you want to trim wherever you think there could be spaces and you just go down to this transform button and trim so all you need to do is right click in the header at your transform button and there is this little trim right here notice all of the other transforms that are super easy to apply to that whole column so if I wanted to make my whole column uppercase or capitalize everything you have tons of options super easy with the click of button that is why power query is so underutilized but so amazing so let's start trimming so I trimmed it we can see it trimmed look at all of that text that was in city and state all those spaces change that and then another thing we are able to do is we changed our date type so there's a few different date types you can have there's date time date time date time time zone now I just wanted to have the date I didn't need to also have the time I didn't need to have other elements on there I just wanted to have the date so you can easily go through here just click again changing the type to what we wanted it to be now the last two steps super easy I added in two additional C columns I pulled the month name and I pulled the year out of that date column and I'm going to delete this very last step and I want to do it again with you because I want to show you how easy it can be so I can see if I look at the code for either of these by clicking over here in my applied steps I can see up here in my formula bar all right that's that whole add column text right here I can see it's a month name and I got like this date I got the fun I I could do this right it's not the hardest thing I ever write but it's a little bit complicated well let me show you the easiest way ever to make columns and even when I know the code and the right way to structure it and write it most of the time I'm still using this feature because it just works faster and it's even easier so I'm going to go over here into my applied step and hit this x because what's really fun I'm going to say fun this kind of can go either way what's fun here here in power query is that there is no undo control Z does not work here in power query so if you need to get rid of something you just come over to your applied steps you see where you hover over the step you want to change if there is an X you can fully just remove it so you just delete it if you're like H did the wrong thing I don't need that I don't want it you can get rid of it if you're like I just want to modify it you'll see some of these will have gear icons and so if there's a gear icon you can click on it it will pull up a window and you can make the modification there or of course this change that's in the formula bar in your code so what we're going to do is we're going to just remove that last step because I'm going to redo it all together so I'm going to hit that little X on our inserted year step and we're going to do it now so we have an add column ribbon here up at the top you can switch from home transform add column now what I'm going to do we can see we have different column types you can add in we are going to do what's called a column from example now this is an AI feature that has been built into Power query for a long while and it is so easy to use essentially all we're going to do is we're going to write in our very first cell of that new column exactly what I want to see so I want to see the year I'm just going to look at my date column see what year is in my date column and then I'm going to type that year in my new column I'm going to hit enter and then power query for me is going to understand where in that row I P that data from it's going to be like oh that's the year it's coming from the date column so it's going to write the line of code for me in the background and then it's going to iterate over the table going row by row applying that to it and the best part it's like instantaneous so let's do that now we're going to go up to our add column ribbon we're going to click on that icon from that column from examples you're going to see your whole screen kind of go gray and kind of freeze and what you're going to do is in that column one right here where we have this green bar we're gonna type our year so down here at the bottom you can scroll over if you're on a really wide screen you might be able to see all of this at once I can see over here in my date column my year is 2022 so all I have to do in my new column one is right 2022 and then as soon as I hit enter it is going to magically fill in everything else for me so go ahead click into that green cell in that column one type in your year you're going to see some options popping up and that's okay you can just go ahead type what you want and then I'm going to hit enter so we've got 2022 and there boom it even named the column appropriately for me and identified oh we are going we're doing a transform we're doing the datee transform and we are pulling it from the date column so I can see that line of M query code that was written for me in the background it went row by row by row by row by row all the way down the whole entire table and went through that date column pulled out the here and I can see it changes I have 2023 data in here too and fill that out so now we're good we can just hit okay we're going to see this gets added to our table all of our buttons come back and get ungr out and we're able to kind of move on and use our data so power query can do so much it is incredibly powerful that goes the name and it is something hopefully it's a little bit demystified for you all now um definitely if you want to dive more into what power query can do I do have a bunch of videos in here um on our Channel you can scroll through anything here on our YouTube channel that is green um you know that's Excel there are a few not other green programs like fabric content is also green it's a little bit more teal green so scroll through anything that's Excel green um you can easily see that Excel content and look through all of the videos we have on this now I'm good I've cleaned it up I'm happy with this data what I want to do is I want to take this data here from Power query and take my updated data into Excel to now use to make a dashboard I want to you know use this data to create other things with this before I do this I'm going to go ahead over my query paint I'm going to delete that second one we added in notice how easy it is to adjust things I'm going to delete that candle table too hit delete on that one and once that is gone and we're back to our other one let's go to our home ribbon so over here in our home ribbon the farle kind of place of honor button that we have over here is close and load and we have two options here so first one close close and load this takes the data to excel so if I just want this data to take it just like this drop it into a another table I could do that first one which would just be the clo close and load take it right to excel now the second one is close and load two and close and load two gives you some more options and you're going to use this one if you want to go and do things like pivot tables um anything else like that it will take your data and move it right into that format for you now if you do your close and load and you want to change it later you can in your query and connection pane but I always like to kind of take a second pause make sure I'm picking the right option from here to save myself some work so in that dropdown we're going to go ahead and pick the close and load two and we are going to VC we have our candle sales and I think mine just went ahead and dropped it in here do I have another window I think I've got another window floating around somewhere in here all right while I'm looking for my window to see where in the world my data just disappeared over two and before we get into looking at designing our dashboard there was one other thing I want to tell you guys about remember so C first thing make sure you go over that second thing I want to make sure you go into um and understand is our C XP because this a super awesome thing so we have for so long people have found us for our YouTube videos and they've gone to our on demand full length classes that we have hundreds and hundreds of hours of training for and a lot of the time it is people who are like hey I want to get into being a data analyst I want to learn this I want to learn that and part of getting jobs in those fields you may know is having certain Microsoft certifications so so often people get asked hey what classes should I take for this certification what class should I make for this certification so we've started making classes just specifically to cover that so instead of hey you're going to have to learn you know 200 questions worth of data but here's you know hours and hours and hours of content that one is super helpful and you should know but it might not be you know specifically tailored for your exam so we've launched a new feature just focused on helping you all prep for exams I've passed a bunch of them all of us here part of our job is actually taking these exams so we are able to then understand what's in them so we can then teach you all what is in there so we have a new feature that's now part of our on demand learning platform that's focused just on certifications giving you the exact information you need um to study and learn and master to do well on these so if you're a season learning past subscriber already or you're an on demand learning subscriber you are going to have access to this content sooner believe it's launching like next week so super super soon but there is I believe a whole little video promo in it has all the information you need so you can take a look at that now the pragmatic Works team is excited to introduce CER XP C XP is not just a learning platform it's a New Horizon in technical exam preparation experience learning like never before with elements of gameplay that make studying not just effective but incredibly engaging with our pre-loaded Journeys you can easily navigate through the vast array of certification options and choose the ones that align with your career goals cert XP will be in beta to our season learning past subscribers next week we hope to give access to all subscribers soon after stay ahead of the curve with cert xp's exceptional training programs join the wait list to be notified when cert XPS [Music] [Music] available all right everyone the links are in the chat to sign up for CT XP it is super super handy um to see all of the different classes if you're going in there we have a bunch um that are coming soon a whole bunch of STS on our list that we're going to be getting you content for to make your life easier you know if you need that content we're here to teach it to you all righty so we've got our data it is loaded in of course because I already loaded it so we've got our candle sales um but what I want to do is if we look at our queries and connections pain I can see I have it loaded right here so if you ever are wondering where in the world did something go or do I want to change it here in your data tab you have a queries and connection paint and that will pull in anything that you've worked on any of those queries where you're able to change it so you can see hey I have that connection only because I want to do stuff with it if I want to edit that I can easily click on that right click and then I can change my load too so if I want to load in the data or if I want to load it into like a pivot table change any of that that is going to be through that queries and connection pane so I can change if I wanted it to just be a table if I wanted to show up right here and work on it then I could click on table if I want to do a report I could do that or a pivot chart I could do that too let's go with pivot table report we're going to Pivot some things up and we're going to once we do that we are going to take it in and make a dashboard now we don't have a ton of of time and I could trust me the designer and me could spend hours and hours and hours working on that which is why I'm going to show you the completed one and you are able to do that oops I think always pay attention to the last section I got chatty um so we do not want to do the existing worksheet right we don't want to drop it right in here because we've already got other stuff going on that wouldn't work so want to make sure you don't rush through this window go ahead we want to click on that new worksheet so we can stick all of our stuff easily over there so excel's always a good spot take a second take a little pause lots of things to click make sure you got the right ones so we're going to go to a new worksheet with that and we're going to hit okay and here we go all of our data we can see is loaded here in our pivot table fields we got our fun little pivot set up now with our pivot table if yours looks a little bit different than mine that's okay you can change the look of your pivot table right here in this little gear icon so if you want to change whether it's on the side your areas or on the bottom some people have a preference for this you're able to move that around really easily with that gear icon now for this one before we get into anything I want to bring over our completed file because I want to show you kind of the end goal let me grab that end completed file and pull that over so we're not going to build the entire thing but I'm going to show you some of the elements on here so if you want to rebuild this on your own you are able to so here's all of our nice handy little candle data this is all pulling from that cleaned up data and we can see we have some we have a logo pulled in we can have some slicers working off of that data we can have these look like cards but this data is actually just pulling into a cell we can pull in you know different elements you can see the data that's pulling over for each of those things and that's just grabbing elements from our pivot table to have this final easy to view output that is really when we're thinking about dashboard design we need to be thinking about how can we make it easy for our end user sometimes yes at end users ourself to understand and see the data we need at a glance we need to make it really clear and easy to do that I actually have a series on here about report design in powerbi but the concepts are the same the structuring of your report really should be the same the way people's eyes move through content that works the same whether you're building a report in Excel prbi Tableau anywhere else you may be doing your report building those kind of dashboard Design Elements stay in play so we've got a bunch of different elements on here and let's go through and we're going to do a few of these again you're able to click on any of these and kind of see that end output but we're going to do a few together I saw a fantastic question in the chat which was why do you not just use powerbi well that is a question I ask all the time too because I do love powerbi I love working in it but a lot of people do not have powerbi a lot of people have never used powerbi they don't have access to it no one else in their company uses it um so if they were building a report it would really just be for themselves so many people get feedback hey like I like to use powerbi it would seems cool but everyone of my organizations on Excel I have to show this in Excel um so I always like to say hey there's a lot you can do yes powerbi was built for visualizations you know it's an offshoot from Excel to work and create visualizations to make that process easier because sometimes when you're building things in Excel you got to jump through a few Hoops it may not be the most intuitive but you can still get really great things done if you do indeed want to or need to build and create an Excel so let's go through a few different options some things um in the way that I build it on here so you can get some good little dashboard design ideas now first off for our pivot table any data that you want to have connected that hey if I'm clicking on the decorative slicer I want my last year sales to only show me the decorative candle categories if I clicking on 2022 in my slicer I want to make sure that I have just you know the proper things showing here and there so if you want to do that you have to make sure that whatever data you want to show is showing up in the pivot table and then we can build charts off of the pivot table and slicers and the like so for my pivot table we can go through um and we can add in the elements that we want to for this so I'm going to add in things like I want to see let's do my total sales can go into values like the unit price could go into values I want to see product names um I want to see what else can we look at we can see the month name so I can see when certain things were sold and no worry we can readjust all of this my goal is not really to make a super beautiful pivot table here I have a video on that if you want to see that my go essentially really get our content in here so I have the elements in place that I need so if I want to we can readjust and move our month name or if I want to you know into my columns let's say I'm going to put my year over into my columns I don't want my sum of my ear I want my ear on my columns so you can easily grab move around all of your elements in there but now I can see hey based on the year here I can find the different elements so I can scroll down and I've got you know Grand totals in all of these elements and I can pull this amount and then use that in the card and so that's how I got all the values I didn't have to write anything I just had my pivot table create all of these things for me and get me the data that I needed so we've got our total sales by the different products by the different months um and we can utilize all of these elements now into our final view now before I ever design a dashboard before I ever start putting visuals onto the page I will always make a wireframe this could be from my like background and web design starting there you always want to think through what am I going to build draw it out and now you don't necessarily have to do in Excel but we're in Excel in Das makes sense to do it here right but you can grab like a sticky note and draw in there you can draw it on your whiteboard and literally whiteboard out some ideas just a simple piece of paper a lot of times I'll also use like a PowerPoint slide and just put little blocks where I need things to be but kind of once you've thought out where it is then it's super easy to go through on your actual Excel page and Mark out the sections and all I did for this was select selct the sections that I wanted to be something and then I added a border to them so I can put in all of these borders I normally do that thick outside border one when I'm doing this and that way you can see hey let's put some slicers Let's Line things up make sure things are even and organized so that way when we are doing our final version you got some of that blank page paralysis taken away where you're like all right I know I have this skeleton of where I'm going to place elements I know things are going to fit I know exactly what I need to then go and get datawise or design wise or element wise for this so then super easy all you have to do is duplicate that wire frame so move or copy and then we are going to create a copy move it to the end and then this is going to be our dashboard dashboard so here on this dashboard one we are able to go through and do all of the changes that we need to so first off before we do anything I think back to front so I'm thinking what is going to be in the very very back in the background then moving forward into layers like am I going to have different colors in different sections am I going to use a rectangle like just put in in your insert tab your insert tab is going to be your really good friend as you're doing your dashboard design so here in my insert tab am I going to use a shape like a square rectangle for my background or am I just going to change my cell colors you know am I going to actually be using my cells to create things and if I'm using my cells to create to create things well then I'm going to need to be able to have access to that back layered actually touch the cells so generally if I'm going to do a background I'm just changing the cell color for that there are some spaces where I'll do a shape so if you look at the completed version over here I have added a shape behind these elements to really help that stand out now when you're doing dashboard design you want to think left to right top to bottom so most of the time you want to put your kind of navigation elements uh your slice things like that either on the left side or along the top or kind of both that's going to be where people are looking you don't want to put a slicer at the bottom right corner no one's looking over the bottom right for their slicer they want to see where can I do that and have it nice and organized so if you think hey when am I navigating around on things like a website and something makes sense well your navigation is generally at the top there so it's really easier fine thing that logo is normally going to be in that top left corner so mimicking the behavior that people are already expecting that they're already trained to understand and use it's going to just make it easier for people to understand and use your dashboard so for this one if I'm going to change the color of everything then you can click that little tiny triangle right in that top corner to select all of your cells and then I am going to change my background color for this I'm also going to get rid of my grid lines now when I'm building I'll keep my grid lines on but then I'll normally turn them off after I'm kind of through my GD process so that way I know everything's aligned especially if I already have drawn out all the lines myself I don't need my grid lines on too so with all that selected I'm just going to go over to home and then I'm going to just use my little Paint Bucket to fill in all of these cells so color theory wise this would really be going off of hey what colors make sense for your organization for your specific dashboard that you're doing um and you also want to think about legibility and accessibility one of our other trainers here Greg has done an amazing series that he's still working through so you get lots more episodes of um and he's going through accessibility in Universal Design and so there's so many good principles in understanding making sure you're color contrast is good because what point is there to build something if some people can't use it right if you're doing this and you want people to understand your information you want to make sure it's displayed in a way that they can get that so I like to have a dark background for kind of the very back of the report I'm going to go with this dark shade and then I'm going to go with some lighter elements so I'm also going to then make kind of a dashboard is rectangle shape of course this really does depend a bit on your screen size but I'm going to kind of contain everything in about like this and make this just like a little bit lighter so that way I have a bit of kind of contrast between the two and it doesn't look like it's you know going on forever next up with this one one of the elements that I did is I pulled over the um cards for this but I did a little cheater method for making the cards because I wanted just my text to be right here so to make one of these cards all I did for this I did a merge and center for all of these cells then it's going to give me a little alert that it's going to only take that you know one element yep that's okay and then for this I was then able to change my my cell formatting for this to add a gradient and a border to the cells so with that that is saved in that finished file as well but you can see you have a cell style Tab and I have this custom one right here with custom ones you all you have to do is go to new cell style and you can easily modify it you can also modify any custom ones by just going to this modify button and you can go and adjust the formatting of anything but in there that allowed me to add a gradient to a cell background and then all I had to do to get the element I need in here super easy what did we start with very very first thing that we were looking for an equal sign so we are going to say hey in this one I want to go find last year's sales that's the card the value I want showing right here so to go get last year's sales I'm going to do my equal sign I'm going to hop over to the candle table that's my pivot table right here so I went to see last year sales so here is my sum of total sales for 2022 so for 2022 here is my total sales for 2022 so skipping over it right here this 3,200 amount so I'm going to click on the sell and you can see right here it says get pivot data so it's pulling it from here the sum of total sales and it's pulling from we can see that locked down um A1 column year 2022 so I'm going to hit enter now this is really important if you're going to another location like another sheet to get your data you don't want to click back to the original one before you hit enter or things are going to break so we want to hit enter and that is going to pull that over and now I have my value and to have kind of that heading that title all I had to do at the top was do a text box so in you're insert all you are doing is going to my text here's my text and then you're going to grab a text box and you can I normally will start it outside so I get the box and then I'll resize it so it fits inside of the location I'm going then here what do I want this is last year sales so you can easily write what you want and put this in and this is the same process I did for all four of those and you can see that in the completed file and of course you can bump up your text sizes to get it to fit um exactly the way and look you want it to be logo that's pulled in as an image again go to that insert ribbon grab a picture and that's going to let you navigate I always do place over cells rather than in cells that way it's not really tie to your grid size so place over cells stick a logo image put that company logo in there and that's going to fit that in last thing I'm going to show you is slicers so charts slicers all of these are coming again from that candle table so in your kind of with your pivot table making sure you have this up and this selected right here in your insert ribbon what do we have we've got a little slicer right here so we need to make sure we have the data in here that we want to be accessible for our slicers and then here on the same page with my pivot table I'm going to click on slicers it's going to show me all of the data I have access to that I might need and I can just pick so let's see one of those is the year and I'm going to hit okay and it's going to give me a year slicer now all I have to do to use it is do a contrl c to copy go to that final dashboard contrl V and then just move it over to exactly where I want it to be and that's the same process boom that you get to follow for those other slicers and that is how you fill out your report so dashboard design it can seem intimidating at first when you have a lot of elements and you're like how do I go through every single one it can seem tedious breaking it down into a process for like all the same things start back to front get that background in move do individual cell coloring options if you need to and then just Place those elements in pull from that pivot table pull from that cleaned up data and make sure that you have that set up little tip Maps currently don't it's really a pain so if you have any map data you will need to kind of copy out anything that you need past that into a map of course makes it not really accessible to slicers but you can get some good aspects from doing that as well so you'll be able to use that completed file if you want to rebuild this of course if you do have more questions related to dashboard design I could talk about this all the time so when join me in the boot camp but also join me next week when we go through um and do a recap of questions because you guys have been so active in the chat which is fantastic and have a ton of time to look through all of those so I will be going through checking out all of these specific questions in there and pulling a bunch of them that we can cover and go over in this kind of recap session for Excel but what I hope for all of you is that you're just feeling more confident in your Excel Journey having gotten a few tips whether it's new shortcuts you didn't know or just being more confident in your formula writing or power query usage so drop in the chat what you've learned and hopefully we'll see you for that question section and then also we do learn with their pretty much every single month and so I know we've got a promo for the next one so you can see exactly what that one is going to be and we will see you [Music] then [Music] [Music] [Music]

Channel: Pragmatic Works

Views: 35,505

Keywords: microsoft excel, excel, spreadsheet, data analysis, microsoft power bi, pragmatic works, allison gonzalez, data analysis and interpretation, google sheets, how to use excel, ms excel, excel basics, ms excel tutorial, excel tutorial, excel for beginners tutorial, exsel, advanced excel functions, excel training, excel full tutorial, power query, dashboard design, microsoft excel world championship, microsoft excel esports, excel full course, excel tutoring, excel 2024, excell

Id: dxKK3rbubvo

Length: 90min 55sec (5455 seconds)

Published: Thu Jan 11 2024