Microsoft Excel Essentials: Level 1 Basics - Master Excel Step-By-Step - Learn Excel Fundamentals! | Alan Jarvis | Skillshare

Microsoft Excel Essentials: Level 1 Basics - Master Excel Step-By-Step - Learn Excel Fundamentals!

Alan Jarvis, Everything Is Easy, Once You Know How

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
57 Lessons (3h 54m)
    • 1. Introduction - Welcome To The Course

      1:43
    • 2. Opening Excel, & Creating A Shortcut

      3:45
    • 3. The Anatomy Of A Workbook

      15:19
    • 4. A Quick Review Of What's Where

      6:11
    • 5. Accessing The "F" Keys (Function Keys) On A Laptop

      0:54
    • 6. Always Do This FIRST! - Save Your New Workbook

      2:00
    • 7. Let's Enter Some Data - Text

      1:41
    • 8. Editing Data - Text

      1:42
    • 9. Ooops. I Made A Mistake - Undo and Redo

      1:20
    • 10. Changing The Appearance Of Text With Formatting - Fonts

      9:03
    • 11. A Quick Word On Formatting

      1:20
    • 12. Formatting Text - Alignment

      3:07
    • 13. Saving Time with AutoFilling Sequences

      4:41
    • 14. POWER USER - Adding Your Own Lists To Autofill

      4:23
    • 15. Saving Time with Format Painter

      2:48
    • 16. Changing The Column Width

      3:33
    • 17. Merge And Centre

      3:01
    • 18. Entering Data - A Couple Of Shortcuts

      3:57
    • 19. Sums - The Old Fashioned Way!

      3:11
    • 20. Sums - Using Autosum

      5:32
    • 21. Copying Formulas

      3:16
    • 22. SUMming Horizontally

      3:16
    • 23. Basic Formulas - Subtraction

      2:28
    • 24. Basic Formulas - Multiplication

      1:32
    • 25. Basic Formulas - Division

      1:21
    • 26. Average Function

      2:41
    • 27. The Order Of Mathematical Operation

      6:38
    • 28. POWER USER - Evaluate Formula Function

      4:50
    • 29. Inserting New Columns And Rows

      6:21
    • 30. Moving Existing Columns And Rows

      2:27
    • 31. Cutting, Copying, Inserting And Deleting

      6:07
    • 32. Hiding Columns And Rows

      2:41
    • 33. ROUNDing Functions

      5:55
    • 34. Formatting Numbers

      2:12
    • 35. A Primer On Building Complex Formulas

      3:10
    • 36. Building A Complex Formula

      4:19
    • 37. Sorting

      4:16
    • 38. Wrapping Text And Soft Enter

      3:59
    • 39. Adding A New Worksheet

      1:07
    • 40. Creating A Simple Chart

      4:20
    • 41. Adding Borders

      2:36
    • 42. Customizing the Quick Access Toolbar

      2:11
    • 43. Simple Printing

      3:34
    • 44. Freezing For An Easier View

      4:06
    • 45. Getting Help

      1:39
    • 46. Filters

      6:21
    • 47. Highlighting Cells

      2:03
    • 48. Closing - Thank You!

      1:30
    • 49. Bonus 1 Whizzing Around Excel Revision

      6:45
    • 50. Bonus 2 Keyboard Shortcuts

      11:18
    • 51. A1 Style - Relative Relative

      2:15
    • 52. $A1 Style - Absolute Relative

      2:16
    • 53. $A$1 Style - Absolute Absolute

      2:44
    • 54. A$1 Style - Relative Absolute

      2:01
    • 55. Level 1 SQA 01 Reverse Engineering A Sample Spreadsheet

      20:55
    • 56. Level 1 SQA 02 Casing And Text Functions

      6:35
    • 57. Level 1 SQA 03 Charts From A Filtered List

      2:54
182 students are watching this class

About This Class

c5cf7978

Master Excel, Step-by-step

Excel: Master the fundamentals of Excel easily! The complete step-by-step guide for complete beginners.

Next to soft skills, being able to use Microsoft Excel well is probably one of the most sought after skills employers are looking for in todays competitive market. Make sure you can compete…

  • Do you want to master one of the must have skills for any work place?
  • Are you willing to invest a little time now for your own future?
  • Want to learn some powerful, little known tools that most users don't know about?
  • Do you want to get ahead of your competition?

If you are a complete beginner looking to master Excel, or you just want to brush up on the basics, you've come to the right place. If you want to earn more money by developing a skill set that todays employers demand, this is the course is for you!

Getting to grips with Excel will give you a distinct advantage in whatever work you currently do, and can open the door to new opportunities. Many of my students have gone on to get better paying jobs, or even start their own businesses, using the very skills you will soon master.

I spent over 20 years mastering Excel, so you don't have to!

In this course we cover in detail all of the basic functions of Excel, and you will learn not only how to use it, but some powerful tools to allow you understand how it works. There is a difference between repeating something and understanding it.

Who is this course aimed at?

Complete beginners, and those who want to build a solid foundation upon which to build their skills.

How is it structured?

Together we concentrate on building a single complete project from scratch, using all the basic functions and give you a thorough understanding. I know your time is valuable, so most lectures are between 3-5 minutes, allowing you to dip in and out when it's convenient for you. This also maximizes your learning, and makes it easy if you need to go back to refresh your memory...no need to click through a 40 minute lecture to find what you're looking for!...all designed to optimize your learning.

What do I get with this course?

  • Lifetime access to over 4 hours of content.
  • Over 50 bite-size lectures, each covering a specific topic in detail. Some of which I have never seen taught before!
  • Downloadable source file, and pdfs that you can take with you and quickly refer back to if you are away from home.
  • Access to the forum, so if you have any questions, you can ask me!

Why take THIS course?

I have been using Excel for over 20 years, but there is a difference between being good at something and being able to teach it. In that time I have taught hundreds of people how to master its power. Because I have taught so many people, I know not to assume you already understand something, and I have probably already heard every question you are likely to have.

This course took over 4 months to produce, and has been tested extensively with scores of people. If something didn't work, or wasn't clear, it was revised until it did. Of course, we never stop learning, and I will continue to develop this course and your skills with your feedback. And remember, if you have a question, post it in the forum and I will answer it for you personally.

Get this course now, and...enjoy!

Alan

5cc3c06a

Transcripts

1. Introduction - Welcome To The Course: Hello. My name's Alan on the creator of this course, Excel from zero to hero of level one for complete beginners. Now, you may be wondering what qualifies me to teach Excel. I've been using Excel since way back in 1993 when version fibers release on dive, using practically every day since not only have I used it, I've also taught others have to use it on. Many of my students have gone on to gain promotion, earned more money using the skills that they have land, and some have gone to create their own businesses. So what you gonna learn in this first course? Well, like all good things, the Masters master the basics first, and that's exactly what this course aims to do. Recover all the basics of excel. Now, I'm not gonna assume that you understand anything about it at all at the moment, So don't worry. You will learn everything in detail. Andi, there's a difference between copying what someone else is doing and actually understanding what it is that you're doing on understanding leads to master race of. That's the aim of this course to give you complete mastery of all the basics and trust may . Many people have been using Excel for years. In my experience, don't have the basics mastered. So what are you going to get? You'll get over three hours of lectures, almost 50 lectures. I think there are. There are many quizzes throughout the course to help cement your learning, and you also get access to me. So if you have any questions throughout the course, feel free to just contact me directly. Or better yet, just posted in the forum at the side of the screen, and everyone will be able to see your question on my response to it. That's it. I hope you enjoy the course. I know I've certainly enjoyed making it, and I hope you get a lot out of it. Thanks a lot. 2. Opening Excel, & Creating A Shortcut: Okay, guys, First things first. We need to open Excel so we can begin to learn about it. Now, I find that the easiest method to do this is to have a short cut on your desktop already. Now I have one just here called Excel 2013. If you have one of these, fantastic. Just hover over it. Double click your left mouse button and you're good to go. If you don't have one of those, Don't worry. I'm gonna show you how to add one to your desktop shortly, right. Method number two. Now, if you already have a workbook that someone has sent you, the simplest method is so literally just double click on the icon of that workbook. You can see we have one down here that I have cleverly prepared earlier. So if we double click this Excel file, which is called a workbook, Excel will automatically open and it will open that particular file. The third way to Open Excel is using your start button. So if you come down here to your start button left, click your mouse button, you may well find that you have Excel already appearing in this first menu here. If you don't don't worry. The more times you open excel, the higher up this list that will become so if it's not there in the moment. Don't worry about it. It will be soon now method for also from the start menu. If you If you didn't see excel in here, don't worry. You can always go to the all programs. Let's go back a little. You can go to all programs left. Click and you'll find a little folder called Microsoft Office 2013. Yours may say 2010 or at major same Microsoft office. If you left click this folder, it will expand and show you all the programs that you currently have installed in the office suite. Just go down to excel. Double click with your left mouse. Andi Excel will open for you now. I did mention that if you didn't have a desktop short cuts already, I would show you a Tad one. We're going to do that right now. So while you are hovering over Excel and it's highlighted, click your right mouse button. Choose send to choose desktop, create short cut left click and then when we moved the menu. We'll see. We have a shortcut icon for Excel there. Now, this is called on my screen Excel 2013 to the reason for that being that I already have Excel 2030 in there. Don't worry about the name of it. If you hover over the icon, right click, choose rename. You can call it anything you want from there. So that's called in Excel Press enter. And there you go. You have a desktop. I come. Okay, The final method, if none of those have work for you yet is again using your start menu. But this time we're going to choose the search programs and files. So if you click your left mess button in there so you get a little blinking cursor on tell effects cell the it will go away, and it will find everything that references excel in your computer. So you're gonna be looking under programs and you'll click it. You'll find Excel listed just there. Okay, So let's open Excel. We can go to our desktop short cut or our newly created desktop short cut double click, and you'll be presented with screen that looks very much like this now, in order to learn about the anatomy of a workbook, We're just gonna choose blank workbook for now, so hover over it. So it's highlighted. Double click with your left mouse button. Andi, you'll be presented with screen that looks very much like this. Okay, next, we're going to cover all of the main areas of this screen and what they do. 3. The Anatomy Of A Workbook: Okay, let's talk about the anatomy of a workbook. And the first thing I want you to notice is that the name of the fall you're working on is always displayed in the top center section of the screen right here they can see it. The moments is, as we have opened a new workbook that Excel is called it Book won by default. If we were to open another workbook, that book would be called Book to Shortly will be covering how to rename the workbook to something more meaningful. Okay, the next thing I want to draw your attention to is this large area right across the sub. Just here. This is called the Ribbon. Now the ribbon is a really useful tool that allows us to do some really cool things in excel. And the ribbon is split into different sections. As you'll see, we have home. We have insert insets. We have page layouts, formulas, data and so on will cover each of these particular elements in more detail was, of course, progresses. But just for now, I just want to remember that this section across the top is called the ribbon righty ho The next thing we're gonna have a quick look at is the quick access toolbar. Now, that's this small area at the top, left hand side of the screen. Just here. Now, at the moment, all we can see is the excel. I come he save. I can't the undo and redo buttons. I'm gonna show you shortly, Hagen it how you can add more icons to that. The things that you use all the time to save. You haven't to go and navigate through the ribbon to find them. The next thing we're gonna look at is at the top, right hand side of the screen. Now we have five little icons. Just up here on the 1st 1 is a question mark on that is the help function for Microsoft Excel. You will see from the little pop up box that's just appeared underneath it, that it will give you a tool tip to tell you what this particular icon does on also in brackets weaken C F one on for the keyboard shortcut junkies out there. F one is the keyboard shortcut to save Yemen to scroll around with the amounts to get to the help function. Just say F one on your keyboard and you're straight into Microsoft's help in this first section of lectures. Will will briefly touch on Mark Soft Excel help and have a look here that can help us. Okay, next icon along is to do with the ribbon display, and we can either auto hide the ribbon show taps, show tabs and commands a recommend leaving that exactly as it is at the moment. Just so you always have the exact view that I have on that will save any confusion a little later on. Okay, the next button along is the minimize button. Now, at the moment, we can see that this is expanded to fill the whole screen. If we were to click the minimize button, it makes it disappear down here to our menu bar down the bottom. Now we have a lost our work. We haven't closed Excel. It's still there, Andi, If we hover over it there, you can see that it's popped back up a little picture so we can either click on it to bring it back or just double click on Beacon will get our work sheep back. Okay, The next one along is the restore down. Now, if we click this, it will shrink the size of the screen that we're looking at. So if you prefer not to work with Excel occupying the whole of your desktop, you can use this and it will shrink it down. Now, if you want to make that a little narrower, you can just come to the right hand side here till the arrow changes left. Click on Dragon or drag out. You can do the same from this side so you can left click Dragon drag out. If you just want to make it taller or shorter, come down the button here, left click, move down, move up. Or, if you want to move in both axes at same time, just go. One of the corners on do that Now Excel will remember exactly that size and that position on the screen. So if we know, go to maximize. So we expand the view to our full screen on, then we choose to restore down. It will go back to exactly the settings we just so exit. Okay, let's go back to full screen and finally the little X up the corner is too close now Don't worry. If you've made any changes to your workbook and you hit close accidentally, you'll get a pop up message that asked you, Do you want to save this file? So don't worry. If you accidentally click it, you'll have an option to get out of it. In case you didn't mean to click it, the next area of the screen will look at is the formula bar. That's this area right here. Now, as its name suggests, this is a place where you can type formulas, but you can also use it to input your data. Be dates, text numbers, whatever you want. So there are essentially two ways of entry in data into excel, either directly in the cell, which will come to short length or in the formula bar, which is this area right here. Don't worry too much about what we're going to do with this at the moment. Just remember that this area is called the Formula Bar. Okay, the next area is the worksheet area, and that's this area highlighted just here. This is the main area that will be working in an entry. Now, data and I have formulas into Andi. It's made up of lots and lots of little cells, and we'll cover ourselves now. So cells Excel is made up of thousands and thousands of cells and cells are these little rectangles that you can see just here. Now a seller's referred to by its column letter, and it's road number. So if we were to select to sell at random here, we can see that this cell is at the intersection of column K and wrote six. And you can see that just here in the name box. So similarly, if we were to click over here, we can see where at the intersection of column Double A and Row 29. Now, this is easy enough when Europe here and you can quickly see the intersex. But if you move, why over to the screen it becomes more difficult to look down the column and then look over at the row. So using the name box up here is a very useful way of finding out exactly which sell your in Okay s. So that cover sells next, we're gonna have a look at the name box. Okay. We refer to the name box just a few seconds ago. Andi, that's this area right up the corner. Just here that the name bucks has a couple of functions. The 1st 1 is to identify which sell your in very quickly and very easily. It has another really useful function which will come to later in the course. The next thing we're gonna look at is how we actually move around the worksheet area. Now we have several options. To do this. We can use the arrow keys, which you'll find on your keyboard and look something like this. And obviously you can use the up arrow to move up one line to time or the down arrow to move down one arrow a two time. Similarly, you can use the left arrow to move left on the right arrow to move right. If we want to move around a little more than that over to the far right hand side of the screen, you can see a scroll bar that's this area just here. Now that the scroll bar you can either left click with your mouse on, drag it down and up, or you can use the little arrows at the top on the bottom, the bottom area. When you click, it will take you down one row at a time on the upper will take you up. One wrote a time now, similarly at the bottom, right hand side of the screen. We also have the left and right scroll bar, which is this scroll bar just here and again. You can left click and drag left or right to move left, all right, or you can use the little arrows on the right hand side. Moves you over one column at a time on the left hand side. You could move over one column to the left each time. Okay, now Excel. Workbooks are called work books and just like a normal book, a book has multiple pages, and so can an Excel workbook. Now, at the moment, you can see that by default. Excel when we open. This file just gave us one and cheat. In the olden days in Excel 2003 possibly 2007 and 2010 we had three sheets by default, but at the moment in version 2013 we just get one sheet. That's this one just down here. You can see at the moment there is a little plus button at the side. So if we want to and another sheet toe a workbook, we simply left. Click the plus button and we get sheet to on. We can keep clicking this to get sheet 34 and five on. That is simply have you add extra sheets to your workbook. Now, at the moment, you may not see the need for multiple sheets, but it will become clear little later on in the course of three progress. But for now, let's just delete those sheets we've added by right clicking choosen delete and we'll right click Delete right click delete right click delete. There you go and we're back to where we started. A very useful thing to know is how to save your work in Excel on. We're about to cover three different ways of saving your work. Okay, at the moment we have a workbook with no data in whatsoever. So we'll go into the quick access toolbar just up here. You'll recall we looked at it a little bit earlier on. We'll click on the save icon. So when we click this ah, I come, this box will pop up now, At the moment, it's a save as because we've not previously save this file. So all we need to do is pick out path. I'm not going to use one drive. I'll go to computer, I'll choose desktop. And here this. This meant this dialog box will ask us to pick a new name for our file. So let's just call it something useful, though my workbook isn't particularly useful will call it that. Ignore the other options for a moment. Just name the file, something that you can recall hit safe and they go, our file saved. That's the first method the next method will use to save our work is using the ribbon that we spoke about little earlier. If we move up to the top left, you can see by default. The home ribbon is displayed on right next to it. On the left, you can see the foil ribbon. If you left, click with your mouse button. You'll come back to this area just here. What you want to do is scroll down to save left click amega. Your work has now been saved on the final method for saving. Our work is to use the keyboard shortcut. Ctrl control button and s. You can see if we hover over the save option just of here in the quick access toolbar. It will cleverly tell us in the tip box that control and s is the keyboard shortcut to save our work. Now, you won't actually see anything happen when I do this. But when I hit control and s that slight flash of the screen just indicated the Excel Understood what we said on our work has just being saved. Next, we're going to cover hair to save as that is renaming a workbook from one file name to a different one. Okay. Save has as a couple of different functions. No, at the moment, we've just saved this workbook as a workbook called my workbook. Now, we It could be that we continue working on this, but we actually want to keep the original file and keep the new file under a separate file name. That way we can always go back to a previous version of a file if we find that something has gone horrendously wrong. So now we're going to cover save as now again. We have a couple of different ways of doing this. The 1st 1 will use is the file menu. So come up to the top. Left hand side, left click file, Come down, choose save us again, specify your path. I'm gonna choose computer Desktop and we'll get this little dialog box and we can see there that we already have my workbook, the one we've just saved. But this time, what we're gonna do is we're going to call this my workbook to which is a very creative name indeed. Again, don't worry about any other options. For now. Just change the name of the workbook and save as a different workbook. Now we still have my workbook, the original version still intact. All the days that we entered in there, even though we didn't enter any data in there is still there. And it's fully protected and will stay there until you delete it. But now we have successfully rename the file as my workbook to okay, The next method will use to save, as is the keyboard shortcuts. Because at the moment, in the current view, we can't see a save as option. We can't hover over it and have excel. Tell us in a tool tip What the keyboard shortcut is. So at the moment, I'm just going to let you know that the keyboard short cook for save, as is F 12 and that's the function key on the top of your keyboard. So if I hit F 12 just now, you'll see that we get the same vast dialog box pop up again. We can just nip in here, change the name of their worksheet, ignore the other options from now click Save, and we've no successfully saved as a new workbook name. Okay, the final thing I want to point out to you is you. You may find that the cells are a little small. It looks fine at the moment, cause the data's completely that this spreadsheet is completely clear. But as we progress and we get lots and lots of data in here, it could become difficult to read. So if you look down to the right hand side of your screen, you'll see a little slide out with a minus button on the left on the plus button on the right. Now, at the moment our view is 100% on. We can see columns A through a C. We can see lines. Want to 39 down the side there. If we want to zoom in to make the data a little bigger, weaken just left. Click on the plus button and you can see tells us zoom in, and that will make each of the cells a little bit larger. You won't be able to see as many columns or rows, but it may make the date or a little easier to see. Similarly, you may want to zoom out just to see how big your data is on by left click in this bar, you'll come out and out, probably to a point where you can actually read the data anymore. Alternatively, you can left click this little bar in the middle and either move to the left to zoom out or move to the right to zoom in and they go. That should make it a little easier if you to see. Okay, there you have it. That's the detailed anatomy of a workbook in the next short lecture. We're just gonna have a couple of minutes just as a quick review. So if you ever need to come back to it in the future. Just go to the short version and you'll quickly be able to find all the different areas of the workbook on what they're called. See you soon. 4. A Quick Review Of What's Where: Okay, guys, welcome to the quick Review off the anatomy of a Workbook lecture. Now the first thing you'll notice at the top center of the screen. Just here is the Excel displays the name of our current workbook here. Now, you'll recall from that lecture that an excel file is referred to as a workbook. And this is where you can see the name of the workbook you're currently working on. Okay. Moving over to the top left hand side of the screen, we can see how quick access toolbar that's this area right here. Now, by default, the quick access toolbar only displays a few icons. We can see we have the Excel. I come, we have the save icon, which we can use to save our work. We have the undo button, which is very useful. If you make a mistake when you're entering data or formulas on immediately to the right of that, we can see the redo button. So if we decide that we actually liked what we just did and it wasn't a mistake after all, just left, click on the reader button on that will restore you to the state that you just undead. From now moving over to the top right hand side of the screen, you can see that we have five little icons. Now. It's worth remembering in Excel that whenever you hover over any particular icon, a tool tip will appear just like this one here. That gives you a clue as to what this Army can actually does. Now, if there is a keyboard shortcut that will also be displayed in the tool tip so we can see here that the question mark is the Microsoft Excel help function. And you can click on that any time you need help. Next along, we have the ribbon options you can left. Click that auto, hide the ribbon or show tab, show tabs and commanders immediately to the right. Of that, we have the minimize button, which, if we left, click without most will make this workbook disappeared and where toolbar at the bottom. I'll just show you that now so we can click that we haven't lost their work. It's still there. Onda automatically weaken, Weaken! Just come down here to the toolbar, hover over it without mouse. We can either left click the little picture that's just popped up above the till bar or double click. The little icon in the toolbar on the workbook is fully restored. Next to that, we have the restored down and maximized. But if we restore down, you'll recall this shrinks excel so it doesn't occupy the full screen. Andi, immediately to the right of that is the close button on that closes Excel. Don't worry if you hit that accidentally, If you haven't saved your work, it will give you an option to save your work before you actually close the file. So you're not gonna lose any work at all. Okay, now onto the ribbon. The ribbon is the place where you can go to find all the functions that you're going to use most frequently their groups into different categories, which you can see by the names of the tabs along the top There. So we have file which will contain all the options relating to file, such as saving which we've covered, and save as which we've also covered the home ribbon displays, all the things that you're likely to use most often. We also have insert page layout formulas at each of these contain things that are pertinent to that particular title. Now, if you find that there is a particular function that you use in any of these ribbons and you'd like to have instantly available, we can always add that to the quick access to or by a little later on. And we'll have a lecture that shows you just how to do that a little later in the course. Okay, immediately below the ribbon. We have the formula bar, and you'll recall that the Formula Bar is one of two places that we can end today to in Excel became the main area of the workbook that we can see highlighted here is the worksheet area, and this is where we'll be doing most of our work in Excel and the word cheese area is made up of multiple cells those of these little white rectangles that you can see just here. You'll also remember that the cells are referred to by their column, letter and row number so we can see here that the cell selected is I six. Now that's pretty easy to see if we're working up the top left hand side of the work cheat . But if we work in way, way over to the far side. It becomes more difficult to see quickly, which is why we have the name box just here, which will instantly tell you which sell your in there to move around a workbook. We can use the scroll bars on the far right hand side of the screen or the bottom right hand side of our screen, the right hander side scroll bar. We can either left click to drag up and down. We can click the little down arrow to move down. One sat one row at a time, the upper row to move one cell up at a time, and to move left and right. We can either use a scroll bar down the button just here, or use the right hand arrow to move one column to the right or the left and arrow to move one column to the left, just below the left or right scroll bar. We can see how zoom options now this will let us alter the number of cells that we can see . In our current view. You can see at the moment we're at 100% but to increase this, we hit the little plus button on the side there to increase our view so we can see less cells or click in the other button the minus. On the left hand side, we can see more cells, but that decreases our view. Alternatively, can hold this little bar here, hover over it left. Click with your mouse on dragon and abs until you get a view that you're happy with. And finally we have a work sheet and we can see down here that we currently have one cheat , which is imaginatively named Sheet one on to add new sheets to my workbook. We just left Click this little plus button just here, and we can keep adding chiefs of the current workbook as many times as we want to. So they go. That's a quick review of the anatomy of a workbook. Look forward to seeing you in the next lecture. 5. Accessing The "F" Keys (Function Keys) On A Laptop: Okay, guys. And welcome back. Now, this is gonna be a very quick lecture. At some point throughout this course, I'm gonna I'm gonna be referring to function keys or F keys on. I know that some people struggle if you're working on a laptop. So if you're working on the laptop, you access the function keys by pressing and holding down the F N key. That's this one here. Eso press and hold that and then hit the buttons across the top that say F one F two after a away through nice and simple. So if you're on a laptop or you have one of these jazzy keyboards like I'm using, just look down the bottom left hand side for the F m key press and hold that and then hit the F keys across the top of the keyboard. Okay, I'll see you in the next lecture. 6. Always Do This FIRST! - Save Your New Workbook: okay, We're about to create a very first workbook from scratch. Now you can see that I've opened a new blank workbook here which Excel is called Book Won by default. Now, very good habit to get into whenever you begin. A new project in Excel is immediately rename the file or something more useful. So in a few weeks time, you'll be able to instantly identify it bites file name. Okay, So the first thing we're going to do is to rename a file to something useful. And to do that, we're going to use the ribbon just up here on We're going to select file by left clicking and you'll be presented with this screen. If you ever click this button by mistake and you don't know how to get back, just left. Click the little back arrow just there and you'll come back to your workbook. OK, so left click file move down the options to save as left click I'm gonna choose the path of my computer by left clicking I'm gonna choose desktop and here we can see we have the save as dialog box. We can see the current file name is Book one and what we're going to do is type over that and choose more useful name of my fruit and veg shop. So my fruit Andi veg shop I wish I could look at the street And for that my fruit on and veg twice my fruit and veg shop they again. Okay, if you decide to change your mind, just hit the council and you can come back and get their attending time It all. But at the moment we're happy that it's gonna be called my Froome Benschop. So we're gonna hover over, Save left, click on. There you go. A workbook has no being saved on. We can see that the new titles no displayed at the top center of the screen in Excel. Fantastic. Okay, the next thing we're going to look at is have you into text 7. Let's Enter Some Data - Text: Okay, let's learn how to enter data into excel. We have two ways of doing this Primarily, we can neither enter the text directly into the cell or we can enter the text into the formula bar. We're going to cover both options here on. Firstly, we're gonna select cell a one by left clicking it on. We were just begin typing on a keyboard, the title ever worksheet, and we're gonna call it my fruit and veg shop again. So my fruits, Andi lips veg shop, they go Now you may have noticed that the same text is also appearing in the formula bar as we're typing it. So if I just show you there and just randomly tope start type in some more techs, you can see that it's also appearing in the former about the same time when we're happy with what we've typed just here. Enter on your keyboard and they go, The data is in there. Okay, so that's how you enter data into a cell. Let's delete that on re do the same thing again. But using the formula bar, we're again, we're gonna have cell a one selected by left clicking on it but this time going to come up to the formula bar, you can see the tool tip there has told us what that were in the formula bar left click till we get the flashing cursor on and type again my fruits and venture up and you can see that it also started filling in cell a one as we were typing in the formula Bar highlighted just there again. When we're happy, press enter. And there you go. Very simple. That's how you enter text into excel. 8. Editing Data - Text: Okay, We've learned how to enter text in Excel, which is very straightforward, but we may want toe edit the text we've just entered. Now we have a couple of ways of doing this, the most obvious being selecting the cell hitting delete on your keyboard and wiping out the cell completely. But we may just want to at least a part of the text that we've just entered. Now we have a couple of ways of doing this. Justus. We did with Enter in text. We can either do it in the cell or we can do it in the Formula Bar, and we're going to cover both of them now. Okay, we'll do the cell first will left click the cell to select it, and then we can either double click left mouse button, and you can see that we now have a flashing cursor inside so we could scroll over and change our title to fruits and veggie shop hit. Enter and there you go. That's one. Let me just move the cursor. They go. That's one way of editing your texts by selecting the cell and double clicking. Alternatively, you can select the cell and hit F two on your keyboard like that, and you'll instantly get your flashing cursor at the end of your text so you can then scroll over. Use in your arrow keys. Onda delete whenever you want to do or make. Whatever changes you need to hit, enter and you're done on the third and final way is too slight the cell that you want to read it. Come up here to your formula bar, and you can add whichever text you want to add in just up here. And that will also edit the text you just entered. Okay, there you go. That's how you edit text in the cell. 9. Ooops. I Made A Mistake - Undo and Redo: Okay. Now, is this good a time as any to cover. What do you do if you make a mistake in excel? No. In the last lecture, we edited the title of a worksheet to say my fruit and veggie shop on then a bunch of God gobbledy goop straight after it. Now, obviously, we don't want all these GSG Hmm? Various letters in there because we've made a mistake. So what we can do is we can use the quick access toolbar right up here and select the undo button. Now, by hovering over the undo button, you can see that you get the tool tip that tells you what the function is. And you can also see that the keyboard shortcut of control and zed is highlighted. Eso if you left, click with your mouse. The undue You can see that that gobbledygook has been removed. Andi Tyler's room restored to what it was before. Now. Similarly, if we move over to the right, we can see the redo button on the redo. It performs exactly the opposite function. So if we hit the redo, it will take us back to where we were just a few seconds ago. We wanna undo because we like the type of our fruit and veg shop as it is on. Do you go? That's how you correct a mistake if you make one in excel. 10. Changing The Appearance Of Text With Formatting - Fonts: Okay, We have a title for a worksheet, but it looks a little boring at the moment. It's just in plain or Calibri 11 on. What we're going to do now is learn how to format our funds. So the first thing we're gonna do is select the cell that we want to format. So just selected cell a one, and there are a couple of methods to do this. The 1st 1 we're gonna look at is using the home ribbon and specifically the font section of the home ribbon. Now, we have a few options available to us here. Not all of the options are available to us, but we'll come to that in a little while now. The first thing we can see is that we have a calibri currently selected. So with the sole selected, I'm gonna choose this little down arrow button just here. And now every form that is available on your system will be listed in this in this little scroll, but scroll box here and notice what happens in cell a one. As I move over each of the different font names, you can see that in real time the phones is being updated in our spreadsheet, so you can get a pretty good idea of what your text will look like if you were to choose any particular funds. Now, at the moment, I'm gonna stick with calibrate Now, there is a quicker way of doing this. If you know the name of the fund that you want to use, you can just come up. So here, left, click hit delete To get rid of the Texas in there and say we wanted to use times new Roman , we could we could literally just start type. In times they go and it's already said times New Roman site enter on, they go. Our text has changed to times New Roman. No, actually, like a livery. So I'm gonna go back to Calibri Calibri. So we're back a clean break. Now the next thing we may want to do is also the size of our text. Then we have three ways of doing this from the home ribbon. We can use a little drop down box here, left click on again noticing the sell. What happens? We can see that the font size is increasing as we scroll down and it will decrease as we scroll back up. Um, let's just leave us. It's 11 for now. The other way is to click this little button here, which is the increased phone sized button on for each click that we give it with our left mouse. You can see in this window here when l 2 14 Now we're up to 16. So that will increase our font size by one of the uniform measures each time that listed here. Okay, we can decrease our fun size by clicking this little button here, and each time we click it, our front gets smaller. Now you may be thinking, Well, actually, that there's a big gap between number 28 number 36 actually want to phone size of 32. That's fine. You can have a fun size of 32 by coming into this little box here. Left click until you get the blinking cursor hit backspace to delete the number that's in there and just type in the number on your keyboard that you want. I said 32 didn't. So let's say 30 to hit Enter and they go l font sizes No. 32 which isn't a standard form size listed here, so you can even have decimals in there. So if we wanted a 27.5, we just talked. 27.5 hit, enter, and they go out of font sizes. No, 27.5. I actually, like 22 4 titles, so let's go back and change that 22. Okay, Next three things we're gonna look at are the bold, italicized onda underlined, which you can see are quickly accessed by using the B button on. As we hover over it, we can see the tool Tip tells us that this is bold and that the keyboard shortcuts his control and bait. Next to that, we have I, which stands for italics on We can see there that the keyboard shortcut is control. And I andi underlying, which we can see is the keyboard choker is control. And you now you'll notice that the minute that all of these are Weiss in background until I hover over them if I actually just want the text to be bold So I'm just gonna left click bold. We can see that that the text is being molded, but as I move my cursor, you can see that the B has remained highlighted. That's a very useful way of finding out which formats have been applied to your text. Okay, so have a play around with those and make it bold making underlying to tell a size or any combination of those three until you get a look that you're happy with. Okay, for now, I'm going to skip over this option. I'm going to skip over this option and I'm gonna come straight over here to the font color . If I select a little down arrow, I have a range of theme Kalis that I could choose from. And as I roll over them you can see that the you can see that cell a one is changing to match whenever I'm hovering over, have standard collis are available across the button. Just here Now, this should be good enough selection for you. But if it's not, don't worry. Just left. Click the more collis and you'll come to this option here so you can see all of your standard colors. You can see your current color is black and if I select this rather nice pinky color, the new color is pink If I clicked. Okay, a text would go pink. If this isn't a large enough array of colors for you, you can hit the custom and you can left click and you can move around and literally select any color that you could create far too many only Click Cancel for now on the left, Click Next. I quite like the green. So they go with no successfully made a text green. We've made it bold and we've increased the font size to 22 which looks much better as a title. Okay, that's how you do that Use in the home ribbon. The next method we're gonna look at is by right clicking in the cell itself and choosing format cells. So right, click the cell. Choose format cells left, click. Let me just drag that into shot. And we now have the format cells dialog box which has now been displayed. Now you can see across the top that we have six sections. Here we have number. We have alignment. We have fund, which is the one we want to play with. We have border Phil and protection. Don't worry about the other five. For the moment we're just going to stick with front Now You can see here that everything that we have displayed in the ribbon is displayed in this box too. So we can see that we have Calibri Kelly Brick Nager. You can see the no text is bold id on. We can see by the highlight there that the Texas Bold We can see that we have a fun size of 22 which is reflected just here. We can see we have no underlying on that are colored green. Now the things that we get access to by going into it this way is we can also get to strike through superscript and subscript. Now, if I want to make a change in this particular window, it will not be updated in the cell until I click the OK. So just to demonstrate, if I chose red, we can see in the preview window just here that the text will may be red, but it doesn't actually show is ready in the cell until we click. OK, so let me just go back and change that to Green. As I quite like it, we'll use the preview window to see what these different effects do so if we left click the little tick box their strike through, we can see that we get a line through our text left click again to remove it Superscript raises the text above the normal line Left click to remove it on Come down to sub scripting Probably guess what matters left Click and it makes the script subscript And I'm gonna left click just to undo That's I quite like it the way Liz Okay, so hit. Okay, when you're happy with any changes that you made and away we go there The final method of format in your font is some combination of the two. If you look very closely at the top right hand corner of just zoom in here, Okay? If you look very closely at the bottom right hand corner of the following section of the ribbon, you'll see this little icon. And if we hover over it, we can see that we have fun settings. So if we left click this little icon immediately, have format cells, dialogue boxes popped back up, and it's automatically going to fund, which is exactly what we want to play with. Because we came from the fund section of the ribbon on again. We can get access to the subscript and the superscript and all these different options. If this is a way that you prefer to do it personally, I prefer to use the ribbon because you can see the changes in real time happening in your spreadsheet. Where's with this? You just have the little preview window. It's a personal choice thing. Have a play around with both. See what you think in whichever one works for you. Use that one. OK, that's funds. Next, we're gonna have a look at alignment. 11. A Quick Word On Formatting: Okay, guys, we just talked about formatting text in the last lecture. But just to be clear, what we've actually done is formatted the properties off the cell itself. So we had to play around with cell a one on. We made the phones. Let's have a look. We made the phone green. We increase the font size to 22. We made it bold, and it looks very good indeed them at the moment, we just have text in that. But because we formatted that cell rather than the text itself within the cell, if we were to go and select the cell now an over type. So let's say we just put a bunch of numbers in there. 1234 five. You can see that the numbers, despite the fact that it's no longer text, it's now a different data type, has retained the formatting that we just set for that cell specifically. So just one thing to remember when you format a cell B number, speed funds or anything at all, the cell itself retains that format in, So if you ever over type and you change the data type from text to numbers from numbers, two dates or any combination of those. The cell itself will retain that formatting until you change it. They go Now let's move on to lining text. 12. Formatting Text - Alignment: right, Let's take a quick look it alignment now at the moment, just so it's clear what change in the alignment settings does. I've made column F wider than the other columns, and I made Row three taller than the other columns. Xolo shy to help us as much as it can by using its default settings. Now it does this very cleverly when it comes to entering data in cells by looking at the data type and automatically apply in the appropriate format. So for text, it is always aligned to the left hand side of the sell by default. So we just talked some texting. Hey, center, we can see that it's on the far left hand side of the South. Okay. Similarly, if we talked to number in, although at the moment it's appearing on the left hand side of the cell, assumes we hit Enter Excel will work out that it's actually a number on a line it to the right hand side of the South. These is the default settings, but don't worry. We can have the we can have the text appearing wherever we want in the cell and the way we're going to do that is by looking at the home ribbon on the alignment section. Just here. Notice that the only option that selected by default is the bottom line, Which is this highlighted? One. Just here. Now we may as well go across the top while we're here, so if we click the next button across the middle line, you can see the text jumps up to the middle. If we choose the next one across top, ally moves to the top on below that we're dealing with the horizontal axis so we can move over to the left. We can have it in the center, or we can move it over to the right again if we want to. Text to appear dead center. Just select center from the bottom and middle from the top and their text appears in the center. Okay, let's go back over to the right. Let's leave it there now. At the moment, you'll see that writer line has been highlighted. So what that means is that we have just overridden the automatic default settings that excel would normally apply. So here, if we type some text in normally, it would leap over. So the left hand side. But no, because we've overridden that default setting, it's staying on the right hand side. So that's the way to tell if you've actually selected the format or if it's the default. If it's if all of these three are not highlighted, you'll get the default options. If you've highlighted one that will override your default, okay, that's how you change that use in the home ribbon on the alignment section. The other two methods are exactly the same we talked about when we learned has a format of found. We have the right click option. So click in the cell right click Choose Format sells the format cells box pops up on select the alignment tab if it isn't already selected, and you get mostly the same options here is. Well, I'm just gonna close that on. You'll recall that the short cut to get to that is this little icon just up the corner here , which, if you left click causes the same box to open, have a play around with the different sentence. Have a lot of fun, as much fun as you can have with a line in text in cells, and I'll see you in the next lecture 13. Saving Time with AutoFilling Sequences: Okay, guys, shortly we're going to be entering the headings for all of our data. But before we do that, I'm going to show you a neat trick in Excel. It's gonna make your life a lot simpler. I mentioned a little earlier that Excel does whatever it can to help you on one of the ways it does. This is by auto filling sequences. I can best demonstrate this by actually showing you how it does it. So if we would type Monday into any one of our cells and hit enter when we go back up to the cell that say's Monday, If you look very closely at the bottom right hand corner, you can see this little block. If you have your cursor over there, you can see that the curse of changes. If you left, click your mouse button there and drag across, you can see that it automatically works out that the sequence should probably be the days of the week, and similarly, we can also go this way on dragged down one day Tuesday, Wednesday, Thursday, Friday, and as soon as you let go of your left mouse, it automatically populates the sequence for you. This will save you a lot of time having to type in Monday, Tuesday, Wednesday, Thursday, Friday, and so on and so forth. Okay, let's just delete these just here. I'm just highlight and then hitting delete on the keyboard, so you'll notice that I typed Monday in full. If I would just hope just mum and come back up to the cell and again go to this little box at the corner, wait till the cat cursor changes left, click and drag down. You can now see that it's also populated the sequence automatically, but because I abbreviated Monday, two months, it's automatically abbreviated Tuesday to chew Wednesday toe when? Thursday to Fu on DSO one and so forth. Okay, so we know it could do the days of the week. Let's come over here if you were to type, not jam Jum and hey, center and come over here. It will also work out that what we probably want to type is a sequence of the months and again, you see, because I abbreviated January, you can see it's automatically abbreviated all of the names of the month, right? So that's fairly easy, because there are very few things that would follow Monday other than Tuesday. One Excel needs if we're working with numbers, is to spot a pattern. And to do that, it needs to see at least two entries to work out what that pattern will be. So if I come into this cell just here and type one If I were to try to also fill now by Dragon, then you can see we just get one more, more, more, more, mom one all the way down. But if I were to Cyp, let me just delete that, Okay? If I were to type one and then type two on, highlight both the boxes, either by left clicking and dragging over them, or click in the first on Hit and Shift and Down Arrow to highlight them on. Then I hover over this little box here you can see that the curse has changed. Excel has no worked out that what we're doing is increasing by an increment of one each time and consequently it will automatically populate 12345 all the way down for us. Far as we highlight now, it can also do things a little bit cleverer than that. So if we were to type two full, it would also work out that what we're doing. His increase in my in increments of two each time. So when we dragged down, we can see that we'll never get 2468 10 12. This could save you an awful lot of time if you have a lot of sequences of data that you need to enter, Okay. Now, not only does it auto populates sequences going down and going over to the right if we were to tight Monday in here on, then we re select the cell, come to this little box up the corner, hover over it until they occur. So changes we can also go up, and it would populate the proceeding sequence. And similarly, if we would just like Monday and drag over to the left, we can see that we know get some day and then a Saturday. So any sequence, no matter where you start in your workbook, you can either drank down using this little box up the corner and left clicking. You can drag right, you can drag left and you can drag up, and it will automatically populate the sequence. The only thing to keep in mind, obviously, is if you're doing a sequence of numbers such as one and two, that you need to highlight both of the cells on. If you want to populate the proceeding sequence left, click and drag up and you can see that will go to zero a minus one. And it gives you a hint as to what it's gonna talk in that tone, a little box that's appearing just there. So they go. That's a very quick way to enter a bunch of data that's in a natural sequence in Excel. We're going to use that, you know, next lecture. When we put in the headings for our new table, so you and then it clicked you. 14. POWER USER - Adding Your Own Lists To Autofill: Okay, guys, In the last lecture, we have a look at the auto fill sequence, and we know that we can use that very quickly to populate the days of the week all the months of the year. But wouldn't it be particularly useful if you could add your own custom lists? Well, luckily, you can So imagine for a second that if you manage a particular department and you have half a dozen members of staff from 10 or 20 members of staff rather than having to tighten their names out individually in a column every time that you have to report on something, you can just add them to your own custom list type. The first name usually drag handle, and they're automatically populated. Well, luckily, it's really easy to do on. What we're gonna do is we're going to use the file ribbon, which is just that the top just here. We're gonna left click on the file option. We're going to come down to options just down the bottom. Here, we're getting a left click, and then this little dialog box is gonna pop up. You see, we have another bunch of options down the left hand side on. What we're gonna do is we're gonna come to the advanced option on left. Click on this window is going to open up and see that we have a scroll bar. What we want to do is left, click and drag all the way down to the bottom until we can see edit custom lists. So just left click the edit custom lists and they go, you can see the built in ones that are already there in excel. And what we're going to do because we have new list added, is we're going to click, add, and our curse is going to jump over to the list and interest. So I'm just gonna pause for a second. I'm just gonna answer a bunch of names in there, and then I'll come back to you in just a second. Okay, There you go. So I've just talked to a bunch of names there. So we now have Tracy, Emmylou, Orville, Ricardo, Maurice, Mike am Belinda. So what I'm gonna do now is click add and you can see that net. That list is no populated over here. We know custom lists. We're gonna click, OK, and then we're going to click OK again, and then we're gonna try. And so Tracy was the first person in our department. So let's just send to Tracy in there. Hit, enter, then re select the cell. Come to the drag handle. Left click drag down on. There you go. You can see that all the members of our department automatically populated. And then when they get back to the beginning, their repeated again, just like the days of the week and the months of the year. Okay, so that's one way to add a custom list into the custom list feature of auto fill. There's also another way to do it. Let's imagine that you have to report frequently on a bunch of departments in your company . So let's go ahead and add a bunch of departments for our company. Okay? I'm just gonna pause for a second while I typed them in. And then I'll come back to you in just a few seconds. Okay, So what I've done have just entered a bunch of departments at random in the cells here, So tight warehouses transport a camp sales tech support marketing human resource is in the States and then what we're gonna do is we're going to use that list to build our own custom list game. We're going to go to the file option We're going to choose options again on we're gonna choose Advanced again. And this time I'm going to scroll down. She was edit custom lists on this time. What we're gonna do is we're gonna click this little symbol just here. So we're just gonna left click there, and we're gonna drag a box by left clicking and dragon with almost You can see that that ranges being selected in here. So now what we're gonna do is click this little button and we can press the import and you can see their lists No, been imported into our custom lists. So again, just here. OK, just hits. Okay again against you know what we're gonna do to test it out? We're gonna type in warehouse on. We're gonna left click on, we're gonna drag down and they go. You can see that all of the other departments have been populated automatically because we've already added that to the list. We can go ahead and quite safely delete the original source. The one that we imported. And we can find out that if we type in again that that should still work. So it's just left, click and drag, and they go nice and using. Now, the one thing you may notice is that here while I talkto warehouse, I typed it in lower case. So it didn't have a capital beginning on automatically. Excel suggested that so that all in lower case. But here I talked with the capital of the beginning, so accept automatically capitalize all of those interests. So there you go. That's how you add your own custom lists. Enjoy. So you in the next lecture. 15. Saving Time with Format Painter: Okay, Now we're gonna enter the headings for our data on. We're going to do this by simply coming down to sell a three and type in the men. Now is this is a fruit and veg shop. The first thing we want to see is what? The items going to bay and the next thing we're gonna record of the days of the week. Now, you'll remember from the last lecture that if we took the first day of the week, we can come up here till the curse of changes to the Black Cross left quick with their most button and drag it across until we get Monday. Tuesday, Wednesday, Thursday, Friday. There you go. So then we have the days of the week, and at the end, we're gonna put our total. So they go Now we have our headings right now at the moments just like our title was originally these just in plain black text and we want them to look a little jazzy s. So what we're gonna do is we're gonna change the format a game this time. We're gonna come up here, we'll choose blue. I think on we'll make the front bold. Yeah, that looks pretty good. Okay, obviously, we don't wanna have to go and repeat that exercise for every single one of the cells on Monday. Tuesday, Wednesday, Thursday, Friday. So there are a couple of things weaken Do, using this tool up here in the home ribbon on this is called the former Painter. And you can see from the little pop up box that it not only tells you what it is, but it also tells you have to use it. So if we select the cell with the formatting that we want, come up here left, click the former pain so we can see that we have the dancing ants going around this particular cell. Now all we need to do to paint that formatting on to the next cell it's just left. Click that cell and then we get Monday, and it has all the same formatting that this cell had, the one that we carpet. Now that does save some time. But we can save ever more time by copying it again, but this time applying it to the entire line by clicking the number three because we're on line three and then you can see that the formatting from Cell one has been copied across all of the headings that we've entered. And if we were to type something else in here, anything, anywhere on this line, that's all. You can see that that for matters being retained. So there you go. You don't have to redo your formatting for every single cell. Just get the one cell correct. Hit the format painter with that soul corrected, and either select the individual cells that you want to format or format in the entire line . A little copy that formatting right the way across and they go. That's one quick way of saving time in Excel. Next, we're gonna have a look at, have to change the wits of the column so we can see all of their attacks clearly. Okay, so you in the next lecture 16. Changing The Column Width: Okay, guys, we're progressing nicely now. You may have noticed that all of our columns across the top here are all the same wits on this with this perfectly fine when we look at words like item Monday, Tuesday, Friday and total because the words of fully visible Thursday just about scraping through Wednesday completely hopeless because we've chopped off the last two letters. Now we have a couple of ways of re sizing the columns. One method would be to hover over the letter right click with the old house, come down and choose column width, and we can see that at the current column with this 8.43 If we were to just type 15 over, that should be roughly twice as big on we can hit, enter and they go. All of column D is no being resized. That looks much better, but perhaps in their looks at just a little bit too white Now, luckily, Excel has a built in feature that makes this very simple to do for you. Currently, if we because Wednesday looks too wide. If we hover between columns D and E. Until the cursor changes to this symbol here double click with your left mouse button. Excel will look down the whole of Colin D and automatically resize to the most appropriate width. Okay, so that's how you do. One column. It's time. If you want to do two or three columns, just literally left. Click on the column heading. Move over as many columns as you want to go. Double click between any of these column letters, and you can see that all of those columns have never been automatically re sized. Okay, so far, so good. There may be that you actually wants all of your columns to be the same wits, but larger than the standard with which was 8.43 And the easy way to do that is to well, first, let's pick our widest column, which is Wednesday. We can right click, choose column widths and see that we know a 10.86 Let's call it 11. We can highlight columns, a left clicking and dragging over two column G right click on any of the column Laissus choose format wits column width. Rather, type 11 hits ends at they go now. All of our columns automatically size to 11 and that looks much better. So it's a personal choice thing, whether you on your columns to be all a uniform size. If you do, just highlight all the columns. Choose the column with that Smith most appropriate or alternatively, if you want to have them size based on the wildest data that's in any particular Cullen, you can just highlight all the columns and double click. Or alternatively, you can just click this let layer of the corner here on left click, and that will highlight the entire worksheet. And then again, just double click between any of the column letters. Onda Uh, and everything is automatically resize for you. It works exactly the same way with Rose. That won't be visible at the moment because all of the data now rose looks pretty much OK. Is it is. It's exactly the same process toe. Also the row height. Just right. Click on the number. Come down, choose row height. Andi, repeat the same process they go. That almost looks really good, but it looks a little odd because of out my fruit and Benschop title automatically. Column A is being resized to fit my fruit and veg shop but we have a very clever way of getting rid of that problem, too, which will come to in the next lecture. 17. Merge And Centre: okay. How to deal with extra wide column wits when we use titles. Rare worksheets. In the last lecture, we covered how to resize cells, and we can either do manually using the right click and column width. Hoboken highlights all of the rows across the top, so all of the columns across the top head until we'll click between any of the column lettuce to automatically resize. And that works perfectly well unless you have a very long total like we have just appeared and sell a one and then Excel has a built in function, which is quickly visible in the home ribbon on that is, merge and center on what merge incented us is. When you highlight a group of cells, it will automatically turn those cells from a bunch of individual cells into one, merge, sell and then sent to whatever Texas within the first cell. That's an important point, because when you merge in center, only the data in the first cell that is the top left cell of the range you highlight is kept. Everything else is miss completely. So if I just give you a quick demonstration if we select cell a one left, click and drag over to be won without left. Massport's I know we've highlighted a one and B one and hit Merge in center. You can see that now. There is no Selby one. It has all become cell a one. So those two cells have been merged. We can also see that the text has been centered. Personally, I prefer my titles aligned to the left. So I'm just gonna hit the left alignment button, which we covered in early. A lecture. And no, if we choose to resize automatically by clicking between columns, am be we can see that column A has never been shrunk down automatically to match the width of item. But we now have a different problem because my fruit and veg shop was only sent it across sells a one and B one. We actually can't see the full title, and that's easy to remedy or we need to do Is left click out new merge sell drank over a few cells. So now we're highlighting a one through e one hit Emergence Center again, which initially will undo the merge in center and then click it one more time to merge in center again. So no cell a one b one c one d one an e one have all been merged into one cell, and now we can fully see our title. We can automatically adjust our column widths by highlighting all the data in double clicking. No, my worksheets, Stansell, look much better now. We may decide to have these all the same width for now. So let's just highlights all the cells choose format with. I think we said in 11 they go and let me just left. Click that and left the lying suffer. Got to do that. And there you go. No table startle a good on there Were ready to start entering some data. So you in the next lecture. 18. Entering Data - A Couple Of Shortcuts: Okay, guys, we're looking good. So far, we have a title or formatted merged incented. We have the headings for all of our data. Turn start entering some data. And now I'm going to fast forward through some of this on. I'm just gonna pause and then show you some useful tricks as we go. So I'm just gonna pause for a second while I type some of these things in and I'll come back to you when there's something interesting for you to say. Okay. Just got a bunch of fruits and veggies all listed here. Okay, We're just type in some mawr. Veg is in here. Okay? No, at the moment we've got apples, oranges, bananas, coconuts, potatoes and lettuce. Now, the first thing that I want you to notice is if I if, for argument's sake, we want to sell plums in our shop assumes they're type P. Excel is immediately gonna look up all the data above it to see through is another p in there. No, I already spotted that we have potatoes. So if we wanted to enter potatoes again, just hit, enter, and potatoes is in there. But we're going to say plums so excels automatically going to check all the data that you've just entered to see if it can help you out. If you want to actually repeat that data. So no, If I type p you'll see that it doesn't select anything because at the letter p it could be potatoes or it could be plums. So far, it typed, Oh, it would automatically say, Do you mean potatoes? If I talked, l it would say, Do you mean plums? So we're just one quick way of entrant data. If their entries that you will repeat just type the first letter of the first couple of letters and if it's automatically selected hit, enter saves you typing in the whole thing, Okay, I'm gonna pause again and come back to you in a few seconds. Okay? There's the list of all the fruits and veggies that we're selling right now. We're just going to start Topol in our sales for the day, and we're just gonna randomly make this up so we'll say Well, sell 745 to 5 11 Now you'll notice that I'm having to type each of these in fault. Now, if for argument's sake, you have a data set. Let me just finish this one off. If you have a data set where you're typing the same number and repeatedly you have a few ways of doing this, so what you could do say, for argument's sake, you mention we want to enter 10 against every fruit and veg in Tuesday. We've got a couple of ways of doing this. We can either just type 10 in the first cell, and you can see this little block up the bottom right hand corner. If you hover over that until your cursor changes to the Black Cross and double click Excel will automatically copy that value down all of the cells until it reaches the bottom of the data. And the way it works out where the bottom of the data is, it looks at the cell immediately to the left and copies as far as the last entry. A case that's woman groups. That's one way of doing it. There's another way of doing it. If we want to enter the same value into all of these cells, just highlights either. Using your shift arrow keys, all left. Click with your mouse and drag over Let's just do Monday to Wednesday and Thursday. Now let's imagine we wanted to enter nine into all of these values. Highlights the highlight. The range hit nine and hit control shift and enter. And it will write that value to the entire highlighted range. Okay, I'm just skin new by randomly changed these to some of the values. Just so we get some more interest in data to play with. Let's hope some dates into here. So say 5 to 937 Right? Okay. I'll just continue filling this data in on. Then in the next lecture, we're going to start to look a formulas. See you in the next lecture. 19. Sums - The Old Fashioned Way!: Okay, now we're going to get to what? X elders. Best maths. Now Exhale has a number of built in functions to speed up entering formulas. At the moment, we're going to start at the very beginning on do sums the old fashioned way. So we're going to come over to sell G five and just left click to select it. Now, the first thing to remember whenever you enter a formula in Excel is it. It always begins with an equal sign. So if you hit equals on your keyboard instantly, Excel will know that what you're entering is a formula, and this is true for every single formula you'll ever enter in Excel. Okay, at the moment, what we want to do is add up a total sales of Apple's for the week on Monday, Tuesday, Wednesday, Thursday and Friday. So what we can do is just simply left Click the first entry, and you can see that it's been highlighted with the dancing ants hit. Plus, we can move on most over click Tuesday hit plus move and those over Wednesday plus Thursday plus Friday and hit Enter and you can see that it's cleverly total everything up. So that's how you do something old fashioned Way now. One thing to notice. With this, if if I hit F two on my keyboard, you can see that it shows us the formula rather than the result. Now, one thing to pay particular attention to I'll just move the mouse out. The way here is that you can see that each of the cell references that is the B five, c five, d five, e five and F five, all highlighted in different colors, and they correspond to the colors that highlighted in the cells themselves. And then this is very useful because he makes it easy to spot which part of your formula is referring to which sell. So, for example, if we wanted to change D five, we can instantly see that that it's kind of a purple color on. We can see that this is a purple color. Well, if we have around most over until our cursor changes to this pointed cross, we can left, click and drag, and you can see that I have formula is changing accordingly so we could move it over to the left. We can move it to the right, we can move it anywhere. So if for argument's sake, we accidentally clicked the wrong cell and let's just hit Enter on DA and hit F two again, we can see instantly by this highlight in that we've actually selected the wrong cell when we typed her formula in. So it's easy to correct, rather than having to retire the entire formula, just hover over the part that's incorrect because he should be adding up all of their apple sales left. Click drag up and you can see that your formula is being corrected. Hit, enter and they go. And that's how you do a some the old fashioned way. In the next lecture, we will be covering how to use the auto some function. See you in the next lecture. 20. Sums - Using Autosum: righty ho. We now know how to do sums the old fashioned way. At the moment, we've only looked, at addition, which was the be five plus C five plus T five plus C five plus F five. But they were going to use one of excels built in functions to make do in addition, a little bit quicker. But just before we get to that, let me show you another useful tool that Excel has. If you quickly want to adult rely on numbers or a group of numbers, they can make column. It could be a row, or it could be any combination of the two. Let's imagine that we want to find there what the sum of all the values and column D r. If we slide toe first number by left, click in drag down until they're all highlighted. If we look down here at the bottom of excel, we can see we have a couple of things instantly reported. We can see that we have a count of nine, and if we have a look, we have 123 456789 cells highlighted. Quite correct, and we can instantly see that the sum is 59. Now we have a few more options in here which are easy to get it. If you just have your mayor Stan over this little bar at the bottom right quick, this box will appear now just in this area. Just here are all the things that we can instantly get feedback on these air. Just tuggle on enough. So if it's ticked and you don't want it left clicking it on ticks it, There's hope we wanted C average. We can click average. We may want to see minimum and we may want see maximum. So I've just selected those click off and now you can see instantly. Excel will tell us the average of this range that you highlighted. Just here is 6.555 lots of five and six. The lowest value the men is to. And if you have a quick look down, we can see. Yep, absolutely. That's too. The maximum value is nine on. We have a couple 23 nines and there, but it is the highest value, so quite correct in this almost 59. So that works in the vertical plane. You can also come across and Newt in the horizontal plane, or you can highlight a range of numbers. So we we selected all of our day to just here. We can see that the lowest value is zero, which it probably is. We've definitely got there there. The highest value is 15 which is there, which is quite correct. We have an average of 7.4. I can't work that out in my head, but I'm sure it's right on the total sum. All of their sales for all of their item for every day is 333. So they got very useful function for quickly finding land sums. All the lowest values are the highest values. Have a play around with it, and I'm sure you'll find that very useful. Okay, let's look at auto sums. Auto. Some is a function that's built into Excel that speeds up a basic addition. Now you'll recall from previous lecture that when we did our tradition, we we hit equals that we click Selby five them, plus that may click cell C five them, plus then d five them, plus nano moment on hit entering. Eventually we got to our answer, so it works. But it's not quick. So the auto some seeks to address that problem. Now. The thing to remember about auto some is it only sums columns, so we couldn't use an auto some toe. Add the this range of values here, but don't worry. We'll let we'll come to that in just a few seconds. But what it can do is automatically add a column. So what you'll do to use an auto some is left. Click the column where you want the result to appear, which will normally be the first blank cell below that, the list of numbers that you want to add up. We have two ways to get to auto Some. We continue to see a hehe in the home ribbon just that or were actually going to go into one of our other ribbons now and that's the formulas ribbon just up here. So I'm gonna hover over it, left click, and there we can see that our formula ribbon is being displayed on The second option in Is Auto some. So if we hover over it without mouse, we can see we get a little tool tip that tells what it does it automatically add stuff up. So let's go right ahead. Left, click on We can see there that Excel has already written the formula forest. It's included the equals to tell it. It's a formula. It's included the function, some it's included. The parentheses, all the brackets which enclosed the arguments, the arguments of just the range of data that the function is gonna act upon. Don't worry about that. Too much now will become second nature very soon on it's also highlighted the range of the arguments, and we can see here that we have cells. Be five through B 13 highlighted. And if we look at how that's expressed between the brackets, we can see that we have be fighting and probably see it a little clearer. In the formula bar. We have be five coal on B 13 and all that means is from B five colon, which means through to sell be 13 his center and they go, we have our first auto some. So there you go. That's how you doing, Auto? Some very simple. Just click it the boss Move your data. Hit the auto, some button press enter and they go. That's how you do in auto some much quicker than select elite cell and hit in plus repeatedly until you get to the right answer. Okay. Next, we're gonna have looking where we can copy a formalist so we don't have to keep rewriting the memory time. We want to use them. Okay, So you in the next lecture. 21. Copying Formulas: Okay, guys, we're just gonna very quickly cover a couple of ways of copying of formula to save you having to rewrite it. Then the last lecture recovered. How to use the auto, some function. So what we're going to do now is we're going to copy that formula all the way across the bottom of their data. The first thing we can do is copy the sell by hitting control and see selecting the cell we want to pace to by left clicking it on hitting control and V. And if I move the cursor right, the way you can see there that are forming there has been successfully copied to get rid of the dancing on. It's just hit the escape key on your keyboard and they go, That's warm way of copying formula. Let me just delete that. Alternatively, we could select the formula we have on. We can come over to this little box just at the bottom right hand corner, hover over its left click on drag as far as we want to go. And then when we let go of the left mouse button, there you go. You can see that the former has been copied all the way across. Let me just delete that. On the other way is if you come to the cell immediately to the right of the cell. You want to copy and hit control and are, which is the keyboard shortcut for Phil? Right? Controlling our you can see that it's been copied across again so we can either copy using control and see Move over either hit control, envy or just hit. Enter weakened drag over when it occurs to changes to the Black Cross. Or we can left click in the cell immediately to the right of it. Hit, controlling our and they go. And that's hey, copyright! Okay, now, very quickly. I'm also going to show you the same way to do it. Going downwards. Now again, we have exactly the same options we can either hit control with C. Move down, hit control and V. Let me just delete that we can hit control and see come down and hit. Enter Amega that gets rid the dancing Lance. We can come to the cell immediately below it. Hit control and D, which is the keyboard shortcut for Phil down. We can also drag down to fill down when we hover over the box on the corner and there occurs to changes weaken, just drag down, let go and they go groups. We can see that the formula's been copied. But if we're talking about the vertical plane, there is another very quick way to copy the formula down again. If you come to this little box up the corner, hover over it until your cursor changes to a plus double click, and you can see that the formula is being copied all the way down. Now, the way it works out where the bottom of the range is, it just looks to the cell immediately to the left to find out where the last entry is. They go another time saving tip for excel. Okay, Next, we're gonna have a look at how we can adapt the auto some formula so we can actually use it to some a row of data. Okay, See you in the next lecture. 22. SUMming Horizontally: Okay, guys, let's have a look in there. We adapt the auto, some function that we've already used to work out the sum of a row of data. Now, at the moment in these cells, we still have the old fashioned formula of this cell. Plus that sell. Plus that sell plus outsell. So what I'm gonna do is I'm just going to delete that so we can start a fresh now if we take a quick look at the auto some function, the formula that was written for is automatically when we used auto some we can see that the structure of the formula is the equal sign. Which means it's a formula. The function name, which is some the brackets that open the range that we want to some on the brackets to close off the arguments. Okay, What we're gonna do is we're gonna copy that, and we're going to use it over the side here. No one quick way of doing this is if you come up to the formula bar and delete the equal sign hit enter. You can see that no excel is treated. This not as a formula, but it's a string that is It's just letters and numbers. OK, so now we've done that, we can easily refer back to it so we can copy it. Okay, so we're gonna come up here and we're gonna type equals because it's a formula that we're entering. We're gonna type some because we can see here that the function iss some. We're going to copy that cell again by entering a bracket on. Then we can see that we have excels tool tip that tells us what we need to enter next is a number than a comma, then a number than a comma, so we could do it that way. Let's just do it that way for a second. So if we hit that and hit comma, you can see that number one has been highlighted in the tool tip. When I hit comma the tools it will expand and say no, you need to enter number two. So if I left click the second cell hit comma, it will then say number three and on and on and on. We're not going to do that because that's almost the same as the long way of doing it. So what we're gonna do is we're gonna highlight the range that we want to made up. So I'm gonna left click on Selby five, keep my mess but impressed on drag over to Friday. So now we can see that the format of the formula is being copied so we can see down the bottom that we have some open brackets arrange and closed brackets. So all we need to do now is let go The left mouse button hit, shift closed. Brackets hit, Enter And there you go. We now have over some affording. Now let me just come back down here and reinsert are equal sign They go So now we have a formula down the bottom And as we learned in the last lecture just to quickly copy that down hover over the corner to the drag handle to you curse the changes to the black cross. Double click and they go, we know know our total sales for each of our fruits and veggies on. We also know actually a better put tight total in the boat. Nasa. We know what it is, and we also know the total of our sales for the date by items. So there you go. That's how you adapt on auto some from a column and use it in a row. See you in the next lecture. 23. Basic Formulas - Subtraction: right. We're moving along at a great pace now. Now, the next formula we're going to look at is subtraction, and it's very simple to do. What we're gonna do is that two more columns to our data we're gonna add an opening stock opening stock on we're then gonna add Are closing stock closing stock again. OK, let's just resize those. We've done this before. Just highlight the columns. DoubleClick in between. And I wish I could type closing stock. They go. Okay, now, I'm just gonna randomly make up some numbers here. So let's imagine that we started the week with 75 of everything, So I'm just going to hit 75 enter re select the Cell DoubleClick, which we know Phil's down. So they go there. We know that opening stock waas, and now it's time to do out subtraction formula never could do this in one of two ways. What we could say is equals. It's a formula that less that less that less that left that less that an enter to find out that we would have 35 left, is over close in stock, and we can do it in a much quicker way by utilizing something that we've already done. So what we can do is win type equals opening stock on as we've already worked out. What the total of all of these are in this formula just here. We're just going to say opening stock minus total sales hit, Enter. And there you go 35 on again to copy that formula down. We're just going to double click when our cursor changes to the black Cross to fill down on . There you go. We can see that we started with 75 of each of our items and we ended with 35 apples, 40 oranges and so on and so forth all the way down just in case it wasn't obvious to you Excel will automatically re calculate. So if we look at oranges, we can see that I open stock with 75 Andi, Once we subtract all of ourselves, we have 40 left. If we change that to 50 in his enter, you can see that this result has also changed. So excel automatically Recalculates. You don't have to do anything again. It automatically works it'll for you. And they go nice and simple onto the next lecture 24. Basic Formulas - Multiplication: Okay. We're covering a lot of ground very quickly. We've learned how to do addition the longhand way, the old fashioned way. We've learned how to do addition using the auto some function on. We've also learned how to do subtraction. Next, we're going to cover multiplication. And to do this, we're gonna add another column to ever table on this time, we're going to call it monthly sales. Okay, we're gonna do this very simply. I'm just going to resize out there, OK, now, because we already know how many of each item we sell a week because we used our some function over here in column G. We're going to utilize that again just to do a simple multiplication, for simplicity's sake. Well, just imagine their four weeks in each month. I know that's not strictly correct, but let's just do that for simplest is sake. So we're gonna type equals, which tells excel that we're about to enter a formula. We're gonna click total because we already know the total of our weekly sales. We're gonna hit, multiply four and hit. Enter and then you go. We can see that if if a month had four weeks their monthly cells of apples would be 160 on . We're gonna hover over there and just double click to fill down so we can instantly see what our monthly sales of each of their items would bay. They go on. That's how you do Multiplication. Very simple indeed. Onto the next lecture, where will cover division? 25. Basic Formulas - Division: Okay, guys, let's have a look at a simple division formula now a game. We're gonna add another column over here at the end, and we're gonna work out this time with an average daily sales are so I'm just gonna type average daily sales on they go. Okay, I'm just gonna resize that again. We're going to come into our first empty self, and we're going to hit equals to tell Excel that we're about to do a formula. Next, we're going to utilize our some again from column G. So we're just gonna left click there and we can see that That's also massively been highlighted. We're going to hit divide. We're going to count up family. We need to divide by. So we have Monday, Tuesday, Wednesday, Thursday, Friday. That's five. So we just hit 500 keyboard hit, Enter on. There you go. We can see that our average daily sales for apples is eight. And again, we'll just click that cell double click window cursor changes. And there you go. We can see our average daily sales for each of our items. Now, that's how you do a simple division. Now, obviously, because we working out on average. We may also want to use the average function, and we're going to cover that in the very next lecture. So you in a few seconds? 26. Average Function: Okay, guys, we've covered a lot of ground. So far, we've covered basic addition. We've covered basic subtraction, basic multiplication and basic division in our earlier lectures. We've also had a quick look at our first Excel, built in function of auto some. And we're about to come to our second built in function of excel, which is average. So just so I don't forget what it is that we're gonna be doing. I'm just gonna type the title average, you know, less there. So they will know what this is when we're looking at it. Okay. Now, when we click auto, some you'll recall from the earlier lecture excel automatically, right? So complete formula using the auto some now some is the operative word there. If you've noticed there is actually a little down arrow below the auto, some I come. And if you left, click with your mouse. You can see that we have other options available to us on. What we're gonna do is we're gonna hover over average left, click on we can see there that excel is automatically written the complete formula forests . Now, at the moment, the formula is incorrect because we have not only ourselves for apples, oranges, all their fruits and veggies at the bottom. We also have a total which is going to skew the result. So for now, let's just hit Enter and we can see that the average is 12. Now, what we need to do is edit the formula, so it ignores the total on. The way to do that is to hit F two on your keyboard, and you can see that you now have access to the formula to be able to edit it. Now we can see that range be five to be 14 as being selected, which is reflected in the highlighted area on the worksheet on. What we're gonna do is we're just gonna come up to the formula bar up here. We're gonna left click just between the full and the closed brackets. We're gonna delete before and we're gonna site five begin to lead the four. We're gonna cite three. So there we can see that the highlighted area is actually now only for our fruits and veg is So Now we're happy that I have formulas being successfully edited. Hey, center on. There you go. We know about average sales of 6.666 lots of six is on the seven. And again, What we're gonna do is we're gonna copy that across for all of our days of the week by left clicking dragon over with their mouths, but impressed. Let go the mayor Spartan. And they go, No, we have our averages. And that's a use the auto. Some average function. Okay, so you in the next lecture. 27. The Order Of Mathematical Operation: okay, goes, we're gonna have a little break from the screen casts to ever look at the order of mathematical operation. Now you may remember this from your school days depend on how old you are. I know I'm certainly old, but it's critical to your success in Excel. So let's have a look at the order of mathematical operation. Or, as I sometimes like to call it when you're formula doesn't do what you thought it would do . Okay, let's move on. Now when we using Excel, we are at some point going to use complex formulas. Now we're going to worry about complex formulas because complex doesn't necessarily mean that they are complicated. All a complex formula is is a formula that has more than one mathematical operator. No, don't be worried by the term mathematical operator, either, because you probably already know most of the common ones they are plus for addition, minus for subtraction, multiplication and division. Okay, so far, so good. Let's move on first, let's cover how you think it would work. So far, we've created simple formulas. We've done basic addition. We've done basic multiplication basic subtraction on basic division, now at the moment, even when we did it the old fashioned way, and we did this plus this, plus this. Plus this. It was fine. And we got the Ansari expected because we only use a single operator in the formula. Whether we use that same operator over and over again makes no difference, because you will always get the answer that you want. So one plus one will always equal to on one plus one plus one plus one plus one will always equal five. And this is because we use only one mathematical operator. But if we were to say three plus three divided by two and we wrote the formula as we said it, that is equals because it's a Formula three plus three divided by two. We may think that we already know that the answer should be three. But in Excel it isn't the actual answer would be 4.5. If that just blew your mind. Don't more acres were about to have a look at how it actually does work in Excel now. Since mathematics began, mathematical operations have always been executed in the same order. Anything in brackets is always done first. Next comes any multiplication parts of the formula. After that comes any division part of the formula. Fourth, we do any addition, parts of the formula. And finally we do any subtraction, parts of the formula. If you think that's going to be difficult to remember, just remember the phrase Bless my dear Aunt Sally, and you'll notice that that is the order of mathematical operations. In the first letter of each of those words, so be would be brackets. M would be multiplication d would be Division a would be addition and s would be subtraction. So when in doubt, just remember Bless my dear on Sally. Okay, let's have a look at how it works. Now when we say a formula like three plus three divided by two, this is what we do in our head. We first say three plus three on we work out that that equal six So far, so good. Then we take care of six and we divided by two, which gives us the answer of three. Which is why you may have thought that the answer to the formula we just looked at would be three. But remember, bless my dear, aren't Sally the order of mathematical operation now because the D for division comes before a for addition. In our phrase, Bless my dear Aunt Sally. We have to adapt own formula, so it actually works in that order. So in a formula, the division will always be done before the addition. Because D comes before the A. So the way Excel will actually do it is it will do the division part first. So what it will say is three divided by two is 1.5, and then that's the division out of the way. Then comes the addition parts of it. So we already know the answer to our three divided by two is 1.5, which we can see here on. No Excel will execute the addition part of the formula, which is the three plus our answer, which gives us the answer. 4.5 on That is how excel will do it every single time. It will always do anything brackets first. It will then do It's multiplication. Zit will then do. Its divisions will then do it's additions, and finally it will do the subtractions So ever. Formula three plus three, divided by two, will actually be done as three divided by two and then three will be added to the answer. OK, so now let's look at a good habit to get into. Now, when you're planning your formula in your head, it may help you to think about it in terms of brackets. First. If we looked at the three plus three in the formula we've just done, we would know that that would be calculated first because B comes before everything else. So three plus three would be six on Ben because we'd already bracketed that off and it's already being calculated with them. Divide the outside by three because everything comes after the brackets. If in doubt, just remember the phrase. Bless my fear aren't Sally so our actual formula in Excel would be equals. Open brackets three plus three divided by two on. We could see that Excel would know Work out three plus three. Give us our answer of six and divide that answer by two, which would give us the answer of three. And that's the way to structure your formulas. So you always get the answer you expect now. Hopefully, that's made it a little bit clearer for you. But when in doubt always remember the phrase. Bless, my dear, aren't Sally. So if the formula you just entered doesn't give you the result that you thought it would always take a look at it and see if you can insert brackets around the things that you thought would execute first. Within a few days of writing your own formulas, you'll be an absolute master of this. Don't worry. So when and does Just remember. Bless, my dear, Aren't Sally on That gives you the order of mathematical operation. Okay, See you in the next lecture. 28. POWER USER - Evaluate Formula Function: Okay, guys, this later is the result of something that I learned only yesterday. Now, I've been using Excel for 20 something years on. I've never even seen this function before, but it's so useful. Had to squeeze it into the curriculum just so that you're aware of it. And it will really help to cement the electorate just on the order of mathematical operation. Okay, here goes. Now what we're gonna use is a tool that I have I only discovered yesterday, and you'll find it in the formulas ribbon. So if you come up here to formulas just left click and this ribbon will open up now, The formula we looked at in the order of mathematical operation was equal. Three plus three divided by two. Okay, so all we're gonna do is enter that formula in our cell on. They were going to come up to the formula auditing section of the formulas Ribbon on. We're going to click, evaluate formula. Let me just drag that into shot so I can ah, consuming and you'll see clearly again. There this little window appears here on what this does is it will allow excel to walk you through step by step every single part of your formula so you can see that Oh, formula up here in the formula bar say's equals three plus three divided my to. And if you look in the evaluation section of the evaluate formula box, you can see that innocently Excel is underlined. Which part it's going to do first so you can see that it's gonna ignore the three plus for the moment, and it's underlined three divided by two. So now you can see instantly that that's the first part of the formula that's going to be executed. So let's go ahead and click the evaluating That's gonna left Click there, and now you can see that it's changed the three divided by 2 to 1.5 because it's completed that part of the formula. And there we can see the wallets left with is three plus 1.5. So if we click again, they go on. This is going to be 4.5 hit restart and it will go through it again. Okay, now, that's one way of doing it when we just use the values and what we're gonna do now, rather than using a number in the cell. We're gonna typo values into these cells here. And then we're going to write a formula that references those middle That means is we're going to click the cell rather than type the value in the formula. So we're gonna type equals to tell Excel. It's a formula we're going to click that Plus that divided by that may be pressed to that That plus that divided by that hit Enter Re select the cell and hit Evaluate formula. Let me just move that down a little bit. Okay, Now, let's quickly go through this right. Okay, Now, because we didn't type of value into our formula, such as three plus three, divided by two. And we use the cell references. The very first thing that Excel needs to do is go to those cells and actually extract what the value is. So at the moment are formula, Say's a one plus a two divided by a three. Now we've got a new function that you can see in here, which is called Step In and you can see that a one is underlying, So the first thing excels going to do is retrieve the value from a one, and if we hit stepping, you can see that a one has been highlighted and it's being selected on our spreadsheet. And it says that the value in a one is three. Okay, so step out. If you want to step in, that's what it does. It will select the So it's just about to look at now. You can see there that previously we had a one on. No, it says three, cause it's retrieved the value there was going to take a look at a two. So when we hit evaluate, it will say in a two you have the value of three, and finally it's going to have a look at a three to see what values in there. So when we click evaluate, it will say to and there is going to say OK, I've retrieved all my values. Now I'm gonna work on the formula itself and again, just as it did before. It's gonna look at the division part first, so it's underlining three divided by two, and when we click, evaluated or calculate that and say three, divided by two is 1.5 and they're the final part of my formula is toe. Add three to that answer. So when we hit evaluate, they go 4.5. That's how we get it. And if you want to go through it again, hit restart and just click your left mouse button on this button and it will go all the way through and they go evaluate formula. Very useful on gun. I've missed it for as long as it's been in Excel, but they go No, you know bad. It's enjoy. See you in the next lecture. 29. Inserting New Columns And Rows: Okay. I hope you enjoyed the last lecture. And you now have a new understanding of the order of mathematical operation. For now, we're gonna have a look at how we insert new columns, a new rose into our data. So far, we've set up Oh, spreadsheet for our fruit and veg shop. And we can see that we're only selling on Monday, Tuesday, Wednesday, Thursday and Friday. But business is good on, we know want to open on Saturday, too. So what we need to do is add a new common after Friday. Now there are a few ways to do this. Firstly, we could click the column that we want to move over. So we just left click the column heading of column G. Because we want to move everything over to the right. We can come up to the home ribbon and have a look at the cells section just here. And you can see that we have inserts now on the little down arrow just below there. If we left click, we can see that we have different options. And what we want to look at here is insert sheet columns and you can see that that has just moved all of our data. One column over to the right. I'm just gonna undo that for a second. And I'm just going to hit the control and said to him, Do which is a keyboard shortcut for undo? Okay. And the other way of doing it is again left Click the column heading of the first column that you need to move over, right click. And you can see down here that we have insert on when we left. Click that because we've highlighted the entire column, it will move all the columns over to the right. So there you go. Now we can enter undated for Saturday. So set day and again, I'm just gonna make up some random numbers in here. So it's a 12345657 eight on and the total is in the bottom. Okay, now, because we need a total and we have our total in these cells here because I'm in the first blank cell. You'll remember that to copyright. I just hit control on our on. That formula gets copied across. So they go. Now we have managed to insert a new common. We've re titled it. And we've put in our sales data No time for a quick sanity check of the formulas we've already entered. Okay, Now, you'll remember that the first formula that we did was the total column. Just here in column H. So if we click on that formula and have a look up here in the formula bar, we can see that it says some open brackets. Be five to G five now. Previously, it only looked as faras f five. So because we've inserted a new cell excels automatically worked out that, actually, no, I'm just gonna hit F two now. You want to add everything, including your new column, which is great, because that means we don't have to Where the seller for me I excel is automatically worked out. You probably want to include this in that formula that we did earlier. So there you go. If we have a look down there, we can see the every one of them has corrected itself automatically. Fantastic stuff. Okay, Opening stock was just a number s, and that's not going to change our closing stock. We I think. Yeah, we referenced the total. So that's still good a monthly sales. Let's just have look at their monthly sales. I'm just gonna get after two. Yes, our monthly sales are still good, cause we're just going to divide the total by four on our average daily sales. Now, this isn't good, because at the moment, what we've done is we've worked out the average using the divide formula on we divided by five. And because we had the number five Excel wouldn't know to update that to six. Now, we have six days of sales. So what we're gonna have to do with this one is just come in left, click delete five, hit six, and then copy that formula down when the cursor changes at the corner, just double clicking. So if you use numbers instead of cell references in your formulas, always take a quick look at your formula to make sure it's still looking at the right data . Okay, that's so you insert a column. Now, let's do the same thing for Rose. Now, what we're gonna do is we're gonna add a new fruit and veg because we're wild and adventurous. That way I'm not going to do is just hover over one of the road numbers, right? Click and hit insert. They go in and say Everything's moved down. And then what are we going to sell? That we don't curl? So sell pineapples, pineapples, and I saw, like, quite pineapples. So they go pineapples and again, we'll just make up some random numbers in here Friday on Saturday. Now you can see that Excel is automatically added the same formula that we have there and that right there and there, and automatically worked out that you probably want that formula in there, too, so it's automatically written it in. And if we hit F to weaken, see, absolutely. It's looking at the right data opening stock didn't do that. No, because this is just a simple entry that is retyped to number. It's not a formula. Excel wouldn't know what value we wanted, so we'll have to add that manually. Now, here's an interesting thing that I want you to have a look at notice that in this column here we have a bunch of formulas and that this formula is in there. This Fortner is in there, but for the new road that we've inserted, there's absolutely nothing in there at all? Never. We added an hour open in stock. Watch what happens to this cell. Just here. Assumes we hit. Enter they go. Excels worked out because you have the same form urinal these cells. Chances are you're probably gonna want it in that cell to, and it will automatically add it in there for us and they go. It's done exactly the same thing with the monthly sales. It's exactly the same thing with the average cells. So they get very simply. That's how you insert new columns of data into your table, a new rows of data into your table. Just remember, have a quick 70 check of any formulas by selecting them, hitting F two and just having a look at the range of data that it's looking at and you're good to go. Okay, see you in the next lecture. 30. Moving Existing Columns And Rows: Okay, guys. In the last lecture, we looked at how to insert columns and rows into a spreadsheet, and then we're gonna have a look at moving on existing column to a different position. Okay, now, this is very, very simple to do at the moment. Our order goes Item. Monday, Tuesday, Wednesday, Thursday, Friday Saturday total opening stock closing stop, monthly sales and average sales. But we may decide we want to see the opening stock at the beginning of the week, So what we need to do is essentially move column I just here on inserted betweens, columns A and B So it goes in the middle, just here now. It's very, very simple to do. All we're going to do is left Click The column heading for column I we're going to right click on. We're going to choose the option cuts. So just left. Click cut now because we want to move everything from column B groups, everything from column B over to the right. We're going to select column B. We're going to right click on. We're going to choose the option of insert cut cells just here by left clicking Now at the moment we've now had a warning that's popped up to say that actually, this operation will cost submerge cells toe emerge on. The reason for this is you'll remember we've merged and sentido title just here. So for now, just click OK, and you can see that now are opening. Stock has moved from over here on is being inserted between columns A and columns. See, so that's exactly what we wanted to do. But now this is no longer merged in center. That's very easy to remedy. X. We've done it before. You just left click cell a one drug over with the amounts to column D, just like we did before. Hit, Merge in Center Again and hit left the line again. And it's a simple is that that's how you move one column to a new position on your spreadsheet. It's exactly the same process with Rose just left, click the row. You want to move, so let's imagine we want to move Rude seven. Left click to select it, right click, choose cut and then whatever you want to insert it, let's say, want to insert it there Just hit cut cells and they go. You can see how bananas have moved on. It's a simple is that I'll see you in the next lecture 31. Cutting, Copying, Inserting And Deleting: righty ho guys, in this lecture, we're gonna have a look at the difference between cutting and copying. And we're also gonna have a look a insert and delete. If you thoroughly oaf A with this already can probably hit complete lecture, move onto the next one. But I know with regards to cutting and copying When I first began using excel, it took me a while to grasp the difference between the two. So just so you don't have to go through that pain will just cover it here. So you have a thorough room to stand them from the beginning. Okay, I'm just gonna answer a bunch of data into these cells here, and I'm going to use our, uh, auto fill sequence unjust. Dragged down with the handle. OK, so now we have a column of data one through 10. Now I'm going to right click anywhere in this highlighted region, and then they choose cuts. You can see that we have our dancing and some. What I'm gonna do is I'm gonna select cell C two. I'm gonna right click, and I'm gonna hover over paste. Now you can see that. Oh, menu box. That was there a few seconds ago has been grayed out on in the background. Next cell is giving us a clue. So what it's gonna do so it's gonna paying stare values into columns. See? And you can see that column B where data used to live is now completely blank. So when you hit the button, you can see that our data is being cut from here on moved over to here and paste it in here . So cut essentially cuts the data that you've selected. Okay, so that's how cut works. If we right, click and choose copy. When I'm gonna come back over to this Soldiers here, right, click and choose Paste. I'm just gonna do the first option. And you can see again that our menu has been great out and in the background exiles going to give us a hint as to what it's going to do. So you can see this time that all about original data remains with the dancing ounce around it. Just there on that. When we hit the button, it's going to be copied into this range. So no, let me just escape. So now we have our data twice. We've copied it from column C on. We've pasted it into column B. So the essential difference doing, cutting and copying is cutting. Cuts it from your spreadsheet, that is, it removes it entirely. And copying will just delay to pace the same data somewhere else. So your original data remains okay? Cutting and copying covered. Now, let's have a quick look at inserting. Now. What I'm gonna do at the moment is I'm just going to click one of the cells on going to right click and choose in certain and just drag that up there so you can see it better. Now, we have four options in here in the bottom to our entire wrote an entire column. Now, this is just another way to get to something we've already covered. You'll recall when we inserted an entire row, we just left click the road number on we chose insert and all of our arose moved down on when we chose to insert an entire column. We just left click the column head in and chose the insert and all their columns moved over , So we're not gonna cover them again here. We're just gonna have a look at 1st 2 options so you can see by default that shift cells down has been selected because it's got this little black dot in the in the radio button that notice what happens to values fall through 10 when we click. OK, you can see that everything has moved down one cell. So any data that was down here would also have moved down one cell. They're just so it's apparent it moved down one cell because we only had one cell selected . If we chosen four cells and we chose right click insert on day shift cells down, you can see that four blank cells were inserted because we had four selected earlier. So it just it control and said to one day. So however many cells you have highlighted is how many cells your data will move down. Okay, Now, let's have a quick look at insert, right? So again, I'm just going to slight one cell for now on the right click and insert on this. So I'm gonna choose right now notice when I click OK, that the four that we currently have selected will move over to the right just for clarity's sake. What's happened is the whole of lime Five from that point has moved over to the right and side. So four didn't get deleted and pasted into column day. Everything here moved over one cell to the right. Okay? And we just take control and said to undo And that's how we do insert there to delete. What we're gonna do is we're going to choose our first column here and we're going to right , click on and choose Delete Notice that we only have one cell selected. So if we chose the default option of shift cells up, notice what happens to 56789 and 10 when I hit, OK, you can see that four has gone. It's been deleted and all of the cells below it have moved up. One cell. No, again because we only had one cell selected. They've only moved up one position. So let's just hit control and sent to undo that on notice that if I selected three sells right click and chose delete. Onda had shift cells up that the data billow will move up three cells. So we've deleted three cells on all of our data below that point has moved up by three cells. Okay, so now we're gonna have a look at delete on. We're gonna have a look at delete left. No, it is here that we have four in the column immediately to the right. So when I click, OK, you can see that the four has moved from there to there hasn't deleted from here on being pasted there, the entire range over to the right here as being moved over one column to the left. Okay, so hopefully that makes it a little bit clearer than we just hit. Boom do. And there you go. That's the difference between cutting and copying on. That's Have you used the inserts and delete options right onto the next lecture? 32. Hiding Columns And Rows: Okay, guys, from time to time, you may find that the data set you're looking at is just too large to be manageable. And it could be that you want to do some work far over on the right hand side. Or you may want to do some work way down at the bottom there to make your data set more manageable. It's very simple to just hide some of the columns or the rose. So imagine for a second that we didn't want to see our sales on Monday through Saturday or we need to do is come up to the column heading left Click drag over all the columns that we want to hide, so have highlighted C through H the entire column. If we just right click just here and choose hide, you can see that all of that data is now being hidden from view. It hasn't been deleted, it's still there and perfectly safe. It's just been hidden from our view. So now we can happily work over over here, and everything is much easier to see now if you're in any doubt that you have any columns hidden. One quick way of finding names is just have a look at the column letters. So here we can see that we have a baby and we immediately jumped toe I. So obviously something has been hidden. So there are two ways toe unhygienic. This. You can either left click and drag over the area that's been hidden on right click and choose unhygienic. You can let me just undo that hitting control and said, or the other ways to select the entire sheet, which is this little button at the corner here. Left click and then just click anywhere in the columns, right click and choose a new hide, and they go whenever columns have been hidden for us. Far across is your data goes will be unhindered. Okay, so that's a you hide columns. If you want to do the same thing with Rose, it's exactly the same process you can left. Click the road number Aziz. Many as you want to hide. Let's just say we're gonna hide those to right click, choose hide and they give their all hidden and again it will be a parent because we can see we be jumped from them. 1234 and then immediately to seven. So something is being hidden and again just left, click and drag over the range that's been hidden. So we've highlighted four through seven. Right click Choose Unhygienic Anay Go All your datas back on the other way works just as we did with columns. If you click the entire shades right, click on the road number and click unhygienic. Anything that's been hidden for us far down is your data goes, will get unhinging. So there you go. That's a Have you hide columns and hydro's See you in the next lecture. 33. ROUNDing Functions: Okay, guys, Welcome back. Now we're gonna have a look at the three rounding functions that built into excel, and they are round, round up and rammed down. Now, you may wonder why you would want to use a rounding function. So if we look over here column l you'll see that we have a couple of inter GIs. We have six, and we have eight. We have some numbers that calculated out toe, one decimal place. But we also have some one of several decimal places. No. Do we really want to know that our average daily sales of oranges is 6.16666667? Or would we like to say, Do you know what? Let's just call it six, or let's just call it seven. So this is what we would use a rounding function for. Okay, let's begin and have a look at all three of the rounding functions just so we can see the data. Clearly, I'm going to do what we did in the last lecture. I'm just gonna hide all of our sales on Monday through Saturday, so I've just highlighted them all. I'm just going to right click and click Hide they go Now it's sticking the title of our three functions. We have round, we have round up, Then we have round down. Okay, let's just make those a little bit wider so we can see nice and clearly they go now around is a built in function on because it's gonna be a formula with type and we're just gonna type equals we're gonna type round, we're going to hit open brackets And now we can have a look at the tool tip to give us a clue as to the structure of the formula so we can see that we already have round there. So it's already narrowed it down for us on The first thing is looking for is a number. Now the number we're gonna work on is this one just here. So I'm just gonna left Click it on. We can see from the tool tip that the next thing we require is a comment. So I'm gonna hit comer, and there you can see that number of digits has been bold ID. So what I'm gonna do for now from type zero, close the brackets, which encapsulates the arguments and hit. Enter. So they go. We can no see that are rounded. 6.833333 becomes seven. Now, I'm just going to copy that formula all the way down. And we've done this before. We just hover over this little box up the corner until a cursor changes DoubleClick and that you can see that I formulas been copied all the way, then Okay, Next, let's type in a round up function. It would help if I could type so around up, open rackets again, you can see that we need to enter a number. So we're just going to click the same number again. We're going to stick with the same format hit, comma type zero closer brackets and hit Enter Andi. Just going to copy that formula down. And then I'm gonna write in the round down function. So round down open rackets, I'm going to select the same number again. Hit comma number digits. I'm gonna stick to zero just so we can see the differences hit, enter and then copied that formula down. Okay, let's have a look at what we've got. Okay, if we just look at our top line our apples. We can see that an average daily sales exactly worked out to 6.8 and a lot of threes after it. Now we round 6.820 decimal places. We can see that it ran its to seven, which is pretty obvious because 6.8 is nearest the seventh and it is 26 If we look over at the rammed up, we can see a game that it's been rounded to seven. Because this time we've explicitly told Excel to round up. And if we look at the round down, we can see that it signalled the 8.3 on its rounded down to the nearest whole number being six. So rounding down takes you down to the nearest whole number. Rounding up takes you up to the nearest whole number on ground. Just rains to the nearest whole number based on whatever the decimal places are after your interview. Okay, so we can see if we look down the data that 6.1666 has been rounded to six, obviously, because it's nearer to six, the new this to seven rounding up takes us up seven because seven is the next nearest large number on rounding down again takes us specifically down to six and so on and so forth. So all the nice and straightforward so far. Now you'll remember that we had comma zero, which means that we're gonna have no decimal places. If you change your mind and you want to change everything to, let's say one decimal place, there's a very quick way of doing this. No, we know because if we click in here, we can see we have round zero. And if we scroll through them, let me just move the curse right away. If we scroll through, then we can see that every single one of them saves comma zero. And there's a very fast way to change all of those in one go. What we're gonna do is highlight the entire range. We're gonna hit control and f on. What we're gonna do is type comma zero, which is the one thing that's common to all of our formulas that we've just written. We're gonna hit the replace button on. We're going to say, Comma, did you say one? Let's say one combat one, and then we're gonna hit the replace all button and they go. You consider Excel has told us it's made 33 replacements. Just click. OK, we can close that now. If we have a look at our formula, we can see that we now have round l five comma one So that formulas changed that formless, changed comma one, as has that as has that one. In fact, all of them know, say comma one. So it's a very quick way of changing all your formulas if if they have a common element and you just want to change one aspect off them. Okay, so now we can see if we've gone toe one day, small place that all of their answers now equate to a whole number a decimal place and one digit after the decimal place. Now, at the moment, it looks a bit ugly because we've got lots of decimal places displayed. So what we're going to do in the next lecture is have a look out of four matter attacks, so it looks a little more uniform. Okay, So you in the next lecture 34. Formatting Numbers: OK, guys. Now we're gonna have a look at how to change the number of deaths. Small places we can see you now. Results. You can see here currently in this range that we have a uniform amount of decimal places. But if you look over here at this data, we can see that we have vary in different lengths. We have some that this long, some with just one. There were essentially two ways to change the number of decimal places you can see. The first is to hover over the soul that you want to change. Make sure it's selected right, click and choose format cells on format cells will bring up this dialog box and you can see that number is already selected. We have a sample data, which is eight point Sorry. 6.8333 which is copied from this cell just here on we can see that we have number of decimal places is eight. So if you want to increase it, just hit the little top arrow button and you can see in a sample that this increases or hit the down button and you can say our sample decreases the number alternatively in. Just highlight the number and type any number you want over it. So that's the one way. Still it. Let's click. OK, OK, the next ways do it is to look at the home ribbon on. We're gonna look at the number section of the home ribbon. Now, here we can see that we have the increased decimal on the decrease decimal, and it's pretty obvious. One day to she left click the increased decimal. You can see that our number is increasing by one. They're small place for each click and to decrease. Just hit the other button and it decreases by one each time they can do this. One is a time where you can do them on Mass, and this time we're gonna do them all together. So I'm just going to bring up that format box. I'm gonna select number on. I'm going to say, Let's just have it down to one decimal place. So I'm gonna hit okay and they go make us our data looks much better. Let's just delete those zeroes off the bottom. So they go in a Cassie. Although data is uniform and it's displayed toe one decimal place. Very simple indeed. OK, in the next lecture, we're gonna have a look at something really interesting. We're gonna have a look at how to build a complex formula by using some that you've already built beforehand. OK, so you in the next lecture. 35. A Primer On Building Complex Formulas: Okay, guys, before we get to the next lecture, we're just going to do a quick one on. It's going to be a primer on how to build a complex formula or right to simple formulas and then squash them together. Okay, let's crack on. The simplest way to work out how to build a complex formula is to use what you already have . Now this is just a screen cap of the spreadsheet that were already building. And if we're looking at average sales in Cell L five, we can see that we have the formula of equals which tells Excel. It's a Formula I five because it's looking at our total of sales for that line. And then over one in column m five we wrote our round formula, which was equals round open brackets. Then we referenced l five comma one to share that it's toe one decimal place. Then we closed off our brackets to encapsulate the arguments hit, enter and they go We were all good so far right now this is what we have. This is just cooked down view. Now, the thing to remember in this formula here on the right is that where we said round L five l five is just using the value that's been calculated in the cell of l five. So it's not L five itself. It's the value that's been calculated in l five. Do you remember from an earlier lecture that we use the phrase bless my dear Aunt Sally, which gave us the order of mathematical operations. So whenever you're in doubt, when you're building a complex formula, always bracket off your formulas is not gonna do any harm, and it just makes them a little easier to read. So what we're going to do first is we're gonna bracket off their formula in l five. At the moment it say's equals I five divided by six on. We're gonna insert a bracket before the I five and after the six. So it's gonna look something like this, and you can see down here in the bottom corner that we've now got that formula just that Now, remember, bless my dear Aunt Sally. So in this video, we're gonna have a look at the round formula that's in. Sell em five on. What we're gonna do to make this a perfect formula is we're going to school. Gee, this part over just a little bit. We're gonna take the l five reference. We're gonna get rid of it on. We're gonna take the formula from the other cell on. We're going to drop it in there. Okay, So now we have a perfectly built, complex formula. We're no longer reference in the cell. Were actually calculating the whole thing all in one formula. And that, my friends, is hell. You build a complex formula. If you ever endeavor, Just remember the phrase. Bless my dear Aunt Sally. Always do it simply first. So if you can work out what your formula should do, but you need to take a couple of steps to do it. And that's perfectly fine. And then once you worked and how to do it simply squashed them together. OK, so you in the next lecture 36. Building A Complex Formula: Okay, guys, Time to look at building our first complex formula. Now, at the moment, we have our average daily sales over here, which currently displays a 6.8. But you'll remember that if we increase the decimal places, it's actual value of 6.8333 on day three, Rikers forever. Now, also, we've worked out over here. That's around that number, actually makes it 6.8 because we've rounded toe one decimal place. Now let's imagine that we actually want around this toe one decimal place, that having tohave another column where we perform that calculation. So what we're going to do now is we're going to turn this into a complex formula utilizing the formula were already written just of here. But in certain, this formula into it. Okay, here goes there. You remember from an earlier lecture. The phrase bless, my dear, aren't Sally, and you'll also hopefully remember that the that is the order of mathematical operation. So it's brackets, multiplication division addition, subtraction, because we know that this former already works perfectly well. What we're gonna do is we're gonna keep that part of the formula, but just to keep it nice and clean. I'm going to click in the formula bar. I'm gonna insert a bracket before the formula. After the equals, I'm gonna close it off a the end with close brackets. So now formula size open brackets equals I five, which is our total divided by six and close brackets. It still has exactly the same result. All we've done is enclosed that formula in brackets. No, for the clever part, what we're gonna do is we're gonna incorporate this formula into this formula. So we end up with one perfect, complex formula that gives us the answer that we're looking for. Okay, now, just to make this simple, what I'm gonna do is I'm just gonna delete the equals on his center. So, no, we can actually see what that formula Saiz. Now you'll remember that l five was this cell here, So this formula looked at this result and then rounded to one decimal place. So we need to do is look at the format of this formula and edit this formula to match so we can see that the L five value is immediately after the brackets. So we're going to do here is come before our brackets, we're gonna type round. Imagine in that round is the option that we wanted to select on. We have to open brackets to enclose arguments. So now we can see that number is selected. But we've already calculated our number because we bracketed. It's off already here, So I'm just going to click at the end of the formula. I'm gonna hit comma. I'm gonna go straight to number of digits, which was one on a tight one, and I'm gonna close brackets. So now you can see that what we've done is exactly the same formula. But rather than referencing cell l five, we've actually calculated the value of l five by enclosing it in brackets. So when we hit enter, we can see that we get an answer of 6.8, which is exactly what we wanted to do. And now I'm just going to decrease the which ones decrease. I'm just going to decrease the decimal places so they go, No, we can see that we have 6.8 just to demonstrate that that works. Let's just increase the brackets and you can see we have many decimal places in this one here in this one here in this one here. So I'm just going to copy that formula down and they go, No, you can see that they all only go to one decimal place. So let's let's just reduce that, which is that one? So there you go. And you have just built your first complex formula by using two formulas that you already had and just copying the answers from one to the other. Just so that's complete. When you when you download this file, I'm just gonna put that one back. So there you go. We've no negated the need to have a second formula to calculate the answer that we wanted to get to. Okay, that's how you build complex formulas. But no, you know how they work. OK, so you in the next lecture 37. Sorting: OK, guys. Now we're gonna have a quick look at sorting out data. Now, at the moment of, um, hidden all of our sales days Monday through Saturday on have also inserted a blank line between celery and total. Just so Data's nicely sectioned off there. Okay, we have three options. What we're gonna do is we're gonna highlight the entire range that we want to sort, and we're going to come up to the home ribbon and to sort and fills that just here on when we left click. We're just going to choose the first option, which is sort eight, or said and that's going to sort all of our data. But notice what happens in the first column highlighted. Currently, we have apples, which is an A followed by oranges, which isn't Oh, and when we click, source data said, you can see that it's no gone alphabetical. All the other data to the right hand side has been sorted with it. So whatever was against salary and all those different columns is still exactly as it waas . We just changed the source order. Now equally, we could choose the sort set to a which, funnily enough puts it in the reverse order. Now what we're gonna do is we're going to insert another column. So I'm just going to right click, choose Insert. I'm gonna copy all that data down using control and day, but change the answer of pineapple. Two coconuts. So now you can see that we have two entries for coconuts, one that has let me highlight that one that has average monthly sales of 44 and one that has average monthly sales of 128. Right now, we're gonna have look at the third option in sort again. I'm in a source, all of our data. But this time we're gonna choose custom source. Okay, let's imagine we start from a blank. What we're gonna do is we're gonna add to the level, and we're going to tell it first, that we want to sort by column A So we're gonna click Column A. You have a few choices. He you consort by cell color font, color, cell icon. Let's just choose values, which is the default option again. We're going to stick to eight or said, but this time we want to rank it According to the monthly sales. Let me just move that over a little bit. We want to rank it according to the monthly sales. So what we're gonna do is we're gonna add a level. So we're just gonna click this button here. Once on, we look over and we can see our monthly sales is in Colin K. So I'm gonna choose column K. We're gonna leave it of values. Now, at the moment, it's saying smallest to largest, but what we actually want to do issues largest to smallest. Now, before I click. OK, let's let me just draw your attention to this little box just up the corner. Now, this is if your headers are in your first line of data. Hours aren't because they're headers, Aaron, Line three and we have a blank line. But if they weren't, you can just click this box and you can see that now is going to completely ignore the first line of data. So just tick that box. If your head is are your first line of data, that's just unclip that click, OK? And they can see that we've gone alphabetical down here. Oh, coconuts of being grouped together on because we said largest through smallest. Our sales of 144 have appeared above themselves. 128. So that worked perfectly well. Okay, let me just highlight that again. Choose custom. Sort on. Obviously you can. Ada's many as you like in here so you could sail. Actually, I want to sort by then on. Let me have a look. I want to sort by Saturday. So we look at Saturday. Saturday's column h on We can choose smallest through largest, and we can rearrange the order. So if we want sort by a then K, then h we just highlight H. And then we hit the little down button and you can see the Leitch has moved to the bottom. If we then decided we don't actually want K, we can just click it hit, delete level, and there are sorts on A and h r left. So there you go. That's how the salt works. I'm just gonna click council there. There. There are two ways to get to this. The way we just looked at from the home, Riman. And you can also come up to the data ribbon left click Data and you have exactly the same options. Just here in the sort and filter. OK, that's it. I'll see you in the next lecture. 38. Wrapping Text And Soft Enter: OK, guys. Now we're gonna have a look a rapping text and soft enters the You may not have come across the term soft enter before, but it's very simple what it does. Basically, whenever you're in their cell in excel and you hit, enter whatever you've talked in, the cell is automatically stored to the cell and your cursor will move to the next cell. But it could be that you want to stick in an extra line of data in there, just like when you tell him a letter room word. The way to do that is through a soft return. Okay, at the moment I'm gonna do is I'm just gonna hide these last three columns. Just so when you download the file there Still, they just unheard them by left clicking l on dragging over to pay, choose right click on a new height. But for now, I'm just gonna hide them. I'm also going to copy over these two formulas here by hitting control and are okay. Let's have a look at wrapping text. First, we can see that closing stock makes a cell a little too wide for the two digit data that we have stored underneath. So what we're gonna do is left click the soul to select it. We're gonna hit right click and choose format cells. It's just dragged round up to that. Now, if your alignment tab isn't selected, just click. The alignments have, and you can see them here that we have the option to wrap text. Just go ahead and click that little tick box there. Click OK. And you can now see that if I squeeze that column a little narrower that the Texas automatically wrapped so it no occupies two lines we can see in the formula bar that the Texas exactly as it was. We still have the word closing space and stock immediately after it. But when we get the enter, we can see that it's now allows us to narrow the columns. Okay, that's how you do a wrap text. Then the other way to do it is to use a soft return, and you can do this while you're actually entering the data. So there are two ways to do it. We can either edit directly in the cell or edit in the formula bar. I'm going to show you the formula bar first, So I'm just going to click before the D on a hit backspace. So I have no space. Then I'm gonna hit old and enter. No, don't worry. If you can't see what's happening in the formula bar, I'm just going to drag it down. So if I just hover the bottom of the formula, Bartle, the curse of changes left, click on, drank down. We can see that we've made the formula bar a little larger. But now I'm gonna click before sales hit backspace his old and enter. And now you can see in the formula bar that what we actually have is average a new line daily. A new line sales No. When a center, you can see that. That's actually now move the data into three separate ones within the same cell. And just to resize, I'm just gonna hover between till the curse of changes. Double click on Diego. Never that looks much more appropriate for the width of the data that we have. The second way to do it is to edit it directly in the cell. So I'm gonna do monthly sales this time when they hit F two to lowers 20. The data. I'm just gonna move my cursor in there. Hit the backspace. Hold bolt press enter. And no, You can see that monthly sales has become two separate lines, which is reflected in the formula bar just up here. Okay. No one in the center resource column automatically. Like clicking between K and L. And they go l columns air more appropriate wits based on the data that sits underneath. Um, the only thing missing now is that we have this text lying on the bottom. Andi, this text of here moving open, open up. So what I'm gonna do is I'm just gonna highlight all of those I'm going to, right. Click choose format. Andi, I am going to say in the vertical plane center, and it's OK. And there you go. That looks a little bit cleaner. Okay. See you in the next lecture. 39. Adding A New Worksheet: Okay, guys, this is probably gonna be the shortest lecture in the entire course. If we look down the bottom left hand side here, we can see that we only have one worksheet in a workbook so far on what we're going to do now is add a 2nd 1 So all I'm gonna do is come down to this little plus button left click, and you can see that we now have sheet to They were going to use this in our next lecture for creating a simple chart from the data that we already have. What we're also going to do is rename the sheet to something more useful. So all I'm gonna do is come down, right Click. I'm gonna choose rename and you can see that the text has been highlighted there. So all I'm going to do is type the name of our new sheet has ever chart hit, Enter on they go. We've successfully renamed our sheet. Okay. I'm just gonna go back to sheet one by left clicking it. Okay. Very simple. That's how you add a new sheet in the workbook. See you in the next one where we're gonna learn about chance 40. Creating A Simple Chart: OK, guys. Now we're gonna have a look at how to create a simple chart from the data that we've already created. Now, I'm not gonna highlight Absolutely everything here woman do is I'm gonna left click item, hold my left. Most button down. I'm gonna come all the way over and just highlight our sales up to Saturday. No, I'm gonna go up to the insert option Just here. Left click on this will bring. Got our insert ribbon. Now we can see that we have the option in the charts section just here to choose recommended charts. So let's just left. Click that for a second on. Let's have a look what it's going to give us, OK, we could have this particular one more I'm gonna choose Here is this one, And this is going to give us a stacked chart so we can see all of our sales for each day. And we can also see the breakdown of what? Each of the fruits and vegetables Where now, if we just click okay for a second, you can see that our chance being created now I'm just gonna drink that out so you can see the little better, but you'll notice that what we have is this huge blue area just here. If we look down to the key at the bottom, we can see that this is the opening stock. Now, if we were to look at that chart immediately, we could see that Apple's it looks like we've sold somewhere near 120 both groups. I didn't mean to do that when we just didn't it. If I move that chance head the way and we look at our total sales of Apple's, it's actually only 41 now. The reason for that is we've included the opening stock because the opening stock is our second column of data after our item titles. So what we're going to do now is we're going to select a non contiguous set of data. Okay, I'm just gonna delete that chart for just one second, and we're going to do it. But this time we're gonna miss out this column completely there. So far, we've looked at how you highlight a range by left clicking, but we haven't yet looked at how you highlight two separate ranges. So we're just gonna do that now I'm gonna left click item tracked down with my mouse button clicked Let go! And now I'm gonna hit the control button on the keyboard and hold it down. And now I'm gonna left click Monday and dragged through to Saturday So they go, You can see that with no highlighted two separate ranges of data on We've missed out our opening stock So let's go back up to insert. Have a look at the recommended charts. Let's just left click. This is the one we selected before and there we can see ourselves for each of our items. So let's just click, OK, they go on, let me just make that little bit bigger by dragging out. Okay, so now we can see instantly that apples are Monday the blue. We sold seven by hovering over it. It will tell you what the actual value is. On Tuesday we sold 10 and so when the so full so we can spot instantly, our biggest seller is lettuce. So we can see by just looking at this chart which are our best sellers, in which are porous sellers. If we look over here, we can see that oranges and pineapples don't sell particularly well, but lettuces were pushing nearly 50 a week on that. This is so lettuce is our best seller. So it's just a quick graphical representation of one. Our sales data actually tells us because we created a new child in the other worksheet. What we're gonna do is we're gonna copy this chart, take it from this sheet and stick it in our chart. Eso Let's just left click cut left, click our chart And let's just go ahead and paste it right in there. So they go No, we can see the chart data. Let's just make a little bigger Occupies our entire screen. We may want to change the chart title, so we're just gonna left click, highlight the date and say, uh, sales chart, call it ever sales chart, click off. So they go. We now have a nice simple chart based on the data they have already created account just going to go back to sheet one. Just click off and they get That's how you create a simple chart. So you in the next lecture 41. Adding Borders: okay and go is what we're going to do now is have a look out to format the cells with borders. Now, what I've done just now is I've copied the formula from here. Our total have copied it over two column B and have also extended over to J. K and L. So we have totals for all of our different data sets to change the border on a cell. All you're gonna do is left. Click the cell and we're gonna use the home ribbon on. We're going to use the front section of the home ribbon on. We're going to look at something we haven't looked at before. We skipped over it in an earlier lecture on that is the border option. That's this one just here. They concede we hover over it, that by default we get the bottom border selected. So just so you can see what that does, I'm just gonna left, click it and move out of that cell and you can see now that we have a line across the bottom of that particular cell. So that's a bottom border. So I'm just going to re select the cell. But this time I'm gonna choose the little down arrow by left clicking, and we can see here that we have a bottom order top, border left, borders, right? Borders, no borders, thick borders, all sorts of it from borders. But what we're actually gonna do is we're gonna create our custom border. So we're going to choose the option at the bottom, which is more borders by left. Clicking in this little box will pop up here. Now you can change the color of the borders by just clicking the collapse down here. We're gonna leave it automatic so it becomes black. You can see because we had a bottom board run, But it's actually there now, so I'm just going to click to remove its Allegan. Now what? We have no borders in their cell, and we're going to create one from scratch. They're here, the different styles that we can choose on because accountants like double lines over the top of totals. What we're gonna do is we're gonna select the double line by left clicking it. We're just going to go to the top, which is exactly where we want it, and click there. And now you can see that we have a double line over the top of our cell. So when I click, OK and just move out the way you can see that we now have a double over line across the top of their first total. So all I'm gonna do is left. Click that cell, copy the format in left, click and drag along the way over an agency that we have the double overline across all of their totals. And just to make it a little tired, I'm just gonna left click Insert a blank line between total Andi after inch. I mean, move down, they go. So that's how you change your borders. See you in the next lecture. 42. Customizing the Quick Access Toolbar: Okay, guys, in the next lecture, I'm gonna have a look ahead to set up our worksheet to print. But before yet, there were going to come back to something I mentioned way back at the beginning of this section. Let's have to customize the quick access toolbar that you'll remember that the quick access toolbar is this area at the top, left hand side of the screen. We can see at the moment we just have save, undo and redo, and then we're gonna add print preview, toe that list. So what we're gonna do is come up left, click this little down arrow button just here, and you can see that we have already have save, undo and redo selected on. We can also see that we have print, preview and print. So I'm just gonna hover over it left click, and you can now see that that's been added to a quick access toolbar. And there may be other options that you want to add into this as you progress through excel . If you find that used in particular function frequently to save navigating through all of the ribbons to get to it, you could left click here choose more commands, and there you can see that you have an entire range of different options and you can choose popular commanders and you can pick a particular tab. So if you said well, actually, I would like to have a look at the view tab. Just click View, Tamp and you could say, Well, maybe I frequently like to have a look at something at 100% so just click 100% click. Add on 100% will be added to your quick access toolbar. Let's go back to Popular Commandments on Let's Say that you frequently use email so you just left click email. Click add click OK, and they go. You can now see that you have these options added to your quick access to a bar. If you want to delete them, just left click. Come back down to more commands. Choose them from here, so left click male and say remove left. Click 100. Say remove and click OK on that. Say you add and remove different options to your quick access. Still, bar easy is that. See you in the next lecture 43. Simple Printing: OK, guys. Now we're gonna have a look at how to set up a worksheet to print. Then we have three ways to get to the print interface. They're all the same methods. Once we get in there, it's just the different ways to get into the interface itself. So the first thing we're gonna do is we're gonna look at the ribbon and we're gonna choose the file section, which is right up here on the left hand side. So it's gonna left click file on this window will appear on. We can see from the options down the left hand side that we have the option to print just down here. So I'm just gonna left click, and that's going to open this window there. We can see in the main window. We have a print preview. This is what the print will look at if we were to hit the print, but immediately. But if we look down the bottom there, we can see that the print will actually go over onto two pages. What we can see there is the first page, and if we click the little arrow to the right, we can see that that will be the second page if he wants all of out dated to appear on one page, we have two ways of doing this. The first way would be to look at the scaling option, which is down the bottom just here now. Currently, you can see that we have no scaling selected. Well, we're going to do is click the little down arrow and we can see that we have the option to fit sheet on one page. So if we left click that we can no see that our table will print out on one sheet now because this is set up to print in portrayed, you might find that this is a little difficult to read. So let's just go ahead and them do that for a second and choose no scale in on this time caught two portrait orientation. So I'm just gonna left click there and switch it to a landscape by left clicking on there. We can see that that's much easier to read, but we still have it over two pages without last column just tripping over onto the second page. So let's just come back again and again. We're going to choose the scaling option. We're gonna pick fit Cheeto. One page on they go. That looks much better. Will bounce here entire data at all on one page, nice and easy to read. And then when we're ready to print, we just make sure we have the right princess selected. Currently I have one nose. I think it picks that by default. So just hit the little down arrow, pick your printer on, hit the print button and there you go. That's how you do you print. And it's a simple Is that the second way to get to that interfaces to hit the keyboard shortcuts Control on DPI on that will bring you straight back here again. And they're just finally the third way Just hit the little back arrow is to use this new button that we've added to our quick access toolbar. So we can see then that we have the print preview and print button just left. Click that and that will bring you straight back here. Now that could be that You want to print the second tab on their work sheikhs as well. Now that the moment you can see in the settings that we have prints only the active sheets . So foods the left, click this button just here and click print entire workbook. We can see that this pages remained exactly the same, but we now have page one of three. So if we click next page, we can see that we have a chart and we click on the next page. We can see that our chart trips over onto the page after that, too. So let's just go go back here again, change it to let escape orientation on they go, and then we can see that it will print out onto two pages without a data table on the first page on their charts on the second page. So when you're happy that you've got it all set up just right, hit the print button on. There you go. That's how you print Easy is that I'll see you in the next lecture 44. Freezing For An Easier View: okay, guys would come into the end of the first section, and we've managed to build a spreadsheet completely from scratch on. We've also amazed to build a chart off of their spreadsheets. Now, at the moment, although data is visible because the table is quite small in if resumed upto 100% by clicking that button down there, a table is still nice and easy to read. Now, this is useful if we only have a small data set. But what if we have a larger data sets and we have twice as many lines? So let's just go ahead and copy all of our data just down here. I'm just gonna right clicking copy and then the bottom right click and insert copied cells and hit escape. They go. So now a data set is twice as big. Now, at the moment, we can't see the totals on the headings in the same view. Now we could come down here and scroll out and they go No, we can see the headings and the totals, but it's getting a little more difficult to read. And you can imagine if we have 1000 lines of data by the time you shrunk back down, you wouldn't be able to read a thing. Now, Excel has a very useful way of dealing with this. Let me just zoom back up to 100% and come. That's the top of the page. Okay? We're gonna use the ribbon again, and this time we're going to use the view section just here. So I'm just gonna left click on view and that we're going to be looking at the windows section and you can see in here we have an option called freeze panes. And in freeze panes, there are three different variations We have free Spain's freeze top row and freeze First column. Let's have a look at the freeze top row first. So I'm just gonna left click free Stop row, and you can probably see that is a faint lion appeared between lines one and two. And what that is is when we scroll down, we can see that our title is always visible. Now, that's partly useful, but we still can't see what this particular terrible correspondence to because they're headings are missing. Okay, So to remove that, just left click again and choose on Freeze panes now. Similarly, we could choose Freeze First column, and you probably guess what that does. We now have a faint line between columns A and B. So if we was a scroll over here, you'll notice that our item always remains in view and all the rest of the columns move over again. Useful, but not particularly useful for this exercise. So what we're gonna do is unfreeze one more time on. We're going to use the freeze panes option on its own. If we always wanted to see our headings on, we always wanted to see our items. What we need to do is pick the first cell, although data now, when you use a freeze, pain, everything above the cell that you've selected and everything to the left of the cell you selected will freeze where it is. So because of selected Selby five for go ahead and click freeze panes. Now you can no see that we have a faint line between lines four and five and we have a faint line between columns A and B on. What that's going to allow us to do is scroll down this way and you can see that I headings of remaining in view and we can keep going. So if we wanted to see it while our total sells for Friday, where we can just scroll down and it's dead easy to see within the same view. So Friday, Total sales 46 Really, really simple today, I'm just gonna hit control, went home to get up to the first cell again. And similarly, if we had a bunch of columns going way over to the side 2030 40 50 columns wide, we can just simply scroll over. And so we get there and we can see that instantly. Even though there's a loaded data now out of you, we can instantly Sparta Average sales for lettuces is eight. So they go. That's freeze panes. Just remember, with the free Spain's on the cell you've selected everything above and to the left will be frozen. So as long as you pick the right cell, you find if you pick the wrong cell, just hit unfreeze panes and have another go. It is they go a very useful tool. I'll see you in the next lecture 45. Getting Help: Okay, guys, you may remember way back at the beginning of this section, we talked about how to get help from Microsoft Excel. Then we have two ways of doing this. We can either left click this little icon just up the corner, the question mark, or if we hover over it, we can see the tool. Tip tells us just a hit F one to get to help. So I'm just gonna hit F one on the keyboard on this window here will pop up. Now let's imagine for a second we would like to see formulas instead of results in our spread sheet. So if we just typed in plain old English show for Mueller's hit, Enter Excel will go away and retrieve all the options that can think of. That may pertain to what you just asked it. So we can see here. The first option is show improvement formulas. That sounds pretty good. So it's just left. Click that and we can see here that excels retrieved the helpful for that particular Queary . Okay, so if we have a quick read through this, we can see down here to show formulas in all cells, press ctrl plus the little grave accent. Okay, so let me just close that and we'll have a play around to see what that does. So it said, Hit control on the grave accent and they go wonderful. We could know, see all of their formulas instead of the results. And presumably, if we want just reverse that we just hit control and grave again. And now ask spreadsheets back to normal. Fantastic. So nice and simple. That's how you get help from Excel. It's a simple as that. See you in the next lecture. 46. Filters: OK, guys. Now we're gonna have a look at what I think is one of the most useful features in Excel. Now, at the moment, we can see all of our data because their data set is comparatively small. Bruce, we have a massive set a day to say 100 lines or 1000 lines or 10,000 lines. A filter will come into its own. Now it's done easy to apply fills that. What we're gonna do is apply it at line four. So it appears here between their title on our data set so long, then do is left click line for come over to this area just like the corn. And then we've already used a sort function before. And we've looked at the 1st 3 options that we're gonna pick the fourth option, which is filter. Now, when I click off, you'll notice that we have these little boxes that appeared on the sides of each of ourselves. And these are our filters. Now we're gonna do is I'm just going to click on it with the left most button, and you can see that all of their data has shown down here. So all of their fruits and vegetables have appeared. They're One thing to note is that we know that we have two entries for coconuts, but coconuts only appears once in the list. Now that's because it filters out any duplicates. So if I were to just want to see coconuts, I can just left Click the select all, which then blanks all of our data on re select Coconuts by Left Click in, which takes it. Click OK and they go. You can see that all of our other sales have been hidden away, and we're only showing our coconuts. That sounds wrong. Okay, so let me just click that re select all. And obviously you can pick any combination. These So if we wanted to show everything except bananas on cucumbers, just click bananas on click cucumbers. Click OK on a go, we can see all of their data. Except for those two items, you can have multiple filters applied, so at the moment we have a filter over in column A there. We may also want to filter out column D Tuesday, we may say, actually, we just want to say anything that we sold 10 off, so let's just select everything click 10. So, no, we can see that those particular items have been filtered out on. We're only showing items that we sold 10 off on Tuesday. Now, a very quick way to find out if you have any filters applied is to look at the road numbers . Now you'll notice that most of the Rhone, um, has just appear in normal black funds. But where we have a filter applied, you'll notice that the row numbers have gone blue. Now that tells you instantly that you have a filter applied, netted to find out which columns you have filters on. If you have a look at the Icahn, you'll know sis that the ones with filters applied have the little filter picture within the ICOM itself. There's a very quick way of removing all the filters that you have applied, and that's to come back over to the sort and filter option just up here and hit the clear option. So when I hit clear, you can see that all of their filters have been removed. But we still have the option to filter on here because, although likens air around now, if we're talking about numbers, it may be useful to find out what your top sellers are on locally. We can also do that within the filter. So I'm just going to click Friday. You can see that our sales are either 012356 or seven Never going to this option just here . We can see that we have a bunch of other different options so surprisingly, if you left clicked equals you would get the equal. So you could say equals 10th and they go. We have nothing with 10 sales on that particular day. So let's click. All we could say does not equal one. Funnily enough, that will display anything that does not equal whatever value we have on. We also have our top 10. No, let's just say we want to see our top two items. Let's just scroll then. So we get to top to hit OK and they go our top two sellers on a Friday. We're bananas and cucumbers because we sold seven of each of those items. If we just select all again at up to settlers, did have seven items sold. Okay, we can also let's just asset, select all you could also look for any cells that above the average for that column. So just left click above average on all of these sales for these items were above the average for that day, and equally if we wanted to have a look and everything that was below average, we can just click below average. And these are all the items below the average sales for that day that we can also create our own custom fills that by choosing this option just here. So if we click custom Filter, what we could say is we actually want it to be greater than on Let's say to Onda, We also want it to be less than six. Let's say so here. The thing to note is that we want Andi. So this is going to show everything that is greater than two on and is less than six. Okay, so they go. We can no see that Fire 553 on two. Indeed, Fit that criteria. Okay, clear fields from column G. On they go. You can have any combination, as I say, so you can fill trade anything that begins with Let's have a look. A text filters create a custom filter. We can say anything that begins with C on click. Ok, And then we can only see our items that have see in it We could say anything that's custom fills there ends with on s so anything that ends with s so they go We can see Apple's been honest Coconut, cucumbers, oranges, plums and tomatoes All end with s And anything that didn't end with this has been filtered out from our view. Okay, let's just go ahead on clear all of those filters and they go have a play around the filters. You'll find it's very, very useful, particularly when you're dealing with large data sets on. I'll see you in the next lecture. 47. Highlighting Cells: Okay, guys, we're coming to the end of the first section Now on. I just want to quickly cover off one of the options that we missed from the font section of the home ribbon just up here. And that's the highlighting option, Which is this one just here. Now, what this does is it highlights the cell in a particular color. Now, there are a few reasons you can use this tool. Some people just use it to make things stand out to make the data easier to read. Personally, I use it for two particular reasons. Firstly, if they have a lot of formulas where people could accidentally over type, I will highlight the formulas to indicate that these are formulas and you shouldn't type in these cells. So at the moment, I'm just going to do that for all of their formula. So we can see here We have a formula. We have formulas down to here, so I'm just gonna highlight this range. Now, I'm also gonna hit control on the highlights. How other formulas You'll remember that if you hit control when you highlight that will highlight noncontiguous sets of data. I'm just gonna hover over this left click the little down arrow and I'm gonna choose. There's some really stand out. Of course, I tend to just use faint ones s. So let's just say that blue accent and they go Now you can see that all of those cells have been highlighting a different color to the plain white. That's their by default. So this is one of the reasons I would use them to highlight formulas. So it's an indication to the use that not toe over type those particular cells on the other reason I would use it is in a form to show that that's where a piece of data needs to be entered. You can use them for whatever you want to do. If you want to remove the highlight in, just literally select a few cells, which have ones you want to remove the highlighting from Come back to hear hit. No Phil and they go. You can see that you're your for matins. Be removed. I'm just going to copy the formatting from here, using format painter on Pace it over there and there you go. That's how you highlight to sell 48. Closing - Thank You!: Okay, guys, congratulations. You did it. You made it to the end of the beginner section of Excel. From zero to hero, you've learned an awful lot of very useful skills. They're going to take you a long way in your journey in Excel Atlanta toe Open Excel, which is a very useful skill toe have indeed you managed to build a table. You built simple formulas you've learned here to build complex formulas you've learned here to print your work. And you've built simple charts offer the data that you already entered. Now I plan to release three levels of this course in all does the beginner's level, which you just completed. Wilder. There will be level two, which will be for intermediate and advanced users. If you really want to supercharge your excel skills on level three will be be be a programming. And this goes way beyond the simple recording of macros, which will cover in section two on this will teach your hair to build full blown applications in Microsoft Excel, which is a very useful skill to have indeed. Okay, now, if you really want to get streets ahead of everyone that using excel, the urine already master the keyboard shortcuts. And to that end of created the next section, which is the bonus section to this part of the course, which will give you some simple ways to remember all of the keyboard shortcuts. Seriously, I recommend just dedicating a few minutes each day to master and a couple of these and you'll be wizened around, excel like a professional. I hope you've enjoyed it so far onto the bonus section, and I'll see you in the next course. 49. Bonus 1 Whizzing Around Excel Revision: Okay, guys, welcome to part one of the bonus section. And in this lecture, we're gonna have a look at how you was around Excel like a ninja. No. One of the first things that we looked at was the ribbon, which is this area at the top of the screen just here. And you'll recall from earlier in the course to navigate between the different sections of the ribbon. We just hovered over each of the names and we would left click to get to insert would move over with our mouths with left click to get to page layouts. The same with formula. Same with day to same with review. But there's an even faster way of doing this. If you're looking of toe, have one of those, most with scroll wheel. Just hover your mouse cursor anywhere within the ribbon on with your scroll wheel just rolled down, and you'll find that your move over one ribbon to the right, and if you keep rolling, you can keep rolling and you'll get all the way over to the right. So I'm just moving one click with the wheel each time to move over one ribbon to the right . If you roll upwards, you'll move over one to the left. If you want to move over multiple, just give it a quick roll and you can scroll through all of the different ribbons. That's one very quick way of navigating the ribbons. If you couldn't be bothered with the point and click method, Okay, so there's your first tip. Use your most wheel to scroll through the ribbons. Superfast. Okay, the next thing we're gonna look at is how you navigate your way around this data table. Now we know that we can instantly click on each cell by just left, clicking it and getting around. But let's imagine we want to get from here all the way over to here. What we could do is just left click. But you need fairly good marriage control to instantly do that, which I'm sure you have. But there are quicker ways you can hit the control button and hit your right arrow to immediately go to the end of your data set. You can also it the control arrow and hit the left to come right the way over to your left . Most column. You can hold down the control and hit the up arrow to go to the top of your data set. Or you can hit the control on the down arrow to go to the bottom of your data set. That's a very quick way to immediately get to the far right, the far left the top or the bottom of a particular day to set. Now there's another way of doing this that I've only just learned about. If you look closely, you'll see that this sow has a green line around it now to move immediately to the top. If you just hover on the top section of that bar, you can see that the curse has changed. Just double click with the left mouse button and you immediately go to the top. And similarly, if you just hover over the bottom line until the curse of changes, double click. You can see that your cursor immediately dashes down to the bottom of your data table. Similarly, if you click on the right hand side, double click it was is over to the far right, and if you have her on the left downside till the curse changes, double click and you immediately zap over to the far left column. Okay, if you're anywhere in your data set and you want to get back to your home, sell your home. Sell is the top left hand corner of your data. Just hit control and home on your keyboard on that will immediately wish you back up to your home. Sell. Okay, so that's how you move around the spreadsheet very, very quickly. Okay. If we've got a large data set, we may want to move down more than one wrote a time, which is what we do when we hit the down arrow. But and But sometimes you may want to move down an entire page at the time, and that's really quick to do. All you do is hit the page down button on your keyboard to move down a complete page at a time. So if I paid job, I can go back up and see that we have visibility of lions five through 20 hit page down, and now I can see lying 20. So I can still see the bottom line of the data that was previously visible, but no down to 35 so on and so forth is eight page down or page up. Very useful if you have big data sets. Okay, so that's how we was around the table within this sheet. But what if you wanted a quick way to flip between the different chiefs that we have Now we know that we already have two sheets on this one. We have sheet one on. We have our charts on again looking like this. A keyboard shortcut. For that. You just hold in your control button and hit page down. So for every time you hold control and hit page down, you'll move over one sheet to the right and similarly to move back. Just hit control and page up and your turn the page back one. So page up moves you to the left page down, moves you to the right. Just remember to hold down your control button. So they go move to that page on, Moved back to that page. Okay. Now, if we wanted to highlight our entire range of data, which is all of this here now? Ex old defines a range of data. If it's bordered by empty cells on, we can see that we have empty cells all the way around this data set so we could left click here, dragged down to here, and that's highlighted. But there is a keyboard shortcut to instantly do that, and that is control in a. So if I hold them control and it's a you can see that that entire range has been highlighted. Now the easy way to remember control and a is that all begins with a so control and a highlights all. If you want to highlight the entire sheet, you can hold down control and hit again, and then the the selection will expand to cover the whole of the sheet. Okay, so it's just choose another self so control and a highlights all of your table control a again highlights all of your sheet. Just one more thing that I want to bring to your attention in this lecture, and you'll remember when we used the format paint and that we copied the format in here. We hit format painter on. Then we copied the format in there. But if you double click the format painter, it retains the four matter that you've just copied until you hit the escape key. So just to show you that I'm just going to randomly pick one of these cells which one sees formatted differently to all of these. I'm gonna double click the former painter, and then I'm going to click there. You see, the formatting is being applied, and you can see by the curse of the formatting is still being retained. So I could click There, There, there, there, there, There, Anywhere I wanted to. So if you have a bunch of data that you need to format in the same way, but they're all over the place, just double quick format pains that and it'll retain that format in until you click escape . Okay, let me just undo all of that. Hits escape on. There you go. That completes the first lecture in the next lecture. We're gonna have a look at all the different keyboard shortcuts for cutting, copying and all that kind of stuff. So I'll see you in the next lecture. 50. Bonus 2 Keyboard Shortcuts: Okay, guys, welcome to the second lecture in this bonus section where we're gonna have a look at all the keyboard shortcuts. Now, one of the first things that we looked at was outing at Excel to automatically populate a bunch of data if it was a natural sequence, which is names of the week. So if I type Monday in there, you'll remember that we use the drag handled. Now, if we hover over the bottom right hand corner of the cell that we've selected, we can see that the curse of changes and if we left, click and drag down, we automatically get Excel to fill that sequence in forest automatically. So if you type in days of the week or months of the year, just type your first entry, select the drag handle and drag either down or across, up or to the left, and it will automatically populate that secrets for you. OK, so use the drag handle tow automatically populate Natural sequence is now the second use of the drug handle. If you already have data in a column, is if you type January in this column here now because we already have some data in this column. Excel knows how far we want to go down. Naturally, we don't have to drag it. It will automatically work it out by find in the last cell immediately to the left. So the other use for the drag handle when you already have data is again hover over it. But this time, instead of dragon, just double click, and it will automatically fill that sequence down as far as your data set goes. OK, so we'll run the subject of double clicking. Let's just type a number in there now. This could be any value or any formula and again to get excels copied all the way down to the bottom of your data Set, double click, and they will automatically populate down for us, far ritual data goes, so double clicking will automatically copy all of that down. As far ritual data set goes now because we type two number in there. That wasn't a natural sequence, so Excel just repeated the number again and again and again if we actually wanted to say put the five times table in. What we need to do is give Excel couple of values so we can recognize the sequence and this time just left, click and drag over the two values that you've entered. So now Excel can spot the sequence, and this time, if you double click, it will automatically populate the rest of that sequence naturally. So there you go. That's how you quickly get the five times table. Just hit five and 10. Highlight the to double click the drag bar, and it will automatically populate the entire sequence down for you. The other thing we looked at was copying right, and that's very simple to do. The keyboard shortcut is control and are so what this is going to do? It's gonna look at this cell and automatically populate this cell with its value, so control and are fills right. They can do one at a time. Control when are or he wanted to do bunch of day to just highlight all of your blank cells , hit control and are, and they go copies everything right, and similarly, you can copy down using control and D, and they noticed that that it didn't extend the sequence. Control and day literally will copy the cell immediately above paste into the cell you've selected so Control and D copies down and again. If you wanted to do a few cells, you could just highlight them by holding the shift. Hit Control and D and they go, you copy down. Okay, so that's a use the auto fill sequence that's a use. The drag handle tow automatically populate a bunch of cells, and that's a use control and are to copyright on control and D to copy. Down right, Let's have a look at cutting, copying and pasting so you'll remember. If we wanted to copy all this data, we can just left click and drag over it, hit control and see on control and see copies. And then we could come over to our first blank column. Now we have two ways of getting the data in here. Notice that we have dancing ands in the data we've just selected in column C, and if we wanted to just paste it once, weaken, just hit, enter and they go. We've successfully copied that data and pasted it somewhere else. But we have two options of paste in the data, and we can hit control and see again to copy it. And instead of hitting, enter I'm gonna hit control and V to paste. No notice that the dancing ounces still appearing around the data in column C. What that means is that that's been retained to the clipboard. So if I was to select the next cell along and hit control on via gain, I can copy it again, and I can keep doing this for as often as I want to. So while the dancing answer there and I hit, control them. V. We can always keep copying the data because it's been retained in the clipboard. If you want to get rid of the dancing honest after you've copied it a bunch of times and pace it a bunch of times just hit the escape key. So the difference between Control and V and enter is when you it's enter, it empties the clipboard, and you can only paste it once if you want to paste it multiple times hit control and the which is a keyboard shortcut for paste. Now the other thing we looked at was how to cut data and cutting. You'll remember, cuts the data from the original place and puts it somewhere else, so you actually delete the data that you're cutting on the keyboard. Shortcut for cut is controlling ex and X looks a little bit like a pair of scissors. That sounds remember the difference on again. Just click over to your first blank cell hit Control and V, and you can paste it in there or simply can just press enter. That will also paste it in there. So control and X cuts the data which removes it from its original place and allows you to put it somewhere else. OK, that's cutting. Copying on paste in. Okay, never gonna have a look at the keyboard shortcuts for format in and those that these cells just up here. So they're very easy to remember. If you hover over them, you'll remember that your tool tip will always tell you in brackets what your keyboard shortcut is on. These three of really simple to remember to make something bold hit control and be because bold begins with be nice and easy if you want to make something italicized hit control and I, which is very simple to remember a game because italics begins with I and if you want to underline something hit control and you because underlined begins with you. And you can have any combination though. So if you wanted to have something in bold and italics let me just move the cursor. You can hit control and be on control, and I on it will have it in bold and italics. And if you wanted to finish yourself by underlining it hit control and you And there you go . You get all three formats so control and be make something bold because bold begins with big control. And I make something in italics because italics begins with I and to underline something control and you because underlying begins with you okay, early. We also have a look at what we did if we made a mistake and we used the undo button, which was this one just up here. But you'll remember if we hover over, it's a game. We can see that the keyboard shortcuts his control ends end. And that's because it on does whatever we've just done. So to undo the things that we've just don't we applied some formatting it. Controlling zed on that will automatically let you undo whatever you just did. If you've made a mistake, okay? So the keyboard shortcut for undoing is control and said, If you decide that you actually didn't make a mistake and you want to redo what you've just done again, if you're not sure of what the keyboard shortcut is, just hover over the redo and you can see that you get the tool tip, which tells you the keyboard shortcut just up there and control. And why allows you to redo what you've just done. So the easy way to remember that will release the way I remember that is by saying yes, I actually did want that after also controlling Why is yes, I did want that. After all. Okay, when you're working with large data sets, you may want to instantly locate a particular piece of data. So for argument's sake, let's imagine that we wanted to find Saturday in our data set is quite easy to say at the moment. It is quite small, but the keyboard shortcut for finding you wouldn't be surprised to hear, because find begins with F is controlling F on that will bring up this dialog box just here , and you can see that we've already typed in Saturday, Saturday on do you can hit find next and it will automatically skipper and spreadsheet highlight each instance of that entry in there. There's another thing you can do here, and that is hit the replace, which will allow you to replace something with something else. So if I wanted to replace all of Saturday's with apples, I just type apples in here. I could type anything, let's say or in just I don't know why we replace ancestor today with oranges. If you want to do them all at once. Hit Replace all. If you want to do the 12 time just hit, replace and it will go through them in sequence. Let's just replace all on. There you go. We're done so to find something. The keyboard shortcut is control and F because find begins with F and if you wanted to instantly have it so it opens up with this. Replace dialog box highlighted control and H An easy way to remember that is have begins with H, and you may want toe have something in place of something else. So control and F find control, and h that you have something so you can replace okay and the final few just to control Excel on its own, we could use a couple of other keyboard shortcuts. And if we wanted to open a new file, we could hit the keyboard shortcut control and end on that will automatically open a new instance of Excel. So if you want a new workbook control and en will give you a new workbook because new begins with n nice needs to remember when we just close that to get it out of the way. If you want to open an existing workbook because open begins with O, you can hit control and oh, and that will bring up your open dialog box. And you can instantly click any of your other files there to open them. So control and oh, opens a new workbook. You'll remember that we already looked at Control and P, which opened the print dialog box because print begins with P. So Control and P brings up your print dialog box and finally one that we didn't Look, I don't think was controlling s, which is to save your work now, because I haven't actually saved this yet. It will automatically bring up to save as dialog box. But when she work has been saved. And you successfully renamed your file control and s will automatically save your workbook . Okay, so they go, there's a bunch of keyboard shortcuts. Please. If you have any easier ways to remember these open suggestions, please drop me an email and I'll happily amend this thing if if you come up with a much better way than I did. So there you go. You've made it to the end of the course of if you had some fun I hope you will enter north a lot of stuff. And I look forward to seeing you in the next course Thanks alot. 51. A1 Style - Relative Relative: Okay, guys, Welcome back. No. In the next four lectures, we're gonna have a look at all the variations of absolute and relative cell reference in No , I'm no. At the moment, that probably doesn't mean much to you, but it will become clear as you work through these four short lectures. So I've created this Excel file which you can download, and you can see that we have four tubs. We have a one which in technical terms is relative. A relative one. We have dollar sign, a dollar sign, one which would be read as an absolute, a absolute one. We have dollar sign a one which is absolute. A relative one and a dollar sign. One which is a relative A and an absolute one. No, don't worry. If you don't understand what that means, just that the moment the easiest way to understand this is watching in action. And then it will become really clear. Okay, so let's begin with relative a relative one or otherwise just referred to as a one. So island do is write a simple formula to multiply this figure by this figure, and you can see when we hit F two that the Blue sell a three is being multiplied by the red cell B three on hit. Enter. If I copy that formula down by using that short cut, just double click in the drag handle on, we come down to here and hit F two. Now you can see that the cell references the A's and the B's have moved relative to the formula that we've just copied down. So we copied it down one cell on the cell references of both moved relatively down one cell to. So before we had a three B three and then we hit enter, and now they have moved in relatively. So we have a four b four, and then as we move down, we can see that as we've dragged the formula, each of the soul references has moved down relative to where the formula lies. So that's a relative relative cell reference in the legs, like you're gonna have a look at absolute cell reference. Okay, I'll see you in the next lecture. 52. $A1 Style - Absolute Relative: Okay, guys, welcome back. Never gonna have look at an absolute and relative cell reference. That is one such as dollar A on day one. So what I've done is I've got a couple of columns of numbers in here morning and do is write a simple formula that says equals this multiplied by this. But this time I'm going to do is his F four once, and that gives us the absolute a absolute one. I'm gonna hit it again, and then we get a absolute one, and I'm gonna hit it 1/3 time. So this is F or three times, and now we get absolute a one on What that's going to do is look all of our formulas when we dragged them around. Two column A not necessarily lying one, but it's gonna lock two column A. So let's just copy that down and we can see how that works. So here, when we hit, have to we can see that we are multi playing, be one which is relative relative to absolute, a one on move down. And it's doing exactly the same. That pretty much looks like it did before, but now you'll see when we copy that formula across that if we click in one of these at random, we're in some seriously big numbers over here. What we have when we hit F to is that this cell has always stuck to column a so di three as moved over as we've moved the formula. But the second pass of the formula has been locked. Two COLUMN A. It will happily move down based on the line number so we can see here that we have absolute a seven, but it's always locked. Two COLUMN A So they go. If you type a dollar sign or hit F 43 times, you will lock the column, but not the rose. So you now have an absolute column reference on a relative row reference. So they go. That's what you do an absolute relative on. You can probably guess what's coming in the next lecture, but watch it through anyway and will cover the relative a absolute one. I'll see you in the next lecture 53. $A$1 Style - Absolute Absolute: OK, guys. Now we're gonna have a look at an absolute cell reference. So I have a column of numbers down here I'm gonna do in Cell. A one is just type three. Let's just type three. So I'm gonna write a similar formula wrong and say, Is this cell multiplied by this cell? But when I'm selecting cell A one, what I'm gonna do is push f four on the keyboard and you can see in the formula bar that we now have. The formula equals a three multiplied by dollar sign a dollar sign one. So that's absolute a absolute one. And I'm gonna hit enter, and there's our formula. Now I'm gonna copy it down just as we did before, using the drag handle and double clicking. But this time, notice what's happened as I moved down here. When they hit F two, you can see that the a reference has moved down in line with our copy in the formula Dem. So instead of calculating on a three is now looking at a four. But the other cell, the absolute a absolute one, has bean anchored up the corner to always be a and one and then if I come down, Teoh here and his have to again. You can see that the first cell a 10 has moved down relative to where the formula is but a game because we put the dollar sign a dollar sign, one that is, we absolute cell referenced it. It's always going to multiply by a one because those dollar signs Luckett in his column. I unluckily INS column one. So if we if we were to copy that formula across what would happen, let's just copy it over there on Copy it down. Oops, tracking that down. So even when we come over here and hit F two, you can see that this sellers moved relative to its position where the formula lives but still absolute. A absolute one is always the second operator. So by putting a dollar sign or hitting, F four once puts a dollar sign in front of the column letter on the road number on. Wherever you move your formula that particular cell reference will absolutely never move. It becomes an absolute cell reference. So they go. That's an absolute cell reference. In the next lecture, we're gonna have a look at an absolute and relative cell reference with absolute a one. I'll see you in the next lecture 54. A$1 Style - Relative Absolute: Okay, guys, welcome back. Now, you can probably guess where we're going to go with this lecture. But just for the sake of completeness, we're going to cover it anyway. Again, I've got two rows of numbers here, and I'm just going to write a simple formula. I'm gonna say this number multiplied by this number, but then I'm gonna hit F four once, and that will give me an absolute a absolute one reference on twice. And now I'm going to get a absolute one. And what that means is column The column that this formula will look at will very dependent on where we've dragged the formula. But the road number will never move from one. So what we're gonna do, hit, enter, copy that formula across using control and are on. Then, when we look at this formula by hitting F two, we can see that it's always looking at road one. So let's just copy that down a few rows, Control and D And again, we're in some really big numbers. So if we hit F two now, we can see that the B cell, the one that's relative relative, just straightforward plane be three as moved down as we've moved the formula around. But the second part of the formula be absolute. One has changed a column B because that's a relative reference, but it is locked. So if we come over here and his F two, you can see exactly the same thing. This reference D four is relative relative. So whenever we drank the formula, it is relative to where the formula is looking. But D absolute one always looks at Row one, no matter where you dragged the formula because we have the dollar sign in front of the one . So that's a relative absolute cell reference. Okay, ever play around with it and it will soon become second nature on. I'll see you soon. Thanks very much. 55. Level 1 SQA 01 Reverse Engineering A Sample Spreadsheet: Hi, David. Hopefully the audio on this comes out. Clearly. I'll be able to come to adjust it so you can read it. I've got your file. It's it's It's all pretty much straightforward stuff. There's just an awful lot of straightforward stuff in there. So if I started the top, we've got there just emerged incented cell, which looks like it's going from a over to end. Probably put that over 20 actually, at the end. But for now, we'll just replicate exactly what you've got. So I'm just highlight in a through to I m going to hit emergence center on. I'm gonna put the texting E T p. Whatever. Excess attendance. Forgive any typing errors that Aaron this s so I'm just going to click that I'm gonna make it bold hitting the bold, but in there. And I'm just going to increase the font size by hitting the little plus button. So you've got attendant stretched attendance roster actually saves, doesn't it? Underneath that looks like we have merged from a through two K, another merged incented cell. So I'm just gonna emerging center that this one says training site lips training site. Okay, so far so good. After the train insights, we have another two merged incented cell. So we have this one here merged incented, and this one saves roster. Eso we have roster and I'm gonna make that bold on we have an outline. He s so I'm just going to choose outline outside Borders, which is gonna just gonna call around the outside there. And I think we have a background of yellow, uh, more colors. It's probably that kind of color. Okay on then we have just this one that's being merged incented on that has an outline around it too. So they go. That's almost what the first couple of lines look like. The next line's been scrooge it up a little bit, so it reduces in size and then we've got down the side. We have number of trainees, then that goes from line unnecessary. We have contractor type. Next, don't make so we have a contract or turn that right. Ignoring spelling mistakes that are made during this contract to name on, then in line here. Whatever that sees on this one's merged incented over to E s. So I'm just gonna merge in center on, then it looks like it's left aligned, but you probably have a few spaces in here to make it look like it nudges in a little bit. The next one we have is I think we have ah, Line five has been Scrooge it up a little bit to make it look a little contractor Names also got a background of yellow, so just selected that choose the color on Choose that outline Borders option again. And I think the text is bold, so it's a little cumbersome, but luckily you only have to do it once. Then we have emerged and sent itself from a six down to a 12. So I'm just gonna highlight a six. Enter a 12 hit merge in center on. There's a trick in this one. So what we have is hashtag dot I think train this on. What we need to do is a line that differently. So I've just selected that sell at the moment. The text is going from left to right. What we wanted to do is go from bottom to top. So I've just hit that there and you can see this little option here Orientation. If you just click on that, you can see that you can flip it. So format sell. Let's go to format cell alignment on. Then we're just gonna drank this little red dot here all the way up until it goes upwards. So it's 90 degrees and then we'll have number of a number of trainees. It say's groups. Number of train, EES. I think that's what it says on the game. We can weaken send to that in that cell by using the the center in the middle alignments, and that is also in yellow and it is also boarded. So now we have that one in there. Now, the next thing we have is this E t p thing, which looks like it's done over two E and down this father's line 10 eso I've just highlighted that I'm gonna hit merge in center. It is colored in yellow on it has a border around it. Now, here's the trick. Okay, so we're gonna type, uh, inside the right thing in here. So we have our first line of text now if you hit old and enter at the same time, you can see in the formula bar that it's actually given you a new line of text type into so in he content my second blind off data and then it bold and enter on. That will give you another one. And so you can put my the line on adult and intact and you can get new lines like that seem , type something else ultimate enter something else hit ends at, and then it will all line up. So I'm just going to re select that just the line is over to the left. And if you want to change the front formatting on any of these, just scroll opinion into your formula bar. Highlight the text. So my first line of text on my line of text will be bold. And then when they hit enter, you can see that that 1st 1 has appeared is bold. So if you want to just highlight some of the text in this, just select the text in the formula bar and we could say, Make that larger, and then we can scroll down a little bit and say, My third line. We'd like it to be read on bond bold and italics and just scroll down through here if you If you got a mouse with a scroll button. You can just use that. If not just clicking and usually arrow keys on. Then you can just select by highlight in any bit of the cell that you want to highlight. So we could say, Well, make that blue Andi increase of font size and make it bold. And then when you finish their continue text in the formula bar hit, enter, and then your text will look exactly as it needs to. Okay. Next. We have, um, some merge in centering over here all the way over column. Oh, so emergence center. Make it yellow. Make it outline bordered on. We have training dates, and then it looks like underneath we've just got at bordered cells for each of those. So I'm just gonna highlight all of those on this time. I'm gonna choose all borders. So there we get those lines and they're just like we did before on to speed it up. I'm just gonna copy that Serve highlighted from here down to here. Hit control and see Come underneath Control. Envy. That gives us our start and ending times and and on day I'm going to copy that against we have another one for heroism in its own copy. And that left click drag down. Copy Andi paste that in there. And here we have hours on end lips. Now it looks like Road 12 is just being made taller. So I'm just gonna drag it down to where Line 13 would be by left clicking. And then I'm gonna merge in center this emergence center, make it yellow. Make it outlined on it. Say's trainee in the Schoen's colon. There to get to the new line holding old press. Enter Andi more text in here on whatever that needs to say. And the next thing we have is another merged in sentence self, which is here. So I'm just going to highlight the to emerge in center, make it yellow and use that outside border again cause I don't want a line through the middle. And here we have a job both and Andi, what do we have? Jump no dot Enter Andi. That one needs to be centered. So we're gonna choose this option here. Rips this option here on this option here to put it in the middle. And I think we also have it bold and underlined. So there we have job number. We have training name across here, which is another merged in sentence emergence center, yellow and again, I'm going to choose the outside borders. And I'm just gonna line this up in the middle. So chosen this option and this option. And in here we type training, name, trainee, name Andi. I think that's bold and underlined. Bold underlined. Now, this cell is a little bit wider, so I'm just gonna widen that out a little bit on DWI and have And do we have another merchant sentence cell that I think we do? So I'm just gonna highlight here and here. Hit merging center, Make it yellow on doing outside Border on. I'm gonna put trainee signature colon bolt and enter and whatever the texts who's in here? Okay. And then you can you can talk whatever needs to go in now, trainee. Sick. Well, this entire box looks like it's centered, which is good, but its top aligned. So I'm just going to come over here to topple line left click, and it will go to top on trainee. Signature is underlined. So I'm just gonna highlight Trainee signature in the formula bar. And I'm gonna hit the little underlying Lemina here. Tender. You can see that. That's no Got training. Signature underlined. Could be boulders. Well, now, if I look over at these end columns over here, there are a little bit narrower. So I'm just going to click on your original document, which I've got open here just a second. So just going to click on here, right? Click and choose column wits. And we can see that the common with this 5.14 So I'm just gonna copy that Hit counts on dragging out the way, and I'm gonna change. The column went from all of these by highlighting f through toe. Right click. Choose column. It's on tight that over the top. So it's 5.14 Okay, so that should be starting to look like the original documents. It looks like we have a bunch of numbers down this side there that go from one through 20. So I'm just gonna hit one on too. Andi, I'm just going to drank that down using the fill handle until we get to 20. Keep going, uh, 20. Okay. And it looks like they've been central lined. So I'm just gonna choose this option here. So they go somewhere. We have number of trainees, sub liquor that looks compared to the original. So far, so good. Andi job number always appears to say one. So I'm just going tight one in the 1st 1 and just double click there, and it will fill one all the way down. And we also want that centered by the looks of it. So I'm just gonna hit center now. All of this area here. Trainee name is merged. Incented. So most incented on C 13 and d thirties or just highlighted there on, I think if I just dragged down and hit control and day and I didn't do it just a second. So I'm highlighting their I'm his emergence centre, and I'm highlighting here underneath emergence center. And now I'm just gonna copy that one down on holding shift. So selected this cell. I'm holding shift. I'm going all the way down to the bottom, and then I'm gonna hit control in D to fill down. And that should give us merge. Sells all the way down. Perfect. Okay, this isn't merged on the rest of this looks like it said just but wind. So I'm just gonna highlights all of this area. Let me come down to the bottom on. I'm going to go into here. I'm gonna choose all borders because we want the lines to appear in these little lines, coming down as well. So all borders. So now it's starting to look something like the original. Let's just narrow this column appellate or here between A and B on narrow. This column up here between being see Andi that let's have a look on the limit. Just regular original into shots. So this column here c and D. If I highlight the to and hit column, Wittes says, 15.43 I don't know if that's the two of them, so let's just try it column. It's 15.43 was it? Let's bring the original backend. Okay, that's starting to look something like the original document. Let's just check the width of AEA's well by right clicking it and choosing column. It's and that want to say is 32.43 Let's copy that using control and see and do the same for this one column. It's 32.43 Enter. Okay, so that should Where's your original document going? Hang on a minute. Way Go. Okay, so that's starting to look. Okay, so we're good for all of this bit. Then we Let's just zoom in a little bit. It looks like we've got a motion sentence cell across here as well. At the bottom, eso will just come down to the bottom. Hit merged incented. So dragging from a 33 all the way across Emergence center said, Look at the original. Um, it looks like some spaces in there, so it will say note one. Whatever the notice in their Andi, it's being left aligned, but it looks like they've just entered a few spaces before the word notes to make it no gin . A little bit Onda. We have no to so highlight the cell that we've just done the no motion centered hold down shift Come down hit control and D and that will copy that down. Um and that will now say note to on whatever note to will say that he is texting here. Okay, So far, so good a game. It looks like down the bottom we have something has been merged and centered across A and B . So we'll emergency and 2 a.m. b on what does it say? It say's date. So we have date. Andi, I'm just going to re select the cell and it looks like we now have a different color. So we now have a blue kind of color. This probably won't be spot on the date on. Then we want the outside border again because we don't want to call her that, Lyle. And down the middle today we have date. We now have trainer name, which is in column c train name Andi. That looks like it's bean. Some look that's been centered and it's got the same for matters. These cells. So we're just gonna hit center, make it blue and color the outlines. Okay, Training name. I think these are actually in bold as well trained Signature. I'm just going to copy that. So I'm just going to select control and are and just change the word two signature picture train a signature course titles again. I'm just going to select the cell to the side and hit control and are because these air single cells. That's so that that and that are single cells, so you can just copy it and paste it in here we have course titles, lips stifles, and that looks like it's being centered. Right now we have another merged incentive, but let me just drag leading. Okay, so now we have emerged Incentive cell that's running from F over 20 for the next one. So I'm just gonna drag from F over 20 on hit Emergence center. Andi then whatever text that say's what Ever Text Gu's in here. Uh, and that also needs to be blue in background on having outside border. I think this entire lines actually bold. So I'm just gonna highlight line 35 by left clicking it hit in the bowl. But eso let's just find your original documents again on Zoom out. I have to do that so you can actually see it'll scroll down a bit on them. It looks like we just got some most incentive cells again underneath here. So I'm gonna hit, merge in, center on. I'm going to give that an outside border. And hamli, do we have We have 123 groups, 1234 So I'm just gonna highlight that Left click one That's 23 foot four rows Control and D something. We have our date boxes in there back to the original document, train and name. That's just outside borders. So I can, in fact, I'm gonna highlight all of those that, in fact, I'm gonna highlight there, through there but rather than outside ball that because we wanted to actually color in all the lines will come up to here and we'll choose all borders So they go. No, we have are all borders. Let's have a look at the original document again. It looks like it's exactly the same thing over here as well. This is an all borders thing. So a lot borders. So just highlighted all of those sounds I'm gonna hit the this one with the cross in its services. All borders. And that's colored that in Let's have a look at the original. Okay, it looks like we've just got emergence center thing across the bottom there, so I'm just gonna highlight away across the bottom hit emergence center. This is the last cell on the sheets and let's also in blue. Andi it has an outside border. There we go. So let me just shrink that down. The colors are probably not right, but it kind of looks something like the original. If I could just put that to the side of it, make that a little bit bigger on Zoom. And it looks something like that. I think now, actually, on your original what you do have where is that? You have a bold line around this section here, this this middle section up to there by the looks of it. So it looks like it's from 31 0 31 up to probably goes all the way over to D six. Yeah, so let's just highlight all of that there on this time we're going to go here on, we're going to say thick box border and click that and know when we click off, you should say that you got a super white, a super thick border around all of that section. And you may want to do that around this section here by choosing the thick box border on maybe even at the top. I don't actually know what the original looks like. Now think around. That's up. And then if assuming, uh, that looks something like your original document. Sorry, that's a 22 minute video that went on a bit, but that should cover everything that's included in that document. So, as usual, if you have any questions, ping me an email or ping me a message or post in discussion on day, I can see if I can source it out for you. Have a great day. 56. Level 1 SQA 02 Casing And Text Functions: Okay, guys. And welcome back to another student. Question answered. Now in level one of the course. How the question says, How can I make Excel right? The first letter in a cell in capital letters automatically. Now, by default, proper words automatically capitalized. So the days of the week and the months of the year are automatically capsulized because their proper words Beyond that, what we need to apply is a formula to do that. And then the formulas are very, very straightforward. So here and you can download this file from this lecture. What we have are a few examples. So when I have in cell a three is Mary had a little lamb, and what we're gonna do is convert that from lower case, toe up a case a woman do in B three, you say equals and I'm going to say upper open brackets. Then just point to that cell and closer brackets and it enter. And there you can see we have Mary had a little lamb all in uppercase on just conduce. Copy that. They're going to paste special values here. So now I have Mary had a little lamb in upper case to convert it back the other way. What I'm gonna do is say, equals lower open the brackets, point to that cell closer brackets and hit Enter. And now you can see I have Mary had a little lamb back in Lo que So again, I'm just gonna hit control and seeing Copy that click here and pay special. So now I have it in lower case. And I'm also going to copy that and put it in uppercase. Oh, no. We have it both here. One in lower case one in upper case there to capitalize the first letter of each word. We're going to use the proper function, so we just come type equals proper open brackets. Point here. Closer brackets on hit, enter. And there you can see that everything has its first letter capitalizing. If I just hit control in D and copy that down, you can see that it doesn't matter whether it's in lower case or upper case. It will automatically capitalize the first letter off each word. Now, a mindful that the specific question says head, I capitalize the first letter in each cell, not in each word. So here, we're gonna use will use this as an example. I'm just gonna pace that in there. So here we have. Mary had a little lamb in lower case now to capitalize. Only the first letter were really into Level two stuff here, but I'm gonna wish through it anyway, what we could have, what we're gonna need to do is combine a few different functions. Eso I'm just gonna wish through the so we can use the left function on what the left function say's is point to the text that you want to extract the left most character from. So we're going to be looking at D three and then we're gonna hit comma, and it says in hair me characters Do you want to return? So Mary has four letters, So free hit four and closer brackets left four off this cell here returns Mary. So there's the left here for completeness. We're also going to do the right, so it's gonna copy that. Paste it in here. Going to say equals right on. No, no, we're extracting the right most character. So getting an a point here on I'm gonna hit comma. Let's let's just say three for a second. So that should give us a M. B. So now we have a M beat. But if I change that to four, I would actually get the full word lamb. Okay, to complete this, we're gonna have a look at the mid function. We don't actually need to use that for the capital ocean of the first letter, but just for completeness, we're gonna cover it. So what we're gonna do is we're gonna say equals made, and there is one extra argument here again, we're gonna point to the text that we want us extract the middle from. So again, we're going to say D 11 here, comma start number will be the number of characters in that you want to start from. So if we wanted to extract had A from this, we would say Mary has four letters. We have a space that's five. And we want to begin at the sixth letter, which is the h So six on hand A has a h a d three letters, a space for andan I, which is five. So come up number of characters to extract five and enter May go. We extract had a no finally, on one thing that we are going to need to do to capitalize. The first letter is working out what the length of the string is. So what we're gonna do is just copy that there, copy and paste it in here. And what we're gonna say is equal Len, which is short for length. They're just gonna point that sell close brackets Nager. Many had little lamb has 22 characters. So here we're going to build a complex formula. It's going to capitalize the first letter. So we're going to Firstly, we're gonna utilize the left so we can say left. We're gonna point here on groups and say the first character, Intesa. And there we have the M. But what we want to do is make that upper case. So we're gonna enclose all of that in upper case. So now we get capital M. That's capitalized our first letter. Now what we need to do is return the rest of the string in lower case. So what we're gonna do on Bear with me here? We're going to say andi, which is the equivalent of a can Katyn eight. We're going to say Andi on, we're going to say, right, we'll do this in stages Were going to say the right off this string just here, comma on the number of characters is going to be the length of the whole string minus one because we've already captured that first letter. So we're going to say length of this string close of rackets minus one on down. We're gonna close that off. So that's going to give us. Mary had a little lamb, but let's imagine for a second that the lamb was also capitalized. So now what we have is Mary had little. And with a capital M in the capital, l full leme. So what we're going to do here is just wrap a lower around that west part of the formula. So there we have the capitalized first letter and everything else in lower case. Okay, I hope that answers your question. As always, any others just post them in and I'll be happy to help. Have a great day 57. Level 1 SQA 03 Charts From A Filtered List: Okay, guys. And welcome back to another student. Questions answered. Lecture. Now, this question comes from offer from what are for asks is is it possible to make a chart from filtered data? And yes, it certainly is. And you can you can grab this file. It's called Level one Sq. A three student questions answered. Three charts from a filter list. This is just data that I've ripped straight out of level one Onda. What I'm gonna do is I'm just going to click line for and get that filter in there, Some coming over to sort and filter from the home ribbon. Choose filter Nager. You can see that we've got all of our filters applied. So here we can select anything. I realize that slightly over two shots. So what will induce click select all which once elects everything? I'm just gonna pick every other one for the 1st 3 Someone say apple, celery and cucumbers it slightly off screen brown clicking. OK, so they go. You can see that just got out apples, celery and cucumber selected. The next thing I'm gonna do is select all the data, including the heavens that I'm gonna head on over to the insert menu. I'm gonna come to recommended charts. You can see that we have a bunch of recommended charts here. And I'm just gonna pick this one at random and click, OK? And they go. Now you concede that we have our chart, and it's only detail in the apples, celery and cucumbers, Which is exactly what we've felt it too. Now, here you can see that we have opened Stop. Monday, Tuesday, Wednesday, Thursday, Friday, Saturday total close and stop monthly on average. No, it could be that. You just want to see Monday, Tuesday, Wednesday, Thursday, and Friday. So I'm just gonna delete that child for a second and show you something else. What we're gonna do is we're gonna site item and drag down. So I'm left Quicken and dragging down That gives me this range here. Then I'm gonna hold down the control key on the highlight Monday through Saturday and again damn to cucumbers. So I held that I selected this left click drank down, hold down the control key, left, click, and drag over until I get all of that data. Now, I'm gonna come back to the insert menu back to recommended charts, and I'm gonna choose a set chest started. I'm gonna choose the same chart. Type on when I click. OK, you can see that now we have most of the data, but only the bits that we've actually selected so we can skip out the opening stock even though it comes before Monday. Tuesday, Wednesday, Thursday, Friday, Saturday, and we've also missed the end bits of data. So there you go. You can see that we have apples and orange, celery in yellow and cucumbers in green. Hopefully, that helps have a play around with it, and you'll be amazed at what you can do. As always. If there are any questions, do let me know, and I'll see you in the next lecture.