Excel 2016 for Windows and Mac: Essentials
  1. Getting Acquainted with Excel - What's on the Screen? - Hi. Welcome to Excel 2016 Essentials. My name is Bob Flisser and I'm thrilled to get you started using Excel 2016. Whether this is the first time you're using Excel or if you've used older versions and want to get up to speed you'll really like what Excel can do, and you'll see it doesn't have to be scary. Also, if you're a Mac user you'll find that Excel for OS 10 is almost identical to the Windows version, and I'll point out the few places where they're different. I'll start by showing you the overall concept of how Excel works and how you can use it efficiently. We'll look at how to use Excel for managing lists, then do some calculations, format your sheets, so they look nice, and then I'll show you the ins and outs of printing. As we go through this course I'm going to show you a lot of keyboard shortcuts for both Windows and Mac. You don't absolutely have to use all of them, but you'll find that Excel will be easier and faster to use with them, so let's go. In this module I want to show you the overall concepts of how Excel works, how to manage your files, and how to create and edit simple worksheets. If you aren't familiar with Excel yet the screen can look complicated, so to reduce the fear factor let's look at it piece by piece in the first lesson. Let's take a look at what I have on this screen. The file I have open here is called Book1, since I just opened Excel, and this is the first file that it's showing. It's just a generic, temporary name, and then later on I can go and save it with a more meaningful name if I want. Right now if I were to go and create another new workbook it would be called Book2, and the next one would be Book3, and so on. If I exit Excel and then restart it starts the numbering of these temporary files all over again, and just like all of the Office programs we have tabs across the top, and right now I'm looking at the Home tab, and the Home tab contains features that I might need at any time, like changing the font or alignment or number formatting. I can click on any of the other tabs, like the File tab, for example, this is also known as backstage view. To get back I'll click this little arrow up here in the corner, and we have other tabs for specific purposes, so for example, we have the Insert tab where I can insert pictures or the Page Layout tab where I can layout how this particular sheet is, and we'll look at these tabs later on in good time, but now I'm going to go back to the Home tab. Now you see there are a lot of icons here, and it can look a little daunting at first, but what Excel and the other office programs do is they separate what's on the ribbon bar into groups, so you can see here we have the Font group, and the Alignment group, and the Number group, and a few others, and you can see in many of these groups in the lower right corner are these little arrows. They're not in every group, they're just in a lot of them, and if I click one of these arrows it brings up a dialog box. We don't need this dialog box right now, so I'll just cancel out of it. For commands that you might need to use often we have at the top the Quick Access toolbar, so by default right now we have buttons to save the file, and to undo and redo, and you can customize the Quick Access toolbar by putting on other commands if you want. Now the worksheet itself, you can see, is divided up into rows and columns, and the columns are lettered ABCD, and then they go off into double and triple letters. There are over 65,000 columns you have available. Now the rows, you can see, are numbered consecutively, and there are over 1 million rows going down in the sheet, so that means there are over 16.7 million cells on this worksheet that you can use. Now you should never come anywhere near that when you're creating a worksheet, but the reason there's so much space is that if you're interacting with a database you won't run out of room. Now where a row and a column intersect that's called a cell. Right now the cell that I have active is cell A1, that's where column A meets row 1, and you can also see that A1 is listed in the name box. Now if I click somewhere else in this worksheet, I'll just click randomly over here, you see that cell E7 where column E meets row 7, and now E7 is listed in the name box. Now to the right of that name box you see we have a few icons that are kind of grayed out, and we use those when we're editing inside a cell, whether it's text or numbers, and to the right of that you see we have a long, blank box, and that's called the formula bar or formula editing bar, and we'll use that in a little while when we start editing data. I could go and click around and select any old cell that I want in the worksheet or if I want to select multiple cells I take the mouse, you see that by default it's this big, old plus sign, and I can just click and drag to select multiple cells, and then if I want to deselect I could just click somewhere. Now if I want to select all 16.7 million cells in this worksheet, you see in the upper left corner to the left of column A and above row 1 is this little box with a triangle in it, and when I click that it selects all the cells in the worksheet, and then if I want to deselect, just like before, I can click somewhere, and I deselect all the cells. Now when I'm talking about the cells here I'm talking specifically about all of the cells in this current worksheet. Don't get confused between workbooks and worksheets. I tend to think of Excel files as like three ring, loose-leaf binders, so the workbook itself is like the binder, and the worksheets are like the pages and sections inside the binder, so right now we're looking at Sheet1, and if I want another worksheet, if I want a new worksheet I click on this little plus sign here, and that creates Sheet2, and I can have as many sheets in this workbook as I want, there's no limit, so this Sheet2 has its own set of 16.7 million cells, so all of the sheets, all of the worksheets are part of this workbook. They are not separate files. When you save the workbook all of the worksheets get saved with it, just kind of like pages in a word document. Now down in the lower left corner you see there's this little word, Ready, and that means Excel is ready for me to do something, it's ready for me to start selecting or formatting or entering data. Now if I'm actually entering data or editing the content of the cell you'll see that word Ready will be replaced either by the word Enter or Edit. Now if I want to zoom in or zoom out on the worksheet I have this little mechanism over here in the lower right corner, so if I want to zoom in on the worksheet I can click this little plus sign, and then if I want to zoom out I can click this little minus sign. Now in Windows I can use a handy little keyboard shortcut. I'll put my mouse pointer in the middle of the screen, and I'm going to hold the Ctrl key down on the keyboard, and with the Ctrl key held down I'm going to take the little rolly wheel on the mouse, and I'm going to roll that towards me, and you see that zooms out on the worksheet, and if I hold the Ctrl key down and I roll the wheel away from me that zooms back in. That shortcut does not have an equivalent on the Mac, but so far everything that I've shown you will work the same way in the Windows and Mac versions of Excel 2016. Finally, you'll see three little icons to the left of that zoom assembly. The default view, the one that I'm in now, is called Normal view, and you can see that's the one that's currently selected. The one in the middle is called Pay Layout view, and that will let you see the actual sheet of paper that you're working on. The one on the right is called Page Break preview, and that lets you interactively set page breaks. We'll look at these later on in this course.

  2. File Management Techniques - To use Excel you need to know how to open, save, close, and reopen files. You're probably familiar with the concepts from other applications you use, but Excel 2016 programs have their own ways or flavors of presenting these to you. Let's take a look. The file I have open is called Price List and you'll find this in the module 1 folder of your data files, and what we'll do with this document is we'll make some changes, we'll save the document, I'll show you how to Save As, so let's make some simple change. I'm just going to go over here and I'll click there in cell A10, and I'm going to type in blueberries, and after I'm done typing I hit the Enter key, and if I want to save my changes I have three choices. One way is up here in the top in the Quick Access toolbar I can click that Save icon, and you see it shows me the keyboard shortcut, Ctrl+S. If you're using the Mac you can press Command+S, and another way you can do it is click the File tab, and then click Save, and if you're using the Macintosh you can click the File icon on the Quick Access toolbar or you can click the traditional file menu, whatever floats your boat. I'll click Save, and now it's saved. That means I can safely close this file, and know that the next time I open it I'll have that change there, so let's close it, and there's a few ways we can do this. In Windows I can press Ctrl+F4 or Ctrl+W. If I'm using the Mac I could press Command+W. If you don't want to use a keyboard shortcut you could go up here to the File tab, and then down over here click Close. Now Excel is still open, but I don't have any files loaded. Now to reopen this file I have a couple choices. I could go to my regular, traditional File, open dialog box, and then hunt for the file, and open it or because I had this file open recently it'll show up in my Recent File list. Let's do that first. I'm going to go up here to the File tab, and you see there it is. I had it open today, I can simply click it, and it reopens, but let me close this again, I'll just press Ctrl+W this time. Now let's say this is a file that I didn't have open recently, maybe I had it open several months ago, and it won't show up in the recent file list. A couple of ways I could do this. In Windows I could press Ctrl+O or on the Mac I could press Command+O, and that will give me the file open dialog box because the work open begins with letter O or I could go up to the File tab, and choose Open, and then you see that's already selected, simply because I don't have any files open already, and then I can select from one of these choices where I want to look, so I'll choose browse. Browse is what will come up if I press Ctrl+O or Command+O, so this shows me I'm back here on my desktop, in the demos folder, in the module 1 folder. If you're using the Mac this'll look slightly different, but that's alright, and I'll just double-click it, and it opens up. By the way, you may have noticed that I had some cloud locations there, so if I go to the File tab, and go to Open you'll see I have Microsoft OneDrive. Now OneDrive is a free service from Microsoft that you can sign up for, and get a basic account. If you're running Office 365 you already have access to Microsoft OneDrive. Maybe you're a SharePoint user. If you're a SharePoint user you may have the SharePoint portal listed here, if not you can click here on Add a Place and go add that. Whichever way you use it works pretty much the same way as if you're browsing your local hard drive. I'm just going to press the Esc key, so I get back to the document here. Now let's say I make a change and I want to save this file with a new name because maybe I'm creating a draft price list, and I'm not sure if I want to keep it or not, so I want to have two copies, two independent copies of this file, so right where I am here I'll just type maybe the word Strawberries (Typing), and again, I'll press the Enter key, and I want to save this as Price List Draft, so to do a Save As, keyboard shortcuts I can do is in Windows I could press the F12 key. If you're on a Mac, depending on which keyboard you're using, you may need to hold the Function key down when you hit F12, but more common on the Mac is to press Command+Shift+S. That does the Save As in just about every Mac application. If you prefer to use the mouse for this just go up to the File tab, and down over here click Save As, and this gives me pretty much the same screen as the File Open dialog box, so instead of clicking browse I can see the folder that I had open most recently was that Module 1, so I'll just click it, and now I'll simply edit this name, I'll click in there, and I'll call this -draft, and I can press Enter or click Save. Now you notice this file up on the top is called Price List -draft, so we now have two independent versions of this document. When I go to my file opener I'll go to the file tab, and go to Open, you can see there they are. If I click Browse or press Ctrl+O or Command+O on the Mac you can see I have those two files listed there. These are two completely separate files, and if I make changes to one it will not be reflected in the other, so I'll just press the Esc key or click Cancel, and click this back arrow, so I'm back to my workbook. Now what if I want to create a brand new workbook from scratch? Just like anything else, there's a few ways of doing it. Because the word new begins with the letter N I can press Ctrl+N or on the Mac I could press Command+N or I could click on the File tab, and over here choose New, and you'll notice that I can create a blank workbook or I can create a workbook that already has stuff in it. You can see here's this billing statement, and blood pressure tracker. Of all the things, why is that the second one? I don't know. Or I could go and search for templates. We'll talk about templates later on. Right now I'm just going to choose a blank workbook, so all I have to do is click it, I have a brand new one, and you notice that it's kind of offset a little bit, a little bit off the screen here. This is just to show you that we actually have two different workbooks open. I'm just going to take this and drag it back onto the screen, so you can see it a little better. Now creating a new workbook does not close the other one, so I can switch back and forth between this new blank workbook, and the other one that we were just editing. Now I can do that down on the task bar. If you're using a Mac you can do it down on the dock, but there's a couple other ways. I can go over here to the View tab, over here to Switch Windows, and then you see I have them listed, and I can choose one or I could go back to the View tab, back to Switch Windows, choose the other one. Now if you're going to switch back and forth that gets really old really fast, so let me show you some keyboard shortcuts. If you're in windows you can press Ctrl+F6 to switch back and forth. That works in just about every single Windows program. If you're on the Mac you can press Command+`. That's the key to the left of the number 1, and above the Tab key, and if I want to close this workbook, because I haven't made any changes, Excel isn't going to ask me if I want to save, and I could close it just like we did before. I can press Ctrl+W or Command+W, I could go to the File tab and Close, or I could click the little x here and close it, and now here I've got the other workbook that I already had open. Now that we've had some basic file management let's continue. In the next lesson we'll talk about how to edit the content and the structure of a worksheet.

  3. Editing the Content of a Worksheet - Editing in a worksheet is somewhat different from editing in a regular document like Word or PowerPoint, so in this lesson I want to show you some important handy techniques. I'll show you some typing and editing, and you can see that I just have a plain, blank workbook open, and before we do anything, just to make it easier for us to see what's going on, I'm going to go down here to the lower right corner, and click that plus sign, that zoom in button a little bit. Now when I want to type all I have to do is just start typing. You can see, like I showed you earlier, in the lower left corner we see the word Ready, so we know Excel is ready for us to type, and all I have to do is start typing, and let's say I'll start with the name of our fictitious company, and as soon as I type the first character, the first letter, you can see that it appears in cell A1. You can see it also appears up in the formula editing bar above it, we're just above column C, and you can also see in the lower left corner we see the word Enter, that's because Excel wants me to press Enter when I'm done typing, so let's just type in (Typing). Now I know I'm done typing, but Excel doesn't know I'm done typing. As far as it knows I could type War and Peace right now, so to seal it in, to get Excel to understand that there actually is something in this cell I have to press the Enter key, so when I do that you notice that we go down to the very next row, you see in the lower left corner the word Enter, it changes to Ready, so now it's ready for me to type something else, and what was in that formula bar is now blank because I'm now on a new blank cell. You can see I'm on cell A2. Now what if I want to type into the cell, and I don't want to move down to the next row? There's a couple ways of doing that. Let's put in, let's say I want to say this is First Quarter Sales. One way to do it on Windows is I press Ctrl+Enter, that is I hold the Ctrl key down and press Enter. If you're on the Mac you can press Command+Enter. Let's just do that, and now you see, once again, I've sealed it in, and I'll show you the mousey way of doing it. Now, actually before we do that, let's just delete what's in here. All I have to do is press the Delete key. I'll type that in again, (Typing) so instead of pressing Ctrl+Enter or Command+Enter I'm going to click up here, remember we talked about these little icons earlier, and you see that little checkmark has a little popup that says Enter, so when I click that checkmark it's the same thing, it enters what I typed in, but also does not move to the next row. Now let's say I want to go down to row 4. A couple ways of doing it, I could either press the Enter key a few times or I can just click there, whatever floats your boat, and let's type in something else, I'll call this Eastern Region (Typing). Now since I haven't entered yet Excel doesn't really know that I've done anything, so if I decide that I just don't want anything in there or at least I don't want Easter Region in there there's a couple of things I could do. I could press the Esc key, and that's not undo because I haven't actually done anything yet. Let's do that again (Typing). If I don't want to remember to press the Esc key, which it really works in every program you can think of is up here I could click that x, and you see the little popup, it says Cancel, and that's the same thing. Don't get that confused with undoing because undo means you undo something you already did, so one more time let's put in Eastern Region, and I'm going to hit the Enter key, and just to show you if I hit the Enter key again, each time I press the Enter key I move down to another row. If I decide I want to undo the last thing I did I can undo just like in any old program. I can click this undo button up here on top or I could press Ctrl+Z in Windows or Command+Z on the Mac, just like in every other program, and I'll do that, and notice it undoes that typing, so it moves out. Now let's say instead of Eastern Region I just want to type the word East, and you'll see why in a moment, so let's just do that, I'll type East. Now let's say I want to enter what I just typed in, but instead of moving down to row 5 I want to move across to column B. I want to stay in row 4, I just want to move across to column B, all I have to do is press the Tab key, so now maybe I'll type West, and again, hit the Tab key, and maybe North, press the Tab key, South, and now that I'm done, right, let's say there is no central or middle or anything like that, now that I'm done I still have the press the Enter key or click that checkmark because until I do, as long as Excel sees, as long as you see that cursor flashing in the cell, and you see that little word Enter in the lower left corner, I haven't done anything, and by the way, notice the ribbon bar up on top is mostly grayed out. That's really another giveaway because until you actually enter something in a cell, while you're still editing in a cell, there are a lot of functions that are just not available yet, so as soon as I press the Enter key, now you notice that all of those grayed out icons on the ribbon bar are now available again. Now let's say I want to edit the content of a cell. I don't want to delete the whole thing, I just want to change something, so for example, in cell A2 you see it says First Quarter Sales, maybe I want that to say Second Quarter Sales, there's a few ways of doing that. One way is let's click it first, and now I can click up here in the formula bar, and then just drag across that, and change that to say Second, and press Enter. That's kind of a long way of doing it. Let me undo, I'll press Ctrl+Z. You can edit inside the cell, you don't have to edit in that formula bar, so what I'll do is I will double-click the cell, and now when I double-click the cell you see the cursor is flashing there, and then I could just edit, just like it's editing text anywhere else I can change that, and again, this time I'll press Ctrl+Enter. Another way I could edit the content of that is by pressing the F2 key, so let me press F2, and now you see I'm editing, but the cursor is all the way over on the right side, so this way maybe if I want this to say revenue instead of sales, then I can press the backspace key a few times, and again, I'll press Ctrl+Enter. If you're using the Macintosh version you may need to hold the Function key down when you press the F2 key, but here's another way that you can easily edit what's in the cell, and let's say again, I want to change this word, Second, I want to change it back from Second to First. I could do that with two double-clicks. The first double-click will put the cursor in the cell, the second double-click will select the word, so what I do is I'll double-click once, and you see there's the cursor in there. I'll double-click a second time, it selects the word. Now that the word is selected all I have to do is type over it. I can hit the Delete key if I want, but there's really no need. I could just type the word First, and again, I'll hit the Enter key. Now what if I want to replace the entire content of the cell? Well one way I could do it is I can click that cell, press the Delete key, and then type something else in, let me just undo, but I don't have to Delete, all I have to do is type over it. Let's say this has nothing to do with revenue, maybe this is expenses. All I have to do is start typing, and you see as soon as I start typing it removes what was in there before, but I've decided I want to leave Revenue in there, I don't want Expenses, so I'll just press the Esc key or I could click that x, and now I have my text back again. The reason I want that in there is this is a very important concept. You notice that with both of these two cells in the top, in row 1 and row 2, it kind of looks like the text is spreading over a few cells, but let's see really what's happening. I'm going to click back in cell A1. In cell A1 you can see in the formula bar it has the full company name. Whatever is in that formula bar, that's really what you're looking at, that's really what's in the cell. What's happening over here is you have a few letters in that cell, but if I go to the next cell, if I click on cell B1 where it looks like part of the name is, take a look in the formula bar, that's empty, that means there's really nothing in cell B1. If I click in cell C1 it's the same thing. It looks like there's a few letters, but when you look in the formula bar, again, you can see it's empty, so what's happening is Excel is letting this text borrow the space of cell B1 and cell C1 just because they're empty, but what if I happen to put something in there? Let's go back here, I'll click back on cell B1, and maybe I want to indicate that this is the first draft, so I could type (Typing). Right now it looks like a mess, but I'll hit the Enter key, and look what happens. Because I have content in cell B1 the text that's in cell A1 is truncated, and it's not being displayed anywhere. Now in real life I wouldn't put something on there just like that, but I want you to see that it works. When I click back here on cell A1 you can see the whole text is in the formula bar, so I haven't deleted anything, there just isn't enough space for it. When I come over here to B1 you can see the whole first draft is there. Now if I delete that, and I say, gee, that's a silly place to type that, I'll just press the Delete key, and now you can see the entire text of that company name comes back again, because it has that room. Now text will do that, text will stretch across multiple cells, numbers will never do that. Excel will never allow a number to span multiple cells. When we get into putting numbers and formulas in in the sheet I'll show you how we can adjust column widths and row heights, so we can really get everything to display just the way we want, but we're just not ready for that yet. Just a few more things I want to show you first, and that is if you want to move around in the worksheet using the keyboard you can use the four arrow keys, so I could hit the down, and up, and left, and right arrow keys to move around. Also, some selection techniques, if I want to select the cells all I have to do is drag across them, if I want to select multiple cells, let me do select, I'll start again. I can select, oh I could go across, I could go down, I could go across and down at the same time, and I'll just click somewhere here to deselect. If I want to select a large range, well one way I could do it, of course I can drag down, but let's say I want to go way, way out to the right, way, way out to the left, what I could do is this, I could click on my starting point, and then hold the Shift key down, and with the Shift key held in when I click I select all of the cells in between, both wide and tall. This works with columns and rows also. I'll click up here, well first let me deselect, now I'll click up here on the header for column A, and you can see there's a little down arrow there on where my mouse pointer is, when I click that it selects all of column A, so if I keep that downward pointing arrow, and I click and drag to the right I can select multiple columns, and this is selecting all million some odd cells all the way going down, same for rows. Let's deselect, I can select row, maybe I'll start with row 2, and you see there's that rightward pointing arrow, and I'll click and drag down, and now I can select multiple rows. I can use the Shift key for this, so for example, let me select column A, now I'll hold the Shift key down. With the Shift key down I will Shift click column K, the header, and I have all that selected, I'm going to do that for rows, I'll click the header for row 1, and I will Shift click row 14, and now I have all of those rows selected. Now those are entire ranges. What if I just want the starting and ending point? I want to select cell A1 and cell D4, but not everything in between, I use the Ctrl key, so I hold the Ctrl key down, and with the Ctrl key held down I click D4, and now I have those two cells selected, but I don't have everything in between, and as you could probably guess, if you're using the Mac you use the Command key. Click one cell, and then Command click another cell. This works for columns, so let's say I want every second column selected. I'll start, let's say with column B, and I will hold down the Ctrl key or the Command key on the Mac, and then I can click various columns, and have just those columns selected, and not the rows. We deselect, and the same thing here with the rows. I'll click row 2, the header for row 2, and then I'll hold down the Ctrl key or the Command key on the Mac, and then I can select multiple rows, and not the rows in between. There's one more selection and navigation trick that I want to show you here. Let me deselect, and that's the name box. Remember, here's the name box. It doesn't just display cell addresses, it will let me go there, so when I click here you notice that C6 is highlighted, right, because I'm on cell C6. If I type, let's say, B10, I'll just type B10, and then press Enter, notice it's not case-sensitive when I press Enter, boom, it immediately brings me to that particular cell, so this is really handy if you want to go to a cell that's way out far to the right or way far down to the bottom, so for example, if I'm want to get to cell AA300, for example, I'll click there, I'll type AA300, and press Enter, and boom, I'm right at that cell, so that's just a lot easier than using the scroll bars or dragging all over the place. Now if I want to get back up to cell A1, yes, I could click in the name box, and I could type A1, but a much faster way. In Windows all I have to do is press Ctrl+Home. Hold down the Ctrl key, and I press the Home key, and boom, I'm immediately zipped up there. Now on the Mac you can either press Ctrl+ left arrow or you hold down the Function key and press Function+Ctrl+ left arrow. It's not the Command key in this case. The last thing I'll show you is that with that name box you can select in addition to moving, so right now you can see I'm on cell A1, and just to make this easy, so you can see without scrolling all over the place, let's say I want to select from A1 down to cell A15. Now if I click in the name box, and you know this already, if I type L15 and press Enter I simply go there. Let's click back on A1. If I want to select from A1 to L15 again, I'll type L15, but instead of pressing the Enter key by itself I'm going to hold the Shift key down and press Enter, so I press Shift+Enter, and now I select all the cells between the current cell where I am to that cell that I typed in that name box. There are a lot more shortcuts that you can use for selecting and navigating, but the ones I showed you here are the most important, the most common that you would use, so now that you can see how to do file management and how to do some basic editing on a worksheet, let's go into the next lesson, and I'll show you how to create a sample worksheet.

  4. Creating a Sample Worksheet - Let's create a basic worksheet from scratch, starting with a new blank page. This worksheet will tally sales in our fictitious company. Let's start this worksheet kind of like before by typing the company name, so I'm already in cell A1, all I have to do is start typing (Typing), and now that I'm done typing I press the Enter key. This is Product Sales, so I'll type that in, and I press Enter, and now I want to enter the names of the products, and I want to start on row 5, so I could either take my mouse and I can click on cell A5 or I could press the Enter key or the down arrow, whatever floats your boat, and let's type in some product names, so I'll say Apples, and again, I just type in a name, and press Enter (Typing). Now I want to type in the months, and let's say I want four months, so I want January through April, so I want to put the first one here in cell A4, so I'll just click there, then I will type in. Now what you don't have to do is, and this is something that I see a lot of people will do, I see a lot of new Excel users will press the Enter key or something, and then they'll do this, they'll press the Enter key, take their hand off the keyboard, find the mouse, find the mouse pointer, click there, and then type. That is just a real waste of time, you don't have to do that. When you're done typing a month or --- typing anything, and you want to go across, all you have to do is press the Tab key (Typing), and when you're done typing the last month you could press the Tab key or the Enter key, it doesn't matter. My point there is as long as you remember that the Tab key will enter what you're typing in, you don't have to do all kinds of acrobatics between keyboard, and mouse, and going back and forth. We have a little problem here though because it's alright that cell A1 and cell A2 are borrowing some of the space next to them because those are titles, but we do have a problem here that strawberries and watermelon are just too long to fit in column A, and we want to put some numbers, eventually we want to put some numbers there for January, so what we have to do is we have to make column A wider, so what I'll do is this, the mouse pointer, of course, is normally this big, old plus sign, but I'm going to put the mouse pointer over here on this boundary, on this line between headers of column A and column B. Notice I'm in the headers, I'm not on cell A1, I'm not somewhere here in the worksheet, I have to be on that header. When I have the two-headed arrow I will just click and drag until column A is as wide as it needs to be or as wide as I want it to be, and I let go, so I don't need column A to completely fit the company name, there's just really no reason for it to be this wide, I can if I want, but having it this wide is nice and comfy. There's a nice little trick though. I'm going to undo a couple of times, three times, so I'm back. If I have maybe a long column, and I can't really see how many rows, maybe I have hundreds or I have thousands of rows to take the mouse pointer and gingerly drag across to the right and guess how wide it needs to be might not work, so what you could do is when you get that two-headed arrow just double-click, and now the column becomes as wide as the widest item in that column, even if it's way down and you can't see it. I don't need that right now, so I will take that two-headed arrow, and I'll drag it back again. Now once I want to specify that this is for the Eastern Region let's go over here, I'll click here in cell A3, and I'll type Eastern Region, I'll press Ctrl+Enter or Command+Enter on the Mac, but you know, I decide I really want a little bit of space in between row 2 and row 3 because it's sort of squished, so what I'll do is this, I'll put the mouse pointer on the header for row 3, see we have that rightward pointing arrow, I'll click the right mouse button, and I choose insert, and that inserts a blank row. Now let's say I want to duplicate this for the Western Region. I don't want to type everything all over again. I don't need to type the names of the months, I don't need to type the names of the products, let's do this, let's select from what is now cell A4 all the way down to cell E11, right, because that's where we'll have the numbers for Grapes in April. Now if I simply want to move this around I'll put my mouse pointer --- somewhere on that border of that selected area, and you see I've got that four-headed arrow. If you're using a Macintosh your mouse pointer will become a little grabber hand. Works the same way, so now I could simply take this and drag it, this whole thing, anywhere around on the page. Let's put it right back to where it was originally because I was talking about duplicating this. Let's say I want to duplicate this down the page. What I'll do is I will hold down the Ctrl key. Now when I'm holding down the Ctrl key in Windows you notice the mouse pointer changes, there's an arrow with a little plus sign in there, that plus means I'm ready to copy, so I keep the Ctrl key held down, and I will drag down maybe to over there, to row 14, and I'm going to let go of the mouse button first before letting go of the Ctrl key, and now I've duplicated it. If you're using a Macintosh use the Option key when you drag. I'm going to scroll down, I'll just use the little rolly wheel on the mouse to scroll down, now all I have to do is change that word Eastern to Western, so like I showed you before, double-click the cell once, double-click the cell a second time, type it in, and press Enter or Ctrl+Enter. Now if you don't like all the keyboard and dragging and all that, maybe you have a very large area, you could use the clipboard, so let's just take all of this, I'm going to select it, I'll delete, let's scroll back up to the top. What I could do instead is this, let me select those same cells from A4 down to E11, and I'm going to copy to the clipboard. Now in Windows you could press Ctrl+C, on the Mac you could press Command+C. If you prefer the ribbon you could click the little copy button there, and it even shows you the shortcut, and Excel, unlike many other programs, unlike most other programs, and actually shows you what is in the clipboard, so you can see those marching ants. Now I'm going to click in the upper left corner of where I want that selectin to go, so that's cell A14, so in cell A14 will be a copy of what is now in cell A4, and I paste. In windows I could press Ctrl+V, on the Mac I could press Command+V or I could click the paste button on the toolbar, on the ribbon bar, and it pastes in. You notice that the marching ants are still there, and the reason is that I can still paste this, so I can paste this over and over again. Notice down in the lower left corner it says, select destination. See it says press Enter or choose Paste, either way is going to work, but at this point I decide, okay, I only need to paste this once. I want to clear out the clipboard, all I have to do is press the Esc key, notice the marching ants are gone, and now I'm ready to continue working. By the way, you notice that the paste button on the ribbon bar is also grayed out. That's just another way that Excel is confirming to you that there's nothing in the clipboard. We also have some paste options. Since this area here that I just pasted is still selected I'm going to delete one last time. Let's select all of that. Again, let's copy, this time I'll press Ctrl+C, so it's in the clipboard, I'll go down here to that same A14. This time instead of clicking right on the pace button or pressing the keyboard shortcut I'm going to click this little down arrow. This gives me some paste options. I'm not going to go through every single one now, but you see there's a whole lot of options that you can do a regular paste, you could paste just numeric values, you could paste as formatting, you can paste all sorts of different things or you could paste even this picture, so there's all sorts of options that you have, and we'll use some of these later on, but if I click the first one here that's just a regular old paste, and again, I'll press the Esc key, marching ants are gone, and that paste button is grayed out. Now what I'll do also later on is I'll show you a nice little trick. Instead of typing the names of the months individually I'll show you a nice little technique that will get those months in there automatically. We'll do that in the next module. For now what I want to show you in the next lesson is let's say you're starting Excel and you're in a hurry, and you just want to get everything done really quickly, you can use a template for that, so come on along, and let's take a look.

  5. In a Hurry? Use a Template - What if you have an immediate, pressing need to create a working spreadsheet, and you're panicking because you just started, and you don't know Excel yet. Try using a template. I'll show you how. There are two ways that you can see available templates. One way you may have seen already, that is startup Excel. It'll show you templates right there off the bat. Another way is when you have Excel already open you can go up to the File Tab or the File icon if you're on the Mac, and choose New. You can't use the shortcut Ctrl+N or Command+N will just give you a plain, blank, default workbook, which is this one over here, and there are two ways to look through these templates, we can browse and we can search. Now the very first one you have over here is a tour, Welcome to Excel, which is fine if you want to watch it, but after taking this course you probably won't need it, and let's take a look. I'm going to scroll down through this list, and you see we have some kind of general all around templates you might want to use. Now let's say I want to get a preview of one of these because these thumbnails are kind of small, I'll just click one, and you see it gives me a slightly larger thumbnail, gives me a description, who created it, and if this is a template I think I might want to come back to later I can click this little thumbtack over there that will pin the template up to the top, so I don't have to hunt for it every time I want it. I'm not ready just yet, so I'm going to click this x to close it. Let's just scroll through here a little bit more, just so you can get a sense of what's there. Now up here on the top where it says suggested searches that's kind of like a search, but it's sort of browsing as well, so I'll choose business, just click it, and it shows me all sorts of business templates, and I'll scroll through this list as well, and again, let's say, oh, there's an expense report, that's something that I want, click that, and it works the same way. I can click Create, I can pin it. I'm going to close out of this, and we have categories, so this is just another way that you can look through the list. Let's say Media, I'll just click this one here, and this shows me templates from other Office applications. Seems that there are no media templates for Excel, and you can see some things you would not have in Excel, for example, advertising or brochures and flyers, so these are templates for all of the Office programs. Now if I click Home up here this will bring me back to that main page, so let's say there's a specific type of template that I want, and I don't see it in any of these suggested searches, I don't see it in this initial list. That's where I'll go and actually search for it, so I'll click in this box here, and I want a template for construction, so I'll type the word construction, and press Enter, and it gives me four, I think there's just four, okay, and there's templates for our other programs. Here's one proposal, here's a bid form. I'll click the Construction Proposal, and I see there's a place for my logo, and there's some calculated fields. I'll close this, and let's take a look at this bid form, and you see this one here is more of a narrative proposal, so let's say this is the one that I want. I'll click Create, and by the way, these are all free, you don't have to pay a dime for these, and now I have this whole template there with fields for signatures, I have another sheet down here, and then I can go and change any of these if I want, so I can change the quantity, I can change the cost. Of course I can reformat it. This is all mine now, I can do anything that I want with it. You'll notice that at the top on the title bar this isn't called like Book2 or Book3 or anything like that, this is called Construction Bid Form1 because it's taking this template called Construction Bid Form, and this is the first copy of it, this is the first instance of it, so I can create a bunch more if I want. Now let's say I use this, I change everything, and then later on I want to use it again. What I'll do is I'll go back to the File tab, go back to New, and you notice it's the fourth one here in this list, it's the fourth one there on the top row, and it's there even though I didn't pin it simply because I used it recently, so Excel figures, okay, if you used it recently you might want to use it again, so I don't have to hunt for it, and type construction, and all of that. Now you can see there are hundreds and hundreds of templates that you can use, but maybe you want one that's just not listed here, and you search for it, and it isn't there. There are plenty of free Excel templates that you could find on the web. My favorite website for Excel templates is called spreadsheetzone.com. It's one word, spelled like it sounds, spreadsheetzone.com, and you could create your own templates as well. I'm going to click this arrow here to get back, and I'm going to close this one, I don't need this right now, press Ctrl+W or Command+W on the Mac. Now I'm not going to save changes, and here I'm back to a blank workbook, and let's say I want a template for a weekly expense report, so I'll just type in Weekly Expense Report, remember I have to press Enter. I'm not going to go through any formatting or anything else right now, we'll talk about formatting later on, but what I want to do is I want to save this as a template. What I don't want to do is a regular old save because that'll save it as a plain old Excel workbook, I want to save it as a template. Earlier in this module I showed you how you could do a Save As, so that's what we're going to do. If you want to use a keyboard shortcut that's fine or you could go up to the File tab, and I'll go down here to Save As, and I'm going to click Browse, and don't worry about what folder you're looking at because Excel is going to tell you what folder it wants to save it in, so down here for the file name instead of a generic Book1 I'll call this Weekly Expense Report, and here's the important part, you see over there where it says Save as type, I don't want to save it as a regular Excel workbook, I'm going to click that, and I want to save that as an Excel template, and as soon as I do that notice the top of the dialog box here where the address bar is, Excel is saying, okay, you want to save this in your Custom Office Templates folder, and FYI that will not happen on the Mac. If you're using the Mac it'll save a template anywhere you want, it'll probably just go right to your documents folder or your demos folder, whatever you were using recently, and now I'll click Save, and you can see here the file name is called Weekly Expense Report.xltx. A regular Excel file is .xlsx, this is xltx, t obviously for template. I'm not actually going to do anything with this. What I want to show you is if you want to use it later how do you get to it? Let's go back to the file tab, and again, we'll choose New, and over here you see the same premade templates like we saw before, except you see over here where it says Personal, click that, and now I've got that Weekly Expense Report template. I find that templates are a great way of avoiding doing the same work over and over again.

  6. Data Entry Techniques - Using AutoFill to Enter Data Quickly In this module I'll show you how to speed up the process of editing a worksheet using various methods and shortcuts. This includes several ways of entering data, including AutoFill, area selection, automatic decimals, and FlashFill. To save time, and make some data entry tasks less tedious, use Excel's AutoFill feature. AutoFill will automatically insert the names of days and months, it will understand standard three letter abbreviations for days and months, and you can also insert a list of numbers using a custom sequence, like 2, 4, 6, 8. The file I have open is called AutoFill, and you'll find it in the module 2 folder of the exercise files. It's a typical sales sheet, and I want to enter the names of the months across row 5 and row 12, but I don't want to type them in manually, I'll let AutoFill do it for me, so let's start over here. I'm going to click there on cell B5, and I'm going to type January, and I want to Enter and stay on the cell, so I press Ctrl+Enter or Command+Enter on the Mac. Now if you take a close look at the lower right corner of the cell, and let's zoom in a little bit, you notice the lower right corner of the cell has this heavy dot, and when I put the mouse pointer over that heavy dot the mouse pointer changes from this big, fat plus sign into that little crosshair. When i get that crosshair I'm going to click and drag over to the right and let go, and notice that Excel fills in the names of the months. It'll even abbreviate them for me, so now let's just go over here, and I will abbreviate January, and a little tip here, in this case I don't even have to enter it. If I just put the mouse pointer over that autofill dot I already have the autofill cursor, so I don't even have to enter, Autofill will enter for me, and now when I autofill across Excel fills in the abbreviations, and it's not just months. Let's scroll down here a little bit, just maybe forget about the sales sheet for now, I'll just click over here. What if we want days of the week? I'll take Sunday, and I'll take that, autofill dot, and I'll drag it across, and if I go for more than a week, for more than seven days, you notice that Excel repeats the pattern, and it does the same thing for abbreviations. I'll just delete that, and autofill the abbreviation, and it fills that in also. By the way, this isn't just going across rows, scroll down here, this will go down columns as well, so if I put in, I don't have to start at the beginning, I can start with Tuesday, for example, and it'll fill in, or I could start with March, and I can fill in, so this'll go across rows or down columns. Excel will also recognize patterns of words and numbers. Let's scroll down here. Let's say if I want to put in info for four quarters of the year, and I put in let's say, Quarter 1, and I AutoFill over here, Excel will put in Quarter 2, 3, and 4, but wait, there's more. Excel will also autofill numbers, but numbers are a little different. Let's go maybe over here. If I just type in, let's say the number 1, and AutoFill that, I just get a bunch of 1s, and the reason is that when you're dealing with months or days or quarters there's already a pattern that's implied, but with numbers there really is no built-in pattern, so here's what you do instead. Let's go over here, and again, I'll put in the number 1, but this time I'm going to hold down the Ctrl key or the Command key on the mac, and now when I AutoFill it example increments by one. Excel will also understand a pattern, so let's say if I go over here, and I type 10, and I'll Enter, and I'll type 20, and Enter. Now when I select these two cells you notice that these two sales together share a single AutoFill dot, so now that I have my pattern implied, now when I autofill Excel increments that pattern. Now this is all really great, and it's a great time saver, but you can also use the AutoFill feature to enter a calculation just once, and Excel will do all the rest of the calculations for you, even rewriting them as needed automatically, and we'll use that technique in the module on formulas and functions later on in this course.

  7. Select an Area for Faster Data Entry - When you want to enter numbers in a solid area of cells there's a much easier way than typing, taking your hand off the keyboard, clicking the mouse, and back and forth between keyboard and mouse. Earlier we talked about the concept of the active cell, that no matter how many cells you have selected there is always exactly one cell that's active. This technique uses that feature. The file I have open is called Selected Area, and you'll find it in the Module 2 folder of the Exercise files. Right now the active cell, you can see, is sell A3. What I want to do is let's select all of the cells in this box that you see, so let's start over here in cell B5, and we'll just click and drag down there, and you know also you can click the first one, Shift click the last one, it doesn't really matter how you do that. Now before we enter any data just hit the Enter key a bunch of times and look what happens, so right now cell B5 is the active cell, but when I hit the Enter key I go to the next one, and the next one, and the next one, which you could probably guess, but because I have this selection, when I hit the Enter key I now cycle to the top of the next column, and it just kind of keeps going over and over and over again. By the way, if you want to go backwards hold the Shift key down when you press the Enter key, so if I hold the Shift key down and I press Enter I go backwards. If I let go of the shift key, and I press Enter, I go forwards. Let's make some use of this feature in entering data. However you want to do this again, is okay, I just want to make sure that it's the first cell in this block that's active. In this case, I think it's just easier to reselect. I'll type in some numbers, and I'll type in a number, press Enter, and I type in a number, press Enter, type in a number press Enter, and now when I press Enter I go to the top of the next column, so you see, it's making very short work of entering a lot of data that a lot of people who aren't familiar with Excel can take a lot of time to do, and here's another great data entry technique that I really like. Let's scroll down here a little bit. Let's say I want to enter one of these names that you see here in column A several times, but not in an order, so I'm not going to use AutoFill to get a whole bunch of them, so I'm going to leave this cell selected, and you may remember that if you want to select multiple cells that are not in a row you use the Ctrl key or the Command key on the Mac, so I'll hold the Ctrl key down, and I will click several individual cells here. Now I'll type one of these names, I'll just use the shortest one, and I'm not going to enter, I'm going to press Ctrl+Enter, and boom, it enters it in all of the cells that I had selected, so let's say I wanted to enter one of the other names in the remaining cells in this area, I can select those and type in, and then press Ctrl+Enter, and it enters in those cells as well. These two techniques aren't earth shaking, but I think they'll save you a lot of time.

  8. Enter Numbers Quickly Using Automatic Decimals - Do you need to enter a lot of numbers with decimal points like dollars and cents? Why not let Excel enter all those decimal points automatically? The file I have open is called Automatic Decimals, and you'll find it in the module 2 folder of the exercise files. If I type something like this, if I want 100.50 I have to remember to press the decimal point, and then whatever's to the right of the decimal point, and then Enter. If I forget the decimal point, and I type something like this, and I say, oh gee, okay, I want that to be 400.50, and I press Enter, well that's not 400.50 because I didn't press the decimal point, so if you need to enter a lot of decimals let's let Excel do them for you, so what I'll do is I'll go up to the File tab, and down here towards the bottom I will choose Options, and on the left side of the left category let's click Advanced, and you see up here near the top where it says, automatically insert a decimal point. Let's just put a checkmark in there. It doesn't have to be two, you could adjust that if you want, and let's click OK and let's use the technique we already learned. I'm just going to delete these here, and let's select these, and now anything I type will automatically get decimals (Typing). You get the idea. Anything that I type in, any number that I type in Excel will take those last two digits, and make those two digits to the right of the decimal point. One thing this will not do is it won't do formatting for you automatically, so let's say I want just maybe 10, 10.00, so I put in a 1 and three 0s. Excel is just going to get rid of those, it says, oh, those are not significant digits, those are 0, so we just won't put them in. That's a formatting feature, and we'll talk about that later on. Now after you're done typing in all the numbers that need decimals you probably want to turn this feature off object it'll drive you nuts, and you turn it off the same way you turn it on. Go back to the File tab, back to Options, back to Advanced, and take this second checkbox, deselect it, click OK, and now you see if I don't type in decimals Excel assumes that I don't want decimals, so that's another nice feature I use every so often.

  9. Generate a List Automatically Using FlashFill - Let's take a look at another automatic feature called FlashFill. Excel tries to recognize patterns in what you're doing, so it can autocomplete the rest of what you're typing. There has to be some consistency for Excel to recognize, and it makes a difference where on the worksheet you type. In the sheet we're about to look at we're going to add to the product list by specifying that each product is available wholesale, so we'll start typing in the column next to the first product. The file I have open is called FlashFill, and you'll find it in the module 2 folder of the exercise files, and what I want to do is I want to add to this list, I want to go down column B, and specify that each one of these products is available wholesale, so let's start over here. I'll go there into cell B5, and I'll type Macintosh - wholesale, press Enter, no big deal, but watch what happens when I start typing the word Empire. As soon as I type that letter E, look at that, Excel understands what I want to do, and it goes all the way down to row 10. Notice it doesn't jump that gap. See row 11 is blank, so it doesn't jump over, but when I press Enter it fills them all in, but I can do it this way. I can go down here to Oranges (Typing), and type that (Typing), press Enter, and as soon as I type the N in Navel it flash fills that also. A couple of things you want to know about this is this FlashFill will work only when you're in adjacent columns, so if I had started typing this down column C Excel wouldn't recognize it, and also, the second item in the list you have to start typing immediately. If you start clicking around or doing other things the FlashFill won't work. You have to type the first one, enter it, and then immediately start typing the second one, and they're all sorts of uses for this. A really good one is entering email addresses where you know everybody is at the same domain.

  10. Managing Lists with Excel 2016 - Editing a List of Names and Addresses - Excel is great for managing lists. If you have names and addresses, product and pricing information, survey responses or almost anything that you want to put in a list, Excel can work like a simple database. This means you extract information from your data using sorting and filtering techniques. Probably the most common type of list is names and addresses like customers, employees or sales prospects, so let's take a look at some methods of sorting and filtering a list. The file I have open is called Large List and you'll find it in the chapter 3 folder of your exercise files, and you notice that this list isn't in any particular order, it isn't sorted by last name or city or state or anything else, so let's go and do some sorting. Let's say we wanted to sort by last name. I'll click any old cell here in the Last Name column. By the way, you notice that I'm not selecting the entire column. If you select and entire column and sort you'll end up sorting just that column independently of all of the others. Now in rare circumstances you might want to do that, but not normally. Anyway, I'm on the Home tab, and over here on the right on the Home tab you see we have this item for Sort and Filter. Now if you're running at a screen that's high resolution, and Excel is wider in your screen than it is on mine, these items here underneath Sort and Filter might actually all be displayed on the ribbon bar. At any rate, let's say I want to sort in ascending order, I'm going to choose this here, Sort A to Z, and boom, there it is, and you notice that it sorts all of the records, not just column B. Now let's click back in here, and I'll scroll, and you can see A, B, C, and so on, and I'll scroll back up. Now this list contains 500 records. How do I know that? First let me click up here on cell A1, and a really nice keyboard shortcut here is if you press Ctrl+ down arrow, notice what happens is my cursor goes to the last filled cell in this current group, I press Ctrl+ down arrow again, and it goes to the next filled cell in the next group. Now you see in row five is where we have the headers, and row 6 is where the data themselves start. When I press Ctrl+ down arrow again, you see we get all the way down to row 505. If I scroll here a little bit you'll notice there's nothing after row 505, so that's how I know that there are 500 records. If I press Ctrl+ up arrow we go the reverse, we go back up to the top, so that's a very nice way of being able to navigate a very large worksheet. Let's do a little bit more sorting. Let's say I decide that I want less names sorted, but I want in reverse alphabetical order, again, I'll click anywhere in that Last Name column, go back to Sort and Filter, and this time Z to A, and here I've got the Z's at the top. Let's say I want to sort by city. Same thing. Click on the city, Sort and Filter, A to Z or Z to A, and there we go, we have the A's first, and then the B's all the way down, but what if you wanted to sort some columns within others? For example, let's say we first want to sort by state, then within each state we want to sort by city, and then within each city we want to sort by last name. To do that we use a custom sort, so for the custom sort it doesn't matter which cell you're clicked on, as long as you're clicked somewhere here in this table, and we'll go back up here to Sort and Filter, and the third item down here I'll choose is Custom Sort, and we get this dialog box, and this dialog box will let us have as many levels as we want, so the first thing we want to do is we want to sort by state, so over here where it says Sort By, click that, and let's choose State. Within each state we want to sort by city, so we're sorted by state, and you see where it says Sort on Values, as opposed to Cell Color or anything else like that, which really isn't what we want here, and the order we can have ascending or descending, so now we want to sort, within state, we want to sort by city, so we go over here and click Add Level, so here you see it says, Then by, and now we'll choose Then by city. Same thing, we'll sort on the values, and we'll sort in ascending order, and one more level, we'll click Add Level, then we will sort by last name, and the same thing there. You notice in the upper right corner it says, My data has headers, that's row 5, and that's why it's a good idea to have headers, so that this dialog box will tell us, oh, hey, you have city and state, not column D, and column E, and all that. It's just easier to work with. Now we just click OK, and there we go. See, so we have Alaska first. Let's just select these. We have Alaska, within Alaska we have Anchorage coming before Fairbanks coming before North Pole, and let's say within Anchorage, let's select just Anchorage, within there you see we have Bickel coming before Hasty, coming before some names that I probably can't pronounce, but begin with the letter V, and if we scroll down further we can say the same thing holds true with any of the states. For example, here is Arizona. Within Arizona we have cities beginning with G coming before P, coming before S, and let's say within Phoenix we have Biscotti coming before Everett, coming before Overfelt, so you can see that we have first by state, then by city, then by last name. Now you might have noticed something a little weird here, and that is you need to be careful when sorting by abbreviations because when the names of the states are spelled out Alabama comes before Alaska, but when we have the states abbreviated you notice that Alaska, AK, comes before Alabama, AL. Also if we go down to the M's, here let's scroll down, when you spell out state names Maryland comes before Massachusetts, but here in the abbreviations MA comes before MD. Now if that's going to be a big issue for you what you could always do is have an additional column off to the right, maybe a column K or column L where you have the state names actually spelled out, and sort on that column, and then go and hide it or delete it later on. Later on in this course I'm going to show you how you can hide columns, and that'll be in the module on managing workbooks. Now I also want to show you how you can filter. First thing I'm going to do is I'm going to press Ctrl+Home, so we get back up to cell A1, we talked about these shortcuts earlier, so let's say I want to filter so that I see only certain states or only certain cities. First thing I have to do is make sure to click somewhere on this table. We go back up to Sort and Filter, and then down over here let's choose Filter, and as soon as you do that you notice we get these little dropdowns on the tops of each columns, and you might need to make the columns a little wider, we talked about adjusting column widths also in the previous module, so let's go and cherry pick some states. I'll go over here and I'll click the dropdown for State, and you'll notice that in this list it shows all of the states listed. Since I just want a few of them I need to deselect those that I don't want. Well, there's 50 states, 51 including D.C., so there's a lot here, so over here where it says Select All, I'm going to click that to clear that checkbox, so now let's say I want to see just maybe Arizona and Connecticut, and I don't know, let's say Indiana, click OK, and I have just those states, and you notice that now instead of going down to row 505 this is going down to row 233. Also notice that those row headers are blue, and there are some gaps in there, so that's Excel's way of telling you that this is just a subset of the entire table. Also, let's scroll back up here, you notice that the little filter button on the top of the state column is no longer a down arrow, it's now a little filter symbol, so that's just another way you know that the state column is filtered. Let's filter this some more. Click back on that filter icon for state, and I'm going to click Select All to put them all in, I'll click it a second time to clear everything, just because I want to see California, only California, it's just the easiest way to do it. Click OK. Now we have a lot of cities in California, and let's say all I want to see is Anaheim, so I'm going to go up here to the city filter, click that, again, I'll clear that and select all, I will choose just Anaheim, click OK, and now I see just Anaheim. Now you might wonder, well gee, did you really have to sort by state, couldn't you just sort by city? Well sure, but maybe there's a city named Anaheim in some other state then you might see those. You could also do custom sorts, like if you want to see certain specifics of last name. First thing let's do is let's go up, and we're going to clear both of these filters for city and state. Now there's a couple of ways you could do it. You could click that Select All a couple of times, and put all the checkboxes back in there or with a single click where it says clear filter, click that and I'll go for State, same thing, click Clear Filer, so just saves me a couple clicks. Let's say we want to find everybody whose last name begins with the letter Co, and we don't care what city or state they're in, so let's click here on the filter, on the down arrow for Last Name, and then go over here where it says text filters, and then from the fly out let's choose begins with, and you see we've got this little dialog box that comes up, and you see where it says Begins with, we're going to make sure we're clicked right over there, and I'm going to type Co, so this'll show me everybody whose name begins with the letter Co, regardless of where they're located. Click OK, and there we go. We could do number filters too. First let's clear this, and I'll clear the filter, and let's say I want to find everybody who has a credit limit of at least $500, let's make this column I a little wider so we can see that, so I will click that down arrow, and instead of text filters you notice that Excel recognizes that this column is numbers because where we had the last name column it had text filters here, it has number filters, so if we want to find everybody who has a credit limit of at least $500, that means $500 or more, so we'll choose greater than or equal to, and I'll type in 500. Click OK, and there we go. If you scroll down you'll see all of these numbers are greater than or equal to 500, and we can filter within this list, let's say we want to find everyone with a credit limit of at least $500 who happens to be in Alaska or Arizona or something. We'll click the down arrow there, clear that Select All, and we'll say just Alaska, click OK, and now we have everyone in Alaska with a credit limit of at least 500, and we can sort this also. Let's say we want to sort this by last name in descending order, click somewhere in the last name, and we'll do like what we did a few minutes ago, and we'll sort Z to A, so you can sort and filter this to your hearts content without worrying about losing any data, and if you want to get everything back to where it was you can simply unfilter and unsort, Sort A to Z, which clicking that is the same way as going up here and sorting A to Z, and I'll clear the state, filters, and the credit limit filter, and we're kind of back to where we started. By the way, there are other techniques that you can use on managing workbooks, and actually we have a chapter later on in this course on managing workbooks, and I'll show you some techniques for handling large worksheets, like for example, keeping row and column titles in view as you scroll. You'll want to make sure to watch that later on, but as for now, in the next lesson I want to show you how you can use a table to do advanced sorting, so let's take a look at that.

  11. Using a Table for Advanced Sorting - Although Excel sheets are all rows and columns, what Excel calls a table is something specific. Tables have certain properties and allow you some techniques that can extract just the information you're looking for. Let's take a look at some of the flexibility we have if we turn our data into a table. The file I have opened is called convert to table, and you'll find it in the module 3 folder of your exercise files, and it's a table kind of like what we saw in the previous lesson. You notice across row 5 we have column headers, so let's click anywhere inside this table, it doesn't matter which cell you click on, just as long as you're clicked somewhere inside it. The ribbon bar, I'm on the Home tab, that's the default tab of the ribbon bar, and over here towards the right in the Styles group you see it says Format as Table. Let's click that, and you see we have all these choices of how the table is going to look. Now when you roll your mouse over these you notice that you are not seeing a preview. We will see a preview in another minute or so. Choose one that you like, I'll choose this one here that has banded rows, and Excel gives us this little confirmation dialog box, and says okay, where do you have your data, and you see that checkbox that says my table has headers, that's row 5, click OK, and now we see the banded rows. I'll just click here to deselect, so now we can see the rows are alternately banded, and we have across row 5 we have those little dropdowns to filter. Because Excel applied this banding automatically we don't have to worry about, gee, what happens if we add or remove rows? Isn't that going to upset the banding? Excel is going to adjust it automatically, so let's say, for example, I'm going to remove this row 12, so I'll put my mouse pointer on that header for row 12, see the mouse pointer is that rightward pointing arrow, click the right mouse button, and from the popup menu I'll choose delete, and you see Excel adjusts. I'll just click to deselect, so you can see it's adjusted there. I'm going to undo, I'll press Ctrl+Z or Command+Z on the Mac. Now you might wonder, okay, how is this different from what we did in the previous lesson when we had the filter buttons across row 5? Well, you notice here on the ribbon bar, because we're in a table, the ribbon bar now has a context-sensitive tab all the way at the right called Design. Now if I click outside the table you notice that tab goes away. If I click back inside the table now that little design tab comes back, and in this design tab we have some helpful features, like these banded rows for example. If I turn that off it turns off the banding, turn it back on. Do I want to have filter buttons or not? I can disable those or re-enable them. Now let's say I wanted to change the style of this table. Here's where we can actually see live previews. Over here on the right where it says, Table Styles, by the way, you will not see live previews on the Mac, you'll see the previews only in Windows, but on both platforms you'll be able to change the look. Over here I'm going to click this down arrow, in the lower right corner of Table Styles, and as I roll the mouse pointer over these you see I'm getting different previews. I'll choose this one here. The two coolest features of this design tab are that you can see an automatic total row, and that you can also remove duplicates, so let's go over here where it says Total Row, click that, turn it on, and notice what happens. Now we've got a row 506, and it shows us a total in column K, in that very last column, and Excel is smart enough not to try to total zip codes or phone numbers or fax numbers. If you decide you want to get rid of the Total row all you have to do is disable it, so obviously we didn't have to write any formula, we'll do that later on in the course. Actually, I think I'm going to leave it off, and let's press Ctrl+Home, so we get back to the top. I want to show you about removing duplicates. Now when we have this list of 500 names and addresses finding duplicates manually is going to be kind of hard, and although Excel does a great job of removing the duplicates, it doesn't show us where they are, so for the purposes of training, since I know where they are, let me show you them. Let's scroll down here to row 105, and I'm going to click, I'm going to select row 105, and I'll hold the Ctrl key down, and also select row 117, so you can see that these two rows are the same. Now let's scroll down a little bit more, so we can see the rows --- in the 190's, and you can see row 191 and row 202 are the same, and now let's go down in the 250's and 280's, and you can see we've got this row 266, row 275, and also row 286, these are all the same. Once again, I'll click in there, I'll press Ctrl+ down arrow, and you can see that the last record here is on row 505, so you know that we have 500 records. Okay, so once again, I'll press Ctrl+Home to get back up to the top, click somewhere here on the table. Now we'll go and remove duplicates. Now right now on the ribbon bar I'm back on the Home tab, so I need to click over here on the Design tab, and then over here towards the left I'll click Remove Duplicates. That brings up this Remove Duplicates dialog box, and we can choose which fields to consider for duplicates. Now if we leave them all checked, if we leave all of the boxes in this dialog box checked it means that every field must match for rows to be considered duplicates. Also, let's stretch this out, I'm going to drag this, stretch it out so we can see all of the checkboxes there. Now what I mean by having all of the fields match, what I mean is if we have a bunch of people by, let's say the same last name and from the same city, these records would not be duplicates if these people had different phone numbers. Right, so if you have a whole bunch of people named Joe Smith you know that they're different people if they have different phone numbers, so I'm going to leave all of these boxes selected, so I'm pretty confident that if I have two or more rows where all of the fields match they really will be duplicate records, so now we'll just click OK, and you see it tells us there are four duplicate values found and removed, and whereas before we had 500 records, and now it tells us we have 496 records. Let's OK that, and let's just make sure. Earlier one of those duplicate records belonged to someone named Lane Haza, H-a-z-a, so let's go and search. A couple ways we can search, we can press Ctrl+F or Command+F on the Mac or we could go back to the Home tab, and over here on the end Find and Select, and then choose Find, whichever you like, and I'm going to find haza, it's not case-sensitive or at least I'm not making it case sensitive, find next, close this, so there is Lane Haza. I'll search again, and it doesn't find it, so whereas before I had three of them, now I only have one of them. Now if you're done using the table features, and you want to turn this back just into a regular worksheet, you can convert it back into a range, so let's go back to the design tab, and again, remember if you don't see that design tab it means you're clicked off the table, it means you're clicked out here in a blank cell, so you need to make sure you click somewhere in the table to get that design tab, and then over here just below Remove Duplicates I'm going to choose Convert to Range, it's going to ask me am I sure, I choose yes, and I'll press Ctrl+Home, and you notice that those little filter buttons are gone, and now even though I'm clicked inside the range that design tab is now gone from the ribbon bar because this is no longer a table, but you notice it leaves the banding intact, so here's a little tip here that I use that if I decide I want a range of cells to have banded rows, and I don't want to go and apply them manually, I will convert the range to a table, and then immediately convert it back to a range, so that's just a nice, fast way of getting banded rows.

  12. Filtering with the Advanced Filter - Although the advanced filter isn't as interactive as the methods we saw previously in this module, it does have the advantage of showing all the criteria at once, and it allows us to do some database type of relationships between the criteria. If you've ever used databases you'll find some of these techniques and concepts familiar. To use the advanced filter we setup the worksheet into three different sections. The first section is our original data, what Excel calls the list range, and it has the column headers. Now below that we have what they call the criteria range, and at the top of the criteria range we just have a duplicate of those column headers, and in the criteria range we specify which data we want extracted, and then finally below that we have the copy to range or what I generally think of as the destination range. That has a third copy of the column headers, and you notice here in this slide I've kind of made it fuzzy, and the reason is this, the list range we know we have a definite number of rows, and in the criteria range we have a definite number of rows, but in the copy to range you don't really know how many rows you're going to get until you actually run the filter, so let's set it up and take a look. The file I have opened is called Advanced Filter, and you'll find it in the module 3 folder of your exercise files, and it's basically the same table that we've been looking at previously in this module, but the first thing we want to do is copy and paste the headers. I also want to zoom out here a little bit, so I'm going to click this little minus symbol, so that I can see all of the columns clearly, and I'm just going to select across row 5. Incidentally, on my screen, I don't know about yours, you notice that column G, the zip code column, we have a bunch of little pound signs in there. That simply means that zoomed out here Excel doesn't have enough room to show all of the numbers, so if it makes you feel more comfortable you can take that zip code column and stretch it out. By the way, you don't have to gingerly select across row 5 if you prefer when we deselect you could just click the header for row 5. At any rate, let's copy, I'll press Ctrl+C or Command+C on the Mac, and let's scroll down to the bottom a little bit more, and let's say I want to put the criteria range a little bit below that data, so I'll just maybe click here on row 508, and I'll paste, press Ctrl+V or Command+V on the Mac, and a little bit farther down than that, maybe on row 512, doesn't really matter, doesn't have to be exact, and I'll paste a third time, and I want to clear off the clipboard, so I'll just press the Esc key. Because this is a database I tend to think of the filtering as more of queries than a filtering, but whatever terminology you want is okay, so let's start with something simple. We want to find all of the records where the state is Alaska, so let's go right over here into the State column and the State cell in the criteria range. We haven't defined it as a criteria range yet, we will soon, and I'll make that AK, and it's not case sensitive, so you can capitalize it if it makes you feel better. Remember you do have to enter it though, so now let's actually go and do the filtering. Click somewhere in your table, and up here in the ribbon bar let's go to the data tab, over here in the sort and filter tab we have the item called Advanced, click that, and it's going to scroll up for us automatically, and you see the marching ants are around our original data. Now this dialog box here is asking us, okay, where do you want to filter the list? Do you want to filter it in place or do you want to copy it to another location? One kind of annoying thing is every time you run this you need to select that second choice. For some reason Excel always defaults to the first choice. Now the list range it already defined for us. If you click in there you see the marching ants again. You don't really have to do anything here, but the criteria range we have to define, so let's click in there, and scroll down, so the criteria range I want to make just those two rows, row 508 and 509, but I'm not going to select the headers because that's too much because right now we're not formatting or copying and pasting, we're defining specific cells for the criteria, so you see I'm selecting the column headers and one and only one row underneath, and that gets put in that box. Now the Copy to range is going to be a little different. Let's click in there, you can hit the tab key, whatever you like, and now let's select just the column headers, and notice I'm being very careful to select only the column headers, and I'm not selecting any rows underneath it, and the reason is let's say that there are, just for arguments sake, let's say there are 10 records in Alaska, and I select maybe two rows underneath this 512, I select rows 512, 13, 14. Excel will only filter those 2, it's only going to show me those 2 of those 10 rows, so you don't want to do it. When you do the Copy to range only select the column headers, don't select any rows underneath it, and now let's click OK, scroll down if you need to, and now you can see all of those records where the state is Alaska. Now let's say we still want to see all of the records where the state is Alaska, but only those where the last name begins with the letter C, so in the criteria range let's go over here, click in that last name box, and to extract just the last names that begin with the letter C, that really means the letter C and anything after it, so I'm going to type C and then a star, I'm going to type an asterisk any characters, any number of characters, and I'll press Enter, so you notice that going across row 509 we have two criteria. We have the last name criterion, and we have the state criterion, so what's happening is going across the row we have what databases call an and relationship, and meaning we need both of these criteria to hold true in order to extract the data we want, so once again, let's run the filter, scroll up if you need to, click anywhere here in the original data area, back over here we're on the data tab, we click Advanced, and this is what I was talking about before. You always have to select this second option because Excel is always going to change it, but we don't have to change anything else. The list range is the same, the criteria range is the same, the copy to range is the same. All we have to do is click OK, scroll down, and now we still have the records where the state is Alaska, but only those where the last name begins with the letter C. Now let's do this with numeric values. Let's say we want to find all of the records where the state is California, and the credit limit is under 500, so let's go over here to Last Name, I'm just going to delete that, I don't care what the last name is, I'll click here on the state, and I'll make that CA, I'll just type right over it, and press Enter or press the Tab key, and let's go over here to credit limit, so we want to all of the records where that number is less than 500, so type in a less than sign, 500, enter that, and now let's run this again. Click back in your data range, go back to Advanced, once again, we have to select copy to, these three items ae the same, click OK, scroll if you need to, and now we can see all of the records where the state is California, and down column K, the credit limit, every one of those is under 500. Let's scroll back up, so we can see the criteria range, and I said before that going across a row we have an and relationship, meaning that all of the criteria would have to hold true in order to extract the data, but going down each column, that is going down the address column, down the city column, and so on, that is an or relationship, meaning that any of the criteria can hold true in order to extract it, so let's say, for example, we want to still see everyone in California with a credit limit under 500, but we also want to see all of the records where the state is New Jersey, and we don't care what the credit limit is. Let's go down here, and what we are doing is actually we are adding a row to the criteria range, so I'll have to make an adjustment in that dialog box, so I'm going to make that MJ, and enter, and that's it, I don't care anything else about what's happening in New Jersey, as long as that's the state, so let's click back in the original data area, we're back in the Data tab, click Advanced, and once again, we have to select this copy to. The list range hasn't changed, but the criteria range has changed, and scroll down if you need to. You see right now the criteria range only has that first row underneath it where it has California? You see the marching ants are not around New Jersey, so you could adjust the little formula in here. I find it's easier just to delete that and drag all over again. Whatever you prefer is fine. I have the column headers and the two rows underneath it, the copy to range is the same, now let's click OK, and now you see we have New Jersey of all kinds of credit limits, and California of just under 500. Actually, you know what, let's go and sort it, so we can see it a little easier. Click somewhere here in that State field, and you see we don't have to go to the home tab to sort, we could do it right here, and I don't need to do Custom Sort, I'll just click this A to Z button, so now I can see everyone in California, and you notice all of those numbers are still lower than 500. Let's scroll down, and we'll find New Jersey, let me just select these records to make them easier to see, and you notice that some of these are under 500, some of them are over 500. That's because, and when we take a look here at the criteria range, California has that and relationship where we need to find California and a credit limit under 500, but New Jersey, the New Jersey row does not have any and relationship because we did not specify any credit limit. One last example, let's say we do want to find all of the records in New Jersey where the credit limit is under 500. All we have to do is click in there and make that less than 500 and enter it. Now let's run the filter, click back in the original data area on the data tab, back to Advanced, choose this, copy to another location. We already adjusted the criteria range, we don't have to do that again, so all we really have to do is click OK, scroll down. We do have to sort again, though, click in that State column, click A to Z, we're still here on the data tab, and we can see California, everybody is under 500, and now in New Jersey, see we have fewer rows of New Jersey, and again, everyone in New Jersey is also under 500. You see the Advanced filter is very flexible. It works kind of like a database, and you can use it on text and numbers, and sort and filter to your hearts content.

  13. Finding and Replacing Data - The find and replace dialogs in Excel allow you to be specific for what data you want, and even where and how to look for the data. Once you see how the find dialog works you'll see the replace dialog is similar, it just adds some functionality. A straightforward way of finding data is to use the Find command. The file we'll have open is called Find and Replace, and you'll find it in the module 3 folder of the exercise files. In Windows you could press Ctrl+F, on the Mac you could press Command+F or on the Home tab here of the ribbon bar you could go all the way to the end and choose Find and Select, click that little down arrow, and there is Find at the top, and if you roll the mouse over it you may actually see the little popup, that shows you the keyboard shortcut, so I'll choose that, and I get this little dialog box. This dialog box has a little bit more to it, and you may or may not see it on the screen. You see over here this little options button? If you click that, that gives you some more options. Imagine that. We'll use those in a little bit, but first let's just say I want to find someone who is on Park Avenue, so I'll type park ave and right now it's not case-sensitive, and I'll click Find Next. It finds somebody on Park Avenue there, that's row 263, keep that in mind, find again, there is someone else on Park Avenue, someone else on Park Avenue, that's the third person so far, and there is no one else on Park Avenue in this list, so it just cycles back to the top. Now let's take a look at this option for match entire cell contents. First thing I'm going to do is I'm going to click in the list, I'll just go back to the top, I'll press Ctrl+Home, so I'm back to the top of the list, and I want to find someone whose name is Carol, so I'm going to take this find one, I'm going to --- select to delete park ave, I'll type carol, and Find Next, so it finds someone named Carol, okay, let's see who else there is. Well it also finds Carolina, it also finds Caroline Avenue, so it does find the text string carol, but in a couple of instances it finds carol with a few additional letters afterwards, so once again, I'll click in the sheet, I'll go back up to the top, and this time in the dialog box I'm going to turn on Match entire cell contents. Now when I Find Next it finds carol, but if I click Find Next again, it doesn't find anyone else. Another command we can use is Find and Replace, so we can find text or numbers, and replace them with something else. Once again, let's click back in the sheet, go back up to the top. There's a few ways you could get to replace. One way is since we already have the Find Dialog box open we can click the Replace tab, we can just close out of there for a moment. Two other ways you could do it is you could go up here to Find and Select and right off the bat you see you could choose Replace, and the shortcut is Ctrl+H or Command+H on Mac, so it's three different ways you can get in there. Here's what I want to do. The person who typed this list made a mistake and they abbreviated Hawaii as HA instead of HI, and you can see there's an example right over here, so rather than manually searching through or doing a Find, and then manually changing it, we want to find every time the state is listed as HA we want to change it to HI, but we want to avoid introducing errors, for example, in the middle of Ithaca we have HA, we don't want to change that to HI in the work Ithaca, and any other similar instances. Let's go back up here to the top. Let's do this. Where it says Find what let's just delete carol and let's find HA, and let's type that in capital letters, I'll just press the Tab key, we will want to replace that with HI, also in capital letters, and over here we want to Match case, so it'll only find HA, that's in all caps, and also find those cells where we match entire cell contents, only find those states where HA is the only thing in the cell. Now if you want to do this one at a time you could go over here, click Find Next, there it finds an instance, and then you can choose Replace, and it replaces it and immediately goes onto the next one. Then you could see at the top of the screen on row 10 it did change HA to HI, but let's say we could potentially have dozens or hundreds of these. If we're confident that we won't introduce any new errors we could replace all of them in one shot, and that's what this button does over here, Replace All. It confirmed it, click OK, and let's see if it really worked. First of all, let's close this dialog box, and let's go and sort, so we'll sort from A to Z, scroll down, and here we go. Every instance of Hawaii has now been changed from HA to HI. The Find and Replace dialog box is a great little feature that can save you a lot of time and effort.

  14. Calculating with Formulas and Functions - How Do You Write a Formula? - Excel is all about numbers of course, so I'll show you the building blocks, adding, subtracting, multiplying, dividing, and using exponents. We'll start off with simple manual techniques, then move on to more automatic techniques I think you'll really like. The building blocks of calculations in Excel are formulas. In this lesson I'll give you an overview of how they work. Formulas in Excel always start off with an equal sign, and there's never an exception to this, so let's take a look at a simple example. Let's say we have 10 in cell B3, and we have 20 in cell B4, and we want to add them, and put the result in B6, so B6 is where we edit, so we'll put an equal sign in cell B6, and then following the equal sign we use the cell references to put in the formula, and we say =B3+B4, and you notice that Excel will color code them. Not only that, but you don't even have to type in the references B3 and B4, you could just click on the cells and Excel will write that part of the formula for you, that is it will insert the cell references for you, and be careful not to put a plus sign after that before because if you do you'll get an error, it'll be B3+B4+ what, so after you put in that formula you simply enter it, and then you get the result. Now one thing that you should not do is don't enter the numbers directly, that is don't hardcode it because if you hardcode it the first time you do it you're going to get the correct result, but what if that 10 or that 20 changes and you hardcode it, well then you're going to get an error like that, and that's what's so great about Excel is that when you're using formulas, when the inputs change, the formulas change automatically, so that's just something you never want to do. Okay, do you have it? Good. Then let's go onto the next lesson, and we'll actually do it in an Excel worksheet.

  15. Adding, Subtracting Basic Number Formatting - Now that we see how to write formulas we'll do some basic ones going across rows and down columns. I'm going to show you exactly what you need, so don't skip any steps or add any additional ones. If you'd like to get the practice of creating this worksheet on your own you can pause the video and type everything in, and then continue when you're ready. We haven't talked about formatting yet, so don't worry about the formatting across row 1. If you'd rather not do the busy work, then you can use the file that I have open. It's called basic formulas, and you'll find it in the module 4 folder of your exercise files. I'll start off by showing you the basic method of adding numbers, it's what I think of as the cherry pick method. With the cherry pick method we say, I want to add this cell, and this cell, and this cell, so we want to start by adding a cross row 5 for the first product, so let's click over here in the Totals column for row 5, that's F5, so I'll start off with an equal sign, I type that in, and by the way, as soon as I type in the equal sign I'm editing the cell, so you could see the ribbon bar is grayed out, and we have the word Enter in the lower left corner just like we talked about earlier, so I'm going to say give me the Quarter 1, and I click that. Now when I click it you see the marching ants are around cell B5. We also see the reference for B5 is in the cell we're editing, it's in that total in cell F5. Also notice it's color-coded, so I'm going to say plus what? Plus Quarter 2, plus Quarter 3, plus Quarter 4, and that's it. Don't type another plus sign because if you do you'll get an error, and now enter it, and now we get the result. We're now on the next row ready to enter the sum for the next product, so let's do that for practice, so we'll say equals Lemons in Quarter 1, plus Lemons in Quarter 2, plus Quarter 3, plus Quarter 4 just like we had earlier, and enter it, and there we go. Now if you want the practice of doing this you can pause the video and enter the totals for the remaining products, but rather than doing that I'm going to show you the autofill, and we talked about autofill earlier in this course, and I was showing you like how to fill in the names of months and days and so on, and I said that you can use autofill as a quick and easy method of copying and pasting formulas, and that's what we'll do now. I'm on the totals for lemons, and I'll put the mouse pointer on that autofill dot, right, so remember the mouse pointer changes to that crosshair, and I'm going to click and drag down to the bottom, and there we go, it fills in those formulas, but it's even easier than that. I'm going to undo, I'll press Ctrl+Z or Command+Z on the Mac, and instead of dragging, instead of getting that autofilled out and dragging down to the bottom, all I have to do is when I get my mouse pointer on that autofill dot I double-click, and it automatically fills down to the bottom. Now when I'm filling down four rows, okay, it looks cool, but it's really not a big deal, but imagine if you have hundreds of these or thousands of these rows. It really is a big deal to double-click and go all the way down. The double-click method to do autofill will go down until it finds a blank row. As soon as it finds a blank row it stops, so if you do have a lot of data, but you have blank rows between. let's say groups, then you will have to go and double-click each one to get to the bottom of a group. Adding down the columns is pretty much the same thing as adding across the rows. Let's go here for Quarter 1. We'll click in cell B12, and we'll say equals oranges, plus lemons, plus limes, plus the rest, and again, finally when you get to the last product you stop, no need to type in a plus sign, and something also that I showed you earlier, is instead of pressing Enter and going to row 13, and then having to grab the mouse, find the mouse pointer, and click, all you have to do is press the Tab key, and that enters, and goes to the next one. One more time for practice, we'll say equals the first one plus the second, and so on. Remember not to enter the plus sign again, and this time instead of tabbing across I'm just going to press Ctrl+Enter, so that we enter it and we stay on the cell, and you know what I'm going to do here. I'm going to get the autofill dot, and I'm going to drag across. Incidentally, the double-click method that I showed you a moment ago, that works for going down columns, but it will not work for going across rows, for whatever reason. Okay, that's adding, what about subtracting. Subtracting is mostly the same thing, so let's change this sheet a little bit. Let's say that these are actually subtotals, so let's go here and to cell A12, you could edit if you want, I'm just going to type the word, I'm going to type over it, and I'll call this subtotal because let's say we have returns. We had some rotten fruit that got returned, and we need to account for that, so let's go in here, and I'll call this Returns, and I'll press the Tab key, and then I'll just type in a few numbers here, let's say we have 85, 45, okay, and we want to get a total of those returns, so here we'll just add again, we'll say equals all of these returns, and I'll just enter that, so let's go down here into row 15, and in there we'll put in our net sales, so we'll call this Net Sales, and Tab over, and all we have to do is take the subtotal, and subtract the returns. We'll say equals that subtotal minus the returns. Then again, I'll press the Tab key, and one more time for practice, we'll say equals the subtotal for the second quarter minus the returns for the second quarter. This time I'll press Ctrl+Enter, and again, I will autofill all the way across. Then I'll just click somewhere to deselect. By the way, you notice that little icon there in the lower right corner below that last Net Sales, ignore it for now, we'll talk about it later on. Now you might be wondering to yourself, gee, isn't there maybe an easier, faster way, something that I can say, hey, just let's grab all of these cells at once and add them in without specifying each one? Yes there is, you do that with a function, and we'll talk about that later on in this chapter.

  16. The Order of Calculations is Critical - Before you write anything more than a simple formula, like what we did in the previous lesson, you should know the order in which Excel does calculations to avoid errors. Excel uses the standard order of operations, so let's look at an example. If I wrote down this formula, 4+5*6, forget about Excel, if I just wrote that down with pen and paper and asked you what the result is what would you say? Is the answer 34 or is it 54? The answer to 4+5*6 is 34. If you said the answer is 54, well sorry, that's the wrong answer, and here's why. In the standard mathematical order of operations you do multiplication before you do addition, so with this formula we say 5*6, which is 30, and then add the 4 to get to 34, but wait, let's say you really did want to add the 4+5 before you multiplied times 6, then you use parentheses, and if you put the (4+5) in the parentheses, and you say 4+5 is 9, and then you take the 9 and multiply by 6, then you can get 54, but you have to use those parentheses to do that. Okay, so what is that order of operations? The first thing you do is parentheses, that takes precedence over everything else. Second priority are exponents, that is if you're raising something to the power of something else, followed by multiplication, division, addition, and last priority is subtraction. You may remember from back in grade school there's a pneumonic to remember this, Please Excuse My Dear Aunt Sally, P-E-M-D-A-S or some people call it PEMDAS, parentheses, exponents, multiplication, division, addition and subtraction. Okay, so let's see this work on an actual Excel worksheet. I have just a plain blank worksheet open, and forget about cell references for now, we're just going to type in the numbers because we're really interested in seeing the order of operations, so if I say =4+5*6 and Enter it I get 34. If I want the addition first, still have to put an equal sign, but then I'd put in parentheses, (4+5), close the parentheses, times 6, and then I get 54. That is how Excel, and math in general, handles the order of operations.

  17. Squaring and Cubing Numbers - We can raise any number to any power we want, but squares and cubes are the most common exponents, so that's what we'll do in this lesson. If you know how to raise a number to one power you can raise it to any power. If exponents make you wonder if Excel does square roots, yes it does, but we won't be covering them in this essentials course. Apeeling Fruit Company has two projects coming up. One is that we have to retile the sales floor, so we have to know how much material to buy, and it just so happens the sales floor is just a square room. The second project is that we're going to test a new product called Juice Balls. Rather than putting juice in bottles or boxes we decide, for whatever crazy reason we want to put them in balls, so we need to know the volume, so we can fill them with the right amount of juice, and you see I have the formula over there on the right, and we'll come back to that in a minute, so let's take a look at the first example. We need to know what is the square footage of this square room, so in cell B7 we're going to put in an equal sign, and we're going to say, take the side of a room, doesn't matter if it's the length or the width right, because it's a square room, and we're going to raise it, we'll use the caret mark that's on top of the number six on your keyboard. We're going to raise that to the power of two, and that's it. I'll Enter it or Ctrl+Enter it, and we can see it's 1444 square feet. The volume of the sphere is going to be a little more complicated, and there's the formula that I gave you, it's 4/3πr³. Now you can see over here in cell B12 we know that the diameter is 10 inches, so we have to divide that in half to get the radius, so let's go down over here, I'll click in cell B13, put in an equal sign, and let's start off the formula, so we're going to say 4/3, right, that's 4 divided by 3, times π, so we'll put in *, and I'll estimate it to 4 decimal points, 3.1415. Now we need to get the cube of the radius or I should say we need to multiply by the cube of the radius, so I'll put in the asterisk to multiply, so to get the cube of the radius we have to take that diameter divided by half, so this is where the parentheses comes into play, like we were talking about in the previous lesson, so in the parentheses I'm going to say the diameter, I'll click that diameter, divided by 2, close the parentheses, and now we need to cube it, so I'll raise it to the power of 3. I'll put in that caret mark and 3. That's it. I'll enter it, and now I know that a ball that is 10 inches in diameter has about 523-524 cubic inches, but we're only half way to the solution of our problem. We need to know how many gallons to put into one of these balls, and again, over there on the right, below that formula I showed you that 1 gallon equals 231 cubic inches. Now that's a constant. We don't have to put that in a cell somewhere because that's never going to change, so let's just go down, I'll hit the down arrow to get to cell B15, and this is simple division, so I'm going to say equals the volume of that sphere, divided by 231, and this is kind of one of those rare instances where I can hard code it because, as I just said, that's never going to change, and when I enter that I can see we need about 2.2 or so gallons, so this way if you're trying to figure out, well maybe I want to put in, maybe I want to sell a few more gallons or fewer gallons, then you can just change the diameter of the ball, and I can say, well, what if I have a ball that's maybe 9 inches in diameter or 7 inches in diameter, you could see the gallons that we need are going to change. By the way, if you happen to be a basketball fan, and you say, gee, what if I took a basketball and filled that with juice, how many gallons? Well, it just so happens that a regulation basketball is 9.39 inches in diameter, so that would be about 1.9, just shy of 2 gallons, so there's a piece of trivia for the next time you're talking with your friends, and you can tell them, I know that a basketball holds just under 2 gallons of liquid.

  18. Understanding How Functions Work - Now that you see the concept and syntax of how functions work let's put this knowledge to practical use. Like before, make sure to type exactly what I show you because Excel syntax is very specific. For example, let's say you're taking out a loan, and you want to know how much you have to pay back every month, so you take the interest rate, the number of months to pay back, and the principle, you feed these inputs into the payment function. The payment function chews them up, and then it outputs the result as the monthly payment, so how do you actually write a function? Functions go inside formulas, so you always start off with an equal sign. After the equal sign is the name of the function, and then you have at least one set of parentheses. Usually you have a few arguments that go inside the parentheses, but there are a few functions where the parentheses are empty. For example, the SUM function will add a range of numbers, which is something I alluded to earlier in this module. Excel has about 400 functions, so there's probably one for anything you need. Let's go into the next lesson, and I'll show you how to create the SUM function, and also how to use the AutoSum tool.

  19. SUM Function and AutoSum Tool - Now that you see the concept and syntax of how functions work, let's put this knowledge to practical use. Like before, make sure to type exactly what I show you because Excel's syntax is very specific. The file I have open is called sum function, and you'll find it in the module 4 folder of your exercise files, and it's similar to what we had open earlier in this module. We have products that we're selling, and we have quarterly sales, so we want to get the total of the products over the four quarters, and we want to get the totals of the quarters themselves, but rather than using the slow, tedious cherry pick method we'll use the SUM function to just grab a bunch of numbers, so let's start over here, we'll get the total for Pinot Noir in cell F5, and we'll start off writing the function, writing the formula, we'll say equals, right, because we always start off with an equals, and you literally type the word sum. It's not case-sensitive. Excel will turn it into uppercase. You notice that there's a little bit of help coming down there, let's just ignore that because I want to show you how to do this manually, so I'll open up the parentheses, and again, it's giving you a little bit of syntax. Now all I have to do is select these four numbers, and be careful you're only selecting these four numbers. You don't want to drag into the total itself because then Excel will give you an error that you're doing a circular reference. That's it. We're saying the sum of B5 through and including E5. That's what that colon means in that formula. The colon means starting from that, through and including the last one, whatever is there in between, and close the parentheses, enter it, and there we go. Showing us a little bit of a shortcut here, again, let's say =sum, this is just for practice, open up the parentheses, and now we're going to drag across Dolcetto, and again, remember you're only doing that second row, be careful not to drag in numbers from the first row or the third row. Now because this is a very simple function we only have one set of parentheses, you don't even have to type the closing parentheses, Excel will insert it for you, so you could just enter it or Ctrl+Enter it, and there's the result. You probably know what I'm going to do next right? We have the autofill dot, so you don't have to write the whole formula again. Take that autofill dot and either double-click it or drag it down to the bottom, and now we've filled in the rest of our totals, and you can take a look here. I'll click in this one you can see in the formula bar it's the sum of B7 through E7, B8 through E8, and so forth for each of the remaining ones. What if we want to get the totals of the quarters? Pretty much the same thing. Let's go over here into cell B12, and we'll use the SUM function to add the first quarter, so again, we'll say =sum, open the parentheses, and let's just drag down. We'll start from the first number, through and including the last number. Be careful that you're not dragging anything else, be careful you're not missing anything, and that's it. The sum of B5 through B10. That coon is B5 through and including B10, and remember, all you have to do is press the Tab key to enter and go to the right, so you're not pressing Enter, and having to do all kinds of contortions with the mouse and the keyboard, so let's do one more time for practice, we'll do Quarter 2, and we'll say =sum, open the parentheses, select all those numbers down quarter 2, and this time I'll press Ctrl+Enter, so I could enter and stay in the cell, and again I'll autofill it, double-clicking the autofill dot only fills in automatically going down columns, not across roads, but that's okay. We'll just click and drag across, you've just got to make sure that you're using that crosshair mouse pointer, and there we have our results. Now there's even an easier way. Adding is the most common calculation you do, so Excel actually gives you a toolbar button of that. First thing, let's' do this, is let's just select and delete all of these numbers, just drag over them, hit the Delete key, let's go back up to the top there, and I'm on the Home tab of the ribbon bar, and you may know that the Greek letter sigma is what you use to symbolize sum, summing anything, and if you take a look up here we have AutoSum. All I have to do is click it, and looky here, Excel puts in the formula, it writes it, it puts it in uppercase, we don't really care about that one way or the other, and you can see the marching ants are across the correct numbers, and we have the formula there, and that's it, we can Enter it, and here's why autofill is really important because it can actually eliminate errors. Now in the second row we won't get an error. If I just click the AutoSum tool you see the marching ants are across the correct ones, and that's fine, but let's take a look at what's happening here down the third row. We have two numbers directly across the current cell, right, we're selected in cell F7, and we have four numbers to the left. Now Excel can't read my mind, that might come in the next version, but for now if I click it look what happens. Excel sees numbers above the current cell, and to the left of the current cell. It has no idea what I'm thinking. It has an algorithm saying that, well you add down more often than you add across, so those are probably the correct numbers, so in this case you don't just want to blindly accept it because you're going to get the wrong number, but that's okay. All you have to do is leave this function open, leave it editing, just take your mouse and drag across the correct ones, and you see that Excel rewrites the formula for you on the fly, but again, you really didn't even have to do that because in real life, let's just select and delete those, in real life I would have taken just the first one, taken the AutoSum, Ctrl+Enter, and then autofill down. In fact, I could even save you a couple steps on that. Let's just delete that, and we'll go back here into F5. If you're confident that the AutoSum tool will give you the correct result, and you won't have to adjust it you don't even have to click it and Enter, and all that, all you have to do is double-click the AutoSum tool, and it puts in the formula and enters it in one step, and then you can take the autofill dot and double-click, and come down there to the bottom. Okay, so let's use the AutoSum tool for the quarters. Let's go down here into B12, click the AutoSum tool, it puts the marching ants. Notice that it does include that blank cell below it, but that's alright, and then you can tab it, and do it again, and again, and then autofill it, etc., and again, just like before, I'll just delete all of these numbers here. This time I'll double-click the AutoSum tool. We know it's the correct number, and you can see in the formula editing bar it's correct, and now we can autofill it across there. You can see this is a lot faster than using that tedious cherry pick method, and would you believe there's even a faster method? There is, and I'll show you that in the next lesson.

  20. Adding a Whole Worksheet with Just Two Clicks - Okay, this is really the best part of this entire module. If you have a worksheet of solid columns and rows, that is, there are no gaps, you can add the whole thing literally in 1 or 2 seconds. This is a little secret that only Jedi masters know, so let's go ahead and add up this whole worksheet with just two clicks. The file I have opened is called two clicks, and you'll find it in the module 4 folder of your exercise files, and this is setup pretty much the same way that previous worksheets have been in this module. You can see we have a bunch of products going across rows, we have our four quarters going down columns, and if you take a look here you notice that, just like before, column F is blank, that's where we put in our totals for the products, and row 12 is blank, that's where we put in our totals for the quarters. Okay, let's see the two clicks already. Let's do this. Let's select from that first number there, Chardonnay in Quarter 1, and we're going to select down and across, and be careful you're using the big, old, fat plus sign to select, you're not using the autofill crosshair, you're going to select down and across to column F and row 12, so you have all of the numbers selected, and you also have that blank total column, and that blank total row selected. Okay, so the second click is the AutoSum tool. Just click it, you don't even need to double-click, click it once, and boom there it is, and you don't have to worry about Excel taking maybe, you know, across row 7, and adding the wrong numbers or anything. Excel is adding all of the rows straight across, it is adding all of the columns straight down. Let's take a look. Go in here to the first total, we can see there's the sum of B5 to E5, and then if you just hit the down arrow on your keyboard you could see each one individually, the formula is correct. It's, in each case, taking column B and adding through column E, and it's just changing the row, and if you take a look down here across row 12 in each one it's taking each column by itself and adding all of the individual rows. By the way, this gives me the opportunity to show you another really great shortcut, and this shortcut is actually identical on Windows and Macintosh, and that is, what if you want to see all of the formulas on the screen all at the same time because right now I have to use the mouse or I have to use the arrow keys on the keyboard to go to each individual formula and look at the formula itself in the editing bar, so if we want to see all of the formulas all at the same time press Ctrl+`, that's the key to the left of the number one and above the Tab key, so in both Windows and Mac you use the Ctrl key, Ctrl+` shows you all of these, and I'll scroll over here, so you can see going down column F, as well as going across row 12, so now visually you can see, yes indeed, those formulas are correct. This is a great shortcut if you need to debug a worksheet that's not working so well. To get back to the worksheet as normal it's the same shortcut. Press Ctrl+` accent mark again, same on the Mac as in Windows, so now you know how to add worksheets accurately and very quickly.

  21. Formatting Worksheets - Row and Column Size and Alignment - In the module on basic formulas we saw how to format numbers with a few clicks on the ribbon bar. In this module we'll see how to format a whole worksheet using just a few clicks, and also how to insert and modify pictures. There are a few ways of adjusting the widths and columns and the height of rows. You can do it by eye, you can have Excel do it automatically, and you can also do it by number. Let's take a look. Let's start off by looking at column widths and row heights. The file I have open is called rows and columns, and you'll find it in the module 5 folder of your exercise files. Take a look at columns B through G. Right now they're slightly different widths, and let's say we want to make them all the same width, and we want to know specifically how wide they are, so let's start by selecting them. Put your mouse pointer on the top here of column B, so you get that downward pointing arrow, and make sure that you're on the header, that gray header of column B, not over here on cell B1 because cell B1 is the cell, the header is the header, and just click and drag across, so you have all that is selected, and to make them an exact, specific width just right-click on any of those headers, and from the popup menu choose column width, and you see the box is blank because they're all slightly different, it doesn't know what to say, so let's say we want this 10, and you might wonder, well, 10 what? Excel has kind of a weird way of measuring column widths. This 10 means 10 characters wide, and those are the characters of the current font. Now I'm using a Windows machine, so right now the font, I'm just using the default font of Calibri 11 points, so this means 10 characters wide of 11 point Calibri, and I guess it means an average character, right, because some characters are wider than others. Anyway, it's just bizarre, that's what we have to deal with. Click OK, and now you see they're all the same width. If you don't like doing the right-click, if you want something from the menu, you'll notice I'm on the Home tab, and you can go over here in the Cells group, you see there's that Format icon, you can click that, and choose Column Width, and you see it's the same dialog box, so cancel out of that. Another way we can deal with column widths is this, let's deselect somewhere, and let's make each of these columns a different width, I'm just going to randomly change them to some very narrow, you see this one is way to narrow, so we have those pound signs, I'll make this one way to wide, so they're all obviously different. Let's select those same columns again, and this time put your mouse pointer on the boundary between any two column headers, and when you get that two headed arrow double-click, and now it adjusts each of those columns, so each column is exactly as wide as the widest item in that column, so unlike before, the columns are not all the same width, they're all different widths, but they're all as wide as they need to be. Row height works mostly the same way. Let's say we want that row 1, where we're going to have the company name header, we want that to be taller, so put your mouse pointer on the header for row 1, and also make sure it's that rightward pointing arrow, and that you're on the header, you're not over here on cell A1, you're over here. Right-click and choose row height. Right now it's 15. Let's set that to 40. That actually does mean 40 points, click OK, and now you see it's taller, and we also have a regular dialog box if you don't like the right-click menu. Let's do the same for row 4. Click that header for row 4, and over here still on the Home tab in the Cells group click Format, and let's choose Row Height, you see that's at the top. Let's make that 25. I'll just type 25, and press Enter, and I'll just click somewhere to deselect. Now you notice that by default the text here on row 1 and row 4 are aligned to the bottom. Let's say we want these vertically aligned to the middle. Let's do them both at the same time. Select the header for row 1, hold the Ctrl key or Command key on the Mac, and select the header for row 4, and still up here on the Home tab we have this icon here. If you click that, see there's even a little helpful popup, click that, that aligns to the middle, and let's say we want to take these column headers and align them horizontally to the center. Let's select those, and up here in that same area we have this button here, which will center horizontally to the middle. I also want to show you how to put in all of those nice colors and fonts and all that, and we'll do that in the next lesson.

  22. Using and Modifying Built-in Styles - Many programs use styles and Excel is among them. The style is like a package or a basket of formats that you apply at once, so it's the most consistent way of applying formatting. If you want to change the format of the cells that you've styled all you have to do is change the style itself, and all the cells tagged with that style immediately change, so that not only helps consistency, it's also a great time saver. The formats can include the name of the font, the size, the color, the cell's background, and the number format, but styles do not include row height or column width. In this lesson we'll first apply built-in styles, and then we'll make some custom changes to the styles. You notice that this workbook has two worksheets in it, and I'm on the first one called creating styles. Let's start by applying style formatting to the column headers, so let's go and select across these column headers, and I'm on the home tab of the ribbon bar, and over here in the styles group let's select Cell Styles, and we have all of these premade styles, and here's what's really cool if you're using a Windows machine is if you roll the mouse over any of these styles you can see a preview. If you're using the Mac you won't see the preview, you actually have to click one to see how it looks. Choose whichever one you want. I'm going to choose this one here called Accent2. I also want to make this bold, so I can press Ctrl+B or Command+B on the Mac or click the bold button over here, and just click it to deselect, so you can get a better look at it, but what if none of those styles appeal to you. Oh sorry, that pun was intended. Well let's do this. Let's select from A1 across those seven cells, and we'll create formatting manually, but then we'll turn it into a style because, as I said a minute ago, I want you to see how you can create your own styles, so let's start by selecting a font, and let's go up here, and again, if you're in the Windows version when you roll over the fonts you'll see the little preview behind it. This will not happen in the Mac version, and keep in mind the fonts I have on my computer are going to be somewhat different from what you have on yours, and there is different fonts in Windows and Mac, so don't stress too much, just choose one that you like. I'll choose this brush script, I think I need this a little bigger, so let's do that, and let's give the font itself a color, and we'll go up here, and I'll give it some fruity color, and let's give these background, so you have this little paint bucket here, and that'll give that some background, and again, click to deselect, so you can see how it looks. Now that we have that done let's create a style from it. Leave the cell selected, and still on the Home tab let's go back over here to Cell Styles, we were in here a little while ago, and down on the bottom choose New Cell Style. Now it gives you this little dialog box. First thing is it asks you for a name. Let's call this Sheet header. Capitalization doesn't matter, you can put a space in the name if you like, and if you take a look at these checkboxes this is telling you what aspects are going into that basket of formatting, so maybe for some reason you don't want alignment to be part of the style or maybe you don't want number formatting to be part of the style, you can deselect those. Notice, by the way, that column width and row height are never part of a style. Now click OK, and what we've done is we've created a style from the example of formatting that we did, but we haven't yet applied the style to these seven cells. We need to tag them, so once again, let's select those seven cells, back up to Cell Styles, and now let's apply that style. Here's where it gets interesting. Let's go to the second worksheet of this workbook, I'll go over here to applying styles, and you see it's very similar, different product names, different numbers, but the layout is the same. Let's select these same column headers as before, back up to Cell Styles, and we'll choose that Accent2, and let's select these same cells across the top for the sheet header, go back to Cell Styles, select that sheet header, and there we are, so we didn't have to do all that formatting manually. Now that's all great, but as I said at the intro to this lesson, the real power behind styles is that if you change what the style is, if you change the formatting that goes into the style you'll change the look of all of the cells that are formatted with that style, so let's start off with this sheet header. Again, let's go back to A1, and let's go back to Cell Styles, and there's this custom style that we created called sheet header, right-click it, and from the popup menu choose Modify, and we're back in that dialog box we saw before, this time click the format button. Now this brings up the Format Cells dialog box, and we haven't seen it yet in this course, but you can always go into this Format Cells dialog box manually even if you're not using a style by using the little down arrow in the lower right corner of the font alignment or number groups in the Home tab. Don't worry about that right now. Let's change the fill, I'm already on the Fill tab, and I'll go here, and choose maybe a, oh maybe I'll make it yellow, and let's go over here to Font, and I'll choose maybe a blue, and you see we've got a little preview there, and let's change the font itself, we'll just choose Broadway just because it's right there, click OK. We haven't seen the change yet, click OK again. Now let's change the built-in style, so again, click on any of those column headers there across row 4, back to Cell Styles, so you can change the definition of a built-in style, not just your own custom styles, so right-click on that Accent2, choose Modify. You notice in this particular style only font and fill are part of the style, number and alignment and the others are not. Let's go back to Format, and let's change the size, I'll make the size a little bit smaller, but I'll make it bold, and let's go to fill, and maybe we'll make the fill a shade of green, and if you don't like it you can always come back, okay it, okay it again, and it's changed. Now let's go back to the first worksheet that we're on, creating styles, and look at that. The formatting of both those rows changed, and you can see where the power is not just that it's faster, but it's more consistent. If you did this manually it would be very easy to choose maybe a slightly different font size or a slightly different color from one sheet to the next, and then the whole thing can look really bad, so by changing the style definition you know that your consistency is there, but wait, there's more. What if you want to use these styles on a different worksheet? Let's create a brand new worksheet, you could go to your File menu and choose New, I'll just press Ctrl+N or Command+N on the Mac, I'll just drag this into the screen here, and let's type some stuff. I'll call this maybe Expense Report, and let's --- use that autofill. Let's go down over here, and let's make this Quarter 1, I'll Ctrl+Enter or Command+Enter, and let's drag this across, so we have four quarters. You know how to do this already. Let's take those columns, stretch them out, maybe make the rows a little taller, okay. Let's go up here to the sheet header, select those cells. Now when you go up to Cell Styles you might say, hey, where's that custom style I created, why is that Accent2 the way it was originally? That's because these are the default built-in styles. What we need to do is we need to bring in the styles that we created in that other workbook, so what we do here in this dialog box, down on the bottom choose Merge Styles, and Excel says, oh, okay you have another workbook open, so choose that open workbook, you can only merge styles from workbooks that are currently open, click OK. It's going to ask you, do you want to merge styles that have the same names, so you're going to overwrite, yes. Now let's go back, and there they are, and here's what's also really cool, is I did not even save that original workbook, that workbook we were playing around with styles, I haven't saved it yet, Excel still reads those styles, so I'll choose that Sheet1, let's go to these quarters, select them, go back to Cell Styles, there's that Accent2. One more thing. What if you want to remove all the formatting, but leave the text, so it looks just like plain old default formatting? What you do is this. I'll just select maybe all of these cells here. Over here still on the Home tab click Clear, and up here towards the top choose Clear Formats, so that deletes the formats, but it leaves the data. I don't need this workbook anymore, I'm just going to close it, I'm not going to save it. Styles is a great feature, and you'll find it not just in Excel, you'll find it in many programs, many of the Microsoft Office programs like Word, and PowerPoint, and OneNote, and you'll find it in many non-Microsoft programs, all over the Adobe suite for example, so you see, it's a really great concept to know.

  23. Inserting and Modifying Pictures - When you need to insert a picture Excel lets you do more than just resize and move it. You can recolor it several ways, and even apply interesting and artistic effects. You might never guess that you can do all of this with a spreadsheet program, and what's also nice is that these techniques are the same in all the Office 2016 programs, so the features work the same way in Word and PowerPoint, for example. The file I have open is called inserting pictures, and you'll find it in the module 5 folder of your exercise files. You'll notice down at the bottom we have three worksheets, so I'm on the first one called Blueberries, so let's illustrate it by inserting a picture of blueberries. Up here in the ribbon bar let's go to the Insert tab, and over here let's click Pictures, and this will let you insert a picture from your hard drive or whatever source you have. If you're using the Mac version what you want to do is on the ribbon bar click pictures on the ribbon bar, and you'll get a little dropdown, and from the dropdown you want to choose picture from file, and then you'll get basically the same dialog box. There's our blueberries, and you'll notice these pictures are also in the module 5 folder of the exercise files, and I'll double-click blueberries. Now this picture is much bigger than it needs to be, much bigger than the worksheet, so let's zoom out. You can zoom out whichever way you like. I'll use this little mechanism here that we looked at earlier in this course. I need to zoom way out, and I'll put my mouse pointer on one of these corner sizing handles. You notice the mouse pointer becomes a two headed arrow, and I will just drag inwards to shrink. You notice that it shrinks in proportion automatically, so you don't have to hold the Shift key down or anything else. Move it out to the side, now you can zoom in if you want. Okay, and you notice that you can drag this picture around on the worksheet pretty smoothly. I'll just scroll back here to the left side. What if you want to move the picture around and have it snap to the gridlines? If you're in the Windows version hold the Alt key down, and now when you drag it, it snaps to the gridlines. If you're on the Mac hold down the Command key, so let's apply some different effects. Actually, I'll just move this a little closer, so we can see more of it. Now when you have the picture selected you see that on the ribbon bar Excel automatically puts you in the Format tab, on the Mac it's called a Picture Format tab, same thing, although on the Mac you'll probably have to manually click the Picture Format tab, whereas in Windows it goes there automatically, so leave the picture selected, and over here on the left side let's choose color, and we could apply some adjustments. For example, we can desaturate, and so much so that it's black and white or we can put in a lot of saturation. Also, in Windows you notice that as you roll the mouse over these items you get a preview what it'll look like, on the Mac you do not get a preview, so you choose one that you like, maybe I'll saturate it a little bit more, go back to color, and we can do a few other things. You can recolor it, so if you want yellow blueberries, I don't know if that's a contradiction. You can do any of those, and there's some interesting things like turn it into black and white, kind of like a high contrast, and so on. We could also apply artistic effects. By the way, if you deselect the image by accident, I'll just click here on the worksheet, you notice that that tab disappears, so you have to make sure to have that picture selected, and again, you may manually need to click that Format tab. At any rate, let's go here into Artistic Effects, and we have different things like marker or pointillism or various blurs, some cool things. I'll let you poke around here on your own. Glow edges, I think that's really cool. Anyway, I want to show you how to fix a very common problem with pictures, and this is a problem you'll find in many programs, not just Excel. Let's go to the Java tab, and I'll show you what the problem is first before I tell you about fixing it. Since we're on the Java tab let's go and insert some coffee beans, coffee's a fruit after all. Let's go to the Insert tab, go to Pictures, and you see in the same folder here in module 5 of the exercise files we have Java, double-click that, we may need to shrink it, I'll leave that up to you, but notice what's happening. These coffee beans are on a white background. The white background is blocking whatever is behind it, so we can eliminate this background color, leave the image selected, and we're still here on the Format tab. Let's go back to Color, and down here towards the bottom you see where it says, Set Transparent Color, choose that, and you see the mouse pointer becomes kind of like a little pencil, take that, and put the mouse pointer on the white background and click, and now that white background goes away. I'll shrink it here a little bit, so now I can put this anywhere on the sheet, and the gridlines or any text or anything behind it will show through. Earlier in this module I showed you how you could apply styles to cells, and you can also apply picture styles to pictures. Let's go to the third tab, Tomatoes, and let's insert Tomatoes, so go back to the Insert tab, and in the same module 5 folder of the exercise files there's tomatoes, and again, let's shrink it, so let's zoom out, and I'll need to shrink that a lot more, zoom back in. Actually this time I'll use the Ctrl key method. I'll hold the Ctrl key down and roll the mouse wheel away from me, and let's set the transparent color, so let's go in there, Set Transparent Color, click on the white background, so in the Format tab still you see we have picture styles, and you can roll over, in the Windows version anyway, and get some previews. You notice that some of these styles like this one here will put that background right back, and if you click this down arrow you can see all kinds of different styles like drop shadows, and borders, and all kinds of things. I think I'll be simple and I'll just choose the drop shadow, and the last thing I want to show you is that you can rotate these images. See when you have the image selected, at the top there's this little antenna with a little curvy arrow, and if you put your mouse pointer on it the mouse pointer becomes a curved arrow, and you can take that and you can just drag to rotate. I'm going to do that for a second because I want to show you that if you hold the Shift key down and drag that you can rotate in multiples of 15 degrees, so if you want an exact rotation, like 45 degrees or 90 degrees or something you could do that by holding down the Shift key. Without the Shift key you could rotate it freely at any angle that you like. Now you may have noticed when you went to insert pictures, at least if you're in the Windows version, in the insert tab there was an item to insert online pictures. That is inserting pictures directly from an online source. That's a feature that's available only in the Windows version, and I'll show you that in the next lesson.

  24. Inserting Pictures from Online Sources - Here's a cool feature that works with the Windows version of Excel 2016, but not the Macintosh version. Sometimes the picture you want to insert comes from an online source, maybe you already have a picture in a cloud storage space like OneDrive or SharePoint or maybe you need to search for a picture you want. Rather than going through the step of downloading an image, remembering where you downloaded it, and then placing it in Excel, you could do it all in one step. Now if you're familiar with older versions of Office you might remember the Office.com clipart library. That no longer exists on its own. Microsoft has folded that into the Bing search engine. The file I have opened is called online pictures, and you'll find it in the module 5 folder of your exercise files. This worksheet is about Java sales, so I want to insert a cup of coffee, maybe a picture, maybe a drawing, so let's take a look. We'll go over to the Insert tab on the ribbon bar, and you see over here next to pictures we have the icon for Online Pictures, so let's click that, and I have two options here. I have Bing Image Search, and I also have OneDrive. Microsoft OneDrive is a cloud-based file storage service. If you have a subscription to Office 365 OneDrive comes with it, and if not you can sign up for an account, and there are free and paid versions of OneDrive. Now in this dialog box on your computer you might also see options for SharePoint, and maybe other services. I'll start with OneDrive, and I've already put a picture of a coffee cup in there, so you may or may not have something like that in your own OneDrive storage, so I'll click Browse, and this shows me I have a bunch of folders, I have some pictures, oh, there's a picture of a cow, but there's a picture of a coffee cup, so I'll make sure it's selected, I can see down on bottom there are the dimensions, and now I'll click Insert, and there it is. If I need to, which I do need to, I'll resize it, and then I can use all of the corrections, and all of the adjustments that we saw earlier in this module. Right now I'm just going to push this out of the way, push it over here and to the side, and I'll just click somewhere on the blank worksheet, and now let's go and search for a coffee cup, because let's say I don't already have one waiting for me, so let's go back to the Insert tab, and let's go back to Online Pictures, and this time here in the Bing Image Search I'm going to click, make sure my cursor is in there, and I'm going to type cup of coffee, and you could either click the little magnifying glass there or I'll just press the Enter key, and this does a search. Notice on bottom it tells me that the search results are licensed under Creative Commons, so there's a good chance that I do have permission to use these. Now this is a web search, so by the time you watch this video the results you see on your screen might be a little different from what I see on my screen, but I'm going to scroll down a little bit, and here's the one I want to use. It's kind of black and white, wood cup looking thing, and when I select it you can see down in the bottom in the lower left corner it tells me the dimensions, so I know this is going to be quite big, it's going to be a lot bigger than the picture of the coffee cup I inserted, so I know I'll have to resize it, and it tells me the source it's coming from is freestockphotos.biz, so I know there's a pretty good chance that I have the permission to use this. You really want to be careful when you're inserting images from the web. If it's for your own personal use you have a little bit of latitude, but if you're going to be distributing your work, especially making it public, putting your work up on the web somewhere, you really want to make sure that you're not using someone else's artwork without permission because you could get into some trouble for that, and if we want to make absolutely sure what we can do is this, click down here on this link, and you see that brings up the page in my web browser, and I'll scroll down, and I can see, there it is, there's a very large image of it, and we see some information about it, and I can see, yes this is in the public domain, so I can do anything I want with it, and if I want I could manually download a small version or a large version of it. Now you might be used to doing something like right-clicking and saving the image or copying it, and if you want to do that, that's perfectly fine, but it's not necessary at all. Let me just minimize this logo back to Excel, and the reason I say it's not necessary is because once you have this selected all you have to do is click Insert, so this is what I mean by saying you only have to do one step. Now this is huge, so let me resize that. I'm going to zoom out here, because this is so big I really have to zoom out very far, and now I can shrink it a little bit, I can move it around, I'll zoom back in, and there I have this free image of a coffee cup that I just put in from the web 2 seconds ago. This is a very nice feature that you could use. Remember, all you have to do is go to the Insert tab, and then click Online Pictures, and search for, and insert the picture that you want.

  25. Printing Worksheets - Page Layout: How Everything Fits Some people will see your Excel worksheets only as printouts or PDFs, so in this module I'll show you how to prepare a worksheet for printing. This includes adjusting paper size and orientation, inserting headers, footers, and page numbers, and tweaking printer settings. The Page Layout tab on the ribbon bar has most of the tools you need for controlling how a worksheet looks when printed. I say most, but now all because you'll find print preview somewhere else. Let's take a look. The file I have opened is called page layout and you'll find it in the module 6 folder of your exercise files. You see it's kind of a large worksheet, that's why I'm zoomed out, so let's take a look at the Page Layout tab. On the ribbon bar I'll click on Page Layout, and you can see here right underneath where my mouse pointer is are a whole bunch of tools for organizing how the page is laid out. Let's start with the very first one. Here is Margins, when I click that you can see right now by default I have regular normal margins, and you can see what those numbers are. If I decide that I want wider margins or narrower margins maybe I need to fit stuff on the page a certain way, I can do that. If I want custom margins I can go and click here, and in the Page Setup dialog box I can dial in any margins I want, as long as they fit on the page. We'll just cancel out of here for now. This is a wide worksheet, so I know that I want to print this out in landscape orientation or what on the Mac is called Wide orientation rather than portrait or what on the Mac they call tall orientation, so let's go over here to Orientation, I'll click that, and see its portrait by default, I'll just switch to landscape. Something I'll come back to in a little while is when you're looking at all the tools here in the Page Setup group in the Page Layout tab you notice here I'm using Windows, the last tool is Print Titles. If you're running on the Mac there's actually one more button there, and that's called Page Setup, and that will bring up a Page Setup dialog box. In Windows we have that dialog box, which we actually just saw in a moment, and there's different ways of getting in there, so if you're using a Mac and you see one other button don't worry about it, you're seeing exactly what you should and we'll come back to it. Now as far as the size of the page, I'll go over here, and I'll click the down arrow for Size, and again, the default is letter size, and I have all these other sizes to choose from. I'll just leave it where it is, I'll click on the title bar there to get rid of that. Now Print Area is a really great feature, and what that means is let's say I don't want to print out this entire worksheet, maybe I just want to print out a little bit of it. I can control exactly what gets printed out. Now before I do that, take a look at the boundary here between column G and column H. You notice that now there's this dotted line, and that dotted line showed up right after I chose what the orientation is of this sheet. We can control page breaks manually, but that just comes in as soon as you start playing around with orientation and things like that, and you'll see that change in a moment. Let's say I only want a few of these cells to print. I'll just select maybe just a little area like that, and I go over here to Print Area, and I choose to Set Print Area. Those will be the only cells that print out. I'll just deselect, and you notice that there's like a little, thin border around them, and that dotted line between column G and column H is now gone. Now you can have only one print area at a time, so if I select different cells to print out from my Print Area, again, let's select maybe something like this, and I go up to Print Area, and I set Print Area, and again, I'll deselect, you'll notice that thin border has changed. If you decide that, okay, now I want to print out the entire worksheet, I want to get rid of that print area, you simply go back to Print Area, and choose Clear Print Area, and now you'll print out the entire sheet, and now you notice the dotted line between columns G and H is back, so let's talk about page breaks. Maybe I want this page to break at a certain spot, and we'll scroll down here a little bit, let's say maybe after row 20, so I'll click over here on B21 or I could click on the entire row, doesn't really matter, I go up here to Breaks, and I choose Insert Page Break, and now you notice that there's this little thin line underneath row 20, so I know that's where it's going to break. Now there's another place where you can control page breaks interactively. Let's scroll up here to the top. Down here on the bottom I'm going to click this Page Break Preview button, and you see this zooms out even more, and I have some lines here showing me where the page is going to break. You notice that we have that line that we just saw underneath row 20, it's a little thicker, and that's a solid line, that means that's a manual page break, and that dotted line between G and H, that means that it's an automatic page break, so I could do something like this. Maybe I decide that I want the page to be a little taller, I want the break to be somewhere underneath, farther underneath row 20, when I put my mouse there or over there you can see that it's a two headed arrow, and I can drag down, so I can extend it. Of course, this is limited by the size of your page. Maybe I decide that I want the vertical break to be between columns F and G, so I have all the phone numbers on the same page. I can also take this dotted line and drag to the left. Now you'll notice that it's solid because it's a manual page break. I'll go back to Normal View, I'll click on this first icon here, and now you can see, again, we have this thin dotted line between F and G. Now you might wonder, well what if I want this entire worksheet to print out on one sheet of paper, can I do it? The answer is yes, though I can't guarantee you'll be able to read it. You do that in the Page Setup dialog box. Click this little icon, I guess you can call it that, a button to get in, and you see right over here where it says, Adjust to a certain percentage of normal size. If I want this to print out on one page I simply click this option, and I can choose how many pages wide by how many pages tall do I want it printed out at, but I'm going to leave it the way it is, and actually I'll just cancel out. Now maybe you want to have a background for this page. What you could do is go up here and click Background, and kind of like what we saw earlier in the course, this gives us an option of how do we want to insert an image or where to do we want to insert an image from, so I'm going to choose to insert from a file. I'll click Browse. In the module 6 folder of your exercise files you see we have this file called cherries. Double-click that, and we've got a mess, and you cannot read this because the cherries are just way too dark. Now there isn't any way in Excel of editing the background picture the way we edited pictures that we inserted in the previous module, so instead what we have to do is we have to use just a lighter image, so first thing you notice that this insert background tool is now changed to Delete Background, so I'll click that. Now we have the regular background tool again, click it, click the same Browse, and now you may have noticed before we had this file in the same module 6 folder called cherries light, double-click that, and now we can read it, and this is just an image that I edited in Adobe Photoshop. Now there's a couple of issues with using a background image that you should know of. Number one is the image always gets tiled. There isn't any way of having the image appear only once or maybe stretched out. Tiled is just the only option. Also, you might not have a lighter version of the background picture you want, like we just did here. Now you can always use image editing software, like I used Photoshop, you might use something simple like Windows Paint, but maybe you just don't have time to fiddle with the image, so that's just something you should consider. In the intro to this lesson I made a point of saying that Print Preview is not in the Page Setup group in the Page Layout tab, so let's go and find it, and what you do is go to the File menu and Print or press Ctrl+P, and over here on the right you can see a preview of it. Notice that the background is not included in the preview. We also see that the stuff on the page is kind of squished into the upper left corner. Maybe we want to center it horizontally and vertically. You may have notice the option a few moments ago when we were in the Page Setup dialog box. Down here at the bottom of all of this stuff, you see it says Page Setup, if you click that you get into the same dialog box that you did when you click that little arrow or if you're on a Mac it's the same dialog box as clicking the Page Setup button, and I'm going to go over here to Margins, to the Margins tab, and I'm going to choose to center on the page horizontally and vertically, click OK, and now everything is centered. Now that's all great, and I want to give you a run through of all of the print settings that you see here on the left side of the screen, but we won't do that now, we'll do that later on in this module. For now let's take a look at how to do headers and footers, that's coming up in the next lesson.

  26. Inserting Headers and Footers - To insert headers and footers you do it directly on the page where you can see it. You do this in Page Layout view. When you edit the headers and footers Excel will give you a special tab on the ribbon bar that's just for this purpose. If you still have the file open from the previous lesson you can continue with it. If not you could open up the file that I have open, and it's called headers and footers, and you'll find it in the module 6 folder of the exercise files. It's the same file, I just removed the background. Let's go and insert some headers and footers into the headers and footers file. Down here in the lower right corner you might see a little popup, little yellow popup telling you that this middle button is Page Layout. I'll click that, and you can see it shrinks a little bit, and now we see a horizontal and vertical ruler. This is the actual page we're looking at, the actual paper, and up on top there you see it says Add Header. When I move my mouse over it you notice that I get these three sections, it could be anywhere if I move my mouse over here on the left or on the right, and these sections are just three sections of the header, and you'll also have three sections of the footer. Everything in the left section is always left justified, everything in the middle section is always centered, and everything in the right section is always right justified. Let's start off on the left side. I'll click in here, and you see it gives me a little cursor flashing, and you can see a tiny little word there says header, move the mouse out of the way, and I'll start with putting in let's say the date, so up here on the ribbon bar you see there's now a new context sensitive tab called Design, and we have all the header and footer elements group as well, so I'll put in the current date, and I'll click that, and it puts it in, and it's a little small. You can see there's a code, ampersand, and the word date in square brackets, so that's just telling me that it's going to update. Let's say I want actually the time, so I'll just type a space, and I'll put in the current time, and again it gives me a little code, and I want to put in the name of the file in the center section, so I'll click in here, and you see up here I have an option for the file name. Now if I want to put the actual path that is the drive letter, and folders, and subfolders that's on here, what I could do is I'll just click before the file name, and I can click the file path. Notice that gives me the path and the file name, but I'm just going to delete it because that's going to be just a little too confusing. I want to put in the page number, so I'll do this here in the right section, and you see over here we have the page number, and if you want to do something like page 1 of 10, 2 of 10, whatever it is, so and just type the space, literally type the word of, and another space, and then you can click here for the number of pages. I also want to put in what the sheet name is, so this workbook is called headers and footers, the worksheet itself is called names, but we're getting a little too crowded up here in the header, so let's do it in the footer. I'll click this button over here, Go To Footer, and I'll click here in the left side, and I'll put in the sheet name, and I also want to put in a logo, so let's do this here in the lower right corner, and I'm going to click Picture, and when I do that it says, okay, where do you want to find the picture from, so you see this dialog box repeats, and I'm going to insert it from a file because I happen to have one in the same folder where this file is, so I'll click that. You can see we have our cherries that we were using in the previous lesson, but there's this logo, so I'll double-click that. Now you don't see the actual image when you're editing the footer, you just see a code, and let's click out of the footer, I'll click back on the page, and now you can see the logo. I'll scroll up here a little bit. Now this logo, unlike the background image, it appears only once, so that's nice, it's not overwhelming, but it's still too dark. It's blocking some of the data, so unlike a background image, this is something that we can edit, so let's go back here into the footer because I'm in Page Layout view I don't have to go into it again. Let me click over here on the right side, and I want to make sure that this picture is selected. If it's not selected already you can select it, and what you want to do is go back into the Design tab if you need to, and now you see I have this item here called Format Picture. Click that. You see we have two tabs across the top of this dialog box, click Picture, and now we can adjust the brightness and contrast, so I'm going to take this brightness and I'm going to move it to about 80%, and I'll take the contrast, and I'm going to move it down to about 30%, somewhere in that neighborhood, and click OK, and now you see it's kind of washed out, and when I click back on the page out of the footer now we can see that it's kind of washed out, so we can read the data. Let's take a look at Print Preview, go up to the File tab and Print or you could press Ctrl+P, and now you can see this is exactly how it's going to print out, and down over here where this shows me it's page 1 of 12 I can click through it, and see everything, and up here on top you can see the headers, and on the bottom you can see the footer. I'll just escape out of it. Just to review, anytime you want to insert or edit the headers and footers you want to make sure to click this middle button over here to go into Page Layout, and then click the top or bottom of the page where you see those little boxes.

  27. Printing and Print Settings - Printing in Excel isn't much different from printing in other programs. Like all the Office 2016 programs, you can control most print settings from a single pane on the screen, and if you need more control or control for your specific printer there's a dialog box you can use. If you still have a file open from one of the previous lessons you can continue with it. If not, you can open up the file that I'm using, it's called print settings, and you'll find it in the module 6 folder of the exercise files. It's just like all the others, but I don't have any of the images or anything on here, so I want to show you the panel that has all of the print settings, but before we do that I want to make sure that this is in landscape orientation or what on the Mac is called wide orientation, and I can see it's not because I don't see any dotted vertical lines here, so I'm going to go into the Page Layout tab, I'll go over here to orientation, you can see it's portrait, so I'm going to choose landscape, and like we saw earlier in this module there's now a dotted vertical line after column G. The reason I'm doing it here is to show you that whatever orientation you set here in the Page Layout tab will be the same as when you're in the print settings, and that's kind of huge because in older versions, not just of Excel, but of so many other computer programs, you might set orientation one way in the program, and then in the print settings it might be the other way, and you print out, and you get a mess, so this is actually not a new feature in this version. This actually started out in the 2010 version, but I think it's a really nice feature so I'm mentioning it. Let's go over here to the File tab, and I'll print or you could press Ctrl+P, and now we can see all the print settings here. Let's just go from top to bottom. You can see at the top there's this big old Print button, so if you want to print all you've got to do is hit Print, and all of the settings that you see here are going to be used for it, and how many copies you want, you can type a number in here if you want or you can use the little up and down arrows for that. As far as printer, whatever printers you have installed on your computer will be available here, so if you click that you can see all these different printers that are available. Now if you want to make settings that are specific to your printer you could click this Printer Properties link, and that brings up a tab for that particular printer, so this is the tab for this laser jet that I have sitting in my office. I'll cancel that. If I go over here and maybe I'll choose Adobe PDF, and go to Print Properties you notice that I have another dialog box. This is specific to that PDF printer, so let me go back here to my regular laser jet, so we'll continue with what we saw before. Now let's say you have a lot of worksheets in your workbook. By default, Excel will print out only the active sheet, so that you don't print out some giant workbook unexpectedly, so you can click this down arrow here and choose that maybe you do want to print out the entire workbook or maybe you do want to print the current selection. By the way, this option here for Print Selection is sort of a work around for having only one print area. Earlier in this module I said that you could have only one print area at a time, but the work around is you can have one print area, and then print a selection that's possibly different from the print area, so that's a nice little feature in case you ever need it. Now let's say the sheet that you're printing out is many pages, and you can see that this particular sheet here is 30 pages. Maybe you don't want to print out all 30 pages, maybe you just want to print a particular range, so you can say something like, alright, just print out maybe pages 5 to 7 or something like that, you can definitely do that. I'll just remove that here for now. If you're going to print out multiple sets up there in the top where it says how many copies you want, maybe you're going to print out five copies, by default it'll collate them, which is what most people would want, but if you want you could do it uncollated if you really do want five page ones and five page twos following it, and so on. Here's what I was saying before, is that because we set landscape orientation in the main part of the program that it changes here, let's try something here. Let's take this landscape orientation, set it back to portrait. I'll click this back arrow here, and now when I'm on the Page Layout tab, and I go to Orientation look at that, it's back to portrait, and now if I set landscape again, this time I'll just press Ctrl+P, and I go in here, you see it's back to landscape, so that's what I was saying before is that what's in the main part of the program actually talks to what's in the print part of the program. Genius I tell ya. Now over here we have the size of the page, also this talks to what was in that previous section. The margins, also this is right from the Page Layout tab. Some of this is off the screen, you can't see it, but it's exactly what you would see in the margin section of the Page Layout tab, and scaling. Again, right now this is a little bit off the screen, but if you click the page setup link, then here in the page tab of the setup dialog box you have your adjustment or you can fit to a certain number of pages wide or pages tall, we'll just cancel that. That's the same exact thing as what's in that scaling box. Two more things I'll show you with Print Preview. One is you can zoom in, but just a little bit, there's really not much of a way of controlling zooming. Over here in the far lower right corner of the page you have this little zoom box, and if you click that it'll zoom in to roughly 100%, and if you want you can scroll around a little bit. I suppose the reason they don't give you much control over how much or how little you're going to zoom in is because you could do this in the Page Layout view of the program itself. I'll click that, so we can go back to full page, and also, as far as adjusting margins is concerned, this little box over here, the second one from the corner, this'll show you margins, and you can actually interactively move these around if you decide that you want to adjust the margins, so this is a nice visual way of doing it without having to resort to a dialog box, and kind of guessing, so I'll just go back to the main document, and just to review here, any time you need to adjust the print settings you could press Ctrl+P or Command+P on the Mac or go to the File tab, and go to Print, and all of your settings are right there.