Excel Essentials

1. Learn Excel Essentials Intro: Hi. My name is Armor, and I'd like to talk to you about the benefits of learning Excel. Excel is the most widely used spreadsheet software in the world today for data storage, organization, and analysis. It's used globally by individuals and businesses for tasks such as data analysis, financial modeling, project management, and much much more. I've personally worked in both Finance and IT where Excel is a staple product is the key software that remains reliable and flexible enough to handle a range of tasks. It's a skill set that's hugely beneficial to learn and improve. These skills can help you handle important individual tasks such as personal finance management, as well as in a business setting, adding value to a huge range of job titles where excellent Excel skills can really differentiate you from the competition. Now, Excel can perform complex work, but learning it doesn't need to be complex. Let's look at three key blockers that prevent someone from learning Excel. Number one, difficulty. You've already tried and gotten stuck and given up, not managed to learn or stay at the skill level you have now because you haven't found an engaging enough learning platform. Number two, you want to learn or improve, but you haven't got the time. Excel seems too much work to really dig into around your already busy schedule. Number three, real world application. You don't get the practice you need that you can apply to potential work and real world problem solving. Let's then look at the solutions that this course offers to these problems. First of all, it's about making your learning easy. If you're new to Excel, then this course will take you on a journey from learning the basics, introducing the most important features and capabilities Excel offers, and then beyond into the more powerful features of Excel. If you're someone that has used Excel previously, but you want to expand your capabilities, then this course will also provide huge benefits in providing an update of your skill set and the opportunity to learn new tips and tricks. Whether you're a complete beginner or further along on your Excel journey, you learn by doing, as well as watching in this course, making it an active learning environment. Each video also has a common section in case you have any questions related to the content. Then at the end of each section, there are quizzes to test your learning. To address the challenge of time management. This course is respectful of your availability and works to your schedule. It addresses this problem as it is carefully structured into manageable byte sized videos, typically ten to 15 minutes at most, which means you can sit through a new topic and walk away with meaningful progress in a short amount of time. And then start again when you do have time. There's no rush. The course will remain available to you to progress at your own pace, and each video ticked off that list is a satisfying marker as you push towards the end. Now, when it comes to application, my background is in the working world of finance, where I've used Excel extensively. So I knew I had to make the course straightforward, yet complete, and practical. Every single video in this course is accompanied by the same Excel sheet that I use, so you can watch and follow along. Even when new functionalities are introduced, you're encouraged to open the matching spreadsheet and follow along. I highly recommend you do practice alongside all of the videos to really improve your skills. For even more practical learning, there are exercises throughout the course where you get an opportunity to put your learn skills to the test, and these two come with a video walk through to check your method against or if you get stuck. Finally, there is a project task at the end of the course to create a data dashboard, this aims to test multiple elements of topics you'll cover in this course, and it's a great chance to review your learning and create your own insightful data dashboard. This is the type of analysis that easily transfers to real world challenges. Expertise with Excel can lead to great new opportunities in your career. With the ability to use Excel skillfully, you can streamline processes, remove inefficiencies, and drive value in so many roles. And because handling an analysis of data is such a key part of organizations and the requirement to deal with it efficiently grows each day, making the Excel skills you learn here invaluable. 2. Buying an Excel License: Let's go over how you can get Excel install on your machine in case you don't already have it. Start by opening up your favorite browser and then search for microsoft.com. Now, what you get here might be slightly different to what you're seeing on my screen, simply because this is region specific, so you might have a slightly different version. Now, the first option we can look at is what I will be using for this course, which is the Microsoft 365 version of Excel, and you might have a handy link right there, but in case not on the banner at the top, you can see Microsoft 365. There's a few different options in here. If we just go into the four home, you can see here, it gives you the different options to pay monthly or annually, and fortunately, there's actually a tri free for one month option as well. So you can go ahead and try that. Just be sure to cancel your contract if you don't want to get charged every month. If you're on a MAC, there's also the option to buy a Microsoft 365 with the same package as the Windows version. Another option is to buy just the standalone version of Excel in case you're interested in all of these other apps being packaged. And you can find that by going to the search at the top, search for Excel, and let's find it right there and you'll find a standalone version that you can buy and in both cases, simply follow through with the installation wizards and instructions, and you should be up and running in no time. 3. First Steps into Excel: Hello, and welcome to the first lecture. In this video, we're going to start Excel and have an overview of the application. Now, there's a couple of ways of starting Excel. If you have an icon on your desktop, you can simply double click that to launch Excel. Or on a Windows machine, you can click on the Windows icon here on the bottom left and type in Excel and press center. Either way, you should be met with a screen similar to this, and there should be an option to start a new blank workbook. So let's just go ahead and click on that. L et's dive right in then and examine the window that we've got in front of us. So across the top left here, you've got what's called the Quick Access tool bar, and this stores handy shortcuts, which you can check on and off to customize as you wish. By standard, it will come with a few options such as save, undo and redo, which will cover shortly in this video as well. The next thing we'll look at is called the ribbon, which you can see right here. And this is comprised of different tabs, such as insert, page layout, formulas, et cetera. Some of these items within might look quite familiar. For example, the formatting options that you might see in a word processor like Microsoft Word. And in effect, the ribbon contains a lot of handy shortcuts and actions for manipulating data within Excel. So we'll be using this a lot and we'll cover the specific items throughout these videos. Underneath the ribbon then, we have a name box. We have a formula bar, and we also have buttons here for canceling or entering data or inserting a function. As you'll see throughout our videos, there's actually multiple ways quite often to do things. So some of these you might not use because for example, data entry is a lot easier by simply typing in and pressing Enter rather than typing something in and then making the effort to go here and click Enter. Both the ribbon and the formula bar area underneath can be collapsed. So with the ribbon, we can right click here and click, collapse the ribbon. To bring it back, there's a couple of ways. You can either right click here and uncollapse or what you can also do is to collapse and uncollapse you can double click on any of these tabs, and those will come back. So for example, you can hide those if they're in your way, and you can click once in order to view the options. And when you're done, if you click back, it recolapses, but to pin it back just double click, for example. And similarly with the formula bar area underneath, you can collapse it like so and bring it back just as easily. Next, we've got the bulk of the window, which is the Excel spreadsheet cells that we'll be working with. Now, Excel uses a coordinate system here to reference each cell. So for example, A one, here, B one, B three. You've got your columns labeled with letters across here, and you've got your rows going down here. Now, in order to move around, you can simply left click on any cell, and Excel will jump you to that cell, and it will show you the name box over here, which cell you're on as well. You can also use the arrow keys on your keyboard to move around. You can select multiple cells, so you can just hold the left click and drag. You can also use the shift key on your keyboard. So if I've just clicked there and I'm holding the shift key, and I'm clicking here, you can select multiple cells like that. You can also combine it with the control key. So I'm holding just the control key now, and I'm holding left click and dragging, and I can grab different sections within my spreadsheet like that. Now, in order to enter data, which you've already just seen me do briefly, there's a few different ways of doing this. You can simply start typing into a cell directly. So let's type in ID in here, and you can press enter, and that will commit the data. The other thing you can do is, let's put and score in here. You can just press stab on your keyboard and that will go one cell to the right and enter your data. So when you press enter, it goes and enters your data, and it moves down one cell. If you press stab, it enters your data and moves to the right. Now you've also got options here to edit that data. So if I was to start typing in something new in here, you'll see it overwrites that data here, and I can cancel that by pressing Escape or pressing the cancel up here, and I can go back to whatever was in that cell. You can also double click, and I'm going to do that on cell P one here. So when I double click, you'll see the cursor actually jumps in between the characters that I was highlighting with my cursor, so I can start editing directly there as needed. Next, we can also use the F two key on your keyboard, and this is a very handy shortcut, and that will edit your data and it jumps the cursor to the very end. Finally, as if there weren't enough options for editing data, you can also simply click in the formula bar and start typing directly in there as well. Let's look at one of the most useful options within Excel, and that is the undo and redo. Now, undo, as it sounds, we simply undo your last action, and you can repeat this to go back up to 100 different steps. And you'll see all of your previous actions actually if you drop this down here and you can undo a mass amount of actions at once as well, similarly with redo. So again, we can go one step at a time by just clicking that, and we can put our data right back. I could have also done that in one step. So if I undo a couple of actions, where then I drop this down, and I highlight these two and click, I can do several actions in one fell swoop. Let's add some data to our spreadsheet now. So I'm just going to add some IDs in here, one, two, and so on. I'm going to go down all the way to ten. But actually, I'm going to use this opportunity to show you another useful feature within Excel, and that is the fill handle. So over here, you'll see whenever you've highlighted any amount of cells, you'll see a little square hair on the bottom right. You can hold your left click on this and drag this down, and Excel will simply copy that into any cells you've highlighted. I'm just going to undo. Now, if I highlight these two cells here, I've got the one and the two, you'll see as I drag this down, Excel is actually showing us that it's recognized a pattern there and you can see 678 Excel is recognizing that there's a pattern and it will fill that in for us. So on occasion, you can actually use Excel intelligently to fill in extra items for you like that. Let's add some scores in here as well. Now let's say that I want these same scores on these cells here as well. So if we were to try and use the fill handle here, which in effect is a copy and paste functionality, you can see Excel SNA pattern there and has pulled that down, but maybe we didn't want that as such, we can either undo to get rid of that or we can highlight the cells and press delete. Now, the other option to copy this down is we can actually select our cells, then right click anywhere within that selection. And what you get here is a contextual menu that opens up with various options. And we're just going to look at the copy option for now, and you can see Excels showing us what we've copied with the dotted lines running around. We can then just move down to the cell below and right click and click on paste. And there's a few different options in here, which we'll cover later. But for now, we're just going to use a standard pase functionality. And that's how we can get our data copied across. Next, let's look at another standard action that you'll use very often in Excel, and that is adding or removing rows and columns. So I'm just going to press escape here to get rid of the copying Now, in order to insert rows, what you can do is you can go over to your row and you can right click on the number there, and you can simply press insert, and this will insert a row above your data. Now you can insert multiple rows. So for example, if I wanted to add two rows above row ten, I can simply select a couple of rows, and on either one, I can right click and press insert, and I'll add in another couple of rows. Let's just undo that. Columns are exactly the same, right click and if I insert, it will insert a column to the left of the one I've highlighted. One column like so or three columns like that. Now, I've just been using undo here as you've seen to get rid of these, but we can also delete columns in case that wasn't the last action, and you can do that by highlighting your columns, right clicking and pressing delete. And the same applies for your rows. Now, the method we've just seen is probably the easiest and most intuitive, but with Excel, there's usually multiple ways of achieving the same action. So within the home tab on your ribbon, you'll actually see we have an insert command here. So we could, for example, highlight this row here and if you wanted to add a row above it, we could also go into insert and insert sheet rows, that will achieve the exact same effect. And you've got the delete option here as well. Now, it is worth pointing out that you can delete individual cells as well. You don't have to delete rows and columns out right. So as an example, let's say we had some data in hair and in hair, and we want to move this cell over here to the left, but we don't want this one to move. So if I was to right click and delete that, both of them shift to the left, and that's not what I want. I want this one to stay where it is, and I want this one to move. So you can do this by simply deleting the cells in front, so I can just right click here on this cell, and I can press delete. And an option menu opens up, where we've got the option to shift the cells to the left up or again, deal with entire rows and columns. So in this case, once I delete this, I want any cells to the right to be shifted across to the left, so I to leave it on that and press okay. And there we go, we've had that cell, move across, and this cell left alone just as we wanted. Now, one more handy action to be aware of that people often forget is that you can zoom in and out of Excel. So if you go to the view tab on the ribbon, you'll see a few different options here, so you can see we can zoom like so and choose a particular magnification or simply type something in. So let's say I wanted 125%, and that would zoom in. As always, you can undo. You can zoom to a particular selection as well. So I've had that cell highlighted, and Excel will simply zoom in far enough for you to really be able to see that magnified. And we can also return back. In this case, I've used undo, but you know, if we had zoomed in a few times and it wasn't your last action, you had typed some data in. You didn't want to undo that data by using Undo, you could just click on the 100% to go back to the standard Zoom level. Now, within the view tab over here, there's actually a few other useful options as well. You'll see that we can actually turn off the grid lines that are standard in Excel, and for certain presentation purposes, you might want to do that. Headings here will actually remove our row and column identifiers. And finally, the formula bar can be gotten rid of entirely as well if you don't want to see that. Now, let's cover off one last thing, and that's towards the bottom of your Excel sheet here. You'll see here that it actually says sheet one. Now, that's because the way Excel is structured is you have a workbook, and within each workbook, you can have multiple work sheets. So by default, we started with one. But we could add additional ones quite easily just by clicking on new sheet here. And this is useful because you can have your data separated out. For example, you might have raw data in one sheet. You might have a dashboard in another, you might have graphs and charts in another. So it's a really great way of separating out your data logically. To summarize, then, we've seen how to start Excel up. We've also had a look at the quick access tool bar, the ribbon, the formula bar. We've had a look at worksheets, cells, navigation, data entry, and some really useful functionalities such as copying and pasting, the fill handle, undo and redo, as well as adding and removing columns, rows, and cells. So we'll wrap this video up there. Thank you very much for listening, and I'll see you in the next video. 4. Formulas and Functions: Now that we've had an introduction to Excel in terms of navigating, entering data and having a general understanding of the screen in front of us. Let's start to dive into it a little more with the more powerful and useful features of Excel, starting with formulas and functions. So at the start of this lecture, if you just scroll down on the video that you're on, and you'll find a file to download. Now you can also find this file in resources as we've discussed previously. But I've just put it here for ease of use. So click on that and either save or open directly, which is what I'm going to do. And this should load up in Excel. Now, you may have a banner at the top that's saying it's a protected view, which means you can't edit. Just click on Enable editing so we can start. And we're just going to click on the formulas here to jump to the relevant worksheet so that we can begin. In front of us, then, we've got some C data ready to use. We've got some IDs and some scores and some more IDs in scores. So let's start with how we can enter formulas in Excel. What is a formula? To start a formula, you enter the equal sign, and you can simply start entering arithmetic operations as a very basic start. So we could simply do, for example, one plus one, and Excel will effectively perform like a calculator there, and we can do the same thing with our usual mathematical arithmetic operators. So just in case you don't know all of these, you use the Asterk for multiplication, and you use the forward slash for division. So that's good, but we don't really want to use Excel simply as a glorified calculator. So let's say we had to tally up our scores here. So what we can do here with Excel is, we can actually press the equo sign, and then we can click on a cell, and you can see Excel highlights the cell that we're on, and we get the cell reference in there, so cell B two here. And then, for example, we can put in our arithmetic operators and we can do the same thing, select another cell. And for example, we could continue in this way and add up every single one of these cells if we wanted. So Excel is able to look at those cell references and work out calculations based on them. Now that's very useful because if we were to change any of these figures, then our formula down here would obviously update accordingly, so we could get that updated as we change. I'm just going to undo for 1 second there. So once again, useful, but that's pretty unwieldy when you look at that formula over there with all of the individual cells. It would be handy if there was a quicker and more efficient way of doing this, and fortunately, there is If you turn to the formula tab on your ribbon at the top. What we'll see here is a function library, and this contains lots of useful functions and essentially shortcuts in order to perform certain tasks. So a good one is how to sum up all these numbers. And for that, we'll look at Math and trig. If we click on this then, we'll see a list of different functions within this category, and let's scroll down and find some. This will open up a function arguments window. What you'll see here is the name of the function. You'll see what are called parameters or arguments for that function. You'll see a brief description of what the function does. In this case, it adds all the numbers in a range of cells. And you'll also have a help on this function, option here, which opens up a Microsoft webpage for basic help on the function. So what we can do here then is we can effectively start to select our cells. So by deleting that and then clicking on a cell, I'll see my cell B two. And then if I move to the next one, I could once again one by one, add in all of my cells. Now, note here as well, because I had already had a formula within this cell when I clicked on the Math in trig function. What's actually done is it's added su to the end of that. Actually, let's just cancel out of this, and let's get rid of our previous formula entirely. And let's start here once again. So we're going to go to Math intrag and we're going to find some We could add each cell individually like this, but that's not really achieved anything from what we had earlier, and this is where the hint within the description of the function comes into play, so it talks about a range of cells. So what we can do instead is, rather than adding up each one individually, we can simply leftck and drag to select all of the cells we want. And you'll see here the syntax that Excel uses. It shows you the first cell reference, B two, shows you the final cell reference that you want B 11, and it's got a colon in between, which means go from B two through to B 11. And we can just press okay on this, and we can see the same result, but in a much more user friendly manner in terms of our function and formula. If you want to edit this formula, there's multiple ways of doing this, you can simply click here on the formula bar and you can manually type in any changes, so we could change this from B 11 to B ten, let's say, or we could look at our visual box here that Excel has shown, and we could simply hover here on the edge and hold the left click and drag to whichever items that we want. Now, interestingly, we can also drag this up here to cover the head itself. And if we press enter on that, we see the formula and the functions still work. Whereas, if we were to do equals that plus B two, Get an error from Excel. So why is it that we don't get an error? But we get the correct result when using the sum formula? To understand this, let's open up math and trigger again and go to our sum function. Now, one thing we didn't talk about here was this description. And what that description refers to is the different parameters or arguments. Now, parameters and arguments are basically the data that you feed into any function. And the description here for the sum function says, you can have one to 255 different values being fed into the sum function individually, and logical values and text are ignored. So we can still have those as part of our function. But Excel will simply ignore those and only sum together the number, so we don't get an error when we execute. Let's look at another couple of functions, then. So in here, we're going to enter another function now. There's multiple ways of doing this. So we've seen the function library here. You can also click on the insert function here, and you'll have a list of different functions. And again, you can access the same categories as the function library here. And the other method we've got is, we can actually start typing in directly as well. So in this case, we're going to use a function call count, so we could just clip equals. And as I start to type, you'll see the different options within Excel will open up for the availability of functions. So as I type in count, you'll see I've got count here at the top, and I can just press stab with that highlighted in blue and Excel will fill in the rest of this and open our brackets as well. And within this, we've got a description of our different parameters or arguments, so we can see here, we've got value one, and then you'll see here a comma and then your other potential parameters. Now, what you'll see here is you've got square brackets here on value two. What that means is, value one is mandatory, but value two and onwards are optional. Let's do this. Let's click on our first item here. Press the comma, and you can see once again, we get more optional parameters opening up. So let's just do three there. So we've got e two, e three, and e four, and let's close the brackets and press Enter. And the result that we get is three. Now, we could have seen what this formula does from the function library, or as we were typing, we can see. It says, what count does? It counts the number of cells in a range that contain numbers. So we can use the range just as easy as we've done with some, so we can delete this, and again, editing directly within the formula bar. I can simply hold the left click drag. And again, we're going to count from e two to 11. So Excel will tell us how many of those cells contain numbers, and the answer is all ten. As we saw from the description of count then, it counts the number of cells in a range that contain numbers. So if we were to try count here and select these cells, we're going to expect a zero because there are no numbers here within this range. What we do have are variations, however, of certain formula. So we have, for example, the count a function here, and that counts the number of cells in a range that are not t, so they can contain anything, so we could select these cells here, and this time we are going to get a ten returned. Let's see how we can combine formulas and functions, then. Let's delete this and let's enter a few numbers in here. Now, a quick exercise for yourself to see if you can work it out before I go through it. Can you get the number of cells that contain non numeric values within this range from d two down to D 16? Give that a go to yourself before we go through it together. Okay. So hopefully, you manage that by combining a couple of functions. Now, of course, the data here is so straightforward, we can simply look at it, and we know it's ten, but it's just to keep it simple so that we can understand the concepts. What we're going to start with is a count A, and we're going to count up all of these cells here. And that will get us the total number of cells that have non blank values. And then what we're going to do here is we're going to remove any cells that have numbers. So we're going to do a count function this time. And again, we could select these cells again because we know Excel will only be looking at numbers here. So should count five there and remove that from the 15 to leave us with ten, exactly like we wanted. Okay. Great. Now let's look at a couple more useful features when it comes to using formulas and functions. Let's consider our sum function over here. Now, let's just expand that to the NFR data like so. Let's say that I wanted to be able to add additional IDs and scores, and I wanted my range here to expand accordingly. Now, if I was to add in a few rows in here, right click and insert, take a look at that range. Now that's still stuck there on the original ten that we had, so it hasn't moved unfortunately. So how can we deal with that? So if we undo here and let's change our range so it doesn't just go to the end of the data there, but it goes down one more cell, so it's just above what our formula is. That's going to make no difference because that's blank, so our value stays the same. Now when I add in additional rows, and we'll look at our sum formula here. You can see it's expanded, so it's still going to just above the cell of the formulas in, and it's been able to take into account the additional rows. So as we add in anything additionally, it will just roll into our formula right there. Now, that idea of having scalability built into your formulas and functions is important, and we can achieve something similarly if we had a function over here in column I, let's say. And let's say we were adding up everything here in column A. So I'm just selecting 1-12. Now, in order to have this be scalable, you can actually select the entire column. You don't have to select a range. So if we delete this, and we simply hover over here on our column, and you can do this for rows just as easily. But if we just left click on our column there, it's just going to go from A through to A press center. And what that means is, as we add in additional items, that's going to be taken care of in the formula without us having to make any amendments. We've seen now a couple of the most common functions that you'll come across in your usage of Excel. In fact, these are so common that as you highlight certain cells, what you'll see across the bottom of your window, which is called the status bar are, in fact, average count and sum. And in fact, you can bring in a couple of the other more common features as well, such as minimums and maximums to deal with the cells that you're highlighting. So you don't always have to enter formulas, but you can see at a glance some of those important statistics. Now, the sum function in particular is so common. There's a very handy shortcut for it. And that is by selecting on the cell that you want your function in, holding down the key on your keyboard and pressing equals. And it will automatically pick the range of cells above it, and you can just press to enter your formula. We'll wrap up our introduction to functions and formulas here then. And what we've seen is how to start formulas within Excel with the equal sign. We've also seen a couple of the more common functions and how you can access the whole list of functions within the function library. We've also seen how you can view some of the more common statistics here on status bar, how you can change those by right clicking and adding in or removing items that you don't want to see. As always then, thank you for listening, and I'll see you in the next video. 5. BODMAS: Hi. Now that we've had an introduction to the basics of Excel, let's look at a core concept that we'll have to bear in mind whenever we're working with numbers, and that is operators and orders of operation. Let's start by looking at our mathematical operators. So we've got our operator names here in column B, a sample, very simple calculation here in Column C, and the result of that calculation is simply by sticking an equal sign in front of whatever's in column C. So we've got addition and subtraction using the plus and minus symbols, very straightforward, multiplication, which uses the asterisk symbol and division, which uses the forward slash. Then we have orders, also known as powers, indices, or exponents. Now, this might be slightly less familiar, so let's just run through a couple of quick examples. The most common one you'll have heard of are squared numbers. So for example, if I say two squared, what that means is two multiplied by itself, which gives us four. Then we also have cute numbers. Two cute would be the same as two multiplied by itself three times, which gives us eight. And we can repeat this four higher powers, so two multiplied by itself four times, which gives us 16, and so on and so forth. But as we start to multiply a number by itself enough times, we're going to have to start to type in more and more, and it's getting really inefficient. That's where powers come into it. So to do two multiplied by itself four times, we can instead use two and the carrot or hat operator, which represents powers, so that's shift and six on my keyboard. And I can put a four in hair, and that's two multiplied by itself four times, which gives us the same result of 16. Now we can also work backwards to get what you might have heard called roots of numbers. The most common example is a square root. So if we talk about square root of 16, we mean what is the number that multiplies by itself to give you 16, and that of course is four, four times four is 16, or the square root of four, which is two, two times two is four. And we can get this within Excel again using the same symbol and to get the square root of four, I'm going to do 1/2, and that will give me two, two is the square root of four. And if I wanted to get the cube root, I could also do the same thing, so I could get the cube root of eight by doing eight to the power of 1/3, and that again is a two, and that's my root freight. One final example here then is the one I've entered here, which is the sixth root of 64, which, of course, is two. And so that means we can go two to the power of six, and we should get 64. We've now covered our mathematical operators, but the key thing within Excel and in mathematics is to understand the order of those operations because some take priority over others, and it's important to know that order. L et's build a longer formula from scratch here. So in general, you work from left to right when it comes to operators. So if we had one plus two, which gives us three, then we divide that by three. You would expect the answer so far to be a one. And then you multiply that one by four. You'd expect the final answer of this expression or formula to be a four, except that's not what we see. So whilst we do work from left to right, there are certain operators that take precedence over others, which is why we get the answer we get over here. So there's a defined order of priority, and if this is new to you, then there's a simple way to remember that order with an acronym, bod mass. And that stands for brackets, orders, division, multiplication, addition and subtraction. What this boils down to then is, if we have brackets around any part of our calculation, that takes priority, so we calculate the brackets separately, and then out with any brackets, the order of operations is going to be in order of priority, any powers or orders first, then division, multiplication, addition and subtraction. So let's look at a couple of really straightforward examples right away. Here, when we were looking at powers, we had 64 to the power of 1/6, and you can see I've had to put the 1/6 within brackets. And the reason for that is because the power will take precedence over my division. So if I didn't have brackets, let's see what that result looks like and run through does what it does. So we get 10.67, and that's because 64 is being taken to the power of one, which is just 64. And then that's being divided by six to give us our 10.67. So a very quick math lesson, just in dicen are aware of why we ended up with that result. Whenever we're using powers, if you have any number to the power of zero, the answer is always one, and if you have that number to the power of one, the answer is that number. So 64 to the power of one is in effect doing 64 times one, which is 64, dividing by six to get our 10.667. The key takeaway then is that we can use brackets to achieve any result we want regardless of this order of operations priority because we can use brackets to segment our calculation in effect. Now, we can also take a look at brackets in relation to multiplication and division actually and just talk about these operators a little more. In effect, these are inverses of one another. If I'm multiplying by two, I can just take the inverse, which is just 1/2, and I can switch my operator from a multiplication to a division to achieve the same result. Instead of doing one times two, I can do equals one divided by, and I'm going to use my brackets here and do 1/2. And that's the same as one divided by a half. So instead of doing brackets and the 1/2, I could have done 0.5. I could have got the two as well. We can do the same thing here for division. So instead of doing 4/4 to get the one, I can take the inverse of the four, a one over, and I'm going to switch my operator as well. So I'm going to do four times, and then I can either just do 0.25 because that's a quarter, or if I want to use the actual inverse directly, and you'll have to use this if you don't have an exact nice decimal like 0.25 or 0.5, obviously, and I'm going to start typing out 0.667 and so on. So any irrational numbers in effect, youre obviously going to use the fraction. So equals four times 1/4 is going to give us our one as well. The key takeaway there then is that multiplication and division are almost the same operator. Now there are, of course edge cases because you can multiply any number by zero, and your answer is always zero, but you can't divide a number by zero because you'll get a divide by zero error. So to make sure that we've understood this properly, let's take a look at a few examples in here. See if you can work out where to add brackets in each of these calculations within our cells here, B 12, B 13, and B 14 in order to achieve these results here. So I'd advise you to pose a vi you now, give that a go yourself, and then we'll go through it together. So hopefully, you manage that. Now let's take a look at this together. So we want to add brackets to this calculation here to calculate the result as a seven. So let's just break down what's happening in here first. So we've got one and then we're adding two times 3/2 minus one. So let's just look at the order of operations here. So we know our multiplation and division are in effect the same operation, so they've in effect got equal priority. So we're going to do two times three, which is six. That's then going to be divided by two, which is going to give us our three, and then we're just adding a one and removing a one. So that's why we're getting the three right now. So for us to get our seven, let's just look at this high level, see how we can get that. So it would work if we just had our six being calculated here, and we added the one to it, that would give us our seven. And we can see how we can get that because if our two minus one happens first, so that two minus one gives us our one, and we do two times three, which is 6/1, which remains six, and then add the one, we'll get our seven. So if I simply put brackets in here, it means that I'm forcing the two minus one to happen before the two forms part of this chain here. We're now going to have two times three, which is six. The two minus one is being calculated separately to give just a one, 6/1, then we add the one, and that will give us our seven right there. Our next example then, we want to add brackets to calculate this result as a 64. First of all, why are we getting the 12 here? Well, we're doing two to the power of two times three. So we know orders take precedence over divisions and multiplications. So what's really happening is two to the power of two is happening first, which is giving us a result of four. That four is then being multiplied by three to give 12. So for us to get 64, we've seen this over here. If we do two to the power of six, we'll get 64. So I want to make sure that two times three happens first, to give us our six, and then it's going to be two to the power of six, and we'll get our 64. Let's look at our last example. We've already got some brackets in here. So let's work out why we're getting a nine first fall. We've got our brackets look at first. So what's happening in here is two times three minus two, and there's no other brackets here separating this out. So what's happening is the multiplications happening first to give us two times three, which is six, and then six minus two, which is four, so the full result of the bracket there is four. And then we have two times the four, which is eight. And then finally, one is added to that result, and that's why we get our nine. Now, in order to get a five in here, this would work if I had two times a two, if the result of this bracket here was a two, because then two times two would be four, and then I could add one to get my five. I know what I want to get. And I could get that actually by making sure that this result over here happens first. So if I get a three minus two happening first, which is just a one, I'll have two times one, which is two, and that will be the full result of the bracket, and then two times that to give four and then add the one. So I can actually put more brackets within here, so I can put brackets around the three minus two. And this is now nested, and the innermost brackets will happen first. So three minus two will happen first to give one, and then two multiplying that to give us R two, and then moving outside of the brackets to do two times two to give r four, and then finally adding the one, and we'll get our five. We'll wrap this video up here then. You've now understood how to use the different mathematical operators within Excel. You've understood the order of priority, and most importantly, you've understood how you can use brackets to achieve any calculational result that you want. So, as always, thank you for listening, and I'll see you in the next one. 6. Relative and Absolute cell references: In this topic, we're going to examine the differences between the two types of cell references found within Excel. These are respectively relative and absolute, and we're going to look at use cases for both. Now, in the previous sections, you've seen a few simple formulas and the cells that these reference. Note that the default reference type that you've seen so far is called the relative cell reference, which basically means if you were to copy and paste that formula to another cell, any cell references within that formula would automatically change to be offset by however many rows or columns that formula has been shifted from its original destination. Now, that sounds a little complicated, but it's very simple in practice, so let's take a look at an example right now. In front of us, we have a few named items, their unit prices and their quantities. Then we've got a couple of blank sets of cells under the headings of total price and total price, including tax. We've also included the tax rate up here, and finally, a couple of sums totaling these two columns here. Let's begin by working out the total price for each item. We'll start with an equal sign. Pick the unit price in B three, multiplied by the quantity in C three, and that gives us the total price for the first item. Now, to copy this formula down, we can use the fill handle here. Left click and drag to each cell below and release. That gives us the total price for each item. Now, let's examine what's happened to the formula. So our starting point was B three multiplied by C three. And if you double click into each cell, you can see that the references have moved down by one row each time. So C five, B five, B six, C six, and so on. Now, this relative cell reference behavior is very useful in most cases. It saves us having to type the formula in for each cell that we want. We can simply drag down and get the expected results. Now let's consider the total price inclusive of tax. How would we calculate this? Let's just start with the first item here. We can take the total price here and we can multiply. Now we've got a tax rate up here and in order to work this out correct arithmetically, you'll want to have some open parentheses here and do one plus the tax rate there and close that and press center. That gives the total price inclusive of tax. But now what happens if we drag this down for every item? Now, that obviously does not look right. So let's take a look at what's happened. So the first cell is fine. We're taking the total price, we're multiplying it by the tax rate. But if we look into the next cell, you can see because we moved down by one row, it's moved the cell reference here for the tax rate down by one row each time as well, which obviously will give incorrect results. What we really want to do here is fix the cell reference for the tax rate, but leave the total price alone so that when we copy down, we still pick up the individual total price for the respective item. The way you do this in Excel is you can use the dollar sign in front of either your row or your column in order to fix them. So if I wanted to fix just my column here, I would put $1 sign in front of the e, and if I wanted to also fix my row, I would put $1 sign in front of the one. So let's try that. In this case, we're fixing the row and the column, so we're pinpointing a specific cell in effect. So we press enter on that, and now let's try dragging the fill handle down. So that looks much better. And if we take a look at what's happened, in the next cell, you can see we're still moving down on the total price that we're picking up. However, this cell reference here is fixed on the tax rate, so we get the expected and required results. Now, here's a quick test for you. Let's say we had a situation where we had a different tax rate in this cell over here, and we wanted to work out total profit, including tax where the tax rate was this amount here. How can you do that using what you've learned so far? Os the video here and give that a co yourself, and then I will work through the solution. Hopefully, you manage that and if not, or to verify what you have done, let's go through this together. I'll start just by setting up my formula is here. And let's start by copying this formula here as you might logically do. If I was to copy and then paste here. Now, whilst I'm doing this, Ashley, I'm going to show you the preferred way of copying and pasting. Rather than right clicking, copy and pasting as I've just done, the much quicker way is to use the keyboard shortcut of control and C. Click on the cell that you want and use control and to paste. Now, let's take a look at this. So immediately, this very first sell is wrong because we're wanting to look at this price here, not this one here. So I would have to move this across, and the tax rate hasn't shifted across either because of course, it was fixed to that sell. So we want that tax rate to be there. So fine, let's set up the initial one like that. Now we can do something very similar to what we've done here previously because we shifted that manually to F now and we made that D three. So if we were to copy this down, this would in fact work for every single cell as expected. So we've got the new tax rate and each one is picking up the relative total price. Now, if you had more costs to do that you had to repeat that every time, it's obviously wasting time. So a quicker way is actually to amend the original formula here. So when we copy it across it copies across as expected. So let's take a look at this. So what we want to do here is, we want this to always pick up the total price within this column here. So we'll always want column D. Now, when I move this across, however, I do want this to still shift down. So what I don't want to do is stick dollar signs in front of both the D and the three here to fix both elements, because then the entire cell will be locked there. Instead, I want the rows to be free to move. So I want to fix that to just the d being the absolute reference, I E, the column. But I want this row to be able to move. Next, I want to achieve a similar effect for this tax reference here. This time, I want it to be able to move over here when I move my column to the right. Therefore, I don't want to fix the column this time, so I'm going to get rid of that. But I do always want it to be on the first row, so I'm going to leave that dollar sign in front of the one. So let's enter there. Now the first thing you'll want to do here is you've got a different formula now in this cell E three than all of your cells below it. So let's track that down and let's make sure that these values stay the same. There you go. You've got the new formula now, it's still referencing what we want, but we've just manipulated the elements of these cells that we're fixing. Respectively here, we're fixing the column, and over here, we're fixing the row. Now what that means is when we take this cell here, we copy and we baste. You can see it's still picking up the correct total price and now it's shifted to the formula that we want here. Similarly, when we use the fill handle to track this down, everything is working exactly as expected. Now, before we wrap up, let's look at a very useful shortcut for changing cells to absolute references and the different combinations. So to start with, let's just remove these dollar signs here. So when you have a cell selected with your cursor anywhere in front or in between or on the end of that cell reference. If you use the F four key, you'll see it wraps that cell reference with the dollar sign. Here we're fixing both elements, the column and the row. If you press F four again, it will fix just the row once more for just the column and pressing F four again, returns us to a full relative reference. So let's copy the formula down again just to make sure everything is lined up. And now you can see the importance of using absolute references because what we have here now is a very flexible formula that can be copied across and down to cover different tax rates as needed. Just to show that, let's look at a 5% tax rate in here. And this time, I'm just going to copy this formula across here and again all the way down. And you can see the form has moved across exactly like we've expected and across to all cells that we've corporate it down into. To recap, you've seen a practical example of why we might use an absolute reference or a partial absolute reference in this case. There's many situations where you will want to fix cell references in this manner. This is an integral action to remember in order to achieve certain solutions. Thank you very much for listening. I'll see you in the next lecture. 7. More Formulas & Functions: Hello, and welcome to this next video, where we're going to go more in depth on formulas and functions and some more functionality that we haven't yet seen. So to get started, we're going to start working on the formulas worksheet again. So if you made changes on this from the previous video, you can simply delete the extra items or you could go to the resources section and simply download the worksheet and open it again, so it looks like this. The first thing that we're going to talk about is nested functions. Now, we've already seen that we can have functions working separately in different cells. For example, we could take an average of our first four numbers here, and we could do the same for the average of our next four numbers in the next cell, like so. Then we could do a sum or a addition here, just to add those two together. Now, we've done that across three different cells, but with nested functions, we could simply achieve that in one cell. So what we could do is actually type in equal sum. Now, for our first argument here, we can actually type in another function. For example, we could say average, and we can select our four numbers here. Now we're working within the average formula here because you can see we've got average written here and we've got our first argument, and we can close our bracket, and you can see the brackets are nicely color coded here for us as well to understand which function we're working in. And once we've closed that off, we're back in the sum function because you can see Excel showing us that here, we're still working on number one or the first argument within the sum function. If we place that coma, we're now into the second optional argument. So for example, we put in another average function here, and we could pick up our next four numbers. Close that bracket and we can close the final bracket, close off the sum opening bracket. And as I do that, you can actually see Excel momentarily puts in bold the brackets that are corresponding to each other. If I put in this bracket, you'll see that triggers bold for a second as does this. And in fact, as you use the left and right arrow keys to move between the brackets, so either side of the bracket, you'll see the opening and closing brackets being highlighted. That's very useful when you get larger nested functions, so you can at a glance, just go back and forth and see if you've got your closing and opening brackets in the correct positions. So let's close this off, and we can we get the same result here, but we've got that all in one cell. So that's a really useful feature to be aware of if you want all of your functionality to happen within a single cell. Next, we're going to look at how formulas can be linked through to other worksheets within the same workbook or even to other worksheets in other workbooks. So let's start by adding a new worksheet into the same workbook. So I'm going to click on new sheet over here, and I'm going to expand this slightly so I can see that as well. So we're now on this new sheet one that we've added here. And if we just press equals here, and then we click on our formulas worksheet here, You can see as soon as I do that, Excel shows the name of the worksheet with an exclamation mark. And if I click on any cell, for example, A two here, it's going to say formulas, exclamation mark, A two, and that's Excel's way of identifying that we're within a different worksheet within the same workbook, and we get the particular cell reference right there. So we can just press enter there and we're going to pick up the value within that cell. You can enter formulas in the exact same way as you would expect, so we can do equal sum, for example, and we can go over to our formula worksheet here, and I can just select the range that I want and close my brackets, and you can see here again, within the range of cells this time rather than just e two through two E 11, we've got that prefix by formulas exclamation mark to show the worksheet. So if we press enter, we'll get the exact same result as if we were working on that worksheet directly. Now, this functionality of linking through to other worksheets extends to other workbooks as well. So for this, we're going to go to file and new and then choose blank workbook. And what we can do here is press equals and then all tab or switch over to your other spreadsheet for learn Excel essentials. And we can simply click on any cell that we want. So let's go to the formulas worksheet here again. And before I click anywhere, let's just analyze what we're seeing up in the formula bar here. So you can see within the new blank workboo we've got equals, and then because we've switched over here, we've got an apostrophe, and then an open square bracket, then the name of the workbook that we're in. So learn Excel essentials, working dot elsX. You one will just say Learn Excel essentials dot Excel SX. Closed bracket, then the name of the worksheet that we're in formulas. Another apostrophe there to close off the opening one there, and then an exclamation mark as before. Again, we can now click on any cell reference here and we'll be able to link through it. Now you can see it's got absolute cell reference tags around this, so we can simply get rid of these, and we will have the expected result there. Now, just as we working within the same workbook, we can apply formulas and functions, and they'll work exactly in the same way and we'll see the full link to anything within our spreadsheet over here. Now, one interesting thing to note here is this file path shows like this because we've actually got the spreadsheet we're linking through to open as well. If I was to all tab over to that spreadsheet and just temporarily close that off. You'll see within the book one that I'm working here on, we've actually got the full file path in here, so my drive, any folder directories, and then what we've seen previously with the name of the workbook and the worksheet and the cell reference. So this is really useful to understand where you're pulling any data from within a spreadsheet, the full file path, because you won't necessarily always have all linked spreadsheets open at the same time. Now, let's leave this book one spreadsheet open, and let's reopen our Excel essentials file that we're working on. So it should look like this if you had saved it earlier. And let's look at a couple of options we've got within the formulas tab here in our ribbon. Now, we've got something here called show formulas. Now, if you click on this, we'll display the formula in each cell instead of just the resulting value. So if we do that, we can very easily see where we've got values and where we've got formulas. So it's very good for looking at a spreadsheet at a glance to understand where there are formulas present. The other really useful feature with turning on show formulas is as you click on any cells containing formulas, you will see the cells that are forming part of the function or calculation, and you won't have to edit those cells. So if I was to turn this off, I won't see those highlights unless I was to press F two or double click in. So it's a really good way of just working through your functions and formulas very quickly with that turned on. For now, let's turn show formulas off again. And let's explore a couple of more other useful options within our formula ribbon over here. And that is t race precedence and trace dependence. Now, what this will allow us to do is look at any cells that are part of a calculation within a cell and be able to trace back and also look at a cell and see where it might feed through into other formulas. So as always best seen with an example. So if we click on our cell A 13 over here, and we click on trace precedence. We'll see Excel draws a line from the four cells that are feeding in as part of the average calculation, and it draws an arrow showing which cell the calculation is in. And what we can do here is if we were to double click on this line over here, it will jump us to the cell where that calculation is present. So that's very useful, especially when you've got lots of different cells referencing a large special that runs down the way. It's often quicker to just double click and jump to the cell that you want. We can continue like this for other cells. So for example, A 14, grace precedents, we'll add that to the list, and in the same way, we can double click and jump to the cell that we want. Now we can also remove these arrows. So what we can do here is we can remove individually. So for example, if I go back to my cell A 13 and select that, if I drop this little arrow down next to remove arrows, and I just remove precedent arrows, it will only remove it from that cell there. Now let me add those back in for a second. If we instead remove arrows here directly, that will remove all precedent and dependent arrows that we've chosen so far. L et's look at t race dependence next. So we know, for example, this cell here B two feeds into this formula here. So let's click on that cell B two and check trace dependence and see that that works as we expect. If I click on that, we actually see it feeds into a couple of cells, so we're taking it as part of an average function here. So it's part of this range B two to B five. But actually is also part of this sum here, where again we were averaging B two to B five, and then B six to B nine. So once again, you can double left click on an arrow, and you'll jump to the respective cell like so. L et's remove these arrows again. And now let's click on cell A two, and let's do trace dependence here. Now what we get is a different broken dash arrow over here. Now let's double click on that, and what we get is an option menu that opens up like this. And we can see where that cell is forming a part of another formula. So we can see it's part of the Learn edEcel essentials working spreadsheet here and the sheet one that we've got and cell A one. So let's highlight that and let's press okay, and we'll jump us to that cell. So if you remember, we link through two formulas A two right at the start when we add to this worksheet. We can use trace dependence or precedence in that way to jump across sheets as well. Extension of that, as you can imagine, is jumping to other workbooks entirely. As an example, let's look at cell e two here, and again, let's do trace dependence here. Again, we have the arrow here, let's double left click on this, and we can see actually that there's a couple of different places where we're using this within formula. Within the same workbook, learn Excel essentials, sheet one, A two, right there it's part of the sum formula that we put in over there. But again, if I double click on that, we're actually linking through to the new workho that we open, which by default, will just be called Book one by Excel. So Book one and then sheet one and cell A one within that. So I can press okay and it will jump in to that cell. Now, I'm going to introduce to you a couple of shortcuts here that actually achieve some really useful functionality in relation to precedence and dependence. So if we hover over our trace precedents, for example, you can see here, Excels tool tape says, we can use control plus the opening square bracket to navigate to precedents of the selected cells, and trace dependence similarly, we can use control and the closed square brackets to navigate to dependence. Now, in effect, this will achieve the same thing as using the user interface. So for example, if I had a cell here that linked through to, let's say e four. And if I was to trace precedents here and I double click on that, I'll just jump to that cell. But if I had e four, and, let's say E seven, and again, I did trace precedents, again, we could click on the individual arrows to jump to those cells. But interestingly, if we use the shortcut here of control and the open square bracket. So if I do that, we'll actually navigate to and select both of those cells for me directly. That's some slightly different functionality than simply clicking on these arrows over here, which is very useful, and we'll see an even more useful example of that when it comes to working with other workbooks. If we look at our example here, where we've got our formula linking through to our Excel essentials workbook and worksheet within there, again, if we just use control and the left square bracket, it will jump us to that cell. Now, if we were to save this spreadsheet, so make sure you save that and then you close that off, If you now try to do trace precedence, and if you were to double click on that arrow as before. Again, we can see it links through to a different workbook. And if we try to jump to it by pressing, Okay, you can see a reference isn't valid. So Excel can't do anything with that. But interestingly, if you use the shortcut here, so control and the opening square bracket, that will actually open that spreadsheet and jump you to that cell. So that's an incredibly useful shortcut to know. To summarize this video then, we've had a look at how you can nest functions in order to have complex functions all within one cell. So you don't utilize extra space that you don't want to, or you want your spreadsheet to look a certain way. We've also looked at how to trace precedence and dependence within the formula's ribbon at the top here. We've looked at how we can show formula, so we can very easily jump through our formulas and see the cells that are involved in calculations. And we've also seen how we can use shortcuts for our trace precedence and dependence, such as control and the opening brace, and we can in fact jump to and select the cells that are directly involved as well. So, as always, thank you for listening, and I'll see you in the next one. 8. General Usage Features: In this lecture, we're going to look at some really useful general usage features, and to start with as always, go to the correct worksheet, and let's look at the data that we have in front of us. So we've got some IDs, some sales dates, which we can't really see, some item names, unit prices, quantities, and gross sales. The first thing we're going to look at is how to change column width or row height. So in order to get this sales date, for example, to show correctly, there's a couple of ways that we can change the width of this column. So one is to simply hover your cursor between columns B and C and to start dragging that with the left click, and you can simply drag that until your date shows correctly. Another way to change the column width is to specify directly. We can do this by right clicking on the column and going to column width and simply typing in whatever width we want. Next, one more method of doing this is to actually select all of your columns, and then you can change the width here by changing any of these columns, and you'll see all of the columns are changed at the same time to the same width that you're choosing. One final, very useful method is on you've got multiple columns selected or one at a time, you can actually double click on a column and Excel will intelligently scale that to make sure that your data fits. Again, I've done that for a single column, but we could actually select all of our columns. And in between any of these columns where that cursor changes, we can just double click. And now each one is differently sized, but size appropriately so that the data and the headers fit Now, everything that you've seen so far can be applied in just the same way for rows. Again, you can drag and change the row there, or you can right click, change row height. Or once again, you can select multiple rows, change them or double click them in order for Excel to choose that scaling appropriately. Another useful feature to mention whilst we're talking about column width and row height is the rap text command. You'll find this on the home tab of the ribbon. Now, let's say that we wanted our unit price quantity and sales to be slightly smaller, but we still want to see those headings. We can use a rap text command here, and again, we can do this for multiple cells at the same time. If I select those and I click Ap text, you can see what Excel will do is have those headers appear on multiple lines. So you can use this command if you want slightly curtailed columns, but you still want to be able to see your headers. Next, let's look at hiding and showing columns and rows in Excel. Now, if you look at our column lettering here as we go across, you'll see we jump from G to K, and you can see in here, there's a couple of lines here. That's because I've hidden some columns actually. So let's look at how we can unhide. What you would do is you would hold your left click on one side of the hidden columns and drag to the other side, and then simply right click on either of these highlighted columns, and just click on Unhide. And you can see that's port in the hidden columns there from H I through to J. To hide the columns again, we can simply select them. Right click and click hide. The reason you might want to use the hide feature is if you have certain data that you use, let's say, to produce your output somewhere, and you don't really want it showing to an end user because it's distracting or it doesn't add any value. So it's really useful. So for example, in here, three reason I've hidden these is, I had simply use the unit prices here when I was producing them all of the different pieces of data here. I don't really need to see them afterwards, so I can just have them hidden away. As always, whatever you can apply to columns can just as easily be applied to rows. Again, we could select any number of rows and hide them and similarly hide them. Okay, so the next feature we're going to look at is free pines. Now, you'll see, I've got lots of data that runs off the screen here, and as I start to scroll down, I lose my headers. So it would be really useful if we could just have our headers frozen in place while we scroll down, and free spines allows us to do that. We can find the free spines functionality within the view tab of our quick access toolbar. Within this, we've got a few different options available. We can freeze panes based on our current selection. We can freeze just the top row or just the first column. So in this case, freezing the top row would allow us to keep the headers as we scroll down, so that will work perfectly well for our situation here. Now, if we add enough columns of further data along here, we might have another problem because as we scroll to the right, we lose sight of our data, and maybe we want to be able to see one or two of these columns. Let's say you wanted to continue to see ID, no matter how far across you went. Now, you could freeze the first column, in which case as you go to the right? You can see ID will always stay on the screen as our subsequent columns show up. But the problem here is our top row is no longer frozen. This is where the freeze based on current selection option comes into play. So to start with, we'll drop this down and we'll unfreeze all of our pains, so there's nothing frozen anymore as we scroll down or to the right. And what we'll do instead is we'll highlight cell B two here, and we're going to use freeze panes based on current selection. And what this will do is, we'll use that as a pivot point. So anything above this and to the left of this will be our pivot or lock point for the freeze. So let's do that. Freeze panes based on current selection. And you can see Excel puts these very faint lines here and here showing you where you've frozen. And as we scroll down, we can see the top row is frozen, and as we scroll to the right, you can see our first column is also frozen, which is exactly what we were trying to achieve. Okay, so here's a quick test for you then. What if I want to freeze my pains such that when I scroll down, I'm always able to see both my header and the first row of data, and when I scroll to the right, I'm always able to see the first two columns. Have to go yourself before I go through that. Okay. So hopefully you work that out, and this is how we can do this. We'll start by unfreezing. And now I'm going to choose my pivot point as cell C three. And that's because anything above this cell will be locked in place, and anything to the left will also be similarly frozen. So we're going to select C three, and I'm going to click freeze panes based on current selection. Again, our faint lines that Excel provides show that we should get the expected behavior. So as I scroll down, you can see Both the header and the first row are visible and equally five scroll to the right. The first two columns are also visible, which is exactly what we wanted. To summarize, then, we've seen how to change the dimensions of rows and columns. We've also had a brief look at the Ap text command. Next, we've had a look at how to show and hide columns and why you might want to do that. And finally, we've also looked at free spines and what a useful feature that is in order to keep your headers frozen as you scroll through masses of data. We'll end this video here. Thank you very much for listening, and I'll see you in the next lecture. 9. Build a Budget Checkpoint 1: In this video, we're going to start to build a useful spreadsheet together from scratch. We're going to build a budget, a really useful personal finance management tool, and importantly, it's something that's going to allow you to start bringing in the various tools and functionalities you're seeing within Excel and start to build up a model of your own. The way this will work then is at the end of each section, we'll come back to this model, and we'll build on it. So we're going to start really simply, but by the end of this video series, you're going to have built a budgeting tool that's grown in complexity and capability into something that really brings together everything you to be learning throughout this course. Budgets come in many shapes and sizes, but at a basic level, it's going to be a tool that's going to allow tracking of income, expenses, and balances. Let's get started then. Open a new blank workbook within Excel. Let's start with some headings in here. We're going to have a month. We're going to have our opening balance. Estimated income and expenses. And estimated profit or loss. Then we repeat that for actual income. Actual expenses, and actual profit and loss. Now, let's expand our column widths here so we can see all of our headings. Let's select all of our columns here from A to H, and when the cursor changes like so, just double click anywhere between the columns, and that will alto expand the column width. Now, let's enter a starting month. Let's say January 2020, and let's enter one more in here. February. Now if you're on the American style date system, you have to oversee Jane this two po2o1. Once we've got a couple of months in there, we can just select these two cells by holding the left click and dragging until they're highlighted, and then hover on the fill handle here in the bottom right corner. If we drag this down, we can see Excels recognize the pattern and it's going to go up in increments of one month for each cell. Let's drag that down to give us a couple of years worth of months and release, and we have our months there. Next, let's enter a few values. So let's say we're starting with an opening balance here of 1,000. And let's say our estimated income is 2,500 estimated expenses, 1,500. So to get the estimated profit or loss, it's going to be a simple formula, so press equals to start a formula, and just click on our cell here, C two. Press minus on your keyboard and click on our expenses, and that's our estimated profit or loss. And the idea here is that you would fill in actual income and expenses as you found those out. So we're just going to fill in some dummy data for now. So let's say our actual income here is actually 22 50, and actual expenses are a bit higher, 17 50. And then we can enter the same formula in here, or we can simply just copy this formula. So click on that cell, hold control and press C or command C on a MAC, and we're just going to base in here with control and V. We have our actual profit and loss in here. Now, in order to get our opening balance for the next month, we're going to have to use another formula, so we're going to start with our opening balance in the previous month. So click on that cell there. And then we're going to add our actual profit and loss at the end of that month, and that will give us our starting balance for that month. Now let's populate in some more data. Let's do this to the end of this year, so we'll go down to December. Let's take all of our data over here, and we're just going to copy this and we're going to paste it here for this next row. And then we're going to copy all of our data, including that new formula for our opening balance, and we're just going to paste that all the way down to the end of that year. And as you're doing this, it is always a good idea. Whenever you're copying pasting or doing anything on a spreadsheet, just run some sense checks yourself. Don't just rely on what you've copied and pasted to be accurate. So press F two or double click into that cell. Just make sure the formulas are picking up what you expect. So if you look at this last one, it is picking up the previous months opening balance and adding the previous months ending profit or loss. Now, let's say we've got this historical data populated in here, and we want to populate for the year 2021, as well, just with some estimated incomes and expenses. So let's change these values slightly. So let's say we've got 27 52,000 respectively for our income and expenses. And that formula can just be copied down or it might copy down by itself if you're on the latest versions of Excel. And you can simply copy all of this and base it down here. We've got our formulas there, and let's also copy our opening balance here and paste it down. Now you'll see our opening balance here actually doesn't move beyond January of 2021, because we're relying on actual profit or loss to be filled in. So this is going to be static here for now. Now, let's add one more thing here. We won't to be able to see our balance movement since the start. So let's add a column in here between opening balance and estimated income. So to do that, we can just right click here on this column, and we can click Insert, and that will add in a new column. And let's just say balance movement since start. Again, let's just double click in the gap between the columns there to expand the column width. Now, our starting movement obviously is going to be zero, so we're not moving at all on the first month, our balance is 100. On the next month, we want to enter a formula, so let's do equals, and we're going to click on B three. I'm going to do minus B two. So we know our balance has moved by 500 since the start, and let's just try copying that down now. So when we go to the next cell, what it's doing here now, it's moved that formula down, changed the relative references. So we're now doing B four minus B three, and that's actually only just giving us the movement between the months, and that's not what we want. We want to know how much is moved from the start, which is 1,000. So we have to amend this slightly. What we're going to do here then is ensure we have absolute cell references on our starting cell that we're checking against. So the B three is fine to move down to B four, B five, and so on, but we always want to take away B two, which is our starting opening balance, since that's what we're looking for here. We're looking for that movement since the very beginning. So what we're going to do is put $1 sign in front of the B here. That fixes the column, and then we put $1 sign in front of the two, which fixes the row, and that's pinpointing the cell to be B two always. If we press enter on that to commit the formula. No change of the first month. But if we copy and paste now, we're going to see if we press F two into that formula. It's now B four, and it's still checking against P two. We can now copy this formula all the way down, and we'll see how much our balance has moved since the start. Before we go any further, let's save down our workbook. So if we're going to file, save as browse and whichever location you find convenient. So I'm going to put mine on the desktop here, and I'm just going to call this build a budget and press save. Now that we've got a monthly breakdown, let's add in a new worksheet here where we'll have a budget overview. So we've added in a new worksheet, let's rename these to be more user friendly. So we call this new one budget overview, and let's also rename our sheet over here as monthly budget. So I'll just double clicked, started typing, and I press enter to commit my changes. Now, let's just drag our new worksheet over here to be in front of monthly budget. And what we're going to do here is have an annual overview. Let's start with a title here. So we call this budget overview again, and a few rows down. Let's have a subtitle here called profit loss, and underneath that, we'll have our projected profit loss. Our actual profit or loss and finally a difference. Now, let's repeat that for income and expenses. So we're going to a projected income, actual income and difference, and again, for expenses. Great. As always, let's just expand our column width here. Perfect. Now, let's enter some formulas in here to bring in our profit and loss income and expenses for an entire year. We'll do this for the year 2020 for now. Let's enter and equals here to start our formula, and we're going to use a function here. Start typing in S, and you'll see it there and just press tab, and that will auto populate with the opening bracket, or you can type as well. And now we're just going to click on our monthly budget over here. As we do that, you can see Excel has entered the name of the worksheet in here, and now we want to get our projected profit and loss here. So that's going to be our cells here from F two all the way through to F 13, and that's going to give us the entire year there. We can just press the closed bracket there and press enter, and that's our projected profit and loss for the year. You know what to do next. So if you want to go ahead and start populating each of these with sums, so we're going to do actual profit and loss in here. For these cells and a difference. Now we're going to do this as actual minus projected, and then we're going to repeat this four income. That's going to be a sum on column D here for the 12 cells that we want. Then actual income will be here and G. Remember to put in the sum, and finally our difference as before actual minus projected, and then expenses once again. So estimated expenses is going to be a sum here, and our actual expenses are right there. Now, for the difference here rather than doing actual minus projected, we're just going to do it the other way around. We're just going to do projected minus actual just so that we see that our actual was worse. It's up to you if you want to be consistent with the formulas and still do actual minus projected here. This way it's just easier to see at a glass if you see a negative, you know, it's not what you want to be seeing basically. Okay, now, make sure to press save on your workbook, and we're going to leave this one here for now, and we'll come back to it later in the course and improve and build on it. Think about the things that we've incorporated already into this. So obviously, data entry, using the fill handle, using multiple worksheets, using formulas, licking across different worksheets for those formulas, using absolute cell references, inserting columns. So there's a number of basic skills that we've already started to use in building this up. Keep this spreadsheet saved in a handy location, and we'll come back to it. So, as always, thank you for listening, and I'll see you in the next video. 10. Find, Replace, GoTo: In this video, we're going to look at the find, replace, and go to features within Excel. As always, turn to the correct worksheet, and let's analyze what we've got in front of us. So we've got a column of customer numbers? We've got some product names, some quantities, unit prices, net amounts, and gross amounts. Now, this sheet is structured such that it relies on the product name to find the correct unit price and populate it within this table over here. And we've got a problem here immediately because we can see the data we've been provided has a misspelling here, which is therefore not returning the correct unit price. So how can we get around that using fine and replace? Let's start by making sure you're on the home tab of the Quick Access ribbon, and then you'll find the fine and select options here on the right. So let's look at Fin first of all. So let's say we wanted to find how many instances of this misspelling we've got within our data. So we're just going to search for that in here. You can see using fine nex will go through each one. Now, if we wanted to see all of them at once, we can just click on the fine all button, and you can see here Excel tells us that there's 22 cells where we've got this misspelling, and we can click on each one very handily like that. So we've got the book that we're in the sheet, the cell, and the value. Now we can go ahead and replace this as well. So that's what we're looking to find here, and I've already typed this in, so we will just type in the correct spelling here. So we're missing an M there, and we just put that back in. And again, we can replace a particular instance, so I can just replace that, and you can see that replacing as I go. Or we can simply do replace all. We know we've got 20 or more of these left now. So I I just do replace all, cell will make those replacements and let us know. And you can see now the correct net amounts are being calculated because we've got the correct unit price being pulled in. Let's look at some of the other options Excel gives us when we're using the fine or the replace function. This time, I'm going to go to replace. And now by default, whenever we're finding or replacing, we're just working on the work sheet that we're in. However, if you expand the options here, you can see we can change from sheet to workbook, and we can also change, for example, to search by columns rather than rows, we can match case, the entire cell contents. There's a few different options here that are useful depending on the circumstances and what we're searching for. So we can expand our fine and replace to cover the entire workbook, but we can also go the other way. We can look within subsections of the worksheet. So, for example, if I wanted to only find something within a particular column, I could just highlight that or a couple of columns row similarly, that would limit my search area. So let's say, for example, I wanted to find quantities of 18 within just this highlighted area. Now I can see there's other ones down here. So let's just make sure this works as expected. With those rows highlighted, I'm going to go to find here, and I'm going to search for 18 and just make sure that I'm searching within the sheet only this time. I'm going to click on Find All. And you can see it's only found the one when we know there's obviously other quantities of 18 down here. So again, we could test that by, let's say, covering the next one, find all, we'll find do this time. But of course, there's another one down there. So if we were to leave that all alone, we will of course find many more instances of 18 throughout this sheet. Okay. So before we look at the next feature, which is go to let's just do some setup work here. Now, let's imagine a scenario where we receive data that has gaps. So let's say we've got a few random rows of deleted data here. And let's have the data itself is complete. It's just the format that we've been provided. We've unfortunately got these gaps in between. Now, it's going to cause a problem if we were to try and filter that data. We'd have to manually make sure we select the entire set of data. What we really want to do here is get rid of these rows, and we can, of course, do this manually, so we could start selecting each of these one by one, and you can do that by clicking on the row, holding control on your keyboard, and you can see we can start to select all of these rows one by one. We go through that. And then we could right click and press delete or use the keyboard shortcut, and we would get rid of those gaps. I'm going to undo that for a second. Let's get back to here. If we had dozens or even hundreds of rows with that problem, that wouldn't be a very efficient solution. Now. This is where the Gu functionality comes into play. We can find the G to functionality from the fine and select menu once again. So inhre, and then GT. Now, what you'll see here are the recent cells that you've been on as well as any named cells. You can also type in cell references directly and Excel will jump you to that cell. That's not the most useful feature here that we're looking at, however. The option that we want to use is within the special area of the go to functionality. So we open that menu and we click on special. Now, there's various different criteria that you can pick on, for example, to find any notes, any constants, or to go to the last cell. So it's a quick example going on the last cell here. That will jump us to the last cell from which we could select the entire data set from. So if you were to drag from there, you would be able to select all of your data there. So what you just saw there is the unique aspect of GOT, which is that it actually selects the particular cells unlike Fine, which simply shows you those cells, then you can select them individually. So how can we use this to get rid of these extra rows within our data very quickly? So we can use the GT to special here. So what we'll do is, we'll select the columns that are affected, and I'm going to go into the Fin and select. And we can now go into the GOT to special menu directly or you could go into GOT and then press special? Either way achieves the same thing. And this time within our selection, we're going to say to Excel, fine and select any and all blanks. And you can see immediately Excel has selected all of those blank rows there, which means that we can now very easily right click anywhere within these highlighted rows and press delete. And because they're blanks, and we just want to shift all of our data up so that it is contiguous, we can just do shift cells up. And note here, for example, you're going to have this row go up, so we're going to have three and then the six, et cetera. There we go. So all of our data is now together. With that, then, we've seen a really good practical usage of the go to functionality. And there's other very useful options here within Fine and select as well. For example, within any data that you've highlighted, we can click on formulas, and Excel will pick out where we've got formulas within our data. We can also similarly find constants, and then we can also look at any notes, conditional formatting, or data validation. So really useful features in here to find certain pieces of data at a quick glance. We'll wrap the video up there then, as always, thank you for listening, and I'll see you in the next one. 11. Formatting: In this video, we're going to be looking at formatting and talking about how important it is in order to display data effectively in Excel. Start by moving to the relevant worksheet, and we have in front of us a subset of data that you've seen already. However, looking at it, you can see immediately how user unfriendly it is without any formatting in place. Let's take a look at what we can do to improve the visibility here. So to start with, you'll probably want to have something differentiating your headers, so we can just put in a color there. Now, the next thing that sticks out immediately is that we have a date column here, but clearly it does not look like a standard date. And that's because Excel stores dates as sequential numbers. So the data behind it is correct, but the formatting isn't, and we'll talk more about dates in a later video. So for now, let's change this to the format that we would expect. The way we can do this is we can select the data here, or we can select the entire column here, and we can just change within the home tab up here from general to date, and there's a couple of options here. Let's go with short date for now. There we go. Is much better. What you have here in this handy drop down box we've just used are typical formats for text, dates, numbers, et cetera. There's a really quick way of changing data as you need. Now, if you want more control, you can actually right click on a cell or cells, and you can go to format cells, and here you have many more options on how to format your data. For example, we could choose different types of date formats as we might want. Just like that. Let's look at some of these other columns in order to make them more user friendly as well. For example, let's look at the number formats that we have here. All of these items here should be number, let's change them up here. And you'll see when we do that, the default there is a couple of decimal places on every single cell. Now we can change that as well as we want. Let's keep those items highlighted and we can actually add or remove decimal places as required. Let's leave that at one decimal place for now. The other thing you might want to do with numbers is add in a common style thousand separator. Simply pressing that, again, that looks much more user friendly immediately. Now, another thing you might want to do is show your negative numbers in a different color potentially. Again, let's highlight the data that we want here, right click, and let's go into format cells again. This time, we want to make sure that it's a number, and we've got a few different options here, so let's pick this one here where we keep the negative sign and we color it in red. There you go. There are options to customize your formatting even more. So let's take a look at this once more. If you go into custom down here, you'll see what we've got right now, and this is actually quite intuitive usually as well to work out. So for example, if you wanted that text not to be in red, blue, simply replace that color there, press, get the results as expected. Now let's go back into that format cells once more. What you can see here is actually fairly intuitive because you've got a sample here of your data. So you can see here what we're saying is we want 10,000 separator. If I was to remove that, you can see that goes away, or equally if I was to get rid of these hashes here. You can see that coma appears before the number itself, which we obviously don't want. So if you want it, you could fiddle around with the formatting yourself. And again, you can change the decimal places here as well to add more or less quite easily. So you've got the formatting for any negative numbers here with a color, and then you've got your positive numbers in the same way here. And again, you could add in a color at the front if needed as well. Another very useful option for formatting is using the format painter. Now, this allows you to take the format from one cell and apply it to another. So a quick example here. Quick ones there, and then SCA here, and you can see we've got the same format as this cell over here now. Now you can drag and cover multiple cells. In fact, what you can also do is double click the format painter. So you've selected the cell you want to take the formatting from, you double click the format painter, and now you can continue to click one at a time or drag and you can see that format painter stays highlighted so you can cover off multiple as of your spreadsheet as required. When you're done, just press escape. Next, in terms of basic formatting, there's a couple of more useful items to look at. So for example, if we wanted this to be displayed with a currency, you got the option within here. Now that will default to lightly whatever your original language is set up, but you can, of course, change that currency there. So if you go into format cells, and on currency, you can see the symbols are all available in here, so we can change that to whatever you want just as easy as that. Next, let's consider that we have profit over sales. We want to express that as a percentage. Now, by default, Excel will give you a decimal. However, you've got this handy percentage style button over here, and clicking that will give you the percent. Just as we did with numbers, you can add in any more decimal places as required. With that, we've covered a lot of the different basic formatting options available, there's a few other ones such as how you would align your text or wrapping text, but these are things you should be familiar with from Microsoft word, very straightforward, pool, italics, et cetera. So we won't go into detail on those. A much more interesting form of formatting is conditional formatting. This is an incredibly useful tool for highlighting items that are exceptional in some way. For example, if you wanted to look at all values above a certain amount in green, and those below another value in red, we can achieve this with conditional formatting. As always, let's look at an example. So let's move across to our manufacturing price data over here. Let's highlight that and click on conditional formatting here within the home tab, and let's look at highlight sell rules here and greater them. Now we can specify a particular value, so let's say 200, and let's say we wanted that to be green fill with dark green text. So any data that satisfy that condition is placed in green here. And then we can layer rules on top of this as well. So we could also highlight for those values less than a certain amount. So let's say anything under 50 we would have in red. And you could continue in this way to add rules on top of each other, which is incredibly useful. So let's undo those a second and let's look at some other options here. So we have, for example, top and bottom rules here, so we could pick out top ten items and color them as we want. We also look at that as a percentage, similarly for bottom ten and bottom ten percentage, and we also look at averages. So anything that's above the average of the cells that we've highlighted in a particular color. Next, we have data bars, color scales, and icon sets. These all work on essentially the same principle. Let's start with a data bar. You've got different options for colors, but they're all performing the same task. So when you've highlighted the cells that you applied this to, Excels looked at the values and it's seen that it's ranging from zero up to a maximum of 360.5 here. So what is done here is it's filled in these bars according to the values. So when you've got something that's one third of that 360, for example, here at 120.5, you've got one third of that cell filled in with the bar. So what this is is a visual tool to allow you to quickly see values that are in the mid range or on the higher end or on the lower end very easily. So let's undo that. And now let's look at icon sets. And the same principle applies essentially for all of these different options here. So when we choose, for example, a icon set with three icons, what's really happening in the background is Excel split that data into three parts. So up to 360 approximately, so you've got anything that's around 120 and below, we'll have the red down arrow. Thing 120-240, will have this yellow arrow, and anything above 240 up with the maximum of 360 has the upward facing O. Obviously, this will change depending on the data set that you're talking about. Obviously, will not apply on 120-24-0360, but it will take the maximum value here and split that into three parts. And so you've got the same options here for further splits into four equal or five equal parts and with different shapes and indicators available. That covers formatting and be sure to have a play around yourself to try the different options we've discussed here. However, as you've seen, the tools are very intuitive and there's a straightforward process to being able to apply basic formatting, or the incredibly useful conditional formatting as well. Thank you very much for listening, and I will see you in the next video. 12. Filtering & Sorting: Hello, and welcome. In this video, we're going to be talking about filtering and sorting data. Now these are both great tools for whenever you have slightly larger datasets in Excel in order to view and analyze effectively. So to start with, jump to the filtering and sorting worksheet. And let's look at what we have in front of us. So we have sales related data here across the various columns, and we have headers as well as titles for each of those columns. And that's very important because when we look at filtering as our first port of coal, if you go to the data tab at the top here and just make sure you've clicked anywhere on the header, it doesn't matter which cell, and if you click on filter over here, You'll see Excel adds in these little drop downs to each of the headers, and this is how we can filter in on data. For example, here, we've got multiple countries here, and if we open that up, we can choose to look at just one of those countries, and you see the data is now filtered, so we're only looking at what we've chosen here. You can continue to filter down further. So if we only wanted a particular product. So let's say the ATR. We can look at that. So you can see we've got filter here on Column C and on Column D now, and you can continue in this manner. Now, in order to remove filters, you can remove them individually. If I just wanted to undo this, I could clear filter from product and then clear filter from country to go back to where we started. Now I'm going to reapply a couple of filters here and show you how we can get rid of all of the filters. So in order to clear everything, you can use the clear button here and all filters will be reset. An additional useful feature of filtering is that you can pick multiple items, so we could pick a couple of countries here just as easily. Now we can also use the functionality to type in what we want. So we wanted to find Brazil. So we could do that. Now you've got to be careful here because if I was to choose just that, we would have just the filtering applied for Brazil, so we wouldn't see the two countries we've picked already over here. So if I was to do that, you can see we lose the previous filter. So if you want to keep what you're adding in, so let's say I've got Brazil right now, and I want to add Spain as well. It's important that once you type something here, you click add the current selection to the filter, and now you see we have Spain as well as Brazil here. Now, it's obviously much easier in situations like this to simply take the ones that you want. The reason you might use that is when you have many different distinct options. So if I had only chosen a couple of options here, if I wanted to add in, let's say ID 500 to this, it's probably easier to just type that in. Make sure I add it to the current selection rather than scrolling down the list. Now, let's reset all of our filters here. Let's look at some of the more complicated filtering that you can use. You have the ability here to use text filters. We can look for text that either equals or contains or does not contain in various other options in here. Let's say we were looking at a product over here. Went to this and we said, does not equal Alpha. You see strips out what we've chosen and only leaves the other items without Alpha. Important to note that whilst we've typed in does not equal Alpha, we don't actually see all of these items ticked here and Alpha unticked because we've actually chosen to go via a text filter as opposed to choosing from distinct options here. That's why you see this setup in this way. So Let's undo that. The other options are just as straightforward as the sound. What we'll look at next though is dates. Within here, you can see it's recognized as a date by Excel. We've got lots of different options here, such as looking at dates today, this week, last month, et ce. You've got some ready to go filters here, but you can also choose specific dates. Let's say you wanted to choose a particular date. You could use the calendar option here. Let's say you only wanted to look at things from 1 November to 31st of December 2019. You can see our filters applied there, just as expected. You can also look at other items such as dates before or after or even equal to specific dates, as well as choosing as normal. Let's clear that out. Now let's look at some numerical examples here. Again, a number filter rather than a date or text filter here because Excel has recognized the type of format here. Again, we've got options here such as choosing values are in a top ten above a certain average or greater than or less than. For example, if we only wanted to see sales that were greater than 50,000, Press okay on that, and you see 310 out of 500 records found matching that condition. Filtering is very straightforward. Therefore, a quick exercise for yourself. Can you filter effectively to show sales in Spain with a profit 2000-5 thousand, pause the video and give that I go yourself, and then we'll look at it together. Hopefully, you manage that. The first thing to remember is you've got to clear the current filters, so we do have one here, so it's going to click clear, so we go back to the start. Now we just want Spain here and profit is over here, and what I want is to pick values between two values. So I want bet 20005000. That's, and there we go, exactly like we wanted. Now, a useful item to talk about while we're on the topic of filters is auto sum. We mentioned in a previous video how autosum respects filters. Let's take a look at that right now. We've got a filtered subset of the entire data, and if we use auto sum here, you'll see it doesn't put the sum formula. I puts a subtotal function in here. And the nine there indicates that we're subtotaling across su as opposed to average or other options, and that's the range that we're looking at there. You'll see it's running from L to old way to l501. However, it's just a subtotal. Only looking at these particular items here. You can see there the value that we get of 114,514, and you can confirm that by simply highlighting these items here and you can actually see the sum here as well to confirm that the subtotal formula is working as you would expect. Now if we were to clear the filters here, Let's just go to the end, CR formula. You can see it's still a subtotal is running across the same range, but because the filter is now cleared, it's actually summing up the entirety of the data. So that's just over 5 million there, and we can confirm that once again by simply highlighting all of that. We'll see the same figure over there. That gives you a really good overview of filtering and how useful and easy it is. Let's get rid of our subtotal here and let's clear our filter. Next, let's talk about sorting data. So With filtering and clearing filters, you've seen that the data goes back to exactly how it was with no change. Now, sorting is different because it will actually change the order of your data here, and the best way to see it is with an example is always. So let's start by clearing our filters here. There's actually a couple of ways sorting works, but we'll start from the very beginning. Within the data tab here, you'll see there's a sort button. So let's say we wanted to sort on sale. So let's just click over here anywhere on the head will do in fact and click sort, and a window will pop up. You have options here to sort on a particular column. Let's say we want to sort out on sales, and we're going to do it on the values, and let's go largest to smallest. When I press okay, you're going to see the entire data set shift. There you go. Now we've got the largest sales at the top, working our way down to the smallest. Now, it's important that if you did not want the sorting to be permanent, that you immediately undo that action. So up here, we can use undo and we'll go back to our previous state. Now, that's one way of sorting, but a quicker way actually is to probably introduce the filter here again. And over here, actually, we've got the same options for filtering, but you'll see we've also got options here for sorting from smallest to largest or largest to smallest again based on the fact that Excel recognizes the data within this column is numerical. So we could achieve the same thing here by choosing largest to smallest. Now, it's important to understand that we can't simply unfilter or undo the sorting directly from within the options here. What we can do, however, is use the generic undo option here on the top left to go back to the previous state. An important thing to note with that is it's only available in the current session that you're in. So if you had sorting applied, so I've just redone the action there, and you would save this workbook, come back to it later, you would not be able to undo. So it's important to keep backups of your original data where the sorting is potentially neither smallest to largest nor largest to smallest. So just as with filtering, there's different options of it's text based data that we have. So for example, you can sort alphabetically from A to Z or vice versa, and similarly with dates, we can again sort oldest to newest or newest to oldest. Those are the basic contextual options that open up for sorting based on the type of data found within each column. There are however more complex options available as well, such as sorting by color or even custom sorting. Let's consider sorting by color. So let's say we've got a few cells in here that we've highlighted to be looked at for whatever reason. L's put those in yellow. Now we can sort here by color, so we can sort by yellow to bring those cells to the top, or we can sort by anything with no fill in this case to push those right to the bottom. Now, sorting by color, alphabetically, smallest largest and dates, oldest and newest, and vice versa are the basic options you can choose from the drop down menu here. Let's undo what we've done here to go back to the start before we color those cells. Now let's look at some more options available from the sort menu here. We've got options within here to sort out on cell values and colors as we've seen, but we've also got font colors. Now, in this case, we don't have anything colored in. Let's just change the font colors on a couple of these here. To go here and just use a different font color there. Let's go back into here. Sort. Let's look at profit, we've changed the font color. You see we've got the option here to order either by the basic font color or the one we've chosen and either on top or on bottom. Let's just push that to the end. You can see that's gone all the way to the end here. Now let's scroll up to the top. The final one that we've got left here is conditional formatting icon. Now, conditional formatting is something we're going to look at later on, but you've seen how to use sorting and filtering at this point. So it'll work very similarly to how we use font colors. Once you have conditional formatting icons, you can simply choose those and choose to either on top or on bottom. So let's undo a couple of times to get back to where we started. Now, to be able to sort with more flexibility, you can actually use the custom sorting for more powerful options, so we can go in via data and then sort, or in fact, if there's no colors or anything on here, we can actually go with custom sort here and we'll get the same window. Now, we can sort by a couple of different layers. For example, we could first sort by product. Let's say we want to go eight to zero on product. Then we can add a level to say after that, we want to look at, let's say, sales from largest to smallest within each product. Press okay on that. Now you can see we've got all of the Alpha products followed by the next in the Alphabetical chain, as you would expect. And then further we've sorted after that by highest sales. We've got the highest sales for the Alpha product all the way down to the lowest, and that is followed by the ATR sales again, starting with the highest going down to the lowest. So we'll end the lecture there, and to recap, we've covered filtering data, whether that's from selecting the drop down, typing in selection criteria, adding your selection to the current filter, numbers and text filters. And then we've looked at sorting data as well. Again, we've looked alphabetically, numerically, date order. We've looked at cell colors, and finally, we've also looked at sorting by multiple sorting levels. So thank you very much for listening, and I'll see you in the next lecture. 13. Copy & Paste: In this video, we're going to look at the various options within Excel to copy and paste data. Now this might sound quite straightforward, but there's actually a number of different things that you can achieve in Excel. Let's take a look at these. Once you're on the same worksheet as I am, let's take a look at the standard copy and paste function. When you copy and paste a standard, what you'll get is formats, formulas, and comments are all copied across. If I use control C, and then I control V across here, you'll see I get the same value or over here with some formulas. I I copy across, you'll see I get the formula with the relative references, and the comment is actually copied across as well. Let's just undo those two. Now, you've got a full site of options available for different pasting options. So if we were to copy here, and let's select where we want to paste and right click. Let's go to paste special here. So here are all the options that you have. You can paste everything, which is the standard, or you can paste just formulas, just values, formats, even just the comments, if you want, and a few other options related to formatting, for example, everything except for borders, column widths, only, et cetera. And there's actually also options here for addition subtraction, multiplication and division. So for example, it would keep whatever value or formula was in there, and then it would add whatever you have from the source, so the cellar copy from, whether that is a value or a formula. Now, let's consider that we don't have any values in here, and we want to copy this formula down. But the comment here is simply showing you that this is score divided by number of customers, and we only want that in the first cell, let's say, we don't want to copy all way down. Standard paste with Control C and then Control V is going to put comments in every single cell, which we don't want. Instead, what we can do is copy, select the cells that we want, right click base special, and this time, we're just going to choose specifically that we just want formulas and press. There we go. We have our relative reference formulas, but we don't have the comments just as we want. Now, another way of doing this actually is when you copy and you right click. You actually have certain standard options that are often used available immediately here without going into base special. So you have the standard base. You also have values, formulas, which is what we've just used, transposing data, so your rows and your columns will be interchanged and taking just formatting and finally any links. So in this case, we can just use formula. Now you'll see as I hover over these. Excel actually gives you a very good preview of what's happening in the background in case you need to check. So let's put the formulas there, and we'll get the same result as through going from the menu. Now, there's a couple of more really useful options to be aware of when it comes to copying and pasting data. So let's just delete this for now. Of course, we can use the fill handle here to just drag down and we will get just the formulas here. The other thing you can do actually is, if you highlight, including the top cell that you want to copy from, and then you use control and D on your keyboard, and that will actually copy the formula in the top cell down into subsequent cells. And this works also for control and R. If you want to take things to the right, so let's start here. We've just got a value in here and no formula. If I drag select here, it's going to take whatever's in the left most cell. When I use control and r, it's going to paste in from that cell into subsequent cells. So that's a really useful method as well. Now, control D and Control R, those shortcuts on your keyboard are particularly useful when you have lots of data. You don't really want to be dragging this fill handle down all the way. It's a lot quicker to simply select what you want and just do a quick control D to get the values exactly like you want them. Now, before we wrap up, there is one other case to talk about when it comes to having filters applied and pasting where things can go a little i. So let's look at that. If you just select those columns and unhide, we've got some scores here that we're going to copy and paste in. Let's filter over here for, let's say 14, 16 and 17. Now, if we were to try and copy these scores in, copy and then paste. You can see we don't get the expected result. We don't get those scores copied all the way down because we've got, in fact, with the filter applied, we've actually got a jump here from row seven to row ten with what we've selected on the filter. So how do we deal with that? Let's begin by clearing our filter and we can confirm that the data copied down without respecting the filter. So it just copied straight from rows five to nine. And so what we'll do is let's get rid of this data for now. Then in order for this to work correctly, what we need is for all of the rows to be lined up. There's no gaps when we filter so that we can copy and paste in bulk. Now, what we can do therefore is mark the cells that we want. Let's filter once again on 14, 602017, and these are the four cells that we'll want to paste the data into. Let's mark them with a color hair in yellow, and then remove that filter again. Now, it's very important to note that what we're going to do is actually going to change the order of these cells, and you want a method to be going back to what you started with. So let's actually put an ID in here and you'll see the importance of this once we're done. I'm just going to copy that all the way down. So we have our unique order there, and I'm just going to clear the filter and reapply so that it captures the additional column as well, and we can just capture the format in the same way as well. Now we have all of the cells that we want to be able to past data into highlighted. And that's important because what we're going to do now is we're going to sort by color so that we get them all grouped up together. It doesn't matter if it's at the top or at the end as long as they're together in terms of sequential rows, so you can see rows three through six, our highlighted rows, there's no gap here, and we can confirm this when we filter as we originally wanted. O original task was to filter on 14, 16 and 17, and we wanted to paste in these scores here. So if we copy and paste now, you can see that's gone in correctly. We can clear the filter. There's no issues there. And this is where the ID that we've placed is very important because you can see obviously the overall ID changed when we sorted. So we can go back to the original sorting order by simply sorting on ID from smallest to largest now, and our data is exactly like we wanted it, and we've essentially respected the filters when we copied that data in. So that's a good little trick to be aware of in terms of dealing with pasting in data when you have filters applied. So that's a quick overview of all of the different copy and paste options that you have within Excel. Do have a play around yourself. And thank you very much for listening, and I'll see you in the next lecture. 14. Workbook Protection: In this lecture, we will look at protection of cells, worksheets, and even the entire workbook. Let's begin by jumping to the relevant worksheet. Now, there are scenarios where you will want to supply an Excel workbook that is protected against certain changes by other users. This could be the entire workbook, a number of sheets within the workbook, or particular cells. Let's start by password protecting the entire workbook as follows. Navigate to file, save as, browse, the navigate to wherever you want to save your file and rename if you want. Then click on tools, general options, and you have a couple of different options here. You can place a password that will restrict even opening the file without the password, or you can allow users to open the file without a password, but are required to use a password in order to make any modifications. So let's put a password in here, test in order to open it, and let's press okay on that. I will ask you to re enter your password to confirm and let's save that. Now, let's check that that has worked as we expect. So close down the Excel sheet and browse to it and try opening it once more. This time, I'm prompted for a password. So I enter that and I'm able to go into the file. Let's quickly look at the other option that we saw as well. I'm going to save as once more, and keep the file name the same, and I'm going to go to general options. This time, I'm going to take away the password to open, put the same password for modification. Now, you have an option here for read only recommended as well. Checking this will throw up a pop up box whenever a user tries to open the spreadsheet, recommending that they view this spreadsheet in read only mode and don't modify anything. Once again, let's confirm the password, save, place that file, and once again, let's close the file and try and reopen it. This time you can see I can enter a password for write access or I can just carry on and do read only. Let's enter this as you want. Now we see the prompt that we chose as well that I would be preferred if this was only open as read only. Once again, you have a choice, but we've already entered the password and we don't want to enter as read only, so I'm going to click no. Now we have the full access that we normally would. Next, let's look at the protect workbook option. So move to the review tab at the top and click Protect workbook. Here, you can see we have an optional password. Let's leave that blank for now, and you can see it will protect the workbook for structure. So let's see what that does. If you press okay, you can see that's now toggled on. I can toggle it off without a password because I didn't supply one. But what that means is, I can no longer move or delete any of the worksheets within the workbook, and I can't add any either, so the structure of the workbook is protected. We can toggle that off. In this case, there's no password, and you can see those options appear again. It's a really great way of protecting the overall structure of your workbook. Now, it's important to note that all cells can still be edited and you can perform any other actions within all of the worksheets there. So that's where the next option comes in, that is protect sheet. Again, let's toggle that here. Again, there's an optional password. Again, just for ease of toggling it on and off, I'm going to leave it blank. You can see you have many more options here on what you want to allow or disallow when this is toggled. Let's leave the defaults here. We're saying is locked, that users won't be able to do any of these actions that are not ticked. So they won't be a to delete columns and rows, for example, nor change formatting. Let's press okay on that, and you can see it's protected now. And if I try and do anything with a column, I can't insert or delete. And if I go to the home tab here, you can see all of the formatting options are taken away as well. So that's a good way to protect the structure of particular worksheets. Now, do note that you have to apply it to each worksheet individually. So let's unprotect that to get back to where we started. Now, one potential flow of the protect sheet option here is it actually prevents you from editing any cells. And the way to get around that is to allow edit ranges, and that's the final option we're going to look at here. So let's click on Allow edit ranges here and select a new set of ranges. So we're going to select the quantity here, and that's in the title there as well. And we're going to say it needs a password of test once again and press F the password. Apply that and press k. Now, that's not going to do anything by itself, so you can edit any cell at this point in time. However, once you click, protect sheet, and again, let's apply that without a password for now. You can see when you try and edit cell that we've allowed the edit ranges on, we're prompted for a password. So it does allow us to edit as long as we enter the password. So if I give it that password, I can now happily edit that, but I can't it any few other cells where I haven't put them into an allow edit range. So in summary, that's a very useful option where you want users to only be able to edit certain cells. Perhaps you've got input cells in a spreadsheet, but you don't want them to be able to change any of your calculations that you've set up in other cells. As a final point, all of the options we've shown are really only useful with a password in place. However, I've done this without passwords just because it's easier to showcase the functionality. To wrap up then, we've seen password protecting workbooks for open or modified permissions. We've seen protecting the entire workbook structure to disallow changing, moving, deleting worksheets within a workbook. You've also seen how to protect worksheets to prevent any editing of data. And finally, you've seen how to allow edit ranges on certain cells where you do want users to be able to edit. So once again, thank you very much for listening and I'll see you in the next lecture. 15. Build a Budget Checkpoint 2: Hello, and welcome to the second video in our Builder Budget checkpoint series. In this video, we're going to carry on with a model that we started in our first checkpoint and build more features into it. So to get started, if you open up the file that you hopefully saved from the last time, if you didn't save one or you can't remember where you saved it, I have attached where we would have got to at the end of CheckPoint one, and you can find that attached to this lecture. So go ahead and download that and open it up, and let's get started. Let's start by changing some of the formatting on our spreadsheet here. It's pretty bare bones looking right now and doesn't really stand out. So let's start by selecting our titles here on our monthly budget tab, and let's just put that in bold. You can either click here or use the control B shortcut on your keyboard. Next, let's change all of our numbers here to have thousand separators. If we select all of our data and we click here, we'll get 1,000 separator, and we'll also probably get a couple of decimal places by default, and we don't need to see those right now, so we can just click a couple of times here to decrease the decimal point. Now, for our months, let's select all of our data here, and let's right click and go into format cells, and we're just going to change our date format here. We're already on date here, but if we go to custom, we'll see a few different options in here. Let's choose the one that gives us just our month and our year. So if we select that and press ok, we'll see our formats like that. Now let's move to our budget overview tab here, and let's just make our titles and our headings stand out a little bit more. So starting with our budget overview over here, let's increase the font size. Let's choose a different font color, and let's put that in bold as well. Let's do something similar for our subheadings here, and we're going to change profit and loss, income and expenses. And let's do them all at the same time. So if we click on one of our cells here, and then you can hold down the control key on your keyboard and just click on these additional cells. You can see all of them highlighted. And again, we can just change our font color and put those in bold as well. Next, let's make any negatives within our figures here standard a little bit more clearly. And so we'll see this in our differences here. So let's select each of our cells again using control and clicking on all of our cells. And now let's right click within any of these and go to format cells once again. And we want to change our category here from general to number. And then within that, we can show our negative numbers, for example, in red or in red with a minus sign at the front. So let's go with that last option. Let's once again use 1,000 separator and let's do no decimal places and press. Now, our differences stand out nicely, but we can immediately see that the formatting is slightly different because we're missing 1,000 separator here, unlike with our differences. So we can remedy that very quickly by using the format painter. So click on the cell from which we want to copy the format. And if we click once on format painter, we can just drag on the cells that we want, and those will now have 10,000 separator. Now, when we use format painter in that way, as soon as we release our click here, it turns itself off. Instead, what we can do is click on the self from which we want to copy our format. Double click on format painter rather than just clicking once. And now we can still drag and copy that format. But this time, the format painter doesn't uncheck itself, so we can continue and just left click once here and again. And once we're done, you can just press escape on your keyboard to come out of format painter. And now all of our numbers are nicely aligned in their format. So as a final finishing touch here, let's select our three differences here again. And let's go to our borders here by clicking on the drop down menu here. And let's put in a top and thick bottom border just to really make those differences stand out. Okay, so we'll leave our formatting there, and we'll look at adding a bit more functionality to this spreadsheet now. So we've got a monthly budget here, but it's quite bare bones, and it's simply simple numbers typed in into each column for our expenses and our income, whether it's actual or estimated. And it would be good if we could have a budget that allowed a bit more detail. So that's what we're going to look to build here. So let's start by creating a new worksheet, and we're going to call this expenses detail. What we're aiming for here then is to create a bit of a breakdown of our expenses, which can then eventually link in to our expenses over here, so that we can have detail behind all of our summarized numbers. To start that, we'll begin with a few column headers. Let's start with ID, date, category description, estimated cost, actual cost and difference. L et's select our columns as always and make sure we've got the column width appropriately set so we can see all of our headings, and let's just put these in bold as well. Now, let's populate some data in here. We'll start with our ID being one for our first item. We'll have our date as 1 January 2020. We'll start with a category of food, let's say, and a description of restaurant. L et's have an estimated cost of 100 and actual cost of let's say 150 for that month, and our difference is just going to be our estimated cost, that's our actual cost. Let's add in a few more items. I think for each month, let's just go for maybe six items here, and I'm just going to copy my dates down here as well. Let's just add in a few more items. We'll have food and let's say groceries in here. Let's go for 300400, and we can also copy our formula down here to cover all of the data we're expecting to enter. Let's sit in a different category here. We'll go with rent and let's say we know that cost is the same every time, so we'll enter and estimated an actual cost that's the same. Then we'll do broadband or Internet here. We'll go for 5050 here, and then again, we'll do electricity. Let's say, with 100150, and finally, we'll do another category here, let's say transport and train with 202 50. Now let's change our date format here to match what we had on our monthly budget. So we can actually take that from here as well. We can just click on the format painter a couple of times. Go back to our tab here and just drag over our cells here, press escape, and we're done. Now, what we have in front of us is a breakdown of the cost that we have in here. And the idea is that we can actually link through to our new worksheet. So for our estimated expenses rather than just having 1,500 typed in there, we could do an equals sum switch to our expenses detail and select these cells here and close the bracket and press center, and we'll select the 1,500 because we chose our numbers like that. But it means that if we were to change anything in here, we would see those changes reflected in our expenses here. We've set up our actual costs in the same way. So again, for our actual expenses, we can do an equal sum, and we can just go over here and select our cells and pre center, and we'll have our expenses linking through to our expenses detail tab as well. So let's just talk about the idea of what we're trying to build here. The concept here is that we're able to have our breakdown for every month in the expenses detail. And typically, you won't have the costs in here when you're on the latest month. So you'll have your estimated cost. And then as those actual costs come in, you fill those in in here and they'll feed through to our monthly budget, which in turn will feed through in terms of the overall annual figures to our budget overview. So in our example set up here, we're saying that we know our costs in terms of estimates and actuals all the way to the end of 2020, and then we've got estimates in, but we don't have any more actuals. So let's set up our detail in the same way here. So we need to repeat this set of data 23 more times to get our 24 months in here. What's the quickest way to do that? Well, we can start by just copying the items that will stay the same because we know we want our IDs on our date to change, but we're going to keep these the same. So let's copy that first with control C, and we'll paste that here. And just to speed this up. Now that we've pasted the next set, we can just grab both sets and paste it again. So we've now got four and now copy those, and all I have to do is just past those four again, and I now have eight. Let's take that again. Just make sure you're keeping track here, so now I'm pasting another eight, so that's 16. And then finally, if I paste the last set here. I've got one extra set that I need, so I can just get rid of some of these ones here at the end, the last six, and that should give me the months that I need. And what we need to do now is copy down our IDs and our dates. Now, there's different ways you could do this. You could start typing in each of those dates and do one month at a time. But there's a clever way of doing this, and what we can use here is the EO month function. If you press equals and type EO month, and let's look at the description here. So this returns the serial number of the last day of the month before or after specified number of months. In practice, what does that mean? If we press tab to fill out the rest of that function, we need two parameters. One is the start date. So let's reference our first state here, and let's put a comma in here. And how many months beyond that we want to go? Well, we'll go with a zero in here, and that's just going to give us the end of that month. So we're going to get the last day in January. Let's just change that format here so we can see that clearly. So we'll see 31 January there, and all we have to do then is add one day to that, so we can just put a plus one within our formula over here. That will take us to 1 February. And what we can do now is just copy that formula all the way down. So I'm just going to go ahead and copy that, and I'm going to go to the very end of all of my data here. I'm trying to press control. And let's just look at what's happening here. So our first cell is looking at the first January date. And because we've got relative references as it's copying down, each subsequent cell, is looking at the next date. So by the time we get to here, and we move on to the next one, we move to looking at our February date here, and we're getting March in here. So everything's looking good, it's always a good idea to do sense checks and just make sure things are working as you expect. Now, we want to copy our IDs down as well, so we can just select a couple of our cells here and just drag this fill handle down to the very end. And as we get to the end, we can see that clearly I didn't count correctly here, so we are missing a few entries that we need to add in. So let's take these cells over here that have been missed out, and let's put them on the end here, and we should have our six here for the last month that we need and just pull those IDs down here as well. Now, if we go to the top, we'll see that we need to delete some of our actuals in here as well. So if we remember over here, we only had actuals until the end of December. We're just trying to replicate that same scenario. So if we find our date here, and let's just get rid of everything from 1 January of 2021 onwards for our actuals. So if we just select our cell here, and we can use control shift and down to select all subsequent cells and press delete on the keyboard to get rid of that. Now, let's scroll to the top here, and let's do free spines here as well. So we can see the headings anytime we do scroll down. So if we go on view free spines and free spines, make sure you're selecting cell B two, let's say, I always like to save my first column and the first row, so I usually like to move to B two, for example. And if you just click on free spines, that we will see our headings as we scroll down through that data. L et's also make sure our formats are aligned for all of our dates here. So if we go back on to home over here, and let's double click on format painter, and I'm just going to press control and shift, and then I'm going to press down on my keyboard, and that's going to copy all of my formats to the very end. Now, let's just talk about a couple of features of this expenses details tab and why we've set up like we have. So what we can do now, for example, is we could filter on this. We could go into data, and we could go filter. And then we could look at just a particular month that we want to do, for example, or a particular category and just analyze that data however we want. We can also sort the data if we want it. So if we wanted to sort on category, let's say, we could do that. So let's say we want to sort on A to Z here. Now, as soon as we do that, you'll see we see these references and values popping up here. So something's gone wrong here. And that's the fact that because we've left our formulas in here, when we sort, everything's changed, cell references, and it no longer works. So let's undo, and let's take care of that. And the easiest way to do that is to just copy all of our data that we don't expect to move and paste it as values. So I'm going to select all of my dates here, and I'm going to use control shift and the down arrow key to select everything, or you can just hold your mouse and drag all the way down. So once we've selected all of that data, we can just use Control C to copy that, and then scroll to the top again. And the first cell that we've copied, if we just right click there, and we go to paste as values, and now we've got all of our dates pasted as values. And if we were now to filter or sort and we go sort A to Z, we don't see any of those values or references, and everything's working as expected. So copying and then pasting its values is very useful for that sort of example where you want to maybe sort data and your formulas might go wrong. It just avoids any unnecessary issues. And it also saves on the amount of computation within your spreadsheet. So as your spreadsheets get really large, it's always a good idea to do away with any extra formulas so that they don't take up extra overhead when Excel is calculating. I also want to briefly talk about why I've included this ID column here. So this is a very good idea in general for anytime you might have data being sorted. So if we were to sort here, as we did on category from eight to z, for example, we're fine to always undo that. If that's just the previous action we've taken. But if you take enough actions after that that you don't want to undo just to go back to your original order, You can't really do that. Or if you save your spreadsheet like this, you close it down and you open it up again, you won't be able to get your original order back necessarily. And the only way you can really do that is to have an original ID in here because then I can always click on this and I can sort on smallest to largest. And I know that's my original order back. Now, let's switch back over to our monthly budget tab here, and we had linked in one month for our estimated expenses and for our actual expenses. And we would have to repeat that now if we wanted to bring in all these other months in their detail. And immediately, the first thing you should be thinking, is, well, that's going to take some time in order to do that one month at a time, if we have to type in that formula. There isn't really a quick and easy way to do it here just from the nature of this because if we copy this formula down here, if we copy and paste that, the references don't work in the way we want. So this first one is running from e two to e seven on expenses detail, which is these cells here. And that's fine. But then if we look at our next one when we copy that formula down, it runs from E three to E eight, which isn't actually February. It's just moved everything down by one cell. And interestingly, it still shows us 1,500 simply because of the way we've chosen our value. So any six contiguous cells that we pick here are always going to give us 1,500. So it's interesting that you wouldn't even necessarily spot the problem if you just copied that down. And this is where that kind of sens check really comes into play. And you know that it's not picking up the right cells that you want. So, there aren't too many months that you couldn't do this manually. There's 24 months here. But then as this spreadsheet expands and as you add in one month at a time, it just becomes annoying to have to do it that way. So I'm not going to make you link in every single cell right now. Instead, we'll leave this part of the spreadsheet as is for now. And in our next video, we'll see a better way of being able to bring all of that data in that isn't so manually intensive. As a final action for this video, then, if we go ahead and save this file, and now that we've seen how to protect files, if you want to do, you could go into file, and you could go to save as browse, and you could save over your previous file with the same name and location. You can now go into tools, general options, and if you want it, you could put in a password to open and or to modify. And if you want to, then press, and then click on Save when you're done, and you'll now have a password protected file if you want. Let's summarize what we've achieved so far. So in checkpoint one, we created the basics of a budget. So we had months and we had our balances, our estimated income expenses, and our actual income and expenses. And we had a front sheet that would link into that and provide some annualized figures. And then in Checkpoint two, what we've done is we've changed the formatting just to make our titles clearer. Our numbers stand out a little bit more, just to make that presentation go up a notch. We've also changed a few other pieces of formatting, such as adding in thousand separators here and having no decimal places, changing our monthly formats here by right clicking and going into format cells and then having just our months and our ears show. Then we've created a new worksheet hair called expenses detail, and this is designed to show us the breakdown of our estimated on our actual costs so that this can be filled in on a more granular basis. We've also seen how to copy and paste effectively and seen a formula here with EO month that gives us the end date of a month, and we've seen how we can incorporate that to very quickly get dates in the format that we want. We've then seen the importance of copying and pasting values rather than leaving in formulas, such as, for example, when we're sorting in order to not have those formulas go wrong with their cell references. And again, in relation to sorting, we've seen the importance of having an ID column here, which preserves an original order that we can revert to by sorting on this from smallest to largest. Now, in the upcoming videos, you're going to see a lot of the more powerful features of Excel being introduced, such as conditional formatting and logical functions. And so I'm really excited for the next time we return to this spreadsheet because we'll have many more useful features that we can add. So we'll wrap it up there, and as always, thank you for watching, and I'll see you in the next one. 16. Conditional Formulae: In this topic, we are going to look at some logical operators and how they can be used to perform analysis or to supplement data. Start by going to the relevant worksheet. In front of us, we have some sample data again related to sales. We've got sales agents, regions, items, quantities, prices, and finally total sales. So let's examine the if formula first. Now, the I formula allows a condition to be checked and a result return based on whether the condition is true or false. So as an example, let's consider reporting if a sale is over 100, then we want to return a yes in a cell, otherwise, we going to return a no. So let's start by typing in equaled and I and here's the different parameters involved. We'll have a logical test. We'll have a value to be returned if true and a value to be returned if false. Our test is going to be if the sales are greater than 100 and for a comment to move to the next parameter. Now the value that we want to return if it's true is, we just want to print yes. We put that in speech marks, and if it's not, we just want to return no like that. And let's copy that formula down. There we go. Next, let's examine a combination of the sum function and this new if operator. There actually exists a formula or a function called sum if within Excel that allows summation of data based on defined criteria being met. For example, if we wanted to calculate the total sales value per region from this data, and there's also the option to do count rather than su. For example, if you wanted to get the number of sales made per region. Let's look at some if first. So Enter equals some if, and let's just look at how this works. So we have a range, a criteria and an optional sum range, which we will want to use in this case. So the range is where we're looking for the data to be found. We know that north southeast and west are to be found in the region column here, and the criteria here is we're looking for the word north within that column. And then the sum range. So whenever north is found in this column, we want to be able to return sales. So we want that sum range to be the total sales column there. Let's complete that formula. You can see the total sales value per region is returned there, and now we can just copy that formula down using Control D, for example. And let's just enter the full amount here as well. And as a quick check, if we total up the value here, we do see that matches our set of data there. You can also filter in just to confirm some of these numbers. So if we wanted to just make sure that North, in fact, does give the amount that we've returned from the formula. So 2,219, which is exactly what we have there. Perfect. Now, the count if formula works very similarly, so pause this video and see if you can work out what to oe for your parameters yourself in here. Now let's look at that together. We want the count if formula here. And we'll want to look at our region here. Again, our criteria are simply going to be the relevant region name here. Then again, we can copy that formula down, and again, we can sum this up using all equals with a shortcut. 40 total entries, which is exactly what we've got here, 40 different entries, and again, the number of sales per region can be confirmed. For example, if we were just to look at, we should have then There we go, count of 13. Perfect. Finally, let's look at the if error formula. Now, this allows for error trapping. Imagine a scenario where we've received data and we're missing quantities and unit prices for certain of our pieces of data. So let's just wipe out a few. Let's say we wanted to work out unit price, and we're assuming a default of ten because that's what we've been shipped across, but the data just doesn't have it for whatever reason. What you would normally do to work out unit price, of course, is you would just take total sales and divide it by the quantity and you would get your unit price. Copying this across will keep all the values the same. Now obviously, if we try that here, on the missing pieces of data, we're going to get a divide by zero error. How can we handle that? Well, we can wrap this in the if error function here. And the value here is what's calculated assuming everything works. I. There's no error. If there was a value in there, it would just divide H h two by F two. However, if it does find it error, we can tell xcel to, for example, return a string, saying default. And then we can concatenate that with a different calculation. We can say, take the total sales and assume a quantity of ten and return that for the unit price. Let's just expand that by double clicking. There you go. If you were to copy this formula down, you will see it's not going to change where we do have values, but it will enter this default where we don't. Now, of course, we could have filled in the quantity of ten in here and avoided the if error usage here. But the reason for doing it this way is, so it's obvious that we were in fact missing data when first arrived, and there are multiple ways of doing that. You could have entered that value and perhaps colored that in in a different color so that it was clear to other users as well. So there we've covered the if function, the sum function count if, as well as using if error for error trapping purposes. Thank you very much for listening, and I'll see you in the next lecture. 17. Shortcuts: In this video, we're going to cover techniques that will make your navigation and dealing with data in Excel much more effective and efficient. Now, one of the best ways to become much faster in all actions on Excel is to really master using the keyboard as opposed to just using the mouse exclusively. I actually, in my day to day usage, almost exclusively use the keyboard for most actions on Excel. Now, if you're not used to this, and at first, this is going to feel a little natural and will be slower. However, persevere with this and you will see the benefits. The best and easiest way to ingrain the muscle memory that you need will be to start picking out actions that you may find yourself performing regularly with a mouse and instead substitute the use of the keyboard or the keyboard shortcut instead. So to show you just how much faster using the keyboard can be. Imagine having to navigate through all this data and scroll down to the end. Now you can scroll down like that all the way, or you can use your mouse wheel if you have that. But it's a lot quicker to use the control key on your keyboard and press any of the directional arrows. If I press down, I'll jump straight to the end, and then again, control up and then control and write. So you can navigate the entire data set very quickly and easily in this way, and then obviously using it without control to navigate one cell at a time. Let's say we had to add some rows to the end of this data. Now we could scroll all the way down, or we can now just use control and the down arrow key jump to the very end, and let's say we needed ten rows here. So we could select the cells that we want, and then right click and insert to get those rows. Now, if I just undo that, again, a very handy shortcut hair using control Z, to undo and control why will redo. So I'm going to undo. And then let's start again. So if I jump to the end, at this time, I'm holding shift and pressing the space bar, that selects the entire row. And then I'm still holding shift, and I'm just pressing the directional up arrow one at a time to select the number of rows I want. You can let go shift at that point, and then you can just use control shift and plus, and that adds in the rows. Let's undo that, and let's jump to the top again. Now, in terms of shortcuts in general across Excel for the different actions, there's a very handy way to learn them all, and that is by holding down the ult key, and you'll see the ribbon at the top shows all of the different shortcuts on your keyboard to access the various menu options. So for example, in order to get to the home tab, you press H, and then you can see all the individual options in here. Now, it's a good idea to learn some of the common ones that you use, not necessarily every single one, that will probably make you a lot slower, in fact. But there's some handy ones to know in here, such as shortcuts for copying and pasting or for working with data, such as filters. So a really good one here is control shift and L will activate your filter. And then actually using the filter can be done purely with the keyboard as well. So holding down t and pressing the down arroky. We'll open the menu. And then again, using the direction arroks, you can navigate to press forward to open up any sub menus. And as you get into the actual filters, you can just use space rather than having to click, and you can select multiple items as required. So as a quick example, can you work out profit on here and make sure that it fills in all of the required cells and do this without using the mouse? Okay. Let's see how we can do that. So we can jump across the cell, just by in the arrow keys, and then we can just work out profit as sales, and I'm again just using the arrow keys minus the cost of sales. And to copy this all the way down, we can just go one to the left, for example, hold control and down to go to the very end. Go across one. Now hold control and shift and go to the very top. And you can see what we've done here is the control has allowed us to jump to the very top and the shift has selected everything on the way to the top, so we've effectively selected everything from the end where we were all the way up to this cell. And now we can use control and D to copy that formula all the way down. So that's incredibly useful. Control basically allows you to jump all the way to the end of any data. So if there was any gap, it would stop. So if I use control and write, that's the last cell with any data, so it won't go any further than that. And similarly, you can couple it with shift. So control shift down will select all the data as well as jumping to the end. So just as we saw with selecting multiple rows using shift in space and selecting more than one by using shift and down archy, for example. We can do something very similar for columns and that's using control and space. And again, holding shift and going to the right or to the left to select multiple columns as required. Let's look at one more example, and that is copying and pasting values. So let's say we want all of these formulas to be pasted in as values instead. So how can we do this with a keyboard, so we can go control shift and down to select all of this data? And then we can use Control C to copy it. And now you can obviously right click, and then you can pay special for values or you can go into the pay special menu here as well if you wanted other options. And we can access both of those using the keyboard. So if you press Alt to show all of the keyboard shortcuts and then H and then V for paste. And now you can see, you can press V again for values, and that will get rid of the formulas there as you can see. Let's undo that with Control Z for 1 second. And to open up the menu similar, you can go to page VS, and you get the menu there. Now, obviously, I'm going through that a little slower just to have the menu that you show, but you can do that so quick that the menu won't even really show up. So there you go, that's star base it as values. In summary, then, we've seen how to find the full list of shortcuts available on Excel directly just by using the Al key and navigating to the menus. We've also seen how to effectively move around Excel a lot quicker using control and shift to jump to the ends of data, and to select data. You've also seen how to toggle filters, for example, and how to copy and paste in a quicker method as well. Now, it will take time for these to be embedded into your day to day work, but it's worth doing so over time and help you with this attach to this lecture. You'll find a list of shortcuts. It's worth having a printout to hand so that you can refer to it and slowly build in those actions to replace your mouse clicks. Thank you for listening to this lecture, and I'll see you in the next one. 18. Top 10 Excel Shortcuts: Hi, everyone. In this video, I'm going to show you my top ten shortcuts for Excel with hotkeys for both Windows and Mac. Now, as well as speeding up your most commonly used tasks within Excel and making you much more efficient. You'll see in at least one case, hotkeys can actually add functionality that the standard user interface doesn't provide. So let's get straight to it. The first shortcut we'll look at is the F two key on a windows machine or control combined with on a MAC. This allows us to edit the cell contents and jump to the end of a cell with our cursor. So simply selecting a cell, pressing F two will jump you right to the end of that cells contents and allow you to edit directly. It's also really useful for looking at the formula references within any cell. So pressing F two here, for example, we show me and highlight the cells that are involved in a calculation, C two times d two here. Now, you can double click into cells to achieve the same effect. But depending on where you click when you're doing this, if microser happens to land just in front of that asterisk, for example, and I click on another cell and try to double click, it's instead of going to pull in a reference. So it's a lot more accurate as well as being quicker to simply click on a cell, press F two or control and U, and then move to the next cell just like that. Next, we have the control grave key. Now, this on my keyboard is just to the left of one and above the tab key. It might be in a slightly different place on your keyboard. But pressing Control grave will toggle the show formulas. And this allows you to see at a quick glance where your values are and where your formulas are. And you can now also click just once on any cell with a formula in order to see those cell references right there. To toggle this off and return to the standard view, just press Control and grave again. Number three on the list is using your keyboard arrow keys to move around and combining that with shift to select and control to jump. So rather than using your mouse, you can simply use the arrow keys to very quickly move from cell to cell. And if you want to move even quicker, you can combine that with the control key to jump to the end of the data region. If I hold control here and I press the down arrow key, going to go to the last cell with data in, and then I've got blank cells underneath. And from this cell, if I was to use control and down again, I've got no more data, so it will jump me right to the end of the spreadsheet. And again, to go up, I can use control in the arochy, and it will go to the first cell with data, and then pressing control and up again, will go to the end once again. I can use this to the right. So if I use control and to the right, starting at LA one, go to the end of my data region here, and then if I use it once more, I'm only going to skip one cell in this case and go to the next cell with data, and again, control and write will take me to the end of that data. So you can very quickly navigate around large spreadsheets using control and the arrow keys. Now, you can use the keyboard okeys and shift to select cells as you go, and you can go in both dimensions here as well. And you can actually combine shift and control to both move through large swathes of data as well as select on the way. So for example, if I start on A one and I hold control and I hold shift, and I press down, I'm going to move to the end of my data region and select everything on the way. And I can do the same thing by holding control shift and pressing the right rochey, and I've got all of my data region there selected. Number four on the list is using the control Zed and Control Y keys on windows or command D and command Y on a MAC. And we can use this respectively to undo and redo actions. So you can, of course, use the buttons up here to undo and redo, but it's a lot quicker to use these shortcuts. And it's especially handy four when you're making changes, and you want to go back and forth to see the effect of those changes. For example, on a model. So if I was to change the unit price here, for example, to do, and I just want to very quickly go back and forth. I can press Control Z to see the effects here, control Y, and I can just go back and forth and very quickly analyze my changes. Number five is using control C to copy and control V to paste or an AMAC command C to copy and command V to paste. And if you're using the standard copy and paste where you're selecting right clicking copying, then going to where you want and then right clicking and pasting, that's very slow. So the very first thing you should be doing is learning these actions here to do a quick control C control V. And that's your standard copy and paste. If you wanted to use your other paste options, you might still use the mouse. There are other shortcuts for these as well, but the handiest one to know is the standard control C and control V. Now, to add to copying and pasting, there's another couple of really useful shortcuts to know, and that's control D and Control R, and it's best shown with an example. So let's say we had to work out a tax amount in here, and that was just gross minus net. And we wanted to copy this down all the way. Now, one way you could do this is using control C and then selecting all of your cells, using Control V. But another method we can use here is we can just jump to the end of our data, and let's use one of our newly learned shortcuts here, control and down to go to the end, and then I'm going to press right once. Now I know I'm covering all my data here. So I'm going to use control and shift and up to go to the very top, and it's going to find the first cell with that new formula. And if I just use control and D, it's going to copy whatever is in the top cell within my selection and copy it down to the remainder of my selection. So I've copied my entire formula down with just one click really. So if you're looking to add a few formulas in, you can just very quickly select your cells, control D and save yourself some time right there. Control and R works in the same way. If I had a selection like this and I was to use Control in R, it would take what is in the left most cell, whether that's a value or a formula and copy it to the right. In this case, it's just a value. I'm going to get that same value pasted all the way across. Number six on the list is control in the open square bracket key, which is the shortcut for trace precedence. And interestingly, this has some added functionality compared to just using the user interface as well. Let's take a look at the user interface first. So if we were to click on a cell and then use trace precedents, it would show us all of the cells that are involved within that calculation. So here we're doing C two times D two, and if we were to double click on those arrows, we would jump to those cells, or if you're starting on that cell and you double click, it will jump you to the cell, which is using that within its calculation. The arrowhead, in this case, makes clear that these cells are feeding into this cells formula. If we now apply trace precedence to a cell that references an external worksheet, we'll see we get a dash line here indicating the external reference. If we double click on this, we can see that we can see the name of the workbook, we can see the worksheet within and the cell reference. If we press okay to try and jump to it, though, that won't work. But interestingly, using control and the open square bracket will open that spreadsheet up. We can use this shortcut to select all of the cells involved in a calculation. So for example, over here, we have a V lookup, using control and the open square bracket, we'll jump to and select all of the cells involved. So here, we've got our reference that we're looking up and here's the table array all selected for us. Next, let's look at how we can effectively apply and use filters. On a windows machine to apply quick filters, you can use control shift and L or on a MAC command shift and F, and that will apply your filter, Doggle it off, you can use the same key combination. Now, once you've applied your filter to effectively navigate through it, you can use the arrow keys. And if you use t and the down arrow key, that will open up your filter there on the column that you're on, and you can use the down arrow keys to navigate through using four to open any sub t to close them. You move down to your actual items, you can start using the space bar here to very quickly toggle items on and off. If you've got a large list that you need to go through, you can use that, and once you're done, you can press enter for okay, and to remove all your filters, you can just press that same key combination of control shift L or command Shift F. At number eight, we have how to effectively select columns and rows. Now, this depends on where your active cell is, either by clicking on the cell that you want or by moving around with the arrow keys. So let's say I'm on cell B two. I can use control in the space bar here to select the entire column, or I can use shift and space par to select the entire row two there. Number nine leads nicely on from selected columns and rows to how we can add and remove selected rows and columns. So on a windows machine, this will be Control Shift and plus, to add a row or on a MAC command shift and plus. And then to remove rows on a windows machine, it'll be control and minus or command and minus on a MAC. As an example, then if we wanted to add two columns between A and B, you would first select the number of columns you want like this, using the left click, then you would right click and press insert, and that will insert your two columns there. And then to delete these again, you could just right click once you've got this column selected and press delete. Using the keyboard, we can just use control and space bar and shift and right to select the number of columns that we want, and then just use control shift and plus to add no columns and control and minus to get rid of them. And this works just the same for rows. So shift in space, and let's say I wanted to add three rows above row five. I could just press shift and down a couple of times and then use control shift and plus to add those rows and control minus to remove them again. At number ten, we have the F four key on windows and command and T on the MC. This allows us to switch between relative and absolute cell references very quickly and easily. As an example, we've got gross amount here being calculated at a flat rate of 20%. But let's say that was variable, and we wanted that to reference a cell, so we could have something typed in in here, and we can reference that cell rather than typing this in directly. And I wanted that copy down all the way. So I'm just going to take a few cells here. I'm going to use control and D to copy that down. Now obviously, that's not going to work because our cell references are relative and they're moving as we're copying this formula down. What we want to do here then is fix our cell reference so that it's pinned on cell G two always. We can do that by putting $1 sign in front of the G and in front of the two here. If we copy that formula down now, we're going to see that that works because each cell is pinned to cell G two. Now, we can do this very quickly rather than typing in those dollar signs by simply clicking on the cell here that we're interested in and pressing the F four key or command T on a MAC, and that will wrap our cells here, so we're fixing the G and the second row. Pressing it again will fix just our row. Once more just our column, and pressing it again will return us to a full relative reference. That covers my top ten shortcuts for Excel. Hopefully, you found that useful and there's a few new ones you can incorporate your work. Now, as always, top tens are subjective, and I'm keen to hear about your favorite Excel shortcuts. So be sure to leave something in the comments below. Thank you. 19. Range Names & Data Validation: In this video, we're going to talk about range names, which can add some user friendliness to your spreadsheets and ease of use features, and we're also going to look at data validation, which can be used to enforce certain controls within your workbooks. Start by going to the relevant worksheet as usual, and we've got some sample data, and let's just look at what we've got in front of us. So we've got some employee IDs, some names, the number of hours worked in the month, and then we've got some blank cells for wages. On the side, we've got months with respective hourly rates that differ. For example, there are seasonality features, which means that the hourly rates are higher in summer and towards the end of the year, for example. Then we've got a couple of cells up here where we've picked out a particular month and a particular hourly rate. So to start with, let's say we wanted to work out the total wages for the month for any employee. So we would do that by saying equals the hours worked, and we will multiply that by the hourly rate. Now, obviously, if we copy this down as we've seen before, we're going to have a problem if we don't fix the reference. So if we fix that, we can certainly get this working. Now this is where range names can come in actually and make this a little bit easier. So what we can actually do is name this cell here and use it within our formulas. For that, go to formulas and go to define name. Now you'll see here Excel has actually picked up the cell to the left where I've named it as hourly rate, so we can leave that name alone, but you can change it as you see fit. And we're going to keep the scope as the workbook. And we've already referred to the particular cell because we've highlighted it. You can see it there, so we're just going to press ok. And now you can see up here, we've actually got hourly rate defined. So if I was to go anywhere else on the spreadsheet, and I could just pull this down and go to hourly rate, it will jump me to that cell. So it's a really handy way of naming certain cells that are key to your workbooks. Now we can amend all of the formulas in here, so we can actually replace this and we can type in the hourly rate. In fact, as we start typing and you can see Excel recognize that that's available, and we can put that in press enter and we could copy this all the way down as usual. There's a quick way of doing this actually. So if we just undo a couple of steps here, so we're back to where we started. What we can actually do is go here within formulas. And we can click on Apply names. And what Excel will do is basically look at any names we've defined and replace any references with those names. So instead of having B four in each of these, we can just do apply names. That's the one that we want to apply and press. And you can see every single one of these cells where we referenced B four has been updated with the new name range. So that's a very handy feature for when you have lots of different input cells and ranges, and you want names that have meaning to them so that other users or yourself looking at these spreadsheets after a few weeks or months, you can look at it a glance and understand exactly what that cell was supposed to represent because it's got a meaningful name. Now, let's talk about data validation next. The way this worksheet is set up is it relies on the month being typed in are in order to work out the correct hourly rate from a look up to this table. So if we were to change this to something that wasn't valid, so a bunch of numbers, for example, we're obviously going to get an NA Hare and our formula will breakdown. So how can we limit the user's choices here to the allowable months? This is where we can use data validation. Go to the data tab on the top and we're going to use data validation in here. So we're going to click on that and then click data validation. Now we've got different choices in here. In this case, what we're going to say is we want to allow a particular list that we've already defined, and we're going to choose the source of the cells that are allowable. We want these 12 months here, p k. Now you can see we have a drop down where you just can actually select the months. As they do so, you can see the hourly rate actually updates with that. If anyone was to try and type in anything else and pre center, it wouldn't allow that. Now, we've got some more options that we can look at within data validation to make this even more user friendly. We can actually have an input message that shows up. For example, if we said, choose a month for our title and if our message was, choose from the 12 months, and we also have the option to set up error alert. We see a generic error alert that pops up, but we can actually amend this, so we can say incorrect month, please choose a valid month. And you can have different icons that show up, so we'll leave that on the stop icon there, and we'll leave everything else alone and press okay. Now you can see when a user hovers over that, they'll get the input message that we've specified and they can still do everything and this time if they try something that they shouldn't, it gives them our customized message. We can also use range names in conjunction with our data validation. Just to confirm right now we're using a range of cells for our source here, and we can instead name these cells. So as before, formulas, and then we're going to define a name. It's for this month names. Now, it's important that you can't have spaces here, but you can't separate out different words with an underscore, for example, and we've already got the range picked H seven to H 18. Wes okay on that. Once again, we can now jump across to that entire range here. Then moving across to our data validation. Let's go back to our month here. Make sure that cell selected, then we're going to go data validation. Instead of the source being range of cells, we can actually choose our range name to do this, type in equals and then type in the name of the range. We had a month underscore names and press, and nothing changes in terms of the drop down menu here. But within the data validation, you can see now we've actually used the name range here rather than a range of cells. Let's look at another option that you have with data validation. So we can also as we've limited with the list, we can also limit on numerical values. So for example, let's say we know that in any given month, these hours have to be 120-180, and we don't want any other options. So we can once again select the cells that we want to apply the validation to and a data validation. And this time for our validation criteria, we don't want to allow any value nor a list. We actually want to allow whole numbers, let's say, and we want to have a minimum of 120 and a maximum of 180 hours work. That's okay on that. Obviously, that's not going to change because these are all already within that limit. But if you tried to enter something that was above, 181 wouldn't work or 119, likewise, or anything in between that range will be just fine. In summary, then, we've seen range aims, which are a great way to logically label cells or ranges of cells, and it's also something that allows us to jump around to those ranges of cells very easily and efficiently. We've also seen data validation, which is another great tool to limit the options an end user can have within your spreadsheet or to allow functionality such as drop down boxes. This ensures the integrity of data is as expected. As always, thank you very much for listening, and I'll see you in the next video. 20. Text to Columns & Remove Duplicates: In this video, we're going to be looking at the text to columns feature within Excel, talking about CSV files and looking at remove duplicates. To start with then, just beneath the video, you'll find a couple of files. Start by opening the dot doc x extension file, and you should be able to open that up in Microsoft Word or a similar word processor. We're just going to select the entire set of data, and I'm going to copy this. Now I'm going to switch over to a new workbook within Excel, and I'm just going to paste my data in here. Now, what we have here is a set of headers and data, and we can see it's all separated out by commas, but it doesn't pull through nicely into Excel. Now, this is where the text to columns feature comes in very useful. So what we'll start by doing is simply selecting the first column and that's where all of our data is. So I just expand that so you can see easily. We're just going to select that, and we're going to go to the data tab in the ribbon, and we're going to go to the text to columns feature here. And this will allow us to split data within a single column into individual columns based on headers. Now, there's a couple of different options in here. You can look at fixed width, for example, if you had a certain amount of space between each of your datasets, and it's rare that you'll have this. So the more common feature that you'll utilize is the Dmited where you've got characters such as commas or defined spaces such as tabs separating your data. So we're going to click next on that. And it's already chosen comma for us here. But you can see you have different characters, and you can specify specific characters as well if your data happens to be separated by different types of characters. So we see a very handy preview here when we have comma check. And see how our data will look. And if we click next once more, the next screen talks about the format for each of our data columns. So for example, we can change the sales date here from general to date. And let's just leave everything else alone and we can click Finish. And you can see Excel has very nicely split that data out based on those headers and the wizard we just follow through into separate columns. Okay, great. So we've got our data separated out into separate columns ready for us to work with as usual. Now, this is important to know because you might have data provided to you that is in fact separated by characters such as a semicolon or a tab, and you want to be able to get it into Excel in order to analyze it. Now, while you have these other characters, the most common one actually is comma separated files, so common in fact that there's a separate file extension for it. And we can see this, in fact, within the lecture material here. So attached to dot CSV file here, and if you click on this and open this, hopefully you'll have Excel show as a default. Otherwise, you can find and choose that manually. And if we open that file in Excel, you'll see it actually comes out separated into those separate columns because Excel recognizes that the commas are separating our data. Now, when you come to saving a CSV file, note that you'll actually have a separate option here as a dot CSV extension, and you can of course, change it to an Excel workbook as normal. But what's the particularities of saving as a CSV file? Well, comma separated files are just pieces of data, so you can't have any formatting, for example. So if you were to put in anything like this, save this and reopen that CSV file, that formatting wouldn't be there and equivalently with any formulas, those wouldn't work either. So it's important if you do open a CSV file and you want to add formulas or formatting to it, then you save it as a normal LS X file. Okay. Now the next thing we're going to look at is the ability to remove duplicates within Excel. So over here, we've got sales agent names that occur multiple times and likewise, for items, we've got items repeating in here. How could we pick out the unique occurrences within these columns? So to do this, let's just start by taking a copy of our data here for items and putting it here into Column k. And then I'm just going to move to the data tab on the ribbon, and we've got a removed duplicate button over here. So we're just going to click on this, and we're going to say my data has headers in this case, so that we'll just exclude the word item. And then what Excel is going to do is going to work down this list and keep the first instance of each item, but then remove all the rest that are duplicates. So let's just press okay on that. You can see it tells you how many duplicate values were found and how many unique values are left now that Excel is done removing. So we can see all of our unique options right there. Now, that's a very straightforward way to find your unique values within a column, but we can also use this to remove entire rows within a dataset. So for example, let's say we only wanted to keep the first entry of each sales agent, and we didn't want to see any of the remainder for the entire dataset. So we can do this by selecting our dataset like this, clicking on remove duplicates. Once again, make sure that my data has headers is checked. We're going to unselect all, and we're going to base our removal of duplicates on sales agent, which is Column C here. And would press okay on this, and you'll see it removes all of the data along all of the different cells and just leaves the five unique values. And once again, it's just kept the first instance of each one. So a little easier way of seeing that maybe is if we just highlight our cells here that have the names. So I'm just going to make sure that we have one instance of each name. And we should only have the names in yellow that are left. Once again, remove duplicates and just on sales agent and press. And you can see those are the five that are left, which are the unique names. To summarize then, we've seen how to deal with converting text to columns. We've seen how to open up CSV files, which are automatically separated out into different columns by Excel. And finally, we've also seen the removed duplicates functionality within Excel. So as always, thank you for listening, and I'll see you in the next one. 21. Data Visualisation: Welcome to this lecture on data visualization. So far, we've really only seen data in tables, and whilst there's plenty of options there for effective presentation and formatting, there's really nothing quite as insightful as a good chart to really provide a picture of the data. Let's dive straight into it by jumping to the correct worksheet, and we've got some data in front of us, but months with volumes, cost, sales, profits, and profit per unit. Let's start with a simple example. For example, we want to view the volumes per month on a chart. So begin by highlighting the data there, including the headers. Then make sure you're on the Insert tab on the quick access ribbon at the top. And let's go to recommended charts. Now, we've got a few different options here for different styles of charts. Let's just leave this on clustered column and press. And there we have our chart. To move it up there. Now, let's see what we've got here. We've got our chart title here, where it's been taken from the second column. We've got our x axis, which is taking the months, and we've got our y axis, which is taking the volume over here. Now we're missing a couple of elements here such as access label saying that this is months, and this is volume. Let's add those. Now, there's a couple of ways of doing this. The Es is probably to just click on your chart, hover over here on the chart elements, and you can toggle these on. For example, access titles. Let's click on that arrow there, and let's add a primary horizontal and a primary vertical. There's more options as well, but we'll keep it at that for now. For example, we can click here and change this to months, and we can change our y access title to volume. Let's say that same features there. Now, let's say we wanted to have some more data visible on this chart. For example, cost and sales. How could we do that? So let's right click on the chart and click on select data. Now, you can see here we've actually got our volume for our series on our Y axis and our access labels on horizontal axis. Let's just look at these firs. If we edit volume, we can see that the series name is looking at the worksheet name and it's looking at that cell, which is of course a volume here on C two. And then the series values are running again on this worksheet from C 13 down to C 14. And similarly on the horizontal axis. This time, you can see we're running from B three down to B 14, and there's no title there as you have noticed. We can very easily add in additional elements. For example, let's click Add here, and let's choos our series name. Let's say we wanted cost on this chart next. I'm just going to click there on cost. And then for series value, I'm just going to click here and I'm going to delete that, and then I'm going to highlight the data that I want. That's left click and drag. Click that again and press. That's cost. Let's also add in sales as well. So I'll have sales, and that's my range there. Press. Now the first thing you'll see is that actually looks quite messy immediately. So what can we do to maybe improve the visuals on this? We can as one example, experiment with the type of chart. So let's right click here and go to JR type. And this time we're not in the recommended charts, but we're in the all charts. Let's go down to combo we can combine different types of charts in here. Let's say we wanted to keep volume as a column. But let's say we wanted to look at cost and sales as lines. It's going to find line on here, and I'll just take the standard line graph there. Now, that's good and it gives you a good preview there. Let's say that we also add in a secondary axis. It would be useful because of course, this is not based on leaches, This is based on currency. So let's add in a secondary axis, and it's important to take both here so that those axis ranges are calculated correctly by Excel, and we would be able to change those manually if we want it, but doing so automatically is very useful like this. Let's press. Let's just expand this a bit as well. There we go. We've added in cost and sales overlaid on volume on the same chart. Now let's add in profit and profit per unit as well. See if you can do that yourself, and I'm just going to carry on add that, so I'm going to select data add profit and the range. The same for profit per unit. There we go. Now, the first thing you'll see is we can't really see profit per unit because of course, that's so much smaller in comparison to our other values here for sales, cost and profit. So one of the things that you can do on here is, you can of course change this range here, but a more useful thing might be to actually temporarily not look at cost sales and profit. So we can toggle these on and off without having to add them back in each time, so we can just uncheck here and leave just profit per unit, and suddenly that's a visually much more understandable graph. Now you can see intelligently scaling the axes here to make the graph fit nicely, but you can play around with this yourself. So if you left click on this axis here, for example, and then right click and format axis, you'll have many options within here to change formatting and values. So for example, we can see that right now it's working on an automatic basis, but we can actually change these bounds. So let's say circle into 3.5, we wanted this to run to seven and just press Enter. And you can see that scales automatically. If you want to return, you can just press the reset button here. Now Excel offers many options for different chart types. For example, if we only wanted to look at volume and we wanted to look at it as a Pi chart, change the chart type to Pi. Now, the first thing you'll notice here is we don't have any indicators of volume. Again, we can just go here to the right and add in those data labels, and you can change where those are placed inside, outside, data outs, et cetera, we can also add a legend to see the months along the side. Now, let's undo a couple of steps here to get back to our graph with the line for our profit per unit. One of the things that I like to do with charts is actually have them in separate worksheets that looks a lot neater, and this can be done quite easily. If you've got your chart selected by left clicking, you can then right click and you can click on Move Chart, and you can move it to a new worksheet, for example. Let's just say this is our volume and profit chart. Press. And you'll see you'll get a new chart with all the same functionality that you had before, but now it's a lot neater looking. Another useful feature to be aware of with charts is how we can view just certain types of data. So for example, we can of course, select data here, and we can change the ranges that we're looking at. So if we only wanted to look at the first three months, let's say, we could actually manually change that range. But there's actually potentially quicker ways of doing this. And one of these is, if we go to our data, we can simply highlight these roles here, that's left clicking and dragging, right click, and then click on Hide. And now we're only seeing those three months, and if we go back to our chart, you'll see that adjust in line with our current viewable data. So now you've seen just how easy and useful it is to be able to create charts in Excel and how much value they can add in really allowing insight to your data on a much more visual level. That brings us to the end of this video. As always, thank you for listening, and I'll see you in the next lecture. 22. Build a Budget Checkpoint 3: Hi. Welcome back to the next video in our Builder Budget checkpoint series. We're going to pick up from where we left off and use the new things that you've learned to incorporate some new features into this spreadsheet. Now, hopefully, you've saved your own version of this spreadsheet at the end of the last checkpoint. But if not, don't worry attached to this lecture, you'll find a spreadsheet of where we got to, so you can download that and open that up so we can get started. Let's start with a quick recap of the spreadsheet that we've built so far. So starting on our monthly budget tab over here, we've got a series of months running along our first column over here. Then we've got a starting balance over here. Next, we've got a balanced movement since the start. So on the first month, there's obviously no movement. We've got estimated incomes and expenses and a profit or loss, which is simply a formula to calculate the difference between our income and our expenses, and then we've got the same thing repeated for actual income expenses and profit or loss. Then we've got formula to calculate our opening balance, simply as the previous months starting balance and that month's ending actual profit or loss, and that's copied all the way down. Now, our actual income and expenses only cover one year's worth of data, and that's because this is designed to be a live spreadsheet or live budget. So as anyone's going through the months, they would fill in the actual income and expenses figures in here, and you would get the actual profit or loss, and the opening balance would move correspondingly. Now, we started by building this stab up first. But in fact, this is going to sit in the middle in terms of our data flow. So what we actually have is this monthly budget will feed into our budget overview sheet over here. And this is designed to give you annual figures. So right now we've got some simple sum formulas here that are just adding up, for example, here from the monthly budget F two to F 13. And we can quickly jump to those cells and just see which ones are included in that sum by using control and the open curly brace key or the open square bracket key. And that will jump to and show you the cells involved in that calculation. Now, let's just switch back to our budget overview tab here, and we can see we've done something similar here for actual profit or loss where we're looking up a different column here. So we're looking at column I, for example, and so on for each of our projected incomes, projected expenses, actual income, and actual expenses. And then we've included a few sums here just to look at the differences. And finally, over on the expenses detail tab that we created last time. We've now got a more granular split of our expenses. So again, we've got our 12 months here, but as an example, we've taken let's say six different types of expenses for each month. We've split them out into these categories and descriptions, so you can have a category with multiple descriptions. And then we've got our estimated costs, our actual costs, and the differences between those. Now, the flow of data that I described a couple of minutes ago will be to go from our most granular data set over here, where we've got multiple costs per month, feeding into our monthly budget, where we've got one row in effect for each month. And then finally over into our annualized budget overview figures over here. So we go from detail to monthly to overview. Now, at the end of our previous video, we were looking at feeding in our expenses detail data here into our expenses. So for example, for each month, we could use a manual sum, and we could bring in expenses detail E two to E seven, for example, here for January 2020. And again, if I use that shortcut of control in the open square bracket, we can see those are the cells we would bring in, so that would be all of our January costs just being summed in directly here. The problem that we have with this is we can't just copy and paste that formula down because it's only going to move that reference down by one cell here from E two to E seven to move to E three to E eight, which is not what we want because we want to jump directly to run from E seven to E 12, for example, for our next step. And so we would have to type in the formula each time in effect for all of our estimated expenses and then repeat something very similar for all of our actual expenses. We now have a good way around this, though. We can bring in our logical formulas that we've seen. So what we can use here is our su ifs function here. So we'll use equal some ifs, and the first parameter that we need here is the range that we're going to sum over. So if we move on to expenses detail, we want to sum our estimated costs. Now, we could select all of our data and move all the way down to the end of this. But instead, let's just select the entire column here that way we're covered for any additional data that might feed into this column later as we add in more months and years. And now that we've put our sum range over here, we can put a common to move to our next parameter. And the next item that we have is our criteria range one. So the column in which we're going to be checking for a fulfillment of a condition. So what we want to check here is our dates. So we're going to select all of column B once again. And then finally, what do we want to compare that to? And that's our actual criteria that applies to criteria range one. And for that, we're going to go back to our monthly budget, and we're going to just select our cell over here, A two. And let's just analyze what this is doing. So let's close the bracket, enter and let's just make sure that that works. And it does. Now, let's make sure we understand what this is doing. So we're asking Excel to sum up everything in column E on the expenses detail tab. So if we just switch over to that, that will be our estimated cost, but clearly it's not summing up everything. So what's our if involvement with this? So the second parameter is a column in which we're checking for a condition, and we're saying expenses detail B, which in this case, is our months. And then what do we want that to be? So we're saying only some any values within column E, if column B within expenses detail is equal to this cell over here, January 20. So, in effect, Excel is working its way down, and if it's finding a match, so Jan 20 here, it's taking 100. Storing that, it's move to the next line, finding the next figure there, and it's continuing in this way and bringing in all of our January's and their corresponding figures. Then we move to the next line. It's not finding a match. Therefore, our total sum stays as it is, and you can imagine Excel in the background is continuing to do those matches, but there's no more matches. So we have our 1,500 cost here from just our January, and that is what we're seeing over here. Now we can just copy this formula all the way down, so we can just select all of our data here, and we can use the control D shortcut here, which will copy those formulas down all the way. And we can see that that's now fed into our estimated profit and loss column changing here as well, because previously we had typed in flat 2000 figures, but our background data from expenses detail is just 1,500 all the way throughout. And if you want to just make sure that you understand how the sum of is working, by all means, change a few of these values in here for a particular month. And you'll see those being pulled in for the corresponding month in the estimated expenses. Now, let's do something similar for actual expenses, and why not pause the video and see if you can get ahead of me and work this one out yourself. Hopefully you manage that. We can do this by using equal sum if again. And the range that we're going to sum up now is, well, we want actual expenses, so we've got an actual cost column here, so we'll select that entire column there. And range and our criteria are going to be the same. Again, we only want to sum anything in column F, where column B is going to be equal to the month on our row over here. And we're going to close that off. And again, because these are relative references here as we copy this formula all the way down, we're just going to see those A three A four changing, so we're bringing in the different months once again. Okay, great. So now we have our estimated expenses and our actual expenses actually linked to our expenses detail here. So you could start changing figures in here, and those will feed through perfectly fine over here, just like we wanted. Next, let's do something about our opening balance here. So right now, our opening balance is a very simple formula, which simply takes our previous months opening balance and brings in the actual profit or loss at the end of that previous month. So this only works when we've got actual all the way down. Otherwise, our balance is staying the same. So let's change this so that we'll bring in our estimated profit or loss if we've got nothing for our actuals, but we'll bring our actuals if those are available. Let's start by looking at the first cell where we don't have an actual profit or loss and amending that formula. So right now our Feb 21 and onwards are just returning the same opening balance because there's no actuals. So let's remove this formula in here, and we're going to build in a logic statement. So let's start with an equals F, and our first parameter here is our logic test. And let's say we're going to rely on this cell over here in column I. So if we look at the previous months actual profit or loss, and we click on that cell I 14, We're going to check if that cell is blank, and we can do that by saying, if I 14 equals, and then we can put two quotation marks here, which means the cell is blank. Now, if that's true, and we put a coma, we're now going to say, if that's true, what action do we want to happen? Well, we want to take the opening balance of the previous month and add in the estimated profit or loss instead. And then if we put another coma, if that's false, which means let's say we actually have a 500 or any value in there, then we want to take our previous months opening balance and add in the actual profit or loss. So let's run through that because that's changed the value of our cell here. So in this case, we've checked whether I 14 is blank, which it is, and so our formula here for the value of true has kicked in, and that's doing our B 14 opening balance plus F 14, which is the estimated profit or loss. And if we were to have any value in here. So if I was just to hard code even 100 in here, we'll see that that now pulls in the actual figure. So we have something built in here now that will correspondingly check, and if we've got anything for actuals, use that, otherwise, revert to using our estimated profit or loss. Now, you always want to keep your formula consistent. So we want to make sure this formula works throughout. So if we copy this, and we're going to paste it on every cell except for our opening balance, which, of course, is a hard coded value. But if you select all of these cells and just use control V after we've copied that cell to paste those values in, and we should see nothing changes in our previous values here. If I just use controls to undo and then control y to redo, just go back and forth with these two actions very quickly. You can see everything's changing here because we're now starting to pull in all of our estimated expenses, whereas previously, we were just pulling in nothing. Okay, great. So we've added in some logical formulas to our spreadsheet here to just bring in some additional functionality. Now, what would be even better here is if we had some sort of more striking visual indicator of where we're using our estimated profit or loss as opposed to actuals, and we can get that using conditional formatting. L et's look at cell B 15, which is the first cell within this data where we start to use our estimated proferer loss coming in rather than actuals. And what we'll do here is go to conditional formatting and we'll go into new rule, and what we're going to use here is a formula to determine which cells to format. So what we're going to do here is check if our cell here is blank, just as we did within our formula over here. I'm going to use that to format this cell over here. So if we just type in equals, and then we chooe our cell over here as I 14, and let's just put that equal blank again by using two sets of speech marks. Now, by default, Excel is going to add in some absolute cell wrappers over here. So let's just get rid of those. So we just want relative references to kick in, and let's change our format over here by clicking on format, and let's go to fill, and then let's just put a color over here. Press and press again. And we can see that's changed color now. So let's just use the format painter here, and I'm just going to double click on that. And let's drag this across all of our data over here and let's see what that gives us. So I've done that, and you can see, it's only our cells where we're bringing in the estimated profit loss that we're getting this result here. But let's analyze what's happening when we're copying this formula across. So let me start by just undoing that 1 second, so we've only cot it on our original cell, and let's go back into conditional formatting and go into manage rules to find our rule. And we can see we're looking at I 14 over here being zero or being blank, and that's applying to cell B 15 here. So that's fine. But if we copy this cell just once to, let's say, the cell below, L et's take a look at what's happening with that formula. If we look at conditional formatting now, manage rules, I'm looking at cell B 16, we can see it's automatically moved that condition down. So previously, when we were looking at B 15 having the rule applied, and we had I 14 being blank, we've now got I 15 being blank for B 16. So when we copy formulas down here, we've got conditional formatting using a formula. Excel will intelligently move those references across as it needs to, which is why we're able to copy that entire format across all of our cells. And if we look at any of our cells, we'll have the correct rule in place. So again, on our very first one here, which applies now you can see, it's applying to the entire range where we've copied that, so it's running from B two to B 25, and it's just showing you the first the first formula where that applies. So for cell B two checks that I one is blank. And then it will move that formula down because there's no absolute cell references on that. It's going to move down as we go to B three to check I two, B four, I three, and so on and so forth. So that's an important thing to remember when you're looking at a rule and to understand what it's actually doing. So if I click on any cell now, so let's say B eight, and again, I go into conditional formatting and manage rules, I'm not going to see that it's checking for I seven and it is applying to B eight. It's just giving me the full contiguous range that I've copied that format across, and it's saying I'm applying this rule from B two to B 25, and on that very first cell within that range, that's the formula. And then you yourself can just extrapolate that out in your mind and say, well, okay, if I'm applying I one being blank for cell B two, that means that when I'm on B three, this is going to move down to I two, B four to I three, and so on, which is different when we copied that formula down to just one cell, and we actually saw the specific formula. So Excel is just amalgamating that formula just so we have one rule rather than one individual rule for every single cell within our range. So you wouldn't want to click on all of this, go here, manage rules and see a whole host of rules within here. So we can see, in fact, the I 14 because I copied that separately earlier is actually a separate rule, but we can delete that. So if I get rid of that I 14, I apply that rule, press okay, we've still got the same yellow formatting across the entire thing with no gaps. So we've looked at a very powerful option there, actually. Conditional formatting and logical formulas by themselves are actually capable of doing a lot of different things, but you can combine them as you've seen here by using specific formulas within conditional formatting in order to achieve certain results. Now, let's switch gear slightly, and let's add in a new worksheet over here on the end. Let's just move that across. And let's name this supplementary data. And what we're going to use this for is within our expenses detail, we're going to bring in some data validation. So right now within our categories and descriptions, we could type in anything, and there's nothing to stop that from typing in silly categories or descriptions, and maybe we want to just protect that or enforce some validation here. Let's start by copying our two descriptors over here, and let's just paste them over here, and let's just enter heading over here as well of data validation. Now, we want to bring in our categories and our descriptions, and we could either type them in or we could copy and paste them. There's so few an example here that any method really works. But if you had a lot more, you would want to look at some different methods here. So if you had a decent amount, you could just copy these by using the control and click method. So I could just click through those. For example, I've only got three categories here, and I could just copy those and paste those indirectly. I had a few more, what we could do is we could take the entire column here, let's say, copy that and just temporarily, let's place that over here. And then we can use our remove duplicates. So we go to data. Let's go to remove duplicates and just press okay on that, and we're just left with our three over here, and we can just copy or we can cut it fat using control and x. Then we can just paste that over there. And let's do the same thing for our descriptions. So again, I know I've got all of my descriptions just within one month. I'm only going to copy that, but you could copy the entire column, and I'm just going to paste those over there. It quick remove duplicates. Now because I've got a column next to it, it's going to ask me if I want to expand the selection to cover that column there. I don't need to do that. I just want to look for duplicates in this column here. I'm going to continue with the current selection, remove duplicates, and just leave description checked and press. Of course, there are no duplicates to be removed within that because I've got one unique item for a total of six per month. Now, the way this will work is that this supplementary data tab will hold the allowed categories and descriptions, and we're going to use that to enforce data validation within our expenses detail tab. We want to start by selecting all of the data where we want that validation to apply. Now, there's two different options here really. One is we could apply to all of our current data, so I could use control shift and the down arrow key to select all of my data here to the end of those two years worth of data. And then I could go into data validation and apply that. And it really all depends on how you would expect this spreadsheet to be filled for new data. So if there's going to be a new month, and you're going to take a copy of the previous month, let's say and base that beneath, change your dates, then when you do that copy and paste, that data validation will also copy across. And if that's how you want to run it, that's fine. Or maybe you expect new data to be filled in manually, in which case, you might want to enforce that data validation for not just the data up to here, but all the way down to cover additional rows, maybe in the entire column here. So let's do that, for example. So if I use control shift down to go to the end of the data region, if I use control shift down one more time, that will take to the very end, and I can click on data validation and data validation again and choose our validation criteria to allow a list, and our source is going to be clicking on our supplementary data and then choosing our three allowed categories here. And we can also change the input message here. So we could give it a title, such as choose category, and we could say, choose a valid category. From the supplementary data tab. And we can also customize the alert here. So we could put our title here as invalid category, and error message, please check the supplementary data tab. And if we press okay on that. So now because we've applied that to essentially the entire column apart from our header over here, we can see we actually have these drop down boxes on every single cell, and we're only allowing what's in that list. And the message that we chose appears anytime we click on any of these cells to advise us what needs to be done. And if I just go to the top, so I'm going to use control and the arrow key to go to my first data item and then control in the arrow key again. Now that's taken me to the top, but of course, I can't see all of my data. If I just press the down arrow key once, that's just going to bring all of my data in line over here. Now, let's repeat that for our descriptions. So this time, I'm just going to apply it to our existing data, so I'm going to press control shift down to select all of the descriptions that we do have right now, going to go in to data validation again, and we're just going to choose a list once again. Our source, this time is going to be our descriptions over here, and we're just going to leave those as standard inputs and error alerts and just press. So again, we've only got the allowed categories here, but the difference here is, we've extended this dta validation all the way along, but not this one. So just to make clear the point I was saying about how this would be extended out. If we were typing in the new items manual, you're expecting those to be typed in by whoever is using this, and we had our new month in here, so we had 11 2022, for example, here. And then we had our category here. So we're enforcing the category here, so we would have to pick one, but our description isn't enforced. So you could still type something in there. But the idea is that someone should perhaps be copying the entire previous month, using that and then changing those dates to the new ones. And then because they've copied this, these categories do have data validation on them, they can still be changed, but only within the allowed categories. Whereas this is extending all the way, but perhaps needlessly so, if we're not expecting to go, certainly all the way down to the very end of the spreadsheet. So either way works, and it's not particularly high overhead, depending on your spreadsheet as well. So go with whichever one you prefer. Okay, great. So now we've got some data validation in place as well. Now, let's move back to our monthly budget tab over here, and let's look to add in a chart. So In order to add a chart, what we'll do is, we'll start selecting the columns that we want included within that chart. So the most obvious one that you want to start with is, what is your x axis going to be? So it's clear in our case, we've really only got one choice. We'll want months running along the x axis. So we select all of our months over here, and then let's say we want to compare look at our estimated expenses. So I'm just going to use control and select my data over here. And then still holding control, I'm going to select my actual expenses. And let's also include our balanced movements in start. So again, holding control and selecting all of my data here. And then if we go into insert on the ribbon and click on recommended charts, and let's click on all charts, and then let's go to a combo chart here. So what we'll want to do is have our estimated expenses as a clustered column, our actual expenses as well in the same type of chart, and our balanced movements since the start, let's change that to a line graph over here. And let's also include a secondary axis for our line graph because the scales you can see here in the preview are very different. So we want to be able to see both those pieces of data well with two separate y axes. So let's press okay on that to take a look at our chart. Excel has correctly placed our months along the x axis over here, and we've got our values in here and our secondary axes as well. And if that wasn't set up correctly, we could always right click on this, click on Select Data, and we could actually choose our horizontal axis over here, so we could click on edit and make sure we're picking up the correct cells that we want. Let's now move this chart to its own worksheet. If we right click on the chart, and click on Mwhart, let's click on new Sheet, and let's move it to monthly budget chart. Press on that. Great. We now have our chart in its own worksheet over here. Let's make a few changes on here. Let's click on our chart title over here and we're just going to change that. Double click on that and let's say expenses and balance. Movements. And now in terms of our axies over here, so our X access is clear. But if you look at our two Y axis here that we've got, if we just hover over our data here, we can see the orange over here is our estimated expenses at 1,500, and then our gray is our actual expenses, and that's 17 50. So we know which is which, but it would be good if we had some axis titles here just to make that really clear when we come back to this maybe later. So if we click on the chart once here and over here, you click on chart elements, and we can add in additional items or remove items that we don't want. So for example, we're going to add in axis titles here. We just check that And within here, you've also got extra options here so you could do just the primary vertical, primary horizontal, et cetera. So in this case, we're just going to get these placeholders over here that we can then rename. So let's delete our access title, placeholder over here, and let's just say that's expenses. And you can put in a currency in here if you want as well. In brackets like that, and then we'll do the same thing over here. Let's delete this and we'll call this our balance movement. Great. Let's also enter our axis title for our horizontal axis here just as months. Now we have all of our axis labeled correctly as we want. Now you can play around with this as well in terms of if you wanted to change some of the colors within your chart here, just to make it stand out a little bit more, whatever you prefer. I'll leave that to you to play around with. That's the final thing that we'll do for this video then. So let's summarize what we've covered here. So we started by using some SF formulas within our estimated expenses and actual expenses so that we could accurately feed in data that we were populating within our expenses detail tab without having to key in formulas manually. We also introduced some logic formulas over here. So we wanted to check if our actual profit or loss was filled in, and if it wasn't, we wanted to bring in an estimated profit or loss within our opening balance. Then we also introduced some conditional formatting to really make it stand out where we are using estimates within our data. We then created a new supplementary data tab where we included our categories and our descriptions, and we looked at using removed duplicates to bring these in, and then we used data validation within our expenses detail to ensure that that was applied to our categories and descriptions respectively. And in between doing all of this, we used a few keyboard shortcuts as well. So, for example, control and down in order to navigate to the end of our data or control and shift with the arrow key to navigate and select our data very quickly. We also originally looked at our budget overview and using control and the open square bracket key in order to navigate to cells involved within a calculation. Finally, we saw how we could select our data over here and insert a chart and then move that chart to its own worksheet so that it takes up the entire space and we can see everything clearly. And then we changed the title, and we added in some axis titles on here as well, as well as just changing the format to make it stand out a little bit more. We'll wrap this video up here then. Be sure to save your spreadsheet down so that we can pick up from here when we come back to it after our next set of videos, and we'll have a lot to look forward to there, as well as we look to incorporate lookups and pivot tables into this budget spreadsheet. So until then, as always, thanks for watching, and I'll see you in the next one. 23. Advanced Features - Lookups: In this video, we're going to be talking about lookups. Now, lookups are a subset of the lookup and reference functions available here, we're going to look at a couple in particular, V lookups and H lookups. As always, make sure you're on the right worksheet, and let's get started. Now, lookups are useful for matching data between different tables. Finding a piece of data from one table, locating it in another table, and then bringing back a different value from that other table. Let's take a look at an example. We have here some names, some scores, and some tasks, and we've got a couple of blank columns here for task difficulty and ranking. Now we can see here, we've got a couple of lookup tables or reference tables where these scores correspond to a particular ranking, and then the task names here correspond to a particular task difficulty. So lookups will enable us to match these off within these reference tables and bring back their values here. Now, there's two types of lookups available, H lookup and V lookup, and the one that you use depends on how your data that you're referencing is structured. So the easiest way to see that is with an example is always. And we're going to start off with V lookups because our data is structured in columns over here. So let's discuss what we're going to achieve here. What we want to be able to do is find this score over here in this table and return the relevant ranking here. And then equivalently, we want to find this task and return the task difficulty in this column over here. We'll start by using the function library within the formulas bar at the top. So go to look up in reference and scroll down to find V look up. Now, let's examine these function arguments. So we've got three mandatory and one optional. So the first thing is, what is the lookup value that we're looking to find? So what we want to do is we want to find this task in this table. So I'm going to say I want to find what's in cell C two for my lookup value. And which table am I expecting to find that a? So that's my next argument here, the table array. And in this case, we know it's this table, so I'm just going to drag and select that table, including the headers. Now, you can return either the first or the second column in this case. It wouldn't be much good just returning the task name again. So we're going to choose that we want to return the second column from our table array, which only has two columns. We enter a two in there. The final optional argument is either a true or a false and a false we'll find an exact match or a true will return an approximate match. In this case, let's go for false, so we want an exact match and press. Now you can see this formula has found the value of C two within this stable array. And what it does is it looks at the leftmost column of the array that you've chosen. So it's looking to find running in the leftmost column, which is column of this stable array in red. And then we've said that we want to return what's in the second column. So task difficulty, and we want an exact match by entering false there. And so we get the expected task difficulty. Now we can copy this formula down as usual. I'm going to copy that, select those cells, and I'm going to paste. Now you'll see we've got a problem here right away because the way this table is moving down because of the relative references is incorrect. So what we really want to do is want to fix this table here. We're just going to move our cursor here, click, and then press F four and do the same for the other cell range. We're wrapping absolute reference tags on both the column and the reference on both ends of the range, press enter, and let's just copy that down again. Great. Now, let's change a couple of these values here just to make sure that the formula still continues to work. There we go. Still finding a reference there as expected. Now if we were to have something else in here that wasn't in the list, we would get an error. For example, like that. Let's undo that. Now let's look at ranking here. Now we can do the same thing. This time, I'm just going to type this in rather than use the function wizard. So say we look up press stab to bring up the rest of that function name, and we've got the exact same arguments here. This time I want to find the score, and again, I want to find it in this table array here. And once again, what it's going to do is it's going to look for score in the left most column of the table that we've selected. And then we need to tell Excel which column do we actually want to return? We want the second column again to actually bring in the ranking name. So column two. And this time, we want to go for an approximate match. Now there's two ways of doing this actually, so we've seen that we can try or false, so two would be an approximate match. The other way you can do this is actually using one to represent two and zero to represent false. I'm just going to enter one there and press enter. If we copy this formula down as before, we're going to have the same problem as we did earlier where this table range is moving down in a way that we don't want it to. Again, we can't fix this, but there's actually another way of getting around this problem. And that is rather than using a particular set of cells that represents our table, we can actually go for the entire columns here, so we get rid of that second parameter for table array, and I'm just going to highlight like that and presenter. Now you can see it's running from column I to column j. That works. If we copy that all the way down, you can see there's no need to fix anything because the entire column is being looked at. What the heloUp is doing is looking for the value here and then finding it and then moving across to find the equivalent matching value and returning that from a second column. Now, let's just study up the formula here on the left Because I think when we were copying these down, we've got one on false, and we've got one using the zero, which is the same thing. It doesn't matter, but we left our top on using approximate. So let's just copy this and paste that to make sure that all of our formulas are aligned. So let's look at the difference between using an approximate match here on the ranking and using the exact match on the task difficulty. Why would you use those and what happens if you get it wrong basically? So press F two, so we can see the different cells and tables involved in this easily. And when we're using this approximate match over here with the one or the two, what Excel is really doing here is it's looking at the different scores here and finding where within those bands, these values fit. So anything that runs 0-19 will be given a lowest ranking, anything that goes 20-39, the low ranking, and so on, so forth. Now if we change that to an exact match, if I change that to either false or a zero on the last function argument there. Press enter, this is not going to work because in this case, Excel is trying to find an exact four within this left most column. Obviously can't find that. Now, this could still work if you had exact matches, if this was a 20, zero, 40, you would get your equivalent values in there. But that's why we have to use approximate match in this scenario, we're looking at different ranges of numbers. Now, let's look at the formula we have in our task difficulty over here. We've used false or a zero representing an exact match. If we were to use a tru or a one to represent an approximate match. We can see it works for the first value. However, as soon as we copy it down, the formula breaks down. This is because Excel finds it difficult or impossible to reconcile non numeric data as an approximate match. So in this case, even though we've got actually an exact match here, the fact that we're asking Eel to find approximate match is unable to do so. So we would have to use an exact match for this to work correctly. I Let's summarize briefly how the V lookup formula is working. What it's doing here is taking the first argument, which is a lookup value, and it's saying, we want to find this value, and then we're asking Excel to look in a particular table array, which can actually be dire columns in this case. So we're saying find it within one to M four, and it will look within the first column of any table array. This could extend out to however many columns you wanted, but it's always looking in that first column to find the match. Now, once it does find the match, we have to specify which column we want. Let's say we did have seven or eight columns here. We could choose seven or column eight, whichever one we wanted. In this case, we're saying, once it finds that value, so it works its way down, hits running, it matches it and says, Okay, I'm going to return the value in the second column here, and it's looking at it being an exact match on running here on the parameter that we've provided here at the very beginning. So when it's looking for cycling, again, it's working down the list and it hits cycling, and then it looks at the second column and returns the equivalent value, and so on and so forth. Now, let's look at H lockups as well and do this. What we'll do is actually, we'll copy our reference tables here and we'll just pace special over here and we'll transpose the data. Now, as you've probably guess, H lookups are very similar to lo cups, except the reference data you're looking at is arrayed horizontally as opposed to vertically. So let's wipe this out and see if you can figure out yourself how to use H lockup before I go through it. Hopefully, you gave that a quick yourself. Now let's see how we can use HH lookup. Again, I'm going to start my formula with an equals and let's start with task difficulty. This time, I'm going to look for H lookup, and again, the value I want is here on the left. And this time for my table array, what I want is this table over here. This time, you can see instead of returning a column index, H lookup wants a row index. Let's think about how this is working. It's looking for C two within this table. And once it finds it, what it's doing is it's going along rather than down. So over here it was working its way down until it found, let's say swimming and then returning the value. In this case, it's working its way along the very top row in the same way that it works its way down the first column. So it's going along the top row and once to find swimming, we have to specify which row we want to return from the table. And again, the table could extend to many more rows. In this case, we only needed to go to there. So we want to return the second row here. And once again, be careful with your approximate and exact matches. So in this case, we want an exact match, so I can use a zero or false. And we can just confirm that that works. Now, again, let's just fix our array here, so we don't have any problems with this moving down as we copy our formula down, and there we go. As before except now, we've used H look rather than V look up. Let's do the equivalent for our ranking formula. We'll start with an equals and a H look, again, pressing tab, just to have Excel auto fill that. This time, I want to look for the score. For my table array, again, I can choose the table like this, or I can choose the entire rows here. And again, which row index we want Excel to return. So again, the way this is working is, it's going to look for this value here that we've chosen as the first parameter. It's going to work its way along. When it finds an equivalent band where it's in, it's then going to be looking at this third parameter here and saying, well, which row is it that we want to return? So we want the second row once again. And this time we want an approximate match. I'm just going to use one and going to press Enter. Now, when we copy this down, we're going to have a slight problem actually. So if you do that, you'll see we get NAs here. And that's because the way we've structured our formulas here as we're moving the formula down, everything's moving on the left, we's also moving our rows here. So we want score to be moving obviously because we want each one to look at it respective score, but we don't want the array here to move. So in this case, we do actually need to fix the rows here as well. So just pressing F four will actually fix that. Again, we can just copy and paste that. Exactly as expected. Now, if we go back into our function library and just look at we look up as an example, I said earlier that there's three mandatory arguments and one optional, and so this range look up here being optional means that there's a default value that Excel will use for you, and that default is an approximate match. So over here, for example, if we were to take away the zero or if you had a false, and we just had our three parameters in here, so the lookup value, the table array, index, and there's no more comma here with a range of lookup. Press center, and we'll just copy that formula down. Now you can see that fails because it's reverted to a default type of an approximate match. Excel has treated that as though we put a comma one in there or a comma tre. You'd be fine for the ranking over here if you forgot to put this in. This would work just fine, but it wouldn't for anywhere that you need an exact match. To summarize then, we've seen the usage of lookups. Specifically, we look up in H lookup, and how they can be used to find values from one set of data in another and how we can bring across corresponding values in different columns or different rows when that value is found. You'll find there's a wide variety of scenarios where you can use lookups to your advantage. For example, in reconciliation purposes, it's very useful to be able to check if a value from one data set exists in another and to bring across any corresponding values that you might want. Well wrap the video up there. As always, thank you for listening, I'll see you in the next lecture. 24. Advanced Features - Match & Index: In this video, we're going to cover a couple of very useful lookup functions and how they can be used to find information within Excel. Let's start with a match function. This can be used to find the relative position of an item in an array. So let's see that with an example. So With Match, we're going to have a lookup value, a lookup array, and a match type. So let's start with our value. Let's say we wanted to find out the position of David here. So I'm going to put that in speech marks. And for our look up array, now, it's very important that it's either a string of cells either in the same column or in the same row. You can have entire columns in rows as well, but you can't have a two dimensional array. So in this case, let's pick the entire column here. And for our match type, we've got three different options here. So what this amounts to is the closest value that is less than a value that we specify, an exact match, or the closest value that is greater than the value we specify. Now, obviously, these two options here only apply when you're dealing with numbers. So as we're looking for text, we're going to choose the exact match. And let's enter that. Now, let's analyze the result we've got. So what Excel has done is it's worked its way down this column, and it's given position numbers to the items in this array. So we start at one, two, three, and so on. And it's found David here, that's position three. And that's the result that we have had returned here. Now, just to illustrate that that is a relative position, if we were to change our array that we're looking at here, to instead start on David, and let's just say we went down to there, and we press enter there. That's the first item within the highlighted array there. And this works just the same for rows. So for example, if we had our cells here, I would expect that to return a three because that's the third item in that selection there. And equally, if we were to change that to those two cells there, that the first item only there. Now, just to confirm that it doesn't work if you have a two dimensional array. So if I was to change that there, we would get an NA. So let's just undo that. So unlike VL cups and H lops, which return the values of cells, we can use match to obtain the position of an item within an array. Now, there's many creative ways of using match, and we'll see one of those in a second. But first, let's introduce the index function. So let's take a look at how index works. So it returns a value or reference of the cell at the intersection of a particular row and column in a given range. So in practice, what does this mean? Well, the first thing you'll see is that there's actually two different forms of this function, but actually it's very fluid between these because if you take a look, you've got optional parameters here for column number. Here and also area numbers. So there's actually no real difference in how you formulate an Excel will intelligently switch between these formats, and you'll see that as we work through this example. So to start with, we'll need an array. So let's pick our table here. And then what we can do is specify a particular row and column in terms of an intersection on where we want our data to be picked out. So, for example, I could say Look at row number one, and optionally, I'm also going to enter column number one. So what would I expect? I would expect that's row one in column one. So I would expect employee ID for the value to be returned when I hit enter. And that is indeed what we get. So we can just check one more. We went 12 and four. That should be the 12th row in RA and the fourth column. So 14 49 should be returned, and it is. Now let's see how we can use the area number parameter. So the way this works is, we're going to replace our array here with a couple of different options. So we're going to have let's say A one to d four, now we're going to have a seven down to D 11. We're just going to put both of those ranges in brackets. We've got two different areas here. We're still specifying a row and a column. Now we're going to change this because our tables are no longer big enough for 12 rows. Let's say we had row three, and we can keep column four. Now we have to use our additional parameter here at the end for area number. So simply it's area one here, area two, and we could have additional ranges in here as well. So let's say I wanted to pick out what's in the third row and fourth column of the second area, this range here. I'll just put a two there. So what do we expect to get? So three rows down and four columns. So I would expect to see 151, and that is exactly what we get. The area number parameter here is optional, so we could delete that out, and in the background Excel will default that to a one, so it will look at this range here. Therefore, I would expect to see Row three, column four, 156 returned. Okay. Now let's see how index can be used to return an array of items. So I'm just going to change my range here to the entire table again. And what we're going to do now is, we're just going to drop, for example, the row number here and just put a zero in there. And what this will do is return the entire fourth column here. You can see it spills out into subsequent cells down here. I entered a zero in the formula here, but I could have just dropped that and had blank in between the coma, and I can do the same thing for the column here, either entering a Zero or leaving that out. We'll actually return the entire table array that we've chosen in our range. What we're seeing here is the latest functionality available in Microsoft 365, and that is dynamic array formulas. Now, in older versions of Excel, what you may have to do is click on your formula and hold control shift and press enter. And you'll see what Excel does is it wraps these curly braces around our formula, and that returns an array formula in the older versions of Excel. Next, let's look at the reference aspect of the index formula we talked about earlier. So let's undo this, and we're just going to change this formula slightly now. So let's say that we wanted a sum, and we're going to start our sum at d two, and then in terms of the range that we're going to go through two, I'm going to choose the index function here. I'm going to use index on the entire array A one to D 12, and I'm going to specify a row in a column. So I'm going to say row 11. And I'm going to say column four. So we know what that returns by itself. That's 137. So let's close that bracket there and press enter and see what happens. Now, Excel has actually summed up from D two to D 11 to bring the 14 49. But we know if we take this portion of index here, I'm just going to copy this. I'm just going to place it here, put an equal sign and press enter. We can see that that returns a value when it's used by itself, and the sum function doesn't make sense if it goes from D two to 137. It only makes sense if this value here is the cell reference. So this is where Excel is intelligently switching as required based on the function it's being passed into. So here it knows that sum wants a reference. So it's actually going to return D 11 here for the index function. We've now seen match and index separately, but these two functions actually work very well together and can be used to powerful effect. To see this, let's unhide some columns here between H and J, and we've got a short exercise here. So can we find David's employee ID using functions? So the first thing is, why can't we use something like V look? Well, we know that V look up looks at the left most column in an array. So let's say we wanted to find David, and we wanted to start here because we know David's obviously in this column. So we could choose Column C. But then the problem is the ID is here on the left, and there's no way of going backwards. You can't specify just column C, for example, and put a minus three in your column. It just doesn't work with a VL cup. So how can we use match and index for this? We can do this with a combination of match and index. So we'll first use match to obtain the row number containing David. So we're going to match, and I'm just going to specify David typed indirectly, and we're going to look for it in column C, and I'm going to look for an exact match. So now we know it's the third row down. So we've got that piece of information, and we can feed that into index. So let's set index up, and I'm going to choose my array here. And for the row number, we can simply feed in what we've obtained from our match formula. So I'm going to click on that. And for the column number, well, because I've got the entire array here, we can work backwards in a sense, so I could specify the column unlike with V lookup, so I can say it's going to be column one, and I'm just going to close that off. There we go. We've got the employee ID using a combination of match and index, and if you were using this to feed data in that could be picked, for example, you could actually specify a cell and instead of typing this in directly. We could reference that cell over here. That way we can find other people's employee ID. So for example, Harry is employee ID ten. We could also have the entire function within one cell, so we could grab our match function here and rather than referencing the result of match in a different cell. We could just feed that in and we achieve the exact same result, like so. In summary, then, we've covered two very powerful reference functions, and we've looked at how they can be combined to achieve certain solutions. So as always, thank you for listening, and I'll see you in the next one. 25. Advanced Features - Pivots: In this video, we're going to be looking at a really powerful tool for analyzing, summarizing, and slicing and dicing your data in Excel. And that is pivot tables, and that's accompanied by pivot charts. So as always turn to the correct worksheet, and what we've got in front of us here are some customer numbers. We've got some dates, a product class, quantity and sales. Now, our data runs down to about 100 roads, and we've got 20 different customer numbers that are having sales being made throughout the months of January and then repeating in Feb all the way through to March. Let's say that we want to perform some analysis here. So we've seen various tools so far, but the next one we're about to see is really very powerful and easy to use actually. So we'll start by highlighting our columns here to pick up all of that data. Make sure you're in the Insert tab on your ribbon, and then click on Pivot table. Now, the table range that we want to select, we've already done that by selecting our columns before clicking, and we're not going to using an external data source. Obviously, we're just using these columns. Where do we want this pivot table report to be placed? Well, we can either put it in a new worksheet or just so that it's easy and we can see our current data. Let's put it on the existing worksheet. And for the location, I'm just going to stick it over there on H one, and we're not going to add this to a data model. We're just going to press okay on that. Now, what you see on the right are the pivot table fields corresponding to the headers for our data. And what we can do very easily with these fields is we can drag and drop in order to create an array. So let's start with customer number here in our rows, for example. And let's say we wanted to see the sales. So we can just drag that in. And now what you can see is we've got all of the sales across all of that data set for every customer. Now you will see that we've got a blank in here as well, and that's because we selected the entire column when we were generating this Pivot table. So we've got blank there, but we can easily remove that later with a filter as well. Now, what we can start to do is slice and dice this data even further. So for example, let's say we wanted to see quantity as well as sales. We can just drag that in. And now we can see how much we sold to every customer across the five months and what the value of sales was. We can also see how many sales we made or how many quantities. So for example, we can click on this and we can change the value field settings, and we can change it from the standard sum to account, and you can see for other options here such as average Max Min, and so on. So obviously, we're going to see five there because in this data to keep it simple, I've just made it so that every single customer has had a sale across every single month. Let's carry on with this. So let's add in product class to our rows. So now what you'll see is we've got each of the different product classes for those customers split like that, and we can change the order on this. So if we didn't want to see it by customer and then product class, but we wanted to see those three product classes, A, B and C, and then see how many sales have we had against each. We can do that very easily as well. If we no longer want to view something such as the quantity, we can just left click and drag this out, and we can actually move things around as well. For example, maybe we want to see product class along the top here, and then we want to see customer numbers along the side and then view those sales quantities. We can view things in a two d array as well. Now, we've still got the blank showing up here, so let's see how we can get rid of that. You can simply go to your roll labels here, which is our customer numbers and click on this, scroll down and we can just get rid of the blank just like that. Now, let's look at bringing dates into this as well, because there's a couple of interesting things to be reviewed there. So I'm going to get rid of product class here, and I'm going to bring dates in underneath customer number. So now we can see the sales made per customer per month, and we can obviously expand these down as well as needed. But there's a quicker way actually if we wanted to expand all of those months, we could simply right click, and we could go to expand Clapse, and now you can expand or claps one at a time, or you can expand the entire fiel, which will open up all of those dates across all customers. And as before, we can, of course, move the customers down, so maybe we want to look at the months and then the customer sales within each month. We can also see that we've bought in months and date, but we can get rid of the specific date and just leave the months in so we could see the sales made in January to each of the customers. Then in just the same way as we expanded all of the months, we can also collapse them in one fell swoop, so we can right click and we can do collapse entire field. And now we can see the sales made per month without seeing the granularity of the customers. Now, let's talk about the filtering again. We've seen how we've been able to filter on the customer numbers here that we had dragged into our rows, but you don't have to have data viewable in order to filter on it. For example, if we wanted to filter on product class, but we didn't necessarily want to see any data on it, we could simply bring it into our filters over here. Drop this down and you'll have to click on select multiple items. Let's say we only wanted to see product class A. We could just do that. So that's an important thing to be aware of because sometimes what people will do is they'll drag something from one of the rows or the columns over to filter, thinking, you know, they'll keep the data there and be able to filter. But you have to remember if you're already viewing something in a column or a row, what you should be using is these drop downs in order to filter and only use this when you don't want the data related to that header to be viewable. Next, let's see what happens when we add additional data within the range that the pivot table is looking at. So in this case, I'm going to add something in for January. So just remember, we've got 13 21 for sales here. So I'm going to go to the end of my data with the control and down archy, and I'm going to put in customer one and first of January date. Product classes A, and I'm going to just put 100 there. Now, we can see that's still 13 21, and if we look at the data that we had picked out, so we'll go to change data source in Pivot table analyze. You can see we've actually picked out the entire column, so it's certainly covering the data. So why is it that our data here hasn't updated? Now that's because within the Pivot table analyze, you'll have to use the refresh command. So we can just click that and you can see the pivotable refreshes, and if you've got multiple pivot tables, you can actually use the refresh all command to refresh all of them at once. One more thing to be aware of when working with pivot tables is, if you've got data, let's say, beneath this pivot table, and I was to expand one of these categories here. Now, that expansion is going to make that pivot table go over the cells where I've entered some data. So Excel warns us that there's data there, and do we want to replace it? So if we were to press okay on that, You see it's expanded over those cells. And if I collapse this again, the data is gone. So just be very careful when you've got data and how you're positioning your pivot tables if they're within an existing worksheet. If you're working within the same session as in you haven't closed Excel down and reopened it, then you can always use the undo button to get your data back. Now, I'm just going to go down here and I'm going to actually delete the extra line that we've added. I'm just going to go back up here and just to show you that we can refresh from a couple of different places. So we've seen that we can go into pivotable analyze, but you can actually go into the data tab as well. And this isn't specifically just pivot tables. You've actually got the refresh here as well. So we can just do that and we're back to our 13 21 there. If we want to remove the pivot table entirely, we can do this easily by selecting the columns that the pivot table covers, right clicking and using delete, or you can also use the control and minus shortcut on your keyboard. So you've seen just how easy and powerful a tool pivot tables are in order to analyze your data and to draw out insights. Now we can actually go one step further and that's via pivot charts. And as you might have guess, it's basically a chart based on your pivot table. So if you move to the Insert tab here, and let's go to pivot chart. Now, you'll see here I've actually highlighted my Pivot table already. So I'm going to click on Pivot Chart, and this is going to recognize that I'm basing my pivot chart on this pivot table. And let's just go with a standard clustered column for now, and that will just bring it in over here. Now you have a dynamic chart that will respond to any changes you make on your pivot table. For example, if we were to expand January out into our customers, we'll see that appear on our pivot chart. You can also change your filters and your values from the pivot chart directly as well rather than from the Pivot table. For example, we can bring in all product classes, and that will update accordingly. If you want to be able to drill down, you can use a very handy expand entire field right here on the chart, so we could see all of the customers within our month there and collapse that again. Now, all of the features we've covered before on chart supplies, for example, we could right click, and we could move this chart to its separate worksheet if needed. We can also change the type into different types of chart as well. Now, what's interesting here, though is we have a couple of additional options, as well as this being a dynamic chart, as you've seen already, based on the pivot table, we can actually go even further, so we can go to our pivot chart analyzed tab at the top here, and you can see there's a couple of options here to insert a slicer and a timeline. So let's start with a slicer. Let's bring that in, and let's just paste it on customer number for now. So what you have here is a very handy visual way of essentially filtering on your data. So, for example, if I click on one there, it's only going to be showing customer one the sales that we've had across every single month. I could also select multiple customers. If I drag down, I can select customers one through 26. L et's amend our pivot chart here so that we can actually see the customer numbers. So you can see here, for example, we want to see the first six customers or just the customer seven, and so on. Now we can clear that filter out, and we can use the multi select toute here as well, and what that will do is start to remove items. For example, if I didn't want to see customer one, you can see now we're just going 2-20 and I can do the same thing. I can select multiple items. Right there, I can't see customers one through seven. We can just as easily bring in additional slicers. For example, we could bring in product class, and it would behave in exactly the same way. For example, if I only wanted to view product class B, that would work just like that. Now, we could also use a slicer for the dates. I could go in here and we could bring in date. And we can work with this in the same way. But you can see because of the amount of dates I have here, it's not the most user friendly way of doing this. So I'm just going to press delete to get rid of that. And instead, we're going to use a different tool here, which is the inserted timeline and works very similar to the Slicer tool, except it's a much more user friendly way of selecting dates. So for example, now, I can simply click on a month, so if I wanted just January, I could do that, or I could drag, if I wanted January February and March. I could see those just as easily. You can very easily drag across months as well. So if I wanted to go all the way from January through to December, you'll see it'll scroll along for me now, in this case, obviously, we only have data going to me, so it doesn't make any difference. And we can also drill down, so we could change from months to days if we wanted, and again, keep that user friendly interface so you can drag across as you need to select the data that you want. To summarize, then, we've looked at pivot tables and what a powerful tool they are in allowing you to analyze your data by slicing and dicing it easily and effectively. We've also seen pivot charts linked to these pivot tables, which is a really great way of visualizing your data and allowing you to have a dynamic chart in essence. Finally, we looked at the slicer and the timeline tools, which are a really user friendly way of filtering your data on your pivot tables and your pivot charts. We'll wrap the video up there then. As always, thank you for listening, and I'll see you in the next one. 26. Build a Budget Checkpoint 4: Hi. Welcome to the last in our Builder Budget checkpoint series of videos. Once again, we're going to pick up from where we left off at the end of the last checkpoint. So if you've got your own file hopefully saved, please open that up. Otherwise, you'll find the file attached to this lecture of where we got to at the end of checkpoint three. I'll start with a quick overview of what we've built so far. So on the budget overview tab, we've got our annual figures, so estimated and actual profit loss income and expenses. And this relies on data that's entered on a monthly basis here in our monthly budget tab. And this data itself relies on our expenses detail tab here, where we can categorize the different types of expenses, for example, and show them per month. And then we've also added in a graph over here just to show some of our data visually, and we've also included some supplementary data in here, for example, to provide some data validation within our expenses detail tab. Let's now incorporate some of the new things that we've learned in the past few videos. We'll start by going to our supplementary data tab here, and we're going to put in some default estimated costs in here. So what we're going to use in here then are some base costs that we can pull into our expenses detail tab. So what we want to do here is enter a few basic costs that are default estimates, and then we want to pull those into our expenses detail tab. So just to keep the numbers simple, if we just take what we've entered already here for the six costs that repeat, we just copy those and paste those in here. Now, think about what we can use that we've seen recently in order to pull these in in order to avoid having to type these in or copy paste these every single time. Hopefully you guess that we can use a reference function of some sort, and the easiest one here is probably going to be our V look. So if we type in equals, V look up, and as that comes up, we can just pres stab to fill in the rest of that function. And now what we want to do is look up a value fair. So what is it that we're looking to find? So we'll look to find what's in our description here on the same row that we're on, so cell D two here. And where do we expect to find that for our next parameter, our table A? So we're expecting to find that within this column here, column B. But we also want to include column C here because what we want to do once we find that is pull in the estimated cost. So we look at supplementary data, columns B through to C. And then if we find what we're looking for in our lookup value, so if we find restaurant, which column we want to bring in from this stable array, where we want the second column C. So we put a two in there, and finally, we want an exact match, so our range lookup can be false or exact match. We just enter zero for that and just close that off and press enter, and we have our value being pulled in right there. Now what we can do is simply copy that formula all the way down. So the easiest way is going to be control shift, and the down arrow key that selects everything. And at the very top, our top cell has the formula that we want, so we can just do control and D, and that's going to copy the formula all the way down, which I've done. Now that hasn't changed the values because, of course, we took our six standard values, and we actually entered them in here. But you can see every single cell now has that formula here, and we've got our relative references looking up each single description as we expect. So let's talk about why we've done this. Well, we've done this because we can now change a value in just one place and have that feed through to the rest of our spreadsheet. So, for example, we could just change train here to 100. And if we go across, we'll see that that's changed for every single instance that we have of that over here. So it's really helpful to be able to enter a cost or any data that you expect to repeat many times using that formula link in with it like that. In terms of building a budget specifically, what that means is, we always expect our train expense to be 100. But if we do expect it to be higher on any given month, we can always say that, you know, we're expecting this to be perhaps 200 that month. You can always type in over that and replace that and then you expect your standard cost to kick in again. So that's really useful. It saves us having to enter these values manually or copy and base them. We can control the entirety of our cost base in away from one place. So if we know that rent goes up, for example, we don't have to change every single value. We can just change it over here and let that feed through. Now, it is good that we can still type in, but it's not clear where we've typed a value in and where it's relying on the V lookup. So we could do a similar trick to what we did with some conditional formatting here for our expenses detail, that could be quite helpful, actually. So let's think about what we did when we were looking at our monthly budget here. The way the conditional formatting works here is we rely on this cell over here being blank. So the moment any value goes in, that formatting goes away, and we can see those are actually pulling in the actual profit or loss. So we can't quite do the exact same thing over here. But what is it that we're trying to differentiate on? So we want this to be in a different color, let's say, if there's a VL cup or more generically any formula, and if there's just a value in here, we don't want that formatting. So that's our differentiating factor right there. So we can use a formula here, and that's the is formula function. So if we use is formula. You can see this checks whether a reference to a cell contains a formula and returns true if it does. Otherwise, it returns false. So let's just test that out first. So if I apply is formula, and for the single parameter, I enter our cell E two here. And I press enter, I can see that's a true. And if I just go down over here to where we entered our manual value, I just to control d to copy that formula all the way down. You can see that one place where we entered the manual cost for the train returns a false. So we could use this formula to create our conditional formatting over here. So all we have to do then is go to conditional formatting elsewhere on the active cell e two, go into new rule, and we're going to use a formula once again to determine which cells to format, and that's simply going to be equal is formula, and we're going to choose e two, and I'm just going to remove the absolute cell references there again. Now, you'll see as we type anything in here, Excel doesn't give us the helpful formula list so that we can just press tab, nor does it give us the parameter list, which is why it's sometimes useful to do what I did there and just run your formula or your logic on the side here in Excel, so you can benefit from those parameters and do some experimentation. And then once you're fully ready and you know the result you expect, you can go ahead and type it in here directly. Now let's change our format once again, and let's use the same color we used before. Press okay on that. Again, we've got that applied. Now, let's just copy this all the way down, including the formula and the formatting. So I know I'm going to lose this when I copy over this, but if I just use control shift down, control D and enter that formula across the entirety of it. And now let's change once again one of our values. So instead of having this V lookup, if we just enter in a value and press center, we can see that the formatting goes away. So now we have a really clear indicator of where that cost is being pulled in from our supplementary data default cost, and where maybe we've changed it for any given value in any given month. Let's stick with conditional formatting for a second, and let's add in a new column header here. So we'll fall this actual cost overview, and we're just going to set this equal to our actual cost. So we're just going to repeat that same formula, so equals F two and then control shift up, control D as always to copy down. So we've got the same information repeated here, but what we're going to do now is we're going to select that entire column here, and we're going to go into conditional formatting, and we're going to use data bars. And let's pick solid fill here, for example. Now we can see a data bar that indicates the proportion of that cost across the entire column. So, for example, our highest actual cost is 750. So the entire data bar is filled in, anything that's, let's say approximately halfway, you'll see half the bar filled in and so on. Now, let's just edit this slightly to remove our numbers as well. So if we're going to manage rules, select our rule and click on Edit rule, we can click over here to show bar only, press, press supply, and ok. And now we no longer see the number in the background. But again, we just have a really good indicator over here. So if any of these costs were to change, for example, if this was 2,500, It's dynamic, so you'll see all of our data bar change accordingly, so it shows that's our highest cost there, and so on. Okay, great. So now our expenses detail tab here is just a little bit more informative and useful. Now, let's change gears slightly here, and let's add some analysis on top of our expenses detail. And we'll do that using a pivot table. So let's start by selecting all of our columns here, and we'll go to Insert in the ribbon here, and we'll click on Pivot table. Now, our table range that we're looking at for the data already selected as per the columns we just selected. And the next thing we want to do is choose where we want that pivot table to be placed. So we'll do a new worksheet here, such as go ahead and press okay on that. Let's start by renaming our sheet over here. So we'll call this expenses summary. And now we can simply drag and drop the fields that we want to see. Let's say we pull in our category over here to our rows, and we also pull in description underneath that. So each of our categories is going to show its descriptions available underneath. And now let's pull into our values over here, the different items we want to see. So let's say sum of estimated cost, some of factual cost, and let's also bring in the difference. With our pivot table place then, let's go ahead and add in a title to our tab over here. Let's call this our monthly expenses summary. And let's just make that title stand out a bit more by putting that in bold and increasing the font size. Now, this pivot table shows us at a glance, our various categories, their descriptions, and those columns and estimated costs, actual costs, and the differences. So we can see a snapshot or slice and disar data very easily in order to get the insights that we want. Within our pivot table, we can go ahead and filter on different items. So for example, if we only wanted to view certain categories or we didn't want to see the blank that's being pulled in, for example, here, we can just remove that. Now, what we see here is we can only access our categories here. We can't access the descriptions within in case we wanted to see an overall category, but maybe we only wanted to see one or two descriptions within that category. We can get around this problem very easily, though, and we can do that by clicking on our Pivot table here, going into Pivot table analyze at the top, and we can go ahead and insert slicer. Now, let's insert a slicer here for category and a separate one for description and press ka. So what that's going to do is create two of these slicer boxes over here. And in effect, these are just quick methods for us to be able to filter on the items that we want. So, for example, we could just click on one category here, or like so. And then in terms of accessing those descriptions, we can now go ahead and filter on the descriptions as well. So we can achieve what I was talking about earlier with being able to filter on certain categories, and it's also a lot quicker to do it this way because you can simply click and drag, select the items that you want, clear that filter very easily, and just really actually slice and dish your data at a glance and get the information that you need. Now, you can go ahead and format this however you want. So if you want to that title and drift color, let's say, as well, and I'm just going to change these pivot tables and slicers as well. So we can do that by going into the design tab here at the top. And for example, we can choose a green color here for our pivot table. We can do the same for our slicers. If we click on our slicer, click slice at the top. Again, we can just choose whichever color formatting that we want, like so. And finally, in terms of our blank, let's just click and drag to select the categories without the blank, just so we don't see that over there. Okay, great. So with that tab set up, let's go ahead and move back to our expenses detail. And we're going to select all of our columns here again, and we're going to insert one more pivot table over here. So if we click on Insert, and then pivot table again. And this time, let's set this up on an existing worksheet. So once we click on that existing worksheet radio dial button, when we click on location in here, we can click on the tab, we want this. So let's say we're going to put this in our supplementary data tab, and then choose a cell, which will be the top left cell of our pivot table. So let's put that over here on cell E three. We just click on that cell and press. This time, let's bring in description to our rows and actual cost to our values over here, so we get su of actual cost. Let's go ahead and filter out our blanks here as well. Now, what we want to do is insert a pivot chart based on this table here. If we click on Pivot table, analyze of the top here once again, and choose the pivot chart option. Let's go ahead and keep a clustered column. Go ahead and press okay on that, and that will insert our chart. Now let's go ahead and move this chart to our front budget overview tab. If we right click here and click on Move chart, and we're going to choose it as an object in rather than a new sheet, and just choose the tab that we wanted in, so we'll choose budget overview over here and press. And that moves that chart over here to our tab. Now, as we did with our expenses summary pivot table, we can go ahead and insert a slicer. So let's do that, and we're just going to choose description only over here. Press okay on that and position that to wherever you think it looks best. So I'm just going to put mine here for now. Now, let's click back on our pivot chart over here, and we're also going to add in a timeline. So if you click on Insert timeline here and just choose date and press okay. And again, position this to wherever you want. And what this allows us to do then is again add with our slicer, just be able to very quickly choose dates that we want simply by clicking and dragging and our data will change based on the dates that we've selected. So we can go ahead and change what we're viewing on our papo chart with a combination of our descriptions over here, as well as the time period that we're looking at. Okay, great. So with that done, let's just come away from pivot charts, slicers, and timelines for a second again. And let's go back to our logical formulas. So if you remember, when we set up our budget over you tab initially in terms of our projected profit or loss here, we're showing one year's worth of data here. So we've just done a direct sum of our monthly budget tab, for example, here from F two to F 13. So if I just use my control and open square bracket shortcut here, just to see that data. All we're doing is we're summing up from January 20 to December 20 manually. And it would be nice if we had a way of being able to choose a year and for these formulas to automatically change and bring in the correct data. So as a bit of a challenge, why not pose this video and see if you can come up with a potential solution. Now, even if you can't come up with a full solution, why not think about the types of things that you might need in order to implement part of your solution, and then go ahead and see how I go ahead and resolve this. Okay. So hopefully, you did pause the video and just give this a bit of a think. But we're going to go ahead and do this together now. And if you were thinking about using a SUMIF, you were definitely on the right track there. Now, in order to make that work, we do need some other information change within our monthly budget. So what we're going to do first is just add in an extra row here. So I'm just going to use the shortcut here for shift and space and then control shift and plus to add in one row. And let's just put a subheading over here for a year. And then this cell is where we'll type in any year, so for example, 2020. And then anywhere that takes data input, I like to make that clear, for example, with a different font color. So if we just put that in yellow, for example, Now, let's go ahead and switch to our monthly budget tab and see what we need here. So what we want to do here in the case of our estimated profit or loss. We want to sum up this column over here, column F, but we want to do that based on if the year over here matches what we have entered into this cell over here. So what we need to do first of all is add in an extra column here that gives us the year. So let's just go ahead and stick an extra column in at the very start. So what we'll do is just select column A. Right click and insert, and we'll just call this year at the top here. Put that in bold as well. And we'll just use a very simple formula over here to just do equals year, which will return the year of a date that we choose. So it only takes one parameter here as a serial number, which represents a date. And if we just take our cell over here, press enter, we'll just get the year for every single cell. So I'm just going to copy that down, and quick way of doing this that I like is just control down to go at the end of my data region, go left once. Control shift and the up arrow key to select all of my cells. My cell at the top has the formula with the value that I want, and I'm just going to use control and D to copy that all the way down to get my years next to every single cell. Now we can switch back to our budget overview tab. So you can use the mouse or control page up, just to switch back. So control page up and page down will switch between your tabs. And what I want to do here is replace my projected profit or loss formula here with a sum. So see if you can go ahead and figure that one out. Okay. So hopefully, manage to do that. So what we're going to do is equal some ifs. And the first parameter is always is the range that we want to sum. So I want to sum up the estimated proffer loss, which now is column G because we've added in an extra column. And then the criteria range that we're checking against is the year. And what do we want that to be a match on? Well, go back to our budget overview, and if it matches 2020 over here, for example, we want that return, and we'll see the same value as when we had our previous sum, and we can just use Control Z to undo to go back to our old formula here and control y to redo. So again, very useful to just undo redo any changes you make like that just to make sure everything's working as you expect. Now, I'll leave you to go ahead and enter the equivalent formulas into all of our cells over here so that we have SIFS based on this cell across the board within our budget overview tab. The addition of the year and the associated SIFS will be our final change to this spreadsheet and to this checkpoint series on building a budget. Let's do a quick recap on everything that we've learned in building this spreadsheet up and incorporating all of the different tools and functionalities you've been learning throughout the course. What we have here then is a spreadsheet that shows us our annual profit or loss, income, or expenses, with a useful chart, slicer and timeline on the front sheet, with a year that we can change and hair in order to get different values being pulled in based on logical formulas. This entire spreadsheet data over here is built off our monthly budget, which has more granular data. So we can see by month, our opening balances, our balance movement since the start, our estimated income expenses, as well as our actual income expenses and profit and loss respectively. And within our expenses, again, we are using logical formulas to build off our expenses detail tab over here. And the idea here is that you can add in the required categories or descriptions that you want, which themselves are controlled within the supplementary data tab in terms of data validation. So we can choose which categories we want allowed, as well as descriptions. And we can also choose default estimated costs for these so that those pull through overhre, but can still be overridden, and when they are overridden, they're shown clearly with a different background color for our cell. You've used conditional formatting in order to achieve that different background cell coloring and similarly over here, using data bars to show an actual cost overview so we can see our highest actual costs at a glance. Then we've also included a monthly budget chart that is based on our monthly budget tab data over here. And similarly, on our budget overview, we've added in a pivot chart. Now, a pivot chart always needs an associated pivot table, which in this case, we've created on our supplementary data tab simply because for visuals, we don't want to see it. Let's say on the front, so we've left that there, but we are able to separately show our pivot chart, the timeline and the slicer here on this tab. Now, think about how much you've used in the creation of this spreadsheet. So we started off with basic data entry, basic formulas, and moved on to using formatting, conditional formatting, graphs, Vps, logical formulas, pivot tables, and pivot charts. Now, during the creation of this, I've purposely left the data quite straightforward and low in volume and complexity. So we don't have loads of months or years running along here on our monthly budget, nor do we have a great deal of categories or descriptions. But the idea here is that you can easily expand this out with those additional items and make this into an actual budget that is very usable. So the skeleton of the spreadsheet is there, and the beauty of this entire thing is you've built this up from scratch. So you know exactly how the model in effect works. You know how every single cell relates to another one, which spreadsheet relies on which. And that's the best thing about building up a spreadsheet by yourself. You really understand how it all fits together. So for some homework, why not think about how you can expand and use this spreadsheet. What more can you do with it? So, for example, we've got some Is here for our actual expenses, and we've got a great deal of breakdown on our expenses here. So we've built up an expenses summary, expenses detail and restricted certain categories and descriptions. But if you look at our income at this point, it's just a single income that's typed in for every month when we get the actuals. But, you know, depending on what you're building, you might have that income needing to be split out and shown in a similar way to expenses. So think about if you can go ahead and incorporate something like that to challenge yourself. The other area is, of course, presentation. And again, I purposely don't spend too long on presentation, but of course, you can go ahead and neaten this up and make this look really good. Explore all the different options you have within the formatting options available in Excel. So that's something else that you can look at to really make this spreadsheet your own. With that, then, we'll bring this video to an end and we'll wrap up this checkpoint series on building a budget as well. I hope you enjoyed and found useful the entire checkpoint series. I know I had a lot of fun making it as well. And we've been able to build from absolute scratch a spreadsheet that incorporates all of the lessons we've been learning throughout the course. And it's something that's actually practical and useful, and best of all, you did it from start to finish. As always then, thanks for watching, and I'll see you in the next one. 27. Exercise - Reconciliation: For this video, we're going to run through a reconciliation exercise. Be sure to grab the Excel file attached to this lecture so that you can follow along. Now, a common exercise that Excel is very useful for is comparing two or more different sets of data. To check what is in one data set or in both datasets, and we're going to look at a couple of techniques for how we can go about this. So let's just analyze what we've got in front of us. So we've got some IDs, some transactional numbers, dates, quantities, and product names, and then we've got the same heaters here again for a different dataset. So in this example, what we want to be able to do is check which of the transaction numbers and data are the same between these datasets and which ones differ so that we can hopefully highlight those in order to deal with them as might be appropriate in any given scenario. In our scenario here, then, let's consider that the dataset here on the left is our main dataset, and we've received a separate file of data here on the right, and we want to compare and contrast these. So the first thing here is, let's consider that we have unique transactional numbers within this data. So every single sale, let's say that's happening is happening on a unique transaction number. So how do we check which items are in one or both datasets? Now, before I dive straight into this, it's a really good idea to actually try and pause this video, and if you've understood the requirement for any aspect of it, try and get ahead yourself. And then you can use the video to check back against what you've done. It's a great way to challenge yourself and to make sure that you're actually embedding everything that you're learning. So our first requirement is here. We need to use a V lookup on a unique feel in our data, in this case, transaction number, to check that the data is in both or either data set in order to flag any inconsistencies. So here's how we can do this. Put a title in here say is the data in set two, one on the right. I'm just going to do a V look up now on the transaction number. For our table array, we can just take the entire column here, and we can just return that first column, and we'll do a false, so an exact match with zero. And I'm just going to copy that down with control D there. So immediately, we can see that we do have 20 matches there, and we can see the NAs, where we weren't able to find anything. So those are the differences between the sets. Now, with a reconciliation like this, you want to go both ways. On the data set on the right here, we want to say is data in set one, and we want to repeat the same look here. So we look up on the transaction number here, and I want to compare it with column B here. Return that first column again as an exact match. So I would expect to have 20 matches to go along with these 20, and then I've got five that are mismatched. So we can see immediately between the two datasets where we do have some mismatches. So let's see what we can do with those next. In this case, then I've already said that we're treating our dataset here on the left as though it's a master data set. So here we can see, for example, we've got some transactional numbers that start before the first number here. So that's fine. That's just saying, for example, that our master data set has more data than our data set here. So for example, we could say we're not concerned about these. We're happy the fact that they're not in the second dataset. Then we have transaction numbers that match for the next 20. Then after that, we have five items here that are not here. So let's just take a look at these again. We've got some items here from transaction numbers ending in seven through 781. You can see here this one goes up to the 776, and then it doesn't have these five. Again, in this example, I could say, for example, we were expecting that because our Master Data set has more However, we do have five other items here that are not in the first dataset. So these are going 782, 783, and so on in their transaction number endings. So I could, for example, take these and enter them over here to add my dataset. So let's just do that. I'm going to add five rows there using control shift and plus to add the rows, and then I'm going to grab this over here, and I'm going to enter those over there. I'm just going to make sure that I've got my IDs running along here. So the IDs are different between these two datasets. You can also see that my V look up here has changed to reflect the fact I've added in the additional data. And that's happened because we've specified the entire column, which means we don't have to mess around with ranges if I had instead covered. Let's say original data set there, we would have had NAs in here. So just to show you how that would have worked. For example, if I had just gone to our 30 items there, and let's just fix these as well. So our references don't go a when I copy this down. So when I copy that down with control, you can see our additional ones aren't covered by that range. So that's yet another example of why it's very useful to cover entire columns where possible. So let's just copy this formula down here on Dataset one as well, just for completeness, and of course, we're going to see matches there. So the point of this is on the datasets here are very small, and we can eyeball these and see them, the techniques you're seeing are important because when you have data sets that run for thousands or even tens of thousands of rows, the only effective way is to use formulas. And what you would do, for example, here on the NA is, we could filter on these, and we could select just our NAs. And we could output them somewhere, for example, if we needed to send them to a colleague to say, look up for differences between this data set. Here's the items. Can you let me know what needs to be done? So you basically have your data flagged up effectively to deal with as a next step as required. Okay. So here we're working on the assumption that transaction numbers a need to check between the two different datasets. But what if there was something incorrect between the two different datasets on the actual data? So this is where the next task comes into play. So we've got creating a URN or unique reference number using concatenation and tells you how to use it there using the Appersand, and we're to use it on cells to confirm that the values are the same between our datasets and use a V look up to confirm. So let's see what we mean by that and how we can achieve that. So let's create a URN for our first set here. And what we're going to do here is then we're just going to go to our first cell, use the Ampersand, and we're just going to do that for every single cell here, apart from obviously our V lookup that's here. So just our original dataset. And you can see what we get here is just a combination of every single one of those cells. So you've got your transaction number right there ending in 752. Then you'll notice we've got the date in the numeric sequential format that Excel uses. You've got the quantity of 16 and you've got the product height there. So let's just copy this down, and we're going to have to do the same thing over here for our second set. So I'm going to copy this for Sep two, and let's copy that formula, and that copies across our references as required and let's copy that down. Again, we want to do a V look up once again, so let's do is the data in step two. This time we're going to do a V lookup on our new unique reference number. We're going to compare to the unique reference number in set two, and we're just going to use column one again with an exact match. So we'll actually get the same set of items returned, but the additional useful information here, we're actually confirming that everything on this data matches. So all of these columns here of data are matching between the datasets, and let's just do the same thing over here. So I do a look up here against the URN here. And copy that down. I'm expecting matches, so that's fine. So why is this useful then? Well, for example, let's say over here on this transaction number 757. What if that quantity wasn't 35? What if this second dataset had 20 in there? Well, you can see with our original lookup method here, where we're just looking at the transaction number, we're saying, yes, we've got the same transaction number in both datasets. But when we go one step further and we combine all of the other data items here, we can see suddenly we've got an NA here. So again, we could flag that up. We could put in different messages or put a comment here to say that the quantity, for example, doesn't match. It's a really useful way to compare the actual items within datasets. Now, let's turn to the URN usage worksheet and take a look at another example of how useful URN can be. In this scenario, then, we've got our dataset here on the left, and we've got some missing transaction numbers. Now, you might just be able to look at that and say, well, if we just did plus one here, we can just get our missing numbers here. But let's say we've been informed that the missing numbers aren't in a sequence. There's been some glitch in the system, and we're going to receive a separate file that we have to enter those transaction numbers from. Let's we've received that data now, which is here on the right. So how can we use URNs in order to bring in the correct transaction numbers here? Let's start then by setting up our URN again. We'll have URN for our set one here. This time, we're just going to use date quantity and product. We can't use transaction number because obviously we're missing some of these. I'm just going to do this and copy the same for our second data set. Just make sure those references of copied across and I have. Let's just do that chin to set two. Then we're going to do a look up once again. Let's check to set two here. And I'm going to look up my data here. Now, what is it that I want to grab here? Though I want to get my transaction numbers here. So we could use something like indexes with match, but instead just temporarily, why don't we put an equal sign here? Bring our transaction number to the right for data here? Because what that means is I can then do a V look up On our URN here. Pick my two columns here. So it's going to look for URN in the first column here as always as per V lookup, and I'm going to bring in the second column in terms of my column index number, and I'm going to choose zero for an exact match. Let's just copy that down. Again, this is our so called master data set, so we know we've got some extra data here that the new data set doesn't, so we're fine with that and equally, we've got some data afterwards that this set doesn't cover. But if we take a look at this now, we've got our new numbers here, so we've been able to pull in that our actual correct transaction numbers are here, so we can just copy those, and when you're pasting these, just be careful to paste these values because you don't just want to grab the formula, so you right click and paste values to put those in. Now, we've used the concatenation to create a URN here, because, for example, if you look at a couple of these transactions here, we've got the same date here, we've even got the same product. The only difference is the quantity. So working on the assumption that when we have three unique items, that is in fact a unique transaction, we've been able to use the concatenation to make sure that we're picking up what we need to. So if I had just relied on date or just product, obviously, I wouldn't be able to get the correct transaction numbers only when I rely on all of those pieces of information that I can be certain that I'm pulling in the correct data. To see this very clearly, let's consider changing our formula here for our URN, and let's drop the quantity in there, so we're only going to combine date and product. Let's copy that formula down to cover all of our cells. And then let's copy this formula for our set to URN as well. So again, just date and product, and let's copy this down. And you can see as soon as I do that, the V lookup is returning incorrect information. So we'd already said the first five items weren't in the second dataset. But now when we're comparing date combined with the product here, you can see we actually come across that right here. And so we're returning this transaction number here 384789 incorrectly. So it's very important to match up on all of your required data items to make sure that you get the correct information in. So let's just undo that to get our formula back. Now, remember from our previous example that using URNs in this way also means that we can spot any changes in data. So if we had a difference in quantity hair, for example, we would end up within NA hair, and you can start flagging these up either by coloring them or putting a comment in a separate field in order to track those, and then you can build up a list to deal with afterwards once you're done. To wrap up then, we've seen the power of V lookups once again. We've seen how they can be used in reconciliation to us, and we've also seen utilizing URNs and combining those with V lookups to really make sure that we're picking up all the information between different datasets and reconciling properly. So as always, thank you for listening, and I'll see you in the next one. 28. Exercise - Effective Data Set Up: In this exercise, we're going to look at effective data setup in Excel and why it's so important in order for your spreadsheet to behave in the way that you would expect them to. So let's start with our data here, and the first task here is, what is the problem with the data being set up in this manner for our various analysis tools within Excel. So see if you can have a go at looking at this yourself and working out what some of the problems might be. Hopefully spotted a few problems with this data set already. Let's look at it together then. So what have we got in front of us? We've got some months running along here, and underneath those months, we've got some salary related data right here, and we've also got totals for each column. Now, the first problem that we'll see here is if we were to try and filter this. I go to data and filter and let's just try and bring in one of our filters over here. Let's bring in let's say employee pension, and we just choose 40 there. You can see that's not picking up our other options down here. So we're only filtering on the first data set when we know for a fact that we've got 80s in here, for example. So you can see there's no filters being applied over here, and it's always easy to spot because your numbers will go blue over here if you're actually filtering on that data. Now, we can temporarily try and get around that by actually selecting our data set like this and then applying the filter again. And this time, for example, when I pick the 80, I will be able to see just those items that I want. But again, this is not ideal because you can see we're actually bringing in the subheading here for employee pension as well, as well as blank lines that we have in between. Not to mention the fact that because we selected it in this way, anytime we add additional data, we'll have to make sure that we reselect the new data set and then unapply our filter and reapply it again. So just to backtrack a second here, the context is that we have some data that's related to salary information, and we're either being provided for some analysis, or we have to maintain this month on month, which is why I talk about adding in additional months down here, and you can see the immediate problems that we have when it comes to filters, for example. So that's one problem there. Now, let's look at our totals down here as well. What you'll see here is if we click or press F two, We can see how the sums are working. It's actually been chosen to sum each of the individual subsections for each month here. So it means we're again going to suffer from the same problem where when we add in another month in here, we're going to have to add in an extra sum in here with its own range to cover that off and then copy that formula across. So that's not ideal. We'd want a much more efficient solution to that. And then we also have the fact that if we were to try and pivot this table, if we were to select the entire dataset as we've seen before and go to insert pivot table, and let's just put it on our existing worksheet so we can easily see the data. You can see here. The problem is we don't have the ability to split by month. So we know we've got data for different months and ideally you would want to be able to see the amount, so the sum, let's say, of gross per employee, but you'd also want to be able to see it by every month, and we don't have that option here. That's purely because of the way our headings have been set up. So we've got a few problems with the way this data is currently set up. So let's talk about what we can do to fix that. Now, as always, try and give this a go yourself, but I'm going to go ahead and show you one way of getting around this problem. So what we can do here is add in an additional column to the start, so I'm going to use row shift and plus to add that column in, and then I'm going to put a new header here for month. Then what I'm going to do is copy my month here next to each of the employees. I'm going to repeat that for every single month that we have here. Now, I also want to get rid of our additional headings. I don't need the separate months, and I don't need the blank lines nor these extra headings because we already cut them at the top here. I'm just going to start selecting these. I'm using control now, holding control and track selecting, so I can pick up multiple selections like this. Now that I've got everything ready that I want to get rid of all of the lines in between and the extra one here, for example, Is going to use control and minus, or you could always go right click and delete to get rid of those roles. So now I have my months running along here for every single employee, and then I'm also going to fix my totals here. So rather than summing up those subsections, I can just use t equals now. And this will sum up all the way to the end, and it will include this this row over here as well, which is important because we've seen before when we add additional rows, it pulls this down as well, so we don't have to keep pulling that range. I'm going to put that formula in there and then I'm going to use control shift right and use control in R, and that will put the formula copied across with relative references into every single cell here. With our data set up like this then, let's try and repeat those actions we tried earlier. So first of all, let's take away our filter and reapply it with the new data format, and let's again look for 80 here in employee pension. And this time we can see it working as we expect, which is great. We've already confirmed that our totals are running in a much nicer way here. And let's now try our pivot table as well. So select the entire set of rows, insert pivot table, and let's just put this here once again. Now, this time, as well as being able to bring in the name and the gross that we had last time, we've also got the option for month. So we can now start to split this data and see individual employee numbers per month. To summarize, then, it's really important to have well structured data in Excel. And generally what this means is having your headings along the top row here, having your data running beneath it, and not having blank rows or subheadings in between and keeping your totals with formulas covering the entire range that you can add in additional rows. So when I add in an additional set here, you can see my totals will run to cover that if I had an additional month of May in here. I could just add that in and know that my totals will work as required. Of course, I'd have to change those dates and have the correct data set. So, we'll wrap this with you up there then. As always, thank you for listening, and I'll see you in the next one. 29. Exercise - Reference Functions: Hello. In this exercise, we're going to look at reference functions, a couple of different options that you have when it comes to problem solving, and how you might go about picking which method to pursue. So to start with, let's look at our data set first. So what we have here in our first column is a series of dates. Then we have some headers here for different currencies. So we've got great British pounds, Swedish Krona, Singapore dollars, Euros, Malaysian ringits, Polsh slotti and US dollars. And what we have here is a base currency here, so the GBP. And then we've got the exchange rate that that converts to on a given date. So for example, on 1 May here, we're saying that one GBP was equivalent to $1.255. Now, the focus of the exercise is, if we have certain currencies being entered, so a base currency, a conversion currency, and a particular date, are you able to formulate a function or formula within Cell M two here that would pick these values up and bring in the relevant exchange rate? And as always, there's a couple of different options of how you might do this. So with that being the primary task, go ahead and pose the video and have a thing and give it to go yourself? Okay, so to start on a solution, the first step is to break the problem down and think it through. So what do I want my formula here to be able to do? Well, given a base currency in here, I want to find that currency's value, and I want to find it for a particular date. And then given a conversion currency, I again want to find the equivalent cell for that date. And then combine those to give me my value. Now, it's easy when your base currency here is just the ones running along here. For example, we know we can just pick up this value. But if we add something else in here such as Singapore dollars converting to US dollars, we need to consider some more arithmetic hair in order to get the equivalent value. So let's just think through that first. Okay, let's say that we've got our base currency as Singapore dollars, for example. Now, we can work this out using cross currencies here. Even though we don't have Singapore dollars to US dollars here directly, we can convert from US dollars back in terms of GBP and the same for Singapore dollars back in terms of GBP, and then just take one rate over the other to work out how these two rates relate to each other. So, as always, let's just see that with an example to make it easy. So one Singapore dollar to GBP, So if we know that one GBP gives us 1.7 Singapore dollars, we can just do one over that rate on the given date, so 1/1 0.76 33, and that's what one Singapore dollars will give us in terms of GBP on that date. We can do the same for US dollars to GBP. So I'll just do one divided by our rate here. And now what we want to do is do a cross rate. So we want to get one Singapore dollar to US dollars, and that will simply be our base currency here divided by our converted currency here. So we can see that one Singapore dollar would be equivalent to $0.709. Now, I've broken that down just for understanding, but we can actually simplify this even further. So we don't have to do one over each currency and then divide it. We can simply take two rates here and divide them by each other. So take the example of GBP to US dollars. So we know that simply finding the US dollar rate on that given date and then dividing it by one, we'll always get obviously whatever values in here, but the same logic applies for any cross rate. So in order to get this value here, I could have simply done the US dollar rate here and divided it by the Singapore dollar rate here. And you'll see I get the exact same value here. So we know what we can do in terms of working our formula out. So let's consider that next. Now we come to the real focus of the exercise, which is, how can we come up with a formula here in cell M two, which will give us the exchange rate given any currency pair and a particular date that can be entered. So what we want the formula to be able to do is work its way down the first column here. Find the date that matches what we've given and lock in that row. Then what we wanted to do is to find a conversion currency value here and find the equivalent cell. And then we wanted to do the same thing for our base currency here and divide one by the other to give us our value in here. So how can we do that using reference functions? So let's start typing our formula in here to get an understanding. It's always best to start experimenting to really get a feel for any formula. So we'll start with a V lookup, and we're just going to test if we were going to look for this date first of fall because we know we want to find a particular date. So I'm going to use a V look up against that date. I'm going to find it within my table array here. Now, I could choose my table array like this. But then I know I'll have to extend it if more dates are added in here, like so. So instead, I'm just going to delete this and pick my entire sets of columns here, which will cover any extra data that's added in. And again, remember, we look up looks up the value within the first column of any given array. So that's fine. I know my dates are in here. So we're fine with that. So once Excel works its way down and finds a date, we then want to tell it where to find the particular column. So in this case, I've got my conversion currency. So I know I want column eight, in this case, H is column eight, but that's going to change. So I can't simply hard code in in eight because anytime something new typed in here, this formula won't be accurate, it will have to be changed manually. So we need some other method here. But let's just go ahead with this for now and just make sure that everything else works in terms of what we've worked through our arithmetic over here. So I'm going to bring in column eight here. I'm going to do an exact match. So I'm going to look for an exact match on the date here within that array. I'm going to close that off. And let's just see what that brings us. So that, of course, is finding the date, bringing in the eighth column here to bring in the US dollar value. And what I'll want to do then is divide that as we said by our base currency here. So I'm going to do another V look up. And again, I'm going to look for that date. And again, the same sets of columns here. This time, Singapore dollars is in column four of the array of just pick heres 8-8, so one, two, 34. So I'll just hard code it in for now and we'll do an exact match on the date again. So we can see we've got our value here, albeit with some hard coding in here, which we'll deal with next. Now, what we want to do next is come up with a method to remove the hard coating of our column indexes here. And we want Excel to be able to find these currencies here and bring in the relevant column number. Now, one way we can do this is to add in an additional row in here, and I'm just going to change that to an orange background, just make sure that my font color is black as well. And I'm just going to give my columns numbers, so I can just do the first two or three and then I can use the fill handle and drag this to let Excel fill in the pattern. Now we have column numbers corresponding to each of our columns, and that means that we have a method to be able to pull these in. So rather than coating it straight into here, let's just do this in a separate cell here to see it easier. We'll start with a H look this time, and the value I want to find, for example, is US dollar, and where am I going to find that? Well, it's within these cells here. Now again, rather than just selecting these cells, let's build in some flexibility here and select the entire row so that if any new currency columns are added, we'll be able to take care of that without having to drag this. So we know we're going to find that within the row there, and let's just drag down our table array. So we're looking for what's in k to the US dollar currency here, and we're looking at the table array from rows two to three. So it's going to look for that value in the top row there, and then what we want for our third parameter is which of those rows we want to return? Well, we want whatever is in the second row here. So once it moves along and it hits US dollars, it's going to move down and give us the value just below. So we're going to put a two into there. And how do we want to match our currency, we'll do an exact match again, so we'll put a zero for exact match. So if we do that, we can see we get the column number corresponding to the USD currency. Okay, so at this point, what we could do is, we could copy this and we could go in here and rather than putting the eight, we could just put that formula in and equivalently here rather than hard coding of four, we could put the same formula in, but then just make sure that rather than looking at K two, we want to look at J two for our base currency, so we can just delete that and put in two. Everything else should be fine. So that should be the same value, and we can just use undo and redo very quickly if you want to just go back and forth between the hard coding and the new formula and make sure that value hasn't moved whatsoever. So controls add to undo control y to redo, just to very quickly go back and forth and make sure it looks right. So that works, and that's fine. But what happens if someone adds in a new currency here? For example, if there's a new column added in here and there's a new currency such as the DKK, I can see as soon as I added that column, exchange it's no longer working because it's finding the column here for US dollar and returning eight, but actually it's returning the wrong currency here. So what is it actually doing? It's going along? It's hitting US dollars and it's returning number eight. But actually, in our array, Soon as we added the column, our array now runs through to nine columns here, and the eighth one now is PLN, and we can just confirm that manually. So if I was actually to do this divided by our Singapore dollar, you see that's the value that we're returning now. So we haven't quite built in what we wanted to do here. What we could do is obviously manually go back and then change all of these, so we can put a four or five in here, six, and so on. But a better way of doing this is rather than hard cote each of these, just put an equals, grab our first cell here and do a plus one. And then we can just copy this formula across to all of our cells, and that will work. Anytime that a new currency is added, there's still some manual work here. You just have to copy the formula from this cell here and copy it all the way across. Because it's going plus one, it's always just going to add in the next cell here, but it's easier to do so than having to type in each value manually. So we have a method now where we can effectively look up based on these numbers here, and we don't have any hard coded values. We will have to make sure that we put some instructions in, though to say that if there are any new currencies ever added to the table, that this is what needs to be done. You have to make sure you copy this cell over here across so that we have the correct formula here. So it works, but it's maybe not the most efficient way. So let's consider another way of how we can do this. So let's set up our criteria and our formula here again. I'm just going to copy these cells here. I'm going to base them here, I'm just going to remove this one here, and I'm going to come up with another method here. And in fact, let's just link our cells here to our currencies up here, so we only have to change it in one place. I'm just going to put equals to our cell here and here and finally here. And we don't need this H lick up here that's separate, but we just leave that there for now. And what you can try here then is changing these currencies to make sure things are working as you expect as well. So maybe try a few different combinations. So we know GBP US dollars is probably the easiest one to keep our base currency as a one, so you can just look at the table, and let's just try, for example, PLN in here and maybe a different date as well. So we can start to see that it does work in terms of what we've set up already. We're getting the equivalent values. We've got a method. It's still got some manual work that would need to be done in order for it to be really robust. So let's consider another method here. And what we'll do here is we'll look at index and match. Let's start then by typing in equals index and opening our brackets here. And the first parameter here is our array. Now I could pick our table array here like this. But again, we've discussed the benefits of selecting the entire set of columns here so that I can build in for any additional data that's added in. So I'm going to select my columns 80 j. We press com to move to our next parameter, which is our row number. Now, for our row, we'll want it to be dependent on the date that we've picked, so we want Excel to work its way down this column here, find the date that we've picked and then lock in on that row before we do anything else. So what I'll want to use here is match, so typing in match, opening the bracket, and now we're working within the match function here, so we're looking at its parameters. So the look of value that we want here is our date in N 14. Now, if you can't quite click on your cell here, just click on the cell above and press the down ok, or you can type it in directly. And then let's put that comma. Now, where do we want to find that value? So I could select my cells here like this. But again, I want to be matching off my array, so it makes sense to select the entire column. The most important thing here though is that you start on the same initial cell as your index array here because the relative dimensions have to be the same. So if I start in my look up array here, That wouldn't match up with where I'm starting my index because index is starting here on A, which implicitly is starting on the very first row, so I wouldn't have a matching dimension here. So I'll just pick my entire column over here. So I know I'm going to find my date within here. Now, my look up type here is going to be an exact match, so I'm just going to enter a zero for that. Close my brackets. And now we're back in the index function here. So we've completed what we want for our number. So let's put a comma here. Now we want our column number. Similarly, we're going to use match again. This time, our lookup value will be our conversion currency. I'm going to select that. In terms of where we're going to find that. We know it's within this row here. Once again, you could select from cells B onwards like so, but that wouldn't be lined with our array over here, which again starts on A so starting back here. So you'll want at minimum to start like so. But in fact, it makes sense once again to select the entire row. Again, you can build in for any additional pieces of data that go in later. Again, we'll want an exact match here. So at this point, let's just close off our index function here. So we've got our array. We've got a function to return our row number, and then we've got another function to return our column number. So let's close that bracket and analyze this. Now, what this formula is doing here is within our index function, we're looking at our array over here, columns eight to, and we want to specify a particular position. So we want to give a row number and a column number because if you have both, you essentially have one singular cell. So for our row number, we've given Excel a match function, and we've said, we want to find N 14 the date here, and we want to find it within our column here, Column A. So it's going to start over here at position one, two, and so on, which conveniently is our numbering over here. So it's going to work its way down and it's going to find the value here 2205, and it's going to see that that's position 25, so it's going to store that as our row number. It's going to then move to the next parameter over here, which is our column number. And we've asked Excel to look for PLN. And we've said, we expect to find that within row two through to two. So just a single row. So Excel is going to work from left to right, and it's going to look if it can find PLN in this cell, and if it can't, it's going to move the next and so on until it eventually finds it here. And again, that's based on position, so that's position number nine here, so one, two, three, and so on. And it's going to return that. So we're going to have our row number 25 and our column number nine here, being returned to our index function over here to give that value right there. Now, you can always strip these functions out here just to see how they're working. So you could copy the match function here, for example, and just put an equals and paste that function into there. And you'll see it's returning those values that we've just discussed. It's sometimes useful to be able to do this just so you understand how the functions working in the background. So you can see row 25 and column number nine are being returned with those two sub functions feeding into here. Now, let's change a couple of currencies here again just to make sure that this is working. So let's go back to US dollars over here. And we can see that we should be getting the same rates being filled in over here. And let's try our different currency in here as well. So let's go back to Singapore dollar and US dollar as we had before. And let's use the date that we use over here. Just because we'd filled that in earlier to see what we can get. Now, we can see here that our function over here isn't quite right because we've missed a step here. So over here, if you remember, we're dividing by our base currency. We haven't done that here. We were just working with GBP in here, so, it was the same as dividing by one, which is the same as not feeding anything in. But we do have to take into account if there's a different base currency than GBP. So what we can do here is very simply divide and we can just copy our entire formula here. Base that into here. We just have to change a couple of things here. So we're fine with the date, we do still want to pick what's in N 14. We're fine with our array and everything else. The only thing here is for our currency over here, we don't want M 14. You want L 14 instead, so you can just do a backspace on that, put in L 14, press enter, and now we've got the same results being entered as our VU up here. Now, as before, let's consider that there was a new currency added in again. So let me just add in another column here. So now you can see our index and match function. Actually doesn't need anything more. It's working fine. It's still kept that correct value, whereas over here, we've now got an incorrect value, so we'll have to remember to copy and paste all of our column numbers to be updated because now we've got an extra column, so it's actually moved the position, so US dollar becomes column 11 rather than ten. So there's extra manual work required in keeping this method running, whereas index and match you can see is very robust. We don't even need this row here whatsoever, and everything works as expected. So just to neaten this up, if you were to go with the V lookup and Hup method, you would want some instructions here to say, if you're adding any columns to your data, you'll have to copy the formulas in cell B two across all the orange cells to provide each column with its column number. And rolling that across each time. Whereas if you're using index and match, then this row of column numbers can actually be safely deleted. So if it doesn't look particularly neat, for example, we could just get rid of this as well, and we could just use this method. So you can really see the power of index and match over V lockups and H lockups here. Now, an extra little task here, can we set up data validation for acceptable values in our cells over here? Now I put j 2k2l2 at the time of producing this, but obviously, I've added some column since, so those are moved, but what I mean is these cells here. And we can do. So what we can do is click here, go to data and data validation, click on data validation again, and choose our validation criteria as a list. Click on source here, and then we're going to supply Excel the allowable value. So I'm just going to select these cells here and press okay. Then we can only choose those values here. Now obviously, I've missed a couple of values in here, but obviously you would only be allowed to choose anything else if you tried to type in a number or anything else. Excel wouldn't allow it. We can do the same thing here for our conversion currency. List, source, and, drag and release. Then finally, we have our date. For this, we can again go to date of validation and choose a date from here, and we can allow our date to be between certain dates or we can say greater than, for example, and choose a start date. So let's say we knew our start date was always this, and any additional dates are going to come afterwards, so we could press okay on that. Then again, anything that isn't a date, or if there's anything that's earlier wouldn't be allowed. Anything later on would be fine. To summarize this video then, we've seen how we can build in dynamic parameters into our V cups and H lookups, albeit with a bit of manual intervention, but you can avoid explicit hard coding of values. Then we've seen how powerful index and match are, where we don't even have to have any manual separate process in order for our reference function to work how we want. So to be really clear, we could get rid of this row here entirely. And our match in index function will still work, but our real lookups here with that method will not. So what you have here are really powerful methods that you can use for a wide variety of tasks. What you've seen here can easily be applied so that you can be supplied with different criteria and you can build in formulas and functions to provide expected results. I hope this exercise really helped to further demystify reference functions and show you potential uses, and you've gone through an exercise where you've had the opportunity to see how to really implement these. So, as always, thank you for listening, and I'll see you in the next one. 30. Project Introduction - Data Dashboard: Welcome to the Project Data Dashboard task. This assignment has been designed to bring together multiple elements of topics covered in this course and to provide you an opportunity to tackle an interesting task yourself. Now, to get started, make sure you download the Excel file attached below this video and open it up. Hopefully, you've done that and are ready to go. Let's cover the setup and requirements together. You have in front of you a set of data, and your task is to put together an insightful data dashboard that presents key pieces of data in a manageable and digestible form. Now, you can go your way entirely with this and see what stats or key performance indicators you want to show and how you might go about it. You can make it totally your own. The other option is to meet the task requirements you see here. Now, I'm going to leave you to read through these and tackle them yourself in your own way. If you do feel completely stuck, there are hints available here. You can click on each cell next to the task and see the hint on a method of resolution for each task, or you can change the font colors for the cells that you want to see. I've put together a video showcasing a solution. However, really give this your best try. Don't give up and use those hints if you have to. Make sure you do as much as you can before viewing that next video. With that said, then, thank you for listening. I'll wish you good luck with this challenge and see you in the next video when you're done. 31. Project Solution - Data Dashboard: Welcome to the data dashboard walk through. Hopefully, you manage the challenge yourself successfully. Now we're going to cover the tasks together. It's important to note here that there are multiple ways of resolving some of these tasks. So if you went with a slightly different route to what we cover here, that's absolutely fine. In fact, that's even better because you've used a method that works for you and that you understand. So let's launch straight into this then. So the first thing I'm going to do here is just take a look at my data. So what I got I've got 40 different items here. I've got IDs, I've got sales dates, sales agent names. I've got some regions in here, item names, quantities, a cost price, and a sales price. Okay? And when I look at that first task here, I can see it's saying that managers losing sight of the headers that they scroll through the data. So clearly we're putting something together that's going to be disseminated to managers, and maybe they don't know the little tricks within Excel, so they receive this and they're scrolling down, and they can't see those headers. So a nice easy start for this then. We know how to do this. We're just going to go into view, and then we're going to freeze panes. So I'm going to freeze it there so that I can freeze my top row and my first column. So I'm just going to go there and freeze panes. You could just do the top row as well, but I like to freeze the first column as well. So if I scroll right, I can still see that. The next item then is calculating total cost, total sales and margin for each row and showing any negatives in red. So before we go any further, let's also bring in our hints. So we can see those. For this, we can simply add in three additional columns to work these out. So I'm just going to add those there with insert, and I want total cost. Total sales and margin. Nice and straightforward here. We know, we've got the quantity and we've got the unit cost price, so we can just multiply those two together, and we can do something similar for sales. So quantity times the sales price. Then the margin is simply going to be our sales minus our total cost. Then we can copy this down, so I'm just going to use control down to jump to the end. Use the right arrow key to go one space to the right. And now I'm using shift and the right arrow key to select those cells, and still holding shift. I'm now holding control and pressing up. That highlights everything. And because these three cells here are at the top of my selection, I can just use control and D, and it will copy the formulas down into each of these cells. Okay. Great. Now, we also to show any negatives in red. Let's do this. Let's cover the entire columns here when we do this, so we can just highlight these and we can change the formatting here. And we can change this to a number. We'll just go with this. There we go. We can see any negatives in red. Task number three then is to produce a table showing total cost, sales and margin per region. And if we take a look at the hint, we can use SIF to create the dashboard item. So this is where we're going to actually start creating our dashboard. Let's create a new worksheet here, and let's just set up our headers. So we're going to have region, and we're also going to have total cost, total sales and margin. And for our regions, what do we have? We just have north south east and west, so we can just enters. Let's just make sure these columns fit. Now, let's see how we can use some if to bring in these items. Let's start with total cost. We can do a SI. Now we want to pick our range that we're going to be looking at a criteria over. So we'll want column D here and we can cover the entire column. And our criteria here, going back to this sheet is going to be this cell here. So we're going to look in Column D on the worksheet, and we're going to see if it matches th, and if it does, we're going to pick another column to actually sum over. We it finds North, we want to bring in anything in total cost. Every time it hits north, picks up that cost, finds the next one, adds that in and it keeps going. Let's just close that off. And we can just copy this down. So make sure our formula is fine. That's not going to move because that's a fixed column and we're going to be copying this down, and we do want this moving down, that's fine. We can just control D, for example, to copy that down, and that looks like it's working, and let's do the same thing for total sales. So if very similarly, we're going to do the same thing here, the region. We're going to use our su range as sales this time. I was going to copy that down. On margin, we don't have to use a su if we could, but we can just go total sales minus total costs here. And maybe it's not a bad idea to have a total in here as well, so we can just use all equals to bring in the sum for each of these. Not a barter you to just check these numbers are actually accurate, there are formula is working. So if we're saying the total cost here is 4537, let's just take a look. You can see 4537 down here on the status bar and similarly for sales, just using control space to highlight the dire column 129-61-2926, that's fine. Great. Now, we can obviously make this look a lot better as well. So put some bolt o wers heads, for example, just play with a formatting a little bit. So that makes it look just that little bit better. Okay. Now, I'm going to be leaving things with minimal formatting here. We can always cover that off at the end, but it's important that we get the functional requirements correct. So let's go back and let's look at the next one. So we have to produce a one item list showing total sales for an agent where that agent can be typed in or selected. What's our hint here? Remove duplicates to find the names. Store those away somewhere, either in hidden columns or a separate worksheet. Use data validation to construct a list and some if to obtain the total sales for the agent. Let's do this one step at a time. The first thing is finding those names. So there aren't too many, we could just pick them out and type them in, but let's just use the remove duplicates method here. We're just going to copy this, and let's just pace it over here. We'll go to data and remove duplicates, like so. We've got our five names there. Now let's set up a little section where we can pick that agent. So we could have agent name and total sales. And for our agent name, we're going to want to pick this from a list. So let's use some data validation in here. And I'm going to pick list in here, and my source will be those names there. I'm going to press. Now we can pick only those names and want to bring in total sales, so we can use some if again. We can look over the agent name on column C. Look for what's in cell B 12 here, and then we can bring in total sales once again over here on column J. Now we have a pickable list here that will bring in different sales per agent. Let's leave any formatting on that for now then and move to the next item. Next, we want to highlight any quantity over ten in the data over here. So let's look at our hint. Conditional formatting is the way we can achieve this. Let's look at our quantity column here on column F, highlight the entire column, and we're going to go to home and conditional formatting, and we want to highlight cells, and we're going to pick anything greater than ten. Let's put those in green so that we can easily see where we've had quantities greater than ten sold. So that one is nice and simple. The next one that we have is replacing any blanks in the quantity with five. As you looked down through this data, you'll see we do actually have some missing pieces of data and for whatever reason, we want to replace those with a quantity of five. How can we do it? A couple of methods here? We could use the GT and find and replace, or we can filter and fill those in. So let's look at the GT two method. So I'm going to select the column there. I'm going to press control and G, or you can go over here and go to special. And on this, I'm going to pick blanks. Within that column it's going to pick out all the blanks. And now I can simply enter in my required data, and I can do that in one go so. Let's say how many cells I've got first I, one, two, three, four cells highlighted. And if I just use the replace function here, so I'm going to go replace, and I want blanks picked out, and it's only on those cells, so that's fine, it's not going to pick out any other cells across this worksheet. I'm going to replace that with a five and to replace all. There you go. Excel tells us have made four replacements and we can see those fives in those cells. Now, let's just undo here for 1 second and look at the other method, which I think is actually a lot simpler. So we can simply look at quantity and we can pick out all of our blanks. We can enter a five there and obviously very small set of data here, so we could just type each of those in. But if we had a lot of data, what you'd want to do is select all of your filtered selections and just use Control D, for example, and that will copy that data down, and we can just clear our filter. So I think that's a much quicker way of doing that one. Okay. So number seven is we want to protect the existing data on this worksheet to prevent unauthorized amendment, but we still want to allow filtering. So what's the hint here? So we can use the protect worksheet functionality and we can check the options to allow auto filter. Now, important note here, you should have the filter on before you protect. So let me show you how that works in practice. If we go into our review tab here, and we use protect sheet. Now, I'm just going to leave this on the defaults here for now. I'm just going to press. Now, if I try and filter, I can't and I can't actually change and bring in in and out those filters. So I'm going to have to unprotect for 1 second here. And let me just clear my filter reapplied, including the new headers I've added. And now, if I go into review again and protect sheet. This time, I'm going to leave the defaults, which are those four that you see the two at the top, the two at the bottom, and I'm going to bring in use auto filter to be allowed as well. Press. And now I can filter, which is great. Exactly what we wanted. Just be mindful that you can't use the clear option here either, so you're going to have to remember to go from here to clear those out. And I can't even though I see those options, I can't actually sort this data. So that's that means that we've protected the original order of the data here as well. And of course, you would normally put a password on, but in this case, I haven't just so I can easily turn it off. So I'm just going to unprotect for the remainder of this, but you've seen how we can do that. Right. So number eight is we want the ability to view total sales made by each agent per region or by region first, and then agent and accompanying chart. So let's take a look at the hint here. So we can use pivot tables and pivot charts, and in fact, that's the same for the next one, we'll come to in a second as well. So what we're going to do here then is, we're going to select our entire dataset, and we're going to go to Insert. We'll do a pivot table, and we'll put it on an existing worksheet, we'll choose our dashboard here. So let's just put it over here for now. What we want to be able to do then is we want to be able to bring in region and agent and I want to see those total sales. There we go, and in terms of seeing the other requirements. Seeing agent per region or region and then agent, it would just be a case of being able to change this back and forth, you could see it by agent and then that agent sales per region, so you can switch back and forth very easily using a pivot table. In terms of an accompanying chart, we can also then insert a pivot chart. Let's go with a Pi chart here. And again, we'll leave this as it is for now. But let's just collapse all of these. Let's just look at it by region, and we'll just get rid of our blanks here where we don't want to see those. Okay, let's look at our next task. So we now have to create a chart with months running along our x axis and showing total sales and total costs as a bar chart and margin as a line chart. And we're going to use pivot tables and pivot charts here again. Let's select our entire dataset and go to insert, and we can insert the pivot chart and pivot table at the same time. So we'll go to the existing worksheet here, and let's just place it here for now. Let's just move our pivot chart over here. In terms of our pivot table, we want to bring in our dates, and then we want to bring in total sales, total cost and margin. Great. Now let's amend our chart here to the format that we want. So we're just going to right click and go to change Chart Type, and we're going to do a combo chart. So we'll leave our total cost and total sales as they are, and in fact, I've already recommended margin as a line here as soon as we've chosen combo. We'll leave it as that, and we'll put in a secondary access as well. We can do that. And let's just expand that Mure working as expected. Yeah, so we get our pivot chart expanding to the individual dates as required. Okay. So that deals with that requirement. Now, our last one here is producing a list showing the five highest margins made. So going from the highest to the fifth highest and showing item name and date of sale. So this one was potentially quite challenging. Let's take a look at the hint here. So we can use the Max function to get the highest value item. And then for the next four, we can use Max IS, and we can then use match to find the low number of that value and the index function to locate item name and date. So let's break this down and go one step at a time to see how we can achieve this. Let's start by setting up our little table over here. So let's have our headers, we're going to have our margin. We're going to have our item, and we're going to have our sales date. And let's get our highest margin first. So this is quite simple. We can just use the max function, and we can apply it to our margin column here. So that we'll simply pick out the highest value here. That's great. We can pick up the highest value, and if you really want to satisfy yourself, that that was highst, you can just drop this down and check manually, that that is indeed the case. Now, to get the next one, what we can use is the MaxF function. Let's step through this then. So we're going to use the Max ifs function here. And the first item that we'll want is the Max range. So where we're looking to pick out a value from. So we'll want to pick it out from column k, which is our margin. Next, we want to pick out our criteria range. So where we're looking for a condition. Again, we're going to be looking in this column here for our criteria range. And what do we want to be checking for? What is that criteria? Well, we want to make sure that that value is less than the value we've already got in our table. So we're going to use speech marks here to wrap our comparison operator for less than We're going to use the ampersand to combine that with the value we've got here on B 49. We can just close that off. What this is saying is, it's looking at column k, which is our margin. In terms of the condition, we're saying, look in column K again and as long as that value is less than 892.5, it's valid to check for a maximum. We're basically ignoring anything that that's not less than this. The next item that we get is the 787.5. Again, we can just manually confirm this one right there. Now we can just copy that formula down so that we get our top five margins here. Perfect. So the next thing that we want is we want to pick out the item names and sales dates associated with those margins there. So as we've seen from the hint, we can use a combination of match and index to achieve this. And anytime you have a slightly complex formula, it's always worth breaking it down into its component parts and then combining it. So we'll do just that. Let's start then with our match function. We'll enter our match function here in this cell. And we're going to look for the value that we've got here, and in terms of our array. We want to find where that value appears in this column here for margin. And for the last parameter, we want zero for an exact match of that value. So we want to find exactly 892.5 and find out which position that's in. When we press enter there, we can see that's in position 29, that's room 29. So great. We've got that, and what we want to do is use that to work out our item name and our date, so we're going to feed that value in to the index formula. And again, we'll do this in a separate cell here. Let's center equals index here then. And for our first parameter, RRA, we're going to select all of our columns covering our data here from A to K. And then the next parameter, which is our road number, we're going to actually pick the value that we've got here on that cell. And then finally, for our column number, what we want is pick out the item name. So we've gone e to k here in our array, so that's one, two, three, four, five, that's our fifth column. So we can just enter five in here. Now you'll notice that as I'm going back and forth between the two different worksheets it's actually filling in the names of those worksheets. So we'll just have to delete this out and enter five manually, and we can close that off. And let's just confirm that that's correct. So 892.5, that is the item name we're looking for. So we've worked that out, perfect. Now, what we want to do is combine this into one cell. Rather than referencing the result of match here, we can simply take our match formula here, copy that, and where we've got our row number, we can replace that with our match formula, press center and we get the same result, which means that we can just copy that entire formula and place that into our item cell there and get rid of these. And then we can just copy this formula down, and let's just make sure this is exactly what we expect, so we can check this off manually. So we should have socks, carve, tennis ball, et cetera. Let's just confirm these items so we'll pick our top five in hair manually. And you can see there those items match exactly the ones that we're expecting. The only thing left then is our dates. Let's just clear that out and go back here. Now, for the dates, we can actually use the same formula here, and we'll just have to change the reference here. So rather than the fifth column, our date is in the second column, which we can see here. So we're going to bring that in, and it looks like we'll just have to change the format here as well, so we're just going to copy this down first, and then we're going to change our format here from general to short date, let's say. And now we've got our margins, items, and sales dates. We've now completed all the functional requirements, but this doesn't look particularly great. So Let's just spend a few minutes on seeing how we can spruce this up. I'm just going to insert a few extra rows in here, so I have some space to work with. Let's just start filling in a few things here. How about a head, for example, we'll have performance summary in here and let's just do some formatting in here as well on the cells. Let's go into fill effects and we'll choose a couple of different colors here. Let's maybe change that font as well and the size. And let's maybe just merge these cells here, so we can see it like so. Now let's bring in our regional stats here. I'm just going to cut that with control in x and pace that there. Let's just g that ahead as well. We'll just put in some colors in here and again, let's just use some formatting in here. Then likewise over here. But and I'm just going to change again just a bit of formatting over here. A couple more things here then. Let's, for example, bring our total there and maybe put this in a slightly different color over here. Then what we'll do is, we'll put some borders around this as well. Let's go for all borders like that. Let's bring in our agent and total sales next. We'll place this here, and again, let's just put a header and we'll expand this out again. Like that, Let's just do some formatting in here as well. We'll use this. We'll do the same thing with our borders here. Let's just select these columns and double click there, just to size them appropriately as well. Maybe let's put a different color in here so that it's obvious that we need to be picking our agent there. And we'll just put in some instructions like so. Let's change a couple more things here just briefly. So we're going to merge and center those, let's say, likewise here. Let's just again change some colors here, like so. We'll also change this to currency in terms of the format there, and we can just use the format painter here then for our total sales like so. Okay. I've got one extra row in here that I want, so I'm going to get rid of that. And then let's do our next section. So we'll do our top five highest margin items here. And let's bring in our dashboard from earlier. We'll just cut and paste this once again. Again, we're just going to change the formatting to match the pattern that we form so far. We'll use this and we'll put these like so. We're going to have to change this formatting back to a date. There we go. Let's bring our pivot charts in next, see how we can position these. Let's put this over here and make sure that these are expanded enough as well, and we're just going to resize this. Fits right there. Let's look at a couple of the options in here. Maybe we can just use a quick layout option here. Rather than just having a blank chart there, we can for example, bring in our titles there. So we can see those region names and those percentages. We'll do that for that chart. And then for our other one, maybe we'll just place it below everything here and we'll expand this out to cover our entire section there, I have to reduce the height here a little bit as well. I all fits on the screen, and so we've dealt with that. Now we still got some data left over here that doesn't look too good, let's deal with that next. One way of dealing with that data then is maybe we can create an appendix and pivot table section, right there, and then we can move this across. Let's move our list of names over here, let's say, and we'll just call this agent names, just to give it a title. And we'll also bring in our pivot tables. I'm just going to grab these and place them here and grab this entire table as well. I'm just cutting and pasting at this point. Put that there. Then what we can do here is we can have these hidden away. So for example, we could select that and we could hide this, but a better way might be to actually group these. What we'll do is, we'll go to data and then we'll click on group, and that will create a group right there that can be collapsed and expanded. You can also just click on the different levels available here. Then maybe we can just make this appendix and pivot tables look similar to the formatting we've got here. We can just change this, and let's just make sure that this is visible across the screen. We can just copy that. Go across like that, and we should have something that looks a lot neater, where you can still work on those pivot tables and they'll change your charts as required, but they're not necessarily visible on screen all the time. Okay. So suddenly after just a few minutes, we've got something that looks a lot better, a lot more presentable, and I'm sure that if you put your mind to it, and you've probably got a lot more artistic skill than I do, you could produce some really amazing dashboards. But this is just to give you an idea of what you can do with Excel if you just take a little bit of time to present your data. So that brings us to the end of this video then. Hopefully, you enjoyed that walk through, and as always, thank you for listening. 32. Course Completion Summary: Welcome to the course completion video. You've made it to the end of the course. Well done. You've now successfully run through the key features of Excel, solve problems, learn shortcuts, and understood how to create and streamline useful spreadsheets. Your takeaway from this course is to know that you are fully capable of using Excel effectively. You now have the chance to use those skills, whether that's marketing your abilities at interviews or improving and implementing new processes at work. You can say with confidence that you understand and have used V lookups, pivot tables, index and match amongst other functions. You can create useful spreadsheets or streamline existing ones with features such as data validation, range names, worksheet protection, graphs, and more. You can perform reconciliations, analyze data successfully, and be able to present it aesthetically. Understanding how to approach solving a problem in the cleanest and most efficient way is crucial. And the best way to do this is to break any problem down and go step by step. You may need to build potentially complex formulas and complex spreadsheet models. But as you've seen, you still need to understand the basics of how it works, and doing that piece by piece is the best approach. The most important thing, even over all of these analytical techniques and tools that you've learned that I really want you to understand, and this does not just apply to Excel is the following. Always believe in yourself. Never give up on anything that you feel is worthwhile. It's easy to give up, but you learn and grow only if you keep going through those tough situations and embed in yourself this resiliency. There are no guarantees on outcomes, but you can always control your effort, and you will leave any task with satisfaction if you give it your all. In closing, I hope you enjoy taking the course as much as I did creating it. Thank you for signing up and for watching. I look forward to hearing your feedback and the impact I hope the course has for you.