Excel 2013 Essential Training

Entering data into cells, typing data is one of the most basic things you do in Excel. There are certain rules and restrictions, regarding how Excel treats data. Let's imagine that we want to keep track of data for a six-month period for a small business that we're starting. We're only going to need a few columns, so let's use the Zoom Slider Bar--in the lower right-hand corner in the Status Bar--to zoom in. We've got plenty of room on the screen. In cell A2, and we can click there with the mouse or use the arrow keys to get there, we're about to type the word "Sales". Type it.

We complete the entry of a cell by pressing Enter or Tab or any of the four Arrow Keys. If you press Enter, the active cell moves down into the next cell below it, if you press Tab, it moves to the right. I'm about to put the word "Expenses" below this. I'll press Enter. As I'm typing "Expenses", I make a mistake. What do I do? Backspace. Not left arrow, backspace. After completing this I realized I've typed it the way the British and the Canadians type it with a "C", so I want change it.

Do I need to erase this? No. Simply type right over it, so very often you'll find yourself not erasing or deleting contents of cells, but simply typing over these. Furthermore, I've decided I want to use the word "Overhead". Enter. And then "Profits". Let's say I make a mistake, but I don't catch it right away. Press Enter. Obviously, that's a misspelling. I want to make a change. How do we edit a cell? Couple different ways, click the cell and then possibly click in the Formula Bar, near where the problem is.

I click in front or after the "G". If I click in front of it, I can press Delete, get rid of the "G" or click after it, I can press backspace, putting the letter "F" after doing that. Another way, I think it's better much of the time, and you don't necessarily have to select the cell first, but what if I had typed this and I pressed Enter-- active cells down here--I see that I've got a problem. I want to do editing by double-clicking, particularly, if you've zoomed in and this is a lot larger than what you see up above in the formula bar, simply double-click near where you want to make the change.

I'm going to double-click in front of the "G" right there. Type the letter "F". Press Delete to get rid of the "G" and press Enter. Now, text entries are automatically aligned in the left side of a cell. Much of the time, you'll just keep it that way. Later, we'll show you how you might want to center this data or line it up on the right-hand side. Let's imagine that we want to put in some numbers here. Now, on purpose here and maybe I forgot that I had Caps Lock on, but imaginewhat would happen if I type 120, I just happen to press "O". It's right next to zero on the keyboard, so that's an easy typo.

I don't think much of that. I want to move rightward, so I'll press Tab. Now, that's 12O. The reason it's left aligned, is because it's not a complete number. Two of those entries are numbers, but as long as there is a non-number in there, Excel says in effect, "this is text, I line it up on the left-hand side". In the next cell I want to type 160--and that's what a zero looks like--and when they're together you can see the obvious difference. If I press Tab now, what happens? Numbers are automatically lined up on the right side.

That's a general rule and we'd learned that back in the second grade--line up your numbers on the right side before adding or subtracting or multiplying--good idea, keep it that way. There will be occasions when you're working with numbers like ID numbers, where it wouldn't hurt to center them, but as a general rule, as a general concept, keep numbers right aligned. Now, we want to make a change here, we see the obvious problem--double-click behind the "O", Backspace, zero, Tab to the right--there we go and I'll just type in some other numbers here.

Here's a 210, press Tab, 250 press Tab, 325 press Tab, and 440. As I press Enter, automatically brings us back to the next row and just a few more numbers here. These are all numbers and they're going to be right aligned and no typos here. Occasionally, you might use the letter "L". It sort of looks like a 1, but it doesn't really. You'll have the same issue that you had with zeros (0s) and "O"s. So, I've got 100, 130, 160 each time tab.

So we've seen data entry. It's basic and the whole idea of changing these anytime, we can type right over the entries or in some cases, we edit the cells. Keep in mind the basic idea, Text is left aligned, numbers are right aligned.

If you need to enter months, days of the week, dates or certain number series, Excel has a great feature called Auto Fill, which is really handy and we're about to use it here. We need to put in the first six months in row 1 here, starting with January. Now, the Auto Fill feature is based on the idea that in the lower right-hand corner of the active cell is a special button and it's called the Fill Handle. Notice, when I slide the mouse over the lower right-hand corner, the mouse pointer becomes a thin plus, sometimes called crosshairs.

If I hold down the Left Mouse button, now and start to drag to the right, notice the pop-up below that says, February, March, April, May, et cetera, I'm going to keep dragging this right up to here. Let go of the mouse and we put in the other months. We'll worry about the alignment a bit lighter. That's really handy. Same thing would have happened here. Now, we're not really going to be using this in column A, but I'm going to click over here in column A and do the same thing. Drag from the lower right-hand corner. If this were a situation in a different kind of worksheet where we were doing this for 12 months, you could drag all the way to the end.

If you dragged a bit too far, it would just start all over again. Now, we don't really need those, so I'll press Delete and they're gone. Now, alternately and working in the same way, if we start with abbreviations-- now, you can start with any month actually-- typically, you would start with January or "J-a-n". We can drag across here using the Fill Handle and I dragged a bit farther this way, so it went into July. Anytime you drag rightward or downward using this feature, you move ahead chronologically. Now, occasionally but rarely, you might drag upward if I take "J-u-l" from the Fill Handle in the lower right-hand corner and drag upward, we get June.

If we were to drag leftward here, we get May and so on. It's a bit unusual to drag in those two directions, but you can do that too. Now I don't need this data here, so I'm going to select it all, starting from any of the corners of this location-- click and drag across and down--highlight the data and press Delete. Now, although we don't need the entries in this worksheet, I want to also alert you to the fact that if you're using days of the week--and you might start with Monday or Sunday, doesn't make any difference--here too you can use full spellings or abbreviations, this way.

In both cases too, if you are using abbreviations, they must be three letters, so although you probably wouldn't start with Tuesday, if you did, use "T-u-e-s" and drag from the corner. It's not going to work, because you'll get the same entries all over again. It's gotta be three letters if you want these to change--if you're using abbreviations--so three letters or full spellings. In addition to these kinds of entries too, if you enter dates, for example, if you type 2/3/13, date entries when copied from the lower right-hand corner, automatically, give you the next day and the next day.

This Auto Fill capability is available in a variety of situations and many, many times it has to do with dates or days of the week or months as we saw earlier. And also handy at times, although not in this worksheet, if you wanted to create a series say, 5, 10, 15 or 1, 2, 3--something like that--you can create two entries. Here's a 5, here's a 10, and then we click and drag across both of them together and then drag from the corner, so we drag downward a few cells here; we'll get the series this way too.

If we had dragged upward--if we had room--we get 0 and then -5 and so on. Now, I can erase these or I can use another feature, which we'll talk about in another movie called "Undo". I don't want this data here, so I'll press Ctrl+Z, or in the upper left-hand corner use the Undo button.So "undo", and we'll just undo some of these entries here; and a few more undos too. Creating monthly entries, like we see here, either with abbreviations, starting with "J-a-n" or with full spellings, we can easily make these entries with Excel's Auto Fill feature.

In this worksheet called "Dates-Times" in the 02-Entering Data Workbook, we've got a date entry in column B and some time entries in column D. Excel is adept at handling date and time information. You want to start off by just making sure that when you do put dates into Excel that you want to enter them in just a few different ways. First of all, if you're working in the United States with Excel, your standard Excel settings are to display dates as from left to right, month/day/year. In other countries it's likely to be day/ month/year, but this represents January 8, 2013. It's typed with slashes. The standard way of entering dates in Excel is with slashes or hyphens. No matter how you type it, you will see the display as slashes. So if we want to put in for example, November 17, 11/17 and it's in the same year, we'll type 13, Enter, no great surprises there. Same thing would happen of course if we type hyphens. It will display with the slashes. A small tip here, any date in the first portion of this century--the first 10 years--if for example, it's April 5, if it happens to be 2007, just type 7, Excel displays the entry this way. One oddity that you might or might not run into--if you're dealing with dates within a certain timeframe--maybe you keep track of retirees or maybe you're keeping track of the age of buildings or something, someone who's born December 13 in 1930, you'd probably type it that way and press Enter and not at all be surprised. How about someone born the year before, on November 12 of 1929? You'd probably type it that way and then be real surprised when you seethis kind of a display. Here's what Microsoft has done and they will change this in years to come. Any date entry that has a year from 30 through 99 is automatically considered last century, and any date entry that has the year from 0 through 29 is automatically this century.Now, when you're typing these entries, if you're dealing with data, just play it safe and type four-digit years. In this case, we would be typing--of course we can edit it now--but if we were just typing this, it's 11/12/1929--if that's what you mean--because we just put in 29 as we saw earlier, it's going to be 2029. Again, the reason for putting these in, in these ways is that date entriesare actually values. They're stored as numbers, that's why they're right aligned. If you do this correctly, you open the door for extensive use of these. We can find out day of the week here.We can find out amount of time elapsed between different dates. There's just a variety of things we can do using Excel date functions that are built-in. Similarly with times, make your time entries in 24-hour style or an A.M/P.M. style and use colons. Recognize that although that's not right or wrong it's 1:32 A.M--unless you type A.M. or P .M.--it assumes A.M. So an entry here--and we can type these in a variety of ways-- use colons, if it's 4:32 p.m., you can even type it "4 colon 3, 2 space P", and it gets stored that way. Also with times, by putting in times correctly, we can later calculate time differences or time into the future. The Excel DateTime system is designed to work by the way until the year 10,000. We're in good shape here if we put in our dates and times correctly from the beginning.

We all make mistakes from time-to-time and of course working with Excel is no exception.Let's imagine maybe we're going to make a serious mistake here. We want to get rid of these numbers and maybe we've just got row 2 selected and aren't thinking so clearly. Cell A, too, might look like it's not part of the mix here. So, you might go to Delete here, maybe you're exploring the commands, you're not familiar with how they work, you happen to click this and you realize that did a lot more than you wanted it to do. So, you'd like to reconsider all of that.In the Quick Access Toolbar--unless you've removed the button and I strongly suggest that you probably didn't and hope you didn't-- the Undo button is right here and it's followed by the word representing what you last did, the last action you took--in this case "Delete".

Recognize also the keystroke shortcut for undo, Ctrl+Z. So, we're going to press this Undo button and bring back the row that we destroyed there by mistake. Now, not only can we undo our last action, but possibly the ones before that as well. With a little drop arrow to the right of undo--if you click it--shows us up to as many as a hundred different actions. Now, maybe I just opened the file, I've done some other formatting, so this one only goes back 18 actions. I think rarely do you want to go back that far, but you can consider undoing a whole series of steps, but you can only undo a consecutive set.

So, if I wanted to go back and undo some of the other things that I've been doing in the meantime--maybe I was working with data in other cells-- if I want to undo some of those actions but only a consecutive set of them, if I were to click right now for example, I would undo the last seven actions. Now, let's take a few actions on the screen here and maybe again we're novices, we're experimenting here, we've heard that you might be able to add color. So, we drag across this. This is covered in a later movie, but we're going to click this button righthere and make everything yellow.

We sort of like that maybe for a while. Here's some button here it says "Font Color", so let's hit the drop arrow and we'll try something here maybe, something that's going to show up, maybe that. Maybe we like Bold, there's B, we'll try that too. So, we're experimenting with some formatting features here. Maybe we use this for a while and we say, "I'm not sure if I like that".Now, we might have done other thing things in the meantime. Maybe we put in some data down here. Maybe put in some numbers. Now, if we want to go back and undo some of the formatting that we did, if we go back to the drop arrow just to the right of undo, it also includes the recent typing that we just did.

Now, if it's okay to get rid of those, we will, if we want to get rid of Bold, well, maybe we do and the font color we made in the format. The point is we have the choice of deleting these, but only the consecutive actions. So, if we go back this far, we will still be leaving the font color in the background. If we include the font, then we will be getting rid of that font color in addition to our two typing entries in the Bold. So, let's say we do that. Now, anytime you undo actions, whether it's one or many, the actions that you undid are stored in a different category called "redo".

There's another arrow over here called Redo. It's got a keystroke shortcut of Ctrl+Y. So, if we click drop arrow here, we will see the features that we just undid and maybe we undid more than we wanted to. So, to undo the undo, it's called "redo". Maybe we want to bring back the font color and the Bold that we had applied, so we'll do that. Now, part of this is being set up just, for example purposes, but I think you can sense how sometimes you use undo, because you really made a serious mistake and you want to undo it.

At other times you might even want to kind of toggle back and forth between changes. This is not the best example of it, but we might, for example using--Ctrl+Z--now and that take us back to here; I'll press again, takes us back to this look. What if I change my mind? I'll press Ctrl+Y, sort of reverse stream. So, you might try that for example, if you're working with certain charts, you can toggle back and forth with the Ctrl+ Z and Ctrl+Y. The main capability though here is this idea that when you make a serious mistake--when you perhaps delete data you didn't want or made a change--that you can undo it.

Now, the feature is not universal, particularly when it comes to changes that you make to Sheet Tabs. If you've never used these, you might not have seen that if you right-click one of the Sheet Tabs, you'll see quite a few choices here, nearly all of these you cannot undo. So, don't ever assume that if you delete a sheet, you'll be able to get it back. So, you want to be careful with undo. It is a great feature, no question about that, but don't assume that everysingle thing you do can be undone; but it's a real lifesaver at times. Keep in mind too, sometimes when you're experimenting with data, it might make sense to go through the cycle of undoing--Ctrl+Z and then redo with Ctrl+Y to compare two different appearances on your screen.

For good worksheet documentation and just to help others who might be using this workbook and maybe even yourself at later date, adding a comment to a cell is very helpful. For example, maybe in this worksheet the number in cell G2, the June Sales expense is questionable. The person who handles this information, who gave it to you is named Joan. So, anybody else who uses this might want to check with Joan. So, you're going to add a comment here. You can press Shift+F2 or right-click and "Insert a Comment".

Usually, you'll see your name here followed by this and so we'll simply type in, for example, here, "Check with Joan to see if this is correct". Your comment can be quite long too. As we click away from this, we see a red triangle in the cell. Slide back over it, we see it again. So, that's your visual clue as to whether there is a comment in the cell. If you were to print this worksheet you wouldn't see the red triangle there. Now, there might be another one over here, maybe this number is questionable and you got that from a different person, so we could do something similar--this time I'll press Shift+F2--once again seeing the name and say, "Ask Max about this".

Now, obviously these are not major comments as we're using them here, but you can imagine how if you've got an unusual formula or you've got a questionable piece of data, putting in these kinds of comments is going to help in the long run. Documenting worksheets is something that a lot of people never do, and these are handy. Notice also that on the Review Tab you've got a choice called "Show All Comments". So, we do have this option, click this, and both comments appear. We might want to move these borders around and while they're visible we can do that.

We can also shrink the edges if we put the mouse on the corner or the sides, we can drag to make them smaller if we wanted to do that sort of thing; that's not really critical, but you can do that too. When it comes to printing, there's a way to print these as they appear here or we can have them all stacked up on a separate page. That's particularly helpful when you have many of these. So, we do have a control over these. If we don't want to show them all, we can turn off that feature, but maybe we want one of them to show for a while and not the other. For example, this one, we'll right-click on this one and Show/ Hide Comments, but that's just for this one.

So, you do have the option of displaying one or both or many as the case maybe in different ways, and simply, if we have one of them showing and we don't want it to show anymore, we'll right- click here and "hide the comment". Once again, we've got red triangles on the two to alert us to these. Now, I have seen this feature used too much, I got a worksheet once and therewere over 500 of these, it just kind of drove me crazy, but I think here and there you want to use these. It does provide good worksheet documentation; it helps others and you understand what's going on in a worksheet.

If this is a brand-new workbook, the name at the top of the screen is likely to be Book1, Book2 something like that. We do need to save our work from time to time. Keep in mind that the entries you might have made here, although they are being stored in RAM--that's working memory--they're really not being stored permanently and you certainly do want to save this information as a file; but the question will surely come up as you click the File Tab in the ribbon, "Should I do a Save or Save As, what's the difference"? Save As is always safer, it's a bit longer.

It does give you a choice as to where you will save the file, what name you will give it, and whether you need to save it in a different format. Now, you might want to save it on the SkyDrive if you're using that capability; you might want to save it on this computer; and your computer right now might have your Flash drive plugged into it. Let's go to Browse here. Maybe in my case here, I simply want to save this file--which was already created-- I really want to save it in its current location.

Now, if this were a brand-new file and this says Book1, I'd probably want to give it a different name, or even now, if I want to use a different name here, I can type in a different name as I choose--up to 255 characters. Do stay away from many of the special characters, you know hyphens and underscores work okay, but a lot of other symbols such as asterisks and slashes, you probably don't want to use. If you want to change the name or if it's a brand-new file, you want to give it a name. That's certainly important. If it's a second, third time, fourth time, often you're not changing the name at all, you just give it a quick look that looks great.

Where are you saving it? If you're using Windows 8, you're screen is likely to look the way it is here. Windows 7, probably fairly similar, but a little different. But you'll have choices on the desktop possibly; some other location; on this computer; on the hard drive; or if you've got Flash drive plugged in--certainly other places to save it--you'll make that choice when necessary. A third choice, occasionally necessary and sometimes really important, "Save As Type". If the file you're saving is likely to be used by some other people who don't have one of the more recent versions of Excel, maybe you want to send this to a friend who is using Excel 2003, you want to be sure that this workbook is saved in the format that that person can use it in.

By clicking the drop arrow here in the panel next to Save As Type, you might want to make the choice Excel 97-2003 Workbook. There are certainly some other choices out here that from time to time you may want to explore. In this case let's say, we don't really need that, but we certainly could in some cases. Choosing Excel Workbook, simply reaffirming what the choice is here, is likely to be your more common choice. In fact, you won't even think of it as a choice, you'll simply ignore it, eventually just click Save or click OK here as we complete this.

Anytime, there's any doubt about where a file needs to be saved or what its name is or what file type you want to store it in, go to Save As. Now, as we go back into Excel, imagine if we've made some more changes, maybe we're going to put in some formulas here. Imagine if we've done that, we've made some more changes. What do we need to do from time to time?We need to save our workbook all over again, but it certainly doesn't have to be that extended series of commands. The "File Save" button--likely to be that first button here in your QuickAccess Toolbar--although it means update, doesn't say that, Save simply means--if we were to click this right now--update this workbook to include our most recent changes.

So, you want to do that from time to time just to make sure that what you're seeing on the screen and what you've got saved match up. If there's any doubt about saving files, you're not sure exactly with regard to the file name, its location, or its file type, choose the command by way of the File Tab-- Save As.

In Excel, writing formulas is probably one of the more important things that you do. In fact, in some worksheets they're just loaded with formulas and also with functions as we'll see. In this particular worksheet called "Formulas". It's in the workbook 03-- Creating Formulas and Functions. We need to calculate some answers in row 4. The numbers here are pretty easy,we can do the math in our heads. This is going to be a 20, this is going to be a 30, and so on.We're going to be subtracting these numbers, but what is unusual, at least at first when you work with formulas, is we will not be typing in or using the exact entries 120, 420 or 100.

We won't be typing those at all. We do this by location. What we're really about to say is "whatever is here minus whatever is there". By implication we're saying that B4 is equal to the difference of those two cells. So, we don't type the B4, it's implied, we type "equal" to begin a formula in Excel. We can type in the B2 minus B3. That's certainly a common way of entering formulas. There is this color coding that takes place as well. When we press Enter, one of the Arrow Keys or Tab, the formula is complete and we have our answer; but the formula gives us the added advantage of, if either of these two cells changes, then our formula reacts and we get a different answer.

In a certain sense, this cell doesn't really contain 20, it contains a formula. When you click on cells--keep an eye on the Formula Bar--it reminds you what the cell contains. If you happen to double-click a cell-- sometimes you'll need to do this to change the formula-- it displays the formula in a color-coded way. Now, if the Overhead number was incorrect and we find out that the Expenses here were really 125, we'll make that change, but we don't have to rewrite ourformula, it reacts immediately giving us the correct answer here.

I'm going to undo that with Ctrl+Z. Let's talk about two other ways to enter a formula. Once again, beginning with the equal sign, but this time using Arrow Keys to get to the cells we need. I'm going to press the Up Arrow Key twice. Now, press the minus key on the keyboard and then the Up Arrow Key once-- indicate B3--once again, Enter, same formula, same result.It's just a question of saving a little bit of time by not having to type in addresses. A third way might involve two hands, it might sound like it's going to take longer, but it can be pretty fast as well.

It begins as you would expect--equal sign-- I'm going to click on B2, then minus on the keyboard, then click cell B3, and then Enter. Here too, same result as before, same formula. In cell H2, we need a total of these six cells right here. If we haven't seen much of Excel formulas or other techniques, we're going to be typing =b2+c2+d2, I'm going stop there.

This method will work but I got to put in three more locations. What would happen if this is 12 months of data? Three years of data? This is not the best way, although, it would give us a correct answer ultimately. Let's show a better way. Built-in to Excel is a feature called "Functions" and there are over 400 of them. "Quick capsule description" is a function--is a shortcut for a formula. They can often be a lot more than that. Probably, the most commonly used function by most Excel users is the one called SUM.

Functions begin with the equal sign also, so it indicates they are in the family of formulas.SUM, it's followed by parentheses and within the parentheses we're either going to see a range of cells across a row, possibly down the column or possibly across many, many cells. We can even have commas in different cells located in different locations. But in this example here, we simply want to select the six cells to the left-- and you can click and drag rightward or click and drag leftward, doesn't make any difference--so I've got the mouse right here in G2, hold down the Left Mouse button, drag leftward, there we go, let go of the mouse and we're done, except for pressing Enter.

We don't need to type that right parenthesis, but it would be there anyway--"Enter". That's a function. That's a correct answer. Double-clicking it simply redisplays it and we can also see it of course in the Formula Bar. Add up all the cells, B2 through G2. Now, for an average here, we need to add up those first six months and divide by six, but we already have the total here, so we don't have to really do the addition here. The average begins with equal and here's the amount that's totaled right there on H2, so you can type it or click on it.

Slash for division. You cannot use the slash that goes in the other direction. You'll see the slash on larger keyboards on the number pad, on most standard keyboards it tends to be above the Enter Key. Divide by 6--Enter"-- we've got our average here. Earlier we saw how to use a function here to tabulate totals and before that we saw a simple subtraction of cells right here. Of course, you will see in some formulas a plus sign for addition. The other symbol that we haven't seen just yet is the asterisk which is used for multiplication, but there's no question that formulas are critical to the operation of many, many Excel worksheets.

In this worksheet CopyFormulas, in the workbook 03-Creating Formulas and Functions, we've got a formula in cell B4 and we need to have that same kind of formula in cell C4, D4, all the way over into G4. Now, if we talk about copying a formula, you might say, "well, we don't wantthis exact formula to be copied into column C, because we would get the same answer, we get a 20". In column C we want to subtract these two cells. Many, many times when you've written a formula in Excel, you need to copy it across a row into adjacent cells or in some cases down a column.

What we would like to see here of course is the answer 30 and the answer 50 over here and so on. We need to copy a formula. Fortunately, the way that Excel copies formulas is that it really copies the relationship and that's an unusual way of saying it, but in this formula right here, a different way of phrasing it is, this formula subtracts the two cells above it--top cell minus the cell below it. Do we want to do same thing here? Of course we do, this cell minus this one.When we copy a formula, we're gong to be repeating the same kind of relationship.

Now, the are various methods for copying data including formulas, but surely the best way to copy a formula into adjacent cells is to use the so-called Fill Handle in the lower right-hand corner. This cell that contains a formula that subtracts the two cells above it can be copied rightward, simply by clicking and dragging this fill handle to the right. As we let go, you certainly see correct answers and of course we want to check these out just to make sure in C4, what does our formula say, double-clicking, I can certainly see that's doing the right thing.

How about column E over here? Double click, that's exactly what we want it to say and so on, every one of these. Copying formulas really means copying the relationships between cells. In cell H2, we've got a total using a function, the SUM function. It tabulates the six cells to its left and we want to do the same thing in cell H2 for the "Overhead Expenses" and in cell H4 for the "Profits". Here too, we can use the Fill Handle, drag the formula--the function in H2-- downward into these two cells to get those answers.

Our Average is a calculation in cell I2 and double-clicking and looking at it we see that it's dividing the cell to its left by six and we want to do the same thing in the two cells below this.Here too, we'll drag from the lower right-hand corner. There are many, many situations in Excel where you write a single formula and then copy it into adjacent cells by using this Fill technique.We've seen it initially with a row and then two examples used with a column.

In this worksheet YTD and Pct Increase, we want to calculate the Year-To-Date Profit. Now, we could do this with Sales or Overhead. We've just chosen to do this with Profits here. We'd like to have a running total month by month of how much profit we've made so far this year. In cell B5, we want to put in the January entry. Let's also be thinking ahead what we want to do in February and March. One thought, as we look ahead here might be, when it comes to February, we willwant to add those two cells. When it comes to March, we will want to add these three, in April these four and so on.

That sounds like it might be somewhat complex, but let's approach it this way. What if we figure out how to put in our January Year-To-Date profit, then we can go to February and add these two cells. So a Year-To-Date calculation is relatively straightforward, but maybe a little tricky at first.First of all, let's keep in mind the following idea. Our January profit so far is 20. Maybe these are dollars or thousands of dollars, depends on the size of the operation, but our Year-To-Date profit should always be the same as our January profit.

We're not going to type in a 20 here because what if these numbers change? This number will change. Do we need to write a formula here that subtracts these two? Well, we could do that, but why don't we just say automatically, this cell here is always equal to the profit. In other words, equal B4, no matter what. If we change the Overhead to 95, Enter, these two numbers both change. This is always equal to the one above it. Let me undo that with Ctrl+Z. How about February? Earlier we had suggested we could add these two cells to get our cumulative profit--20 + 30 is 50.

We could also add these two-- 20 + 30 is 50--so what's best? Well, look ahead to March. In March, will we be adding just these two cells to get our Year-To-Date profit? It'll make more sense to take the March entry and add it on to the previous year-to-date, provided this Year-To-Date is the total of these two cells. So let's write a formula here, equal. Now, we can add these two cells in any order we want. It's either going to be C4 plus B5 or the reverse, B5 plus C4.

Remember you can click on cells if you wish-- do it this way--that's an answer. One way to express this formula is: it's the cell above, plus the cell to its left. Will that makes sense over here in March? It surely will. The cell above added to the cell to its left. The formula for February here is the one that we want to copy all the way across. We'll simply drag the corner here into June. A quick check is that our June Year-To -Date profit is the same as the total Profit that we have here.

Writing a simple formula like this ultimately is simple and straightforward, although initially, it might be a little tricky. I'm not saying in any particular way that this formula is needed by everybody, but it certainly is commonly used and it does bring out and emphasize the idea that cell references are really important when you build formulas in Excel.

In this worksheet we might want to calculate how much our Profits have changed month by month. We can certainly do this with the Overhead numbers and also of course with the Sales too. Let's say we want to focus on Profits. Now, is it always going to be an increase? It so happen that this day it is going upward, but not always, so maybe that wording there is a little bit optimistic; perhaps a better heading here might be "%ProfitChange". We're not going to have any number whatsoever in the January column, but in February we will, because this involves comparing what's happened over a two-month period.

How much has the Profit percent gone up, we hope, or possibly gone down over these two months? It has gone up. How do we make this calculation? This may or may not be bringing back fond memories of high school math. You probably learned that back then, but do you use this capability all the time? Maybe not. It's relatively straightforward formula, but it does bring up a major issue with Excel formulas. Let's talk about how we do this. To calculate the %ProfitChange, we need to first figure out how much change has occurred.

We need to subtract these two and then divide by the starting point. In other words, the difference is 10, we'll divide it by 20, this represents 50% growth and that's what we hope to see in our Formula here. Let's do the subtraction first, equal the February entry minus the January entry, and then divide that by the January entry. We're expecting to see 50% or .5, something like that, and it comes as quite a shock when you see a number like this.

We're getting to the heart of the issue of what happens in Excel when you write a formula?What does Excel do first? If we're thinking standard calculators, well, we do the addition first, then the division, but in Excel that's not the case. Excel refers to what's called a "hierarchy of operations". What you see in column A, starting in row 8, is not something you're likely to see on your screen, but it does represent the hierarchy of actions. In performing this calculation, Excel, first of all looks down the list-- what's the first symbol in the list that's in the formula?Division, so this happens first, B4 divided by B4 is one, what's C4 equal to? That's 30, 30 minus 1, that's why we get the 29.

What we want to have happened first is the subtraction, so we put it in parentheses. And of all the mistakes you might make in writing Excel formulas, this is certainly one of the most common. You either forget to use parentheses or possibly you use them in the wrong order. By entering the formula this way, Excel in effect is saying, "All right, I see parentheses, I'll go there and do what's inside of them first, then I'll get to the division later". Now, we will get the subtraction done first, that's 10 divide by B4 which is 20, .5 is our answer, that's what we would expect.

On the Home Tab to make this even better, let's use the % button, it's found in the number group here to display 50%. This formula deals with the two cells up in row four in that order.We would expect this formula to deal with these two cells and this formula with these two cells and so on. As we drag this rightward in the column June, we get our answers. Keep in mind these are not always positive, they are in this case, so far, but if our April Overhead number gets adjusted to be 240, that's certainly not a positive number.

If your Profits go from 50 down to 10, they're down by 80%, so that's going to happen from time to time; but the main idea here is to recognize that when we are writing formulas--I almost want to say that whenever you have a mistake in a formula where you know the answer is dead wrong--first thing to look for is missing parentheses or misuse of parentheses. Excel operates on what's called the hierarchy of operations. Profit Change as it would be properly labeled here, is just an example of that issue and it's going to come up from time to time as you work with Excel formulas.

In this worksheet called "Absolute", we're about to put a formula in column F. We're going to create New Salary simply by giving everybody a $2000 salary increase. Now, a formula like this involves a standard Excel technique. If I type = and click cell E3+2000, and after typing this entry, you would want to copy this down the column. If you work with Excel formulas a bit, you know Excel will do exactly the right thing here. In other words, if we complete the formula and drag from the lower right-hand corner--let's just check it out on a few cells-- Excel is not using E3 over and over and over again.

It's surely adjusting to using E4 and as I double-click on these, E5 and E6 and so on, for as far down as we might copy this. That's called a Relative Reference. It's the most common kind of reference in Excel--a cell reference that is relative. If we copy the formula into different rows, it adjusts the Row Reference of a formula. Let's take a different situation here where we want everybody's salary to go up by a certain percent, maybe this 2.1%. So a formula here--and there are certainly a few different ways to write this-- might be E3, times--using the asterisk--E3*, this percent.

Now, if we were to press Enter now, we would simply have the amount of the increase, so we need to add on to that initial Salary. The New Salary for this person is going to be 55,696. It's simply calculating the amount of the increase and adding that onto the existing salary. We would want to copy this down the column. Again, testing it out on a few cells makes sense. As we do this, we almost immediately recognize that only the very first person is getting the increase. What happens in the next entry here? The E4 references are correct, but the reference to H2 has now slipped down into H3.

You can probably guess what's happening down below here. Down here, it's referring to H5 and down here, as I double-click, it's referring to H6. In all of these examples, we always want the reference to be to cell H2, the percentage of increase for everybody. We don't want H2 to change. There are two ways to make a change here. Neither of them is really intuitive. We need to put dollar signs in front of the 2 and in front of the H. Why dollar sign? That's the rule in Excel. We need some symbol here to indicate, we do not want this to change.

To make it a little bit faster, you can click after the H2 or in front of it or between the two, it doesn't make any difference. Press the function key F4. Now, had we been doing this from the beginning, right after putting in the H2, we would have pressed the function key F4. Now, the dollar signs have nothing to do with salary. That's just a coincidence. The dollar signs mean, if we copy this formula, the reference to H2 stays the same, exactly. That's what we wanted to have happen.

As we complete the entry here and copy it down again--just a few cells--check it out, all are getting their increases. What does this formula say here, for example? It refers to H2, and so do the other ones. You quickly get used to this idea. When you see dollar signs in formula, you're not necessarily thinking salaries, although in this case it is. It's an indication that the cell, if copied, the reference to this cell will not change. Now, there are situations where you've got a dollar sign in front of the row or just the column--those are usually a bit more sophisticated we won't go into those-- those are sometimes called Mixed References.

In the example here, and we might have thousands of salaries, we want to make sure that every salary increase here is based on the Absolute Reference to cell H2. On the bottom one here, we can just double-click the bottom edge and copy that down to the end of the column.That might be thousands of rows deep here; all based on that single cell H2 and the cells over in column E, using what's called an Absolute Reference. It's an absolutely indispensable feature in Excel; almost everybody needs it at one time or another.

Two of the most widely used calculating tools we need in Excel are totaling and averaging andwe've got functions for those. Reminder, it's "Sum" for totals, "Average" for averaging. In cell G3, we need a total here, but rather than using the Sum function, let's use something called the AutoSum tool. It's found two places in the ribbon. On the Home Tab, you'll find it way off to the right, right here in the Editing Group. It's also found, as you might expect, on the Formulas Tab in the ribbon.

Here it's off to the left-hand side. In both cases there's a little drop arrow associated with it as well. There is also a keystroke shortcut, Alt+=. If we want a total in cell G3 of the adjacent cells to the left, we can click the AutoSum button and see what the AutoSum tool is about to do. It's about to add up the cells to our left. That looks good. We'll press Enter. The AutoSum button is designed to look at data both upward and to the left to tabulate totals. Now, we can do this slightly faster though.

We don't have to pause each time we're looking at this. I'm going to press Ctrl+Z to undo.Another way to do this is to click "AutoSum pause", just like a half a second and click it again, something like that. It makes it a bit faster. A double-click doesn't quite work, but that was clicking twice, just a slight pause between the two clicks. If we want a total on this column, we could highlight the cells ahead of time and then press AutoSum once to get our total below. If we're in cell H7 and we want to add the data from above or maybe we want to add the data from the left.

What's Excel going to do? Because AutoSum is designed only to add from above or from the left, what does it do in a case like this? If we click AutoSum, AutoSum always looks upward for data first. Now, if we truly want to add those three cells to the left, we will intervene and simply click and drag across those cells and then press Enter. If we want totals right here in these cells, we can highlight them ahead of time and click AutoSum once; or similarly, if we wanted totals here, highlight these cells ahead of time, click AutoSum once.

Better yet, as I undo both of these, what if ahead of time we knew that we wanted totals on the right and below? Highlight both of these and then press AutoSum. Now, there could be times that you want to do more than just adding and averaging. Suppose instead of total here, you wanted to find the maximum. I'll just put in "Max" for now. AutoSum has a drop arrow, in either of its locations, click the drop arrow to the right of AutoSum in this location or below it in the other location, and this time we'll choose "Max", because it's in the list here--choose "Max"--here we go, and Enter. So that's the maximum number in that range of cells.

We can get to those capabilities too. And sure enough we can do the same thing with "Minimum". So at different times, we can use different features available from the drop arrow associated with AutoSum. Going back to the data over here--as I delete this, and delete this--if we wanted averages on the perimeter here, we'd highlight the cells this way, then use the drop arrow for AutoSum and choose "Average", to get our averages on the perimeter here. This tool is extremely handy and there are so many times when you need to add data or average it; and in some cases, finding the minimum, finding the maximum--take advantage of the AutoSum button in either of its locations and this drop arrow to the right.

With over 400 functions in Excel, you may be wondering "how do you get a handle on them, how do you know what to look for? We're going to give you a few examples on this functions worksheet, which is found in the Workbook 03- Creating Formulas and Functions. When you do have that extra moment, if you go to the Formulas tab in the ribbon, recognize that there are some groupings of functions here. Now, depending upon what it is you're doing, maybe you'll click on Math & Trig and begin to see some of the many functions here. Most of us don't have the time to do this, maybe not even the inclination, but if you do pause and hover over one of these, you do get a description of them.

Sure enough, some of these names are going to jump out at you, something about degrees maybe. Converts radiance to degrees that may or may not be useful to you. Here's somethingcalled Fact, which opens us to factorials which is something you may have heard of. Logarithms-- those are built-in there. It sounds like maybe we're in some big fishing expedition maybe, but, based on the work that you do, maybe you do work with financial functions or financial data, and here's a financial group over here. Sure enough, some of these are likely to have relevance to what you do.

Here's a nice one here for calculating mortgage payments, that sort of thing. There are tons of functions. You just don't always know where to begin. Date & Time functions can be pretty interesting for a lot of Excel users as well. There's one in here for picking out the day of the week called Weekday. There's another one for calculating monthly differences, differences between months or so many months out. There's an EDATE reference right there, so many months before or after; there's an EOMONTH. This just goes on and on and on. Let's talk about a couple of functions that you might not have heard about, you wouldn't necessarily need, but on the other hand, I think they have a lot of relevance to certain kinds of lists.

Here's a simple one here. We simply want to count how many names are in column A. It might be a huge list, it might not be. We can count the number of cells that have data in them. That function is called Count A. Now, anytime you type equal and then a letter, you'd see all the functions that begin with that name. Now, I could certainly type this faster than finding it, but nevertheless, if you do find it, for example, "Count"--we see it in there--Count A right here, thisparticular function counts the number of cells on a range that are not empty.

Rather than typing it, we can press Tab right now and use that. Where are we looking? Maybe we're looking in Column A. By the way, it will ignore the icon, the image that's in cell A1, sitting on top of cell A1. People ignore that. If we want to know how many cells have data here, we are counting cell A2 as well. If the question is, "do we want to count how many people work here?" Well, we'd want to subtract 1 from this, but if it's simply a count of how many cells have data, there is our answer, it's 13. If we wanted to change that wording to the right, let's say number of people, then we'd subtract 1 here.

If we want to count the cells that have numbers, how many different salaries do we have here, it's almost the same, but it's called Count. Here, we'll just click Column E. By the way, referring to an entire column often makes sense. If there's nothing else in this column--now there is text in cell E2, but there's nothing else there--and we're trying to count the number cells--we shouldget sensible answer here-- there are 12 of them. In this list here, we're dragging downward to see that. By the way, when you drag downward, you will see in the upper left corner to the left of the formula bar, the indicator up there, and you don't see it at the moment--but as I highlight this again, keep an eye on this area--the name box, to the left of the formula bar here, will indicate how many cells I'm highlighting, and it does it in the following style that says, "12Rx1C"--meaning 12 rows by one column.

There are 12 cells here that have numbers in them and here's the formula again. Using the Count function, count the number of cells in Column E that have numbers in them. Now, Median is a commonly used number when dealing with certain kinds of entries. Earlier, you might have seen how on the Formulas tab, you can use the drop arrow for AutoSum to get to a Max or a Min, but we don't see Median in here, so it might be faster in this case to actually type in the name of that function. How do you know there's a Median function? You happened to discover it.

It's probably a good guess, because that's a commonly used statistical measure. The Median salary, say for the New Salaries in column F, What's the Median Salary? There it is right there.Is it different than the average? Well, we could calculate the average, just as easily, and let me move this down actually. Let's do an average as well right here. This time we'll use the AutoSum drop arrow, choose Average, and we don't want that average, but we want the average of column F, and Enter. Of course it will make sense to format both of these and compare them.

Go to the Home Tab. Click the comma button, good enough for now, the Median and also the Average. Now, finding this 2nd Largest entry, it may or may not be that important--it wasn't to me until a few years ago when I needed it a couple of times--and I thought you'd do something like a "max" or "sort the data", but there is a function way, and it's called Large. I don't think you'd exactly guess that. Large says where are we looking? We're looking in column F, comma. The 2nd highest, put in the two, third highest, put in the three, and so on.

What's the second highest salary there? 76,728. It doesn't tell us where it is, but it tells us that it exist in here. Here's an 81,000, so that's the highest. It looks like somewhere in there, we've got a 76,000, there it is right there, that's the second highest. Now, if you're familiar with statistics, you would know about standard deviation. Now, do you make a guess here? Do you start typing? Maybe. Perhaps better-- go to the Formulas Tab. Strange enough, you don't see statistical here at first, but if you go to More functions, you do see statistical and quite a few entries here.

You could probably guess--and guess correctly--if it's going to be standard deviation it begins with the letter "S", probably ST. As it turns out, there's a cluster of them here, and you just have to figure out which one suits your needs best. For those of you who have used this, you know--you have some sense of what it does-- it indicates how much variance there is between the average and each of the entries here. It does it in a waited kind of way. Maybe it's this one, maybe it's this one. You'll know which one to use. That example here, it actually will prompt you into highlighting the data, for example, column F, and we'll click OK and get our answer.

Now, if you're familiar with standard deviation and how that works, that's a meaningful value.It's hard to come up with a master list of the functions that you need or that I need or the next person needs, because so many different people use Excel in so many different ways; but the Formulas Tab, at least opens you up to some of the major categories, gives you some ideas and as you work with these more and more, you'll have a better sense of which of these functions is likely to be used. There's just a ton of them. Don't get overwhelmed, but there's no question, they're going to save you a great deal of time as you work with Excel.

On the Home Tab in the Ribbon, one of the most prominently selected group of icons is the Font Group right here. These involve changes that we might want to make to our worksheet, to make data standout a little more prominently or give greater emphasis to a title or some of our row headings, for example. Certainly, one way to get to this might be, for example, the title we have on this worksheet called Fonts, in this workbook 04-Formatting. Let's change this cell D1.The text extends into the other cells, but we could easily change the font type itself.

Calibri is the most commonly used font in Excel in recent versions, but as we slide over these other choices, we can see how they might look. There are so many choices here that if you're a bit picky, you might spend a lot of time looking at some of these. You can also use the mouse wheel to scroll up and down, slide over this one, slide over that one, maybe make a choice, maybe not. Some would be, perhaps totally inappropriate for workplace usage--others look pretty interesting, maybe they do--just a ton of choices. How does that one look, and so on? At some point, maybe you'll say, "okay I'll try this one or that one".

Of course, what else might we do with this? A fast way to get here is by right- clicking because that activates the mini toolbar. Nearly, all the buttons we see up there in the Font group on the Home tab are also shown here in the mini toolbar. So maybe we'll change the Size of the fontthis way, or that way maybe. While we're at it, color background for the cell, and of course, not all these are going to be great choices. Bold, it's already bold, perhaps, italic, slanted, and so on. Lots of choices here, by the way of the font group, or by right-clicking in the mini toolbar.

Now, there could be a feature that maybe you just don't see there, but you suspect it's available. You perhaps have seen subscript or superscript. Sometimes, the way we need to get to features to adjust the font are by way of the little arrow on the corner here, the so-called Dialog Box Launcher. If, for example here, if we wanted to use--maybe in a different situationhere--like how do we get to the two, how do we write the little two as a superscript here? In this example, let me zoom in on it, and I will use the zoom slider in the lower right-hand corner on the status bar to zoom in on the data here.

If we were typing this, we would type it, at least initially this way, "e=mc2". Highlight the 2 now, by clicking the Dialog Box Launcher. And there are two other ways to get here by the way, one is Ctrl+1-- it activates the Format Cells Dialog Box-- just like that arrow does there. This leads us into some other capabilities here. There aren't a whole lot more than what we actually see on the Home Tab, and certainly superscript is one of them here, and that's the one we might want to use in this case here; so now the 2 is a superscript.

Sure enough, same idea here with the subscript H20, you'll just type it as H20, then select the 2 and jump back in here. Once again, H2O, highlight the 2, this time a little bit different, we'll press Ctrl+1-- get there a little bit faster--and we'll try subscript this time to come up with H2O.There are some unusual underlining choices available with fonts too that you don't see in the font group here. Those too, you can get to with that Dialog Box Launcher or Ctrl+1.

One of the methods of getting into those choices--let's imagine we might want to make some font changes here-- we can right-click and go immediately into Format Cells. This doesn't always take us to the Font Tab, although often it will. Sometimes, you'll find yourself at the Alignment Tab, but you can easily make the switch there. Maybe we're considering here some underlining changes that we don't see on the Home Tab, so we've got some others up there as well; maybe double counting or something that appeals to us. Click OK and maybe it is or maybe it isn't, but we can get to those features as well.

There are lots of different ways and reasons for wanting to change the font, and again, some of them are quick and easy like Bold, Italic, Underline. We can get to them this way or earlier, we saw how by right-clicking you activate the mini toolbar to get to those features even faster.

You can easily adjust the width of columns and the height of rows whenever needed. You're much more likely to need these with columns. Rather than using commands, although you can, it's much, much easier to use the actual column separators. If I want to make column B wider, I'll point the mouse on the boundary between B and C, where the actual letters are, click and drag to make it wider or in some cases narrower. If you make a column too narrow for the numbers to be displayed properly, you will see pound signs. A better solution sometimes is simply to double-click a boundary, and that means in effect, make the column wide enough to handle the widest entry; so we'll double-click.

Now, if at a later time, if we happen to put a word here or a long number, maybe I'll put in a long word like this, and press Enter. The column width doesn't adjust, but if I double-click the boundary between B and C, it certainly does. I misspelled it anyway, but if I take that out, what happens? The column doesn't get any narrower, so we'll double-click. With numbers, you're less likely to need that. Now, how wide is a column and do we really care? If we put the mouse pointer on a column boundary and hold down the left mouse button, we see its width with some number and then the term pixels--this is 64 pixels wide and so is this one.

That's a number hardly worth remembering, but it does at least inform us that the two columns were of the same width. Sometimes, we do keep an eye on that a little bit. Now, recognize if I changed this June entry from $980 to $1000, that's going to require the use of a comma, and so this will take up more space. What happens when I type $1000 here? Enter. The column grows automatically. The other columns here, like the one on the left, 64 pixels wide, this one is 75 pixels wide, but we don't worry about that usually.

If somehow or the other, you wanted all this to be the same width, you could drag across these columns here, take any of the boundaries, drag it to that width, 75 pixels, something like that. Here we go. Now, they're all exactly the same. If on the other hand, if you said "I want each once of these to be wide enough to handle the widest entries", in other words, let's make them all be "best fit". Let's drag across all these maybe, even on the column I--double-click aboundary--and every column is wide enough and just wide enough to handle the widest entry.

Now, you can certainly get to these features as well by going on the Home Tab to the cells group format but again this takes us in the territory which usually is no more efficient; it just takes longer to use the feature. It's much easier to adjust these by dragging these boundaries or by double-clicking. You can adjust non-adjacent columns. I could click column B and then with the control key, click column I to adjust both of those widths at the same time. When it comes to row heights, often this is automatic. For example, if I click in cell A1 and I want to use a larger font, in the font group on the Home Tab, I'll click the drop arrow, and as I slide over these numbers, you see what's happening to the row height.

It's happening automatically. Occasionally, you might want to change row heights, we could easily do that. Let's change the height of these rows here. What do we do? Drag any of these boundaries, it doesn't make any difference which one, any boundary between the numbers, make it a little bit taller maybe. We've made all of those taller at the same time. You're much less likely to want to do that or need to do that, but you certainly can. It's easy to adjust the column widths and row heights, much more likely with column widths, but it's a feature that works best by using the boundaries of the columns, rather than using the actual commands in the ribbon.

On the Home Tab in the ribbon, there is an alignment group with a variety of tools for allowing you to line up text in different ways within cells. Here's a look you may or may not like. I'm going to highlight the cells in row 3 and using this icon right here for Orientation, click the drop arrow and maybe angle the data counterclockwise, maybe gives it a little bit of flare or possibly exploring these some more and clicking the arrow. How about Rotating the Text Up? How does that look? Could be interesting maybe. We could adjust the row height eventually too, if we wish, so that makes sense sometimes.

If we want to change back here, we can simply rotate the text up again and actually that turns it back to normal or possibly could have done an undo there too. There are times when you've got data in a cell and you actually want to wrap the data, so that it appears as if you've got two rows of data in the same cell. We could certainly make column A wider, but on the other hand we might want it narrower. Let's apply what's called Wrap Text to the data here, for example.This may cause some unsatisfactory appearances. We might eventually change our minds and readjust the column width.

By going to Wrap Text, what we're seeing here is "wrap the text". In other words, if it won't fit across the column width, then put it in the same cell but underneath the data this way. I think in the example here, these aren't great choices, but do recognize this, if you were in a different worksheet-- let's imagine that we were for the moment-- if you're going to put in an entry on the column, maybe you want to put in 2013 Salary and you're envisioning the column not needing to be very wide. If you want the word Salary to appear under 2013, you can press Alt+Enter and then type Salary and then Enter.

It automatically stacks up the data that way. By pressing Alt+Enter, you were controlling where the wrap occurs. Another example could be--we'll just use this again--imagine that we're in a totally different worksheet here. If I were to type in 2013 and then Alt+ Enter and put in Tax, and then Alt+Enter and then Rate, if this is a column, it only is going to have letters or a fewnumbers in them, that would give us the opportunity to make the column maybe a lot narrower, if we had that kind of data in it. Recognize that you can also wrap texts, simply by pressing Alt+Enter to force a line break.

Now, at times we have data like this that's a title, and this is in cell D1, and it might look fine there, it might look better though if it were in the middle of the cell--not middle left- right--but middle, meaning top-bottom. These three buttons here have to do with vertical alignment. This is currently bottom aligned, it might look better in the middle, so let's see it that way, where it might look better on top; so we can make those changes. Now, it might also make sense to center this across these cells.

The data itself is in D1, so what might we consider doing here? How about merge in the center? And that makes sense there too. We might want to do that with this as well, centering all the way across there-- that may or may not be a good choice-- but we could try it, Merge & Center. In this case, as in the case up here, we've essentially blended or merged all of these cells, A1 through I1 are now one big cell called A1, and there is really no B1 and C1 here. This is all one big cell here.

You might add color to it, something like that. We've centered this across columns A through I. In this example here, we've centered it across columns D through I. Keep in mind too, something really basic, we might want to take this data and center it and of course we've got these buttons here for Aligning Right, Center, and Left, maybe Center looks better there and maybe not, maybe lining it up on the right. From time to time, that might be the better choice.We've got easy tools here for realigning data, both horizontally here, vertically here.

Earlier we saw Orientation, Angling Text and Wrap Text, as well as Merge &Center. Various Alignment Tools we have at our fingertips, by way of the Alignment group on the Home tab.

Adding borders to a group of cells is another way to give emphasis to a particular part of a worksheet. In this worksheet here called Borders, highlighting these numbers here maybe isbeing done because, these are pure values, we want them to stand out a little bit differently than the other numbers. There are two ways to get to this capability. On the Home Tab, we can use the Border button right here. Click the drop arrow, tons of choices. We might just want a "thick box border", and there it is, but a little tricky to see while they're still highlighted.

Many, many times when you're applying border features, you want to click elsewhere and then see the effect of what occurred. Also of help here, particularly if you're using even lighter borders than this, you might want to turn off or inhibit the display of gridlines as you work with the data. Remember, that's independent of working with the data when you're printing it. On the View Tab, you can uncheck the box for Gridlines. That will just allow us to see these a little more clearly. Another possibility here, we can do this, maybe we want to highlight these forone particular reason or another, here too, we could, and this time, by way of right-click, use the mini pop-up toolbar right here.

We've got the same choices here and we'll just use outside Borders. That's tricky to see too, until we click outside of it, but there it is as well. Now, if you're interested in different kinds of borders, sometimes what you might want to do is right-click and go to Format cells or if you're on the Home tab, you can right-click either this button here for Alignment or Number--thatdoesn't sound exactly relevant, but you can use those--or you press Ctrl+1. Right clicking format cells Ctrl+1 or any of these buttons and I'll press Ctrl+1.

Any of those choices will take you to the Format Cells Dialog Box, and a border isn't selected, click it, and we got some choices here. The case could be that you wanted to use color. Here's a color choice. Maybe we want to use a dark red border or something like that. It could be a thick border, it could be one of these kinds of designs, and while we're here, we could have an Outline Border as well as an Inside Border, and there are even oddities like these here, which I think most of the time we are not interested in. Once again, OK to get that effect.

If that were not enough, there are other techniques here too, and obviously, it would be overkill for me to put borders on this part of the worksheet, but once again, on the Home tab, clicking the drop arrow, there's even a choice here for Drawing a Border or Drawing a Border Grid. Draw Border Grid, we'll just drag across these, like that. May be we don't' like the color, go back up here and pick a Line Color and so on. You could spend a lot of time out here, fine-tuning this to fit your needs. At times, you don't want any borders.

You want to get rid of them possibly or maybe these were sent to you and you don't like them.A quick fix here by way of a keystroke shortcut is to select the data in question, maybe even the whole worksheet, but let's say we get rid of--how about the lower ones here-- We don't want any borders there, it's Ctrl+Shift+Underscore. That will get rid of them. A more logical way would be, highlight the data or possibly by clicking the upper left hand corner, using the entire worksheet. Go to the button right here on the Font Tab and choose No Border, and all borders will be gone. But I think there's no question, that in some worksheets, putting a border around certain groups of cells does give greater emphasis to the data that you've selected.

Excel has a wealth of numeric formatting options, and two of the most common options are the dollar sign and the comma button. They're found in the Number Group on the Home Tab in the ribbon. Column F shows salaries. It's probably unnecessary to show the dollar sign, but we might want to. Notice that when you point to dollar sign, the pop-up tip says, "AccountingNumber Format" and that's what we see. If you make the column wider, the dollar sign stays on the left-hand side. You might or might not like that.

That's certainly one option. You could easily make the case for saying, "Well, these are salaries. We don't really need to see the dollar signs. It's pretty obvious, isn't it?" So we might want to use comma. This is not the opposite of dollar sign or accounting format. It simply doesn't display the dollar sign if we use comma. I wouldn't do this for the whole column. And we don't need to make the column that wide, we can make it narrower. If we don't want to see the pennies there, they're all zeros anyway, we could certainly make that column even narrower by not displaying pennies.

By the way, using these buttons to increase the display of decimals or decrease has no impact whatsoever on the actual content. Sometimes, people do make that mistake and if we did have pennies there, this would simply be hiding them and it would do visual rounding. Now, column G here, similar data, although this case does have negatives, what happens with accounting number format as we readjust the column width by double-clicking? Negatives appear in parentheses, here too, making the column wider and narrower.

The dollar signs are always on the left-hand side. There are other variations though. If we press the Dialog Box Launcher right here, or possibly press Ctrl+1, that will take us to the Format Cells dialog box. On the Number tab, we see Accounting format here. How about Currency format? Isn't that the same thing? It's going to be different in two respects. You want to keep an eye on the dollar signs here in column G as well as what the negatives look like. And I think you can see ahead of time, the negatives might be in red or they might be black in parentheses or they might just be red.

In other words, you make the call as to how you want this to look. Here we go. That's another variation. Notice on the whole numbers that are positive here, the dollar sign is right next to the number. If I were to change just this one-- watch Accounting Number Format-- shift the dollar sign that way. The objective of this is not to confuse but to have you recognize that, when youdo get data from other sources, you might get a mix or maybe you're getting data that's in one format or as you use the other. You want to come up with some kind of a standard.

I say by all means, if possible, if you like the dollar sign, Accounting Number Format, use it and stick with it, and maybe show the decimals or don't. But if you simply stick to one variation or another, you won't get too bogged down on these. Recognize too, when we have data like this, making the columns wider, sometimes we want the dollar sign to stay hugging the left-hand edge, sometimes we don't; and recognize here, that's accounting format in these two cases. The example here, this is simply the comma button choice with no decimals.

Now, with certain other kinds of data, for example, the data in column B, you may or may not use Social Security Numbers, but it looks like something happened here that's not quite complete. Whoever typed these entries here decided not to type the hyphens. Well, why not?Well, it's a good idea but not quite fulfilled. If you're going to use Social Security Numbers, take advantage of the fact that Excel has a built-in format, and rather than typing the hyphens, let Excel enter those by way of a format. So this time I'll right-click to get to it, remember, we could also press Ctrl+1 to get to it.

Format Cells, if you right-click. And this time, using the Number Tab, the category is "special" and there we are with the Social Security Number, click OK. Make the column wider in this case, and let me--using the zoom bar in the lower right-hand corner--zoom in a bit on that so we can see it even better. There we go. These are actually not here. They're not there in the Formula bar. If you double-click in the cell, you don't see them there. If you're making a change, maybe that should have been an eight, you don't see any hyphens.

Press Enter, they are there. They're in the format but not in the actual content. If these are supposed to be phone numbers in column C here, I want to take the whole column here, and format these in the same way that we did earlier; either by right-clicking and going to Format Cells or pressing Ctrl+1, Format Cells, and here, Category, Special. Same place we found Social Security Number. Now, we have phone number. Click OK, and that displays the numbers once we adjust the column width this way.

So, rather than typing 14 characters, you just type the numbers. So here too, if you're adjusting these, maybe that shouldn't have been an 805, maybe that was a 213, you want to double-click in here, change that to 213. Press Enter and you've adjusted this. So those are two built-in formats as well. At different times, you will want to change formats, but again, make it simple.Rely upon the buttons in the ribbon as much as possible. Remember, these are also accessible by way of the mini toolbar.

So, if we were to right-click here, we could go into the mini toolbar here and make our choice here of dollar sign or comma as well. And then there is the occasional use of adjusting, the positioning of the decimals as well. So, a variety of numeric formats, we can get to them easily by right-clicking or going in the format cells to adjust those displays of numbers.

Excel gives you a variety of ways to display Date and Times. In this worksheet called Date and Time, look at the data in columns B and columns C, both showing essentially the same kind of data, simply displayed differently. The data in column B, shows four- digit-year and column C, two-digit-year. Obviously, one takes up more space. Sometimes that's the issue, usually not. Is it clear to the audience, whoever is using this? Recognize also the data in column I is different too. The formatting in column I, does clarify which month it is.

Sometimes and particularly, if you're getting data from other countries, outside the United States, they tend to work with the layout of day, month, year, whereas in the U.S., we tend to use month, day, year. But if there's any doubt, column I tends to eliminate that by that display.Let's take a look at some of the built-in formats here. The standard display in most Excel versions is, as you type an entry, you see a four-digit-year, the way we're seeing these in column B. Let's imagine we might want to change the ones in column C here. If it's the entire column, we'll just right-click on the entire column, and one of the many ways we can get into formatting--Format Cells--after right-clicking.

In the Format Cells Dialog Box>Number tab>Date, and you've got to kind of put the pieces together a little bit, if you simply wanted to show month and day, that's the way we see it right here. We would use that display. Here's the one with the two-digit-year. Here is the one that uses leading zeros for months that are under 10 or days under 10 and other ways to display the data as well here. Quite a few variations as we look through the list, even spelling it out this way. You might even try this one for example. That certainly takes up more space, but it certainly eliminates any doubt as to what you mean by this.

And you could even go further with these. You can apply your own. Now, I often steer people away from the idea that you can actually create a custom format, but what I just did here was to right-click column C and choose Format Cells, and on the Date tab-- after having selected this, I'd just made the adjustment to-- I'm going to jump over to Custom. What I then might do in this display and I don't want to explain every single icon here, but I'm going to change this so that instead of simply four M's, a D, and three Y's, in front of this, I'm going to put four D's.

That actually will spell out day of the week. If I put in three D's and we put in the abbreviation, a comma and a space. Now, would I really care about a higher date as to what day of the week it is? Probably not. But in certain other kinds of data, I might, and this would allow us to see it clearly as we see here. So there are lots of variations on that. These do take up a lot of space, but for clarity, sometimes that make sense. There's only one keystroke shortcut associated with date entries, that's currently being used in column I. If we want to use that in column B,simply click column B and keystroke shortcut is Ctrl +Shift+#.

So, that displays the date information that way. Now, when it comes to times--we've got some columns over here with times-- the way these times are entered--and they're also set up to handle formulas too-- we use colons for time entries. One way to display times is the so- called 24-hour style, which is widely used throughout the world. Maybe a little bit less so in the United States, but even here we see these a lot; 24-hour time like this. The variation you might want to use here is, once again, by right-clicking, going in the Format Cells, on the Number tab>Time.

Maybe not obvious at first, but the choice for PM is right here. Now, this will show AM or PM as necessary. Click OK. So, we see that display and readjusting the column, it's possibly this way. We can display times also more coherently, could have done those both. There we are.We either use the AM or PM or the 24-hour style. If the numbers are coming to you this way and you're saying, "I want to change them all", we'll do the whole columns, here too.Remember, another way to get to Formatting Cells is Ctrl+1.

You can do that here, and the variation here, where we want to want not see AM, PM, is the choice 13:30. Click OK. If you entered times with colons, and previously in our examples, entered dates with slashes or hyphens, you do set the stage for using these in formulas andtaking advantage of Excel functions that relate to dates and times. So, it's a strong feature and the formatting capabilities certainly clarify the appearance of the data in the way that you want this data to appear.

In this worksheet called Conditional Formatting, we might want to make some of the years in column F standout a little more prominently. Conditional Formatting practically explains itself.We're saying we want certain numbers, those above a certain amount, within a certain range, to have a different look. And as we make the choice here, we've selected column F ahead of time, Conditional Formatting, highlight Cells Rules. How about those Greater Than? Greater than what? And immediately, we see some color changes in column F. We haven't evendecided what we want yet.

Format cells that greater than eight. Well, maybe we want to do this for those that are greater than nine in other words the 10's and above. Do we want to use light red fill and dark red text? Well, maybe so, that looks okay. We can see it already. We don't get a preview on these but we can imagine what some of these we might use. How about a red border maybe, something like that? Well, that looks okay. How about red text or whatever? And if these aren't good enough, create your own custom format. How about yellow fill? Anyway, click OK. You've made your choice.

It is dynamic, too. If we make an adjustment to the Hire Dates and one of these years slips below 10, it's not going to be highlighted anymore, or one goes above, the opposite effect, it will be highlighted. But there are other features, too, maybe on the salaries here. How about those above average? Conditional Formatting, we've got Top/ Bottom Rules here, and quite a few choices. We might want to highlight just the top 10% or the bottom 10 entries or top 10.How about those above average? There we go. And once again, a similar kind of selection, we may or may not like this capability here.

Let's just cancel this. We can certainly explore that option, too. Let's go back again to Conditional Formatting. How about Data Bars? What we see here is a different bar for each entry, and the width of the bar, more or less, conforms to the higher salaries, as we see this.Now it's a little bit difficult perhaps to read the numbers or read the bars depending upon how you're viewing this. But we can see what's happening here, a quick visual addendum to the data here to point out which of the salaries are higher. And there is by the way, as we'll see, a way to hide the numbers and just show the bars.

So, that's certainly an option there. A lot of choices here, too. And in a similar vein, how about Color Scales instead? What Excel does here is it divides the data into fifths or sixths or whatever here and then applies color here. It actually uses more than you might think at first, and some of the color shadings are very subtle. It's a bit hard to figure out the differentiation on some of these colored variations. But I think you can have a sense here. In the example, if I were to pick this one, the lowest salaries are dark green and the highest salaries are dark red. And the early one here, this is just the reverse of it-- higher salaries are dark green, the lower salaries are dark red.

So you've got some options here, too. All these selections here are dynamic and they do react to changing data. So you might want to try that, maybe better yet though. How about Icon Sets? This will divide our data into thirds with these choices here using different icon choices; or how about fourths this way or fifths even? And we've got different shapes, different choices here. We might want to use, say this scheme here--four arrows. Greens are the highest salaries, reds the low, the two yellows are in between.

If we don't want to see the salary, we have to come back and choosing Conditional Formatting, Manage the Rules. Now we're not deleting the cell, what we're doing is hiding the salary and we do this by way of editing the rule. So here's the rule in question, Edit the Rule. And there's a box, Show Icon Only. Click OK. So if you want to make a presentation of this data, click OK again, and not show the actual salary--but just give a rough idea of which range this falls into--we probably want to center this as well, this way then we have that.

Now, if you click a cell, you will see the Salary in the formula bar and it's still there. But nevertheless, sometimes this gives us the broad picture that's all we want for a certain presentation. And certainly with Job Rating, we could certainly use some of the schemes here, too. So it's a rich feature. It will take you a long time to explore a lot of the variations here. The three major ones, the ones that get the most attention are Data Bars, Color Scales and Icon Sets as we'd just seen in these examples here.

If you work with large lists of data or lists that you think are likely to become large, you might want to look into the feature called Tables. You can simplify working with data if you convert Data into a Table. This feature primarily gives you the visual coherence to a data, but also gives you some tabulating enhancements, and allows you to treat your data as an entity, and also, it gives you features that are ideal for dynamic data--lists that are going to grow. You can start this process if you have only a title row and one record stored at that point if you wish.

This is larger list here about 700 rows. Let's take a look at this Table feature. First of all, in this list there are no empty rows, there are no empty columns. There are some empty cells in Column G, and there could be some else where too, that's not the issue. We don't worry about that. But how do we convert this into a Table? On the Home Tab within the Styles Group, you'll see a choice called, Format as Table. Also, on the Insert Tab, we see a choice called, Table.Here, the description is a bit longer. Create a table to organize and analyze related data.

Tables make it easy to sort, filter, and format data within a sheet. Let's make the choice. Excel scopes out the data, give it a quick look there and make sure it's picking up all of your data. In this case it goes down to row 742, columns A through I. Click OK, and its pretty obvious there's a visual change here to the data. Every other row is blue. Furthermore, we've got a new ribbon called Table Tools with a Design Tab. Lots of features here related to, what we might want to do with this Table. Off to the right, we see Table Styles, click the drop arrow.

How about 61 different ways to format this table? We can slide over various choices here and decide which color we want. Maybe we'll change our minds later too. We'll just pick one of these. Another obvious visual difference to our data is that we see Filter arrows in each column. Now that could be a feature that you're not familiar with yet but if you find those arrows obtrusive, you could go into the Design Tab and simply uncheck the Filter button. Recognize something else-- as I start to scroll here, I'm using the mouse wheel-- keep an eye on row one and the column letters above it. What's happened? The column letters have disappeared and the Field names, the column headings now appear at the top.

It might be able disconcerting at first, but I think that's what you might want. Are the column letters that important to you? So as we scroll up and down, unless we're at the very top of the list, we don't see the column letters. The emphasis is on the data itself. Recognize too that in the Design Tab, we currently have the feature called, Banded Rows on. You might want to uncheck that. There's another choice, a similar choice called Banded Columns. Let's choose that. Maybe that's the look you prefer. Recognize here too that if you go into Table Styles and slide over the choices, you're seeing Banded Column, look right now, so that adjusted the fact that you've chosen Banded Columns.

You don't want to choose these together probably because it looks a little bit strange. I want to stick with Banded Rows. You might want to give special emphasis to column A. Usually, what that means is it will make it bold and sometimes, it will apply some colors as well, so I'm going to choose first column and see what's happened there. If that's a bit much, you might go back to Table Styles. Recognize that a number of the choices here do use a color in that firstcolumn but some don't. So pick the style that you like best. Now, in addition to this, and maybe again, you're not too familiar with Filtering, you might want to add something called Slicers.

I'm going to zoom back just a little bit here so we can make a room for these. This is a feature that you might be familiar with if you've used the Pivot Tables. On the Insert Tab, you'll see a choice called, Slicer. You can use a Slicer with Tables. You can't use it with the regular Excel Data, and you can use them with Pivot Tables as well. I'm going to use Slicer here. What do we see? The names of our fields, now here's what we're heading into, whether you are familiar with Filtering or not, you might want to see at a certain time, just the Full-Time people, or maybe just the Full and a Half-Time people, or maybe people from just certain Departments.

Let's choose Status and Departments, and we might later change our minds and come back and add some more fields-- we can do that at anytime--but by clicking OK now, we're going to see Slicer panels for Department and Status-- there they are, right there. That's larger than it needs to be, so I'll just grab the corner--do that sort of thing. With Department, we have got about 20 Departments or so here. Look in the ribbon. We've got a Slicer Tools ribbon with an Options Tab. We could show this as three columns and there are lots of other features here tomake this a bit wider and we can see those names better.

We don't really have to see them all just for now, but here's the idea--we've got our data here and whether you're familiar with Filtering or not, that's not the issue--but if you say, for example, "I want to see just the Full-Time people", in the Slicer panel for Status, let's click Full-Time. There we are and we're only seeing Full-Time people. In the left side of the Status Bar at the bottom of the screen, it says that we're viewing 393 of 741 records. Suppose we want to see the Half-Time people as well. We'll use the Control key to click Half- Time and that means we'll be keeping the choice Full-Time as well.

Now, we've got Full-Time and Half-Time, that's 489 people. The red X up here, by the way does not mean get rid of the Slicer, it simply means, don't use the Filter. In effect, let's show all of them, so I'll click the red X, now reviewing all the statuses. If we only want to view the people within certain Departments, we'll click a certain department, for example, Quality Assurance, and we see 73 records there. If there's a consecutive set here, we'll use the Shift key, as I'm about to use here and we'll see the Quality Assurance, Quality Control, and the ResearchCenter People, all together.

I'm using the Shift key now to click Research Center, and now we're seeing people from those three departments. Here too, you can use the Control key to select others as well, whilekeeping these here. This gives you new insight into your data as well. Now I'm going to move this aside because sometimes the data you're working with grows. It might grow in the right-hand side or grow on the bottom, and when it does, the Table feature automatically expands the data we're dealing with. Now ideally, what it should do is show all the data, but we don't even have to worry about that.

In cell J1, I'm typing New Salary and look at what happens when I press Enter. That column is now part of the Table. Let's show all the data here and we can do that by clicking the red X in the Department Slicer panel and also Status, we're seeing all of them anyway. I'm going to write a simple formula here in J2, =H2+2000. In other words everybody is going to get $2,000 more on the salary, but look what happens when I press Enter. The formula has automatically copied to the bottom of the list. Now, if we go to the bottom of the list and we want to add a new record, all we need to do is type in the name, fill in some of the data.

Recognize though that the bottom row is now a part of the Table. You can tell by its coloring scheme. Now, I'll just put in, for example, a starting date and maybe put in a salary over here, we won't worry about the other fields just yet, so we've added a record at the bottom. Another feature working with Tables that can be helpful too, and initially, it might seem like it's obtrusive. On the Design Tab, when you got the active cell within the data, you might want to choose Total Row. Look what happens at the bottom here.

That puts in a total. Now, maybe that total isn't that interesting to us, we'd rather do an average, so here's a drop arrow, we can choose Average, and we might want to do that over here too. I'll drag it over here. Obviously, on some situations we don't want anything. We can delete that. We might want to do averages for all of these or possibly totals. Maybe you'll change your mind and want to do totals on this one. That's fine. We'll choose Sum, total number of years of service within the company. Now, what happens if we'd like this feature and like to see it updated at the bottom? What happens if we want to add a new name?Momentarily, disable the Total Row.

Add a new name out here. Fill in a little bit of data here, good enough for now. Now, going back to the Design Tab, let's bring back the Total Row and there it is, and it has recognized the additional data, and has updated our totals too, so you can work with the data that way. I think you can see some of the advantages here of working with these data as a Table. Although, it's beyond the scope of this particular movie, if you're working with charts and the data is growing, perhaps you update a chart each month by adding new monthly data, if the data you're working with, if the source data for the chart is a Table, the chart will automatically expand if you add new data on the right side or bottom of the Table.

Now, there could be times when you no longer want your data treated as a Table. Now, I would hope that's not the case-- I'm going to press Control+Home to go back to the top-- but you can turn-off the feature too. The idea here might be maybe you just started with it, somehow you think maybe the data you're working with isn't appropriate for a Table or maybe you're not quite familiar with some of the Table features. You do have the option on the Design Tab to make the choice over in the Tools Group--Convert to Range. Do you want to convert the table to a normal range? If you choose Yes, this is no longer a Table.

Now, the coloring scheme still resides here. If you want to keep that, that's fine. If you want to get rid of it, you'll just have to select the data and get rid of some of those features. This is a feature, by the way, that does fall into the category of those actions that you could undo and redo. Maybe I just change my mind now. I'm going to press Control Z to undo the fact that I'd converted that away from a Table, and now it's a Table again. So I think it's a potentially really interesting feature. It does allow you to treat your data as an entity. You have the Slicer capability here to do filtering and it allows the data to grow both on the right side and on the bottom.

If you'd like to add visuals, pictures, icons or shapes to a worksheet, the Insert Tab in the ribbon provides us with a number of choices, for example, pictures. In files that ship with this course, you will see some pictures in Chapter 4, here's one for the company here that makes clothing items. We want to show this in the worksheet. Just double-click it and there it is. We can move this around. We can drag its edge or just inside. If you drag one of the corner handles as they're called, you can make this bigger or smaller keeping the same proportion.

Dragging the side handles sometimes distorts it or does whatever you wish with it, but you can do that too. Recognize also that when this enters the worksheet environment here, there is anew ribbon called Picture Tools with a Format Tab on it. Exploring all the options here would take you a long time, but maybe I like that one. If that weren't enough, how about moving this over a little bit maybe here, some picture effects and just tons of options you never even dreamed of. Do we really need a reflection on this? Well, we could have a reflection and make it more prominent, maybe move it back over here at some point.

Tons of options, it will take you a long time to explore a lot of those. So, that's certainly one option. How about the trademark that we see over in column A? That's actually an object and we have that available also. Insert>Pictures, also located in the same place where we found these sneakers right there, Insert and there it is. If you're making size changes here, it's always best on logos to be using the corner so you keep that same proportion of height to width. It's generally frowned upon to distort the look of a logo, but you can certainly move that around, too.

You can copy and paste these and do different things with them as well. Maybe this time, we'll just put a border on it and decide on one of the borders this way maybe. Thick border does that look good? Well, it might or might not, as long as it doesn't violate our company's copyright restrictions regarding how this is displayed, maybe that's going to be just fine. There are a lot of other features we can get to as well from the Insert Tab. Here's an icon called Shapes. Now, there's a ton of shapes here. Many, many of these, in fact, most of them do encompass space.

So, we might want to put text somewhere. Here's a rectangle and we can then click and drag and draw a rectangle-- it could be wide, it could be tall. If we want it to be a perfect square, we hold down the Shift key. That's a perfect square. You should let go of the mouse first. If we wanted to draw a perfect circle, once again, on the Insert Tab, Shapes, this time we choose an oval. Drag it to the right. It could be an oval of any size, but if we want it to be a circle, hold down the Shift key. A number of the shapes here also include yellow diamonds.

They're not obvious until we see them. Let's take an example here of a hexagon, right there.We can make that anyway we want, but there's a yellow diamond. If you drag the yellow diamond, that changes the diagram. It's going to be more like this, approaching a diamond or in this case, approaching a rectangle. So, we can do that. And you probably saw a smiley face out there too, just tons of choices for any number of different reasons. So we're drawing this one. That's a frown face or anything in between of course, too.

We can change the color of any one of these. Recognize when we select one of these, the Format tab is active in the ribbon and there are all kinds of things we might want to consider doing. As we slide over these choices, keep an eye on that hexagon, you'll see how it's changing. If we want to put text in it, we can just start typing and maybe we're going to use this with the worksheet. Maybe it's going to be our title, in fact. We might want to move this toward the title area here. Move that box out of the way, maybe press Delete to get rid of it. Click this icon, perhaps delete that.

Maybe we're going to use this for a title. Pop it over here, maybe make it wider. We like the look of it perhaps. Maybe this represents first half sales report. So we just type it in place. And why stop there? We'd want to format it. So if we click its border, we could then go to the Home tab and choose perhaps a much bigger font and maybe change the font--make it bold, center it, top, bottom, left, right--and so on.

Just a ton of things we can do with these features. If that were not enough, there's another feature up there on the Insert tab called "Insert a SmartArt Graphic". Click. And how many choices do we have here? Oh, about 230 of them. Get a list of all of them and they're broken into various groups here. One that I've worked with a little bit is under "Hierarchy"-- it's an organization chart. But why stop at one? There are quite a few variations on it. Maybe we will choose Organization Chart here. Click OK. It's already set up for us.

Maybe we'll just type in a few things here to show you how it works. There is the CEO. Maybe this is the General Counsel. Notice that as we type this, because we're using more text, it automatically adjusts; and without doing too much typing here, maybe this is the Sales Manager and over here is the IT Manager. Now, we might want to add some things to this. I'm going to right-click on the Sales Manager here and add a shape. For example, we're going to add a shape after it, to add another manager at the same level or possibly by right- clicking on Sales Manager, adding a shape, adding a shape below, someone who works for the sales manager; or maybe we will repeat that with F4 to add another one, and that's someone who works for this person.

So there are a variety of ways to use this and this is just one of many. As we're using this, too, recognize that all of these different SmartArt options, were likely to have a Design or a Format tab as well here. So on the Design tab, we might want to change the color of this, and as I slide over these choices, look what's happening to that organization chart. It will look even better as I slide to the right here and move this out of the way and explore some of these. If that weren't enough, we've got styles out here too.

I mean you could be busy for months exploring all these options, perhaps years. It's not the purpose of this, but you get the idea, definitely worth exploring. These visual features go on and on and on, quite a few of them. You can add text to many of these shapes you saw or you could add pictures, you could add logos. It's a great set of tools available on the Insert tab in the ribbon.

We're reviewing the worksheet called Insert-Delete within the file 05-Layout, and we need to add a new column, a Phone Number column between Columns C and D. When you insert columns in Excel, select the column to the right of where the new column is going to appear.Now, using the standard menu techniques we can go on the Home tab to the Cells Group and choose Insert and simply Insert Sheet Columns; and we automatically get a new column to the left. All the other data gets pushed to the right.

And so I'll put in our Phone Number heading here and then eventually we'll fill in the details. We can also insert rows in a similar way. It's often going to be handier to use the right mouse button. Suppose we also need to add a Social Security column, we could right-click Column D and simply choose Insert. Notice that it doesn't say Columns but by implication that's what it means because we've right-clicked on a column-- Insert, and there's a new column--andeventually maybe we'll put in a Social Security Number. Now, sometimes when you're inserting data, you have to consider what is already there.

And if we wanted to put in Pennsylvania here in this list, what about the fact that we've got formulas right there that are adding up these numbers? Should we put Pennsylvania (PA) at the bottom here and move these down first, that sort of thing? Well, we could, but it's going to be simpler here to essentially take this data and insert new cells above it. Now, we could insert a new row, but if we look at the data to the left, we really don't want a new row in the middle of that TaxTable, nor do we want a new row in the midst of the other data that we've already got accumulated here.

So sometimes what we need to do is Insert Cells. So I'm going to select these cells right here, and using the right mouse button, Insert, notice that there are three dots behind this. If we had chosen a row or earlier as we had seen, we'd choose a column, no questions asked. This means we go to a dialog box automatically. We are about to insert cells, but do we want to shift them rightward or downward? And based on the nature of the data and how we've highlighted them, Excel is suggesting we want to shift these cells down. So we click OK.

Now, as we do this keep an eye on the totals that are in row seven, they're going to get bumped down. They're still going to be accurate, and maybe we'll put in Pennsylvania (PA)over here and also over here. Although I don't have the numbers ready just yet, what's happened to the formula here? It has been adjusted automatically. So there are times when you want to Insert Cells. Now, if we insert a new set of cells above the data here, it's going to push all the formulas down. So as a general rule, you don't worry about your formulas getting destroyed if you insert rows and columns.

There are exceptions to that, but for the most part that's not a major issue. But let's suppose we wanted to add a name to the list here. If we're not too careful and if we haven't scoped out this worksheet or if we're unfamiliar with it, we could easily make the mistake of saying, "Okay, if I want to put in a new name here--and of course we can do this at the bottom--why don't we right-click here, insert a row?" And we could add a new name. But meanwhile, what has happened to the other part of the worksheet as we scroll rightward here? We've put a new empty row on the TaxTable and we've put an empty row out here. That's probably not what you had in mind.

And so here too as I press Ctrl+Z to undo--which you probably would mean to do here--and let me make these two columns narrower, so we can see this a bit better, I'll just drag them this way. If we want to put a new name above this set of data here, highlight just this data, then right-click and Insert, shift these cells down, add the new name that way. And of course that does not disrupt the data to the right. Earlier we had the Pennsylvania (PA) in there automatically. So it didn't destroy the table in any way, didn't insert any empty cells there.

So just be sensitive to the idea. There certainly are times when you want to insert a new row,at other times insert cells, and certainly the same idea applies to columns as well. Now, there will be times of course when we need to delete a column, and maybe we've decided we're not going to put the Social Security Number in here, maybe it's too late to do an undo because we've taken some other effective measures in the meantime. So we want to get rid of Column D. The easiest way would be simply to right-click Column D and choose Delete; and all of our columns shift to the left.

If it turns out that we really don't want to add a name here, of course we don't want to delete the entire row, we've got data off to the right that we want to keep, but we might want to delete the cells. So right-click and Delete and shift the cells up. This only affects the data between Columns A through I. So the data below that will shift up. Nothing to the right will change whatsoever. So inserting and deleting columns and rows as well as inserting and deleting cells makes sense. It gives us the basic tools for redesigning our worksheets when necessary.

You can hide a column to simply get the data out of the way for a while because you don't use it very often, or maybe you're about to print data and you just don't need to print a certain column. And similarly, although less likely, you can do the same kind of thing with rows. We're looking at a worksheet called Hide -Unhide in the 05-Layout workbook, and maybe we are about to print this data and it's just not important for us, for example, to show the Hire Date.So, what can we do? We can get to the Hide-Unhide feature on the Home tab in the Cells group, under Format.

This is not the fastest way, but for the record, on the Format button, you'll see the choice Hide & Unhide and we could then slide on to "Hide Columns". When you hide a column, there's a slight visual difference and this might not be clear on the movie as you're watching it right now, but the columns separated between D and F is slightly different. And of course, if you simply remember your alphabet, you'll recognize that there is a missing letter E here. Now, I get data all the time from different sources and one of the first things I check for, is to see if there are any hidden columns.

You can also by the way, hide more than one column at the same time. If I'm about to print this and I don't want to print the Department column and the Salary column--columns C and H-- I'll click column C and then with the Control key held down, click column H. Both of those are selected, then I'll right-click and go to Hide. Right-clicking often is the fastest way to Hide a column. Now, we have a number of different hidden columns. If we're printing this data, we're not going to see any gaps. I'm going to press Ctrl+F2 here, a quick way to get a print preview.

And there we are, and we see the data there without the columns that are hidden. There's no gaping hole in the printing itself, so it doesn't draw attention to the fact that we're not printing all the data. Press Esc to get out of here and we're back to our normal view. Of course the question comes up, how do you get back the hidden column? If you simply want to bring back one of the hidden columns--I want to bring back column E--we can drag across the surrounding columns. Click and drag across columns D and F and then do one of two things, either right-click on Unhide-- --that's probably the most logical and reasonably fast way to get there-- let me undo that and show you another method.

After selecting the columns in question, double-click the boundary between them or in fact any column boundary that's visible up there. Double-click, and that brings back the data as well.Let me undo that again. What if we've got multiple columns hidden? And we do. Column C is hidden, column E is hidden. There could be some others. What if there are some other columns off to the right we're not sure of? I don't want to check them all out manually maybe. Click in the upper left corner to select the entire worksheet and then simply double-click any column boundary-- like this--double-click and all the hidden columns are back.

Let me undo that again. Another way is--after selecting the entire worksheet--simply right-click any column, and choose Unhide, and all hidden columns will return. There certainly will be times when we want to hide rows as well. Maybe in the list that we're about to print here, it just so happens that we know that Michael Ashley here has left the company, we still want to keep the record here; we don't necessarily want to show it as we print this. Same thing has happened with Heidi Barker here. So once again, we'll use the Control key to select both of those and then right-click and Hide; so we're not seeing those. And here too as I press Ctrl+F2, if we look at the printed data, we're not seeing those names in the list. Escape from here.

And so if we were to print the information, those names would not appear. As you might imagine, we can bring back the hidden rows by clicking the upper left-hand corner; simply right-clicking any of these and choosing Unhide; or as you might have guessed, we could simply have double- clicked on the boundary of any two rows. So, as we've seen, sometimes we want to hide columns, other times we hide rows. The feature is easy to get to, we do it for a number of different reasons. It's one more tool in controlling the display of our worksheets as we workwith Excel.

One of the basic tools we use at different times to redesign the look of a worksheet is the ability to move or copy data to different locations. We're looking at the worksheet called Move-Copy-Insert. And in Columns A to I, we've got Sales and Profits for the first half here. And possibly for presentation reasons, or for printing reasons, we might want to redesign the look of this. For example, maybe we want to display this on the screen. We'd rather not talk about this information. We don't want to delete it.

Why don't we just move this data somewhere else? Now, there are multiple ways to move information. Certainly a common way is the two- step technique called Cut and Paste. We can get to cut in a number of ways. On the Home tab, you'll see a Scissors here. As you slide over, it recognized the keystroke shortcut, Ctrl+X. We could click the Scissors, and if we want this data down in Row 17, we could then click there, and notice the prompt at the bottom of the screen--"Select destination"--and press ENTER or choose Paste.

We could click the Paste button up above or we could simply press Enter, and we move the data that way. Now, generally there's a faster way of doing this. I'm going to press Ctrl+Z to undo what I had done and Escape here. When we select data, which we must do before cutting and pasting, why not just drag the data? Now, unless we're dragging this hundreds of rows, thousands of rows or columns downward or rightward, why not just drag the data? So using the mouse, we can point to any edge-- --it doesn't have to be the top, it can be any edge--drag it here, we could even put it into separate columns--although that probably wouldn't make a lot of sense here-- but drag it to wherever you want, maybe even overlapping.

It looks like it's overlapping, but this is simply going to move the data downward. So dragging data. The formulas within here still refer to the data up above, we're still seeing the same numbers. It's easy, it's fast. I think much of the time moving data really is just a simple drag.You select the data, hold down the left mouse button, drag across all the data, move it wherever you want to move it. We can do that easily. Once again, I'll press Ctrl+Z a few times to go back to where we were. There we are. Now, at other times you might want to copy data.

The data here has formulas with it as well. We might want to make a copy of this, maybe we want a different set of numbers, we want to experiment with these a little bit. So we want to keep the data here, but let's also copy it down below. Now, many times this too is a two-step process, and the process here begins with Copy or Ctrl+C. So we could copy that data and just as with Move, we could go to a destination area down here and press Enter. So we've copied the data, but let me press Ctrl+Z to undo.

Dragging probably is going to work better. Select the data that we want to copy, drag any edge--it doesn't make any difference which edge--and as you drag, hold down the Ctrl key. You'll see a tiny plus that accompanies the arrow there, and you don't have to hold down the Ctrl key immediately, but just as you get toward the destination, make sure you are holding down Ctrl;be sure to let go of the mouse first. So we've copied the data, and we see it up above as well, and we could copy it again and again if we wish. The formulas that are here, for example this one, refers to these cells, not to the cells up above.

So copying data many times is, after selecting it, simply dragging the data with the Ctrl key.Sometimes you heard the phrase Ctrl-Drag. There might also be times when we need to insert data. Now, if we're looking at this list where we see Sales, Expenses and Profits, why down here is it Sales, Profits and Expenses? That seems to be not right, they should be in sync.And so one way to rearrange this is to insert a new row above Profits and then move this data above it, something like that.

Let's simply make it a one-step operation. After selecting the data here, we're going to drag this upward using the Shift key. In other words, we want this data--the Expenses Change data--to be between Sales and Profits. So we've selected the data, we hold down the Shift key as we drag this upward, and we let go of the mouse first. There it is. So we've simply moved the data upward, moved and inserted at the same time. You can do this with entire rows. You can do it with entire columns.

On the same worksheet, off to the right, in columns M rightward, we've got a list of data.Maybe it just so happens that we want the Benefits column to the left of Hire Date. So we'll click Column R. We're going to drag the left edge. Now, we can drag either edge or even the top, it doesn't make any difference, just put the mouse pointer on the edge here, and as we drag leftward, we've got the Shift key held down. Drag it over to here, let go of the mouse, we're moving the Benefits column to the left of the Hire Date column.

And where necessary, you can move two columns, three columns, you can move theseleftward or rightward; it doesn't make any difference. If we want to move the Building and Department columns to the right of Years, we'll drag this rightward here, with the Shift key held down, drag it right there, let go of the mouse; we move those two columns that way. So we can easily move cells or columns or rows while inserting the data. So anytime it comes to moving data or copying data or moving and inserting as a general approach, dragging the data tends to work faster than the commands we see on the clipboard.

But there's no question that we need this capability as we redesign our worksheets.

On the Home tab in the ribbon the editing group on the right-hand side contains a number of features including a binoculars icon for Find & Select. Some of the things we're about to show you in this movie could be done more efficiently with a filter, but not always. Sometimes we're simply looking for data. Let's imagine that we're looking for somebody named Rick. Before searching for data in Excel it's best to narrow down the search. If you click a single cell and then activate the Find feature, you will automatically be looking in the entire worksheet.

If you select a range--possibly you have this range already selected for a different reason-- if you start a Find now, we're only looking for data within the range. Now let's say we are looking for someone named Rick. The only location in this worksheet where we see names like that is column A. Let's go to column A, select that first, and now our search process using Find willonly be looking in column A. Notice it's "Find and Replace". We're simply going to do Find here. We're looking for Rick. So find what? Rick.

Notice I'm not capitalizing it although I could. Let's just see where we can find here. Find All, find all occurrences of Rick. As we move this around a little bit and expand it, well, perhaps more than we would have expected. But recognize that we found Strickland and Patrick and Frederick and Erickson. There's an "R-I-C-K" in these names. But since the list is relatively small, that's not so bad. We could easily figure out the person we were trying to find. Now what we could have done here, we could have used a capital "R" in our search and then chose the option "Match Case".

Now at times when you're using Find and Replace, you do see these choices but if you click the options tab, it might collapse. So there could be times when you're using this you don't see those options--choose options. If we Match Case and then choose Find All, the list will besubstantially smaller. Only three entries. Now we also found Ricky, but that's okay too, probably, in the case here. So be sensitive to that idea. Recognize another choice here "Match Entire Cell Content". Sometimes that's helpful too particularly with larger lists it helps narrowdown the search faster.

Now step outside into the data again-- another use of this and now for Replace situations-- let's imagine that in this list here the company has decided to call the half-time status, "part-time".This is a number of people who work, maybe three quarters or 60% or 40% of the time, we simply want to use the more encompassing term, part-time instead of half-time. So what is that we're looking for here? We're looking for half. Now do we want to match the entire cell contents? In no case, let's say in column D, do we have the word "half" and only the word "half", so we do not want that box checked.

At other times it will make sense to check the box. If we want to match the case, yes. Now, is it critical here? Probably not. Probably half is always capitalized. So don't worry about it too much, but let's say we want to replace half. We go to the Replace button half. Now, do I want to replace it with full-time? No, I don't think so. The reason that's there is that maybe a recent search included that. So let's instead say we're going to replace "half" with "part" and we wantto replace all of them. So we'll do that.

So what happened? Ninety-six replacements were made and we can see in the backgroundpart-time, part-time. No more half-time in column D. So we click out here. Now another use of this--and this also implies we can use formatting and we can-- we've got some hourly employees in here, but two of them have been selected here. We've simply made them yellow.We're going to replace their status. We are going to change it from Hourly, for example, into Contract-- so we want to make a difference there. Sometimes what we want to do is replace data using a combination of not only the content, but also the format.

We want to replace the word Hourly. Now not all of them. For example in row five, you can see Hourly. So we are going to take the Hourlies here and replace it with Contract--but not all the Hourlies--just the Hourlies where we've got a format here. In other words, the format of yellow cells; the fill effect yellow. Let's say although we don't really have to do this, say that when we do replace the yellow, we want it to be blue. It's not really necessary, but we want to highlight the data.

So everytime we see Hourly that's yellow we want to replace it with Contract and make it blue.Replace all of these. Now we didn't necessarily select any part of the worksheet-- so the active cell is in one cell or another-- if we make this a little bit more efficient, let's click in column D because that's only where we want to make the change. We'll do a Replace All. Two replacements. Click OK and as we put down the screen here, these both had been Hourly and they were yellow and now they are Contract in blue.

Again, I use the blue here simply to highlight the idea that we sometimes want to use the Replace technique along with formatting as well. Another use of Replace, the Taft building has been renamed the Harding building. Let's make a wholesale change here. We'll simply select column B, go back to Find & Select, this time a Replace. Nothing to do with formats here. So clear the format in both cases and we simply want to change Taft to Harding.

Now it's probably the case that we'd never see the word Taft there along with another word, but just in case we want to match entire cell contents. Matching case here isn't really relevant this time, but matching entire cell contents is. All occurrences of Taft when it's the entire entry we want to replace with Harding. We'll simply do a Replace All. So we've taken care of that--63 replacements. So as you can see, Search and Replace, a valuable tool for making wholesalechanges or in some cases just finding data that meet your criteria.

If you're about to print a worksheet, it's best to get a Print Preview first. A couple of ways to do this. You can go to the file tab in the ribbon and choose print and you do get a preview.Recognize on the far right-side of the screen you'll see two sets of scroll bars. If you use the outer scroll bar and drag downward, recognize not only will you see the full page but the all-important indicator at the bottom of the screen is to how many pages this is likely to take up.Now if this particular worksheet has about 700 rows or so and you see that it's going to take 35 pages, that don't sound quite right, does it? If you click the Preview here and either use the mouse wheel or use the inner scroll bar there to drag downward--eventually, we'll begin to realize that in this particular set of data here that we are working with--it looks as if the first few columns are going to be printed and then some others on additional sheets.

And then if we keep scrolling here we'll see how the other data eventually will be printed as well. Let's say that's not quite ideal. There certainly are some settings over in the left-hand side that might change our minds. Some of you might be familiar with the term "Portrait Orientation"--where the papers are oriented vertically-- we might change that to Landscape; will that make any difference here? Now it looks like it's taking up 49 pages. So that certainly didn't help here. Change it back to Portrait. Let's escape from here or press the left arrow at the top of the screen and go back into our standard Excel view.

Recognize that the ribbon has a page layout option with a number of choices here related to features that you will consider using before printing but also same term--but not in conflict with--but not really the same. In the status bar, we've got three buttons. Normal, the normal view that we typically use as we work with Excel. Next button, Page Layout-- our screens look different here. If we were to zoom back a little bit here, we'll see multiple pages.

The page on the left as we scroll down a little bit is page one. Keep scrolling here, Page two.But the pages on the right, pages 18, 19. Now if you're working with your data at this point, of course, this is likely to look quite a bit different and recognize also that Excel is trying to use all the data in the worksheet unless we indicate otherwise. So a couple of choices here we might want to consider. First of all, in this page layout view let me zoom again by dragging the zoom slider bar and focus on just the upper portion here.

Notice where it says "Click to Add Header". A design tab is activated, Header and Footer Tools. So we might want to add the current date here. If you click this option Current Date, that's some kind of a strange indicator, but it will print the date. If we want to preview that, just click in a cell below. We see what will pan out. That's the date of this recording. So we'll click back up here. What if we want the time here as well? We might just click here and add a space and then click the icon for "Current Time". So we'll see both of those and over to the left here we might want to put in the name of the company or maybe the number of pages.

So there it says, Page Number--that little indicator. How's this looking now? Click below it. One out there for the page number. We'll see the date and time here. We might want to have the word "page" there. So click in front of that little ampersand there-- that ampersand symbol.Type in the word "page" followed by space if we want that. So we've got some control here over our header and footer. Now if we click back into the worksheet portion of this in the preview and then go back to the Page Layout tab, there are some other options here.

Now we can't go through all of these, but from time to time we might want to go back to our print preview. We don't necessarily need to click on the File tab. Here are couples of other options. In the Quick Access Toolbar, you could add a button that gives you print preview. The rightmost arrow, it's a drop arrow, click it and choose Print Preview and Print. So now if we want to Print Preview, click that button. We are back here again. Let's take a look at this. Is it looking any better? Well, we changed our titles there at the top. It's looking better that way.

We can scroll up and down. How do we move away from here? Escape. So we've got the Print Preview button. There is also a keystroke shortcut, Ctrl+F2. It is important to get that preview from time to time as you're setting up printing and Escape again. Now another option here.Once again using the zoom slider bar to move back a little bit. We still haven't quite dealt with the issue of what's showing in our potential print out here. Some options outside of print features might simply be if we don't want certain columns to be printed, we could hide them.

The idea might be we're trying to bring these columns onto the same sheet, but here's a completely different approach. Let's go back to our normal view and suppose we're saying, "You know, the data to the right, that's useful, I might want to print that separately". Maybe all we really want to print is the data from these cells over and downward. A quick way to select this data ahead of time is drag across these headings and holding down the Shift key just double- click the bottom edge of a cell here. Now we can certainly drag across the data.

What we are about to say is "This is all we want to print". So there's an option of the Page Layout tab called "Print Area". Select an area on the sheet you'd like to print. Well, we've already selected it. Click this--"set print area". Now let's take a look at our preview. There's our button up there or Ctrl+F2. We see the preview. Use the outer scroll bar on the right to scroll downward. We're at 34 pages maybe. We're still not there yet. So possibly we will reconsider landscape orientation here.

Instead of 34 pages it's down to 24 pages. Click here. Maybe scroll up and down again. Is that acceptable? Well, it might be. Recognize again that you're the one making the final call on how this is going to appear. Maybe that's pretty acceptable. One thing that you might not care for though is this. On Page one we do see the heading, Page two, Page three, we don't see that.Do we always know what column we're looking at? So let's escape here and once again go back into the worksheet environment.

We don't necessarily need to have the page layout view in the lower right-hand corner activated. It doesn't hurt, but it's not necessary at this point. But the option we want to focus on is on the Page Layout tab and it's called "Print Titles". Choose rows and columns you'd like to repeat on each printed page. Click there and what is the row we want to see repeated at the top? Click here and we can simply select row one. In some cases rows one and two depending upon the worksheet.

That looks good enough. From here, we could just jump in to Print Preview. Let's take a look--Print Preview. Now we're back here and what happens if we click and start to scroll? Now we're on page four, page three. All of these have that heading in place. Now we haven't covered all printing features, but let's say in this case it looks pretty reasonable. We could then print. So escaping again, using a combination possibly of Page Layout View in status bar and also some of the features available on the Page Layout tab we can prepare our worksheet for printing.

Another approach to printing begins with the idea that you'd like to get a preview of what your printout is going to look like. In the status bar, the three buttons in the lower right-hand corner include a button called Page Break Preview. And as we click this, the screen changes and we see blue borders and possibly dotted lines as well. If you start to scroll in this list, as I'm doing here, recognize the watermark depiction here of Page 1, over in the left-hand side--up to the right in this example it's Page 18. So as we scroll up and down here, we see these. Now, with Page Break Preview, you have the option here of controlling not only the breaks, but the actual borders here.

It's good to get an actual preview here. If you do have available in your Quick Access Toolbar, the Print Preview button, we added this in an earlier movie, you can click that or possibly press Ctrl+F2 or maybe even go to the File tab and choose Print. Any of those three ways gives us the Print Preview. And as we look at this list here and then use the outer scrollbar on the right-hand side, scroll down, see how many pages it is, 34 pages. If you click in the list and start to scroll up and down, you might see breakpoints.

You might see the data about to be printed in a way that you wouldn't necessarily want. So what do we do in the situation like this? Let's escape from here and take advantage of the fact that as we're viewing Page Break Preview, first thought might be, do we really want to see columns K and L? They are empty except for the top two cells, so let's drag the blue border inward. Put the mouse right on there, hold down the left mouse button, drag it to there. Now, if we don't want to see these last two columns on separate sheets, we could take the dotted line and drag it rightward.

But that means that the data is going to be squeezed onto the page which may or may not be acceptable. It might be in this case, so let's jump into our preview again either with Ctrl+F2 or possibly the Print Preview button. As we look at the data now, it certainly looks more crowded.What might be appropriate at this point is simply to print one page. You do have the options here, when printing you could just say print page 1 to page 1 and do that and see what this looks like. And you'll have to be the judge as to whether this is acceptable.

If it's only for you, perhaps, that's just fine; if you got a different audience, maybe that's too crowded, it depends. Another option here could be to jump into Custom Margins over on the left-hand side and use Narrow Margins, in other words, less white space on the perimeter. So you can certainly make that choice. It looks like that pretty much just shifts the data leftward,it doesn't do much else. So, maybe that option isn't one worth considering, but you do have some choices here as you consider how this is likely to look when you print. Let's escape from here, Escape key, go back to our data and here's another potential option too.

And this is outside the realm of printing, but it does bring out the idea that you're in-charge of how you want this to look. Do you really want to see the Salary in this list? Do you want to see the Status? Well, you are the judge, but if we were to hide a column, I'm going to hide theSalary column simply by right- clicking it and choosing hide. How's the Print Preview looking now? Perhaps, not as crowded. And so sometimes you will use that approach to adjusting your display. Here's another thought too, as we scroll up and down, sometimes the breakpoints on the pages, we might want to be different.

Now, in this case, I can't make a strong case for saying change this, but we do have the ability to change the location of some of the page breaks. And you can imagine how in certain cases here, how appropriate or inappropriate this might be to adjust these breaks. So, maybe at some point here--I'll just pick an example of one of these here where there's a breakpoint that I might want to change-- I'll just drag that blue line elsewhere--so maybe in this case here. Now, that might be crowding one of the sheets, but I want the breakpoint to appear right here after Marketing. In other words, you can override the standard setting.

Jump into preview here. Let's take a look at this and we'll scroll down relatively quickly and try and find that breakpoint and there it is. Looks like one of the pages was quite a bit shorter.Again, you'll just have to decide whether that's acceptable or not when you print this data. Once again escape. So you do have control over that too. I'm going to press Ctrl+Z to undo that break because as it turns out, it wasn't maybe the best choice; but you do have control over those as well. Using Page Break Preview doesn't get us into some of the other printing issues, but it does give us a quick read on the data and allows us to choose the data that we want to use when we print our work.

And that button in the lower right-hand corner, Page Break Preview does allow us to continue working with the data. Now, most people probably don't want to work with their data in this view, but we can write formulas, we can insert data, we can do other things, just as if we were in the Normal view. So you can leave it in this view as much as you wish and when you're finished-- and for example you find this obtrusive-- simply click back on Normal. But PageBreak Preview does give us a quick way to view our data just before printing.

We're looking at a worksheet called PageSetup Sheet and we want to print this worksheet. On the Page Layout tab in the ribbon, you'll notice that one of the groups is called Page Setup. In the lower right-hand corner, a dialog box launcher button. If we click it, activates this dialogue box, called Page Setup and there are many, many different settings here. One group is on the tab Page, another on the Margins tab, another on the Header/Footer and another on Sheet.Although, you might have seen some of these choices in the Page Layout tab in the ribbon and you might even have seen these on the File tab, here they're all together.

And if you have used Excel in prior versions, this is familiar territory as well. So, let's consider some of the options here, but let's first break out of this. Press cancel or escape and let's indicate what it is we want to print. So one approach to printing begins with let's select the data. If for example here in this list, if you say, "I don't want column N, but I do want all this data here"--if you simply want to select all this data ahead of time--you can press Ctrl+A and then on the Page Layout tab, go to Print Area>Set Print Area.

If you want to get a preview of this, we can simply click this button. If you've added this button called the Print Preview button, which we did in a prior movie, you can simply get the preview on the right and by clicking in here, dragging that outer scrollbar on the right-hand side downward, we'll see approximately how many pages we're about to print. We can scroll up and down. While you're on this page, notice off to the left, at the bottom, you see a choice called Page Setup. Click that. This is the dialog box that we just saw.

Recognize again, some of the features here are also being presented off to the left. For example, if we're looking at Page Setup here, the Page tab, gives us a choice for Portrait and Landscape. We see the same choices over here. So one of the issues we confront as we work with printing is the fact that a lot of these settings appear in multiple locations. Let's press Escape here--and also escape from this view--and shift our focus again to the data that we're about to print. And as we look at the Page Layout tab again, click within this Page Setup group, the dialog box launcher right there to activate some of these choices.

Without going through all of these just recognize that we do have choices as we mentioned earlier on Portrait or Landscape. There could be times too when you want to fit this printout to adifferent number of pages. Could we fit this to one page wide by ten pages tall? Well, let's try that. Either click the arrows or type in a ten, something like that and make a choice that way.Let's get right into Print Preview and see how this looks, maybe not so good. Let's escape and come right back in here, doing the same thing all over again, but this time maybe, just ignore this choice.

Let's say we do want to fit it to a number of pages, we click back on Adjust to and again, somewhat arbitrarily make this an eighty--Print Preview-- see how that's looking. Maybe that looks better. So you want to experiment a little bit here. Can we try this again, going back here with 100? Right within the same dialog box-- Print Preview--and there we are again. We could end our page setup from here. It's over on the left-hand side at the bottom, and consider some of the options. Now once again, I'm not going through all these.

Do recognize here you can center the data horizontally, left to right, maybe, that's what you want to do. You've got control over the margins here, is an inch of white space on top and bottom too much? Maybe click the drop arrows that way-- maybe it'll take up fewer pages--similarly on the right, something like that. Again, lots of choices here, maybe less space for the header top and bottom; we can also go into Header or Footer this way, make some changes. You can create your own custom header. There are some built-in choices here, some use dates, some use file names; various combinations in here, quite a few choices. And so you can get to some of the fine-tuning tools available when you want to print.

Also, there's a Sheet tab here. Do you want to see the Gridlines? Probably. Do you want to see the Row and column headings? These are here. Now once again, recognize, if we click OK--and we've made that choice--watch the preview change in the background here. We now have column letters and row numbers. And that certainly is appropriate for some kinds of data and not necessarily this. Recognize too, if we escape and go back into the worksheet environment, a lot of the choices that we make here with Page Setup--from the dialog box here--are already here under Page Layout in the ribbon.

So, this idea of "where do we go when we print and where do we make our settings?" The Page Setup dialog box is certainly handy, but a lot of these choices are right here. Maybe we don't want the Gridlines, we could uncheck it here. By the way, if you uncheck the Gridlines here, it actually changes the current worksheet view as well. If we jump back into our preview, we won't see Gridlines there either. And the row and column headings, we can also control those right here too. Let's not print them. And then let's go back to our preview and now we don't see the row and column headings.

So, any number of choices you can make here by way of Page Setup: clicking the dialog box launcher here and using the feature this way; or once again on the File tab from the ribbon; going to Print and making choices here by way of Page Setup. There are lots of ways to control the way our printout is going to appear by way of Page Setup.

In this worksheet, called CreatingCharts in the workbook 07-Charting, we've got two sets of data and we might want to depict this data in a visual way. Excel's charting capability has long been one of its most popular features. And by the way, the term "chart" and the term "graph", often used interchangeably, in Excel, we use the term "chart", officially and formally. Let's select the data that we want to depict graphically. We can easily display this information as a chart simply by clicking the Quick Access Tool that often appears when we select data.

Click it, choose Charts>Clustered Column. What does that mean? We don't necessarily know.But that chart looks pretty good. Let's just click and we've got a chart. That's certainly one way. You can move charts--and eventually we would want to move this to position it so we can see your data as well as the chart-- simply drag an edge of the chart. You can resize the chart by dragging one of the so-called corner handles or side handles, shrink it and make it enlarged as you wish.

We've got other data here as well, how about another approach. This data has totals in it, as a general rule--but certainly not an ironclad rule--including totals and details together doesn't work so well; but let's show another quick approach to creating a chart. This time on the Insert tab in the ribbon, choose "Recommended Charts". And as we look at these charts, we can click on them and get a better view off to the right. The grand totals seemed to be not ideal in terms of our display; it distorts the look of the charts.

So, let's escape from here, select just this data, jump back up there to Recommended Charts and now these previews look a lot better. And as you look at these previews too, you begin to pick up some of the terminology. This is a Stacked Bar Chart. Bar charts are horizontal in Excel. Column charts are vertical. Stacking means you are putting multiple fields together, clustered means you're not. So we see different terms here that we will see often as we work with charts. We like one of the others here. We'll just click it, click OK or double- click it and we've got a chart for that data as well; move it off to the side.

At certain times when you're creating charts, you've selected the data and you know which chart you would like to use immediately. So when you're ready to make a chart selection, click Insert and then to the right of the Recommended Charts, we see various types here. We might want to choose a Line Chart here or maybe a Pie Chart--is that going to make sense?--or maybe we do like a certain kind of bar chart here. So there's a Bar Chart and there's the Stacked one. We like the look of that, so there we go. An even faster method, but not necessarily the best--depends upon whether you like the chart style--is to select the data and simply press Alt+F1 and you will get a chart immediately; a Clustered Column chart on the same worksheet.

Another quick approach, you've got your data selected, press the function key F11 and you'll immediately get a chart on a new sheet to the left of the sheet that has the data. So we are on a sheet called Chart1. Our data is on a sheet called "CreatingCharts". The advantage of working with a chart all by itself is that's our focus and nothing else--no data around on the side. We will spend some time perhaps in designing this chart or making it look the way we want. If we change our minds at some point and say we want this on another sheet, we can simply right-click here and then move the chart to a different location.

If we no longer need this, we'll simply right-click and delete that sheet. Similarly, if we are working with a chart and we do want it to be on a separate sheet, for example this one, we could right-click the chart and choose Move Chart and put it on a brand new sheet; in this case, it would be called Chart2. So that's another option. Now, many times when you're creating charts, the amount of data that you're choosing to depict in a chart is a relatively small amount compared with the size of some of the worksheets we might have been working with; but there are cases when you're selecting, for example, meter readings over a huge amount oftime, you might have quite a few cells selected, so there's no real limit on how many cells are being selected.

But in general, we tend to see, when we're creating charts and in many of the examples, depict a small amount of data. But we've seen a number of quick ways to create them. Again, selecting the data and pressing Alt+F1--the very fast way--or simply using on the Insert tab, the various recommended charts that pop up. There's no question that creating charts is fast, it's easy and you can easily get rid of them just as well by simply clicking on the chart andpressing Delete.

Excel has over 50 different chart types, so it's not always clear which chart is best for the kind of data you're trying to show. We're using the sheet called Chart Types, let's select the data over in columns A and B and simply press Alt+F1, a quick way to create a chart. This gives us a Column Chart. Is this the best way to display this data? When you create a chart, recognize that the Chart Tools ribbon is activated, and we've got a Design tab and a Format tab at the top of the screen.

Off to the right we see a choice called Change Chart Type. We can easily get a preview here of what this chart might look like, if for example it were a Bar Chart. Click here, we see some examples of that, we can slide over it, would this be better as a Pie Chart or how about a Line Chart? We can get a quick preview here and decide what looks best. If our starting data is multi- dimensional, like the data in column D, we get more choices here and more previews, so let's escape from this option. Recognize that this might be the best chart for the data we're seeing over in columns A and B. Let's move this down below for the moment we might come back to it later.

Let's select this data here. Once again, press Alt+F1 for a quick chart selection. When you're creating charts, consider this possibility. Sometimes, the data looks better in separate columns as it is here, and when you're working with Column Charts, those are the vertical ones like what we see here or Bar Charts, the horizontal variation of these, clustering is the firstchoice, this may or may not be your best choice. If we change the chart type, we might change this to a Clustered Column.

The advantage here is it simplifies the look of the chart, but on the other hand, if you're trying to read for example, the southeast entries, those are the green portions of the columns, you can't follow them from month-to-month so easily. You have got some other options out here as well that you might explore. Sometimes, you'll see a 3D Stacked Column, and as we move to the right, different variations on that, and then another variation like this. This might have some visual appeal, but it might be kind of hard to read too; but we certainly have easy access to the different chart types.

If you only use charting occasionally, I think the best approach is to stick with perhaps only four major types here: Column, Line, Pie, and Bar. Pie is, by the way, somewhat specialized as we'll see. So, let's say Column and Bar are certainly common choices. You've seen these in magazines, newspapers, television, they're commonly used. Now, with certain kinds of data, if you're trying to emphasize volume, perhaps Columns and Bars are the best, but again, sometimes it's just a judgment call.

Let's escape from here and move this over a bit, and consider the data in columns K through P. In this data here, we've got the same kinds of choices. Let's not include the totals, just the data here, and possibly getting a preview this time by way of the Insert Tab-- Recommended Charts--what are some of the examples that we might want to consider here? We can move the Title bar, the dialogue box, over to get a sense. If we're trying to show a trend, there's probably no better chart type than a Line Chart. And these are universally recognized because our eye tends to follow the lines from left to right; nothing wrong with a Column Chart here.

There's another kind of chart here called a Stacked Area Chart. Maybe that displays the trend pretty well. We can certainly see a trend emerging here, at least in terms of the totals. But if it's a Line Chart, looks pretty good here, let's go with that option; and we can move these around too. Now, recognize that when you do choose Chart Types too, there's an option herethat is not very obvious, and sometimes it brings us an interesting alternative, but with a Line Chart, we'll see how well this works. Switch Row/Column, not an obvious choice to make.

I think in this case, it's a horrible choice, click it again. If you've got data that deals with times--for example Months here, or Years, or even Hours in some cases-- Line Charts tend to work best there. Now, again, that option that we have here on the Design Tab of SwitchingRow/Column, I think in this case is not a good one, but with the other data here let's scroll here to move the chart over. Move this chart all the way temporarily. The chart to the right is depicting our data right here.

Let's select this chart and then Switch Row/Column. Now, here's an interesting variation. This shows columns differently. Each cluster of columns here is about a region. Earlier, it was about a product. Let's go back and switch that again--Switch Row Column. I use this feature all the time, not because I'm always looking for a better chart necessarily, but the difference in the two--and we can bounce back and forth if we wish--sometimes is interesting and sometimes we end up saying, "let's have both of these".

So one possibility might be, we could just shrink this a bit, move it over here, and make a copy of it or create a brand new chart, either way. How can we make a copy of a chart? We can simply drag a chart with the Ctrl key held down, let go of the mouse, we made a copy of the chart. Let's change this chart here. On the Design Tab, we'll Switch Row/Column. Let's say for the moment, we'll put them side by side, but now we can see the two charts together. What we're saying here is it's not always the case that you want to see both charts, but recognize that we have this quick ability, by using Switch Row/Column, here to get different kinds of charts.

One or the other might be better. We'll choose just one, fine, or maybe both. As we work with different kinds of data, that's an option you want to explore. Now, if you do want to try a Pie Chart--it makes sense sometimes-- usually, they work best if you have only a single column or row of data. A strange thing will happen if, for example, we take this data here and try and create a Pie Chart. Insert Tab, here are the Pie choices, right here, click here. How about a 3-D Pie? It sounds good. That looks pretty good in the background there, let's keep it.

But it's got the title NE on it. So what did it do? It really only used this first column, even though this data was highlighted. So there are some serious shortcomings in terms of the amount of data that can be displayed in a Pie Chart. Recognize this oddity too, what if one of these numbers was negative? I'm going to make this to be -100. Watch the Pie Chart.Obviously, the wedge changed, but as you slide over the wedge--as I'm doing right here--it's displaying this as if it were a positive number. And the more you think about it, it would sound kind of strange to say we've got a negative piece of pie, but oddly enough, if you have got negative data, it's just a terrible candidate; it doesn't even fit here.

I'm surprised that Excel even allows us to create a chart, where we have got a negative entry here. So, I'm going to press Ctrl+Z to undo that, but be careful with that idea. If you're using a Pie Chart, it's got to be all positive data to make any sense. You can only really do this with a single row or a single column of data. The column AB combination does make good sense. If we want to get a breakout here of how these various locations for our sales are occurring-if we want to get a breakout here of the items in a Pie Chart-- simply select this data--Insert>Pie-- there's our 3-D Pie; and that's a good visual depiction of how our sales have broken out.

You can also, eventually, add percentages and other tools here as well. So in this move, we've seen a variety of different chart types. If you only use the feature occasionally, I strongly recommend sticking with Column, Bar, Line, and Pie. These are the most widely used charts, and the best charts for depicting data most of the time.

Excel has so many different formatting options when it comes to charts that you could easily waste a lot of time. Let's make this relatively simple. If we want to create a chart from the data in columns A and B, let's simply select the data. Maybe we're interested in a Pie Chart here.We'll go to the Insert tab in the ribbon and choose Pie, perhaps a 3-D Pie. It looks pretty good.We might not like that a whole lot, but immediately, what do we see in the Chart Tools ribbon, that's active when a chart is selected? We have got a Design tab, Chart Styles, as we slide over these choices, we see some variations.

By the way, when you see these, don't overlook the drop arrow here because there might be more choices than you initially see--and exploring these--that one looks pretty good. Click it.So that's the first thing you might want to consider doing when you create a chart. You also have the same capabilities here exposed by way of the three buttons that you see to the right of a chart whenever a chart is selected. The middle button, the Paintbrush is Chart Styles, so click it and here we see the same choices we saw before, but presented differently.

We can scroll up and down and maybe change our minds by making one of these choices. So that's certainly easy to get to make our changes this way. Now let's work with the other data here. I'm going to shrink this a bit by dragging its corner this way and for the moment that chart doesn't look so good, but we'll put it down here below the data. Let's take a look at this data here. Let's create a chart quickly this time with Alt+F1. We get a Clustered Column chart. It looks pretty good. Chart Styles up above, there they are, same idea as with the Pie Chart.

This time if we click the drop arrow to the right here, we'll see even more choices. So depending upon the chart type, you will see more chart styles and pick the one you like best, maybe this one. Notice how all these choices give us the generic term Chart Title, something we will want to change. Once we have created a chart, we do want to make some changes to it. Certainly Chart Title, we don't want to keep. We might want some explanation as to what these numbers really mean. We might want some information below the chart as well. The legend might be just fine where it is or we could put it elsewhere.

But something you could easily overlook is a feature on the Design tab. Second button from the left is called Quick Layout and when you first click this, it doesn't look too promising, like these images are awfully small. As you slide over these though, keep an eye on the chart to see the differences in these choices. Now nearly all of them contain Chart Title although some don't. Some place the numbers, the values of the columns above them. Some use gridlines, dark and light, some don't. Some place the legend on the right-hand side.

After using the feature for a few times, you'll come to recognize that some choices work better for you. I like this one here, Layout 9. Notice how it provides space for a title at the top and also down the left hand side and also below, so I'll just click it. Now it's pretty obvious I don't want to use the term "Chart Title" so normally what you do is click here, type in something new and press Return--and that's how you adjust the title. But what if you've got a worksheet cell that has data in it? Click Chart Title, click in the Formula Bar, type Equal, and then click the cell that has the label that you want.

In my case here, I want to click D2, the cell right there. Press Enter and the title was placed in here automatically. I don't have a similar title for Axis here and the data apparently is by itemssold, so I'll just say, "Items Sold". You can imagine in some cases where you would be typing in something like Value in Dollars or Items Sold in Dollars, something like that. Whatever you type, press Enter and then we see that as the label; and you could of course change that later.

We don't necessarily need a title down below so we could press Delete or if we want to put in the year here, fine, we'll do that, 2013 Sales, and we see that at the bottom of the screen. So that's a quick way to adjust the format, the display of a chart and for some people that's pretty much it-- that might be all they want to do-- but let's not overlook the Format tab. Here's where you could get bogged down if you're not careful, but if you like different colors perhaps on the perimeter of the chart, you might have recognized or picked up on the idea that the inner area of a chart--the one that usually contains a grid and contains columns or bars--is called the "Plot Area".

The outer area near the perimeter of the chart is called "Chart Area". Would you like a color out there? Click Chart Area and then on the Format tab, consider the possibility of changing the styles here. As we slide over these choices, you could see what's happening in the background. If you're a little bit unsure as to what it is you want to use here, well, you might spend a lot of time looking at some of these choices, so you make a choice perhaps. The inner area, maybe you want that to be a contrasting color, so you click there and make a similar choice for the inner area, recognizing quite a few different choices here.

When it comes to other kinds of formatting, you might click here and say, "Well, I want that to be bold, I want some other option here", you might consider going to the Home tab and choose "Bold". So now the text is bold. And use some of the other features available in the Font group on the Home tab. So formatting is certainly important because you want your chart to look a certain way. First approach, again, on the Design tab, choose a Chart Style, after making that choice, go to Quick Layout-- consider some of the options here that will allow you to place the titles and the labeling information appropriately.

Those are the two kinds of features that you want to use to make formatting relatively straightforward as you work with charts.

Each part of an Excel chart has a name and although it's not important to memorize those, you want to get used to the idea that a chart is comprised of various elements. In this worksheet called ChartElements in the 07-Charting workbook, if we select the chart--it's depicting the data in columns D through I--depending upon where we click and how we move the mouse around, words pop up on the screen. For example, I just slid the mouse into the area where the columns are and we see the term "Plot Area"; slide up here, we see the term "Chart Area".

Point to one of the columns, we see that it's part of a series; point to the data along the left hand side that's the Vertical Axis; down below, we have got a Horizontal Axis and so on. If there are gridlines, if we point to them, we will see that we have got possibly major gridlines and maybe minor gridlines. If the Format tab is active and it will be if the chart is selected--it's part of the Chart Tools ribbon--the upper left corner in the current selection group contains the name of the current element, the one that's selected.

If you click on the chart and start using the down or up arrows that's another way to see the elements--no real reason to do this except possibly to take stock of what can be selected--there for example are the gridlines. And we see that in the upper left hand corner, there is the Title, Chart Title and so on. We sometimes want to make changes. For example, in this chart we have got gridlines here, but maybe they are not strong enough, they are kind of weak-looking, so we might want to make changes simply by right-clicking on one of the gridlines. If you right-click a chart element, you will get a menu that encloses the word "Format" followed by the element that you had clicked, for example, Format Gridlines. And that activates a dialog box over on the right hand side with many choices depending upon which element you have selected.

In this case I want to make the lines thicker, the term is "Width", it's currently 0.75, I will change this maybe to be 2. You can see the lines, getting thicker already. That's what it would look like if it were three, maybe two is better. So we'll make a choice here. And we are done, and we close the dialog box. Maybe we don't like the way the scaling is done here, maybe someone else has been working with this chart, why does the scaling go to 1000, looks like itonly needs to go to 700 or 800 or so. So we right-click the Axis area and choose Format Axis, again, activating the dialog box over in the right hand side.

It shows us we have got a Minimum and a Maximum, we can go with the automatic settings--looks like someone has altered this a bit--we might change this to 800 or maybe change it to 900; simply do a reset maybe, see what happens. What happens now? It looks like it goes to 800. We could override that, we might put in 750. So we have got some control over this. And the idea here is not to create "make work projects", but to suggest that we do have some options for controlling the display here. So, right-clicking any element leads us into some other choices.

Right click Chart Area and choose Format Chart Area and we see some choices out there too.Those tend to be mostly visual, but we sometimes want to make some changes. Now, there is another approach to this too, when a chart is selected, of the three buttons on the right hand side, the top one plus indicates Chart Elements. Add, remove or change chart elements such as the title, legend, gridlines and data labels. Now we haven't even seen that term "data labels", maybe, what does that mean? As soon as we click the choice, we do see the Chart Elements that are currently active.

And we don't see anything about data labels--what are they? Let's go here. Well, immediately we see something happening on the screen. Now, there is an arrow to the right, let's click it,center, and look what's happening on the chart or inside end. The data labels that we might want to consider using can be placed on the columns here. As we look to these choices, we decide whether we like this or not, I think we probably wouldn't care for that one; make a choice here. That's provided of course, we do want data labels. Looking a little crowded there.That might work better if we had fewer sets of columns here, fewer series, but nevertheless that's a choice you might want to make.

So from time to time, I think you do want to explore these to see what they might do to make this chart look better. Data table, probably not a good choice here, it simply replicates the data.This might be a good choice if you have a chart on its own sheet. Axis Titles, off to the left and bottom we don't have them right now, maybe we want them. You make decisions about how you want to change the appearance of the chart. And this might be a good starting point because it does alert you to some of the terminology. It does give you some ideas. We do have Gridlines here, but you may or may not have been aware of the idea that with gridlines can come also--by clicking the arrow here--Primary Major Horizontal which we have seen; how about Primary Minor Horizontal? Now those are very faint, I can see them you probably can't.

But I could, if I wished, then select these minor horizontal lines and make them thicker or use a different color. And again, I think the danger here is not to get bogged down in features thatyou never even knew about, but to give more impetus and weight to the visuals in the chart, make the changes that you want. I think the gridlines actually look pretty good here, so I am going to bring them back. Not too sure about the data labels, but we can leave them there for awhile. If the chart gets bigger or if we decide to show a fewer series, maybe those numbers aren't quite bumping into each other so easily.

We could also right-click one of the numbers and choose Format Data Labels and possibly make some changes there too, either going to the sizing and maybe reconsider the positioning too. So lots of choices here for controlling the various Chart Elements that exist within Excel Charts.

If you're interested in creating a quick visual representation of data without creating a full-fledged chart, you want to be looking at Excel's feature called Sparklines. We've got some data here in columns D through P. If we select data like this, as we often do in Excel, we do see the Quick Analysis button pop-up. Let's click it and there's the choice called Sparklines. There are three kinds of Sparklines. Actually, one is a Line, and we see the preview already or Column or Win/Loss, which in this case we will not use, possibly Column or Line, so just click Line.

What do we have here? That's a depiction of what's happened over these 12 months. When these are selected, we have a Sparkline Tools ribbon and a Design Tab, and so we might want to make some quick changes here. Nothing wrong with that really, but maybe this will look better if we change the Sparkline Color possibly even the Weight, meaning the width of this. So now maybe that's a bit more prominent. So we get a quick read on the data here. In this case too, it might make more sense if we use our Zoom slider bar, zoom in a bit to see what's happening there.

Now, to enhance this, you might also want to consider on the Design Tab, showing just the High Point or the High Points and the Low Points, maybe all the points, the term is Markers like that, looking a bit crowded. That might look a little better, if we go back to Sparkline Color and perhaps change the Weight of this to be a little bit thinner, looks a bit better that way. So we can quickly see what's going on during this particular set of data here. Another option which we saw briefly, and we can get to it on the Design Tab here, is to change this to be a column and that might make sense too.

We do have the High and Low Points selected, maybe not as necessary now, but that too gives us a reasonable visual depiction of the data. Now, at certain times, the data you're selecting doesn't automatically fit into the quick analysis ability to create Sparklines. So suppose for example, we wanted a Sparkline here to show what's been happening. Now, here, the data is oriented vertically. If you choose the box here and go to Sparklines, the choice here comes up to be nothing. It tries to put the data on the right, so let's not do that.

We've got our data selected, then we can go to the Insert tab and choose Sparklines, this time we use a Line as well. Let's start with Line. It asks us, where do we want this to be? We've selected the data that's the source, where do we want the Sparklines to be placed? We'll click in cell B13. click OK and there it is. Organized differently and perhaps not as commonly seen this way as we saw over here, but nevertheless, that's a reasonable visual depiction of what's happened to these numbers over this nine-month period, and here too, you might want toconsider making that thicker.

Now, we've got some other data off to the right here and this has negatives in it, so let's consider putting in Sparklines here; but this time, let's explore by way of Insert and Sparklines, possibly Win/Loss because we've got negatives in the entry here. How might this look? Here, somewhat differently than the previous example, I selected the area where the lines are going first, so that's already selected, but now it's asking me where is the source of the data? So clicking in the upper panel, then selecting these cells right here and then clicking OK gives us this look.

The red, of course, represents the negatives as we see them here. Depending upon the nature of the data, these might be a better choice. By the way, if you make the column wider, it's easier to read perhaps, but we always have that right when we're doing these to consider how this might look as a Column or as a Line. Now, here's something you want to be a little bit careful with, the idea that you can change the meaning of this--not by changing the values, thatwould be completely unfair and perhaps illegal--but look what happens here if we make this column wider.

It tends to flatten out the changes that we're seeing. We could also go back to column Q, by the way, try it over there, it's going to have greater implications over there for that previous set of data we were using. Let's change this by way of the Design Tab to Lines, and then possibly make this wider; and that gives us a completely different picture--as I press Ctrl+Z-- as to what we saw here. Here's another possible option, although less likely. If I select rows 4 through 7 and make them taller, watch those lines change, and that certainly accentuates the idea that there's been a lot of change through this period.

So making columns wider or narrower and rows taller or shorter, does change the impact of what we're showing here with these Sparklines--so think out those issues a bit. I'm going to press Ctrl+Z here a few times to return these to the previous display. Let's go back to these again too. The idea here with these pluses and minuses, if we are choosing Lines and that certainly is an option, we might also want to consider on the Design tab here, choosing Axis.

If we show the axis, that accentuates the idea that we have crossed the "zero boundary". So, we don't necessarily have to use the markers. We can certainly do that or the negative points.Use the markers maybe or maybe not, but we do see where the data falls below the line. The idea here is, with Sparklines, we want to get a quick, good, visual depiction of the data in question without necessarily showing a large chart and this gives us more freedom within a worksheet here, to show other sets of data as well. With this data here, it may or may not make sense, but you might consider putting Sparklines below the data.

Now here Lines perhaps wouldn't be as good, but we can check this out and decide. After selecting the data here, Insert Tab, Lines and the Source Data--the Data Range, this data right here--and OK. Now, we see a depiction of each one of these. So what we're seeing here of course is reflected right here. The difference here is, and this could be considered actually better, showing line charts across different regions, as if they were somehow connected, in acertain sense is misleading, but showing lines here--and in each case it's about years from 2008 through 2012--perhaps this is a better use of the idea that the data flows from year to year.

So even though we're not seeing any indicator on these actual Sparklines as to what the various points mean the points here mean different year entries for Northeast and Northwest and the other regions as we click across here. But the lines here, I don't think you'd really want to connect them, so I think you can make a strong case for saying, "Lines are not the best choice here, maybe columns are better". So you can see the variations here and the idea behind Sparklines, a quick visual representation of data in a single cell.

When you work with a large list, as in the data that we're seeing here on the freezing sheet, one of the problems you run into and particularly if you're not familiar with the data, is that as you scroll up and down the data, you lose sight of where the column indicator is. We're not seeing row 1 anymore. Similarly, sometimes we scroll rightward and we see some data and we wonder,well, who is that person? Which salary are we looking at here? We don't see the names out of column A. Sometimes we might want to see not only the name, but also perhaps the name and the building where the person works at or maybe some other information.

We're talking about the potential ability to freeze rows or columns, so that we can always see certain information on the screen. So, if we only want to freeze the top row, the active cell can be anywhere within our data and we go to the View tab, choose Freeze Panes and Freeze Top Row. It keeps the top row visible while scrolling through the rest of the worksheet. Click it, a slight visual change here, very slight. The row separated between rows, 1 and 2 is slightly darker than the others, but as we use the mouse wheel or the scroll bar or the trackpad to scroll up and down here, we always see row 1.

Now it's possible that in a different situation with different kinds of data, maybe we don't care about row 1, but we do care about always seeing column A, so let's disable this feature. Many of the features on the View tab, you cannot reverse with the Undo command. Let's go back to Freeze Panes and Unfreeze the panes. Now consider possibly freezing the first column, a slight visual difference there with a darker gray column separator. Now as we move rightward either with the arrow keys or we scroll in the lower right hand corner, we can move rightward and leftward, we're always seeing column A. Click again here.

Let's remove that feature, Freeze Panes, Unfreeze the panes. Now, if we'd like to see row 1 always and column A always, we'll position the active cell in B2 or if we always want to see columns A and B, no matter what as well as row 1, we'll Click in cell C2. Let's suppose it's just the top row and column. Click in B2, Freeze Panes and then simply Freeze Panes. Keep rows and columns visible while the rest of the worksheet scrolls based on current selection.

That's what we want. Now we've got a two-way freeze in effect. We can scroll up and down and always see row 1. We can scroll left and right and always see column A. So it's great feature forworking with large lists of data when you definitely need to see specific information all the time.

When working with large worksheets, it may be advantageous to see different portions of the worksheet at the same time. In this list here, we've got some entries here for the Admin Training group and later there's a Professional Training group. We might want to see some of those names together at the same time. You can split a screen vertically or horizontally or even both ways. If we want to split the screen top bottom horizontally, put the active cellssomewhere in column A, no matter where you happen to be on the screen at any given time more or less around the middle, somewhere out here for example.

And then, on the View tab in the Ribbon, choose Split. We split the screen top bottom. Here's the split line right here. We can move this up and down if we wish. Now recognize there's a scroll bar on the right-hand side that lets us scroll above the split line. Nothing is scrolling below the line though, so maybe we want to see some of those Admin Training people over there that we were looking at earlier. There they are, some of those people. Meanwhile, below the split, we might want to scroll to see the Professional Training group, which is much farther down on the list somewhere in the 500 row area somewhere down there. There we are.

We're seeing some of those people. So we're seeing two different portions of the worksheet. Up here, we're looking at rows 97 through 104, down below it's rows 533 down to 540. By changing the Zoom factor possibly, we'll see more rows. The main idea though, is we'd like to see data from different parts of the worksheet simply by splitting the screen. That's a handy device to be sure. Now at some point, you don't need this anymore. Remember you can move this up and down. We can also get rid of it simply by Double-Clicking and once again, if you wish to split a screen top bottom, in a so-called horizontal split, put the active cell in column A, more or less mid-screen and apply Split.

Now, maybe not as obvious, but a similar feature to split the screen left right or vertically also exists. The idea here might be, you might want to compare some of the data or look at some of the data that we see here along with some of the data way off to the right. Now we could certainly hide columns to get there but this too might be an advantage to us. Maybe we don't care too much about department, we'll make that a little bit smaller for the moment, ignoring it pretty much. We want to split the screen maybe left to right. This time, we want to put the active cell in row 1 and then split.

We split the screen left to right. So, we've got two scroll bars at the bottom, allowing us to scroll on the right side of the screen, left to right. We could even possibly be showing columns A, B and C here. And the scrollbar for the left side of the screen down here, we can be lookingat the data that way, so that's a little bit unusual perhaps, but nevertheless we're looking at Salaries and Ratings next to Employee Names. We're splitting the screen left-to-right. As we scroll up and down, they're always in sync, so that's another possibility, splitting the screen left to right. Now if we want to get rid of this, we can simply Double-Click the split line.

Now, what happens sometimes is people are not quite aware of this feature. They've got the active cell, this location, that location and they say, I want to wonder what Split does. They see the description: Divide the window into different panes that each scrolls separately.Sounds interesting, Click. Now we've got a two-way split. If you activate split, when the active cell is not on the edge, either the top or left edge, you get in effect, four different panes here.Now you can move this intersection point over here if you wish or wherever you wish, but with the idea of having two vertical scroll bars on the right side, two horizontal scrolls on the bottom, it's a little bit chaotic as you work with this.

Now, to prove a point, one thing you could say is that in one portion of the screen or the other, I could press Ctrl+Down Arrow here maybe and ultimately go to the very bottom of the worksheet, very last row and there's the very last column. So I can see the last row, last column cell right there, in the upper left-hand corner, I might be looking at A1. I'm not sure if that proves a strong enough point, but with a four-way split, in other words having a vertical and horizontal split line at the same time, you could conceivably view any two cells in the worksheet at the same time no matter where they're located.

I think it makes a lot more sense in these examples to either have a horizontal or vertical split and more often than not, you're likely to see the horizontal split. So I'm just going to Double-Click the vertical line here to get rid of that vertical split and now we're left with a horizontal split, which I think makes a lot more sense. Now, we can view our data this way. At some point, we don't really need the feature, we can either Double-Click this or possibly go back to Split and simply, it removes the split by clicking it. So splitting the screen either vertically or horizontally gives you the option of seeing data from different parts of the worksheet at the same time.

In the worksheet called Outlining, we're seeing Budget Projections for 2014 and this sheet contains a lot of data. Now imagine if you'd like to display this information for presentation. It contains 12 months, it's got quarterly totals, a lot of information here and perhaps overwhelming, too much for a visual display and what you might want to consider doing ahead of time is hiding, for example, columns B, C and D and showing the first quarter totals and then doing the same kind of thing for the other months as well. You'd like to be able to present the data in a more compact way, not showing all the detail.

Similarly, looking at the rows here, maybe you don't want to show that detail or this detail here.You do want to show some of those total rows. We've got expenses down here. You might not want to show that data as well. Now, hiding columns and rows doesn't take that long, but if you're making a presentation, you might want to have the flexibility to quickly expand andcollapse the data and not necessarily show the audience the various Excel commands needed to get there. What if we could quickly Click a button or two and watch this data expand or collapse? Now there's nothing unusual in this worksheet really.

Column E has quarterly totals as does column I, and a few others to the right. So there are formulas in column E, not in every single cell as we can see, but there's one here that's tabulating the sum of these and so on. Now, just by having these in place, Excel's outlining capability will pick up on that. It will allow us to essentially collapse this data and possibly show only the quarterly totals or maybe even show only the grand total. And so, you might think that the data in this worksheet is rigged or has to be set up in a special way, it isn't.There are certain rows here, for example, row 7 that tabulate data from the two cells above.

There's a formula right there, simply adding the two and we see other kinds of rows here as well, not nearly as many as we have for columns perhaps, but nevertheless they're there. So how do we activate the outlining capability here? The active cell is somewhere within the data.We go to the Data tab and in the Outline group, we have an option called Group. Click the drop arrow and choose AutoOutline. Look what happens to the worksheet here. We could see some symbols up above.

We see some vertical numbers 1, 2, 3 and some horizontal numbers over here, 1, 2, 3, 4. I'm going to Click the 2 where we see the vertical 1, 2, 3. This collapses the display so that we only see the quarterly totals. If I Click one, we're only seeing the grand total. Now I haven't done anything with the rows just yet, but if we Click number 3, we are going to see the horizontal 1, 2, 3, 4, this will collapse the detail somewhat, but not completely. We've got interim totals there.

That's why we have four instead of three levels. We'll Click the 2 and we're not seeing too much of a display there right now, that's it. Click one and now we're down to one cell, which probably we wouldn't use very often. So a more typical display for this data might be Click the 2 or the 3 here for the horizontal data and then Click the two up top where we see the vertical 1, 2, 3 for the rows this way. Now, depending upon how you're making the presentation or what it is you want to show, you could imagine saying or thinking, those Outlining symbols take up alot of space or a couple of options.

You may be familiar with the idea that you can quickly hide most of the ribbon except for the tabs simply by Double-Clicking the current tab. So if we Double-Click Data at the top of the screen, that collapses and gives us a bit more room. If you want to hide the Outlining symbols, you want to press Ctrl+8 and that hides them and you can get them back with Ctrl+8 as well, so maybe you want to keep the display this way. Another option here, too, is with those arrows present, so we'll press Ctrl+8 again, bring them back. If a discussion begins around what happened in the third quarter last year, if someone is interested in seeing the detail for the third quarter at least momentarily, you can Click the plus right here that's above the third-quarter and what happens? We see the detail for the third quarter for a while.

We'll leave it there for a bit. The discussion has run its course. What we do now? We collapse it. Similarly, with the data down the left-hand side, if we don't necessarily want to see the Gross Profit detail broken out this way, Click the minus, collapse it that way. Do we need to see those expenses? Well, we might at different times and at other times, maybe collapse them so we're looking at a list like this. And so in the different ways, we can expand and collapse this view. Once again, getting of rid of those symbols, it's Ctrl+8, bringing back, it's Ctrl+8.

If we Click the three in the vertical 1, 2, 3 as I just did, we see the vertical display expand.Click in the four here, we're back to the original display. If you want to redesign the layout of this particular worksheet, you want to exit this procedure and here we do want to bring back the data tabs so we'll Double-Click Data and then go to Ungroup and choose Clear Outline. And we're back to a normal display. This is potentially an ideal vehicle if you're making presentations of complex data.

So, rather than hiding the columns, let the outlining capability work for you as you use these features available from the Outlining group on the Data tab in the Ribbon.

If you're working with a workbook that has more than one sheet, you sometimes want to see a portion of each of these sheets at the same time. If you're working with different files at the same, you might want to see a portion of each of those files at the same time. In this movie, we need to be viewing the file 09-01-RegionalSales and two other files. When you do you have more than one file open, on the View tab in the Ribbon, the Switch Windows button shows you currently open files. The other two files we need to see are 09-01-Home Product Line and 09-01-EmployeeTable.

In this workbook RegionalSales, suppose we want to see a portion of the Midwest and the South and the West sheets as well as the East sheet that's currently showing. On the View tab, if we want to see additional windows, we need to Click New Window three times. Back to the View tab, New Window, Back to the View tab, New Window. We now have four windows available, View tab, Arrange All and we do want to make sure in this case, we want to view windows of the active workbook. There are four choices here, Tiled is usually the best. Click OK.

We now have four windows. It's looking very crowded. One thing we can do to see more of each worksheet is to Double-Click any of the tabs, for example, the View tab and that collapses the view. I'll do this in the window on the right side too, Double-Click View in each case here. Lower right-hand corner, Double-Click View and lower left corner, Double-Click View.It's quite possible we're looking at the same worksheet in each window so that's not so helpful so in the upper right window, let's just Click there and view the Midwest sheet. In the lower left corner, Click there and Click the South sheet, lower right corner, Click the West sheet.

So now we're seeing four of the different sheets at the same time and this layout might be ideal if we're simply comparing the information, looking back-and-forth or if we need to copy information, it's best to have the sending and receiving areas all visible on the screen at the same time. Now we don't always want to see four windows. Maybe we only want to view two, so we can close down these windows. It seems like we're closing a file if we Click the X in the upper right-hand corner of one of these windows. I'm about to do this in the lower right-hand corner. So just Click the X and if there are other files in the background, we might be seeing a portion of them, I'll go to the upper right, Click the X there and now I want to view just these two windows so I'll back to the View tab in the ribbon and choose Arrange All.

We might possibly want to arrange these windows in a horizontal arrangement. Click OK and now we see the windows this way. At other times maybe it's more advantageous to view these vertically, View tab>Arrange All>Vertical. You'll be the best judge of which layout works best for you. Now we do have two other files open. Sometimes we want to see portions of each of those files. Currently, we see two windows of the same workbook. We can close either one of these and we're back to one window here.

Now, let's consider viewing a portion of each workbook. Back to the View tab, we don't need additional windows here, simply Arrange All, this time make sure that you un-check the box for Windows of active workbook. You can choose any arrangement you want. Tiled tends to work best most of the time. Click OK and now we're seeing portions of three separate workbooks.Whenever there's an odd number, one of these will get a larger share of the screen and so if for example here, if we want the Employee Table, which is now on the lower right-hand corner to have greater dominance here, just Click there and then go to the View tab, choose Arrange All>Tiled>OK and that's now going to be on the left-hand side.

And here too, when you're comparing information in different files or you want to copy-paste information from one file to another, this is the ideal arrangement. At any time, if you simply want to focus on one of these, just maximize using the box just to the left to the X in the upper right-hand corner, the Maximize button, to make that particular file become full screen. So as we've seen, you can see different sheets of the same workbook by first creating new windows and then arranging the layout or you can see portions of multiple workbooks simply by clicking the Arrange All button.

We're using the workbook 09-02-RegionalSales. The sheet names are appropriate except for Sheet3 and so changing the name of the sheet is something you want to be able to do quickly and easily. You can access many features related to sheet tabs simply by Right-Clicking asheet and that brings up a menu. Here, of course, is a choice for Rename. It's much faster, however, simply to Double-Click a sheet tab. This Sheet3 for example contains Annual Performance Scores. It contains some sparklines in column H . Let's just change the name of it by Double-Clicking and we could type anything we want.

Why not Annual Scores? You can have spaces in sheet names. They can be up to 31 characters. Simply type in the name you wish and press Enter. If we need to add a new sheet, we can simply Right-Click and we're about to insert a new sheet. If we choose Worksheet here and then OK, a worksheet just to the left of the sheet that had been selected. But there are two faster ways, if we want a new sheet to the right of any worksheet, suppose we're going to add a new region between South and West. Maybe it's going to be a mountain region.

We want a new sheet to the right of South here. Click the South tab and then to the right of our sheet names, we'll see a plus, simply Click New Sheet and we got a new sheet here called Sheet4, which we will rename eventually, Mountain. And there's the keystroke shortcut method as well. We might want to put a summary sheet to the left of the East sheet. If you press Shift+F11, you will automatically get a new sheet to the left of the current sheet. All sheet actions, except for changing tab colors, cannot be undone or you cannot use the Undo feature.

That's particularly meaningful if you're about to delete a sheet. Maybe I'm not thinking too clearly or maybe the thought has occurred to me that I don't need this sheet, I Right-Click and choose Delete and now I see this message, You can't undo deleting sheets. You might be removing some data. If you don't need it, Click Delete. It is a strong warning and so if I were to press Delete here, I can't use the Undo capability to bring back that sheet. What I might need to do in a case like that if I had second thoughts, is simply close the file without saving it and then reopen it.

Of course sometimes that happens after you've done a lot of good things along the way. So you want to be very careful about deleting sheets. So as we've seen, we can rename sheets, we can insert sheets, we can delete sheets either by Right-Clicking or using some of the shortcuts I suggested.

You can easily move a worksheet to a different location within the same workbook or you can copy of a worksheet within the same workbook. And at times you might even want to change multiple sheets of the same workbook at the same time. In this workbook 09-03-RegionalSales we want to move the Midwest sheet to the right of South, we simply Click that Tab and drag it rightward. Sometimes we want to make a copy, maybe this data here is questionable, we might want to work with this separately, if we want to copy a worksheet, there are two ways to do this, we can Right-Click the sheet tab and choose Move or Copy and then create a copy and then Click OK.

We also can position it while we are doing this if we wish before a certain sheet, the example here if I do nothing special, it will put this before the East sheet, copy of Midwest. A much, much faster way is that if I want a copy of Midwest, I will start to drag it and hold down the Ctrl key, as I do, there is a plus that appears within that icon and I can put this anywhere I want, perhaps I will put it off here to the side after Pacific or after Sheet1, wherever I wish. In doing this though, you must let go of the mouse first. So a Ctrl+Drag as it sometimes is called, is a much faster way of copying a worksheet.

Now it's also possible that you might want to copy this sheet, this time the Pacific to a different workbook that's open or possibly to a brand new workbook by Right-Clicking a sheet tab and choosing Move or Copy. We can create a copy, To book. If we have other workbooks open, we will see that here, but we do have the choice New Book. Create a copy in a new book, Click OK. And we now have a new workbook open it's going to be called Book1, Book2 et cetera, we save it at the top of the screen and here we have a copy of that Pacific worksheet from the other workbook that we started with.

And pressing Ctrl+Tab will take us back to the other workbook. Now if we want to make changes to these, let's consider we might want to make change to the East, the South, the Midwest and the Pacific. For the moment we will be ignoring Midwest. If we want to select multiple sheets, if there is a cluster of them, we can Click on the first or the last sheet and then using the Shift key, Click on the Sheet at the other end. So for example, if I have selected Pacific and I want to make changes to the East, South, Midwest and Pacific sheets, I will now hold down the Shift key and Click East.

And anytime you have grouped sheets they have a different look to them and be sensitive to the notation at the top of the screen. You will see the word Group in brackets whenever you have got two or more sheets grouped. So, these four sheets are grouped. Imagine that on all four of these, we want to have an empty row between the current rows, 6 and 7. So I will simply Right-Click row 7 here and insert. And for the moment I am looking at the Pacific sheet and we see the new row, but if I Click Midwest, you see the new row and South and East and they are still grouped.

And I remember not that long ago, changing a number here, thinking that I was only changing a number say for East here, and because these are still grouped if I do change that number, what happens, this number will change on all four sheets at the same time. You can ungroup the sheets by either Clicking on one of the sheets that's not part of the group or simply Right-Click and ungroup the sheets. So as we have seen, you can move a sheet, you can copy a sheet, sometimes even to a new workbook or to another open workbook and you can group sheets and make changes to multiple sheets at the same time.

Sometimes you need to write a formula that gets data from different sheets within the same workbook. At other times, you might need to write a formula that gets data from another workbook. In this movie, we need to have two files open, one of them, 09-04-RegionalSales,the other one, 09-04-EmployeeTable. Let's go to the RegionalSales file first. There's a sheet called RegionalTotals and we want to get Retail Totals there in cell B2. Each of the four sheets preceding this, East, Mid West, South and Pacific, all have a Retail Total in F4 and then below that a Wholesale Total and an Internet Total.

So let's start where we want to put the formula, that's Regional Totals, in cell B2 and when you write formulas that get data from different worksheets or even different workbooks, by all means, Click the various locations using the mouse or the trackpad rather than typing in file names and sheet names. We begin with equal sign here. We'll Click the East sheet and then Click the cell that has the total we want, that's F4. So the formula starts to build and you can see it in the Formula Bar. We'll put in a plus then go to the Midwest sheet.

Click that same location and then a plus, same thing with the South and so on here. And then Pacific and finally Click that F4 location and then Enter. And we've got our total and as I Double-Click here to see this more easily, we can see what's happened. Recognize how and it's certainly not intuitive that sheet names are followed by exclamation points. Now something else could happen too and it's not bad, but just be aware of it because it does make formulas look different sometimes. If the sheet name has a space in it, now none of these do, but I'm going to change the name of Pacific to Pacific Rim.

So I'll Double-Click the sheet tab, Click right after the C at the end there, add a space and put in Rim. So I've changed the name of that sheet. How does our formula look now?RegionalTotals, Double-Click. Because there is a space, we also see single quotes so Excel does accommodate that and it certainly is okay to have spaces, but in certain kinds offormulas, you can imagine how this clutters up the formula and makes it look a little bit more crowded. So I'm not saying it's wrong, but just do be aware of that. It's another strong recommendation as to why we should not be typing sheet names.

If you simply Click the sheet name as you're writing the formula, Excel pops it right into place properly. So I'll make another change here, I'll simply go back to the Pacific Rim. I'll Double-Click and Click out there and change the name back to Pacific, Enter. And then our formula, of course, has been readjusted. In this example too, since each of the sheets has Wholesale and Internet right under this, I can simply Double-Click or copy this downward and we've got ourtotals for Wholesale and our totals for Internet as well. So we've written formulas that gather data from different sheets of the same workbook.

Now let's imagine that in the other workbook, I'll press Ctrl+Tab to get there, there's a sheet tab called Furniture Sales and in cell J8, we want to get the Couches Total from the other file, the East sheet, the West sheet and so on. So we're going to see a similar formula, but it's going to be a little bit different and the way we get there is going to be a little bit different as well. Keep in mind, the other files, I press Ctrl+Tab, on the East sheet, Midwest, South and Pacific sheets, all have a total for, for example, Couches in cell B8, there it is right there, same thing with Midwest, South and Pacific.

So, as we jump back into the other file, Ctrl+Tab again, it's the fast way to get there and here too we will not be typing much. We certainly will begin with the equal sign and now what do we need? We need to get our East total and you can get to the other workbook by way of Ctrl+Tab or we can go to the View menu, choose Switch Windows, go to Regional Sales and then on the East tab, Click B8, put in a plus. Now, as you watch the formula being built in the Formula Bar, you see that the file name is referred to as well.

So after putting that and we then need to go to the Midwest sheet. Click on that same cell, a plus and then the South sheet, Click B8 again and a plus and Pacific sheet and Click B8 and we're all done. Let me scroll to the right here a bit so we can see this even better and I'll even zoom in too so that we can see what's happened here. The formula certainly is not something you would have typed as I Double-Click it. So what are we seeing here? We are seeing the name of the file in brackets and then the sheet name and then the location of the cell.

This happens four times here since we're gathering data from four separate sheets. So the formulas certainly look a lot more complicated and involved, but it really isn't. In this case, it's really gathering data from four cells and there's our total right there. Now this is going to look quite a bit different though if we close the other file. If you are writing formulas that get data from other workbooks, you definitely want to have the other file open at least as you're creating the formula, but what happens if we go to the other workbook? I'll press Ctrl+Tab again and close and save this workbook.

So I'll choose File>Close or possibly just press CTRL+W, either way. File>Close and do I want to save this? Yes, I do. We've saved it. Now look at this formula as I Double-Click, because the other file is closed, we need to know -- have a reference as to where it is. It's on the C Drive, within a folder called Users, within a folder with my name, Desktop>Exercise Files and of course, this will look different in your environment if you've saved it in a different location, but all this that I'm highlighting right here represents the content of one cell and then we see this being repeated for the three other cells.

So, the formulas look incredibly complex and involved, but they're really not when you think about it. So we've got a formula that's gathering data from different sheets in a completely different workbook and as we saw earlier, we can also write formulas that gather data from different sheets of the same workbook.

If you work with files that have formulas that get data from other workbooks, those are called links. And you need to know where those links are and how to find them. We need to have two files open in this movie, 09-05-EmployeeTable as well as, I'm pressing Ctrl+Tab, 09-05-RegionalSales. Right now, there are no linkage formulas. How do we know that? First of all, in Regional Sales, if we go to the DATA tab, the term Edit Links is grayed out and we cannot select it.

Therefore, there are no formulas in this workbook that get data from other workbooks. Let's press Ctrl+Tab and go to the other workbook. Are there any formulas here or on the DATA tab?Edit Links here is grayed out as well. So let's create a linkage formula between the two. In cell J8 here, we want the Couches' total. Equal or press Ctrl+Tab, go to the other workbook and we're going to get from each of the four sheets here, the Grand Total for Couches. It's in cell B8 so we'll simply click there, click Plus then do the same thing with Midwest, click that cell and a Plus and South, same cell and Pacific, same cell and Enter.

We've got our total here. Now, DATA Tab>Edit Links and as we click this option here, recognize Edit Links tells us that this workbook, the one we're currently using, is getting data from 09-05-RegionalSales below this we see the option Startup Prompt. In the future, when we open this file, let's imagine we're about to close it, when we open this file in the future, it would be worth considering a Startup Prompt. The Startup Prompt box gives us three options.

The last option says, 'Don't display the alert and update the links.' I think most people would not want this. This simply means if we open this file again, nothing special is going to pop-up on the screen. We won't even know necessarily that there are links and they will be updated.And the one above it is hardly much better. It doesn't display the alert and it does not update the links. The first choice is the best one and it's worded a bit strangely. It says, 'Let users choose to display the alert or not.' That doesn't mean that every time we open this file in the future, that there's going to be a prompt asking us if we want to see the prompt anymore.

It's simply going to ask us if we want to update. I think this will be the preferred choice for most people. Let's click OK and close this. Let's imagine what happens in the future if we were to open this file and remember the total here is 11,660. So we're going to close this file and save it. Click the Save button in the Quick Access Toolbar is certainly one way and we can close this with Ctrl+W. Let's imagine we're working with the other file now and I'm going to make a change here to Couches. I'm going to make that to be 2000.

If the other file were open, that total of 11,660 will have gone up substantially, but it's not open right now. So we're going to save 09-05-RegionalSales. I'll click the Save button up there and then press Ctrl+W to close it. Now I want to open the other file and I can go to the File tab in the Ribbon and down below under Recently Open Files, there's that Employee Table so I'll open this again. For the first time, we are seeing this prompt and remember that 11,660 has not yet changed.

Our prompt says, 'This workbook contains links to one or more external sources that could be unsafe.' Do we want to update? Yes, we do. Watch the 11,660 change. It's changed to 12,510 even though the other file isn't open. The formula is still in place and of course on the Data tab, Edit Links is there reminding us the source of that formula. Now, what happens if you're a different user or it's you a few months later, you open this file perhaps you saw the prompt and you said update, but you forget where the formula is.

Maybe you're looking at this sheet or that sheet or maybe you've got more sheets in the meantime and you want to know where linkage formulas are in this workbook. Let's go back to where this particular formula is. I'm going to Double-Click here. What is it that's different about these kinds of formulas? Well, there are two things are different. It's probably unlikely that you're ever using a left bracket or a right bracket in other parts of a workbook. Now you certainly can and that option is open and if you work with tables you might even have formulas with brackets in them, not parentheses necessarily, but brackets.

What else is unique here? We are seeing file names with .xlsx. Now possibly it could be another Excel file, but let's say that .xls is probably unique or .xl. So it's highly unlikely that you would see that combination of characters anywhere else. Let's imagine that you have opened this file, you either forgot this or maybe you're a completely different user and you've seen by way of the Data tab, that there are links to other workbooks, you've seen that but you don't know where the links are.

So what might you do? You might be on any worksheet here, Right-Click and select all the sheets and then go to the Home tab, and the extreme right button, Find and Select, Find andlet's look for all occurrences of .xl. Find all of them. In this example, there's only one so we see that there and we see the address. In different situations, you might see a pretty large list here. All cells that have the .xl combination are appearing here in this list.

So that helps us track these down. Now, links are one way. In this workbook, we've got a linkage formula, one or more, getting data from another workbook. Suppose we open the other workbook. I'll go back to file and open Regional Sales. Does this workbook have any formulas that get data from other workbooks? Data tab, Edit Links is gray, we cannot select it. There's no indication in this workbook that there are formulas elsewhere using this data so the linkage concept is based on the idea that in those workbooks where there are linkage formulas we can find them, but there's no way, when looking at a source workbook, that we can track the link in the opposite direction.

It gets a little tricky at times when you're trying to explain this to others but in our example here, we've got two workbooks, the other workbook as I press Ctrl+Tab and go back to it, has one or more, in this case only one, linkage formulas that are getting data from another workbook. In any workbook that gets data from other workbooks by way of formulas, we canthose track formulas on the Data tab, the Edit Links command.

One of Excel's most powerful functions is the IF function. It's almost programming-like in nature. It gives us the ability to come up with alternate answers depending upon the content of certain cells or calculations that we're comparing it with. In column G, we want to establish a shipping cost that's going to be 2% of the total cost except in cases when the total cost reaches 1500. We see in cell C4 a description, "No shipping charge for orders over $1500" soto make this clear, I'm going to make column G wider and also use the Zoom slider bar so that we can see this a bit better.

Ultimately, column G doesn't have to be this wide. The IF function allows us to test a condition and then come up with two different answers in its basic form; "=if", we simply want to check that entry in F7 to see if it's greater than 1500. Greater than 1500, this does not mean if it's equal to 1500, it means greater than and if the person who's designing this says, well, I want 1500 to be the breakpoint, then we want to make sure if it's equal to 1500, that that doesn't get the charge also.

So you have to be clear on what it is you mean, so depending upon on what it is the designer means here, sometimes you'll use this construction, meaning greater than or equal to 1500, sometimes it's simply greater than 1500. Now as soon as we put in the, notice that the wording below this right now logical_test is in bold print, as soon as we put in the comma, the focus shifts to value_if_true, when this is true, we have an answer and it's going to be zero or blank.Let's put in zero here.

If the order's over 1500, there's no shipping charge. When that test fails, in other words when the cost is not over 1500, we're going to put into calculation 2% times the cost. Now, it might seem like a small matter here but 2% of some of these costs is likely to give us portions of pennies as our answer and what we need to do sometimes is round this right at the spot of creation. So the Round function, which is often used by itself, is here used inside of an IF function and it's not always used in the IF function, but here it's going to make some sense.

We want to take this calculation and round it to the nearest two decimal places, comma two. If we were dealing with larger numbers and wanted to round this to the nearest whole dollar, we'd use comma, zero. So here we're using the Round function as one of the answers in our IF function and so that needs to be embedded in parentheses as does the IF function itself, one more, out of parenthesis there. We should be seeing in our example here, 2% of that $1449, almost $30.

And there's our answer, $29. Check this out by dragging it across a few cells, making sure it works okay and we've got some sensible answers. Drag it into cells where we go over 1500.We should expect to see an answer of zero there or a blank as we see it here. Depending upon the number format this might display as a blank or a hyphen, dash as we see it here or possibly a zero but the answer, as we see it, is correct. The formulas in column H that I didn't refer to you earlier are simply taking the total cost and then adding the shipping cost on to it, to get the grand total.

Eventually, we'll just make column G wide enough, Double-Click. So we've seen how to use the IF function here in a very simple straightforward way. If a condition is true, there's one answer, if it's false, there's another answer.

One of Excel's most powerful functions is the VLOOKUP function. It has a companion function called HLOOKUP. V means vertical, H means horizontal. VLOOKUP allows us to look up information and compare it with the left column of a vertical table. In column E, we want to look up the reduction for certain items that have been priced in column D. For example, if the subtotal, as we see it here, is 1363, if we were looking in this table we would be saying for example, It hasn't reached to 1500 so it's not 4.5%.

It's at a thousand therefore it's going to be 3%. The VLOOKUP function has two major forms.One of them is an exact match. We don't need that here. That would be for situations for example where you're trying to look up text entries or ID type numbers or we're trying to find something exactly. We wouldn't expect to find this number, the 1363 or any of these in this listalthough certainly one or two of them might be. And so the idea here is when we use VLOOKUP on a table, that table can be in a different worksheet, it could even be in a different workbook, but certainly for ease of use in testing it, it's going to be nearby.

We might later move it elsewhere, that wouldn't be a problem or it might originally be in a different location but when it's right here, it's just easier to work with. VLOOKUP tables, for situations like this, must have in their left column, numerical information in ascending order.We'll discuss later what happens when these are not in that order. We want to use VLOOKUP right here. I'm going to make the column wider so we can see this better and zoom in a bit also using the Zoom Slider bar.

So in cell E3, now what if you have used a function but it's been a long, long time or maybe all you know about a function is its name? Rather than typing in a function and making lots of guesses and going back to books and trying to figure out how to make it work, what you might consider doing is clicking the fx, the Insert Function button to the left to the Formula Bar. So let's imagine we're about to use VLOOKUP here, we know maybe a little bit about it, we've heard about it possibly. We click fx. It might turn up in a list of recently used functions.

We could possibly narrow it down by referring to Lookup and Reference functions; maybe we'll go there or in the list of all functions, too. Lookup and Reference should be in this list. We'll scroll up and down, it's there alphabetically. There it is, a brief description of it here and we click OK. We actually see it being displayed here and Excel we'll build this for us as we look at it. What is it we're trying to look up? It's this value in D3, this charge for an order.

We're trying to see if there's a reduced rate for it. The table that we're looking at is off to the right and it's in these cells right here, so we will highlight those. Column index number, this throws people at first often. Which column of the table has the answers that we're looking for?What we'd like to come up with here is a percent. There's already a formula which we haven't seen yet in column F, it's going to use this percent to adjust the charge total by reducing the amount. So the column that has the answer is the second column.

So we put in the number 2 here. Now there are situations where you need an exact match. We don't need that here so the fourth argument of VLOOKUP is often not used if the data is approximate. We don't need to worry about that at all. We simply ignore it. So, we can simply press OK or Enter. We should have an answer here and there it is. It's 3% and that's what you would have guessed it is. It hasn't reached the 1500 level which would be 4.5. It's at the 1000 level of 3%. We do see the Adjusted Total here.

Here's the formula that works off of that percent and you see how it's set up. It's always best to test these out by dragging them but before dragging this, do we need to really make a change here? I think a lot of you know if you've worked with certain kinds of data if you're familiar with the idea of relative and absolute references, the reference to this table needs to be made absolute. Drag across this address of cells, press the Function key F4, that makes it anabsolute reference then we can copy this down.

Let's copy down a few cells just to get the feel of how this is working for other values and each of those should check out properly based on the table on the right. Recognize something about this table, it only goes to 2500, some of our values might go higher. So what happens in this case right here? This is well above 2500. It's simply reversed to the highest entry in the table which you've got to be much more careful with the low end of these tables. One of our amounts here is a $159. What if we started the table at 1% and our first amount here is $200? In other words we're saying there's no reduction until you reach 200.

There's 159 over there, what's going to happen now? We've got a problem here. So you want to make sure that in your VLOOKUP tables when you're using an approximate match that you do cover the lowest possible entries that might occur. I'm going to press Ctrl+Z twice to take the table back to its prior form. So we see what happens there when we are not covering the lowest entry. Let's make another change here and I will do this on purpose. What if the numbers are not in ascending order? If this is 4500 right here, instead of 1500, the problem will be that we will have answers.

Some of them will be wrong though, but they won't necessarily jump out at you. As soon as I press Enter, you'll see some of the answers in the column E change, but not all of them. And some of them are still accurate so it can be very misleading at times. You always want to make sure when you're using VLOOKUP for approximate matches where you've got numbers representing break points, these must be in ascending order as we look down the table. So, I'll press Ctrl+Z again and some of those entries in column E will not be corrected. Ultimately, we don't really need to make column E this wide, we'll simply Double-Click the boundary.

We've used our VLOOKUP function here to look up data in a very efficient way. That table might be in a different worksheet, different workbook, but it works smoothly and nicely when it's nearby and you can check out its totals. One improvement we could make to make this even simpler is if we know there is no other data in columns H and I, instead of having this somewhat complex looking reference, let's simply drag across the columns H and I. And that notations style referring to the entire columns works just fine here and we can recopy that, we'll Double-Click on the lower right-hand corner.

All of these entries now refer to columns H and I and it's much easier as we view the function to figure what's going on. We don't have to worry about absolute addresses. So another adjustment to the VLOOKUP capability which accentuates how easy this function is to work with. A powerful tool to be sure.

Sometimes you need to use the VLOOKUP function to match data exactly. In this list, in columns A through I, we need to find the department for the products showing in column A. In columns K, L and M, we've got a complete list master inventory, some 90 rows or so of all of our products by department, by category. What's not broken out there is the various colors which are implied and so we do see more data over in column C. So when we need to make a match, we want to use VLOOKUP but we've got to make sure that the data matches exactly.

Unlike comparing numbers where we sometimes search for breakpoints if it's a text entry, it's got to be an exact match and with certain kinds of numbers like ID numbers, Social Security numbers, they too must be exact matches. We use the VLOOKUP function differently for exact matches than we do with approximate matches. Now to make this even clearer, I'm going to hide some of the columns, column C, D, E, F, G simply Right-Click and Hide for the moment, so we can focus on the idea that we want to use the VLOOKUP function in column H then in column I to look up the information from column A and return the appropriate department and then category.

So I'll make the column a bit wider here, also zoom in a little bit using the Zoom Slider bar in the lower right-hand corner. As we're about to look for the department, the table reference that we will be using will be columns K and L. We don't need the category just yet, VLOOKUP in cell H6=vlookup(. The value we're looking up, the text we're looking up is in cell A6, click it or type it, comma. Where is the table that we're using? It's in columns K and L. There's nothing else in those columns so we can simply drag across, hold down the Left Mouse Button and drag across columns K and L and we see that indication in the VLOOKUP function.

That's where the table is located, comma. Which column of the table has the answer that we're looking for? We're looking for the department entry that's in the second column from the left so we put in 2. With an exact match, you need four arguments sometimes called parameters inthe VLOOKUP function; comma and you'll see a pop-up tip. False for exact match then we would click this and then tab into place. The word FALSE doesn't exactly scream out as if to say it means exact, but that's what it does mean.

If you prefer to use zero, fine, you can use that. But neither one of them really makes the association that you might expect it to. In other words, it does find the exact match but that's not exactly that something that you would know obviously. Zero or false will work here, that means the matching must be exact. It can't be close as it might be with numbers. And when we press Return here, we should get a correct answer. Bamboo Arm Chair is in the Furniture Department. We can copy this down the columns simply by Double-Clicking. Because the VLOOKUP table reference was a column reference, we don't have to make that an absolute reference.

Now using the same function in column I, we could possibly just copy this portion of it. I'll press Ctrl+C and Escape. We'll need to make an adjustment. Let me make the column narrower and make column I wider. This time we will have to refer to a three column table, so if we were to click here, type "=" and press Ctrl+V to paste, of course we could have been typing all this too. Instead of columns K through L, we want to make sure that that's columns Kthrough M. We want our answers out of the third column. This becomes a 3.

Zero still means exact match. Remember it can be zero or false. As we complete this entry, it's in the Dining Category and Double-Clicking here will copy this down the column. It looks like we carried some formatting with it but we'll adjust that later. So VLOOKUP, we've seen two examples here of how we look up data in a table and as always with VLOOKUP tables, these tables might be elsewhere, although it's certainly handy and convenient and easy to check our results when the table is nearby. If ultimately you want to move this table, if you move the table, for example rightward a few columns or possibly hide this or even drag it on to a differentworksheet, it functions as written, should automatically adjust and the answers will continue to be correct.

There's no question that using VLOOKUP for an exact match is always required with text and it will be required if you're trying to look up ID type numbers. They've got to be exact as well.

A category of functions that's extremely valuable when working with large lists is the COUNTIF family of functions. There is a COUNTIF, a SUMIF and AVERAGEIF and three more sophisticated variations that allow you to use multiple criteria. In this particular list here we might want to tabulate how many Kitchen items we have. It's a large list, 300 rows or so and we don't necessarily want to sort the Data but we do want to know for example, how many of our products are in the Kitchen category, Kitchen Department, Bedroom Department, Dining Department, so on and we have various entries here.

Now to make this easier to see I'm going to hide a few columns here, columns C, D, E as I drag across these into, let's just say those three columns we'll hide for the moment, Right-Click and Hide. We've got a list here of all the different Departments that's been set up ahead of time, so let's zoom in a bit on this list, we'll use the Zoom Slider bar on the lower right-hand corner to zoom in a bit. So let's imagine, we want to tabulate how many kitchen items do we have here? And the function to use is COUNTIF; make the column just a tad wider as we watch this.

=countif, this function requires two arguments or parameters. Where are we looking for these entries here? We're looking in column B comma and what are we looking for? The word Kitchen. Now if we don't have this in a nearby cell, we'll type "Kitchen", but since it's right here to the left we'll just click that cell and press Ctrl+Enter since we don't want the active cell to move and we have an answer, it's 45. By Double-Clicking here, we'll copy this down the column.

Out of all the items that we have here, some 300 items, we've got 41 Bath entries; we see the formula there, 12 Entryway items and so on. In all examples here we're looking in column B simply counting how often that text appears. A companion function called SUMIF allows us to tabulate data. Where is the revenue coming from in our list of sales over here? This time what we'd like to be able to do is to focus for example on the Kitchen items and then go into the Revenue column to figure out how much money we're making here.

Here too I'll make it for the moment a bit wider. The function is called sumif. Where are we looking? We're looking in column B, so it starts off the same way, comma what are we looking for? All of the Kitchen entries, comma and when we find them where do we want to go? In the column H. Now if you are using this function where you have specific cell references forexample, we could have highlighted cell B7 down to B310 or whatever, the length is here.

Just make sure that in situations like that that the number of cells that you select here matches the number of cells that you get here. And if we use column references like in this example, we don't worry about that. But what we're saying in effect is, every time the entry in column B is Kitchen, then go into column H and grab that information and keep doing that over and over and tabulate the total, because it's a SUMIF. So there is the Revenue for Kitchen items, Double-Click to copy this down and we could see very quickly, Bedroom and Dining Room and also Living Room as three top items revenue-wise as we look at the data here.

The SUMIF function, where are we looking, what are we looking for, and then having found that, which numerical field do we want to add, in this case Revenue. And as you might expect, AVERRAGEIF is going to work pretty much the same way, this time it's doing little bit differently though, because we want to look at the average cost of something. So to make this a little easier to see, I'm going to make one of the columns, let's say column A, a bit narrower so we can see this better. So Average Cost/Item is going to be averageif.

Where are we looking? We're looking in column B again, comma what are we looking for, the entry in J7 namely Kitchen and when we find this what are we trying to do? We're trying to average the price, that's in column F. What's the average price of our kitchen items that we're currently selling? $26.30, how about the others? Probably no surprise that Living Room is the highest. Bath is pretty low, isn't it, compared to the others.

So all we're doing in this case is saying in effect we're looking column B and when the entry is equal to what we see in column J, then go into column F instead of adding them as we did in column L or simply averaging these entries this time from column F so these three functions;COUNTIF, SUMIF, AVERAGEIF give us great tabulating capability when working withdatabase type lists.