cgs-excel-power-users

Excel 2016 for Power Users

Conditional Formatting Using Data Bars Conditional formatting is a really neat way to look at your data without having to read your data. Wait, what? Yes, it's really interesting because instead of having to look at the individual values, 3664, ooh, this one is 3828 is more, 49 more, 46 less, 39 less, 38 whoa less, 31 way less. Right, instead of having to look at the numbers and evaluate them by reading them, if we're in a spot where we need an idea, which ones are large, which ones are small, at a glance, what do we do? Conditional formatting! So we need to select the cells we'd like to affect. I have about 100 rows of data here, so I want to select the sales column pretty quickly without, you know, the old drag method. So what I'll do is select the first cell and I'm going to give it a Ctrl+Shift+End to head all the way down to the bottom, you can see all the way down to the bottom, you can see over here on the left, I'm down at row 103, but I only want the sales column, so I'll Shift+Left Arrow to select just that column and head back up to the top, giving a couple of clicks here and I've got that column pretty quickly selected. So conditional formatting is found on the Home ribbon tab, Styles group, Conditional Formatting. Alright, take a look at that screen tip. Easily spot trends and patterns in your data using bars, colors, and icons to visually highlight important values. Oh, yes. So we're going to go down to Data Bars and take a look at the fly out, there's a gradient section and a solid section. So if we float over these, we get a live preview. There's what it looks like with the blue. And again, notice the higher the value, the longer the bar. The smaller the value, the shorter the bar. So again, at a glance very easy to pick out high values, low values. Oh, I like this. So you've got all sorts of gradients that you can try on, float over them, and then as we move into the second section, let me head down, solid. So yes, even more bold, even more visible. Maybe we'll go yellow. It's not too dark and let me give that one a click. Click away from the data to take a look at it, right. Really easy to see. This one almost fills the entire cell and this one well, a little less than half. And as I scroll down, right, really neat. Now when we need to delete a conditional formatting, two ways to do it. If there is no other conditional formatting on the sheet, you can just clear the sheet of conditional formatting. If there is other conditional formatting and you'd like to keep it, you need to reselect the range. This is the only one we've got here, so back up to Conditional Formatting, down here at the bottom, Clear Rules, and look selected cells, look at where I have selected, yeah, a cell with nothing, I'm going to go Clear Rules from Entire Sheet and it's gone. You have a really cool way to look at your data without actually reading the numbers. When you find one that interests you because it's very long or very short, right, read those numbers, but otherwise, you can glance at the sheet and take a peek. Now there are a bunch of other options, which we'll see in the upcoming lessons. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Configuring Data Bar Rules Now as we use conditional formatting, there are defaults that are set up, so as we apply it, it applies with these default settings and the default settings are good, but sometimes they're not exactly what we want. So let me select my data once again, so I'm click into cell E4, and giving it a Ctrl+Shift+End, Shift+Left Arrow, and a tab to head back up to the top, and a little scroll up. So this is the data I'd like to affect and we did see a data bar on it, and what I want to do now is manage the rule. Maybe I'm not quite crazy about how it appears, what can I do? So Manage Rules shows us the rule that's currently applied, you can add more than one rule to the same set of data, edit your rule, delete your rule, so we will edit the selected rule and look at my different types of rules. I can build rules that format all cells based on their values, only cells that contain, only top or bottom ranked, only values that are above or below average, only unique or duplicates, how handy would that be, or use a formula to determine which cells to format. We'll stick with the first one for now based on their values. So you see here it shows us our current style is the data bar. There are other choices in here. Our minimum and maximum values are set to automatic meaning it automatically looks for the lowest value as a start, highest value for the stop. Let's change. Alright, for the minimum value, look at your choices. I'm going to give it a specific number because as I glance down, let's say it 2000, you know around 2000 seems to be the lowest that we see here. I don't know what's below, but let's play with 2000 just to see if we can see the adjustment. I'll leave the maximum at automatic or the max value. Look in here as well. If I decide, you know what, let me change my color, maybe add a border? Nah. And down here you can see the preview. So when I click OK now, it shows me my updated rule, and I can definitely see the color, and I click OK again, ooh let's click away, notice the 2000 all the way back to the beginning. So when you do apply conditional formatting rules, you're not stuck. You don't have to stick with what they've given you. There are adjustments that can be made. Some of the rules you'll love, some of them will come in and you're like whoa, that's not quite what I had in mind, and if there's nothing is what you had in mind, see if you can adjust, manage those rules. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Using Color Scales and Icon Sets Color scales give us a way that we can view our data by color and it helps us so that we don't have to read every value in every cell. Alright, we've got a lot of sales figures in column E and what if the highest values we needed to send some type of congratulatory note to those cells reps and the lowest sales values, maybe we needed to also send them a note just to let them know they need to step it up. So without going through and reading every single one of them, 3664, 3828, 4921, 46 ooh, send them a good note, right, high number. What we can do instead is use conditional formatting's color scales to help us out. The first step in conditional formatting is always to select the range that you'd like to affect, so I'm clicking into cell E4, I'm going to give it a Ctrl+Shift+End and a Shift+Left Arrow to get back over, and then I'll press Tab to head back up that way I don't have to drag and I don't have to scroll a lot. If I scroll back up, I'm back to the top of the data, so some quick keyboard shortcuts to get me there. Once the data is selected, we head back up to the Home ribbon tab, back out to the Styles group, and up for Conditional Formatting. I'll use the drop-down there and we are headed for Color Scales. There are a lot of choices in here and the neat thing is the pictures help, but if you float over them, two other things help. We get a screen tip, so check out the screen tip all the way over here and that screen tip will help you decide, do I want to use that one, green, yellow, red. Apply a color gradient to a range of cells. The color indicates where each cell value falls within that range and if I glance over to the left, I can't point, but look at column E, yeah, we get a live preview. So if I float back, it disappears, float back to the right, it comes back, and this one's kind of nice because look at that, that 4921, the 4681, these are high and they're green. Look down to row 15, the 2149 is red. Boy, this really makes it easy to determine which are the highest, which are the lowest. Notice the different shades in between. Oh, I kind of like that. Now sometimes we do want the highest in green and the lowest in red. When that's what we want, we pick that first box here. Let me head over to the next one. This one reverses it. It's red at the top, green at the bottom and you'll notice as I'm floating over this one, the 4921 and the 4681, they're red. The 2149 is green. So again, if lower numbers are better, you could use this one, in our case, that's not the case. And then, if we didn't like the first one because the yellow was in there, it was too much color, look at the third one. This one's just green with some white and red, so it really stands out again. And to the next one reversed. So you see all the choices that are in here, and of course, as you float over them, we do get the live preview. So you can decide which way you'd like to go and then once you've found one that you really like, I think I'm going to go this one, I click it to select it, let me click away from it, and again, just look at how easy it is. I can easily see the values, and with the shades of green, I can tell which ones are the highest, and which ones are the middle, and which ones are the lowest. So much better than having to read every value, and of course, as I scroll down the page, right, ooh, that one really jumps out, right? Ooh, we've got to send them the note. Man, look at that one. Alright, back up to the top. So that's one way to go. We also have icon sets and these are fun. So same concept here, we need to select the data we'd like to affect, so I'll give it a Ctrl+Shift+End and press Tab to jump back up to the top and that way I'll only have to scroll a couple of rows, select all my 100 rows of data, and now heading back up to the Styles group, Conditional Formatting, and Icon Sets. Wow, look at these. These are fun. The problem with these right now as I'm looking at my screen is this gallery is on top of my data, so my live preview is limited to down here. That's a bummer when that happens, so what can we do to fix this up? As I look around the page, I can't scroll out. Well, let's try a scroll. Alright, let me go back up and let's scroll over. Duh, duh, duh, a couple of clicks, and try one more time. That was a little unexpected. There we go! So now that we've scrolled off to the right, as I float over these, again, live preview, and you see the icons and here you see that the icons go green for good, highest values get green, lowest values get red, also pretty helpful in finding those values without having to read. So as you float over them, try them all on, make a decision. I know it's a tough call, they're all so good. What do you like best? And many times, you get in here, you're playing with the icon sets, ooh, I like the icon sets and the question comes up, can I do icon sets and color? The answer is yes. You do have the ability to put more than one conditional formatting style on the same cells. It can be busy, so you might not like it, but in some situations, it could be nice. So I'll choose this one just for fun. Right, it looks nice. Alright, and then go back in to Conditional Formatting, Color Scales, and look. So yes, and if I want to continue the style that's in the column next to it, I can pick this one, let me click away, and we can see. Let me scroll back to the left. So you can do both. This one's not too bad. I might even choose to make the column a little bit wider. Alright, see how it looks. Give them a little bit more space. Hmm, maybe. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Configuring Highlight Cells Rules Another option we have when trying to make information jump out at us is Highlight Cells rules. It's a big help. So I'm looking at my commission column once again and wanting certain values to jump out above others. So I'm going to select my column. I've got over well about a 100 rows of data here and I'm using my Ctrl+Shift+End to select the data, pressing Tab to jump back up to the top, and scrolling just a little bit. Heading up to the Home Ribbon tab, Styles group, Conditional Formatting, first choice there, Highlight Cells Rules. Look at all the choices out to the right. What do we need to see? Is it greater than something, less than something, between, equal to? Text that Contains, look at that, even works on text, a Date Occurring, Duplicate Values, ooh, that's probably a good one to know about as well, right, pick those duplicates out for me, not so much in this case, but boy we could use that in certain situations. In this case, I'm going up for Greater Than. It instantly puts some conditional formatting into live preview to show me what I'll be seeing if I choose this. It has looked at my data high to low and picked a value out somewhere in the middle and said how about this number? That's not really the number that I want. I'm thinking the number that I'm looking for is more like 475, so I change the value and as I'm making the change you see the cells adjust. As far as my color scheme goes, yeah, we could go with that, but hmm, high commission seems like it would be more green than red, right? Well yeah, we've got yellow fill with dark yellow text, now green fill with green text, better. Alright, light red fill, red text, red border, or custom. What does custom let us do? Let's check it out! So the custom actually takes us in where we can pick a fill color for the cells. So maybe I'd like to go maybe a lighter shade of green. I can do that. I can pick a pattern, a little pattern in the background. Oh, maybe not that color black, maybe a green. Ooh, that's fun. Alright and how about border? You can put a border on those cells if you'd like. So pick your border style, pick your color, and then place it on the sides you'd like it. So I'm just clicking on all four sides, so I get a border on all four sides. Oh, this is getting fun. Up to the Font ribbon tab and here I can change the font to something like Bold Italic. Yeah. How about color? I kind of like the dark green, so maybe I'll kind of stick with that. It shows me the preview of it here. Alright, and then even number. You could change the number, but our numbers look good. I don't want to mess with the numbers, so I've made changes on the Font tab, Border tab, Fill tab. Let's click OK. It's showing me live preview. This is what it will look like on the cells. Ooh, that's fun! I like that. So let me click OK. Click away from the data to take a peek. Scroll down a little bit. Yes, all of the cells that are over, greater than 475 are highlighted, so they jump out and I can find them quickly and easily without having to read every individual number. With the reading the individual numbers it's so easy to miss one or two. Yeah, trust me, been there, done that. This, Excel's doing it for you, Excel's showing it to you, tougher to miss them. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Working with Top/Bottom Rules Imagine looking at this spreadsheet and being asked for the top three categories. Well, to do that, top three total for the quarter? Yeah, okay. My move would be to look down the list. Oh, I've got 12, I've got 10, I've got 30, I've got 22. So top one, top two, top three, right. It takes me a minute because I have to read the values, I have to think about the values, and I have to pick them out. Conditional formatting can do this for us. So let me select my cells that I'd like to affect and head up to my Home ribbon tab, Styles group, Conditional Formatting, Top and Bottom Rules. Oh, do you need the top 10 items? Or the top 10%? Well we only need the top three. It's okay, these numbers are configurable, so it doesn't have to be the top 10 or the top 10%. You can put in any number that you'd like and look down the rest of the list, Bottom 10, Bottom 10%, Above Average, Below Average. It's all here for the picking. So I want the top three, I'm going to go with top 10, the dialog opens, and you see it is top 10, and it's pretty much my entire list, so let's knock this number down. I can use the arrows and click, click, click, of course I could type in a number as well, but notice as I'm clicking, it's changing. So I want to take it down to three and it's showing me red. Well the top three should be a happy thing, right, associate that with green would be better and look here, ta da! Yeah, let me click OK, click away, and I see it immediately. Really easy. Now we also have the ability to set this up, so as the October, November, December numbers are coming in, this is live. So if the values change, I've got this one here, the 10,000, let's say that the December number comes in and instead of it being 3000, it's like 6000. When I press Enter, look what just happened. Yeah, we just bumped that 12,000. Yeah! So you can place it on your spreadsheets and as the values change, the conditional formatting updates itself. You don't have to look. Let Excel show you. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Manage Multiple Rules So when we find ourself in this spot, we've got some conditional formatting on the cells and I'm thinking you know we should do the total too. I'll select the total column, head up to my Home ribbon tab, Styles group, Conditional Formatting. I'm thinking about putting in a top 10, but really a top 3 just like we see on the others, so I grab the Top 10, change it to a 3, and make it green to match everybody else. Highlighting my top three values, we've got it going in the first quarter, the second quarter, third quarter, fourth quarter, and total. You do have the option to put multiple conditional formatting rules on different areas of your spreadsheet, so I've got a rule here on the total column, and another rule here on the fourth quarter column, and another rule. Right now the rules are the same, but I still have had to select the data individually, so that the rule applies to the selected data. If I select the whole thing at once, it'll look at all the numbers at once and pick out my top three out of that whole thing. Now I want the top three for first quarter, also for second, also for third, also for fourth, also for total. Let me click OK, and I'm thinking you know it might also be nice to pick out which are the bottom three on this same range. So with it selected, heading back up to conditional formatting, back into my top/bottom, and I'll go for the bottom 10, also changing it to a 3, I'll leave with the red, click OK, and let's click away. So if I've put that in and I think that's good, I can be done. But if I'd like to try something else, select it, can I apply more? You absolutely can keep going. Now the more that you apply, the messier it'll get and sometimes, it'll make it tougher to actually read the values and see some of the other conditional formatting as you see here. Right, especially my 114, it's entirely filled in with the data bar. I can't see any of the green. If I go with the gradient, I can see a little green. Hmm. Maybe the yellow. Oh, I don't know, right. But if I pick it, and I apply it, and I'm looking at it and thinking this is too much, what are my options? If I select the range, I have options in conditional formatting to clear the rules and my options are to clear the rules from the selection, but that would remove all three rules, the top three, the bottom three, and the data bars. Where clearing the rules for the entire sheet would wipe it all out. I definitely don't want that. So what do I do? How about Manage Rules? If I go in here, it shows me all the rules that are on the selected range, right, Current Selection. I can select any one of them, edit them, delete them. So if I'd like to delete this one because it's just a little bit too much, sure enough, I can pick delete, it's gone. It's not displaying yet. I can try and apply. It disappears and now I can see what it would look like with that one gone. If I'd like to make an adjustment, maybe this is just too red, I can select it, edit it, and I can change this. Alright, there's my bottom three. A huh, grab the format button, I can come in and adjust the fill color. So if this is too much, maybe I'd like to take it and put it to no color in the background and just leave the red font. Maybe that's enough, but you've got border options and font options. You can keep going. I think that's enough. Take out the red background, leave the red numbers. Let me click OK, and OK again, and apply. Try it on before I leave this window entirely. I like it. So by using the Apply button instead of OK, the dialog box stays open, allows me to continue adjusting and formatting, adjusting and formatting until I do like it. And then once I'm agreeable to what I see on my screen, I can click OK, click away, it's just enough. Perfect. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Using Cross Sheet So here's what I found myself doing. I'm here on the 2nd Qtr sheet tab looking at my second quarter totals and I'm wondering if my second quarter totals are exceeding the average per quarter total. I do have a Summary sheet tab down here at the bottom, and on the Summary sheet tab, I do have average sales per quarter. So I'm wondering, the average sales per quarter for baseball 9148.89. I flip back to second quarter 9148.89. Oh, this one's not greater than the average. Right and then down to the next one, 6158, back over to summary. Nope, that one didn't do it either. Instead of flipping back and forth, back and forth, how about if we use conditional formatting. Right, how would we use conditional formatting across sheets? Here's what I'm going to do. Back to the 2nd Qtr sheet tab one more time and I'm selecting the cells I'd like to affect. Heading up to the Home tab, Styles group, Conditional Formatting. This time it's Highlight Cells Rules, Greater than, in this case, right, I want to see if I'm greater than the quarterly average, you see there is plenty of other choices in there. I'll take greater than. And it's giving me a value. Well I don't want a particular value, so this needs to be deleted. Bye bye. And then how about my color? I don't want to go red. I'm thinking if it's average or better, greater than, how about green? Green for good. So how do I get this worked out? With my cursor blinking in that field, I'm heading to the Summary sheet tab and clicking on the first cell that I'd like to compare. When I do that, it puts in a sheet tab name, Summary C5. Notice the C5 is absolute referenced and while I am working in column C, that's okay to absolute reference column C, I don't want all of them compared against C5. So I'd like to take the absolute reference out. I'll backspace it out, so now it's absolutely always referencing column C, but not absolutely always referencing 5 because I want the 5 to become a 6 to become a 7, and so on. So let's see what happens when I click OK, fingers crossed, and wow, look at this. Let me click away so we can see the green. How about that? So now these values are being compared to the Summary sheet tab, and when they are greater than the value on the Summary sheet tab, they're turning green. Very easy for me to pick out, very easy to see at a glance. No more flipping back and forth, back and forth. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Working in Ranges with Errors So in this spreadsheet, I'd like to take a look at the percent of total column and use conditional formatting to highlight the cells that are less than 6%. That's what I want to see, that's what I want to jump out at me. We can do that. So we know the first rule of conditional formatting is to select the cells you'd like to affect and uh-oh, we have an error. Oh no, but these are the cells that I'd like to apply conditional formatting to, so what do I do? Pretend you don't see it. Yeah, did I just say that right? Pretend you don't see it for now. As far as conditional formatting goes, we are still going to select the range, we're going to create the condition, and it's going to affect that cell. We will eventually have to fix the error, but as far as conditional formatting goes, keep moving forward, pretend you don't see it. Select your range, head up to the Home ribbon tab, Styles group, Conditional Formatting, let's go Highlight Cells Rules, Less Than, and that was 6%, less than 6%. I'll stick with the dark red, and click OK. Click away from it and I see that I've got a number of them selected that are less than 6%. Very nicely done. Now what about this one? Yes, eventually of course, we have to come in here and fix this error. So if I click into the formula bar, I get a quick peek at the cells that are involved and it seems like A16 is the blue one and we are dividing that with C19, which is the total. Hmm, pretty tough to divide text by numbers. So let me just drag that one over, press Enter, and watch the cell. It's red. It knows that it's less than 6%. Let me select it, I did lose my formatting, but I can fix that pretty quickly. So as far as conditional formatting goes, the errors don't matter. Build your rules, build your conditions, and when the formulas are fixed, the conditional formatting is there waiting for you. Really nice. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Functions Creating Formulas So as far as creating formulas goes, there are a few things that we need to know. How does Excel understand that what we're typing in to Excel is to be calculated on and not just displayed. Oh, that's the trick right there. Well, when we're going to build a formula, we start all of our formulas with the equal sign. That's how Excel knows what follows is to be calculated and then a formula result is to be displayed, not the formula. So let's take a peek here. I've got an empty cell here just waiting for a total and I'd like to add up the 5770, the 6994, the 7023, and put the answer here. Now there's a couple of different ways to do it and there are some shortcuts to do it, but when we're focusing on basic formula construction, we need to know this. So we start all formulas with the equal sign. To tell it which cells we'd like to add up, we actually go get them. So I'll click on the 5770 and it puts in the =B18. Well I want to add that to what's here, so I'll type in the plus sign and click on the 6994. It pops in the reference to the cell. I, again, will type on the keyboard the plus sign because I want to add and click on the 7023. Now look at my formula =B18+C18+D18. Notice the color coding to help me see where it's referring to and the big benefit here is that it refers to the cell. Because it refers to cell B18, it doesn't care if the number in there is 5770 today and 5870 tomorrow. This formula will look at cell B18 and say whatever you have, that's what I'm using. So the formulas update as the values change. So that's exactly what we want. So how does the formula display? When we press Enter. So I press Enter and look at that. My 19,787 displays in a cell, which is exactly what I wanted, and when I head up to the formula bar at the top, you can see there's the formula. Oh, that's why it's called the formula bar. Formula displays here, formula result displays here. So this syntax that we're talking about all formulas starting with the equal sign and then it's cell reference to cell reference, right? Here I'd like to find the difference between the sales February to March. So I start with the equal sign. To find out the difference between them, I can click on the total, it pops in the D19, type in the minus sign and then click on the value I'd like to subtract from it. Right, equal sign, cell reference, minus for subtraction, cell reference. When I press Enter, there's my 580. If I click back on the cell, again, formula bar displays it. So as we work in here, we have the standard operators, + for addition, - for subtraction, / for divide, and the * for multiply. So how about this one, percent of total? What percent is this 41,000 of the entire total? That's a divide. So my first move will again be to click on the cell where I want the formula result to appear, I will type in the equal sign and I'm going to get the cells. So I click on the 41,000, it pops in the C19, / for divide, and I click on the 121, pops it in, I press Enter, 34%. Once you get a handle on basic formula construction, everything starts with the equal sign, we use our standard operators, everything gets easier. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Understanding Functions As we work in Excel, we hear the term formula, we hear the term function. What's the difference? There is actually a difference. We saw formulas in the previous clip, so here let's talk about functions and see how they differ. One thing that we know that still remains the same, every formula, every function, they all start with the good, old equal sign, so let's head down here into cell E18, that's where I'd like the formula result to appear, and I'd like to see the total of this + this + this total here. I'd like to sum it up. So if I start with the equal sign and I happen to know that there is a sum function, I can start typing the letter S for Sum, the letter U to get closer to sum, you see this handy dandy list popping up. It's a big help. These are functions. So when we work with functions, it's equal sign, function name. This list that pops up displays all the functions, in this case, that start with the letters SU. Now there's even more help available. As I use the down arrow to down and down the list, I get a screen tip that tells me what the function is all about. So if I'm unsure what to use, I can read. This one adds all the numbers in a range of cells. That's exactly what I want. So to pick it, I'll press Tab, it pops in, pops in the open paren for me as well, and functions also will show you the syntax. We have a function name and then in the parens are the arguments. The arguments are what the function needs to perform its job. So you need to give it values. Really, cell references may be actual numbers depending on the function, in this case, it's telling us we need some numbers. In some upcoming lessons, we'll dive even deeper into those arguments, but here, we just select the cells we'd like to use as part of this function. As I drag across the three cells, you see it pops in the references B18:D18. Now a little common sense might tell us that we might need a closed paren at the end of that statement. It's true, but we don't have to type that. No, we press Enter and Excel pops it in for us. Let's click back and take a look at the formula bar, close the paren. So come here with me for average. Now I'm thinking I'd like to get the average of these categories up here, average sale. Is there an average function? Well, is it =avg? Is it equal sign and the whole word average? I don't know, but you know what, if I type in the equal sign and I start typing in a V, look at this. I get the list of all the functions that start with A-V and the first one is A-V-E-D-E-V. Well I'm looking at the second one and when I press the down arrow key on the keyboard and highlight it, I can read all about it. Ooh, this is the one that I want. Returns the average of its arguments. Perfect. Press Tab. Again, take a look at the syntax, it needs the numbers, select the numbers, press Enter, and I'm on my way. Let me click back on it, so you can see the formula bar. How about that? So the big difference between the formulas and the functions, the functions have the function name right in the syntax. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Using AutoSum There are certain functions that are commonly used like sum. The sum function to add up columns and rows of numbers very commonly used. So because of that, we do have a shortcut to it. If you know the syntax, you can type the syntax, but we also have a quick click button that we can just use. So check this out. I would like to put a total here that adds up the sum of these three values. Put the answer here. So I click where I'd like the formula result to appear and I'll head up to my Home ribbon tab into my editing group and here this button is the AutoSum button. And look at that screen tip. Automatically added up. I like that word, right. Auto show me. So if I give the AutoSum button a click, it shows me right off the bat, pops in the syntax, =SUM open paren, shows me the range of cells, close paren, and it's highlighting them, putting the marque around them to show me these are the cells I think you'd like to add. If that's the range you'd like, it is, I just press Enter. Done. Ooh, that was super, right? So it was just click in the cell that I like, click the AutoSum button, press Enter. Almost too easy, right? Let me click back on it and we see that in the cell the 5865 appears, but in the formula bar we see =SUM(B6:D6. Of course, the big benefit here with using cell ranges is that if the values in that range update, this automatically updates, right. So if I do click on one of the three cells in that range and I change the number, let's go big, press Enter, you'll see that it automatically updates here, it also automatically updated here. That's why we like that. A reference to the cells involved, not the actual values. So we're telling it whatever you find in that cell, pick it up, and use it. Let me undo because I don't need that. Back up. Alright, so I'm back to my 1822, I'm back to my 5865. Now AutoSum is terrific, but occasionally, it does weird things. Oh no, right? Well check this out. Again, in this row, I'd like to add up the three values to the left. I click here and I head for my AutoSum button, give it a click, and this time, it's looking up. It says hey, I see numbers up above, I'm going to sum those, and I think no, that's not what I want. When AutoSum selects the wrong values, you select the right values. Click, hold, drag over the three cells I want involved, be sure to stop on the third one, don't go all the way into the last cell. We're not adding that one to it as well. So now it updates the reference to be B7 through D7. That looks great. I press Enter. Beautiful. So again, if we check it out here down here at the bottom, I'd like to add up the numbers to the left, put the answer here, AutoSum, beautiful, press Enter. Many times, it's just that easy. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Using Average and Count And when we find ourself in the spot of needing to perform an average, we have a function for that and we have a quick click for it as well. If you take a look down at the bottom of my spreadsheet, row 21, down here I'm looking for the average. So across all of my different categories, what's the average? Now to build an average, we want to use the function to make it happen. Average happens to be one of the five most commonly-used functions, and therefore, we have a quick click button for it. If I head up to my Home ribbon tab, out to my editing group, the AutoSum button is here, but next to the SUM button is a drop-down arrow. If I click on the drop-down arrow, I see my five most commonly-used functions. So while clicking on the button gives me SUM, I can click on the drop-down arrow and get average. Choose average, and look at what it does, pops in the syntax =AVERAGE open paren, the range, close paren. Now if that was my exact range that I wanted, I would press Enter and be done, but just like we saw with AutoSum, occasionally, these functions don't pick exactly what we need. When they do, we press Enter, when they don't, we select the correct range. So here I go. I'm clicking on the first cell that I want included in the range, dragging down to the last cell I want included in the range, the marque updates, and the range here updates to match. That's what I'm looking for and since that's perfect, I press Enter. The average displays, and if I click back on that average, I can look up to the formula bar and see my formula in the formula bar =AVERAGE. So now that I know it, the next time I want to use is, I could just type it in. Click here, equal sign, start typing, and I get a pop-up list of all the functions that start with the letter A. Oh, that's nice. Well, I could scroll that list, but if I type in the letter V, I get closer, list gets much shorter, and as I take a look, right, I want the second one. I'll use the down arrow on the keyboard to highlight average and look at the screen tip. It shows me the description of average, so I can be certain this is what I want. It is, so I press Tab to pick it. Now I've got the open paren. Notice this method doesn't automatically pick any range. Oh, I have to select, but that's okay. I had to select in the last one too. And I get my range selected. It updates the range C5 through C18. I'm not putting the closing paren in. Excel will do that for me when I press Enter. So my average displays, and if I click back on it, it did put in the closed paren, so either way works. Now while we're here, let's also take a look at another function that's in that list of five. I need number of categories. I need a total count of how many items are here in the list. Alright, count happens to be one of those top five most commonly used. So if I click where I'd like the formula result to go, head up to the Home tab, out to the Editing group, use the drop-down arrow next to SUM, count is in here. I pick it. Oh, it's really picking the wrong range. That's okay. No big deal. When it picks the wrong range, you pick the right range and press Enter, $14. Yeah, we've got a formatting issue there. So let me go onto that cell and let's set that to be maybe just a number. Maybe we should remove some decimals. Yeah, that's a little better. So, there we go, for count 14 items. So now we know the average and the count of items. If we click back on it, head up, we can see the syntax. It is =COUNT. So the next time we need it, we have that decision to make. Should we use the drop-down arrow and pick or should we type =C-O, I'm going to keep going, U to get closer, there's Count, it's highlighted, I see the screen tip, counts the number of cells in a range that contains numbers, I press Tab to pick it, shows me the syntax, gives me that open paren, I select, we kind of know the answer here on this one, and press Enter. We've got formatting issues, but nonetheless, 14 items in the list. Here's the syntax in the formula bar. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Using Min and Max So to finish up this spreadsheet, we are looking down into row 23 and 24, lowest sales, highest sales. So what I need here is a peek at my 14 categories for the lowest value. I want that lowest value to be placed here and then highest value to be placed here. We're using the functions rather than doing it by I because if these figures are updated, we want the lowest and highest figures to also update to match, so we want to use a function. And it just so happens that to get lowest and highest, we have two functions min and max, they happen to be two of the five most commonly-used functions; therefore, we can find them up on the Home ribbon tab, Editing group, and using the drop-down arrow next to the SUM button. So I've clicked where I'd like the formula result to appear before I head up here and we're doing min first. Lowest, right, min. I give it a click, it selects a range, sometimes it selects the range you want, in this case, it's not selecting the range that I want, so what I do is I head over and select the range I would like it to look at. The function updates to read =MIN open paren and now it's B5 through B18, close paren. That's what I'm looking for. So I'll press Enter to accept that and it shows me 798 is the lowest value and if I give it the eyeball, 798 would be right. If I click back on it and take a look at the formula bar, we can see that syntax =MIN(B5:B18. If I wanted to use it another time, now I have options. I could type in the =MIN or I could head up to the Home ribbon tab, Editing group, drop-down arrow, and pick from here. This time, why don't I type it in? Let's see how that looks. So I've clicked where I'd like the formula result to appear, typing in =M-I, hey look at that, let me move my mouse out of the way, we get a short list of different functions that start with the letter M-I and MIN is the second one. To choose MIN, I'll down arrow, notice as I down arrow, I get a screen tip that tells me the MIN returns the smallest number in a set of values. That's exactly what I want to do. So since it's highlighted, I can press Tab to pick it, it pops in and even adds the open paren for me. Now all I need to do is select the range I'd like to look at, range pops in, and I'm pressing Enter. No, I'm not going to close the paren, Excel does that for me. So press Enter, I get 815, click back on it, and they add that close parens in there for me. It's beautiful. Alright. What about highest sales? Well, you've seen the process, so now you know we're clicking where we'd like the formula result to appear. Heading up to the Home ribbon tab, Editing group, drop-down arrow next to SUM, we're going for max. Pick Max, yeah max isn't selecting exactly what I'd like. If that was the range I wanted, I would press Enter, but since it's not, I select the range I want, I take a peek down there, it looks good, and I press Enter. Highest sale 8367, and again, I can take a quick glance at all the numbers and confirm it, it's working. Alright, the other method, well let me click back on it. We saw it was =MAX open paren, the range, close paren. So the next time I go to use it, I might be thinking I'm good, I can just type it. I type in =M, look at it, it's right there top of the list. Down arrow once, read the screen tip. Returns the largest value in a set of values. That's what I want. Press Tab to pick it. Pops it in, =MAX open paren that's great, I select and don't type the closed paren, you don't have to, it's extra work, just press Enter. Yeah. Click back on it just to take a peek. It's good. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Exploring the Function Library One of the big reasons to use Excel is its power of calculation. Yeah, it's a spreadsheet program. We put numbers in, we can perform calculations on the values. So what if we're in that spot of yes, I need to perform some calculations, some functions, what do we do? Where do we go? How do we build? Well if we take a look up at the tabs at the top, the Formulas tab might be a great place to start. So we head to the Formulas tab and check out the Function Library. Excel has a ton, no kidding, a ton of built-in functions that are there. We can just use them. All we have to do is show Excel where the values fall in our spreadsheet and it can do the calculations. It's amazing. So if you take a look at some of the categories that are up here. Financial, oh yeah, we can go into the financial category, there are so many financial functions. What do you need? Sometimes, yeah, I don't know what I need, but if you float over them, you'll get a screen tip that tells you about the function, the function name, the syntax, a description, and even the tell me more option. So there is more help and assistance along the way as you start building them. So the first move is to find the category that sounds like it holds what you need. Float around, do some reading. Yeah, there are just a few categories to go through and then more functions. Oh yeah, just a few. And once you find the function that you're interested in, you're just going to pick it right off the list. Excel will step you through step-by-step in getting the function built, and you'll see once you do it a time or two, you can build all sorts of functions. Get the theory down behind it, get the process down behind it, you can build all of these functions. The upcoming lessons will show you what to do. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Using Insert Function And what do we do if we find ourself in that spot of I know I need a function, but I don't know, I'm looking up at the Formulas tab, I don't know what category it falls into. I know what I need to do, I don't know where to go. Well, let me click where I'd like the formula result to go and I'm looking to do an average, maybe I'm not sure where I should go to find average. Well, in the function library, Insert Function can help us. Give it a click and we get this amazing dialog box that lets us search for a function. Look at, type a brief description of what you want to do, then click Go. Love this. So I'm going to do just that. I want to create an average, so I'm just going to type in average. I click Go and watch in here. That list is going to change and only show functions that have something to do with average. Now not only does it show us the functions, but if you select a function, down here it shows you the syntax, so I can see exactly what it's all about, I can read its description to see if this is the one I need. Returns the average of its arguments. It's pretty good, huh? So if I'm looking at it and I go okay, now I see that there's an average function and I need to give it number 1, number 2, but I don't know what that means. It's okay. There is more help on the way. I click OK, and I get the function arguments dialog box that breaks out to the arguments, allows me to see them, and down here is a description of exactly what it's looking for 1 to 255 numeric arguments for which you want the average. Oh, I need to show it, the cells that have the numeric arguments that I'd like to average. Are these them? Mm, kind of, little too much. So since that's not the right range, I will go and select and that's the range I'm looking for. Now number2 is not bold. When it's bold, it's required. If I had another range, I'd also like to include in this average. I could click in here and select the next range and you see a number3 is added as well. What's nice about this is yes, it grows, so as you go to work with it, it will expand if necessary. As I select my ranges and my cells that have values, they are shown to me out on the right, so if there's anything showing weird here, error messages, unexpected values, you see it before you click OK. You can fix it before you click OK. Also, down here, it's already showing me the answer. So again, if there's an error, I know before I click OK. Now with this dialog box, we can build crazy functions. Yeah, pretty much anything that's built into Excel, by following the function arguments dialog box, you can do this. Let Excel step you through the process. Now in the upcoming lessons, we will be doing some more functions, some more advanced functions, this one you may have known, you may have been okay with it. I wanted to use a simple one to get you started here and see the dialog box and let us focus on that, but in the upcoming lessons, we'll do some more advanced ones and we'll use this dialog to help us. Let Excel help you. I'm going to click OK here and my formula result shows in the cell, formula bar shows me the function. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Logical So in this spreadsheet, we're looking to create a commission calculation, but not just a straight commission they do get 12% commission, but only if it meets the sales quota. The sales quota is 4000. So if sales are greater than 4000, then they get the 12% commission, otherwise, 0. How do we make that happen? Oh, boy. So I'm going to click onto cell that I'd like the formula result to appear. I'm checking out the Formulas ribbon tab and I'm heading into Insert Function. Remember, I can type in a brief description of what I want to do. I want if sales meet the quota, then a value to appear. So I'm looking at IF, I don't know, is that going to help me? Let me type in Go, can you help me out? And look at this, the first one that appears IF. Checks whether a condition is met and returns one value of true and another if false. That's exactly what I want to do and it shows me the syntax. If logical_test, value_if_true, value_if_false. Uh, oh. I need more help. It's okay. When I click OK, I do get more help. I get the function arguments dialog box to step me through. So logical test is first and if I read down here, it's any value or expression that can be evaluated to true or false. I need to create a test. I have a test. Sales has to be greater than 4000. How do I put that it? Well, my first move will be to click on the sales figure, and then I'll type. I need the greater than sign and 4000. Now notice as I start typing, I get to 400, it says true. That is greater than 400, but when I hit that last 0, oh, 4000, no that's false. So I can see the result of this test as I begin, which is also nice to help me confirm that what I'm seeing and what I'm thinking match. I click into the Value_if_true field and the value_if_true is described down here, is the value that's returned if the logical test is true. If omitted, true is returned. So if I say nothing, it'll just say the word true. I don't want the word true, I actually want it to calculate the commission if it's true and that was 12%. So if it's true, I want to take sales, I'll click on it, and multiply it, that's my asterisk, by 12%, .12. And again, I am seeing a value here, but it's also showing me that it's going to be false, so no worries on that. Clicking into the next field, Value_if_false. The value that's returned if the logical test is false. If omitted, false is returned and that's why I'm seeing false here because I don't have a value. Now my choice here, I could type in a 0 because they're going to get 0 if it's below quota, but sometimes when I type in 0 people see the 0 and they think something's wrong with your formula. So better than a 0, how about if I type in the words below quota. That would be cool, right, to see the words below quota and then there's no mistake, the function ran, it worked. It's below quota. And if I do decide to use words, I have to put the words in quotes. Below quota in quotes. And again, as soon as I type that in, you see it shows here. Whoa, so this is pretty good. Let me click OK. Below quota. Look at my syntax in the formula bar. I know it looks a little bit more scary when you look at it like that. Don't worry, the fx, the Insert Function button is there to help you build these functions step-by-step-by-step, so it's not that scary. If I give it a quick click to fill it down, double-click, you can see that some are below quota, some have hit. Let me click away. Yeah, so we've got some people making it. I think we need to light some fires there, get those commission values up, get those sales values up. Alright, so use the dialog. Let the function arguments dialog help you through the function. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Text Maybe you've found yourself in this spot. We need full name. We have first name, we have last name, we need full name together. Look at this spreadsheet. I am not retyping this and I've seen way too many people doing the typing. First name plus the last name displayed here. We have functions that can do this. I know, right, who thought we could calculate on text, but if you think about it, what we're doing is we're combining what we find in cell B4 with what we find in C4. So it doesn't matter that it's holding text, we're still looking at the cell references and picking up what's in the cell references, it just happens to be text. And we also, when we take a look up at the Formulas ribbon tab in the Function Library, have a whole text category. It's always worth a look when you're in a spot and you think there's no way out. I'm going to have to retype things. Plenty of text functions available. And for this one, as I float down, the one I'm looking for right now is concatenate. Look at the description in the screen tip. Joins several text strings into one. Yeah. Let's pick that one. And if I take a look at the function arguments and I read, right, it's looking for Text1, Text2. We just need to show it the text down here. Look at the description. One to 255 text strings to be joined into a single text string can be text, numbers, single cell references. Well let's see how this works out. For Text1, I'm clicking on Michelle, it shows me Michelle, shows me Michelle as the result. If I click into Text2, I noticed Text3 appears. Remember, it'll keep adding up to 255. So if I click on Adams, it does pick up Adams, but look at the preview of the formula result. There's no space. Right, that's not quite what I had in mind. So let me take this one out and I'd like to put a space bar space in. Well if I'd like to pass a space bar space on into this, I'll have to put the space bar space in quotes. Yeah, so it's open quote, space, close quote, and notice that we have a little space in there. And now, if I go into Text3, well Text4 appears, and I can click on Adams, Michelle space Adams. Really? Let's click OK. It's perfect. And with the double-click, boom, boom, fill it down. I didn't have to retype them. Wow. How much time did that just save me? Oh my gosh. So yes, think about that. Even when it's text and you think you're stuck, there is a whole category of text functions available. The answer could be right here. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Date In this spreadsheet, we have employees and department information, as well as date of hire and days employed. If I click on the days employed, you can see that the formula bar shows me there's a formula that involves cell G1 minus F4. G1 is here, today's date, and F4 is the actual date of hire, so we're just subtracting today's date from the date of hire to get the days employed. Looks good. Right, what's wrong with that? The problem with that is if I click on today's date, look at the formula bar. Today's date, is that today's date? If it is, I'm fine, but if it's not and today is the 3rd, I have to click into that cell and change the date to be November 3rd. Well, I could do that and it recalculates and then when I come back on the 4th, I could update it again, press Enter, and it updates. I don't want to do that, and you know what, I don't have to. No, we have date functions available to us. So if I select the cell that I'd like the formula result to appear in and head up to the Formulas ribbon tab, in my Function Library, we have a whole date and time category. Take a peek in here. There are a lot of them listed and well, there's a few ways to go here. One of them is to float over them, read the screen tip, right. Another method that we talked about earlier might be to go into Insert Function and do a search on finding something that would give us today's date, but while we're here, right, float over them, read the screen tips. It's a good read because there's a lot we can do in here and a lot of things that you just didn't know and we could discover by viewing those screen tips. We'll make our way down to TODAY, returns the current date formatted as a date. Sounds exactly like what we need. So if I give it a pick, the function arguments pops right up, small window unexpected. Where is the place that we put in values? Where do we click on cells? No, this one tells us it returns the current date formatted as a date. This function takes no arguments. So we don't need to do anything, except click OK? Yes. So I click OK. It pops in today's date, the whole thing updates, and when I look at the formula bar, that's the formula, =TODAY open paren, close paren, no arguments. Don't forget about the date and time functions. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Tables Create and View Maybe you've seen those spreadsheets where every other row is a different color, it's like two shades of gray, light gray, dark gray, and it's every other row and it's really nice and easy to read because of those banded rows. I want that. Now to put that in, manually selecting the rows and changing the fill color, whoa, very time consuming. Also, if I have to insert a row into the middle of the data, then my banding is off. Oh no. Well if we make our data into a table that is one of the many benefits we get. The banded rows happen automatically and we can format it to make it look differently. We also get the benefit of column headings. Now right now you see we have column headings at the top, first name, last name, region, yeah, they're there, but when we scroll down the table, they disappear. In this spreadsheet, it's not such a big deal because we can figure out what's in most of the columns, but you've probably worked in some spreadsheets where you've had column, after column, after column of numbers, and in that case, with no column headings, what number represents what? It gets really confusing. Well if we make our data into a table, the headings will stick. We'll always get to see them. I know right, tell me more. Now there's a lot more that we do get with tables, but those are two of the best things and it's so easy to make it happen. There are a few rules that go with this though. If you would like to take your data and make it into a table, rule number one, you need to have column headings. We do. Rule number two, the column headings need to be formatted differently than the data. They could just be bold or underlined, we have both, so that's good. Check, we've got that. We also need to make sure that the headings touch the first row of data. Check on that one. We can have no blank rows. There can be a blank cell here or there, but no blank rows, and as I scroll through the data, looks like we have no blank rows. The other biggie is consistency because when we build a table generally we'll get into sorting and filtering, so having consistent entries is always a big help, right. SE represents Southeast, it's always SE. Hockey is always spelled like this, football like that, no abbreviations, no shortcuts. I think we have all the bases covered. So let's head back up to the top. When we're ready to create the table, we'll click into the data. Anywhere in the data, but you've got to be in the data. Alright, and then we will head up to the Insert ribbon tab, into the Tables group, and pick Table. Because we clicked into the data, it automatically selects all the contiguous data. We don't have to select the range. Look at it, A3 through H103. It also thinks we have headers. We do, so we leave that checked. We click OK. I like it already. Let me click into the data, scroll back up to the top, there are those banded rows that I talked about. Wow. Boy that was really easy. Every other row, very easy to follow across and make sure we stay on the right row. Love it. Now what about the column headings? I told you that the column headings would always be there. When I scroll down, usually the column headings disappear. Watch what happens. Just keep your eye on one of them. I scroll down. They jump up. Did you see that? Let me scroll back up. So the ABCs that we always have across the top, temporarily disappear and become our column headings. So now we always have the headings while the data is in view and when I get down towards the bottom, watch what'll happen. If I scroll passed the bottom of the table, the ABCs are back. Scroll back up, column headings again. Now while you were clicked into the table, the column headings are always visible. If you click outside the table, we get the ABCs back because if you're clicked outside the table, you're not working with the table, don't need them. Click in the table, and they're back. How about that. Back up to the top. I don't know, I think it's worth it. Take that extra second, Insert ribbon tab, Tables, make it a table. Why not? And you'll find out in the next clips, there is so much more we can do. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Exploring Sort in Datalists and Tables Data listen tables are pretty similar. They do follow the same rules that they have to have headings, the headings have to be formatted differently, the headings need to be touching the data, no blank rows. If we meet that criteria, we have a data list, and the data list can be converted to a table. We saw the benefit of the table. It was pretty cool, right, the banded rows, the headings, and there's something else that we get that we'll see. But if we decide we don't want to convert our data into a table, we still have the ability to sort. So if I'm looking at my data and I'd like to put it in a particular order, maybe it's by region. If I click into the region column, anywhere on a single cell in the region column, I can head up to the Home tab, out to the Editing group, I've got sort and filter here. I can choose Sort A to Z ascending. If I give it a click, you'll see that it does just what you thought it was going to do. So that's one way to go. Home ribbon tab, Editing group, sort and filter. Let me come back over to the last name column, I'd like to sort it back by last name now. Let me show you a different method. This method's on the Data ribbon tab, Sort and filter group, hey look who's over here, the A to Z, same picture. It's the same tool, just another place. When we work with the data list, we spend a lot of time on the Data tab, so it makes sense to have it here as well. I'll give it a click and I am resorted by last name. Notice it's a single cell. I'm not selecting the column. Don't do that. If you do select an entire column, Excel warns you, hey you're about to make a mess. Do you want to do that? Let me show you. I'll come into days employed, select the entire column by giving it a Ctrl+Shift+End, and I'll press Tab to move back up to the top of the data. If I choose A to Z here, there's that message. Are you crazy? Right. Microsoft Excel found data next to your selection. Since you have not selected the data, it will not be sorted. If you were to continue with the current selection, it would sort this column and Michelle Adams would be assigned a new days employed. That would be bad. Right, so usually you'll choose expand this selection and it will expand it to select the entire data list or if you're really nervous about doing anything, cancel. Click into the single cell, and then A to Z. There we go. And you can see Sarah Bryant looks like our newest employee. So doing all of this on a data list, cool. What about if we have a table? Oh yes, I just switch sheet tabs down there at the bottom. If I've got a table, I can do the same thing here. So I can click into a single cell in division up to the Data ribbon tab, A to Z, done. Baseball's at the top. I can click into Last Name, head up to the Home ribbon tab, all the way out to the Editing group, go for sort and filter, A to Z over here. It works this way too. But with the table, we get one additional option. The arrow on the column header, yet we have that here with the table, we don't have that in a data list, so I can click on the drop-down arrow next to a column heading and look at the top, it's in here too, Sort A to Z, and it did. So we do get some benefits with going with the table, but if you're against going with a table, it's okay. If you have a data list, you can still sort your data, put it into an order, alphabetically, still showing all the data. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Exploring Filter in Datalists and Tables Here I am in a classic data list, headings at the top formatted differently than the data, the headings are touching the data, the data has no empty rows. I'm meeting all the rules for a data list. We've looked at sorting the data, which is great, but the problem with sorting data is it still shows all the data. Sometimes I'm interested in only seeing what I need to see. Sometimes sorting ascending, think alphabetically, I've got marketing. If I'm sorting ascending and I have departments like accounting, when I sort ascending accounting goes to the top. If I have departments that end towards the end of the alphabet, the Ms stay in the middle. So our descending, the Ms might not come to the top. I may always be scrolling to find the Ms. Hmm. Wouldn't it be great if I could just hide things that I wasn't interested in seeing and only see what I wanted to see. That's a filter. Yes, filters allow us to hide records that don't match our criteria. I'm in a data list. I can't filter. I have to make it a table. No, no you don't. If you meet the criteria of a data list, alright again, headings are there touching the data, formatted differently, no blank rows, we can head up to the Data ribbon tab, into the Sort and Filter group, and here filter. Look at the screen tip. Turn on filtering for the selected cells, then click the arrow in the column header to narrow down the data. If I click on filter, my first row, my header row, is recognized and I get the little arrows. I can now filter just like a table? Yes, you can. From here, the filtering side is exactly the same. I don't have my banded rows, I'm not going to have my heading benefit, but I can come in here and work the filter just like in a table. Let me cancel out of here. In the spot where you no longer need the filter turned on, you can click it again to turn it off, so turn it on, turn it off as you need. If I head over to a table, let me go to Commission sheet tab, arrows are there, it's part of the table, and I can click on the dropdown and look at that, it's pretty much the same. So while we do get some benefits from making the data into a table, you don't have to have a table to be able to filter. There it is. Turn it on when you need it and when you don't, turn it off. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Performing a Color Custom Sort When you have color in your spreadsheet, you can actually sort on it. In here, we'll see three different ways based on how your data is formatted on how to get that sort done. The first sheet we're looking at happens to be just a data list. Alright, standard data list following the rules we've discussed in the earlier clips, so I have data here with color coding. If I'd like to sort on it, I can click into the column, head up to my Data ribbon tab, Sort and Filter group, and use sort. In the sort dialog box, I can choose which column I'd like to sort on, this is region, and because region has color, when I come into here, sort on values, no, let's go cell color. I get a new column that shows me the different color choices that it finds in that column. So I pick and where would I like it placed, at the top or at the bottom, well since I'm at the top, how about put it at the top. When I click OK, it does just what I asked for. It sorted all of that color to the top. That's pretty cool. But let's move on to my next sheet tab. I have the same spreadsheet, but with an autofilter turned on. This time we'll use the drop-down arrow on the column, sort by color. I'll pick my cell color, how about the bright green, done. Just a little bit faster. And onto my Commission sheet. The Commission sheet is formatted as a table. So if it's formatted as a table, how about drop-down arrow, that's always there, and yes, sort by color. Let's have some green. So different data setups different ways to go, but in the end, if you're using color to color code, you can also use that to sort your data. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Using Multiple Level Custom Sorts Sometimes we need to have a sort that is multiple level. So I might be interested in seeing this data by division first, so maybe I sort by division. But then within baseball, I'd like it sorted by region. Well if I click into region and do a sort here, it completely undoes my first sort. Let me go to last name because after region I also wanted it sorted by last name. So if I had two or three people from a particular region in a particular division, they would be in alphabetical order as well. Hmm. So that method doesn't quite work. And whether I'm in a table or a data list with an autofilter or a regular straight-up data list, doesn't work. So if I want to do a multiple level sort, I must be clicked into my data anywhere in the data and head up to the Data ribbon tab, Sort and Filter group, and go for sort, the big sort button. Once I'm in here, I can tell it what I'd like to do. Division was what I was thinking on values A to Z. Now there's other choices in here, but values works for me A to Z, that also works, ascending. Now I can add a second level, Then By. If I put in region here also on values also on A to Z, that could work, but maybe I'll go with color. Well we have color in those cells, why not color? I could definitely do that. Pick a color, have it put that color at the top if I'm interested in a specific color. I could even add a level. So now when it brings me division and the region of dark green, then by, last name on the values A to Z, this might be exactly what I need. If you put something in and you don't like it, you can click on it, delete it. You can also move it up or down depending on where it falls in the list. So you can make adjustments. Let's see if this gives us what we need. Click OK. And what am I seeing? So baseball is definitely all together. And then when I look here by region, hmm, because I went with the green it's not quite doing what I thought. It's focusing, you can see, on basketball it put the dark green together. Alright, and then the next column alphabetical by last name. That kind of is working, but not quite. So click on a single cell, never select a group of cells, back up to sort and filter, it remembers what I did, so I can just change it up to values A to Z, click OK. That's better, that's better. North Central is together, northeast is together, northwest is together, and alphabetical within each one. Very nice. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Using Data Filters In this spreadsheet, I'm really interested in a particular division. Right now, we know I can sort it ascending or descending, but I'm only interested in seeing one division. When it's formatted as a table or even autofilter being turned on, I can use the drop-down arrow next to division. I can come down here uncheck select all and pick the one division I'm interested in. When I click OK, watch my spreadsheet. It hides all the other rows, only shows me what matches my selection. Wow. That was easy. Now if I sent this to print, what I see on my screen is what comes out of the printer. Also, notice to the left side it shows me the numbers in blue to let me know I'm looking at a filtered list. Also, down here, bottom left-hand corner, it shows me in the status bar 7 of 100 records found. Wow. Yeah, when I'm ready I can clear my filter by using the drop-down arrow with the picture of the funnel. It shows me the funnel to let me know the filter is done here and I can clear the filter. I know, that is so great. Come back to region, I can do the same thing, but perhaps this time uncheck select all, I want to see everybody in the north, so I check all three, north central, northeast, northwest, click OK, and that's what I get. Another way to clear the filter, instead of using the drop-down arrow, which now looks like a funnel, I can head up to the Data ribbon tab, sort and filter group, clear here, and it's gone. Now when we are filtering columns with numbers, they can be a little bit tougher because when I use the arrow, I have all of the values. So if there's a 100 records and there's a 100 unique values, I have a 100 choices. So actually unchecking select all to pick the ones that I want, hmm not quite the best way to go. I have number filters, so if I'm looking for values in a certain range, I can come to number filters, does it equal this, not equal this, greater than, greater than or equal to. Look at the different choices. And by making my pick in here, how about between? Is greater than or equal to, I can give it a number, let's say 3000 and is less than or equal to, how about 4000. I click OK and that's what I'm seeing. As I scroll down, everybody falls between 3 and 4. Pretty good, huh? Alright, back up and let's clear the filter from sales. What about in the tech side of things? I can do the same type of a thing. Let me go to last name and again, same issue here. But if I'm looking for, I don't know, a couple of people whose last name starts with the letter M, instead of scrolling down and unchecking and checking, there are text filters in that same spot when you're on a text column and my choices are Equals, Does Not Equal, Begins With, bing, bing, bing, that's the one I want, Ends With, Contains, Does Not Contain. So if I'm looking for names that begin with the letter M, I can do an and or an or in here as well, but that's all I need. I click OK. Only the Ms, ah, there it is, David Moore, the one I was looking for. Really nice ways to find the data that you need quickly use the filters. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Filtering Multiple Columns Sometimes we have a need to filter on multiple columns. We can do that. You can filter on every column if that's what you needed. So in this case, let me go to division, use the drop-down arrow, and let's say I'm interested in not all, but maybe, I don't know, baseball and basketball. Give me a couple. I click OK and I get baseball and basketball. And then I need to see just a particular region. So let me go with, hmm, tough choice, how about we stick with the south. Now this is good, two columns are going. I'm happy about that. If I needed to go also with sales that fell between a range, I could do that too. You can keep going column, after column, after column. So here we want to see maybe sales that fall between 3000 and maybe 30, I'm just looking to make sure we get some results, let's go 4000, and click OK. So now we've got filters on three individual columns. That's super. So if somebody said hey what are we doing in basketball and baseball in the south, between 3 and 4000, that's what we have. Also, if they say hey, you know what, widen that range a little bit. Let's clear the filter on region. When I clear from region, the other two stay. So I can expand it back if I needed to little by little or if I want it all out. We're done, it's over, I don't need to see this anymore, I can head back up to the Data tab and clear all the filters in one shot. But before I do that, keep in mind if I do hit Print, what I see on my screen is what comes out of the printer. You get the filtered list. I'll clear it now and everything comes back. That is huge. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Using Search Features When you are doing your filter, from time to time, we bump into a problem with choices. There are too many choices. Let me show you what I mean. If I go to division and use the drop-down arrow and I'd like to filter on weights and wrestling, I can come in here, uncheck select all, scroll down to the bottom, find weights, and wrestling. That's one way to go and there is nothing wrong with that method. Let me cancel. But a faster way to do this is to use the search feature. So this time, I click on the drop-down, I click into search, type in the letter W, and I'm done. Look at that. Weights is selected, wrestling is selected. All I have to do is click OK and it's done. I know! Let me clear that, so data, clear. How about last name. Alright, last name, let's say I'm looking for somebody's last name that starts with the letter M. When you look at the list, again, huge amount of scrolling before we even get to the Ms. This is really a problem when you have a lot of choices. So instead, we go into the search. I need somebody whose last name starts with M, I type M. Now notice what it's doing. This one has an M, this one has an M, yeah, it's giving me a lot more than I bargained for. So I type the letter A and now that list has gotten shorter, only M-A, so M-A here, M-A here, M-A here, M-A here. If I keep going one more letter, put in the R. If these are who I'm looking for, I click OK. If I only wanted Martin, I uncheck Martinez, click OK, and it's done. And remember, I can clear my filter from here as well. Clear filter from last name. So when you're in that spot where you have so many choices, instead of scrolling that list to find what you're looking for, start typing, it's faster. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Add Data I've got this beautiful table, every other row is colored, it's looking great. I need to add a record to the table. How does that work? You have to make the new entry part of the table. Let's head down to the bottom of the data. I will use Ctrl+End to jump down to the bottom pretty quickly and I'll just left arrow over, let me scroll down so you can see it just a little bit better, because you can see here there is a slight edge around the edge of the table to let you know where the table ends. I'm not in a table. I'm in the next available row, but I want this row to be part of the table. What do I have to do? Just type. So if I type in my next entry, I got Karen, tab, Smith, did you see that? As soon as I typed in Karen and pressed Tab to move over to type in the last name, the table extended itself. Not only did it extend itself, look at this. Where there are formulas, the formulas automatically extended, mhmm for commission and for days employed. Values aren't great, but that's because I haven't typed in all the rest of the information. So if I keep going and type in southeast, and soccer, and her sales, when I press Tab, watch my below quota, it calculates, and I press Tab to move into the date of hire, and I put in the date of hire. When I press Tab, watch the days employed recalculate. So really nice to know that as you click in and start typing, it extends automatically. Another nice little shortcut to know about is if you need to copy something from the row above. See here it was southeast, southeast. Let me come back here, press Delete. I didn't have to type in the SE. Now it's pretty short, I can usually handle that accurately, but sometimes the entry is longer and I need the same thing from the row above. If I Ctrl+', it copies from the row above. Let me press Tab, delete that, Ctrl+' copies from the row above. So when that's what you need, that's perfect, just copy, don't type. Ctrl+' copies from the row above. There's also a shortcut key for inserting in today's date. So if Karen was just hired today instead of August 10th, let me delete that out, it's Ctrl+;. Ctrl+; enters in today's date. It's a nice shortcut because that's a lot easier than typing in the date with the slashes. Yeah, that's not her hire date, so I'll go back and change it to 8/10/2015 by typing, but if you need the current date, today's date, Control+;. Nice little shortcuts there. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Add a Column and Totals Row I'm looking to add some totals. I've got sales. I've got commission. What's the total? Let's head to the bottom of the table, Ctrl+End to jump down to the bottom. Down here at the very bottom of the table, I can see there are no totals anywhere in sight. If I stay clicked into the table, got to be clicked into the table because we're using the Table Tools design tab. If your Table Tools design tab is missing, it means you're not clicked in the table. Anytime your Table Tools are missing, it means you're not clicked in the right place, click into the table, and the Table Tools reappear, so watch for that. On the Design tab, in the Table Style Options, yes style, we find Total Row. When I check the box for Total Row, I get just that. Look at this, double line, the word total and it decided to add up the days employed. Well that's interesting, I wasn't thinking about adding up the days employed. I was more thinking about adding up sales. If I click here for sales, drop-down arrow appears, give it a click, look at all of my choices. I would like to sum. Ooh, it's a big number, it's wider than my column, so I get the hash marks. Remember, anytime you're seeing those hash marks, it's not an error in your formula, it just means the column isn't wide enough to display it. So we'll give that a little double-click to stretch it out. I also would like a total here. Can I get that total here? Sure thing. Of that 360,000 in sales, yeah, only 13,000 in commission because many were below quota. What about this one? I need it out of there. Let's click right on it and set it to none. Now you see in here, there are other choices for count, and max, and min. Whatever you need, you can turn it on one, turn it on multiples. That's really nice. Alright, let's head back up to the top. So Ctrl+Home to jump back up to the very top of my sheets. And I'm also thinking from time to time, I need extra columns. Now if you know how to insert columns into a spreadsheet, you've already got a head start because if I did need a column, let's say to the left of column G, I could insert a column just like normal. Give it the right-click and insert. Gives me a new column, formats it to match the table, gives me a column heading field and it's ready to go. That's pretty good, but there's something even better. Let me back up, so I'm going to undo. What if I need a column out here? Right, when you select a column, the new column goes to the left. I want to use this one, it's here. If I type the column heading in, it'll automatically extend. So if I'm thinking of adding in a column for, I don't know, the quarter, alright what quarter did the sales fall into, quarter one, quarter two, something like that, I can type in quarter and watch what happens when I press Enter. So I didn't need to insert the column into the middle, I didn't have to insert the column at all. I typed in the column heading and when I pressed Enter, it extended the table because I'm using something that bumps up against the table, it extends it and extends the formatting all the way down. And again, I'm ready to go start filling in those values. Awesome. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Create a Calculation Now calculations in tables are kind of fun because Excel helps us out. It's a little bit scary in the beginning because it looks a little different, but in the end, you go wow. Let me show you. Let's say I'm here, I need to calculate commission. I've got sales, commission is 12%. Everybody gets commission. So I click in where I'd like the formula result to appear and I start my formula just like I start any formula with the equal sign. Alright, and we also start off just like we would because it's going to be this cell. I click it and that's when I go uh, oh, this is different. Don't worry. Everything that you know about formulas still applies. This is not showing me the cell reference like I was expecting. I was expecting to see E4. It's okay. When you're in a table, you see it puts in @Sales. This is the reference to the column. So it's saying it's looking to the sales column. Alright, let's keep going. So I need my asterisk for multiply and the commission rate, .12. So I'm building it just like I usually do, type in the equal sign, click on a cell I want to pick up, type in the asterisk .12. Alright, so it looks a little different. Here's where you go wow. Ready? I'm going to press Enter. Wow! You can't even stop yourself because it just filled the formula all the way down the entire table because it knows, I love when Excel is smart like that, it knows you're building this formula, and odds are, you'll want it to continue down the column. If you like that, we're done. If you don't like it, there's a smart tag. You can tell it to undo that fill down. That's not what I wanted, you could tell it that. You could tell it to stop doing it everywhere. Now I don't want to do that. I like what it did and I want to keep it. So I'm going to just click away from that smart tag and save my work. That's awesome. I know, a little scary in the beginning, but pretty exciting at the end. Cool. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Formatting When Excel creates our table, this is the standard view we get, the blue and the white, the blue and the white, the blue and the white. It's good, don't get me wrong, I like it, I'm happy, I'm so glad it happened for me automatically, but sometimes I want something different. We can get different. As long as you're clicked into your table, we've got our Table Tools design tab at the top and we have all the way out to the right, table styles. And in here at the quick styles, whoa, there is a big gallery of choices. What's your fancy? And not only do we have them, but you can just float over them, see what they look like, try them on. You'll notice as I float over them, I get a preview. What do you like? So we have the light category, the medium category, and then as we scroll down, the dark category, which are fun, and when you do work in any of these categories, you also have more options. So this is nice. If you find one you like, that's great, you can choose it, and then we can take it a step further because here we have table style options. So the header row, right now it's there, it's turned on, we can see it, we kind of need it, you can uncheck the box to turn it off. Yeah, I kind of like it, put it back. How about the banded rows? Right. We've got dark blue, light blue, dark blue, light blue. I can uncheck that, they're all just blue, you don't have to have the banding. I think it makes it easier to read, so I'll put that back. Also, here first column, watch the first column, column A, just made everybody bold and solid. Sometimes if those are titles, it's helpful to make them stand out and be just a little bit more different. I'll uncheck that one. We have last column. Alright, keep an eye on my days employed column. Right, nice if its totals and I'll uncheck that one. We have banded columns. So right now we've got the banded rows where every other row is dark. If I choose banded columns, yeah, wait a second, maybe turn off the banded rows, we have stripes this way or you can have them both and kind of, well I'm not quite sure. I'll uncheck the banded columns. And also out here in the styles group, you can turn the filter buttons on and off. One of the benefits of the table is the filter, but again, sometimes we just want the effect. You don't have to have the filter arrows turned on. So if you like the blue and the white, stick with the blue and the white. If not, feel free to come in here and to be a little bit more colorful. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Graphics Inserting and Formatting Pictures and Clip Art And even though, this is Excel and we deal with numbers, and cells, and spreadsheets, we do have the ability to insert pictures and what was called clipart in previous versions. The clipart aspect has kind of gone away, but we can still insert pictures of different types. So if we're interested in inserting any kind of an image, the Insert ribbon tab is a great place to look. We can head over into the Illustrations group and in here, pictures and online pictures. So if you have a saved picture, we can go find it, just navigate to wherever you may have saved it, give it a pick, and insert. That's really big. So a quick adjustment I can make is to grab a corner and when I grab a corner, I can drag it in make it a little bit smaller or a lot bit smaller, right, and make it something much more manageable for the page. When we have a picture selected, we do have the Picture Tools Format ribbon tab available, we'll come back and check that out in just a little bit. Let me click away, and you'll notice that ribbon tab disappears. So always keep in mind when you're working with anything, if you're missing your tools, click on it, and the tab reappears. Click away, it disappears. Alright, back up to Insert, back to Illustrations, and how about Online Pictures, which has replaced clipart. Now we don't have a gallery of clipart that's downloaded to your machine. Right, if you were looking for something outside of your computer, you can go out to online pictures. It's kind of cool because it does take you out to Bing and you can do your search, press Enter, and we can find a variety of images, but of course, we do need to be careful with licensing, copyright, so do be careful. When you scroll through and find the image that you think you like, you can click it, and insert. Of course, there are tons of pictures because it's using the internet, but the same problem exists. We're going out to Bing, how do I know what the licensing is. Well I do want to show you something else, let me just finish sizing this down to a reasonable size, and if we head out to Bing directly, alright, I'll use this little shortcut that I put here, and if you just opened a browser and went out to Bing, you could do a search and let's say I'm searching on clip art, of course you see all the different choices that are in there, clip art free downloads, Microsoft is a great place to look. We can also go out just look for what you're looking for, sales graph going up. When I type it in and press Enter, I'll want to head to the images and this is kind of nice to know because here we have a license filter where you can filter and make sure that what you're picking is okay. You can see that we've got some that are free to share and use, free to share and use commercially, modify, share and use, modify share and use, right, commercially, so lots of choices in there, and when you do find the one that you're interested in, right, point to it, right-click it, and I can save it, and give it a name. Alright, close out of my browser. And then of course, I'd have that available to insert as a picture because now it's been saved to my computer. So any way that you want to go is fine, just know you've got multiple methods to get there and we can find lots of different clip art available, just not directly through Excel any longer. Once we've got it inserted, there is a ton of formatting that can be done to adjust it. We've got that Format ribbon tab with the Remove Background group, the Adjust group, Picture Styles, Arrange, and even Size with cropping. So much to see. Take a quick look at some of the quick styles here. With my picture selected, if I point to any of these, just hover over it, it applies that frame, that style. We've got a couple of them listed here and it's kind of nice to see we can just try them on. The gallery, woo, there's a lot. And again, float over them to try them on. Watch the live preview. Really nice to be able to float around, find something that looks good before we actually click on it. Oh, that one's kind of fun. The way it flipped, it actually makes it looks like the arrow is going straight up. I might take that one, give it a click to apply it, click away to see how it looks. Check out the next clip for even more. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Additional Formatting of Pictures In order to do any formatting to any inserted picture, we have to click on it. So I'll click on my little sales picture here, brings back the Picture Tools Format ribbon tab, give that a click, and let's start all the way over to the left. In the Adjust group, I have Remove Background. You can see in this picture it has a white background. If I drag it over top, definitely has a white background. So I can remove it, not hard to do. What you'll see is that it turns bright pink and whatever is bright pink is going away. Well I don't want to lose the word sales. So what I do is I grab these big white handles and I'll drag them and drop them so that the word sales is included inside that area and that way I'll be keeping it. Looks good. All the way inside, I think I've got it all. And here Keep Changes. If I don't like what I've done, discard. That's like a big cancel. Keep Changes. Oh, the whole background just disappeared. That's pretty cool and pretty easy. Alright, but there's more. So with this sales image still selected, let me drag it off to the right just a little bit because we're going more into the Adjust group and the galleries that open are pretty big. So you can see in the corrections group we have sharpen and soften, brightness and contrast. The neat thing about them is it's showing you lots of previews, so you'll also notice as you float over them, you do get a live preview right on your image. So if you see something that you like, you can just choose it. Screen tips that popup tell you a little bit about what it's doing. Look at that live preview, got to love it. So if you like one, you click on it to choose it. I'm going back to the Corrections menu to close that up. Same concept here with color. Oh, we don't have to stick with what we've got. So it did come in a certain way, but we can play with saturation and color tone, and even recolor the entire image. Yeah, my picture is underneath the gallery. That's the biggest downside with so many of these galleries, they're huge, and when you open them up, they land on top of your image, but on the other side, you can see a preview of what it's going to look like if you were to pick it. It's right there. That's exactly what it'll look like. Let me close the color gallery and show you the artistic effects. Now these are pretty amazing because as you float over them, they do have names, chalk sketch, and look at my sales over on the right. Light screen, cement, ooh, that one's kind of cool, and as you float around you can try them all on, see what they look like before you commit. Well this is pretty cool. So easy to do and really makes it look like you've done a lot. So which one? I kind of like that cement one, right, and I can pick it and it's done. When we get in here a little bit further, there are other options that we can go with. It's nice that the artistic effects have it all piled together, that's great, but sometimes we need a little bit more control. There is at the bottom of the Artistic Effects the artistic effects options because these are combining them together to give you the effect and like I said, while they are great, actually amazing, right, sometimes we want a piece a here, a piece there. You can go into the Artistic Effects options for more control. Look at this. And we can deal with fill in line, we can deal with all the picture effects, we can deal with size and properties, and even head right into the picture. So what's cool about this, let me scroll out to the right, so you can actually see my image, is in here if you choose to do something like a shadow, you can expand shadow, a ton of settings, presets as well, so you can just pick and choose. Check this out. They all look like they just lifted off the page and then you can adjust it even more, drag the transparency down to make that shadow stronger, make the shadow bigger, blur it to make it softer. So you have total control and depending on your image, many times as you see here, it's applying to the individual pieces because it's transparent. Some things will apply to the entire image. The reflection is one of those. When I choose reflection and head for the presets, if I choose a reflection, you're not seeing anything happen here, it's down. Look at that. It's down here. So in this case, maybe not what I'm looking for, most settings have a no choice, so you can set it back to no. Into the different categories we go, lots of choices, and then additional settings, so you can adjust it and make it work for you. So you've got to check this area out, try them all on, there are so many to choose from, we could be here all day. When you like it, close it up, and let me scroll back to the left, click away, and look at that. So you can really jazz up your spreadsheets, but don't lose the focus, don't overdo it. This one's overdone just so I could show you what's going on there, but really can be an effective way to help convey your message. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Inserting and Formatting Shapes Sometimes shapes can be a big help to get your point across. In this spreadsheet, I have one value here, look at this, 22%, soccer running away with it. I want this number to stand out, and of course, I can make it bold, and I can change its color, I can do those things, but how about something different, maybe a shape. I'm heading up to the Insert ribbon tab, Illustrations group, and shapes. Look at this gallery. All sorts of shapes, lines, and rectangles, basic shapes, block arrows, equation shapes, flow charts, look down the window, stars and banners, and callouts. Well, one way that I could draw some attention to it is if I drew a big arrow right next to it and we have a bunch of choices here for different arrows. Well, let me just pick a standard big block arrow, and click, and drag. Look at that. The longer I drag, the wider, the bigger. What am I looking for? Well when I draw it and let go, it's not exactly where I want it. I'll move it up by clicking inside and dragging it. Well yeah, that will get your attention, but a big blue arrow, well maybe not blue and maybe I'd like to adjust it a little bit more. The yellow circles that you see allow you to make adjustments here as well. Yeah, that's kind of fun isn't it. And on top of that, we also have the option to take it even a step further. Up here, in the Insert Shapes group, here for edit shape, well if I decided I want a different shape entirely, I could go pick another shape and it would replace the one that I have selected, but this is fun. Edit the points. I give that a click, I get anchors, and I can click each anchor, and move them, and even change the shape of this arrow. Yeah. Ooh, I don't want to do that, but how about that. Let me click away, click back on it, and then adjust it even further, let me make it a little bit longer into the Shape Styles. So here I have a gallery, big, big gallery of all of these different colors. The gallery covers my arrow. I can't see it. I can grab the bottom edge of the gallery, drag it up, so I can see it, and then just have a little bit more scrolling to do. So I can float over them, try on different options, what am I looking for. Ooh, look at that one. I'll pick that one. And if I'm not crazy about the shade of green, I can also come into the shape fill, I can pick a different shade of green, brighter green, I can also put gradients inside of it, textures inside the arrow. Oh my goodness, let me just pick a color. I can give my arrow an outline. So in here, I can float around and choose a color and then even, let me pick a color so you can see this, come back in and adjust the weight, thick line, really thick line, that's fun. I could make it dashed or dotted. Yeah, the choices are extensive, and for even more fun, we have shape effects. Now with these, the preset group we'll come back up to last so let's start at the bottom because what we have here, is the 3-D Rotation and as I float over these, you can see the arrow rotating and moving. Yeah, pretty powerful, right? I go to bevel, I can add beveling, floating over them, see that live preview. This is amazing. Soft edges, well in here we could soften the edges. I don't want to do that here. We'll go up to glow. You can add a glow. Look at that, bigger glow, bigger glow, wow. How about a reflection? Yeah, float over them. Look at the arrow. It has a reflection. I don't think I want that. And then the shadow allows me to give it a shadow so that it looks like it's lifting off the page. See that. Pretty neat. Inside to look it like it has more depth. Wow. Now with each of these you can see you can spend a lot of time going into each one of these categories and then by the way, each of the categories at the bottom has more options. As if all of this isn't enough. Yeah, one of my favorite categories is the preset category because the preset category combines a number of those categories, shadow, reflection, glow, bevel, and gives you some presets, so you can pick from here and get a whole bunch of those settings without having to build it yourself. This is fast and they look good. What do you need? What do you like? What do you want? Hey, that one's not bad. So I can take that one, click away from my arrow and when someone looks at this spreadsheet, where do you think their eye is going to go. That 22% is jumping out now. And right, it's a little bit fun. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Adding Smart Art SmartArt gives us a way to put some nice little graphics into our page and also display data. Come with me here. I'm heading up to my Insert ribbon tab and into my Illustrations group, we've got SmartArt here. And you can see the description in the screen tip, insert a SmartArt graphic to visually communicate information. Alright. And take a look at the picture there. We've got like a process circle. Let's head into SmartArt and take a look. There's a bunch of categories. So the old category, right, contains them all, but as I move down to list, you can see that we have different list style SmartArt options, and if you click on them, you'll get a description. So you can click around, scroll as well, and see what catches your attention. There's a process category, some one's that look kind of fun. Cycle, ooh, these are good to. And the little preview over here shows you what they could look like with some formatting. Wait till you see what we can do when we get into formatting, a hierarchy category, relationships, matrix, pyramid, and picture. I'm going to head back up to process, and I think I'm going to scroll up and maybe grab this one with the big arrow, a continuous block process. So I kind of think that will suit my needs. I click OK, it pops in. Now let me drag it down here towards the bottom. It's a little bit big for my screen, so I can grab a corner, shrink it up a little bit, and the arrow here opens a pane where I can enter the text. I can click on the shapes and type or you can click here and type your text. It's whatever is best for you. So here we're talking about sales and commission, so perhaps I'm looking at a plan, maybe action, maybe deliver. X the pane to close the pane. The information stays in. I'll click on the edge of the frame and that looks good, but it can be better than this. So if I head up to my SmartArt styles here, you can see that we can float over different views, get that live preview down at the bottom, and I'll click the down arrow here for more. That's why I dragged it way down because when the gallery is open, they cover up your window. And look at these. Wow! So just by floating around, we can really format this, make it look great, and it's kind of easy, right? Find one that you like. I may go all the way down here with this one. That one's kind of fun. Give it a click and if I'd like to, I can change the color. I don't have to go with blue. Now when your galleries do cover, there is a grab handle at the bottom of many of them, grab that grab handle, drag it up. You'll have a scroll bar so you can still see everything, but this way as you float around, you can see the live preview, and make your decision, alright, while you're seeing it. I'll grab this one. Really nice. Not too hard either. Now the other thing is if I say, well maybe I should have tried on one of those other layouts. Here I have the layouts and I'll use the More button to expand the gallery, yeah, this is a big gallery as well. The grab handle is here, so I can move that up a little bit, and again, float over them and see if there's something better. Right. There are so many choices. What works best? Wow. And you'll notice that as I float around in here and finally make my decision, the formatting that I put on the original carries through, so I don't have to delete it, I don't have to restart. Any formatting that I've placed on the original comes through and if I like it, I can keep it or I can head back up here and continue to adjust. Once I've got it set, I can make my adjustment to the frame, put my mouse on the edge, and drag it into position, click away to see what it looks like without a frame on it. So the SmartArt is a nice option to put something in, jazz it up a little bit, snazz it up a little bit. Right and it's not hard, doesn't take long. It just looks like it did. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Adding Pictures into Smart Art And when we're looking for SmartArt that's just a little bit more fancy, we can put pictures into the SmartArt. So come with me up to the Insert ribbon tab, into the Illustrations group, and let's go back for SmartArt. Now all the things that you saw in the previous clip about formatting will still apply here. Let's focus just on a picture style. So in here, we have a whole section on picture, and remember, you can click on them to read their description and see if there's something you're interested in. The one that I want to grab is here. There are so many choices, so spend some time looking around, alright, but when you find the one you're looking for, ah, I think I like how this will go, use to show a set of pictures with text. The corresponding text appears in the central circles with the images alternating left to right. So my process here is how about the process of putting together a chart. One of the things that we do is we gather data. We may also enter the data or data, depending on where you are in the country, and then we will format our data or data. Alright, so we get that information entered in. I'm going to stretch this up and also move it off to the side a little bit. If I have images, the images go right into the boxes. So if I give a click, it asks me where my picture is, so you can go out and search Bing, if you have images, you can just grab them off your computer, which is what I have, and I'll grab my data and insert. It pops in. The next one, click, browse, and again, with your pictures together, we can just choose the picture and they pop right in. Click the last one, browse one more time, and choose it, and then of course, we move onto the formatting and as you've seen in the other lessons, it's really easy to do some nice formatting, not a lot of work to really jazz it up. So between that and our color schemes, we can really make this something that looks great without a lot of effort. Let me click away and there you have it. Right. Nice diagram. Pretty quick to throw together. It looks like you spent a lot more time than we did. How about that. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Snapshot Ah, the Snapshot feature. This is a really interesting feature. Now take a look with me. I've got this spreadsheet, annual sales; it shows me my four quarters. I'm heading up to the Insert ribbon tab, Illustrations group, over to Snapshot. Quickly add a snapshot of any window that's open on your desktop. When I click this, it shows me the available Windows, alright, so any files that are open will display here, and if I want to pick one, I can just pick it. Now I'll do this one. I'm going to undo this one when I'm done but I pick it and it pops in this really big window, which of course then I could resize. It took a snapshot of an open window on my computer. It's kind of cool, right. That's not quite what I had in mind, so let me press Delete to get that out. Let me head up, Insert, Illustrations, snapshot, and this time, I want to go screen clipping. Yes, that's my desktop and it shows me the entire desktop. What I do is drag across what I'd like to pick up, whatever is in the box is what I'm getting, when I let go, look what just happened. It popped right into my spreadsheet. Yeah that was easy too. And it comes in as a picture, so all the things you know from the earlier clip on inserting and formatting pictures, picture styles, adjust options, it's a picture, so you can make it look fancy with just a click or two. Wow. Let me click away. Show you what it looks like without the selection. How about that for easy. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Inserting and Formatting Word Art Another way we can escape from standard spreadsheets is WordArt. Yeah, we've got the name of the company up here and it looks fine and it's good, but you know sometimes we're looking for better. WordArt. Head up to the Insert ribbon tab, out to the Text group, WordArt. When I click on WordArt, I can choose a style right off the bat. If I like one of these, I can pick it, I can use it, I can be done, but as you've seen with other areas, there is much to be done in the formatting aspect. So many choices, so many changes, and yes, the same here. So I'll choose one that I like. I kind of like this one. I'll pick it. It jumps in your text here. So I'm going to type in Sports and I'll just type in Equip Ltd, shorten it up just a little bit, and move it off to the side, maybe even down here towards the bottom, so that when my gallery is open, they don't cover it entirely. Heading up to my WordArt styles, there's the first gallery and it looks just like the gallery that we originally chose from. So if I type it in and I think it's not bold enough, it doesn't stand out enough. So if I like one of these, that one's kind of nice, I pick it, and it's been changed, but in that same thought, maybe I'm not crazy about these colors. I like that it's bolder, it stands out. I can still go into that WordArt styles group and here change the text fill. What color do I want? So maybe I'm thinking a blue would be good and you know, by the way, gradients, yes you can, textures, absolutely you can put textures in there. Wow. Alright, but I'm not going that route today. Even pictures, yes. Alright, I'm just going to pick blue. I can also change the outline color. Float around, what do you like? I'm feeling a little purple today. You also have the ability once you choose an outline color to come in and adjust the weight, make the outline thicker, really thick, too thick, and you can even go back in there and change the style of the line. Dots and dashes, you could, I don't want to do that either. Alright and then here is where the fun begins. Shadows, reflections, glow, bevel, 3-D rotation, and transform. Now if you saw the last clip on shapes, some of these are familiar, so you may be already comfortable with using the shadows, to put a shadow on your text, as I float around, watch the text change. It's a live preview. Wow. And don't forget to scroll. There are always more choices and if you don't like any of these wonderful choices, there's always more options. I'll pick this one. Back up into my effects, same thing with reflection. As you float over them, live preview occurring, look down towards the bottom of my screen, you can see it. On for the glow. The glow is fun. Yeah. Not adding a glow. Head over to bevel. Bevel also really fun because we can float over them and really change the look of that text. So I'm looking to make it kind of bold, but also you know not too crazy. So I'll go with a bevel there, 3-D rotation, yeah, look at this gallery. It completely covers everything up. That's the worst part of these. That's the worst part. So let me head down towards the bottom, grab that handle, and drag it up, and that way we can see what's happening down there and we'll just have to scroll, but I can handle that. Float over them, I mean just wow. Wow, wow, wow. Yeah, you can really see that outline color when we knock it down. So amazing. I'm not looking for something that crazy, so maybe one of these? Yeah, I want it to be kind of straight up and down. I kind of like that one. This one's a little too much. Alright, so we find the one that works. And then, our last category is the transform. And again, same thing happening here, gallery covers it right up, so I head to the bottom of the gallery. You can see things changing as I just head to the bottom to grab that grab handle and drag it up, so I can see what's happening. And you see as I float over, yeah, wow, these are so cool. And I love the live preview so that we can click around, find something interesting, some of them, yeah, interesting, but not really effective, can't even tell what it says on that one. Some will be better than others. It all depends on what you're doing, what effect you're looking for. And when you find one, that looks good, I can pick it. Let me move my WordArt up here and I'm thinking of using this, it's a little bit too big, so let me shrink it down a little bit, instead of this. Select that. Let me delete that out. Come back in here, click on it, click on the edge, and let's move it in, resize it down a little bit. Now how's that? Makes it a little bit different. The other thing was good. Right, you decide. This is definitely different. Is it better? It's definitely different. Have some fun with it. Spend some time. Right, we don't have to be that boring old Excel spreadsheet. That works, but feel free to add in just a little bit of interest. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Printing Customizing Options As we finish up our spreadsheet, we have to start thinking about printing, and yeah, we can find some nice print options up on the ribbon. Come with me up to the Page Layout tab. In here, we've got a Page Setup group that gives us a lot of options. From configuring our margins, many times when you build a file, you might need to jam just a little bit more on the page, and if that's the case, changing your margins from normal to narrow allows just that. Let's fit a little bit more on the page because we've got smaller margins than the normal setup. And with a pick, they adjust. You can see as soon as I make an adjustment, I get the dash line letting me know that this is my now printing area. This is where the page break will fall and as I move down the page, here going across between rows 46 and 47 showing me that this will be on page 1, this will be on page 2. So this can help us. If I head back to margins and go back for normal, oh, look at that, so I kind of need those narrow margins to make this one work. Sometimes, as well, adjusting your paper orientation will help, portrait to landscape, tall paper to lying on its side paper. I'll stick with portrait for now. And even my paper size, we're working with letter, but if I needed to change it to legal or some other size, it's just here for the picking. Now also, there are times when you would like to print just a certain area. With this, I can select the area I'd like to print, set a print area, and now if I did send it to print, what I have selected would be what came out of the printer. It's kind of nice to know. And when I'm done, I can clear that print area so the next time I go to print, it will print just like expected, an entire page on a page. Wow. Now breaks we'll talk about in another lesson. You do have the option to put backgrounds in, so if you do want to as the screen tip says add some personality to your worksheet, you can add a background image in it's a little bit tough to do. You need to make sure that you have an image that's washed out and works or it will be quite big, quite bold, and can make your page hard to read. It's kind of cool looking though, right? Yeah, let's delete that. So keep it in mind if you are going to go with a background, make sure you've got something that's faded, washed out, it'll fit nicely back there. And we'll do print titles in another lesson as well. So these are some quick and easy ways to make some adjustments to help fit your data onto the page. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Scaling Page Dimensions You may have bumped into this one. I know I have too many times where I send my file to the printer and it prints the page, I pick up the page, I start to walk away, and wait here's another page coming out of the printer, and when I pick it up, it's one column or one row that didn't fit on the page. Oh, I hate when that happens. Well, we can fix that up with scaling. Yes, you'll notice here on my screen, I see the dash line letting me know my page break will fall here, so this would be one of those situations. Most of it fits on one page and then oh, that's going to be on a page all by itself. It's going to look terrible. So if I head up to the Page Layout tab, I have scaling options here and we can adjust the width and the height to fit ours to a certain number of pages. So if I've got a width option, which I definitely have going, I can set it to be one page wide. And when I do that, my dash line disappears and it will now be one-page wide. If I also needed it to fit on one page tall, I could do that, but you know what, before I do that, yeah, where is my page break? Here's one. Alright, and if I continue down a little bit further, here's another. So I've got a few lines that'll fall onto their own page and I might not want that. So if I tell it to fit to two pages tall, that line disappears. Yeah, this all fits on the second page. So now when I send it to print, it will be one page wide by two pages tall. Notice here the scaling shows me 90%. That could have been my other way to go. Set these back to automatic. I could have typed in 90% just to see. If I put it back to 100, there's my dash line. We can always knock it down, 95, 90, 85, whatever works to get it to fit on your page is what it's all about. There's another method for this as well. And I think it's a little bit easier to visualize because you can adjust the page breaks to fall where you want them. Now before I do that, let me just sort my data really quickly by region, and let me head over to the View ribbon tab, Workbook Views, and head into Page Break Preview. This one will actually let you see where the page breaks fall and it's really obvious, so yeah, different than this dash line, which is hard to see. So I'll give it a click, oh yeah, that's kind of obvious, these bright blue lines. There's my blue dash line. If I want this to fit so that it's one page wide, I can drag this out and now it's one page wide. I just scaled it down. Now also as I come down here, I can see the dash line to see where it falls and where it falls, hmm. So what I can do back here is I can drag and drop these lines where I want them. So if I want it to fall there, I can have it there and then the whole southwest region prints on its own page, page 3. How about this one? Drag it down so that it looks like we've got northwest, and northeast, and north central onto one page. Hey, can we get south central, and southeast, and southwest onto a page? I can just drag that page break and drop it where I need it. And now, you can see page 2, page 1. You make the adjustments. Now when you come back here, every once in a while, I know I've done it and made a huge mess, and I wish I never did that, you can undo your adjustment by going up to the Page Layout tab, Breaks, and you can reset all the breaks as well and it's back, and it's back. So it's nice to be able to do it in this view because you can really see it. Drag them around. Life is a lot easier. Once you're done working back here in Page Break Preview, you'll need to go back to the View ribbon tab, Workbook Views group, back to normal. That's the view that we generally work in, normal view. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Modifying Gridlines and Headings Now stop for a second and take a look at this spreadsheet. Something is different. What's going on here? Where are the gridlines? And what about the column headings, the ABCs, the numbers down the left? Yeah, what's happening? Sometimes we see a spreadsheet like this and we kind of like it with no gridlines. Sometimes, yeah, it's just a little bit different and we want them back. Well come with me up to the Page Layout ribbon tab and into our Sheet Options, Headings, you want the ABCs back, the 123s back. Check the box for View and they come back. Man, it makes it a little easier to work in Excel, but same thing with printing. Have you noticed when you print the spreadsheet, you don't get the ABCs and the 123s, the column headings, and the row numbers, but sometimes it would be really helpful if somebody had a print out and you could say look at cell B4 and they had the B at the top of their printout and the number 4 and they could see exactly what you're talking about, you can tell it to print the headings, and it will print that structure around the outside of your spreadsheet. It's so cool when you need it. Alright, and then we've got our gridlines column, so if I check the box next view, yes, they sure do come back into view, and same thing with the print. You may have spent time in some of your spreadsheets adding borders around all the cells, so that the gridlines print. You don't have to do that. If you check the print box here, it'll print the gridlines. Wow. That's a nice time saver. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Creating Headers and Footers When we have a multiple-page spreadsheet, many times we will want to have page numbers that print at the bottom. Yeah, it just makes like easier and sometimes even when it's not multiple page, we might want a heading to print at the top, maybe the company name, maybe the word confidential. If that's the case, we can put this information in the header or footer area and have it pop onto every page automatically. The header prints at the top half inch of every page, footer, bottom half inch of every page. When we're ready to add these in, we'll head to the View ribbon tab, Workbook Views group, Page Layout. In Page Layout view, we actually see the edge of the paper and it's kind of exciting because for many years in Excel we didn't have a view like this with rulers and the edge of the paper like in Microsoft Word. This is kind of new over the last few years. So here you can see it says Add header. When I click in to add header, I want you to watch the ribbon at the top. Watch to the right of the View tab. Over here, watch what happens. New tab. Yes, we get the Header and Footer Tools Design tab. So if I give a click to it, I have a lot that can happen back here. If we start over in the header and footer group, this is a really nice place to grab quick headers and footers. Watch. When I choose it, you can see here, it gives me pre-created choices. So if I wanted one I put in the word confidential in the left corner, the date in the middle, and the page number in the right, I pick this, and it's done. And it just jumps in and it's on every page. You can see here and then over here. If I scroll out to the right, it's on this page too. So build it once, it goes on every page, which is great. If I like it, I'm done. If I don't like it, I can click back into it, back up to the Design tab, and I can change to something else or none, click onto the page, click back into it, and it's gone. Back up to design. I can build my own number of pages, drop them right in. Page number would give me the number one on page one, the number two on page two. Number of pages gives me the total number of the pages, so if I did want to use that, I could hit Page Number, type in space of space, number of pages. It puts in this crazy thing, but what that does is that will adjust so on page one, it'll say one of, I don't know, however many there are, six, and then on page two, it'd say page two of six. So there's the 1 of 6, if I scroll over to the right, this one's number 4. Yep, two and three must be down. Scroll back over to the left. That's kind of cool. Oh, I need to leave that out. Alright, the current date, the current time, the file path, really helpful when you need to find that file that's printed and in your hand, the file name, the sheet tab name, even pictures can go into the header or footer. You also have the ability to just type in whatever you'd like. So if I did want the word confidential, or draft, or both, I can put that in. Click back on that and you can see here our next group, go to footer. So if I give a click to that, it'll jump down to the footer of the page, there's the header of the next page, and I can build something here as well. We also get options where you can have a different first page or a different odd and even pages, you can have scaling and aligning. Really kind of nice. Let me choose go back to header, I'll leave that one in, I'll click into the page, and also while you're in this view, we came in here to do the headers and footers, but this is an actual working view. So I can click into any cell and make changes. So if you'd like to work in this view, you can. But if I'm done, I can head back up to the View ribbon tab and head back to normal. Normal is our standard view. It's the one we're most used to seeing. And we're back. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Print Titles When we're working in a larger spreadsheet and we have to print it, sometimes we can't jam everything to fit nicely on one-page wide by one-page tall. Sometimes we need the columns to go onto another page. Sometimes, yeah, it'll split in the middle of the spreadsheet and when we print, it looks bad. Let me show you. I'm heading up to the View ribbon tab and into Page Layout view. Let me scroll. This page looks good, column headings across the top. I know what everything is. When I scroll out to the right to go to this page, imagine holding this page printed in your hand. Days employed. Who? Who do these go with? I don't know, right. It would be nice if the names printed next to that again on that page. How about full name again over there. And when I scroll down to page two, imagine holding this page in your hand. There's no column headings. Well I can figure it out, right, first name, last name, department, region, what's the date? Oh, date of hire. It would be nice if the column headings printed again on this page. We can make that happen. So let me head back up to normal view, over to the Page Layout ribbon tab, and here print titles in the page setup group. Choose the rows and columns you'd like to repeat on each printed page. Okay. Let me click that button and drag it down a little. Rows to repeat at top. I click in here. Which row do I want to repeat at the top of each page? Row three please. So I click on the 3 for row 3, it puts in row 3 through row 3, 1 row. You can select more than one. Columns to repeat at left. A, I'd like A. Is that all there is to it? Let's see. I'm clicking OK, and I'll check out view, workbook views, page layout, and I'm seeing good things over here out of the corner of my eye. Let's scroll over to the right, look at this. This is much better. Ah, the heading needs to be cleaned up a little bit, but this is yeah, what I'm really interested in. Wow. Now what if we scroll down, down, down, down, down, yeah, yeah, yeah, yeah, yeah, this is good too. Column headings and if I scroll out to the right, yeah! So rows to repeat at top, columns to repeat down the left. Awesome. I'm heading back up to normal view. You can't see it in normal view, but you can see it in Page Layout view and Print Preview as well. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Print Settings In the previous clips, we've see a lot of different options for setting our page up for printing. Well let's actually head in to print because there's a lot to see in there as well and when we head to the File ribbon tab, we see print here. Print is also print preview. So when we give it a click all the way over to the right, this is your print preview. Down at the bottom, you can see 1 of 6, so we can use the arrow to go to the next page and to the next page, right, and all the way through, and see exactly how it will look on the printed page, which is nice, no surprises. So if you had one column or one row that was going to print all by itself, we could clean it up here. Of course, we've got the big button here. This is to actually send it to the printer with how many copies you'd like. Here's where you choose your printer. That's stuff we've all seen many times. But there is something in here that is kind of neat, exciting and a lot of people don't know about this. Here you print the active sheet. So whatever sheet tab you're on, you come in here, you hit print, you get that sheet. I can't tell you how many times I've gone to one sheet, print, gone to a different sheet, print, gone to the next sheet, print. How about this? Print the entire workbook. Whoa. One clicking of print, whole workbook. Also, this one's nice. I know there is an option to set a print area, we saw that in the earlier clips, but if you select an area and then choose this, whatever you have selected is what comes out of the printer. So if you only need cells A1 through J22, select it and print selection. Yeah, those are some good ones to know about. Alright, you've got the option to print from a page to a page. How about here, one-sided, both sides, and the determination of which way to flip the paper. If it is multiple pages and you're doing multiple copies, how do you want them to be printed, collated or uncollated? There's your option to change it portrait to landscape, paper size, those are standard, margins even, and then your scaling options. This is kind of nice as well because you see in mine I have six pages. You have one column is going wide. So I could say to fit the columns on one page, if I choose that, it shrinks up and now my document's only two pages. Right here you can change your mind. What do you need? Alright, so make those adjustments and then hit Print. No need to head out and go back to the Page Layout tab to make those final adjustments, do it right there in print preview and as you make the adjustments, you can see how it looks on that preview right there for you. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Review Using Proofing Tools It's never a bad idea to run a spellcheck on your file. Typos slip by. So if we're interested, this is up on the Review ribbon tab, all the way over to the left side, Proofing, there's spellcheck. Look at that. Typos, not on our watch. So we give it a click and the spellcheck runs. It's similar to what you've seen in probably every program out there. It will look through your spreadsheet, find words that it doesn't recognize, and offer you suggestions, so you would pick the one that you need. Now over to the right, your option, if this is what I want to go with, I can choose change and it'll change it in this instance. If I have mistyped stick elsewhere and I chose change, it changes it this one time, and if I've done it again, it will show it to me again and again, almost like laughing at me. Ha, ha, did it again. Yeah, probably. So instead, I could choose change all and every time I've mistyped stick with the i and the c reversed, change all will fix it. It doesn't show it to me, just fixes it. I like that. But one of the problems I do have with the keyboard is I do reverse the i and the c a lot, so I can add this to my autocorrect and then anytime I type stick with the i and the c reversed, autocorrect would automatically fix it to the right spelling. Ooh, is that cheating? Kind of, right, but it works, so I like it. Then there's the option to ignore. Sometimes we have names, company names, people names, we have industry-specific words, if that's the case, we can tell it to ignore that word once, ignore it every time it pops up, or even add it to the dictionary, so it knows this is a term we use, stop showing as a typo. So I'll go with stick spelled correctly, and I'll pick change all, just in case, and it says do you want to continue checking at the beginning of the sheet. It starts where your cursor was. I think mine was here in B1, so that means it's going to go all the way down and then go back up to the top to make sure that it got everything before where I was clicked, so yeah, please check it all. Ah, I like that. Spellcheck complete. You are good to go. So I click OK. Spellcheck done. At least I feel good about that. Now we have another tool in the proofing group called Thesaurus. So yes, when you're stuck, I don't want to use that word again. What can I use? The thesaurus can help us out and show us words that have the similar meaning. I'm going to head over to my Commission sheet tab and here I have region, maybe I want to see if there's a different word for region. I select the cell that holds region, I hit thesaurus, it brings me up a list of words that have that similar meaning, area, district, county, section, province, territory, well it is sales right? So if I like territory and I want to pop it in, drop-down arrow, give it a click, insert, and it's in. So we all find ourselves in that spot where we're stumbling for a word, let the thesaurus help you out. When I'm done with it, I can use the X to close the panel, and of course, save my work. I hope you found this information helpful, and I look forward to seeing you in the next lesson.

Working with Cell Comments As we look at this spreadsheet, there is probably something jumping out at you. Yes, look down towards the bottom of column F. Do you see that little red corner? What is that? Yeah, well I'll tell you what. When you put one of those into a spreadsheet, whoever looks at it does just that. What is that and they point their mouse to it and it's a comment. It pops right up. We can add comments to our spreadsheets to add additional information. Wow, how about that. I like that. I move away, it disappears, I move back, it comes back. That little red corner is so irresistible. We always take our mouse and point to it, and they popup, and we go oh, that's what that is. What if we want to add these in? Maybe I've been talking to the manager of how about the football division and their fourth quarter numbers aren't final. We want to put a note here on this cell, a comment here on this cell just so that we remember maybe to check with them for the final numbers or to let everybody else know that's looking at this spreadsheet this number isn't final. We select the cell we'd like to put the comment on, head up to the Review tab, Comments group, New comment. We give it a click, it pops in your name, depending on how the software is registered, your name, your initials, and gives you a blinking cursor. I can type right in here fourth quarter numbers not final as of and then I could put in today's date. So if today is November 15th, I can put that in. Check with manager before final submission. There's my note. Click away from it, it disappears, point to the cell, it pops up. Well, how about that. Now let's say I've checked with them. Maybe it's a month later, maybe it's December 15th. I want to edit my comment. I can right-click, Edit Comment, and change that number to 12/15, click away, and it's in. Pretty neat, right. When we have comments in a spreadsheet, we also in the comments group will have the option to just jump to a comment, so you don't have to float over it, you can hit Next, and it pops up, hit Next, and we can click OK, and it'll come all the way back around. And if we're done with this one and we've received the final numbers and we'd like to delete it, there's a delete here and it will disappear or let me click away, right-click, there's a delete in here too. So I can delete those comments when I'm done. I'm not done yet, so I'm going to leave it in. Notice up here, show all comments, how about that. Click it again, they disappear. Oh, that's kind of cool. Now also, one other thing I do want to show you. On the Page Layout tab, in my Sheet Options group here, we have a Dialog Launcher, Comments. You can have them print at the end of the sheet or print as displayed on the sheet, so they are printable. Come in here, Comments, Print Group, and say how you'd like them to print, and that's what will happen. Nice. I hope you found this information helpful, and I look forward to seeing you in the next lesson.