Excel spreadsheets made easy

1. Introduction: Hi, I'm Karin. I'm 51 years old. I live in Geneva, Switzerland. I have been a freelance consultant for 20 years now, helping my various clients to get their bearings around computers. It goes from very basic stuff, like holding your mouse and really accompanying someone with their very first steps on a computer to quite technical stuff. Debugging complex excel sheets, repairing computers, all sorts of very different things. I'm pretty sure a few of you have opened excel and thought to yourselves "I'm never going to manage this on my own" and closed it. So that's why I devised this little class to take you through the very basic first steps on Excel, setting up spreadsheets and putting in numbers and then making a little chart at the end and printing it. It's going to be very basic. You need absolutely no prior knowledge of Excel whatsoever, and hopefully, by the end, you'll have a nice little file that will give you a vague idea of how to use the very basic functions of excel. The idea, of course, will be to build on this class with later classes taking you through more advanced features and helping you to grasp a certain knowledge of excel and a certain confidence using the software. So let's go ahead and start with our first lesson. 2. Class project: So before we actually start working on Excel, I'm going to walk you through border class. Project is going to be what is going to look like and then you can decide for yourselves if this is something for you or not. So our initial starting point is going to be taking a very basic spreadsheet like this one , which we will, of course, build together and make it look like this printed sheet of paper where have a list of monthly expenses up here and a little pie chart to show what the percentages of all my expenses are. And it might look daunting at first. But as you will see, it's a few very simple steps which we will go through together and at the end, this is what you're going to have. What we're going to do is we're going to enter the data together. I will guide you through all the formatting steps, and then we will create the graph together. If at any point you get lost, I'm going to be posting as class resources the different Excel sheets at different levels. So I will put posts first Excel sheet with all the data entry done, but no formatting. I will then post one with formatting. I will post one with the formulas, and I will, of course, post the final result. So any time is you get lost, you can go back and take one of the class resources, so let's get started. 3. Basics of excel: So in this first lesson, we've got to start learning the geography of excel. So when you open your screen, this is probably looking a lot like what you're looking at. You have three title borrow to the top, which is in the Green Excel color, which will indicate over here the name of the special you're working on and the fact that it's an Excel spreadsheet, your user name over to the right on the little X to close your window. And then you have the quick access toolbar on which there are a few tools that you can customize and add the ones that you prefer. Then, right under that, you have a menu bar, which is going to lead you to the different tabs on which the tools organized. The main bar that we're going to be using is the home ribbon, which is the one that you're on normally, which will have all of your current tools that you need to use on a daily basis. Now we're going to be going through these quite a lot drinks class Onda. By the end of it, you should be fairly comfortable working with most of them, then if you continue down, we have this little area here, which is the formula bar, which is going to be very useful. As soon as you start doing calculations within Excel, Um, we'll see that the things that are shown here are often quite different from the things that are showing in the actual spreadsheet, which is the next part we're going to be looking at here, which is your spreadsheet, which has split out into different Collins. If you click on the Kahlan, it will highlights. And as you can see, they are named with letters. It will go from a old way through, Two said, And then it starts getting a and it goes on like that all the way to, UM, X f d, which means you have a total of 16,384 columns. That wife 16,384. Why not 16,000 or 17,000? A nice even number, simply because computers work in powers of two on 16,384 happens to be two to the power of 16. So that's what they've decided on. So you have 16,000 odd column that you can fill and in the other direction we've got Rose, which are numbered 123 all the way toe 1,048,576 again, a very weird number, but it just happens to be two to the power of 20. So there you go, quite a few rows and columns that you can fill. Um, the limit here is going to be the power of your computer. At one point, your computer is going to say it's sorry I can't take anymore and it's going to start crashing. And that will be your limit that you have reached. Now at the bottom, we've got little tabs for sheets. When you open an Excel sheet, there is one tab available, which is sheet one, and there's a little plus sign right next to that, which is going to allow you to add a new sheet. And every time you click on it just like that, it's going to add a new sheet again. The limit here is going to be the amount of memory you have in your computers. So the more memory you have, the more cheats you're going to be able tohave What I definitely do is I try to keep my sheet within a kind of logical grouping. For instance, if I'm working on a budget, which is what we're going to be doing later on a little personal budget, what I'm going to be doing is I'm probably going to be keeping one year in one file, so I'll have a tab for January attack for February, etcetera, etcetera. Then if I start a new year, generally speaking, I will start a new file on that way. It allows me to archive whole year and not have to worry about working with files that become too big and difficult to manage. The last area on the spreadsheet that we're going to be looking at is this bar down here, which is the status bar, which for now doesn't mean much. But you will see later on that there is a lot of information that will be visible down here , and also we're going to be able to the way or be able sorry to alter the way that we look at our layout, which is the normal layout down here. The page layout, which is going to allow you to view your Excel spreadsheet on a piece of paper, which is very useful when you're trying to find you in your printing. Onda thin the page Great view, which is basically just going to give you a vision of the different pages that excellence man to print. So in this case, nothing at all, because my spreadsheet is empty and therefore there's going to be nothing on it. So I'll come back to my normal view, which is the view in which I gently work. Um, if you want to select a whole row, you'll click here to want to select many rows. You click. Keep it clicked and drag it along. Um, if you want to select Rose, same thing, click on the actual road number. If you want to select several, you'll just click and drag. If you want to select the whole of a spreadsheet, come and click on the little square. That is between the letter and the number off the roads and Collins, and that will select the horse spreadsheet, all one million rose and 16,000 Collins off it Armed. To remove a selection, just click anywhere in your spreadsheet on the select, more be removed. As you move around your spreadsheet, you will notice in the top left corner up here in the name box that there is a little reference that comes up, which is always made up off the letter of the Kahlan and the number of the road. So here we're actually in cell D 10. The little squares here are cold cells and eso. You immediately know where you're at. If you have several cell selected, it will give you the reference off the cell that you started your selection to. In this case, G five, If I start my selection at the bottom and move up, it will give me the bottom cell, which in this case is age 21. So will always give you the first cell in which you clicked to get your selection for. And we'll see also later on this course that we're going to be able to do various little things with little name box here. So I think for basics, this is pretty much it. And so we will move on to the next lesson where we will actually start entering data into a spreadsheet 4. Data entry: authorities. So now we're going to be capturing data into a spreadsheet, so leave a few lines free. I normally start around line for it was like having a few lines blanket the top toe, add things. You could always add them later because you can at Rose later on. But that's just the way I like to do things, no scientific reason whatsoever behind it. So what we're going to do is type text. I hope you've got your list of expenditure is ready to copy from my 1st 1 is rents, which fits nicely into the cell. My next one is health insurance. Now, as you see, it goes out of the cell and, um goes into the next cell, which isn't a problem for now because the next cell is empty. Now we'll see what happens when we feel the next one. So I'm just going to go ahead on and pack in all of my expenses. I'm just hitting the enter key. Asai finished, and it will go to the next line. I've got a line for food shopping. I've got another line for restaurants and coffee because I feel that if I don't add that into my budget. A lot of money seems to go on that, and I the account for it, which silly. Another Chris Ity telephone, Internet and TV clothing is definitely not one of my big expenses. Amusements slash entertain minutes on various for all the things that we never think off counting, as you see all the text has aligned on the left, and that's automatic. You don't have to do anything. We'll see in the formatting chapter that you could actually do other things with it. Now what I'm going to do is I'm good to go into column B, and I've just realized I've got to put a header from Icahn. My 1st 1 is the description of my expenditure. It's the 2nd 1 is going to be the budget amounts, and I voluntarily don't leave any black Collins in between my two in between my Collins, because what I'm going to do is I'm going to make my Kahlan a bigger to account for the fact that some of the text goes Internet Collins. But it's a bad habit to leave Black Collins, because if you start using Excel is a database that will be a major problem later on, so get used to using Excel with proper good habits rather than leave space. And just because so, I'm just going to bash in my numbers. As you see, the numbers are automatically aligned to the right of the cell. All of my months are, um, amounts that don't have any decimals, so I don't have to worry about it too much. Onda. As you can see, any time my texts overlapped into the next column, it's been hidden. No, it hasn't disappeared completely, but it has been hidden. If I come back onto my line five and I look at my health insurance in the actual cell weaken, see health inch. Now, if you look up on the formula bar, you can see that actually, in the cell is health insurance. So I haven't lost anything, but it's just not visible for the moment. No big deal. And then I am going to go into next Kahlan Common see, and I'm going to put in actual figures which will allow me to check out the difference between my budgeting and my riel expenditures. Now, for my rent, that's an easy one. My health insurance is the same every month. The bus passes the same every month. Now all of the other ones are going to be dependent on the bills that I get or the expenditures I actually do. And I'm going to make a detailed list for my food chopping my restaurants, my amusements and entertainment on my various, which I will do on new sheets. Because that way I'll be allowed. I'll be entitled to have a little bit more space, and it won't print on my final sheets that I want to print. So I'm going to go ahead and do that right now and at the new sheet on which I am just going to put my list of food shopping expenditures in the first Kahlan will be a date, and the next will be a description. In the last will be. The amount on these were going to be actual figures and not budget figures. Our dates have to be entered with a separator that allows excel to know that it's a date on my computer. That separator happens to be the slash now to go into the cell. Next to that, I'm going to use the tab key on my keyboard, which is just above the caps. Look, he the description is the store I purchased the things at on then the amounts. And that way, when I hit the enter key, it will go not down one cell. But it will go back to the beginning of the line which will allow me to enter my second expenditure. As you see, I enter 15 slash to slash 18. I'm in Europe, which means I do a date month, year, and not month, year, a month date year, as you do in the States. So for those of you in the States will obviously have toe factor that in. But, um, once I have entered the date, as you can see, it has automatically changed it to 15 slash 02 slash 2000. 18 years I had entered 15 slash to slash 18 so it automatically translates it into a day that is recognisable by the system. Now, if I don't use the right date separator like here, I'm using a dot This is what happens. It automatically alliance to the left. And that has to set off some little alarm bells in your head because, um, when it aligns, it's the left. That means that it has considered it as a text, which in this particular instance, is not a massive problem. But if you're going to be using your dates to extract information from here from your Excel sheets, as in a database, or if you're going to calculate days between one date and another. For example, a date when a bill is due in the date that you have paid the bill and you want to calculate how many days are in between those two dates, then you won't be able to do it with a date that is considered as a text. So you do have to make sure that your date separator that use is correct. It could be a slash. It could be adult. It could be even a little dash that is really going to depend on how your computer is set up. So you've got to try that out on Go figure it out for yourselves on your computer. And here, Earl, my food expenditures for the month. So that's it for basic data entry. We've seen days we've seen tax. We've seen numbers Onda. Now we're going to be formatting them and making sure that they appear the way that we want them to appear. But that's for the next lesson. 5. Saving your file: okay, Before we go on to formatting, there's a few more things we want to do. And the 1st 1 is saving your worksheet to not lose all the work that you've done so far. To do that you should have on your little quick access toolbar. You should have a little floppy disk, and for those of you that were fairly young, you might not remember what a floppy disk looks like. A few of my students often call that the TV because it looks a little bit like a TV, but this is the little button you're gonna press on. The other alternative is to go through the File Savers menu, which will be the equivalent of clicking on the icon. Now when you go into file, save as it's going to show you your recent places, it's also going to show you your one drive, which is thes space, which is yours on Microsoft servers, and you can also go onto this computer. Now it's up to you to decide where you're going to be saving your things. In my case, I'm going to save them to my one drive skill share, Um folder, which is over here, which I've used already, So I'm going to go click on it. Now. If it was a fault of that you hadn't used before, you would have to go onto this PC and then you're going to have a list of different folders that you have on your computer on and you find the folder that you want to go into or alternately, you can click here on documents which will open this little window were on the left. You've got all your different folders. In this case, it will allow me to choose one drive and then skill share, which would be the same as clicking on the one Dr Skill Share folder that I had in my previous list. And here I'm just going to call it Class project. And then I click on Save. If it wants to say, There we go now it has been saved. It will show up here that it has class projects. It has been saved. The one Dr it also tells me that it's automatically saving it because I've got the auto save on. Now, if you don't want the auto safety beyond, you can turn it off. And that way you decide when you want to save, and it won't save anything that you didn't want it to save. Now, in this case, I am perfectly okay with it being on auto save, because that way I don't have to worry about it. That's the first thing I wanted to show you. The second thing being down here, we've got different tabs now. On the 1st 1 you saw me and to the budget on the 2nd 1 You saw me enter my lists of food, shopping expenditures, and I have since then entered 1/3 cheat, which was my entertainment and forth sheet, which is my restaurants. Now what I'm going to do is I'm going to name these sheets in order to make it easier defined. So to do that, you just simply double click on the tab and then you type the name that you want. So my first one's gonna be called Bucket. My second one's going to be called food. My third one's going to be called restaurants, and my last one is going to be called Sorry, I got that wrong. This one is actually going to be cold and her entertainment on my last ones going to be called restaurants. There we go 6. Formatting: Okay, so this is where we left off. Ah, we have a budget which now has a budget, Colin, an actual Colin. And in the meantime, I have created three different tabs, one with food, which we did together, one with my entertainment expenses and one with my restaurant expenses. Now, the first thing I want to show you on the entertainments, um I have a first entry which is supposed to say books and which actually says book cap, because I made a typo. I'm quite good at doing typos, so I wanted to show you how to edit information. You can do it straight in the sell by double clicking in yourself, and then you can backspace and enter the S. Or you could do it up in the formula bar by clicking and editing on those air the easiest ways to do it. The last way of doing it would be to completely delete what's in the cell and re type it. And you actually don't even have to delete what's in cell, because if you re type anything, it will automatically override what was in the cell beforehand. So I'll go ahead and put my books back in here. And so I've got now three tab details and one tab, which is my summary tab. Now what I'll show you later on in this course is how to go and get the information that's on the food, entertainment and restaurant tabs to bring it back in my actual Colin on my budget. But for right now, what we're going to be doing is we're going to be formatting this sheet that we set up, and the first thing we're going to deal with is the call in Woods. Now the first Colin is to narrow to show the whole of my text. So what I'm going to do is I'm going to change the width of that Colin. And again, there are several ways of doing so. The easiest way, in my opinion, is to go and position your cursor Between column A and column B. You see the cursor changed shape. I'm not going to click. I keep it clicked, and I drag it to the right to make my Colin bigger if I want to make it bigger still, I click again and I drag again. Now my Commons, B and C are both going to contain figures, and I want them both to be the same with. So instead of doing them individually, I'm going to click on my column B, keep it clicked, drag over to see and let my Moscow, and then I'm going to come and make Colin be bigger by positioning my cursor between B and C. And I'm not going to click and drag, and it's going to make both my Collins. The exact same size will make its tiny bit wider. I'll come back to my column a and make it a little bit wider now. As you see, I hadn't removed my selection from being see. That does not matter, because Colin A. Is not in the selection, so changing the width of my Colin A. Is not going to change the Collins of my Collins B and C, even though they are actually selected now. The next thing I want to do is I want to change the color on my headers, so I'm going to select all three of those cells and I'm going to come and select a color. Now there are two ways of doing so. One way is by using a little paint pot, which is right here where I click and I've got also its different colors. And if I want more colors, I've got a little button that says More colors and there I've got a whole pallets that I can change select from the other way is to come into the style section over here where I can click on the Drop down arrow, and I've got different themes where I've got the same kinds of blues in different hues, same greens, etcetera. Now that's one way of doing it. I normally use a little paint part up here, but it's really up to you which one you prefer using. So I quite like blues. So I'm going to put my head or with a blue background. And then what I'm also going to do is I'm going to make the text a tiny bit bigger. So the text formatting is up here where you've got the font name the size, and here you've got an A with an arrow pointing up and the name with an arrow pointing down . If you know exactly what size you want to go for, you can click here and select your size. If you're not quite sure, then you can click on the A with the up arrow and it will go up one step at a time. And when you think that it looks okay, then you can stop. In this case, I left it in Calibri and I selected size 14. The size of my actual text is okay, so I'm going to leave it at that now. The next thing I'm going to formats are my numbers. My numbers for the moment, are just showing as such. And what I want to do is I want to put a little separator for the thousands, and I want to add decimal points with two decimals. So I'm going to select all of my figures down here, and I'm going to come and select this little style, which is the common style, which will add a 0.0 after all of my figures and a little apostrophe for my thousands. I'm not going to go ahead and formats my food and entertainment and restaurant cheats. So same thing, I'm just going to make my Colin be a bit bigger, make Michael and see a bit bigger. Select my figures put them in the Kama number style. I'm going to add my little header. Now. I had selected a blue and, as you can see, that stayed in the little paint selector so I don't have to go and remember which one I clicked on. I can just click on my little pain pot, and it will automatically put my headers in blue. I'll do the same on the entertainment. Make my description, Colin. Bigger my Mount Colin bigger my numbers in the Kama formats and same thing again on my restaurant page, and that's it for my very basic format. So for this lesson, we're going to stop here, and what we will do in the next lesson is we're going to actually start adding things up, so we're going to start with formulas. 7. Calculating: Okay, so the next step is going to be to add up all our expenditures and see how much we spend every month on, Then compare it to what the actual figures air going to look like. The first thing you need to remember when you're calculating is that a formula always starts with the equal sign. So that's what I'm going to start doing. And then you've got to put in cell references and indicate to excel what you want to do with these cell references. In this case, I want to sum up the whole Colin, so I'm going to show you two ways of doing it. The first way is to go and select your first cell, plus your second cell, plus your third cell. Now, as you see as you go along, several things were happening. For a start. Your formula at the bottom is showing what references you're clicking on. It's also showing here on your formula bar, and it's also showing you in color, which sells have already been used and which cells haven't. So I'll just keep going down the common like that until I've got all of my entries added up and the last thing I do to confirm that this is what I want to do is to hit the enter key, and now it's going to show me a total of how much I expect to spend every month. And as you see, if you look at the formula bar, you will actually see the formula. If you look at the cell, you will actually see the result. Now. What I want to do is I want to do the exact same thing in my actual Kahlan, and there is a very easy way to do that. And that is to come down onto the bottom right corner off your cell. You see, there's a little square. When I position my cursor on it, my little square turns to a little plus sign. I click and I'm going to drag along. And what it will do is that it will repeat the formula in my second column, and if I go and click in my second Colin, you will see that it has adapted the references. So when I clicked on all my B cells in the first column and before be five etcetera, when I dragged it along to the Sea Colin. It automatically adapted these references to C four C five C six, etcetera. So that's a very easy way of taking a formula and putting it into other Collins that look very similar to your 1st 1 Now, in this case, we only had about 10 different lines of expenses. So it's fairly easy to go and say, plus one plus two plus three plus four etcetera. Now, when you've got larger Collins, it's obvious, you know that you're not going to go through and add up every single line. So in this case, I'm just going to remove what I already did. I'm going to click in my first Colin, and I'm going to come and use a little tool, which is over here on the right, which is called Auto Some. What auto some is going to do is it's going to use a function which looks a bit like a formula. The difference being you've got. The function name up here equals some, so it knows that it's going to be a some, and then it's going to show me here in brackets the references that I'm going to go from two so it's going to say it's going to take the some from before to be 14. It also shows you those references in a box with a blue background. If that's what I want to do, I am going to confirm with the enter key again, I can position my cursor on my total. Take my little square at the bottom click, keep it clicked, dragging along, and it will do the same thing in Collen C. And if I go look as you can see, it has also adapted the references, so it's a very easy way of adding things up. I'm going to go do the same thing on my food tub now. What I'll do is I'll leave a few lines here so that if I've got a fume or food expenditures for the month, I can fill them in. I kicked on auto some, and it's also going to take the empty cells an account. So for the moment I've spent 403 francs on food. If I add in an entry for a later dates, you'll see that as soon as I enter that it will automatically change the total, which is very convenient. I'm going to go do the same thing on my entertainment page, leave a few lines, auto, some enter and the same thing on my restaurant page. Leave a few lines. Also, some enter. So as you can see if your calculations are very basic, which is the case here, Um, there's not too much to worry about with Excel, because it's going to do most of the work for you. Now I'm going to add 1/3 figure calling here, which I'm going to call over slash Under, which is going to indicate if my expenses for the month are over or under budget. Now what I'm going to do here is I am going to make the call in a little bit bigger. So I click on the space between D and E and drag, and here we go. And now what I'm going to do is I'm going to take my actual figures to which I'm going to subtract what I had budgeted for now, obviously, for rent, health insurance and bus pass, which are fixed costs, it's going to be identical. And as you can see for the moment, I haven't entered any actual figures for food restaurants, electricity, telephone or anything else. So for the moment I'm under budget for the month. I am going to go and take my total from Colin, see and drag it along to Colin D, which means that I had expected to spend 3370 francs for the month. I have, for the moment, recorded on expenditure of 2000 and 90 francs, which means I'm under budget for 1280 francs. That's good news. The bad news is I haven't entered my figures yet, So if I go ahead and enter the figures for my actual bills for electricity and telephone, I'm going to enter a an electricity bill for 127 francs on a phone bill for 40 to 70. In this case, you see that these two have gone over budget. The other ones, they're still nicely under budget. I haven't spent anything for clothing, and the rest I'm going to go get on my other sheets. Sorry, I've still got the Internet and TV, which is a fixed amount per month, and I haven't recorded anything under various, so I'll just enter that as being zero which means I'm under budget for the various post again. And now in the next lesson, I will show you how to go and retrieve the figures for with the amusement tab, the restaurant tab and the food and shopping tab, which are the figures we entered in the other tabs. So that's for the next lesson. 8. Adding things to a sheet: Okay, So what happens if you realize down the line that you've forgotten something? We're going to have to modify our occurrence spreadsheet to take that into account. Now I'm going to add a line between my line eight and my line nine. And to do that, I'm going to go click on line mine. No, again to add a line. There are several different ways I'm going to show you a few of them. The 1st 1 is to right, click on your line number, world number, and go and select the insert menu on that will insert a line above the point where you had clicked. I'm going to undo that so that I can show you a second method. So the second method is to come up here to the cells portion of your home ribbon, and you see you've got a little menu here for insert you click on it and you're going to ask to insert a sheet rope, which will do the exact same thing and insert the road above the point where you're currently positioned. And for those of you who, like, um, keyboard took shortcuts, the other method is toe press. The control key on your keyboard and to keep that pressed and to press the plus sign on your number pad. And that will also add a line above the point where you're at. If you want to delete a line, you do the same thing, which is hold down the control key and press the minus button. If I press minus again, I'm going to get rid of my electricity line. There you go. If I realize I've just done something silly, I go back up onto the quick access to a bar and undo my latest action. So I'm just going to control plus it again to add another line. And here I'm going to add a line for my station re and art supplies because I seem to spend quite a lot of money every month on stationery and art supplies. And so I'm going to budget for it. Um, let's say I'll give myself 100 50 francs to do it. For the moment, I'm under budget because again, I haven't added anything, and what I'm going to do is I'm going to create a new spreadsheet and you sheet for my stationery and art supplies on What I'm going to do for that is I'm going to take one of my sheets that already exists, for example, my food spreadsheet. And I'm going to copy it so that all the formatting is already there. All the formulas are already there, and the only thing I'll have to do is to change the actual data which will allow me to, um, cut a lot of time on my processing. So to make a new copy of a spreadsheet again, there are several ways of doing so. One way is to right, click on the tab and you'll go to insert, which will create a new blank sheet. That was not what I wanted to do, so I'm just going to undo it. So to create a copy of my existing spreadsheet, I'm going to right click on the tab. I'm going to select the option which is move or copy. It opens this little dialog box. I'm going to tell the little dialog box I want to create a copy, and I'm going to put the copy right at the end. So I'm gonna click here on move to end and hit OK, and that's going to create a new tab, which is called food number two right at the end. That's one way of doing it. The other way of doing it is to select the sheet that you want to use as a model. Now you're going to press the control key on your keyboard and keep it down. You're going to click on the tab that you want to take. You see that my cursor changes shapes, and now you're going to drug it toe where you want to keep it, and when you drop it, you can let go of your control key. Now you will have another copy of the spreadsheets. I've got food to which I created using the right mouse button on. I've got food three, which I used by dragging it and hitting the control key. So I'm gonna go to keep one of them to remove a spreadsheets. You right, click on it and you select the delete option. You'll get a little warning message, which will tell you that Microsoft Excel will permanently delete this sheet when it says permanently delete. That means that if you try and click on the little undo button, it will not work. Now you've got to be aware of that and make sure that when you delete a sheet that is actually what you want to do, I'm going to double click on my food sheet and I'm going to rename it Station Reek and Art . I'm going to remove anything that was already on here by selecting it and then pressing the delete key on my keyboard. You see that my calculation free for my total is still here, but right now it shows a little dash because there's nothing in it. And now I'm going to enter all the expenditures I've done for my art supplies and my sexually this month. Now, if I don't put a proper separator on my date, it will not be recognized as a date has you remember? - And I've added three different lines of expenditures for the month, which adds up to 55 francs 40. So I've now got four different tabs with detailed expenditures on them, as well as my extra tab for my budget. So now what we're going to do is we're going to go and get the different expenditures on my tabs and bring them back on my budget to bring everything together. But that will be the next lesson 9. Linking data between sheets: So what we now need to do on a spreadsheet is to go get the figures that have actually been spent for the food, the restaurants, the stationary and the entertainment, which are spread out onto our different spreadsheets down here at the bottom. So how are we going to do that? I'm going to show you two ways again for the food. I'm going to start from the budget spreadsheet. I'm going to enter an equal because I want Excel to calculate this amount, and what I'm going to do is I'm not going to go click on my food sheet. If you look at what happens up here in the formula bar, you'll see that it has translated my click into the name of the spreadsheet, and I'm not going to come and click on the cell that contains the total off my food expenses, which is down here. And what I'm telling Excel by this formula is I want it to go and get the information that's in the food spreadsheet on Cell C 13 and I'm not going to confirm my entry with Enter I hit Enter. It comes back onto my budget cheat, and it shows the actual expenditure for my food shopping this month, and it automatically re calculated whether I was over under. In this case, I'm over for the month now. I'm going to do the same thing for the restaurants, but I'm going to go about it a slightly different way. In this case, I'm going to go to the restaurant tab, which is down here. Select my total. I'm going to copy it so again to copy. There are several ways of doing it. You can click on the little icon up here, or you can do a right mouse click and select copy. Or you can use the keyboard shortcut control. See, then we go back to our budget spreadsheets. Now, this was the restaurants I wanted, which I'm going to go and enter in the appropriate cell here. Now I'm going to go with my right mouse button and I'm going to select this little paste like on here, which is called Paste Link. And what that is going to do is it's actually going to go and get the reference that we wanted on the restaurant spreadsheet, which is C 16 and bring it back onto my budget cheat. And if I go on to my cell here, you'll see that it will have translated that to exactly the same thing as what I did hear the difference being in this case. It added little dollar signs, which we're not going to worry about right now, but which I'll explain in another lesson. Now I'm going to do the same thing for my spreadsheets. A. Sorry, my stationery. I'm going to go and get my stationery and art figure, which is down here and hit Enter. As you see, I tend to use the equal sign, and I go click afterwards rather than using the copy and paste method. But if you prefer the copy and paste method, that is perfectly fine. And finally, I'm going to go and get my entertainment figure, which is down here and hit Enter and there we go. We have our links to our actual figures, and so I can see that I had decided or budgeted to spend 3500 francs, and I have currently spent 3351 francs intense on teams and I, um, over for some lines and I'm under for other lines. So that's it for linking your data toe other spec sheets. So as you can see, it's very basic and very easy to do. 10. Conditional formatting: now the next thing I'm going to do is going to allow us to see if we're over under budget a bit easier, because right now we can see that summer above budget. Some are under budget, but it's very difficult to tell which are which. So we're going to use is this little tool here called conditional formatting. What I'm going to do is I'm going to select the whole of my Colin whips the whole of my collar. I was going to be moving that cell rather than selecting it. Um, so I select my whole Kahlan, and then I go and click on the conditional formatting menu, and I'm going to go to the highlight cell rules. It's the easiest one to use, and it's the only one I'm going to be using in this particular class. We'll probably see the other ones in other classes, but they're a bit more complex to set up, so we're just going to go to the highlight cell rules and the search. The rule is very basic. Either I'm above budget, which means I'm greater than zero or I'm below budget, which means I'm below zero. So what? I'm going to do is I'm first going to select the greater than I'm going to tell it that if I'm greater than and I'm going to click on zero for some reason it chose 59.45. Who knows why? But I'm just going to tell it that if I'm above zero, that means I'm over budget. I want it, and it automatically selects light red filled with dark red tax. And what I just want is a very light red Phil. So I'm just going to select Light Red Phil. And now I click on OK, and I can automatically see that all of my posts that are above budget are automatically put into that light Red Phil. What I'm going to do now is I'm going to do the same thing for those that are below budget . So I go back into the conditional formatting. I don't change what I have selected, go back into the conditional formatting, highlight the cell rules and go to less than. And this time I'm also going to tell it that if it's less than zero, I want it. And this time I don't want it read. I'd like it blew to go with my header. So I'm going to click on this little arrow and you can see that I've got the choice between red light red, green, Phil, yellow Phil and there's no blue. So what I'm going to do is I'm going to go right to the very bottom here and click on custom format and that will open a new dialog box on which I'm going to go to the fill tab and I'm going to tell it that I want it to be filled in a very light blue and I'm going to click on OK, I'm going to click on OK again. And there you go. All of my items that are below budget are blue, and all the ones that are above budget are red now. I don't like the blue A selected. I think it's very light. It's hardly visible, so I'm going to go ahead and do it again. So I select my Colin again, and now I'm going to show you what you shouldn't do. If that is something that happened to you. If I click on conditional formatting now, go back to highlight cell rules. I'm going to go back to the less than because it's the blue ones that I don't like. I'm going to tell it that if it's below zero, click here again, go onto custom format and I'm going to select a blue that I prefer, which possibly would be that one. And I'll click on OK and I'll click on OK again. And of course it worked. What I wanted to show you walls. Okay, now you have to be careful when you use conditional formatting just selecting my cells again because every time you go into the conditional formatting, what it's doing is it's adding a new rule. And if I go right down here to manage rules, you'll see that in my case, I have three different rules for my cells. And what it's doing is it's first testing my cell value lesser than zero on adding the pretty blue that I like. If that doesn't work, it's going to go to the second rule and put the very light blue, which I didn't like. And the third way is it's going to go back in and test if it's greater than zero and put it in that little light red. Now what I want to do is I want to get rid of this middle rule because it's not used. What I'm going to do is I'm going t just select it by clicking on it and hit the delete rule button and then click on OK, so that way I won't have any nasty surprises and one day have a very light blue cell. So that's what you need to know for now for conditional formatting, and I'm pretty sure we'll go back to it in a bit more detail in ah, further class. 11. Inserting a chart: so a few final things I want to do now on this spreadsheets. I want to put a few borders around my totals to make them stand out a little bit more. So I'm going to select those three cells, and there conveniently happens to be a little border preset in the style. So I'm gonna come and click here, and I'm going to select my little total style, which adds a single border at the top, a double border at the bottom. And it puts my cell in bold format, which, incidentally, is over here. If you want to remove it or added on other cells, that makes it stand out a little bit more, which I personally prefer. I'm going to do the same thing on my food and other spreadsheets. So go into the tab. Select the cell that you want to format, click on little style, drop down box and select total select turtle. There we go. All of them have been done. I can come back to my budget cheeks now. What I'd like on this budget sheet is I want to add a title at the top of it. So what? I'm going to do is I'm going to add a few lines, so I remind you, click on them row number and then control. Plus is the easiest. And here I'm going to call it list of ex Penn that sures dash Karen and what I'm going to do now is make it slightly bigger because it's tiny and I'm going to click here on Calibri and select a different font. So just go down the list and choose something that suits your fancy. It doesn't matter much. I just want to make it a little bit more readable than it is at the moment. So I'm going to use this brilliant fault, which I quite like. I'm good to make it bigger, and I'll stop when I find it. Begin off, which would be right about now. Now what I want to do also is change the color of it because I think black is bit harsh. So I'm going to click on the little a underlined in color, and I'm going to select in the same set of blues that I've been using a darker blue, and the last thing I want to do is I want to center this title above all of my cart, Collins. So I'm going to click in Colin A drag my cursor along to Colin D. And now that has been centered. I've got this total button up here which is called Emergent Center, which, when I click on it, will center all of my text into one big cell that has been merged together, starting to look pretty nice. And now the final thing I want to do on this spreadsheet is add a little graph to visually represent how much I have actually spent now to do so. I want to select my description all the way down to various. Now I can let the mask Oh, I'm going to press the control key on my keyboard, which I'm going to keep pressed down. And I'm now going to come and select the actual figures, including the column header all the way down to the various, not taking into account the totals. Now I can let go of my mouse and I can let go of the control key. Now go on to the insert tab on which I'm going to go to select a pie chart, which is down here. I want a three D pie chart, so I'm going to select it by clicking on it. And just like that, you've already got a pie chart, which looks quite small and for which we're going to do a few edits. But at least it's very easy to make, as you can see. So now to move it, I'm going to click on the white area of my spreadsheet, and I'm going to drag it down toe where it fits below my spreadsheet. I'm going to make it slightly bigger by clicking on the little buttons in the corner. And now what I want to do is I want to remove this text, which is below my expenditures, and I want to put it next to the actual pie chart. So what I'm going to do is I'm going to come up here in my pie chart design tab, which is new, which came along when I created the pie chart, and I'm now going to click on the little arrow for the chart styles, and the one I want is this one, which puts all of the, um, expenditure names right next to each slice. Now, as you can see, it doesn't look very good for the moment because it's all overlapping. But we're going to fix that. I'm going to show you how the first thing we want to do is I want to rotate the charts so that it's, um, appears with the slices precisely where I want them. So I'm going to go to the format tab, and I'm going to select my pie chart by clicking on it. I'm then going to click on format selection and over here to the right appears a little window, and I'm just going to move this angle of first slice around until I get the result that I want, which is having the rent slice to the left back lefts. Let's say, Ah, maybe not that far. You're possibly something like that. Trial and error. We'll see how it goes as we go along. And now I'm going to close this little window by clicking the little X because I don't need it, and I'm not going to take every one of my texts and move it to where it doesnt overlap with the other. What I'm going to do here is I'm going to go between amusement and entertainment if my mouse will let me, it won't. Okay, so I'll solve that at a later moment. No, I want to move my restaurants and coffee further out my stationery and art supplies a bit further down. My electricity can be moved over here. I'll leave the telephone right where it is, the various I don't actually need because its non existence my clothing is also nonexistence. At least Azan expenditure. Bring my telephone down here. Click out of my graph and look how it looks. It looks very messy and don't like the result. So I'm going to go back and click on my pie chart again. Click back on format selection and try and rotate it a little bit more to see if that helps any. That might be the best bet here, so I'll go back in. Andi, grab my different boxes one by one and drag them down to where I want them. The telephone can go over here. The amusement can come over here. Actually, my various slice, I'm just going to get rid off. I click and hit on Delete my clothing. I'm going to do the same thing with because the figures this month or empty, which will allow me to bring what, Internet and TV up here, My telephone, my electricity. Then I'll move my food chopping over to the right and that seems pretty good to me. I'm just going to come and change the heading amusement entertainment here. I'm just going to keep entertainment, and that will make the text here easier to read. So I'm gonna come up here on my formula bar, click remove anything before entertainment. Just put a capital E there instead, hit, enter. And as you can see, it will automatically update. My graph is well, if you're not sure about colors like if you don't really like the colors that have been selected for you, you can change them. Now, this rent this big blue box I don't like too much. I would like it to be a different color, so I'm clicking on it so that the dots are around the rent portion and I'm going to come in and click on my little paint parts and I'm going to select a green. Unfortunately, it has not done the data label at the same time, so I'm going to have to click on my data label and select the same green for my data label . I like this better. Um, so I'll leave it like this. I quite like it and I'm going to click outside of my graph. Teoh, remove the selection. If you want to edit it, you just need to click back in the white portion and you can edit anything you want at that moment. So it's very flexible. And if I move back up my sheet, this is what it looks like, which I quite like. And now we're going to be able to start setting up for printing my sheets and having our final result. 12. Printing your final project: So the final thing we need to do is check out what er spreadsheet is going to look like when we print it and then finally actually print it. So I don't know if you remember in the very first lesson I was telling you about these little modes down here, which are the normal mode, which we've been working in the page layout on the page break preview, which we haven't used yet. So what we're going to do is we're going to go ahead and click on the page layout button down here, and what you're going to see is that it's going to show you what your spreadsheet is going to look like when you print it, which for now is going to look as this with you're budgets and actual figures at the top and our little graph at the bottom. If we go into the page break preview, it's going to show us that we've got one page and this is our one page. If we had any more data on the spreadsheet, it would show a za second page and maybe 1/3 page. So this is what those different modes are, and it's a bit more easy to visualize now that we've actually got data entered rather than in the first lesson where we were looking at a blank sheet. So I'll come back into my normal mode. And when I have hit one of these murders, you'll see that automatically on my spreadsheet, our little dots that come down the screen, which are basically going to show me where the page ends. So I've got a visual indicator on my screen that I could add a few more lines if I needed to. And at least that way I immediately know if it's going toe fit on one page or not. Now the next step is to actually going. Go in print are sheet. So I'm going to go to the fall menu and the fourth option, the file menu. Sorry, the fifth option on the file menu is print. It's going to show you which printer you're gonna be printing on in my case and HP inkjet printer, and it's going to show you the preview of your results. Now. The difference between this and the page layout mode that we saw earlier on is that on this one, we don't have the grid lines, which we did have down here on the page layout if you the grid lines are the cell borders, but they're not visible when you print them. So if I go back into file print there, no grid lines, which was why I wanted to put my borders for my totals, to make them stand out a little bit more. And in this case, I don't need to change anything because everything fits my sleep on one page. But if I had needed to do anything, this is where I would need to do it. Now. In this case, I'm going to print only the act of sheet I don't want tohave to print. I don't need to print my restaurant details or my stationery details. They can stay in my spreadsheet for work purposes, but I don't need to actually print them out. I can decide if I want to print one sided or double sided, and then I contain to the orientation. So in this case, I'm good to be printing in portrait format. But if need be, I could have printed in landscape orientation, which is good if you've got several Collins now in this case, you see that if I do that, my graph is not going to hold fully on that page now. If that happens, you have the possibility down here to scale and fit everything. In this case, I'm going to tell it to fit my sheet toe one page. And in this case, I've now got a landscape printing of my spreadsheet with the spreadsheet on Top. The graph at the bottom, which now fully holds on the page. I'm just going to go back and sets it back to portrait because it fits perfectly well in portrait format, and that's all I need. And then I'm just going to hit the print button and set it back into normal mode, because that's the mode I prefer personally. And here's my final results on a piece of paper. So there you go. You should now be holding onto your very own cheetah paper with your list of expenditures and a little grafter represented visually. And I hope that, um, you have achieved your wanted results 13. Thanks: so thanks for folding this class. And, um, I'm going to be making more classes for you to be able to experiment with excel and get attracted. So I will see you soon. And another class. Thank you.