Transcripts
1. Welcome to Excel Essentials Masterclass: Excel is used all around a business, but how
many people do you know that are actually confident using it? Not many, right? And you may
feel you’re one of them. This class is going to change that and make you stand out using the
world’s most popular business application! I’m Dan Stockdale - Welcome to Excel Essentials
Masterclass. I’m a former PwC Excel and Financial Modelling Instructor and Consultant, and I also
have a background as a Commercial Analyst and qualified Management Accountant. I now run
my online training business Claritix.co.uk, creating courses and content, for all abilities,
in Excel, Financial Modelling and related areas, and I’m always developing new free content, such
as my Top 10 Excel Tips eBook or videos through my LinkedIn and YouTube. Aside from PwC, I’ve worked
with companies such Conde Nast, the BBC, DAZN and WPP, helping Execs doing everything from launch
TV channels, expand into new markets, secure 10-figure funding and acquire cinema chains. So
my experience is varied, but the one thing that it all has in common is Excel. This class will teach
a Beginner or Intermediate the most useful Excel knowledge in under two hours - maximising your
learning. After the Class you’ll work faster using keyboard shortcuts, have a strong command of
5 essential functions to create calculations with and build robust, functional spreadsheets. You’ll
be confident using Pivot Tables to work with large amounts of data, make your work look professional
with formatting, and you’ll have plenty of Pro tips to share with your colleagues! I’ll start
the Class by taking you through 10 Quick Tips to help you work faster and more efficiently in the
Excel Environment. We’ll then move onto my Top Keyboard Shortcuts – navigational shortcuts
to get you around your spreadsheet faster, and action shortcuts to perform repetitive tasks
quicker. Next will be the five essential functions that you really need to know – we’ll look at
SUM to add and summarise ranges of numbers, VLOOKUP and Excel’s new XLOOKUP functions to
return data, the IF function to incorporate logic into your calculations and SUMIFS to summarise
financials using criteria. And you won’t just learn how they work, you’ll see them applied in
a variety of ways. I’ll then show you how to use Pivot Tables to summarise, slice and dice large
amounts of data super fast – they’re going to make your life much easier! Lastly, formatting –
if your work isn’t presented well then people just aren’t going to trust it. We’ll cover the basics,
where to go to explore more Advanced options and 3 ways to add the finishing touches. But it won’t
all be me talking and you listening – I’ve created a really awesome Project for you to complete
as you go along. You’ll be meeting Federico, the owner of a small cinema chain who needs your
help applying your new knowledge to a range of data and scenarios to improve his business. It’s
going to be challenging but not overwhelming, and will give you the opportunity to immediately
put your skills to use in something fun that we can all relate to - the movies! This class is
packed full of quick wins that will transform people’s work in no time at all, and that’s
why I love it! And I’ll tell you something - In 2010 I started a new job as a Finance Analyst
that required me to know a lot about Excel. And I didn’t have a clue. I had to teach myself.
By 2016 I was teaching it at PwC - one of the world’s top professional services firms. It goes
to show that every minute you spend here is a minute invested in yourself and a minute invested
in your future. So have fun and learn loads!
2. Project & Class Overview: So the point that this class is of course, to start mastering Excel. To do that, I'm gonna be teaching lots my best knowledge without the lessons. And as you go you have projects complete seeing stock pricing where you just learn. It's let me take a moment to introduce the project by setting the scene. You'll meet Federico. Federico then is a small chain of three cinemas in Santa Barbara, California. The lighthouse baseline pitcher house, and shoreline theatre. He differentiates itself from Laker competition by Shane classic blockbusters rather than the latest releases. And since opening two years ago, the business has proved a real successful locals. Federico has a problem known as the business has expanded. He feels his lost touch with some aspects of it. Losses data had been collected, his team is growing bigger and some reporting is being produced. However, he doesn't really understand the numbers. He's not sure if you stuff performing well enough. And often the information produced just doesn't appear very engaging. Striding brief things federico has reached out to you, a freelance Excel consultant to apply your skills for a number of tasks he needs help with. You find each of those tasks in a downloadable XL workbook. He's promised if he can complete each of the tasks, then they'll always be a free sci fi along with unlimited popcorn. Whenever you're in town, it's the good luck of a C. There's Neitzsche free popcorn. This is all made up. Anyway. There's projects is great because it frames are learning in a business we all understand and enjoy the cinema. And whilst learning Excel knowledge is great. It's so, so important to learn how to actually apply as well. And once he might find some aspects that challenging if you get stuck, there's a downloadable solution file. Now, to briefly explain what you can expect from the lessons, I'll start each with 22nd intro that sets the scene for what you're about to learn and anything to watch out for. I'll then demoed atopic, explaining and applying the learning and throughout our throwing my advice thoughts and plenty protests. And I've learned over the years, you notice that there's lots of keyboard shortcuts in this course, and I'll be explaining keystrokes for both Windows and Mac when I use them. I'll end each lesson with a quick twin secondary cap and the key take outs, actions to take. And briefly what's coming up in the next lesson. My final advice if we start is come with an open mind. Be ready to learn. And even if you think you know, subject is still well worth watching video through as I'll be throwing and plenty of additional tips and advice, right? I've talked enough. Let's dive into the learning and start transforming your skills.
3. Quick Wins: 10 Tips: Okay, so we're gonna kick off a ten quick fire tips. These are predominantly bits advice though happy work fast and Excel. I'll also be showing you how to massively reduced file size, increase your screen real state, overcome printing problems, and even hide wealth from your colleagues really well. Let's get. First up is one of my favorite tips. And it's if you have a large file such as this, which is over a 100 megabytes, but yours could be say, 30 or 40 or 50 megabytes. Then a quick and easy way to massively reduce file size, which was a disk space. And more importantly, time when opening and saving the file, to simply go to File, Save As, and change the file type from the default XLS x to x plus b and hit save. In this case, it would use the fossilized just six megabytes. But the second one would be this big, and it will depend on the size and the contents of the file. Next up, if you know, maximize your screen real estate, then you can actually collapse this ribbon just by clicking this upwards arrow and removes it from site. You can then access again just by clicking on any of the tabs. And if you click away from it, it collapses to keep their permit to going and then just pin it and it'll stay there. Apparently, I'm gonna collapse it again. The next tip comes down to personal preference. For me, I can't stand having grid lines. I think they look really messy, so I always remove them and you can do that just by going to View and untaken Gridlines. I just think that looks more professional, smart, untidy. The next tip is to use this, the Quick Access Toolbar. So if you have the command in the ribbon, such as untaken gridlines which I just showed you. Then, rather than having to go here and the Nantucket, you can actually add the command to the Quick Access Toolbar. There's two ways of doing it. You can either click this down arrow and go to more commands, or you can go to file options and then Quick Access Toolbar. From here I can click this drop down and pick one of the ribbon tabs. In this case, I'll pick the View tab. And then you've got all the different commands here. I'm looking for show grid lines. I can click that and add it. And then I can actually reorder it. If I move it, then if I press OK, I'm going to find the grid lines here, so then I can use it to toggle the gridlines. There's something in here that I don't like. I can go back and engage more commands. And for instance, this I can remove and then press OK. And we lost that. Next up. If I've got a report by wanna print out on two different pages, then if I went to File Print, then you can see the printing format isn't quite right. Second report here, and this, the second page is being missed off here. So it get round that I can go to either view and page preview, or I can actually go down to Page Break Preview here. Now from there, I can take this dotted line and just move it over. It tends solid. And now if I gave you print. Then it prints, as I went to the next tip, follows on from the last. And it's if I was to switch back to the new movie, then you may have found before, if you've used Page Break Preview, you get this line which represents the page break. And it can be really tricky to get rid of if you were to save the file, close it, and then reopen, it, will get rid of it. We don't really want to have to do that. So instead, we're gonna go to File Options advanced, and then we can scroll down to display options for this worksheet and untick show page breaks. The page breaks still exist. They just don't show there. So we can go to File Print, and it's still going to print how we want it. Next up. If I've got a worksheet and my workbook that I don't want b to c, then I can right-click on it and select Hide and it's hidden. But if that person was, then right-click here, they can unhide sheets and they can find it and see again. If I really don't want to see it and I can press out f 11. And then we have old worksheets in the workbook here. This is the sheet I want to hide. I can go down to here and click sheet very hidden. Now if I close that, It's now hidden. And if I right-click, there's nothing to unhide. Next up, if I hold the left mouse button down on worksheet, a tiny little worksheets symbol appears next to the pointer. I can then move the worksheet. If I keep ONE down and then hold down Control, a to10 plus sign appears. And then I can drag this anywhere and it will create a copy of the worksheet. I could then hold shift click sheet one, go back to the original left-click control, and then copy them all. Next, lots of worksheets in my workbook, then it can take awhile to navigate around them. If I right-click here, then I can see a list of all the sheets and I can select one to actually then see the first sheets again, I can hold Control and presses left arrow. Finally, if I've got a spreadsheet or report, I wanna send someone and it's got lots of calculations in it. And I don't want them Seed calculations or change them or Amanda report, then I can hard code or hard paste with calculations in the spreadsheet. To do that, select the first worksheet, hold Shift, slit the last dislikes Gould worksheets. Then I can click on this square to the left of a in above one and this legs all the cells. If I then press control C to copy and then control V to paste special, the valleys and, okay, and it now changes to the former in the spreadsheet values. So I can go back. And these are all just numbers. There are lots of him tips and tricks to working faster and better in Excel here of the shinny ten, we're going to see loads more throughout lessons. And they weren't always relate subjects the lesson. You never know what you're about to find out. For now. Why not sap your Quick Access toolbar with some of the commands from the ribbon you use vice regularly. It's an easy first step, working more efficiently. Next up, I'll be taking you through the Tukey Bushnell, began to rapidly speed up your work.
4. Working Fast: Navigation Shortcuts: In this lesson, we're gonna be talking keyboard shortcuts. Specifically, shortcuts that help us navigate around our worksheet. If every time you want to do something, we have to reach for a mouse, click it, drag it, and these back to the keyboard. And we're wasting time that could be better spent elsewhere, like check-ins, Graeme, We've got celestial cuts for our makes repetitive tasks are the way that wasted time really dogs up. And I'll show cosmos laid down first as you get used to them. Remember this, you developing my skills, meaning once you've got you don't have to give any conscious thought when you use them a lot. Riding bikes will see plenty of keyboard shortcuts. And of course, in this lesson we'll start with my top five. That'll get you around excel really fast. Okay, so for the first of the navigation shortcuts, I'm just gonna show you how we can navigate through these blue cells in numerical order, quickest. So wanna get from the one to the two to the three, might run to the seven. Now, normally of course I could do with the arrow keys, so I can just press right. And we get round all the cells kind of quickly enough. But sometimes we will have a much larger range or many more cells and we can just do a lot quicker if we use a shortcut. So if I'm in this cell, then what I want to do is press Control and arrow keys to jump me to the next or last non-blank cell. And so in this case, if I hold Control and press right, I jumped the next non-blank cell, this the blank cell, this a non-blacks OCC has contents in it as a two. If I press control right again, we jumped to the three control down to the fourth, then left is to the five. Now, these are non-blank cells. So the next time I press control left is going to jump to the last non-blank, so which is the 6 first. And I press down. So you can see we can actually get memory fast. The second chunk I want to show you is how we can extend our selection of cells to select a group cells. Or we'd normally left click with the mouse and then just drag. If I hold Shift and press right. You can see this kind of highlighting the cells is selecting them. And we get to the eight and I can just hold Shift and press down. And we select the whole range. Now to do that slightly faster, we can combine the last few shortcuts that had just seen. So if I was to start in one and hold, Shift and control and then press right. We jumped to the two. We actually selective thing at the same time. And if I keep holding, shifting controlled press down, we jump down to the three. We've selected all that by c into keystrokes. So control shift, right? And then control shift down. Now what's important to note here is we can see these rules darkly colored that then this one, it's a lighter shading or there's no shading. And that's because this is the active cell and we can tell the active cell exists, listed here is F2 and this is f 29. When we do the control and an arrow key, it jumps from the active cell to the next cell. And in this case, in the second step we jumped from we did control shift, right? And then down to jump from one to three. And that's important because in this next example, if i again when to select the same range, then I press control shift, right? And if I press down again, It's actually taking us to the bottom. And that's because there is nothing below the one. These are all blank cells even in this text here, that she belongs in this cell. So the quick way to do this is if I hold Control right, we jump to the two. And they say we want to reverse this. So now press Shift Control left and then down. So it jumps here and the negative. So we can then jump down to the three. The next World Cup is page up and page down. If I press page up, we basically jump the active cell up a page. So page being essentially this many cells. So if I start here and press page down, we get to the bottom of the worksheet pretty quickly. There's just another quick way of getting up and down the lead sheet. And then finally, if I want to cycle through the worksheets in this workbook, we've just got two worksheets. But if I hold Control and press page down, we cycle right to the action worksheet. If I press control page up, we cycle back to navigation. Thus navigation shortcuts. Okay, so that was our first law. Shortcuts start using this scene. He gets the project and carry on, use them in your work. In particular, the control shift in our actual costs. You'll be using them a lot. See you enough that become second nature. That's what we've seen will help us navigate around fast. Next, we're going to see five shortcuts that's needed in Gregory productions. Again, these are gonna save you a lot of time. He watching.
5. Working Fast: Action Shortcuts: So in the last lesson, we took a look at the key navigation shortcuts. They'll guess around XL superfast. In this lesson, we're going to look at the key action shortcuts. They're gonna speed up how much repetitive tasks and are an absolute must if you want to start getting good at Excel. My advice, pay particular attention to the pay special shore current options. You're going to use a lot. So I'm gonna start the action shortcuts with a really simple one. And let's copy and paste. Say if I was to slightly cells and press control C, then we've copied them. And we can see that by the dashed line around the cells. If I now press control v over here, then we've pasted divert the actual value, the 0.7, for instance, the formatting of the number, which is the decimal, whereas before its percentage and the highlighting of the cell. So basically everything in this cell, the numbers and the formatting hasn't been copied over. If I just wanted to copy over the number itself, but retain this formatting so the blue and the percentage, then I can select these cells, press control C to copy. And then if I press control v, we bring up the Paste Special dialog box. And this just gives us lots of different options for how we paste what we've just copied. And so I'm going to run through some of those options under the shortcut. So for instance, here we want to place i've just the values. You can see values here and it has an unlined v. So if I press V and then OK, we've taken over the numbers, for instance, the 0.7, we see as the percentage, and this has been retained as blue. Another option that we have there is if I wanted to paste over the formatting but not the numbers, so retain the 96% as a decimal. And I can highlight the cells Control-C, then control OK, v. And then it's formatting or formats we wanna copy over so we can see the underline T. So I press T And then, okay, see we've taken over the green and the decimal formatting. We've actually kept the number which was previously ninety-six percent. So next up here, we have some formulas. Now when we're doing the values, it's just a hard coded value that we'd actually paste over. Here. I actually want to take the formula itself. So this is actually referencing other cells. If I copy this, control c and control v, then f for formulas and press ok. See this again, this is in pounds and it's blue. But we've got a formula in here now. So if I press F2 and I can actually see which cells are referenced in this formula. So we've actually taken over the formula, whereas previously this was kind of just hardcoded figures. So next up, the last option I'm going to show you is transposing to go these green cells and they're in a row. If I press control C to copy, and then if I press control v, then down here we've got the Transpose option. So if I press that, we've taken what's here and then we've just kind of rotated it 90 degrees. So now I've got the 250 here. And it goes up to the 1250, which is down here. But you can see again, we've taken the actual formatting. If I press Control Z, I'm undoing the last thing I've just done. If I press control, why I'm redoing it for price controls that again, if I'm done it, I go here, press Control-C Copy, and then control over V. I can hit E for transpose, but also the values for k. And we've taken these values and we've turned them 90 degrees, but we've retained the number formatting and the highlight color as well. So the next option is going to be find and replace. So just go fee simple cells here. January, February, January. If we had a report, for instance, we might have a monthly report and we repeat the word January. I can find the wet January by pressing control F four, find, it brings up the fun replaced dialog box. This is find what? If I type in January and press F9 next? It will show me that January for press enter again to find next. It takes me to the other one. And then I've got it here as well, actually in the text. If I wanted to change these January's to February, I could highlight these figures and then I can get to replace. Or if I didn't have this open, I can press Control H and bring up the funding replace dialog box again on the Replace tab. And I can type in, for instance, February because we're updating of January report to a February ripple. And then I can replace all. If I press replace all, it's going to find all instances, instances of January in the selected cells and changed them. And says UDN, we made two replacements, those two January's we changed them to February. Press OK. But it hasn't changed this January because we went selecting this cell. If I select just one cell and I do the replace all again, he's gonna find that January here and it's changed it to February. Ok, so finally, I'm going to show you quite cool little trick if you've got formula and I'm going to zoom out slightly. So in these cells we have some formula. I'm gonna press F2 and you can see the reference, these gray cells around them. Now there is actually an error here. Say, if I was to press Control and this little dash is usually below the escape key. Then I change my spreadsheet into a formula view. So if I zoom out again, if I now just press right, then the cells that this references in this formula are shown. And then moving as we move around the cells, we can see where our formula is referencing. And you'll notice in a moment that looks odd. So this red cell should have moved here. It's just way of fussy kind of auditing formula. We look at, say, understanding the cells that references and, and it's just very visual. So you can see actually as you move around the formula which sells the formulas referencing and say if I press the actual code again, so control and the load dash then actually returns the previous format and changes back the column widths. Have you found something that you'd like, guys, it's crucially thought using the straightway and don't avoid them. They can have a big impact in your work promises. And then over time, try to build up your repertoire emotional Casino, the faster you work will be. And include the link to the Microsoft website that lists tons of shortcuts, both Windows and Mac. So check it out. Next up we're going to dive into the central functions. You've got to know. We'll start with some function you may already be familiar with. I'll show you some shortcuts, different ways to use it. And great technique known as a 3D sum or punchthrough.
6. Essential Function #1: SUM: You may already be familiar with some, he's probably my central function in Excel and one of the simplest. But you familiar with the three, Some will say nine established through. In this lesson I'll begin with a brief word on exiles functional library and have sought Creating Functional calculation. And then show a few applications of some, including cumulative sum. As I do, I'll introduce the concept of ice cream formally. This is something you'll see throughout the functions lessons and he's worried important. I'll then demoed a 3d, some technique I mentioned earlier. It's a real time saver when adding up worksheets. Okay, so what we have here to demonstrate the sum function is kind of an overly simplistic income statement of an imaginary International's bar business. And so is this bar business that operates over several continents. And what we see here is kind of global summary of all those concerns to have total revenue, total cost, profit, and then we'll have a cumulative profit here, which is basically just the profit added up over time. We're going to start with the sum function. But before I do just a word on kind of functions or the functions in Excel. You can find if we go to the Formulas ribbon. And then here we have the function library. So we have all the functions arranged into different types. So for instance, in Matson trick, go down to the bottom. We've got some here, also got some Fs, which we're gonna see later. If I look in Lookup and Reference, then we, for instance, got VLookup and x lookup, which are both functions will also look at in this course. Now what is also useful if you really need to excel can be this insert function. I can type a description of what I wanted to do. So if I wanted to add values, I can enter that. And then it just suggests some functions. So for instance some or if I wanted to look up data, it gives me some suggestions like VLookup, next lookup, etcetera. Say with for instance, some, forget about there. If I double click on that, it starts to create a function for me. And then I can actually just select cells that I want to add to that function. And if I was then press OK, it's actually created this function here, which we can see. It's not exactly what we want to do there, but that's just to give you a demo of how you could use that to enter a function. Now the way I would normally enter function, it's just by typing out. So a function will always start with an equal sign. And then you type, start typing the name of the function as He do. It will bring up some suggestions. So I've just typed SU there and so we can see some here. So I can either either click on that complete function name or you can just press the down arrow. And then if I press tab, it will complete the function name and the opening bracket. And now from here, I can then select the cells I want sum together. So I've got a few different options here. I could select a range of cells. I could select a cell and then a non-adjacent cell by holding, controlling clicking second cell. Or I could select a range and then non, non-adjacent range, and then a non-adjacent cell, et cetera. And from here, I can either close the bracket and then presenter or actually I didn't even need close bracket. I can just press enter and we complete that sum. I can show you by pressing F2 and stepping into the formula. Cells were referenced and you'll see these first cells have blue highlighting and blue border. And then you see the cell reference here in blue. And you have read and then purple veggies helps you understand visually which part of the function is referencing which sells. So if I just delete that, so I'm just gonna use some to demonstrate how we can summarize some of this information. So if I start by entering some and then I'm going to select this range of cells, presenter Control-C to copy shift, right? And then control V to paste. Whenever we enter a calculation of function like this, it's worth double checking our totals. So at the bottom here, it will sum the cells which is selected. So we get 2x 8.9 is the sum of all these cells. It's worth just double-checking that we're capturing all the figures to first select those cells and we get the same 2.968 now when dissimilar below. So I could copy these cells by just want to show you another way. If I go down here, I can actually just press alt equals and it automatically creates a sum function which references the cells immediately above. I press enter and then I could copy this across as before. Or foreign is city that even quicker. I could press Shift, right? And then navigate down here, I want to calculate profit, which will be the revenue plus the cost. And to sum. And then I am picking two non adjacent cells. So I click the first one, hold control and click the second, press enter, and we have the profit there. And so again, we can double-check that. Next up, I want to calculate the cumulative profit. So I can do that by entering some, sliding this cell and then pressing Colon and it repeats the cell reference. If I close the bracket and then copy the this across, then you'll see the cell reference just shifts as we copy this cross, the short-sell reference will shift cross as well. Now, I can solve that by anchoring the column, this first cell reference. I do that by entering a dollar sign, which means that that J is going to be there, said the column weight shift on the first cell reference, but it will in a second. So if I copy that to the right, you can see the first reference. The first cell reference is J2, J3, and then the second is K23. So if I then copy, that's the end. We get Kinsey revenue of 45.7, which equals all of these added together. So that's correct. Now, I'm going to summarize these here by just copying this across. And then I need this cumulative profit here because the anchoring won't be correct. Now, if I select g, then g here, and then copy that 45.7, and I need to delete these figures. And instead I'm going to use some here. We're going to see a better way to do this later with some ifs. If I select that, I can now just drag this to the second year. And then I'll copy these figures down. And that is using some to summarize these figures. Now the final thing I wanted to show you here is that these figures were referencing J 13 in each of these tabs. So if I was to get any of these Tabs, J 13 is here, the UDL identical structure. Now, the way we've done that is basically entering a sum and then gone to each of those worksheets and pick J 13. And that's fine because any five worksheets here, if this was actually countries and there was a 50 or a 100 countries there now take really long time and there's a quick week, we can do it. And that's using what some people would call a punch through. And it's simply this. If I put equals sum, then if I click J 13 In this first worksheet, and then hold shift and click the last worksheet. Then you see we get N America, Coulomb aipac J 13. And what this does is sums J 13 in each of these worksheets. Copy that, and then copy that down here. Then we have summed the same cell in each of these worksheets. That's a big time saver. But there is a risk that if I did this, I went to any worksheets and for instance, and added a new row. Then all of sudden this has changed because it's still summing J 13 in each of those worksheets. In the North America or in America worksheet, that is J 13. And it doesn't shift as the shifts down. This one to be aware of. If I was to delete that with control minus k back, then we've got the right total again. So as you can see, some is a pretty simple function when you use a lot. And there's a few different applications and shortcuts associated with it. And show you understood the anchoring technique I used to create the cognitive profit. If you didn't, don't worry. We'll be seeing more of it throughout the course. For now, complete the short, some exercises in the project and try using some of the shortcuts we learned earlier. In the next lesson, we'll look at VLookup, one of Excel's most popular ammonia in functions. So keep watching.
7. Essential Function #2: VLOOKUP: At some point, you're going to want to look up data and access most popular function. To do this. We're going to learn the basics of using VLookup value. How to values can be combined and then look at how to create some simple mapping. For this demo, I've got a really simple dataset. It's for different types of jackets, a trench leather, denim and swayed. Two types, customer, women and men. And then the price of the jackets, the units sold, and the money. And we're gonna start with question one, which asks, what is the price of a women's Dannon? Now I can just see here by scanning the data that Dan in women is a 110. I'm going to show you how you can use a lookup to look that up. But before I start with a VLookup, I'm going to just show you how we can enter a drop-down list here so we can look up the type of jacket. So with that, so selected if I go to Data, and then here we have data validation, select that. And then in allow this drop down here, I select list than insource. Who's going to drag it over these four types of Jackie here and press OK. And now we have a drop-down and we can select denim. And here I'm going to put the VLookup. So I'm gonna start by talking out VLookup. You can see Europe is here. If I press tab, then we enter into the function and here we have function arguments guidance to sue come up. Anytime you enter a function, it can't just gives you advice on what inputs you need to be putting in more order. I've also got it listed up here. So here we start with the lookup value. In this case, that's going to be the denim. Next we need to put a comma. Here you can see it's now asking for table array. Table array is the dataset. Where are we going to firstly look this up and then also return a value. So in this case, I'm going to select all these Hells here. The first column of table array has to be where you find this. So it's always going to look up the lookup value in the first column and then put another comma. And then it's asking for a column index number. This is saying once it's found where denim is, how many columns across Does it need to look to actually return what we're after, which is the price. And so in this case it's 123 columns. So I'm gonna put three in here and then a comma. And then it asks for either true approximate match or an exact match. Almost always you're gonna put false. So if I talking folks here, closed bracket and press enter or VLookup is done. It's taken the lookup value denim, and it searched for in the first column of the table array is founded here and it's returned three columns across. So 123, it's written a 110. It's important to note that it always starts from the top and searches down to this data. And now I wanna point out risk here. And that's the here we hard coded a three into the function. Now, see what happens if I enter a column here. It was a 110 is now 0. And that's because this dose is three. The table array still looking up then in the next searching 123. And so we've changed the table array and this is now shift the prices shifted into the fourth column. The three is hard-coded so it doesn't change. And that's the problem people often have with VLookup and kind of how they use it. There's ways to get around it. You can use a much function. There's alternatives such as index and match. Or x lookup, which we're going to see next. If I undo that. And move on to question two is asking what is the price of amendments suede? Now here we want to look up sweat IID. But if we were to look as before, we'd get to the swayed and return it. So the VLookup lookup, look up the lookup value, find here, and then we return the 16T here, but that's women. But we want to be returning this wait for men. And the way we gotta get roundness is actually to create a lookup Valley which utilizes this weight here and this men and then looks up in the table array. We can't look up from two columns where you get roundness is if I expand this column slightly, I'm just gonna put equals this cell, use an ampersand or enzyme and then select this cell. Basically understand joins pieces of text together. Now we have trench women there. If I drag that down, we now have swayed men and so can actually look up swayed men in this column and then returns something over here. It's gonna put VLookup tab. The lookup value is swayed and the sand, and then the customer here. And then comma, the table right now is gonna take in this first column. And now price is 1234 columns crossing gonna put four, then false. And you can see it returns 140. And now I can change this, for instance, to women. And it looks up the swayed women, which is here, the 16T. Now another thing I wanna point out with this is the V lookup. You look up the lookup value in its first column and you have to return something to the right. It can't be above or below or to the left. So actually if I was to drag this over here, then we get an error. So that is a downside of VLookup. We're gonna see how we can get around that in the next lesson with x lookup. In example two, I'm just gonna give a quick practical application of VLookup or any type of lookup function, and that's for mapping. What Got Here are some sales reps that grade and the cells that live made. And I've got here is the three different types grade and then the commission percentage that those grades earn. All I wanna do is map these numbers based on the grade into this column so that the commission is worked out, which will take the cells multiplied with the commission percentage. I'm simply going to do that by entering a VLookup. And the lookup value is gonna be Manager here, comma, the table array is going to be this. Now I need to anchor this because I'm gonna be copying this down. If I don't anchor it over the dollar signs in, then this will shift down as I copy it down. And I'm just gonna give you an example of that. Actually, the column index number will be two. And on these portfolios at the end, that's fine. Manager is Tim sentences looked up this Thompson here. If I was to copy this down, I'm going to copy the form x. I wanted to retain the format in C. We get errors here. And that's because as we copy this down and she shifts this down now we're not actually looking up Associate anywhere. We need to do is I need to anchor these row numbers and so I can put a dollar sign in front of the 911. I can actually do shortcut F4. If I press F4 once I'm gonna anchor the column and the row, I don't need to anchor the column. If I press it twice, it's just the row, which is what I want. So press it twice here. And then if I press enter, then copy this down, copy the formulas. Then we get this. We can see measure Thompson, Thompson, then for instance, senior associate five. So if I was changed one of these figures say 215%, then it'll update these figs here. Maybe this goes to seven and this goes to 800. And that is an example of mapping. As you can see, VLookup isn't super complicated. You'll probably notice to implant in spreadsheets you encounter work. So it's really important to know and understand. Give it a go back to it in the project exercises before moving on. And in the next lesson we'll look at an alternative, excels new, Excellent.
8. Essential Function #3: XLOOKUP: Relief country like rings 20 x lookup is Exxon's new Swiss army knife of lookup functions. There's a lot we can do in the choice, can even feel a little overwhelming. Excellent cup is kind of a hybrid between VLookup and a more advanced, conceptually more difficult method of looking at data and its index and match. If you don't currently have access to x look up and you will once you've updated to the latest version of Excel. And it's still well worth watching this lesson. As in time, this function will entirely replace VLookup. We'll look at how we can create one and two-way lookups. Return a specific result when lookup value is in pound search using wildcards switched the direction in which x old searches for the lookup value in a range of data and how to use non-exempt matches. So a lot. To demo x Lookup. I'm gonna use this simple dataset and it's the price of flights between knees, US cities. So we've got the root, the economy premium and business class prices. So if question one is asking news x lookup to form a one-way vertical lookup, the business ticket Kansas City to Philadelphia. So by vertical lookup, it's kinda like VLookup. So V and VLookup stands for the school. And so that's where we're going to look up this lookup value in this list and then return something over here. So we can see straight away, Kansas City to Philadelphia is here and the business price is 742. So I'm gonna use x lookup to do that. If I type out x lookup, we got lots of different options in the function arguments advice. We don't actually need to use all of them. But for the simplest nslookup, we're gonna put in and lookup value, which will be this. Then comma. The lookup array is where you're going to find that, which is going to be this list of roots here, another comma, and then the return arrays where we're going to actually find the price that we want to return. And so that's going to be this final column. If I close the brackets there, we get 74 to say it's looked up the root can city to Philadelphia. It's found its here, and then it's returned the sum for two of the associated array over here. Now if I just wrote that, question two asks US X lookup, former one-way Horizontal Lookup of an economy to get Las Vegas to houston. So first off, I'm gonna change this to economy and then Horizontal Lookup. Basically we'll look up value here and then return something below it. So as the equivalent of an HLookup, which is similar to a VLookup. And the VLookup, we look something up and turns them into the right. With an HLookup, we look something up and then returns, I mean, blended with x lookup. We can do both those things. Now, I'll stop by entering X lookup. And then the lookup value will be economy. The lookup array will be the class of travel. And then the returned array will be Las Vegas to Houston. Are close that press enter and we get one 56. The x lookup has found that economy is in the first cell of the lookup array and so is return, the first cell of the return rate, the one 5-6. Okay, so I'm just gonna group those up. And in question three now is a news nslookup to form a two-way lookup of a premium ticket, Minneapolis-St. Pittsburgh. So here, this is where I'm gonna be firstly searching for this in the root. And then I'm going to be searching for this in the class of travel. Now this is where x lookup gets a little bit complicated. So I'm just going to enter the function or the answer and then I'm going to kind of explain what's going on. If I enter x lookup. And my lookup value here is going to be Minneapolis to Pittsburgh. Lookup array will be the roots. Then rather than end to return array kind of as we did before just selecting arrange, IT need to enter another x lookup. And the lookup value here will be Premium. The lookup array here will be the three costs of travel. And then the return array has got to be all the prices. If I close the bracket to that x lookup, then close the bracket to the wide X lookup and press enter. We get the price to 77. So it's looked up. Minneapolis to Pittsburgh is found it here, and then it looks up premium and it's this to 77. Now what is going on here? If I select the x lookup here and I press F9 toggles the values of the formula because the valleys 39 for further one to 277, that's basically this values here. What that x lookup, the second x lookups done is it's searched for premium, here, found it, and then return the array of values associated with that premium. And if you remember for the x lookups we've previously done, we just selected one column of figures for the return R3 here. But now this x lookup is looking for F 33, Minneapolis to Pittsburgh here. It's found it here. And then the returned array, which it's returning from this array here. So returns to 77. Hopefully that made sense, but x lookup can be a bit conceptually difficult when you're using it for two-way lookup. Moving on to question four, saying use extra kept formal one-way lookup for premium ticket on the route San. Now sun doesn't actually exist here, but I'm gonna try and search for anyway. So if I put x lookup, then the lookup Peleus's son. The lookup array will be the roots and then the returned array will be for premium ticket. So e this for close the bracket and press enter, we get an a because sand doesn't exist. Or we can do the lookup in that case, is actually put something in here if not found. So it couldn't find sun. So I can actually enter some text, for instance, not found. And then press enter. And if it's not found in better, I could type that in a cell and then reference it in the formula which is kind of better practices. And I just referenced that. So essentially when we can't fund the lookup value in the lookup array, it'll just return whatever we say in this fourth, fourth part of the function. Now moving on to question five, it says Use x lookup to form a one-way lookup using the wildcard match mode of a premium ticket or root him with Sam. Appreciate there's a lot there. But we're essentially saying is we want to look for a route which just has sand in it and we're going to use a wildcard match mode. So the way we can do this is if I put x Lookup and the lookup value will be sun. I've been ampersand quotation marks and then an asterix in quotation marks. Quotation marks essentially means this is text and the ampersand will join a cell reference to text will have San, and then this wildcard symbol. Now the lookup array will be where we find this. And so it's going to search for San something and see, for instance, San Francisco to Miami. The returned array is going to be the premium prices. I can put comma, skip if not found. So put another comma and then a much Mode I take two which is wildcard character match for close the bracket, then is searched for San something from the start, the finish is found. Here, is returned the 5.1.1. Now with X lookup, I catch you reverse it. So we look up from the bottom to the top. For that, I need to put another comma. And then in search mode, we can put last first, which is a negative one. If I do that, we get five 66. So searched for San something from the bottom to the top is found, here, is returned the 5-6. Finally, you also know that there's a Nashville to San Antonio. If I actually put a wild card in front of this. And the ampersand presenter is now looks up something, San, something. So it started from bottom, found there's a SAN here with something for and something after it returned the figure there. Now, I just want to show you a couple of other options. Index lookup. We have here some prices for, let's say some software. If someone's buying it for between one's ten users and its foot, €599 per user. If it's 1150, then it's 1499, etc. Now if a business comes along and says I want to buy for a 100 users, then a 100 doesn't actually exist here. So how do we look up the price for a 100 users? This is where we can put x lookup. So lookup value's gonna be a 100. The lookup array can be here. And then the returned array will be the price. And then we skip if not found, then a much mode, we can put exact match or next small writen. If I put that and then close the bracket, we get 3199. So essentially what it's done is it's looked up a 100 in this list of numbers, and it can't find it. So it returns the next lowest number, which is 51. 2-5-1 would be bigger. As he can see, extra copies of variables. There's a lot you can do with it and you'll probably sold most of you will. Go ahead and check whether your version of Excel has excellent by talking me into. So if you do then complete the project exercises, if not in continuous, the next lesson, we'll take a look at the cornerstone of logic index o calculations. The If function.
9. Essential Function #4: IF: If is another vaccines most popular functions with if we can start introducing logic into our calculations, you'll see examples of that in a moment. I'll start by briefly explaining what luxury statement is and the outcomes true and false. We then see what parties play in it's important function. And then going to show you a couple of practical applications of why you should avoid nested if statements and how flags can be used. Simplify our calculations. Let's get to it. Before get into the function, I'm going to briefly talk about logical statements. So question one asks, create logical statement five equals five. Stop creating it. We put an equals sign. And then I just simply put five equals five. And because it does five does equal five, excel returns true. Now question two asks create logical statement. Five is greater than ten. And of course that's not the case. Fight isn't greater than ten. So if I create a logical statement and reference these cells, then Excel returns false. So if I was to change this, for instance to 15, it would return true. I'll change that back to five. A question three asks test a logical statement using an if function, return correct if it's true or incorrect if it's false. Say within a function, we have three parts to it. We have a logical test, kind of like what we've seen. And then if it's true, if the result is true, you will get a value of true. Otherwise you get value false. Now in value of true and false, you could put a cell reference or a calculation or a function. So in this case, we're going to create a logical statement. So we'll say five is greater than ten. And if that is the case, we'll return correct. And if not, we'll turn incorrect. So here, the logical test is resulting in false because five is greater than ten. And so it turns the value false, which is f21, which is the incorrect here. And so I could change that for instance to not, right. And then it'll update this. If I change this 215, then the logical test equates to true and say the IF function returns the value if true, which is correct. So an example two, I'm just gonna show you a practical application of the IF function. So we've got here our revenue salespeople. So you've got salespeople here. We've got a grade, their commission percentage, the revenue for each quarter in the net total revenue for the year. In question one asks Catholic commission only for salespersons generating total revenue of at least 200 thousand. So the way I'm gonna do that is I'm going to put an if function. And then the logical test is going to be the person's revenue and whether it's greater than or equal to 200 thousand. And I'm going to anchor the rho here because I'm going to be dragging this formula down. Now the value, if true, will be the commission. Multiplied by the revenue and the value of false will be 0. If I copy that down. You can see we have commissioned calculated for people whose revenue is greater than 200 thousand, is less than 200 thousand, we get nothing. Now a question to ask, calculate commission only if the team's total revenue is at least 2.5 million. So we're saying we still want to test whether the generated over 200 thousand in revenue. Also, the team's revenue needs to be greater than 2.5 million for anyone to get commissioned. This is two different ways we can do this. And the first one I'm going to show you involves a nested if statement. This is where we put one if statement inside another. And it's kind of quite bad practice. It's something to be avoided actually makes things more complicated and increases the likelihood of errors in your work. And so I'm just gonna show you how you do it though. But we'd create an if function here. And I'm going to test whether this is greater than or equal to this. And if I anchor the row here, so I'm going to be dragging this down in a moment. And if it is, then I want to multiply the commission percentage by the revenue. And if it's not, I want to put 0. So we can see we get that answer. If I take this and copy it and actually paste it into the value if true here. And then I'll explain this. So in the first IF function, we're testing whether the person's revenue is greater than 200 thousand. If it is, then we move onto the second test with a secondary function which has a test in it, which tests whether the teams revenue is greater than or equal to 2.5 million. And if it is, we calculate the commission for the percentage for the person. If it's not, then they get no Commission. And for the first IF function, if the person's revenue wasn't greater than 200 thousand in the first place, they're getting 0 commission anyway. Now, you might think that's a little bit tricky, a complicated, and it kind of is, and the bad thing about nested functions is people stopping and another function inside here and then another function inside of that. And things get super, super complicated. Now better, more simpler way of doing this is just go down here and type in a function in test whether this is greater than or equal to the 2.5 million. And if it is, I'm gonna put one if it is not limited to 0. So if the team do generate 2.5 million or more, we get one and which can then multiply this calculation up here by that one. So I'll go up here it just back to what we originally had, which was the commission calculation. And I'm just gonna multiply that by one and rho there. And I'm going to drag this down. You can see we get the same answers as before. But if I change this to 2.6 million, everything gets cancelled out because now the team don't earn 2.6 million or more. And so everyone leaves the Commission. And you can see that's just a simple calculation to before. We've just got one f function in each of these, and we've got one function here. Now an example three, I'm just gonna show you one other simple application of the function is not gonna apply to everyone's work, but it's just good to see the different ways that you can use this knowledge. So let's assume your business and you're about to run a new business premises. And I've just plotted out timeline of 12 months here. And let's say the month that you rent out the business premises, you have to pay a one-off deposit. And then from that month onwards, you also have to pay rent. And I'm just gonna plot out the costs to this. So we're going to create a couple of flags here. I'm simply going to print if function in and test whether this period, the launch month sediment thes dot renting out. And we're going to anchor the column of the launch month because I'm gonna be dragging this to the right. And if it does, I'm going to put one. If it doesn't, I'm going to put 0 close bracket. Now, if I drag this across, this is a flag. It says in period six, we rent it out. And that's because we've got this assumption here. If I change that to seven and it shifts the alone. Now, I'm also going to create another flag here, and it's gonna be similar. I'm going to test whether this is greater than or equal to the launch month. I'm going to anchor the column again, and it is again up at one, if not up at 0. And if I copy that across, you can see we've got different type of flag is just saying these are the months where we'd be paying rent. And here I can simply do the deposit amount and the column, multiply it by this first black. Then here I'm going to put the rent amount and got the column multiply by the second flag. If I drag these two over. Here, you can see in the month that we start renting, we pay the deposit and then from that month onwards we also pay the rental amounts. I could change this to 40, change that to a 100, and then I can bring this forward to month three. And it just shifts around our costs. It's great for little model because you can visually see what's happening here in terms of when the launch month is and how many months you're actually paying rent. And then we might have lots of different costs. And it means we don't have to keep on writing different if functions down here every time we want to calculate the cost based on the timing, you can probably see how it can be released within our work. And we can evaluate true and valid false components. Pretty simple there. For instance, we just multiply two cells together to establish commissions. But we could instead insert functions to fall slightly more complex calculations. For now give the project exercise, could go practice your new knowledge. In the next lesson, we're gonna be covering Summit, one of my top functions. And I mentioned earlier in the SUM function lesson to keep watching.
10. Essential Function #5: SUMIFS: Some Fs is a function that I regularly use in my work. I'm sure you will, allows us to summarize numerical data based on criteria. We're going to see some examples of how we can use one criteria, two criteria and special criteria. As you watch some syntax, pay particular attention to how I can the formula. This is where I'm inserting dollar sign scope some the cell references from shifting around when I copy the formula. I'm going to begin by just introducing this data is the cutesy sales of a fashion retailer. The items of clothing, the category they belong to, whether they're male or female, the price and the quantity sold by quarter K12 key for the three years 2018 to 2020. Now, question one asks, summarized cuz the sales data annually in this blue cells above. So we want to summarize this data into these years. When we did the sum function lesson, we could see we could do this quite manually, but it takes quite a long time. We'd have the sum of those four quarters. Copy this, and then we could move this over, et cetera. But much better way of doing it is to use a SUMIFS. Now if I type out SUMIFS, with the SUMIFS, we start with sum range, and this is going to be a range of cells that we want to sum together. And the cells that we sum would depend on a criteria. Now, the sum range has to be either a row or a column. It can't be several rows, several columns, that you'd need a sum product. But here we're just going to select this top row of that sum range. And I'm gonna anchor the columns because I'm gonna be copying this to the right, but don't want this to shift to the right by I'm copying it down as well. But do you want this to shift down as I copy it down? So I'm going to anchor the columns with a four. And then my criteria range one units hasn't won at the end, we can add many criteria into this function. But for this first question, we're just going to use one criteria. Criteria range one. I'm going to select these years. And I don't want them to shift to the right or down when I copy this formula. So I'm going to double in Colombia there for and then my criteria one is gonna be the year here, and I'm going to include just the row. Now, if I close that and just explain what's going on. Some Fs will look for this criteria in this criteria range and where it finds it, it will sum together the corresponding cells from the sum range. So in this case, it's summing together these first four cells because this is 2018 and these are 2018. That comes to 86 nine, which we have there. And now if I copy this to these four cells, show you with F2, we can see that the criterion, criteria range stay where they are, but some ranges shifted down. And then if I get this criteria one is shifted to the right. The criteria range again has stayed where is, and this is shifted down as well. And so if I take this and just copy it to all the cells, we can check the title of this is one to 5181. Can check that against the data, which is one-to-five on A1. And such good check to make sure that I've typed up function correctly. One of the biggest challenges of SUMIFS is actually getting your anchoring right. As you can see, we were sometimes anchoring the columns, sometimes the row and sometimes the column and row. And so it's always good just to check that even good things correctly. Moving on to question two, hit asks, summarize annual, quarterly quantity of jackets and coats sold by gender in the blue cells below where this first question, we were just using one criteria. This time we're going to use to. So I'm going to start by talking about the SUMIFS. And we'll select this column as the sum range, and I'm gonna anchor it just the rose. My first criteria, I'm gonna use jackets and coats. And so the criteria range one is going to be the category here, and I'm going to double on collab. Then criteria one will be the jackets and coats. I'm gonna doubling for that as well. If I was to just close this now and copy that are around. You can see we're getting the same result here as we have here. That's because these are basically exactly the same formula that just referencing the same things. As I then move to this year, we can see the sum range shifts across. And so this 6347 is just adding up all the jackets and coat sold, which would be these. And so we can see that comes to 63 4-7. Now I'm going to add in a second criteria. So for against the SUMIFS, x is now us for criteria range two. And my second criteria is going to be the gender, male or female. And so my criteria range two is going to be this doubling clap. And then my criteria is going to be male here, and we're going to anchor just the column. And then if I copy this around, you can see this together. Now comes to 6347 and split the jackets and coats across male and female. So I could check that for instance here, male Jackson coats in 2016 is 2460, and that will be these two cells, 2468. So I can just take this and copy across there. Now, question three asks how many female items priced at 269 pounds of Rayleigh sold each year? So again, this is going to have two criteria relating to the agenda and the price that do 269 pounds a Rover is going to require special criteria. We're going to show you how to do that. So I'm going to enter the summits. And then the sum range is going to be this first column here. And I'm not going to anchor the columns or the rows because I'm not dragging this down. But when I drag it to the right, I do want this summaries to shift. So my criteria range one will now be the agenda here. What do you want to anchor the columns here? My criteria one will be female here. I wanted to anchor the column. Then my criteria range two is going to be the prices. I need to anchor the column there. And then my criteria two is going to be this price. And I'm going to anchor the column. If I leave it just as this, and we get 1392. And so this is actually just this item. We're just looking for items which are female and priced exactly 269. But the question's asking for items which are price 269 or over. And so for that we need to include the greater an equals sign. And so if I go back into here and criteria to, I need to put quotation marks greater than equal quotation marks and then the ampersand. And so because these are text, we need to put them in quotation marks. And then the Amazon joins them to the cell reference. So this is a bit like the x lookup function where we look to the wildcard and we put the asterix within the quotation marks and then joined it to the cell reference. So if I present an L and C, we get 6598 and say some Fs is looking through these. It looks just for the female, and then it looks for prices which are 269 or greater. And then sums up the associated numbers from the summer range. And so that here is going to be these first six cells. So 6598, which will we get there. And now I can just copy that cross. And so that is some Fs using two criteria and one of them using the special criteria. Some Fs is vital to note when summarizing data. Two pieces of advice. Firstly, Tesco SUMIFS to ensure you don't get it correctly as it's easy to make a mistake. Secondly, work using some effluent, very large ranges. For instance, 10 thousand rows of data from or you may find it really slows down your spreadsheet as it can be quite a memory intensive function, better and much quicker alternative to summarize such a lightweight set would be to use a pivot table. And that's what we'll be looking at in the next lesson. Before we show you attempting some ice projects exercises to consolidate your learning. I'll see you in the next lesson.
11. Working with Data: Pivot Tables: If you ever work with large amounts of data and come across content, then you're going to love this lesson. There are massive time-saver and ideal for summarizing lots of data really fast. In this lesson, we're gonna be creating PivotTables from some IMDB movie data. We'll see how we can summarize data by multiple fields. Filter the PivotTable credit slicer switch from summing data to everything and more. To demo pivot tables. I'm going to be using this IMDB film data. So it's the top ten movies between 1992,014 says 250 rows of data here. And we're including the title, studio, year, release day, the amount the film actually grossed worldwide. There's genre certificate, IMDB rating, length in minutes, and then ranking year From one to ten. Now, there's a lot of data here, and that's perfect PivotTables because they're great summarizing, slicing, dicing data can have really, really fast. So I'm just going to group this because we don't need to see the data. And I'll start with question one, which is saying using the above data, show worldwide gross groups by certificate. So to create pivot table, I can just click into the data and then go to Insert PivotTable and asks us to select the table or range. Now because I clicked into the PivotTable, it automatically selects all that data and we can just see a dotted line around all. Now then asks where I want to create the PivotTable either on a new worksheet or an existing, existing. And then for location, just pick a cell here and then press OK. And it creates a blank Pivot Table. And we also have this pivot table fields. This is where we can actually kind of design the PivotTable. Now, we want to show, well by gross groupBy certificate. Say if I take certificate and drag it down to rows and we can see the different types typically arranged in rows. And then I go to worldwide gross and I can drag it down to values. So this is where we put whatever field we actually want summarized. So we can see here, this is all the worldwide growth just summarized by these different certificates. Forget to home. I can just format that's lightly. And that's kind of our first pivot table. Now what I want to say at this point is that PivotTables are static. So if I was to change the underlying data, then if it doesn't automatically update, we actually have to go in and refresh it. So I'll just give an example of that. So we can see this title here. Now, if I deleted the line of data that did not change and say I have to right-click on it and gauge refresh. You can see it just updated. And you're going to undo that to get back the data. And then move on to question two. So it saying show well why gross grouped by genre and stiff curve. So again, we're interested in will buy groceries, we can leave that where it is. But this time I'm gonna move certificate to columns. And then I'm gonna go through genre hampered in the rows. And so we've now taken the same data and you see it's the same total and just summarized it instead by the genre. And then the stiff curve, we can go further and we can actually take another field and added in Pennsylvania will then show the genre and then each of the studios within that genre. But for now I'm just gonna remove that and move on to question three, which says show worldwide gross. Groupby studio in the left column, and years 2006 to 2010, the top row include a certificate filter showing any PG and PG 13. So this time I'm gonna remove the genre and certificate. I'll take studio and put it here. And then we're going to have years across the top. You can see it's all the years. Now the question asked for 2006 to 2010. So it can actually go into this drop-down filter. And I can select the years and then untick them with the space bar. I can retake them with spacebar as well. And then I can move down with the Cherokees and just untick all these. So another way to do this is to go to label filters and select between. And then I'll select 2006, 2010 press. Okay. And we've quickly shown just the years that we're interested in. The question also asks us to include a certificate filter. Second, take certificate and drop in filters here. And it creates a filter there. And I can get to this dropdown. And right now, I can't select just peachy in PG 13. I have to take this and then untick g and R and now press ok. It's just removed the valleys relating to those tickets. Now what you might have noticed is that when we create PivotTable, we actually have two new tabs in the ribbon. We have design, which just kind of deals with how the PivotTable appears. And then pivot table analyze where we can do things like change the underlying data or insert a pivot chart or Insert Slicer. And we're gonna give you an example of the Slicer. I click that, then we can pick a field to show a slicer of. So if I pick a genre and press okay, then this is actually quite youth-friendly. We can create this little slice list where we can then unselect by holding Control certain genres that we don't want. And then if I let go of control, it looks them out that pivot table. So we can have as many slices as we want. And it just makes things a bit more user friendly way. If I delete that, then I go back to the PivotTable and just move on to question four. It saying, show average IMDB rating by genre for movies with a length of a hundred and twenty, two hundred and thirty minutes. So this time we're interested in genres. I'm going to just take all these out. And we're not interested in the robot gross anymore. So take genre appeared in the rows here. And then I'm gonna put length as a filter. I'm gonna sort that in a second for a hundred and twenty, two hundred and thirty minutes. But with IMDB rating previously, we just had worldwide growth and we wanted to add all that up. If I took IMDB rating and put it in values, then what we have there doesn't make sense because an IMDB rating is, they see from 0 to ten, it's what we've actually done there is we've summed all the IMDB ratings by D genres, and that doesn't really make sense. And if you'll notice this is some of the IMDB rating. Forget to this drop-down. I can get a value field settings and change that from some to average, which makes more sense. And say now, if I just go to that and then add a couple of decimal places, this makes much more sense, showing the average IMDB rating by genre. And like I said earlier, we now need to filter the length to only show me these between a hundred and twenty, two hundred and thirty minutes. So we can do that here, or we could do the same thing over here. And if I check this and then unteachable, and then I go down to 120. And then I can go through and manually select all the minutes we want and press OK. Unfortunately, because this isn't rather than rows or columns, we didn't have the option to go to labor filters and gave between when you have the field as a filter here, it's just a bit more of a manual process. Clearly, if you ever worked with a lot of data in terms of vital tonight, and if they're new to you and think about the datasets you come across him work and how you can start applying this learning to them to make your life easier. For now, completely PivotTables exercising project. In the next lesson we're gonna be looking at formatting and I'll show you some quick ways to make your work more professional, trustworthy, and usable.
12. Look Professional: Formatting Basics: Formatting is way more important than people give it credit for. A badly formatted spreadsheet will look less professional, trustworthy, intractable, and ultimately is unlike gain trust formation shirt with. In this lesson I'll be demoing some XOs, basic font alignment and number formatting options to make spreadsheet and look much more presentable. Also show you waking far more advanced formatting options. At the end, I'll provide three different tips to quickly apply some finishing touches. Okay, so let's imagine I'm a project manager and I've created here a spreadsheet to track the costs and hours worked of contractors working on my project. So I've got the name of the contractor that day, rate, their role, the hours they work each month. And then I go to the right, the cost each month. Now this spreadsheet isn't that terrible, that there's quite a few formatting options I can walk you through just to make things look a bit better. Start by pinning the Home ribbon, by going to Home. And then this loop pins and MOOC, and this just keeps the home ribbon in place. Now the basic formatting options here are font, alignment and number. We're just going to walk through a few of these. I'm gonna start by selecting these two cells we shift down and then shift space will select the row I can go. And both these headers. And I'm gonna navigate over to this column and press control space. And I'm going to center the column. And then I'm going to do similarly the here I'm gonna press control shift, right? It's Take me to the end and then control space to select the columns. And then align that to center. I'm going to go back to the left. Next up, I'm going to apply some colored these headings. So I'm just gonna select this hitting here. I can apply color or highlighting by going to this fill color here, I'll go quietly colors to select from. I can see more colors here and then even more in this custom from, I'm going to go back and go to this fill color here and I'm going to pick a light blue. And then I'm also gonna select the cells and navigate to the end and apply the same color. Okay, next up. These hours worked. Some of them have one decimal place and some have none. Can write half days. I just wanted to be consistent so I'm gonna navigate to the bottom and then press control shift up, and then shift to the right. And then here I can remove the decimal place there. So we've got a decimal place in the mall. And I'm gonna go to these figures over here. Now, we can see you can call us 0's is basically multiplying the hours by the day rate. I want to put a thousand separator in there and I don't want any decimal places were showed this theory as a dash. So I'm going to select all of these. And then if I press control one, we actually have some more advanced formatting options here. Forget to custom. Then I can pick a figure here which this the positive figure. This is the negative figure. You can see there's a comma in the middle which thousands separator. If I put a semicolon there and then put a dash, the dash represents how a 0 is represented. So if I press OK, and you can see I've changed the format of those figures. Now. I'm gonna go back over to the left. Now what might also help is if I put a border and these figures are select them and then go here. And I've got lots of different border options, but I'm going to choose. Borders, set kind of boxes, the figures. Now I'm gonna do the same for these figures over here to the top, and I'll put a box around them. Then finally, for these figures as well, I'll do the same. Here. I have this kind of cost header. If I select the cells and then press control one, I can go to alignment here and choose central cross selection. And see it puts the header in the middle of these cells. And if I copy that control C, And then I go here, paste the special formats, done the same for hours. This threefold things I'm gonna do here. First off, if I scroll down, then I lose these Month headers. And if I go to the right, then I lose the name. Day rate, and position of the contractor asks I need to put a freeze pain in accordion got freeze pain which keeps the two top rows visible. And I'm gonna change this so I go to the View ribbon and freeze panes here and unfreeze pains. That gets rid of the frieze paying on the top two rows. We can't see it when we scroll down. Now, if I select this row and go to Freeze Panes, Freeze Panes, then when I scroll down, we keep those months. But if I go to the right, we lose the contract is names, right? If I go to unfreeze, and then if I select this column here and freeze it, and then when we scroll down, we lose them months, but we keep the contractors. So I want to do something which is kinda basically freezes both of them. It's okay to unfreeze, that gets rid of it. I need to select they sell here because I want to freeze everything to the left and everything above side. Just select that cell, get Freeze Panes. And you can see it dissects here. And so when I scroll down, we keep that top header. I'm going to get to the right. We keep the contractors. Secondly, I'm going to apply some grouping. So if I select these cells here and then parse control space, and then I go to Data and group here. You can see we get this line. If I now click that line. Actually groups all those months. So we only see this title here. I can do the same over here and go back to group or instead of group, like press Shift out, right, and it does the same shift out left, we'll move it, ungroup it. Okay, here again, I can click this negative sign and it groups apps, we've just go out to totals. I can actually ungroup them again by pressing this two. Or I can group them by pressing this one says quite handy to make a sort of more compact summary. But I'm going to ungroup them for now. And then the final thing I'm gonna do here is because I absolutely hate them. I'm gonna go to View and untick Gridlines. Ages removes these and I think it just makes it a bit clearer. If I now go back and collapse this ribbon to give us a bit more screen size. Just going to zoom out. And I'd say this is a neater, more tidy spreadsheet across though maybe do a couple of final things like putting this in bold. Bold. But generally, that's quite useful, I think, to recap, investing some time formatting will instantly increased trust you dirty and professionalism in your work and also encourage good upkeep. And in general, that's like look through some formatting options available and apply them to the spreadsheet in the project.
13. Final Thoughts: Congratulations and well done. You've made it to the end. If you're quite new to excel at the start this course, then you just transform your skills. And I can tell you having worked in many companies as an employee and as a consultant, you probably know more than half to be plowed their and in such a short space of time. So what D Now, you've got laser tips to work more efficiently in Excel that you can share with your colleagues. From customizing your Quick Access Toolbar to reducing your file size, increasing your screen real estate will hardcoding in Entire Workbook in just seconds. Your science make use of the top keyboard shortcuts. It will save you tons of time navigating around a spreadsheet and forming actions. Now put the five missus central Excel functions, how to apply them in a variety of ways and has stopped building formally. You can summarize slice and dice large amounts of data in moments with pivot tables, you conscious of how formatting can improve your work. We've seen the basics of how you can start making work more presentable and trustworthy. Where to look for more advanced options. And three quick wins though, add sophistication and professionalism to your spreadsheet. Finally, throughout the class I throw in Lodz or pro tips and additional shortcuts. There's lots more out there though. They just take time to pick up. If you'd like, you can grab a free copy of my top ten XL tips ebook or clarity x-dot Cato ek. Anyway, your skills, knowledge, and ability in the world's Macy's business application have not just improved their transformed. And that's one of the most transferable skill sets you can take to practically any company. Congratulations. If you'd like to, I'd love it. If you upload your projects, the projects and resources, talent. There's no wrong answer here, as we're all learning, feel free to connect with or follow me on LinkedIn and checkout will cause I'm offering or my website. Finally, thanks so much for doing my course. Like I said at the beginning years ago, I had to transition my Excel skills and it was tough. There's now no big satisfaction for me than passing on that knowledge. I hope you learned loads and look out for more of my course in the future.
Dan Stockdale, Excel Online Trainer
