Find online courses made by experts from around the world.
Take your courses with you and learn anywhere, anytime.
Learn and practice real-world skills and achieve your goals.
Whether you're a novice or an expert wanting to refresh your skillset with Microsoft Excel, this course covers all the basics you need to start entering your data and building organized workbooks. Author teaches you how to enter and organize data, perform calculations with simple functions, work with multiple worksheets, format the appearance of your data, and build charts and PivotTables. Other lessons cover the powerful IF, VLOOKUP, and COUNTIF family of functions; the Goal Seek, Solver, and other data analysis tools; and how to automate many of these tasks with macros.
Not for you? No problem.
30 day money back guarantee.
Learn on the go.
Desktop, iOS and Android.
Certificate of completion.
|Section 1: Getting Started with Excel 2013|
If you're just getting started with Excel, you've probably asked yourself the question, "What is Excel used for?" Excel is usually described as a " spreadsheet package" and that it certainly is.For example, what we're seeing on the screen here perhaps is the budget, the sales, expenses for small operation, and of course, we could easily expand this to a much larger operation with a lot more detail. This is covering data for a whole year, nice quarterly totals that could easily be expanded into a five- year budget projection model. Excel also can be used for handling database like information.
Here's a list of our customers. We might want to sort this, we might want to do other things with it, it might have over a million rows even. We might keep track of our employees. Excel also has a table feature that facilitates treating this data as a unit, so that we can sort it and filter it quickly and easily. When it comes time to presenting information, Excel's charting capability allows us to quickly turn numbers like we're seeing here into attractive charts, like the one to the right. Excel has tons of analytical capability. It's got lots of formula capability, as well as over 400 built-in functions to ease your use of mathematical and statistical tools.
It's got a great future called Pivot Table that allows us to quickly analyze information like this transactional data to the left, into a compact table on the right that quickly gives us the big picture. If that were not enough, Excel has a number of visual tools as well. For example, the title the way we're seeing on the screen, the organization chart to the right. There are over 200 built-in similar tools here like Venn Diagrams and others that quickly allow you to create presentation tools. When it comes to using Excel, there are just so many different ways you can use this package.
It's just a giant grid of columns and rows and you can use it for a variety of applications.
Excel's menu System is extensive and well-organized. It's got a variety of icons and pop-up screens that eases your understanding of how Excel works. Called the Ribbon, it's located at the top of your screen and it consists of a series of tabs, Home, Insert, Page Layout, and others to the right. The Home Tab is the most important and probably the one that is going to be visible on your screen well over half of the time. It contains a lot of features that you tend to need often as you use Excel. Without talking about all of these in details, just take a quick look at a few of these.
The Insert Tab has a lot with adding additional features such as Charts, Pivot Tables, and Sparklines, features that you may or may not have heard about. Page Layout has a lot to do with printing and getting your printing organized. Formulas, as you might expect, has a lot to do, not only with formulas, but some of Excel's many built-in functions. The Data Tab has a lot to do with sorting and filtering and those data handling kinds of tools. Recognize too that as you are looking at the Ribbon, as you slide the mouse over of one of the features, you get a pop-up description, sometimes quite lengthy as we see here; and it enhances your learning capability with Excel as well.
There's a Review Tab with some specialized commands and the View Tab as well for those special kinds of visual arrangements of data that we sometimes deal with. Now, different from the others, but also a tab on this list is File. When you click the File button on the left- hand side, using the left mouse button, we're taken into what Microsoft calls the Backstage View.Many of the features here have to do with file handling capabilities, opening and closing and saving files, as well as printing and some other features here.
The idea of course is, here, we're dealing with information more at the file level, than at the cell level. We can easily escape from here by the Esc Key or simply clicking the Left Arrow at the top of the screen. Recognize also that when you are working with a specific tab, for example, the Home Tab, the icons below are divided into what are called "groups". Here's a Font group, here's an Alignment group, a Number group. If you had worked with Excel in prior versions, it's very comforting to know that when you see the arrows on the lower right-hand corner of a group, for example, here on the Font group, a pop -up description shows how you can gobehind the scenes to get to other features.
This is called a Dialog Box Launcher. I'll click it now. There's a Dialog Box for formatting cells, bringing out other features that we don't see in that Font group. This is very similar to what Excel looked like in prior versions. We can easily click OK here and move on to others as well.So you won't see these as much as you will on the Home Tab, but again, it takes you behind the scenes to get to other features. In addition to these groups, also, you will see in the upper right-hand corner, a special arrow here for Ribbon Display Options.
When you click this, you do have the ability to auto-hide the ribbon. Now, as I'm about to do this, you might note on the bottom of the screen that I'm almost seeing all of row 23. If I auto-hide the ribbon, now I'm seeing all the way down into the part of row 31. There will be times when you work with Excel that you want to get that ribbon out of the way and that certainly is an option. If you then slide the mouse up top, if you made that choice, a banner will appear, just click it and then we have the ribbon appearing, at least temporarily.
You have another option here on that same button for showing the Tabs Only, and so now, as we work with Excel, we're seeing, for example, row 27 here, but whenever we need to get to the ribbon, we could click Home, for example, get to some of the buttons there. As soon as we click below this, the ribbon goes back to showing us just the tabs. The third choice, the default choice, the one that we will see throughout this course is called Show Tabs and Commands.That's the more or less standard view. Another way to temporarily hide the ribbon, and you might even do it by accident, is to click twice on the current tab.
For example, if I'm using the Home Tab and I want to quickly get it out of the way, I'll click it twice and it collapses, momentarily, while I do other things. To get this back, I will just go back to Home and double-click and it's back again. The menu System, the Ribbon, has a variety of tools, a variety of Help screens as well as you slide over them, and you'll use it extensively as you work with Excel.
Located in the upper left-hand corner is what's called the Quick Access Toolbar. Initially, it consists of four or five buttons, but it can be expanded and put to good use. What it represents is an area of the screen that's always going to be visible. The idea here is, if there are certain features that you use in Excel often, you might want to have them represented on the Quick Access Toolbar. Initially, you'll see a button for "saving", "undoing", and "redoing". Off to the right is a Special Drop Arrow, Customize Quick Access Toolbar.
Do you want a special button for Quick Printing? Do you print often? This might be handy. If we click this choice, we now have an icon for Quick Printing. Click this again, might we use or do we think we know we will be using Spelling checking a lot? We'll, click this icon as well.Now over time, maybe you'll decide, we don't use that that much or don't use that often, maybe we don't need this. You can easily click with the right mouse button and simply remove this from the Quick Access Toolbar, but there's an even broader use. Suppose you've gotten comfortable with Excel and one of the features that you use often is applying a color background? Fill Color-- it's on the Home Tab.
Well, what if one day you're working with the Data Tab and maybe you start to use the Data Tab a lot because you're working with a list like what we see here? You might be doing sorting and filtering. What if you want to apply color right now? What do you need to do? You've got to go back to the Home Tab to get to this button. Instead of doing that each time you need this feature, one that you use often-- and you can do this with any icon in the ribbon system-- right-click and add to Quick Access Toolbar and there it is. If we are working with our data on the Data Tab or the Review Tab or the Formulas Tab, and we want to use that color background, well then, we can select the cells and simply use that button without needing to go back to the Home Tab, so it can be used that way as well.
Furthermore, the drop arrow that we see here on the right, Customize Quick Access Toolbar, has the choice at the bottom called More Commands. This leads us into a completely different dialog box, alerting us to the fact that any of these commands that we see here--under the heading Popular Commands-- any of these can be buttons on our Quick Access Toolbar. If there's a feature such as Shapes that you might use often, you can add that to the Quick Access Toolbar.
If that weren't enough, in addition to Popular Commands, click this arrow to the right and you'll see "Commands Not in the Ribbon". Now probably, this is for people who've been using Excel for a while or who have specialized uses. Here's a huge list of commands, well, over 300 of them. Any one of these has a button and in it too could be added to the Quick Access Toolbar.Believe it or not, there's a third choice here called "All Commands". Here, we have about a thousand choices. Again, file that away, come back to this some later time perhaps, and decide whether any of these buttons represents a feature that you use often-- you can add it to the Quick Access Toolbar.
The top-down order that we see here to the right does reflect the left to right order that we see in the upper left-hand corner of our screens. From time to time you may say, I want a certain button to be on the right hand edge. I want to make it easy, well, here's Quick Print, maybe you use it often. What might you do here? Move it down the list, which in effect will put it on the right-hand side. As we click OK here, we now see that our Print button is there too. Another option is the placement of the Quick Access Toolbar.
You might want to put it below the Ribbon. It will use up slightly more screen space, but it does put it closer to the data that you will be working with. If you right-click anywhere in the Quick Access Toolbar, you'll see an option called "Show Quick Access Toolbar Below the Ribbon" and you can put down here. And if later you change your mind about that, you can right-click the Quick Access Toolbar and show it "Above the Ribbon". Sometimes, you'll have many, many buttons in your Quick Access Toolbar. Sometimes, you'll decide to just start all over, or maybe you're working with another computer with many, many buttons, you want to simply start all over and design it your way.
You can easily right-click and customize the Quick Access Toolbar. And then on this Excel Options dialog box here, reset the Quick Access Toolbar, and now it's back in its original state.It's a great feature for customizing your use of Excel, so you can get to often used commands.
In Excel the term "workbook" and the term "file" mean the same thing. We are currently looking at a workbook called "01-Getting Started" and you see that name at the top of the screen. If you're working with a brand-new workbook, you'll see a name like Book 1 or Book 2 perhaps, at the top of the screen. We use those terms workbook and file interchangeably, as we work with Excel. Every workbook is comprised of at least one worksheet. At the bottom of the screen, we see a sheet tab, maybe one, two, three, perhaps many.
You can add sheets, you can delete sheets, you can change their name, you can move them left and right. Every worksheet has the same general characteristics. For example, as I use the mouse here to click on the sheet called, 2013 HOME products revenue, we see column letters across the top, row numbers down the left-hand side. A worksheet is comprised of columns and rows, and we never want to use the two terms interchangeably. Rows are horizontal, columns are vertical.
If you use the mouse to click on a cell, you've selected the cell, you'll hear that term used from time to time. Let's select a cell. This is in column G, row 1, therefore it's called "cell G1". As you work with Excel, you do frequently need to refer to a cell by its location, that address as it sometimes is called, that's cell G7. Just above the column letters over on the left-hand side, you'll see an indicator as to what the current address is.
Sometimes, you'll hold down the left mouse button and highlight more than one cell. Still, within that highlighted selection, the cell that you begin to do the dragging with is referred to as the active cell, and you see its address, once again in the upper left-hand corner. Now, if I go to a different worksheet, typically, we do this with the mouse. We can click another worksheet name at the bottom, this is for existing files where you already have data, we go to a different worksheet-- this one has a chart in it. There's another worksheet down there called Profits.Let's click on this.
Now, every one of these worksheets does have the same number of columns. In this worksheet here, the active cell is at K1, if I start pushing the right arrow keys-- and possibly we could do this by scrolling as well-- after coming to the letter Z, the lettering scheme begins all over again with AA, AB, AC, and so on. This continues for over 16,000 columns. If you happen to press Ctrl+Right Arrow by the way, this will take you to the very last column XFD, and that's over 16,000 columns.
Getting back to the upper left-hand corner of any worksheet, Ctrl+Home, nearly, always this means go to cell A1. In some case, there is an exception to that with frozen titles. As we move down the screen, pressing the Down Arrow, we see the row numbers on the left-hand side, and eventually, if we kept doing this, and it would take a long, long time, we will reach the very bottom. I'm going to press Ctrl+Down Arrow here, and we're now well over a million rows.That number by the way is a power of two, the underlying math here is all binary, we don't worry about that too much.
Ctrl+Home will take us back to the upper left-hand corner. Every worksheet has the same number of columns and rows. At different times, as you will see, we can easily adjust the width of the columns and the height of the rows as necessary. So in Excel workbook comprised of one or more sheets, you can add sheets, at anytime, take them out and the more you work with Excel, the more you will see that from time to time it will make sense to have multiple worksheets within the same workbook.
Even if you have not yet used formulas in Excel, you need to know about an important aspect of the Excel screen called the Formula Bar. It's located below the Ribbon and above the column letters in a worksheet. It's right here-- it's called the Formula Bar. Now, in this worksheet, which is already started and perhaps finished, we don't know necessarily. If we use our arrow keys and position the active cell for example, over one of the entries in row 6, we'll see that it's a formula.
If I press the Up Arrow and go to cell G5, we see that that's simply a value. Many times when you're working with Excel, either when you're typing data or simply looking at a cell, you develop the habit of looking in the Formula Bar. It's almost an instinct, like the way you look out of the rearview mirror in your car when you're driving-- we do it frequently and often. The key idea here is that when you're looking at data that maybe you're not too familiar with, you want to know often, is it simply raw data or is it a formula? As I press the arrow key to the right here, now that may or may not be something I'm familiar with, but it's certainly is not 1600, really, that'sactually a formula.
The cell to the right of that, that's some kind of formula as well. Maybe we haven't seen that just yet, but it too is not 266.7, really. The numbers off to the left, the sales numbers are just pure numbers. Text entries are usually exactly what you see. As you type data, as you enter it, you'll be keeping an eye on the Formula Bar. As you are exploring worksheets that you're unfamiliar with, you'll learn to look at that Formula Bar frequently. It's always at the top of the screen and it's a good visual check as to what your worksheets really contain.
The Status Bar located below the sheet tabs, at the bottom of the screen, often contains the word "Ready" in the lower left-hand corner. If you're entering a data you'll see the word Enter there, but many, many other indicators can appear in the Status Bar. Off to the right, you will see three buttons for different kinds of use, Normal, Page Layout, and Page Break Preview. To the right of that, you'll see a Zoom Slider Bar. We might want to zoom in on this worksheet to see the data large and clear that way, or may want to zoom back and see more data, although not nearly as large.
As we do different things using Excel, other indicators might pop-up as well. I might be typing and might want to use the Caps Lock Key and I'll click it, or maybe I did that by mistake. In the lower left-hand corner, we see the term "Caps Lock". As you select the data, now, I might be interested in the revenue, the total revenue here and by clicking Column I, suddenly quite a few numbers appear at the bottom of the screen. I know immediately the total revenue here for 2012 is over $22 million and it's showing me the maximum, the minimum, account of how many entries, and the average and quite a few bits of information that I'm really interested in.
If you highlight two or more cells in Excel, you will see at the bottom of the screen some of these indicators. Although possibly, you could have turned them off. If you click with the right mouse button anywhere in the Status Bar, you will see a rather large selection of check boxes with a number of features here. You want to explore these from time to time. If you want to be notified, if you accidentally clicked the Num Lock Key, then turn that check box on. The numbers that I just referred to at the bottom of the screen here are in this section right here--do you find these valuable? You might leave them all checked and then over time say, "you know, I don't use Count very much, I don't need that".
We won't see them at the bottom of the screen as we highlight data. I'd say leave them on for awhile and then come back and explore this every so often. Think about how handy it is when you're looking in a large list here. If you know your data well enough, if there's nothing else in the column. How many items did we sell this year? We'll click column H, and we sold -- and it's kind of hard to read without commas, but how many items did we sell? 389,000 items, so it's a handy feature. The Status Bar gives us a lot of good information, and from time to time, do right-click on it and consider which of these options, which of these indicators you do want to have active.
As you work with Excel, many times you'll be using a mouse or a trackpad, and of course, the keyboard too. The mouse pointer, as we currently see it, is probably in its most common appearance; that of a three-dimensional plus sign, but the mouse pointer is context-sensitive. If we are about to make a change, for example, the width of a column, we're going to be positioning the mouse perhaps in the column itself, notice how the pointer now is a black down arrow or if we put on a column boundary, it's a double left right arrow. If we position it on a row boundary, it's a double up-down arrow.
As we point into the menu, the mouse generally is an arrow. Also, notice that if you put the mouse pointer in the lower right-hand corner of the active cell, it becomes a different kind of plus--very thin--so we get used to the idea that the mouse pointer changes as we work with Excel. Now, in working with different workbooks, I currently have opened a workbook called 01-Getting Started. I also have another workbook open as well. On the View Tab in the ribbon, if you point to the choice called Switch Windows-- click that icon--you will, if you have more than one file open, see the other filename; you can easily get back and forth by switching the commands here.
It's not uncommon to be working with multiple workbooks at the same time. In a workbook like this that has five or six sheets, if you want to switch sheets, more often than not you're likely to use the mouse. In other words, I'm looking at the 2014 Budget right now, maybe I want to lookat the Employees worksheet, so with the mouse I'll point to the Employees sheet and click and now we're looking at that sheet. If you do have a workbook that's January, February, March et cetera, if you're on the March sheet, you don't have to stop and think that April's next.
So as an alternate way, you can press Ctrl+Page Down to move one sheet to the right or Ctrl+Page Up to move one sheet to the left. In working with different workbooks or in workbooks that have many worksheets, you do want to feel comfortable in navigating back and forth between them. Although, you can do these with commands, there are also as we suggested here, Keystroke Shortcuts. You want to have a sense that you're in control of where your data resides and how to get to it quickly.
In Excel, the Right Mouse button always means "shortcut menu". It's a great feature because it alerts you to some of the commands and features you'd like to be able to use, and I say that based on the idea that Microsoft has done a lot of research about how people use Excel. The Excel Ribbon menu system which is quite extensive, can certainly be intimidating, and particularly, if you think of all the many, many buttons and choices that are available there.Suppose you want to make a formatting change in this worksheet and you click on cell A4, hold down the Left Mouse button and drag across the next three cells.
Now, you'd like to make some formatting changes. You're not quite sure where to find them, so let's right-click here. Now, this doesn't mean that what we're seeing is every possible commandavailable--by no means at all. What we're really seeing here are some potential choices that we're likely we want to get to. Not only do we see this pop-up menu above it, we see a feature referred to as the "mini toolbar". For the most part, this consists of buttons and features that you'll find on the Home Tab, but is the Home Tab always the one that's currently active? Not necessarily.
These buttons, if we wanted to, for example, experiment a little bit with say a yellow background that looks yellow, well we'll click it and now it's yellow. While we're here, I don't think we want to change the font but we might and there's even a drop arrow there. We might want to use maybe a dark blue font and while we're here, we might want to use Bold. This is close to the data we are actually working with. Now, if we click somewhere else, the mini toolbar disappears. If you use the Right Mouse button in different locations, for example, if youwere working with the Home Tab and you were to right-click on this button, for example here, you see some choices related to what you might be doing with that button.
This has nothing to do with commands in the worksheet necessarily, but it's again a reminder what the Right Mouse button does. If we right-click a column letter, the choices we see here are different, than if we were to right-click on a cell on the worksheet. For example, I'll right-click now on cell G3 and that's a different set of commands, some of them are the same, but some are different. If you right-click on a sheet tab at the bottom of the screen, as you mightexpect, a different set of choices there, and of course, these are related to the kinds of manipulation you might want to consider when dealing with sheet tabs.
So the right mouse button is really handy. Here's another idea too. Suppose you want to add a new column here to the left of column B, you might right-click on column B. You don't necessarily know which command it is, but "Insert" sounds like it's the one and so you might use that. At a later time you might say, "well, I don't really need that". "How might we get rid of it?" Well, you might right-click again on column B. By the way, you don't have to select it first.Right now D5 is active. I'm saying to myself, "I don't really want that column B there anymore".
So, I'll point to the B and right-click and there is "Delete". Although, not foolproof, the Right Mouse button is incredibly handy at times, because it pairs down the list of possible options that you're likely to want to use in any given situation. When it comes to formatting issues, that mini toolbar pops-up as well, and both of them are extremely helpful as you work with Excel.
There's no question that when you're using Excel at different times, you will need help. Keep in mind again as you slide over one of the many icons in the Ribbon, for example, "Conditional Formatting" here, you do get a quick description of what that feature is about. Sometimes, these are very small, but at other times they're more extensive. For example, on the Data Tab, if you were to click the Data Tab in the ribbon, Text to Columns may not mean a whole a lot, but when you read this, perhaps it means more. Notice also, that you will see, as we see here a choice, "Tell me more" and you will get the Help screen here too.
Sometimes, the Help screens lead you into multiple other choices, you can click there. "Do I want to do this?" Maybe/Maybe not, I'm not sure what that means. This could be very useful you might want to print this. We can see, by way of the ribbon, we can easily get to some Help features. Also, available in the upper right-hand corner of a worksheet, you will see the question mark and recognize also that it has a Keystroke Shortcut--the traditional F1 key meaning Help. Throughout Microsoft products and many, many other software products as well, the F1 key means Help.
It does here, as well as the question mark. This leads us into the Excel Help screen. While you're here, you'll also see a lot of other tips here as well. Maybe we've done some of these searches or maybe we're interested in some of the more popular searches. If it's a completely different feature than the ones we see here, we might want to type in something. For example, we might be saying, "Well, I've heard about charts, let's find out about a chart". "What does that mean exactly and how can I create one?" We can type that in and either press Enter or click the magnifying glass and possibly, this will lead us into online help if we're logged on, or maybe getsome information right on our screen, even if we're working while we're not online.
There are a lot of ways to get Help as you work with Excel. Recognize also that the more you work with Excel, the more you become attuned to shortcuts. Particularly, on the Home Tab, recognize that as you slide over certain icons, for example, B for Bold, there's a Keystroke Shortcut listed there, it's Control+ B. There's one for Italics--Control+I-- and Underline--Control+U--and so on. Not every feature has a Keystroke Shortcut, but learn to discover those and make note of them. Here's Find and Select.
What's that one all about? You could click there, there's Find, maybe I want to use that later. Oh yeah, there's a Keystroke Shortcut, Ctrl+F, as we slide over Find or Ctrl+H for Replace. Some of these are not exactly obvious. The Excel Help System is extensive, you can get to it at any time with the F1 key or the question mark in the upper right-hand corner or as we saw withcertain features--as on the Data Tab with Text to Columns--the "Tell Me More" option as well; one more way to use the extensive Help system in Excel.
If you like to create a brand-new workbook and you're simply thinking of a blank slate, you can press Ctrl+N--that's the letter N--and suddenly, we see on the screen a new workbook. Now, that's going to be called Book2, Book3--depending upon what other workbooks you might have open-- that's its temporary name, but you should not overlook the fact that Excel has a number of built-in templates that might get you started much, much faster. Suppose for example, you're interested in creating a Budget or a Personnel list or a Phone list, you can go to the File Tab in the Ribbon and choose "New", now it does give us the choice for a Blank workbook or possibly a Mortgage Refinance Calculator; Financial Portfolio.
Take a look at some of these that are built-in. What you get here in all cases is an attractively designed worksheet with built-in formulas that might just be enough of a kick-start to get yougoing pretty fast here. Maybe you're considering some Home Loan options. Choose "Home Loan Comparison" here, "Create" and here we are started; and we're going to have to explore this a little bit. Maybe for it to make sense to our situation--change the Bank name. A lot of formulas are built-in, we might be looking at different terms, different rates.
This is enough to get you started many, many times. Furthermore, it gives you some insight into how, perhaps, certain formulas work in Excel, how certain formatting capabilities exist. We also see some nice charts in this option, so don't overlook this possibility. Again, going to that File Tab, choose "New"; explore some of the many options that are here. Furthermore, at the bottom of this list, you will see reference here, to the fact that you can use the search box at the top to find more templates.
If the recipe template that you saw here wasn't quite what you wanted, you can, for example, go to "Lists" here, possibly find some other options here and here we see a huge category of possibilities. This is almost an endless list of, possible built-in, templates that you might want to use to get a workbook started very quickly in Excel.
|Section 2: Entering Data|
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.
|Section 3: Creating Formulas and Functions|
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.
|Section 4: Formatting|
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.
|Section 5: Adjusting Worksheet Layout and Data|
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.
|Section 6: Printing|
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.
|Section 7: Introduction to Charting|
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.
|Section 8: Adjusting Worksheet Views|
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.
|Section 9: Multiple Worksheets and Workbooks|
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.
|Section 10: IF, VLOOKUP, and Power Functions|
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.
|Section 11: Security and Sharing|
In Excel there are any numbers of protection schemes and one of the ones that gives you quite a variety of options is called Worksheet Protection. The actual sheet we're using right now is called WorksheetProtection; it's in the file called 11-Security&Ensuring. Let's imagine you're an HR manager, you've got a trusted assistant and what you do periodically handing that personal sheet of paper is a list of changes that need to be made to this list. One of the changes for example might be to change this Hire Date here to be 1998.
Now you could imagine someone who doesn't know Excel way saying, okay, I'm going to make that 98, I'm going to make the years here be 15, so I'll go change the years to 15 and come over here and make this be 98. That's an innocent thought. However, this is a formula here, an unusual formula but nevertheless if you type a 15 there you've wiped out the formula. And so one security concern for sure is I want to protect my formulas. I don't want them destroyed.Now you want also be saying at the same time, I want to allow this date to be changed and have my formula work, then that will happen if we set this up properly.
There is another concern, should these job ratings be that available? Maybe this is a part-time employee and that's pretty confidential information and that shouldn't be thrown around so easily. So what might we do here? We might simply hide this column. I'm going to Right-Click on the column and hide it. Sometimes that alone does the job. But we want to be a little bit more careful than just that. What we ultimately want to do here is to make sure that information that we just hid is not available to certain people and we want to make sure that certainformulas are not destroyed.
And here is another concern, let's say we need these salaries to be available for reference purposes, they help us to find which person is here, we certainly don't want them to be changed though, at least not by the person who is making these other changes. Now, the idea of protection starts off with a slightly different idea. In Excel all cells within a worksheet are initially locked. Now you might have unlocked them for one reason or another, let's say we haven't here. If you Right-Click any cell and go to Format Cells and this is one of many waysto get to the same feature, if you go to the Protection tab you'll the term Locked and probably a checkmark in front of it, nearly always.
The description below, Locking cells has no effect until you protect the worksheet, now we're about to do that. So right now all of these cells are locked. If we turn on this feature called Worksheet Protection, we won't be able make changes anywhere. But let's say as a general rule in this worksheet we do want to allow changes in most locations. We don't want our formulas altered in column F, we don't want the salaries changed in column H and we don't want that hidden column revealed, so what do we do first? Click in the upper-left corner to select the entire worksheet and then Right-Clicking on any cell, we can go Format Cells, we could also get here by the way by pressing Ctrl+1.
In both cases we're back at the Format Cells dialog box. Let's unlock all cells, at least for the moment. Sometimes when you come here, this box is gray and that means that some of thecells are locked, some are not so you might have to click it once or twice, make sure that it looks truly empty, the way it looks here, click OK. Now, we want to make sure that no changes are specifically made in column F, in other words nobody can type here, same thing in column H. So using the Ctrl key we'll select both of these and then Right-Clicking we want to go to Format Cells and now on the Protection tab we want to lock these cells.
Still we haven't gotten to the stage, where this means anything yet; Locking cells has no effect until you protect the worksheet. Okay, we're about to use this feature it's on the Review tab, it'scalled Protect Sheet. Prevent unwanted changes from others by limiting their ability to edit, click the option. And then, a rather extensive list of things that we might allow while using thisparticular worksheet.
Now at a minimum if you want to allow any changes to be made you must have the second box checked, it says Select unlocked cells. If you can't click on an unlocked cell then you can't make any changes to it. Now the box above it says Select lock cells. If we have a check box there it means that someone can click the box or tab into it but if it's locked, no one will be able to make changes there. So let's -- first time around here, we'll show it to you both ways, leave this unchecked. Now if you want to allow the other user to be able unhide columns; then you will allow formatting of columns, but let's say we don't want to that.
So we'll leave most of these, in fact all of the others unchecked. We're ready to click OK here, but let's provide a Password as well, another level of security, put in a password. We'll click OK or press Enter and here is the password again and of course don't forget it, we click OK.Now when you're in this mode, one visual giveaway is if the HOME tab is active, look how most of these features are grayed out and that's not fool proof but usually that's the case when you see this, you've got worksheet protection in effect.
But no other words or phrases on the screen that seem to suggest that. So here we are, imagine the other user is using this and sees that sheet of paper and this person is really hard 98, so what I'll do is I'll click over here, gee I can click there, can I use my right arrow key, well that jumps me over it. I can't even go there. So I'm not going to be able to make any changes in column F. Well I guess I'll just Double-Click here and change that to 98. And as I do, watch the years to the right change. So the formula does its job if you're not specifically allowed to click here and make changes to it.
And this is my friend's salary, I think I'll change that, or raise that. Well, gee I can't click on, I can't tab into it, I can't arrow into it anyway, can't get there at all. And it looks to me like there's a hidden column between columns H and I. This I'll drag across here and can we somehow do that? Looks like I can't do that. Can I Right-Click and -- no, can't get there either. Can we somehow Double-Click here, nope, can't get there. Could we somehow go up to the commands Format, Unhide, Hide sheet maybe, what's this Row, can't get to any of this.
So I mean depending upon the persons who are using this and the security levels, you can begin to see how this makes some sense here. When the worksheet is in this stage, changes can be made in most places, in our example here but not specifically in column F and not for the salaries. How do we turn off the feature? Review tab, Unprotect the Sheet. And of course you got to know the Password, I do. Okay, we're back in the stage. Now one variation on this, almost the same but slight difference, if we protect the sheet and this time allow all users to select the locked cells, and once again we'll provide a Password here.
When the worksheet is in this stage you can click on the cell here, but as soon as you start to type something, immediately you get a pop-up message, The cell you're trying to change is on a protected sheet, same thing with salaries. Now we can click here, that hardly makes much difference if we can't do anything, can't make any changes there at all. So that's a slight difference in the settings, but in both cases we are not able to make changes here when it's in this state, unless we can Unprotect the sheet. So I think you can sense the value of this, it works only on the current worksheet; it gives us powerful ability here to control what's being changed within a worksheet.
If this is your workbook, but others are allowed to see it and work with it, you might have some security concerns regarding the structure of the workbook and even the appearance of certain worksheets. In this workbook called Security&Sharing, the first sheet here is calledWorksheetProtection. And there are some formulas in column J that relate to a table, it's actually on another worksheet called TaxTable, see at the bottom of the screen. Now, you don't want that table to be disrupted or altered in any way and you rather not even have others see it.
So what can we do here? We can hide a sheet by Right-Clicking on TaxTable the second sheet from the right, we can choose Hide, and we no longer see it there. At a later time if we need to get this back we can Right-Click and Unhide. And by the way, if you have more than one unhidden sheets you're going to only bring back one at a time. So, we don't want that visible.Now our formula still works, this still refers to the hidden sheet. But, we want others as we said to get to it and furthermore, we don't want the order of the sheets here changed also, we want to keep the same scheme as we see it now.
We do want to allow others to work with this and possibly make some changes. So we're not talking about worksheet protection here, but protecting the workbook. We go to the Review tab and choose Protect Workbook, keep others from making structural changes. And the box for Structure is already checked, we will provide a Password, we don't want others somehow changing this and getting to those hidden sheets possibly, we click OK. There is a password again, we'll put it in again of course and OK. While it's in this state the other person who might be using this or other people might say, gee! I wonder if there is a hidden sheet here.
If we Right-Click any of the sheets here, we don't even have the possibility of seeing whether there's a hidden sheet. We can protect the sheet, that's totally different action but nothing aboutunhiding, so we can't get to that. And notice also how Insert and Delete and Rename and Move and Copy, all those are inhibited. And we say, yeah but I move sheet by dragging them, well I try dragging this one. You see that symbol, you know you can't do that and similarly with copy as well. And so if we need to get this back of course, we would know the Password, comeback to Protect Workbook and provide the Password to unlock it.
So it's a completely different feature than protecting the worksheet and it's primarily about the structure of a workbook. Back in this state now we could Right-Click one of our sheet tabs and go to Unhide and unhide that particular sheet that we've hidden. So it's a great feature for controlling the structure of a workbook allowing only the sheets that you want to be seen to be seen.
If you have multiple users of this workbook, you might want to think out a couple of schemes regarding who can access the workbook, in other words who can open it and also who can make changes to it. When you save a workbook you have options. For example, if we click the File tab in the Ribbon and choose Save As and recognize I am using Windows 8 you might not be but no matter what your using here, if you go to the appropriate location where you're about to save the current file, you're likely to see a dialog box and although perhaps a little different than this, it probably has the general characteristics. The term we're looking for here is going to be Tools, often in the lower right-hand corner and then General Options. You'll see a Password to open, Password to modify. The more you think about these possibilities you might realize that there are actually three possible scenarios, one of them might be the following. You've got an organization of say ten or fifteen people, eight or ten of these people might be allowed to open this file. You've decided to allow those people to open the file and so you provide apassword for opening, provide a password. For all those people that can open it, they can all modify, so we don't have a password to modify maybe. So one option here is simply to provide a password to open this file, and only some people know that, we'll put it in again. And when we save our file that password is in effect, we're replacing it and it's open right now, so I'll close it, I'll just use Ctrl+W, that's a fast way. And the next time I want to open this, I can got to the File tab in the Ribbon and since it's a recently used file I'll just go to the bottom of the screen,down the left-hand side, choose Security&Sharing and there it is, it asked me for the password, okay. And now what can we do? We can make changes to it; we can do whatever we want. In other words, if you've got the password to open the file you can make changes to it, you've got free reign. Now, let's imagine we change our minds, a little more security conscious here. Now these eight or ten people that can open this file, only some of them areallowed to make changes, we've decided that. So let's go back to the File tab in the Ribbon, Save As, same location as before, this time the Tools button, General Options, we're going to provide a password to modify. Now in this scenario there is a password to open the file and let's say eight or ten people have that, but password to modify, only four people have this orthree or whatever is necessary. So we click OK and we put in the Password again of course, and okay. And let's save this, already exists, we'll close it, once again Ctrl+W is a fast way to do that. At a later time we open it, I'll click File, it's down the left-hand side; there it is, Security&Sharing. There is our password to open because we didn't take it off, we put it in and we also get this prompt. This is reserved by me, Enter password for right access or Open read-only. So I might be another user here who hasn't been given the password, but I can still open this but in read-only format. If I am the person who's been given the password and I'm about to make some changes maybe, I'll put in the password and click OK, so that's a second scenario.A third scenario would be as follows. You allow anybody to open it, but only some can make changes to it. So in that scenario we do not provide a Password to open, but we do provide one to modify. So in thinking these out, you might want to sketch this out on a white board or a large sheet of paper or whatever and make sense to think these things out. Let's go back to File, Save As again, once again going to the appropriate location and this time under Tools>General Options we turn off the Password to open, but we leave the Password to modify. And so it's best to have thought out these possibilities and see which is going to work best for your environment. This time we'll do a Save. Again, just press Ctrl W to close this and what happens to the next time we open the file? Once again, clicking the File tab, choosing this file Security&Sharing, there it is... Nothing about really opening the file but we do see the prompt about the ability to change it. And if we want to make changes we'll provide the password, if not it will be open as read-only. But everybody can open it. So three different ideas behind applying passwords, both to open a file or to open and make changes to the file or simply to make changes, all by way of File>Save As and them using Tools under General Options.
We're looking at the file called 11-Workbook before Sharing. It's got a single sheet in it called Vendor Sales and let's imagine that one person has been updating this from time to time and then others get involved too and they make changes to it. Eventually the need might arise that these different people who do the updating might need to have the file open at the same time.A file can be open by different users, but we also want to allow them to be able to make changes at the same time and that's a special status. Excel refers to this capability as sharing the file and that might sound as if we're just allowing others to open the file, but it's more than that, it means simultaneous sharing and making changes.
So to put a worksheet in that kind of status we go to the REVIEW tab and Share Workbook.Share your workbook so that others can work on it at the same time. Note the information here, workbooks containing tables can't be shared and by the way, you should check the help system as well to see that there are quite a few other features that are inhibited by this capability. Share Workbook and this dialog box appears. The Editing tab has a box, Allow changes by more than one user at the same time, that's the key idea.
It goes beyond the non-Excel meaning of what share really means. Who has this workbook open right now? I do. Now eventually there will be others. There is an Advanced tab here.When you share a workbook, it's going to be advantageous much of the time to keep track of the changes. Although that can be a separate feature, it comes with the capability of sharing, and we might want to keep changes for 30 days or for a long time, I'd strongly suggest to keep this checked, consider how long you want to keep it, don't check the other box for not keeping the change.
Update changes, every time we save the file. Well maybe or maybe every 15 minutes or some other time setting. And ultimately by way of tracking you might even want to consider what happens if different users at approximately the same time are making changes? It doesn't have to be the exact time, one person might make a change to a cell; another person might come back. We'll talk about these in the next movie on tracking changes. But these are features that are allied with shared workbooks and we want to be thinking ahead of time about what's going to play out here.
If we click OK, remember the key step here was on the Editing tab checking the box. When we click OK, we see the dialog box, This action will now save the workbook; do you want to continue? Now I've name the workbook, Workbook before Sharing but we're actually about tomake it be a shared workbook. And as I click OK, watch the top of the screen. We still see that same file name which maybe now is a bit off, but in brackets we see the word shared.Now shared does not mean by itself that someone else has this open or even that someone else couldn't have it open.
If this file is saved on my flash drive for example, no, nobody else can do it right now. Shared means we open the door for the possibility of allowing others to get to this file and open it and make changes at the same time. So it's ultimately a powerful feature but it's one where you really have to think out the possibilities. And what I would strongly suggest in exploring this capability is either think out how you on two separate computers might have the same file open, test some of the possibilities that might occur when you're making changes to the samecell at approximately at the same time.
Another way to do this is to work with another user and probably in the same room on different computers and go back and forth for some of the changes that might occur to get a better sense of how this capability works. But there is no question that in certain kinds of files, say a file like this that is tracking transactions, you want to be able to allow different people make entries here and make changes. And so as we've seen on the Review tab you can share a workbook, meaning that we allow multiple users to get to this at the same time and make changes.
We're looking at the worksheet called RecordedChanges and it's in the workbook called TrackingChanges. And we're about to setup a feature where we're going to keep track of all thechanges we make in this workbook for an indefinite period. The feature begins on the REVIEW tab with the choice Track Changes, and in the description there it tells us that if you're working with others to make revisions or give feedback, it's particularly useful. But that's not saying that that's the only case where we might use this. What if you were the only user of this workbook and you simply want to keep track of the changes that you're going to make perhaps over the next few weeks, something like that.
When we turn on Track Changes the only choice we have his Highlight Changes. This brings up the highlight changes dialog box, click the box for Track changes while editing. The sentence that follows this might be a little confusing; This also shares your workbook. Does that means that others are using this right now and this immediately gives them access to it? No, it doesn't. It simply puts it in the same status as if you were sharing the workbook andthat's the situation where multiple users can make changes, simultaneous changes while they have the workbook open at the same time.
And eventually when we click OK we will see the word Shared in the title bar at the top. So we want to highlight which changes, all changes, by everybody, even if it's only us, highlight the changes on the screen, just click OK. And immediately we see this prompt. Now if you're familiar with the concept of sharing, you know what it really means is that the workbook once we click OK will have the word shared in the title bar. Now once again that does not necessarily mean that others are using this file or can even get to it, all we're concerned about for the moment is tracking changes.
So I'll make a few changes there. I'm going to change this Job Rating to a 5, good. This number here I'm going to adjust, I'm going to make that 75,000, good and you see what's happened as I complete the change with Enter, move away from it. If we slide over these, it looks somewhat like a comment, we see whose made the change, of course in this case it's only me and when. If this was shared and if others were using it, others might have this open at the same time, they wouldn't be seeing the changes that I've just made, but they would be sending a similar kind of display on their screens.
So at some point we might want to revisit this, we might want to see all of these at ones.When we go back to Track Changes, we also have this option here and when you do have multiple users, that's a concern, we might want to Accept or Reject the changes. Let's go back to Highlight Changes, we've made a few changes, only two, but we might want to list the changes on a new sheet. And if we've been doing this for a while we might want to consider notnecessarily listing all of them, but just the ones since we last saved or since a certain date something like that.
So if we click OK and it says All, only changes which have been saved can be listed on the history sheet; we didn't save the workbook so I've got to do that first. So I'll simply click File>Save, I could have got the keystroke shortcut there too. Now let's go back to Track Changes>Highlight Changes, List these changes on the new sheet. And once again consider, if this were multiple users, we probably want to check everyone here but we might at certain point say everybody else, everybody but me or just me.
So when we do have multiple simultaneous users we might want to consider that. If we click OK, we see the changes. Now it's only me, I've only made two changes but we see these here.If this is truly shared and multiple users have been making changes at the same time, we might want to review these and go back to Track Changes and consider Accept and Reject Changes, and all the ones we have not yet reviewed by everybody, OK and here is one change, I'll Accept that, okay.
Here is the second one; maybe I will Reject that one. And if we have many, many of these we could Accept All or Reject All. So these are features that you really need to check out on your on, particularly if you are working with multiple users and truly is share where you've gotdifferent people changing this at the same time. But in this situation I'm the only user, but I'm finding it very useful to know and remember which changes I've made and where they are. So it's an extremely powerful features, it's an editing tool that helps us keep track of the changes that we've made to a workbook.
|Section 12: Database Features|
If you work with large lists, sometimes you want to change the order of the list. This list which has over 700 names, it's on the sorting sheet in the 12 Database Features file; it's organized by Employee Name alphabetically. We might want to rearrange this list based on what we see in the Department column, or possibly the Hire Date column. In fact almost any column in this list could be the basis for sorting. Sorting will proceed much more smoothly if you know for sure that the list you are working with has no empty rows, no empty columns.
If I put an empty row in here and I'll do it on purpose here, Right-Click Insert, if I had ignored that or didn't know about it, and I'm going to change the zoom factor here to make it visible. If we were to click in one of the cells here and start the process of sorting by way of the DATA tab and choose Sort, you can see in the background that Excel will only pick up the data down to row 27 and that's certainly not what we would want. So make sure that the data you're working with doesn't have any empty rows within it.
Sometimes people put those in for printing reasons or just for break reasons. Let's get rid of those we have them. Press Ctrl+Z here and we can get rid of those and zoom back and so on.So let's imagine we might want to rearrange the order of this list. We can do it on the basis of any of these fields. Sorting begins with the active cell within the data; you do not have to highlight all of the data provided you know it's in one solid cluster. Sorting is one of the most basic things you do with data.
We frequently need to see this in a different order. So, the DATA tab has one option, also on the HOME tab you could be starting there, second button from right Sort & Filter, Custom Sort or DATA tab, Sort button. In either case we see the Sort dialog box and the data below is highlighted. Recognize also, Excel will pick up the fact that you've got a title row if you have one, sometimes it misses that, you want to check the box just to make sure. You don't want row to as in this example to be treated like the other data.
In nearly all cases sorting means rearranging the order of rows, there's an option for sorting by column which is rarely used. We sort based on what's in different fields. Let's imagine we want to rearrange this list based on Department. So let's add a level here Column Sort by, click the drop arrow, we want to sort by Department. Some of our departments here are quite large;might have over a hundred names. So for looking at a department with lots of entries we might want to sort that list by Status, so we can add level, sometimes called the secondary level.
Let's sort by Status within each Department. And we might have a lot of Full Time people within certain departments so let's add another level, perhaps by years. Now the other two fields that we saw were alphabetical and it's quite likely you'd want to sort them in A to Z alphabetical order. Smallest to Largest, this is a numeric field. While we might want to have those people who have been here the longest to appear at the top of the list within each Status and Department, how about largest to smallest? In Excel 2003 in older versions you could only sort on three fields at once.
As of Excel 2007 they've up that to believe it or not 64, so we could sort on a fourth level as well. We still might have a lot of people having the same number of years, same Status, same Department, so how are we going to order those? May be by Employee Name, A to Z. Click OK; we're expecting to see this in order by; Department, Status, Years, Employee Name. And as we scroll up and down here in this Account Management Department we've got a lot of people the same Contract Status, Full Time.
Here is a group of people here with the same number of years, 14 years within the Full Time Status, within Account Management group, so what order are these in? Alphabetically by their last names. Now there will be times when you simply want to sort on one column. For example, you have sorted this, maybe you've printed it or you've copied it for others to use.Now you want to sort the list by Employee Name. If you simply click in column A, you can click in A2 or here, there, it doesn't make any difference, don't select the column simply click one of the cells here.
And if we click the AZ button located just next to the Sort button, this will sort the entire list, it won't just rearrange the data in column A, it will sort the entire list based on what's in column A. So now I've got an alphabetized list, maybe we print that. What if then we need to sort it again by Department, if we simply click somewhere in the Department column, click AZ, now the list is in order by Department. But recognize here, we have sorted by Department but what order are these people who are in the same department? Alphabetically by name.
So even though you weren't necessarily thinking of successive sorts, when you use the AZ and ZA buttons, the last use of this automatically overrides the previous orders but it does remember them. And so for example if we now sort by Status, we're going to see all of theContract people together, but they will be in order by their Department and within Department by Employee Name. As you get familiar with that concept and you get comfortable with sorting, you might find using the AZ and the ZA buttons faster than using the command.
So once again if we click in column D here and do an AZ sort, we're sorting by Status. All of our Contract people here are in order by Department and all of these Account Management people who are Contract are in order alphabetically, because that's the sort we did before Department which is before Status. So working with those buttons could be faster. There is no question that sorting is critical; it's one of the things we often do as we manage lists. If you only sort occasionally use the Sort button, the command take your time, consider some of the options that you might want to use here.
There is an option for sorting by column, but most people don't need that very often.
In this worksheet called Subtotals, we've got a list of data here; it's sorted by Department and Status. And what we'd like to do here is insert subtotals every time there's a change in the Department. In fact we might even want to do that within each Department, do it by Status. So we've got a lot of Account Management people here and whenever there is a breakpoint, in other words whenever we see a change here, we'd like to insert a new row that tabulates the data from above. Now not all of our columns have data that we could tabulate. However, it might make sense to show totals for Salary, well not total for Job Rating, maybe an average.
And so we can create subtotals, insert them automatically not by painstakingly doing it manually, by using a feature available on the DATA tab. In order for this feature to work sensibly, the data must be in some kind of coherent order. For example; if we want totals by Building, we're going to get totals all over the place right now because practically every other cell here changes, location of the Building. It's not sorted by Building. We do want totals let's say by Department and possibly within that by Status.
So the data is sorted already. Then because we have no empty rows within the data, no empty columns, we don't have to select all of the data. We can simply make sure the active cell is within our list, then go to the DATA tab in the Ribbon and choose Subtotal; it's in the Outline group. Quickly calculate rows of related data by inserting totals and subtotals. In the Subtotal dialog box we want to indicate which change in a field will trigger the insertion of these subtotals.
At each change in Department, there it is. We don't want to total all the fields; by the way Sum is certainly common, probably the most common but we could choose Average instead.Unfortunately you can't mix these within the same row, let's choose Sum here and which fields do we want to Sum? Excel will make suggestions but not necessarily all the ones you want. I think both of our Salary columns; we want to have a total. We wouldn't want a total Job Rating, that wouldn't be very helpful, total years of service possibly, let's just click OK.
The first thing you might notice is the entire list has been shifted to the right, we see outlining symbols along the left side of our screen. As we scroll down here and we come to the end of Account Management; we'll see a Total row right here. We're not seeing our headings for the moment, that's Years of service and we've got our two salary fields, many times you want to readjust these columns right here because we will see pound signs if the column isn't wide enough, so we'll do that. But recognize the ADC Department is substantially smaller, we see the totals here too.
And so every time there's a break in the data here, that is every time there's a change in column C, we see subtotals. Now those outlining symbols on the left-hand side of the screen allow us to collapse this view of the data. You see a 1, 2, and 3, currently 3 is active so no change there, let's click 2 and suddenly we've collapsed the data to show only the Subtotals and the Grand Total. There it is a handy bit of analysis there.
Choosing 1 is not that valuable; it simply shows us the Grand Total. Click 2, we're back to here. And Click 3, we're seeing the detail and the Totals. Now possibly, you could be thinking, can we work with our data like this all the time, what if we just leave these here? Well when it comes time to sort the data or to work with filtering, these might get in the way. So there is a general tendency to use this feature, print it, collapse it, copy it, that sort of thing and then when you're finished; simply remove the Totals.
Now recognize here that we also have the possibility because the data was sorted ahead of time of inserting an additional level of Subtotals by Status, that's column D. So let's go back to the command, making sure the active cells within the data. On the DATA tab, Subtotal and now at each change in Status let's insert Subtotals as well. Be sure if you're using multi-levels, as we're about to do here that you do not replace the current Subtotals, uncheck that box then click OK.
And now we're about to see two levels of Subtotals. Recognize we've got four Outlining symbols and as we scroll down here, for example in the Account Management Department, we've got some Contract people and there is the Contract Total, we've got some Full Time people, Full Time Total, Half-Time, Hourly and so on. ADC, pretty short list there but we see that break out too. If we click number 3 in our outlining symbols here, we've collapsed the list to show both levels of Subtotals and the Grand Total. Let's see this here.
Clicking 2 we'll see just the main level of Subtotals like we had seen before. In both cases here you might want to print this list or possibly copy it to another worksheet. Let's imagine I want to copy this to another worksheet. I'm going to create a new worksheet by simply clicking the plus to the right, down at the bottom of the screen here, this will add a new sheet to the right of Subtotals. So going back to Subtotals what if I want to copy this data here? So I'll select it, I want to copy it just as I see it. So I'll copy the list there, there it is, press Ctrl+C or maybe Right-Click and Copy.
Go to the other sheet, the empty sheet, Right-Click and Paste and that's probably not what you were expecting. We get all the data; surely there must be a way. Not an obvious way, but there is a way if we only want to copy the visible data. If you work with Filtering you may have gotten used to the idea that when you select data out of a filter, you can Copy-Paste it and it only shows you the data that's visible. But in other situations including this one, if you want to copy this data here and not include all that hidden data, you can use a keystroke shortcut ofAlt+Semicolon and when you do that, it selects only the visible data.
If we now Right-Click and Copy, watch the screen difference, you can probably sense what's happening. We are about to pick up the data in chunks or in pieces. Then if we go to the other sheet, Sheet1 and I'll Paste this just to the right of the other data, right here in J1, Right-Click and Paste, now we're getting only the visible data, that's what would make some sense. Now let's go back to the original data. If you didn't know about the keystroke shortcut or if you had forgotten about it, if you were selecting the data, it's buried pretty deeply in the Excel command structure.
So imagine we're highlighting the data, somehow we know or we think there is a way to copy just the visible data, where do we go; HOME tab, extreme right button Find & Select, Go To Special, Visible cells only. Remember the keystroke shortcut for all of this is Alt+Semicolon.Click OK, that selects the visible cells, then Right-Click Copy or Ctrl+C and then we can go to the other location, I won't do it again but we could Paste over here.
So there certainly will be times when you're working with Subtotals that you would want to copy the collapsed list, in some way if we'd collapsed the list this far too. Recognize also that if you are working with data and maybe you're using this for presentation purposes, if you were making a presentation of this data or maybe for printing reasons, if the question comes up about for example, how many Hourly people do we have here in Account Management here or who are these people. If we click the plus over here just to the left of Hourly Total, we will seethe detail for that particular group for the moment, for as long as you want to keep it there.
If we no longer want to see that detail, we'll click the minus. So we have this ability for presentation purposes to quickly expand and collapse our Subtotal list here. If at some point after doing the printing, the copying and the display, if you no longer want to see the Subtotals or have this feature in effect, simply go back to the DATA tab, choose Subtotal and Remove All, sounds drastic but all we're doing is removing the Subtotals and returning the data to its starting position. So the Subtotal capability, a quick way to insert subtotals based on a sorted list.
When you're working with large amounts of data as in the list here which has over 700 rows, you don't always want to see all of the data, we might want to see just the Full Time people, or just the people in certain departments, or just those who have been here so many years or have a certain Job Rating. The filtering capability of Excel works smoothly and easily. We're on the Filter worksheet in the workbook called 12-DatabaseFeatures. You can start filtering from the HOME tab by choosing the second button from the right and then choosing Filter or on the DATA tab in the Ribbon, simply click Filter.
And that gives us Filter arrows in the top row of our list. If we want to see just the Full Time people, we'll click the arrow for Status and this gives us a complete list of all possible entries within the column. And we can begin here by un-selecting all of them and simply choosing Full Time and then OK. We are only viewing the Full Time people. In the Status bar on the lower left-hand corner, it reminds us how many records we're seeing, 393 of 741 records found.
Recognize that when a field is being used for filtering purposes, the icon there is not simply an arrow but an arrow with a filter. And while we're looking at this list, if we only want to see the Full Time people who have a Job Rating of 5, our highest level, we'll go to the Job Rating column, click the arrow here,un-select all the entries and then simply click on 5. And now we're seeing a much smaller list, it's 102 out of 741 records. We're only seeing the people who have a Job Rating of 5 and the Status Full Time.
And if we want to narrow this further, we could, maybe we want to see people in certain Departments and it can be more than one. We'll un-select them all, maybe we want to see those people who have a Full Time status and Job Rating of 5, but only those in certain Departments, maybe the Environmental Health & Safety, Human Resources and possibly a couple of others here. It's your call, it's your choice; you click OK. And now instead of 102 records we're down to 14 records. And once again, recognize that the icon changes when that particular field is being used as the source of filtering.
If we want to bring back all the records, we could either click Filter twice or perhaps more directly, simply click the Clear button, the Filter arrows stay there but we are no longer using any column for filtering purposes. Sometimes you want to simply look for data. If we click in column A and we're looking for somebody named Sam, you might start by just clicking in the panel right here typing s. Now, although it's not obvious, all the entries below contain the letter s. Now they contain s-a; now s-a-m and there are no matches there. How about James? We know there is at least one there, we can see it down below and it's not case sensitive.
And each time as I add a letter here, the list below, although we can't see it all, is getting shorter and shorter. So if we were to click OK now, we would only see the word James as it appears there multiple times. Let's click Clear and bring back all of our data. Excel does recognize what kind of field you're dealing with. If we click the drop arrow for Department, recognize that we see the term Text Filters. So for example, we could be looking here for only those Departments that contain a certain word. The options that we see here are related to Text Types Entries.
Column H has salaries; we might want to see the salaries in a certain range. Because this is a numeric field, we see the term Number Filters and a different set of choices out here. So we could look at all of the salaries between a certain range, for example all of those that are greater than or equal to 40,000 and less than 50,000, example here less than or equal to or maybe just less than, we've got some variation there. So now we're about to see just the salaries within that range.
How many people are within that range; 134 out of our 741 records and here too we could be looking at other fields at the same time. To bring that back again, simply click here, we could also simply remove the Filter here this way and how about a Date Field here, drop arrow, Date Filters, this gives us some amazing capability. I think for hire dates some of these wouldn't be very obvious choices, but if these were sales dates think of how handy this would be if we were dealing with transaction data to just see the data from Last Month or Yesterday or Last Year and furthermore, if we looked farther down All Dates in the Period.
We could isolate the dates by month, no matter what the year is, let's get all the February entries here and we see all the people hired in February in different years. May be we'll use this to create the anniversary list. Drop arrow again, Date Filters and also don't overlook All Dates in the Period where we see it by Quarters as well. And so there are some very strong capabilities here in Date Type columns. Once again we could Clear Filter from Hire Date.Filtering is fast and easy. Recognize too that when you do have a filtered list, let's create another one maybe this time we'll choose just the Hourly people who have a good Job Rating.
We'll choose Hourly only by Unselecting all, then choosing Hourly, click OK, 54 of those, Job Rating 5, Unselect all, choose 5, click OK and now we're down to just 11 of these. If we copy this list, if we simply highlight and copy, Right-Click and Copy, automatically we are only getting this visible data. If I click plus at the bottom to create a new sheet and I paste this list out here, Right-Click Paste or possibly Ctrl+V, we've got just the visible data.
In a filtered list you never worry about that, we can of course print this too. Keep in mind too that whenever you're using filtering you'll see blue rows to indicate that we're not seeing all the rows. So this is a feature that allows us to quickly view just the data we want and to easily get back all the data simply by choosing Clear.
In this worksheet SplittingData, we've got our customer list, it's over 200 names and we'd like to be able to sort this list by the last name. But because the names are in first name-last name order in column A, we can't do that. This is a common problem, by the way. Also, if we want to sort our list by State, we cannot do that. Look at the entries in column D; City, State, Zipcode all together in one column. There are two methods for adjusting data here. One has been around for a while, it's a Text to Columns feature on the Data tab and a new one in Excel 2013 called Flash Fill will quickly allow us to readjust data as well.
Let's focus on column D first, click column D. Before actually splitting this data into separate columns, make sure we've got some empty columns to the right and we do here. The command on the DATA tab is Text to Columns; Split a single column of text into multiple columns. Note how they do make reference here of separating a column of full names intoseparate first and last name columns. We're going to be doing that in column A using Flash Fill. We could use this feature as well; but we're going to use it here. We want to split this data into different columns.
The Convert Text to Columns Wizard begins with a choice Delimited or Fixed Width. If we look at the data in column D, it's not every six characters or every eight characters in each case where we see a city name ending, in other words it's not a Fixed width. Delimited means characters such as commas in our case or tabs separate each field. So we want to choose Delimited, then Next and a Delimiter and these might not be checked, they could be checked based on the last time you've used this, so these might or might not be checked.
We don't need to see Space there, in fact that would be wrong to choose Space, because we see spaces between for example, San and Diego, Dana and Point and so on. It's just Comma that we care about. The Preview below this, going to keep an eye on it, scroll a little bit. If we're using Space I think you'd see we'd have a problem there, just use Comma and then Finish. And we've taken care of the Cities, we've isolated them based on the commas and we don't see the commas anymore. Now let's tackle column E. Looks like we might have a leading space but that shouldn't be a big problem.
We'll go right back into Text to Columns, this time Delimited then Next and it is Space that we want to choose now. It doesn't hurt to leave Comma checked; we don't have them there anyway and we see what's about to happen here based on this Preview, Finish, good. Column E is empty, we'll just Right-Click and Delete it, re-adjust these column widths and we are all set. We will be able to sort this list now by State or by Zipcode. Take a look at column A though, we still have a problem here, we cannot sort the list alphabetically by last name, we need to split these names into separate columns.
And although we could use Text to Columns, let's put in some new columns here, I'm going to drag across columns B and C and use the feature new in Excel 2013 called Flash Fill. So, in cell B7 here I'm going to type Jim. Now I'm going to type Lawrence and as I type the letter L, look what happens. Excel senses that I'm going to type only the first name and it fills in all theother first names below, they're kind of gray, you might not see them so easily, I'll simply press Enter and we've got all of our first names in place.
And then in C7, right here I'm going to type Duffy, Enter and as I type, T that's Lawrence Tibbett there, that T for the last name. As I type the letter T, we see what's happening; once again we'll press Enter. So ultimately what we're going to do here is get rid of column A, we'll get to that. Meanwhile we will re-adjust these columns, put in our appropriate headings for First Name, Last Name and we'll be able to sort by last name now. Now although we don't really need to put the name together we can do that also with this feature. I'm going to Right-Click column D and insert another new column and suppose what we might have, because it will be more efficient space-wise, to contain the names Duffy, Jim; Tibbett, Lawrence and so on.
So I'm going to type Duffy, Jim Enter and now as I type T, immediately Excel senses what I'm about to do, I'll press Enter and we're all set there. And so what I might do ultimately, I'll just move the Name over here and then delete columns A, B, C, Right-Click and Delete. So now we've got a list where we can sort by last name, in fact the column doesn't even have to be that wide anymore. We can sort it by the last name, we can sort by City; we can sort by State.
So the Text to Columns feature does by the way include some additional options that you might want to explore particularly in some of the other steps here regarding dates. But, if it's simply a question of splitting data in the columns, we can use either this feature or as we also saw the ability called Flash Fill that allows us to quickly do a little bit of typing and have Excel fill in the columns for us automatically.
In this worksheet called Remove Duplicates, we've got duplicate records. Row six, row seven, that's Michael Adkins, same Building, same Department, everything is the same, that's a duplicate. We've got two Thomas Allens, but they're in different Departments, so different people there. We need to get rid of duplicates. A list like this doesn't have to be sorted but maybe that's how you've found a few. There might be another one down here somewhere else here; there is another one yeah, 47 and 48, Tim Beasley. But think of how painstakingly boring it's going to be if we start scrolling through this list looking for duplicates.
And the list might be large. And by the way, you can get a quick read on the list if you simply click one of the column entries here. I'm clicking column C. What do we see in the Status Bar?Count 763 simply means we got 763 cells that have data in column C. We're also counting this, so we've got 762 rows below this. The list that we're about to use here does not have to be sorted. Maybe that's how we saw these duplicates, but that's not a prerequisite for using the feature of Removing Duplicates.
It's found on the DATA tab. And as is typical with a lot of database commands in Excel, if we have no empty rows or columns in our list, simply click within the data and then on the DATAtab in the Ribbon use the choice Remove Duplicates. In the Remove Duplicates dialog box we'll see a list of all the columns that had been selected. Now, possibly in some list you don't want to include all columns. I think in this case, we would. We want to say in effect if in every single column here all the way across, the entries are identical then we want to get rid of one of those or in some cases maybe if we've got the record in triplicate, get rid of the duplicates so we have only one of those left.
But do check that out at different times, sometimes you need only to check a few columns.Let's get rid of the duplicate records here, click OK. 21 duplicate values found and removed.That really means duplicate rows and we see only one Michael Adkins, we do however see two Thomas Allens because it's not a duplicate record all the way across. Seven hundred and forty one unique values remain, think of rows. Click OK and our list is all cleaned up. What the feature didn't do and sometimes you might want this but it doesn't happen with this feature, it didn't show us which records were duplicated, it simply got rid of them.
So that's what the feature really is all about, get rid of those duplicate records. It's a common database management problem and this command, Remove Duplicates on the DATA tab simply gets rid of the duplicate records in a list.
|Section 13: PivotTables|
One of Excel's most powerful and creative tools is a PivotTable. It's the ideal feature for making sense out of a large amount of data. In this worksheet called Creating, in the Workbook 13-PivotTables, we've got over 900 rows of transactional data here representing sales, usually multiple sales on a given sale. We've got our Salesperson, Product, our Customers are major retailers and they are located in four different regions in the United States and we'd like to beable to get a good quick summary read on what's happening here.
The data currently isn't sorted, it doesn't need to be. Think of a PivotTable as a separate entity, we're about to create one that will quickly give us summary information about this list. If you look at this list for a little bit, one of the things you might want to see would be a list of our Salesperson's names down the left-hand side and across the top maybe the products that they've sold and then how many at the intersection or possibly the dollar amount. I've done this manually off to the right, it's in column J. I'll just make column I narrower and scroll over there a little bit.
Here is the kind of list I'm thinking of: Customer names down the left-hand side, Products across the top and we can see how many Items have been sold 7997, almost 8000 items had been sold and we quickly see who the top Salesperson, we see which item is selling better than another one, this is good information. Take a look at some of the formulas required to get this though. This formula, not exactly easy, SUMIFS uses a function that's rathersophisticated and setting this up is going to take some time.
Furthermore after doing this, could we easily change this display so that we showed for example the customers across the top, which customers are buying from which salespersons?And as we look at the different fields there, we might want to compare, for example, Region and Product, or Product and Salesperson, Customer and Product, a variety of different interrelationships, somewhat like we're seeing over here in column J. With the PivotTable, we can create a list like this in under 10 seconds without formulas.
It's going to be that simple, and furthermore we won't have to worry about formulas like this one, we can easily change the PivotTable to include other fields across the top here. The PivotTable actually is a separate creation, typically placed on another worksheet, although we could place it on this very same worksheet. Although the feature is database-like in many respects, it's found on the INSERT tab in the Ribbon. A PivotTable not only gives us this quick ability to summarize information, it also allows us to sort the data, it allows us to filter it, it does consist of a number of database technics that you might already be familiar with.
We begin the process by simply clicking within the data. Now you just can't take any collection of data and say, well I'll use a PivotTable and analyze what's going on. The data you're working with has to be list-like in nature and that means you want your titles in a single row. If you other information above it, that's fine but it's best to keep the row above it empty if you do have other information up there and make sure that within the data that you don't have any empty rows or empty columns and that does facilitate the creation of a PivotTable because we need only click within this data here.
If you do have other data to the right as I do here, make sure you've got at least one empty column isolating that data from the data that you want to work with. So the active cells within here, let's create a PivotTable rather quickly. INSERT tab, left button, PivotTable. The Create PivotTable dialog box tells us the data that it thinks we want to use and it nearly always gets this right so a quick look here. This goes down to row 213 out to column H, looks good. The default location for PivotTable, new Worksheet.
Let's go just with it. Recognize you could put it on the Existing Worksheet right here. We could do that if we wanted to. Let's just click OK. We're now on a new worksheet. Off to the left, we see a PivotTable placeholder and off to the right a PivotTable Fields list. We can move this over if we wish, you don't have to do this; you can make it a bit bigger as well. As we make it bigger here, recognize we are seeing the field names right here that we saw in the previous sheet and possibly you can move this down to see them all, sometimes you'll do that but that's not critical but that could be helpful at times too.
Here are the fields that we're interested in. I'm going to click the box for Salesperson. Two things will change on the screen. Off to the left and alphabetized list of our salespeople and in the lower portion of the PivotTable Fields list, we see in the area called Rows, Salesperson.Let's now click the box for Items, let's say we want to track the number of items sold. That's a numeric field. If we check the box, it automatically goes into the Values area that may be justas important over in column B, we see the sum of items for each salesperson and there is that 7997 total that we saw on the previous worksheet.
Now something you wouldn't expect at first, if we do want to show the product here, we're going to drag the word Product into the Columns area and there's our PivotTable, we can move this to the right. The same total that we saw before and if we were to compare the numbers withthat manually created table, the same numbers in every single case, nowhere here do you see a formula, click here, click there, look in the Formula bar just the numbers are here, pretty fast.As you look at this you might say, what's this Row Labels, Column Labels? If it's only you working with the data, don't worry about those.
The focus is on the numbers and what they mean, but this does reinforce the idea that this portion of the PivotTable has a name, it's the Row area or the Row Labels area, you could use either term. We also see this term off to the right, in the lower portion of the PivotTable Fields list, we see the term Rows here. Salesperson is in the Row Labels area or the Row area.Column Labels, that's the term we see across the top, we see that also reflected in the PivotTable Fields list to the right. Now if you are making a presentation of this data, you might not want to see these terms here, you might have noticed already that when the active cell iswithin the PivotTable anywhere here we've got a PivotTable tools Ribbon in our menu system with an ANALYZE tab and a DESIGN tab.
Also whenever the active cell is within the PivotTable, we do see the PivotTable's Field list to the right. I'm about to click on cell E1, watch what changes. That field list to the right is gone and the menu that we had seen is gone as well. As I click back into the PivotTable, both of those features return. If we don't want to see these terms here, Column Labels and Row Labels we can go to the ANALYZE tab and simply not show Field Headers. In some cases that might be appropriate, surely those are products we sell; these are our salespersons so we don't necessarily need labels there.
But I think a better choice will be, let's return those Field Headers and then let's change these cells so that they actually reflect the names of the fields. And the way we do that is to go to the DESIGN tab and choose Report Layout. Now you wouldn't instinctively know that we have been viewing Compact Form. The other two variations Outline and Tabular do show the field names. The difference in these two is that Tabular shows gridlines, let's just choose this one and now we see Salesperson instead of Row Labels and Product instead of Column Labels.
We also see those two terms in our PivotTable Fields list to the right, down here. The more you work with PivotTables, the more you appreciate the PivotTable Fields list and the fact that the lower portion of it serves as kind of a map of the PivotTable itself. A couple of other quick changes you might want to make here, you might click in the upper left-hand corner, Double-Click a column boundary to readjust the column widths, now it's not critical but any other feature that you might be familiar with formatting, you can certainly use it too. So it certainly didn't take us very long to get here.
We have no formulas in sight and we will as you'll see in an upcoming movie the ability to quickly change the layout of a PivotTable. It's very fast to create a PivotTable as you've seen and you will have the choice at any time to put the PivotTable on a separate sheet as we see it here or on the same sheet as the source data.
One of the best features of a Pivot Table is the ease with which you can manipulate the fields.We're looking at a worksheet called Manipulating, it's in the Workbook 13-PivotTables and we're looking at a Pivot Table that was created earlier and we're thinking of changing the layout of the fields as we see them here. The Pivot Table fields list is off to the right. Sometimes this is anchored, you can click and drag its title and place it above the data if you wish or closer to the data and also make it larger this way. Again, as we look at the Pivot Table field list Product, Items, Salesperson, that's the layout we see to the left.
Imagine if you're making a presentation and the discussion about what we're seeing here has pretty much run it's course and the question that has come up, could we see this breakout by customer, possibly customer and region or customer and salesperson. So sometimes we want to make a change. What if I like to see Product down the left-hand side? I'm not interested in Salesperson for the moment, so I'll take Salesperson out of the Row Labels area. You can do it one of two ways. You can click and drag and remove it this way, another way is simply touncheck the box above.
For the moment we've got a pretty small Pivot Table, not very valuable. But let's place Product in the Row Labels area and then let's see who are the buyers of this product, perhaps we'll bring in Customer and put that in the Column Labels area and now we see this layout. We have the same total that we saw before, 7997 but a different layout. We don't have to worry about formulas, we still don't see any formulas here but we can easily change the appearance of this and another thought might have occurred to you, what if a few years ago you created a list like this, perhaps it was done manually and you like to recreate that list and in that list thecustomer's names were down the left-hand side, in that portion that we refer to as the Row Labels area and the Product was across the top, in that area we refer to here as the Column Labels area.
So if you were to think of the data this way and you'd like to flip the data, so to speak, or transpose it, the term pivot comes to mind. We're going to pivot the data from the upper left-hand corner and actually that's not a single action, it's two actions. We're simply going to reverse the order of Customer and Product and that means dragging these two fields to their opposite location and in no particular order. So I'm going to drag Customer from the Column Labels area into the Row Labels area and we see an interim view there that occasionally might be okay.
Let's drag Product to the Column Labels area and there is that layout. And as we do this at different times we might want to click in the upper left-hand corner and Double-Click our boundary to adjust the column widths. Based on what we've seen so far, you might imagine that a Pivot Table is mostly about comparing two fields. Well it often is, but certainly it isn't limited to that. If we want to show this breakout here also by Salesperson, we could bring thatfield back into list. Because that's a text field, if we'd click the box for Salesperson, it willautomatically appear in the Row Labels area.
If we want to go to Column Labels area we have to drag it there. So I'm just going to check the box for Salesperson and we see the list here. Now that's not very compact, that doesn't mean it's bad. But if we were to print this list, we can see that it's certainly lengthier than one we've been seeing but 70 rows is not outrageous, that's a reasonable amount of information and a lot of detail too. If we change the order of Customer and Salesperson, not exactly an instinctiveidea, but let's do it anyway, watch what happens to our list. Currently as we view the Pivot Table, Customer is to the left of Salesperson.
As we look at the Pivot Table field list, we see Customer above Salesperson, so let's drag Customer downward and now we see the display this way. A different way of viewing the data and it's up to you to decide which you like best; you can change your mind at different times too. You might also be seeing as we do this, for example subtotals. You may or may not want those. If you don't, go to the DESIGN tab, Subtotals, Do Not Show Subtotals, we see the list this way. And once again we might want to switch back and forth the order of Salesperson and Customer just to see how it's looking.
And at different times you want to see a list a certain way, you can see it another way. All the while we're manipulating a Pivot Table; there is no possibility in any way of altering the original data. Think of the Pivot Table as a separate entity and we can change the layout any number of different ways. There might even be times when we say, you know I don't want to see thissalesperson here but I want it nearby so I can get to it quickly. We can drag Salesperson into what's called a Filters area, right there. Recognize that in the Pivot Table now, we have an upper portion here, this is the Filters area.
The advantage here might be we only want to see data about one person or maybe two of our salespersons, click the drop arrow there and maybe we just want to see how well Emily has been doing, there is Emily only, her sales, 632 items sold altogether. And you've got other options up here as well, there is a choice for selecting multiple items, let's say we also want to see in our list here John Lucas and now we are seeing jus the data for those two people. So the Filters area has a role to play as well.
If we no longer need to see this differentiation, we could just possibly remove Salesperson from the list completely or possibly bring it back into the Row Labels area. Now there's another aspect of Pivot Tables that does surprise people. It's almost as if we have created a chart here in the sense that we've created something different, it's a different view of the data, it's certainly based on data in a different worksheet in this example, but what happens if thesource data changes? So as we look at this list here, remember the total is 7997, let's suppose our list here is showing the Salesperson and the Product.
So I'm going to remove Customer, this time dragging it off, click the box for Salesperson, we see our salespersons here and I don't know if you remember but a few minutes ago when I actually removed it from here, we were using a filter and now that filter is still there. So if we look at Salesperson over on left-hand side and click the filter arrow there, there we might want to now Select All of the names there, click OK. In this list our total 7997, our top seller here is Harlan Vaughn 1068.
I want to show you what happens here and this might be a surprise if you haven't worked with Pivot Tables very much. I'm going back to the source data. It's on the sheet called Creating.I'm going to make a change to one of the records. We've got an update here and actually 12 items were sold here. Again, remember our original list is 7997. If this is going to be 12 that list is going to go to 8000. So I'm going to change this to 12, that will have the impact in the cell to the right which is calculating a total, you would expect that to change and surely our Pivot Table will have changed, right? If we go to Manipulating, it still says 1068; we still have the same total as before.
So what's happening here? In Pivot Tables, even though they are based on source data on another worksheet possibly or maybe on this worksheet, Pivot Tables don't change when the source data changes. You have to make that happen. And so with the active cell within the Pivot Table, go to the ANALYZE tab in the Ribbon and choose Refresh. Also there is a keystroke shortcut, Alt+F5. And what has happened, it's now 8000 and this number here which had been 1068 is now 1071.
So refreshing your data is something you need to do as you work with Pivot Table data. And there is another aspect to this as well as you work with Pivot Tables. Suppose you're having a meeting here and you're looking at the numbers and for whatever reason, one number jumps out at this person to that person, somebody wants to see the detail behind one of these. Who are the 19 customers here who purchased the bamboo coffee table or Icelita Kelly was the Salesperson? Double-Click, what's happened here, we're in a brand new worksheet and we'reseeing here a list, these items here do total, 19 there they are.
This represents the source of that number, in other words what we're seeing here and let's adjust the column to make it a little bit more readable. This is data gathered from the source list and placed on a new worksheet in table format and so this represents the 19 that I Double-Clicked on. Here are the 19 items that were sold. And so as we go back to the Pivot Table right here, when I Double-Clicked 19, suddenly it's as if we go back to the original data we find these 19 items and we see them on a new worksheet, that's called drill down and we canDouble-Click on any of the numbers in this list here and get the same effect if we're interested in that.
Now, if we make changes to our source data and then update this Pivot Table, this sheet isn't getting updated and so it could be incorrect. So when you do create these lists, keep in mind that you might want to get rid of them pretty quickly. But if you're not changing the data that much, they're going to be accurate until you make changes. If you no longer need the sheet, simply Right-Click and delete it. But it is a great feature called drill down and sometimes you'll want to do that. So we can see as we work with Pivot Tables we change our minds sometimes about the appearance of it, what it is we're looking at, we have the ability to move these fields back and forth.
Sometimes we're just looking for a better display; sometimes we're really looking to analyze the numbers in greater depth. The capability to manipulate and change the appearance of a Pivot Table is one of its greatest strengths.
Grouping by date and time
In this worksheet called DateGrouping, we see a Pivot Table with the date field in the Row Labels area and this is not a very compact Pivot Table. There are no restrictions on how big a Pivot Table might be, but this is not much of a summary hare. We're seeing the breakout day by day over a two-year period on any day where there was any sale. Let's compact this. If we have date entries and all of our date entries are valid, we can simply Right-Click on one of these date entries and choose Group.
Excel recognizes the data as date typed data. It suggests possible months. Let's go with that and see what happens, click OK. And immediately, we're seeing a summary by months. Now, remember this is a two-year period, so this might not be quite what we're aiming for and yet you can see pretty quickly how this summary is practically instantaneous. We've got totals by month covering a two-year period across the different regions here. Let's Right-Click on one of the months here, chose Group again. Earlier when we saw this list we could have grouped by quarter and years as well.
You simply click on the additional fields you might need. So, we might choose any two of these or all three of them or just one of them. Recognize also that if we were looking at time data, we might want to group it by hours of the day. But let's take a look at Months, Quarters, Years as we click OK. Now, we see a nice breakout here by Year, and Quarters, and Month.Now, the word Date here is a little bit off. These are months so let's change that and you can do this within Pivot Tables. Let's just call that Month, much better.
And you might or might not have noticed that in the Pivot Table field list we see the word month. Now, the source data still does not contain a column with the word month, but we see that here as we work with the Pivot Table. Now, scrolling up and down a little bit, this list is not that large. It's relatively compact. It goes down to row 29 here, but as we look at this list here and click back within the Pivot Table and see our Pivot Table field list, recognize that in theRow Labels area, we've got Years, Quarters and Month.
Now, what might we do here if we want our list to be a bit more compact? Maybe we're going to try looking at this just by Years and Quarters. So, let's take Month and to keep it handy and nearby, let's put it in the Filters area and now look at our Pivot Table. We see totals by Years and Quarters. Now, let's try something that you might not instinctively try. Flip the order of Years and Quarters within the Row Labels area. So, I'm going to drag Years downward, watch the display now.
That's probably not as common as the previous view, but it does allow us to show for example, our two quarter ones next to each other for 2010 and 2011. Here we see our two quarter twos and so on. So, that kind of a grouping not exactly obvious at first also emerges from thiscapability, let's put it back again. Let's bring in Month, take out Quarters for the moment. Now, we're seeing Years and Month, and here too might we flip the order of Years and Month? It doesn't seem like the most obvious thing to do.
Here we're seeing our two January's next to one another. Now, when you manipulate date at different times, sometimes you will see subtotals, sometimes not. If you don't want to see them, on the Design tab in the Ribbon, Subtotals, Do Not Show Subtotals. If the view that you have here is by way of the Design tab, Report Layout if you choose Outline or Tabular, sometimes the capabilities of these two are a little bit different. Let's go to Tabular form. You might have seen a choice there under Subtotals, Show All Subtotals at Bottom of Group or at Top of Group.
Now, on the example here, I chose top and yet they appear at the bottom. That's because on Report Layout and you wouldn't know this until you tried it. Tabular form doesn't recognize that choice, Outline does. So, I made the choice to put the Subtotals above and they stayed below.How about show in Outline form now? Now, we see our subtotals above. In other words, here are the totals for two Januarys. So, that's a bit of a change that you might not pick up on at first. If we don't want to see them again, let's just jump back to Subtotals, Do Not Show Subtotals.
And now it's a simpler view. Experiment with these two choices too though and see the differences. Tabular will show gridlines as well and show the display this way. Now, there are a lot of possibilities for changing the way we group Month, Years and Quarters and so although we're not counting the number of possibilities here, just by simply moving these fields back and forth and in different ways, we can get a good quick view of what's happening based on time criteria here over Years, Months and Quarters by using this grouping capability as we see it here.
One other option that wouldn't be so obvious either, if we were for example here, to simply Right-Click one of the year entries and choose Group again and this time turn off these three groupings but instead group these by days. Now, that doesn't sound like it's what we want at first, particularly if we see number of days one. But what if we change that to be 14? What we're now going to get is a two-week grouping and maybe it so happens that in your organization, you group your sales or you tabulate your sales on a two-week basis.
Let's see how this date looks grouped every two weeks. Now, a little bit of fine tuning touch here, you might want to go to your calendar and you probably would want this to start on a Monday or possibly a Sunday so you might want to revisit that and start these on a different date, but you could quickly see we're talking about 14-day periods here. If this covers two years we're about to see 50 rows or so here. So, that's another kind of grouping. If the data had been hours, we could group these by hours of the day to see which hours of the day provided the most sales as well. So, the ability to group information by date and time is a powerful toolwhen using Pivot Tables.
Grouping by other factors
In addition to grouping data by date and time factors, Pivot Tables also gives us the possibility to pretty much create grouping on our own, sort of an ad hoc way. We're looking at the worksheet called OtherGrouping in our file 13-PivotTables. Imagine if somebody asks us to group data by the east region. Now, we don't really have an east region, we've got a northeast, we've got a southeast. You can move fields. For example, if we want to move southeast next to northeast, just click that cell, drag its bottom edge.
Those two fields are now adjacent, northeast and southeast. The date is together. Suppose we want to create a grouping called East. Select just the two labels, Right-Click Group. Suddenly, we see a new row in the term GROUP 1. That's not very meaningful, so let's just call it East.Recognize there's a minus in front of it. Let's click the minus and before doing that, look at the total. We got a northeast and a southeast. This total here is going to be about 4000 or so. Let's just click the minus, what happens? There is the total.
What we're seeing here is the total of the northeast and the southeast with no detail. If we group the east that way we probably want to group the west similarly. So, drag across northwest and southwest and here too, Right-Click and Group and we'll simply change that word group to West.We're going to type right over. Now, we can collapse the west as well. So, for the moment we're seeing East and West, new grouping that didn't really exist in our original data. In the Pivot Table field list to the right, recognize that we now have a field called Region2.
You might leave that there or click on it, give it a different name, maybe east-west or something like that to differentiate a bit. So, we now have the ability to view this list whenever we wish in the East, West breakout or in the four breakout region the way we saw it earlier. So, we can create our groupings in an ad hoc kind of way. We can also do this with data in the Row Labels area. So, if we simply want to group these two, maybe these are the two bamboo items, we're going to bring out that idea. So highlight those two, Right-Click>Group.
You see the term Group1 again, let me just type in Bamboo. There we are, and we could collapse that. So, we got our Bamboo total and then the other ones we haven't grouped just yet,maybe we won't but we have that capability. So, grouping sort of on the fly or in kind of an ad hoc way, we can do this easily in a Pivot Table simply by selecting the two, Right-Clicking andchoosing Group.
Using slicers to clarify and manipulate fields
If you want greater control over a Pivot Table when you're making a presentation or when you're simply analyzing data, you need to know about Slicers. This is a relatively new feature in Pivot Tables introduced in Excel 2010. In this particular Pivot Table we're seeing Salespersons in the row labels area, Product in the column labels area. Now, suppose we want to see just some of the products. A standard way to make this choice is to click the drop arrow for Product and unselect the items we don't want to see. So maybe we don't want to see the two tables here and we're not seeing them and the totals are adjusted.
Now, do we know which fields were not seeing? Well, if we just made the change, yes. But, we might be looking at the Pivot Table in later time. Do we know our product list that well? Do we know what's not being seen? So, one aspect of using these arrows is that we might at different times want to know not just what we are seeing but what we're not seeing. And maybe we want to focus on just some of our salespersons. And so similarly, we could go to the drop arrow for Salesperson and not select all of the names. I'm not doing that but let's say, that we certainly could do that as well.
Now, Slicers which is available on the ANALYZE tab in the ribbon when we're using the Pivot Tables is found in the Filter group. Insert Slicer what we're about to see here is all of the field names. Now, we're not using all the fields right now. We're not using for example Region, we're not using Customer but we still might want to see Slicers for our various fields here. Perhaps not all of them and you want to experiment with this; but I'm going to use Salesperson, Product, Region and Customers, a small problem here it can be, our screen is going to be a bit crowded.
Let's click OK and see what we'll see here. We've got slicers now for Customer, Region, Product and Salesperson. We can make these a bit smaller something like that, take a bit of time to do it for all of these, you don't have to do this but just a reminder, you can redesign this as you wish. As you're manipulating these two, recognize that there is a SLICER TOOLS ribbon in the menu system also with an OPTIONS tab; we can change colors, heights and so on. Products, same idea. Now, the advantage of these is two fold. One, is we can see which entries are not being shown.
So, for example, right away we can see here on Product the two items that are not showing. If we only want to look at two of our customers, say Ellington Designs and Home Emporium.We'll click Ellington Designs and we're only seeing that customer and now holding down Ctrl if we click Home Emporium and we see the two of those together. And so, as we're looking at the Pivot Table, even though customer isn't even visible anywhere we're only looking at two of our customers. And so ultimately this could be better than what we're seeing with the standardPivot Table field list.
And the more we move these, the more you manipulate them and you'll decide what's best in terms of how many of these you want to see but that gives us a clear picture of what this Pivot Table is all about in terms of what is showing and what isn't showing. You can make the Salesperson slicer by the way, be two columns. It might be a good choice, and then possibly make it wider and that way we can see the names that way too. So at any given time when we're working with this Pivot Table, we don't necessarily need our Pivot Table field list although it's certainly is helpful to remind us of the layout.
But let's say we move that aside a little bit by dragging its title at the top. Let's drag it over here for example, and focus more on the slicers. The red X within the slicers looks like it's a way to get rid of the slicer but what it really means is clear the filter. In order words, select all of the fields. So click the red X to select all of the fields, and in effect we're not using Product now because all of the products are being shown. Once again, if we only want to look at two of these, click that one then use the Ctrl key. If there's a consecutive cluster we want to use, if we want to use the first three; click Bamboo Coffee Table and then with the Shift key click Captain Recliner, so we see those three.
So, at any given time with these slicers here you know what's showing in the Pivot Table and what's not showing.
When you're working with Pivot Table data, invariably it's about the numbers. And as we work with numbers in Excel sometimes it makes sense to have a chart as well. You can create a Pivot Chart along with a Pivot Table. Also at any time when working with an existing Pivot Table, you can instantly create a chart. For example, if we go to the Slicers sheet in this workbook, Pivot Tables. If we want a Pivot Chart based on the Pivot Tables that's visible simply click within the Pivot Table and press Alt+F1 and there's a chart.
One suggestion with Pivot Charts, make them be stacked column. Not always the best solution, but many, many times even when a Pivot Table doesn't seem to have that much data and a Pivot Chart looks fairly crowded as this one does. Let's go to the Design tab in the ribbon when the Pivot Chart is selected and the second button from the right, Change Chart Type, Stacked Column, possibly stacked the bar, Double-Click tends to create a cleaner lookingchart, not so crowded. If you have created a Pivot Chart it is in sync with the Pivot Table at alltimes, so if for example, if we go into the Pivot Chart fields to the right and change the location of Product and Salesperson.
I'm going to drag product down here. Notice the look of the chart right now, fairly crowded and the labels look terrible across the bottom and put Salesperson here. Also note that the wording looks different than it does typically here. Instead of seeing column here, we see the Legend (Series) and instead of row we see Axis (Category), but I think you can see here the chart will at times compliment the data to give us that quick visual read that we want. Let's go back to the worksheet called PivotChart and show how when we're creating a Pivot Table we might want to create a Pivot Chart as well.
INSERT tab, we do see the choice Pivot Table off to the left but off to the right in the Charts group we see PivotChart, click it. And we're on our way to be creating a Pivot Chart. Now, although we don't see the term Pivot Table at the top, we will be creating a Pivot Table as well.Suppose we want both of these on this worksheet. Choose where you want the Pivot Chart to be placed; Existing Worksheet, Location, how about J1? That's the upper left hand corner, click OK and we're about to see a Pivot Chart.
These screens tend to be a bit busy so let's scroll rightward, and chart fields, let's say, we want to see Salesperson, down the left hand side. Product, now I'm dragging this into and area called Legend. This is analogous to column and let's say, we're tabulating; this time maybe Total Cost, drag that to the VALUES area and we got a Pivot Table as well as a Pivot Chart.As you would expect changes to the Pivot Table immediately occur within the Pivot Chart as well.
If the Pivot Chart is selected, the terms we see here as I pointed out earlier are bit different but the concept is the same. If we click within the Pivot Table as I'm doing now, we see the term Columns and Rows. Again, we can change the order this way, Product, Salesperson, drag product into the row labels area maybe, drag Salesperson in the Columns. When we are looking at data like these two, particularly with the large, large numbers, one of their feature here that you want to have control over is when the numbers are looking like this, a bit difficult to read.
First of all do we really need the decimals here? Perhaps not, Right-Click anyone of these, chose Number Format and from there choose an option that doesn't show decimals and yet does show commas as a thousands separator. And so that cleans up the list a bit, and now we've got our Pivot Chart and Pivot Table together. Remember, they're always in sync. You can create a Pivot Chart instantly by simply clicking in a Pivot Table and pressing Alt+F1 or you can create a Pivot Chart and Pivot Table together from your source data.
|Section 14: Using PivotCharts|
Excel has a number of analytical tools and they fall under the heading What-If Analysis. You'll see these on the Data tab in the ribbon. One of them is GoalSeek. And the description we see on the screen doesn't really do it justice. Find the right input for the value you want. In this worksheet called GoalSeek, there's a function in cell M5. You may have seen it; it's called PMT. What we're trying to do in this particular cell is calculate the monthly payment for a loan of $24,000 based on a 60 month term.
Interest rate is 3.5%. The answer that we get here is 437. So, if you borrow $24,000, pay it off over five years at 3.5% interest then, monthly payment is $437. Imagine a situation where you want a different answer. Maybe it's based on a real life wish that you want this to be lower or maybe you're saying, I know I was planning all along on spending 500 month, let's just make this answer to be 500 or 400 as the case may be. GoalSeek feature in a certain sense let's us work backwards.
It doesn't use sophisticated math, it uses brute force. We're going to say, we want this to be a different answer. Click this cell on the Data tab, choose What-If Analysis, choose GoalSeek.Here's the Goal Seek dialogue box. We want to set this cell M5 to be the value 500. By changing which cell, Goal Seek only allows us to change one cell. The function in cell M5 is based on data from M2, M3 and M4.
We can only change one of these. Suppose it's the principal at cell M4. If we're going to be spending 500 a month, we will be able to borrow more but how much? Click OK, and we see that amount 27,485. If we click OK, we keep the result. If we click Cancel we return to the original values. You might want to try this feature changing just the principal as I did orpossibly change in the rate. You might not have control over that. If the borrowing institution allows you to change the term but keep the same amount, you might want to cancel, try this again, What-If Analysis, GoalSeek.
This time let's set the cell to be equal to the value 500, based on the term changing. Click OK.So, we could pay $500 instead of 437 and then pay off our loan in 52 months instead of 60.So, there are certainly different ways to use this. Let's take another case, scrolling down in the same worksheet. We're taking a course somewhere and here are the scores we've got on various tests. At the bottom here, in cell M14 is a formula that's averaging the cells, andrecognize that it's averaging the empty cell as well; and the way this function works, if you work with it, you know that Excel adds up the data that it sees but it divides by the number of cells that have data.
So currently it's adding up the numbers and dividing by 6, but as soon as we put a number in this cell M13, the average will add up those numbers and divide by 7. So we want this average to be 90, so it can get an A. We'll go to What-If Analysis, GoalSeek, set this cell M14 to be equal to 90 by changing which cell, the cell that's currently empty, M13. Click OK, and oddly enough it seems to take a while to get there.
And finally when it does get there we get our answer and it's not too satisfactory, we got to get 102, well we can't do that. So, let's cancel this. Maybe if we get 89.51 we'll get our A, so let's try this again. What-If Analysis, GoalSeek, set this cell M14 to be equal to 89.5, by changingwhich cell, this empty cell again M13, click OK. So, if we ace this final test and get a 99, looks like we got to get that then we'll get our 89.5 average.
Maybe we'll get the A after all. Now, let's take more business like situation here. Column H has a bunch of salaries, over 700 of them. New salaries that have been calculated based on the old salary and a percent increase of 2.7%. So, every one of these salary calculations is based at least partially on this 2.7% in cell J2. A total in J1 of all those entries in column H is 38,889,000. Let's imagine you are the chief financial officer of this organization and you say, well, in our prior planning we were planning on 39 million expenditure on salaries.
Let's make this be 39 million. In other words, let's recalculate all 700 or so of these formulas so that our total is now 39 million. So when you use GoalSeek here, What-If Analysis, GoalSeek, and we want that cell J1 to be equal to 39 million 39,000,000 you don't to put in the comas but you can. By changing which cell, the cell that's got the percent in it. Now you saw how long it took with our example of those test scores, how long is this going to take? Click OK, it's done.
There it is, if we want to keep it we'll click OK. Looks like 3% is what we need here. Now, when you use GoalSeek, don't assume these answers are exactly what you see. If I click the cell, this is the one that change from 2.7%, I click this cell, look in the formula bar. It's really 2.99175 et cetera; it's a big long number up there. Now, someone has to make an exacted decision and decide what to do, so you might just Double-Click and change that to 2. 99 and it's probably going to be close enough.
There we go, but as I press Enter our total will change. It'll be a bit lower, not a whole lot lower.So, you can see how this feature works. It's ideal for working backwards so to speak. We want a different answer for our formula and we can do this by using GoalSeek to change one of the cells, that's a component of that formula.
If you'd like to get a different answer for a formula or a function in Excel, and you would like to be able to control the variation of a number of other cells that control that formula, you want to be considering the feature called, Solver. We're looking at the Solver worksheet. This feature is actually an extension of or an optimization of the GoalSeek capability. We want a different answer here. We want this to be 265,000 in cell E14. The formulas in this worksheet and they're in columns C and D as well as E essentially build off of what's happening in column B. To activate "Solver", you need to first make sure that this feature is added in to your Excel capability.
If you go to the Data Tab, if your version of Excel has already added this feature in, you'll see the word Solver in the Analysis group, just to the right of the Outline group. If you're not seeing this group or not seeing Solver, you'll need to take the following step. It only takes a few seconds and you need only to do it once. Go to the File tab in the ribbon, choose Options and in the list of choices here in the Excel Options dialog box, down the left-hand side, choose Add-Ins.
And then in the list of Add-Ins choose Solver Add-in and then, at the bottom of the screen here, Manage Excel Add-ins, click Go. And here we see a list of Add-ins, Solver on my screen is already checked since I already have it installed. In your case, if it's not there, you will want to check the box for Solver and then click OK. And within a few seconds you will have activated the Add-in. And then on the Data Tab you will see Solver. The pop-up description says, What-if analysis tool that find the optimal value of a target cell by changing values in cells used to calculate the target cell.
So lets imagine that this cell E14 is one that we want to change. We want a different answer.Now it's a formula that simply is subtracting two other cells, but those cells are getting their data from a lot of other cells. And ultimately, the cells that trigger big reactions in this particularworksheet are the once we see in column B. So unlike GoalSeek which does allow us to get a different answer, based on one other cell changing, Solver allows us to come up with a different answer based on multiple cells changing.
Let's go to the feature called, Solver on the Data Tab in the Analysis group. Here's the Solver dialog box. Set Objective, we want to change the cell E14, Click or type, E14, set our objective to the Value of 265000. By changing which cells, now we could change the Sales entry for January, the Shipping entry, the Goods, the Freight, the Miscellaneous, maybe all of them.Lets just change a few of them.
I'm dragging across cells B5 and B6, and also I'm going to select B10. Now we want to allow these cells to change. But we do want to be realistic about it. So, let's choose Add; this means Add constraints. The first constrain is that we want this cell B5, lets click on it, to be less than or equal to a certain amount. So let's imagine that we want to allow that January sales number to grow somewhat but not too far. How about 139000? Now that might not be enough, we don't know that yet.
Let's add another constraint. Let's allow our income from shipping, that's in B6 to grow a bit but certainly no more than 28,000. Let's add another constraint. And again, being somewhat optimistic, this time let's focus on Goods, the cost of goods in B10. This time we want to allow that value to drop, but not too much but we want to reverse the arrow here to say that that must be greater than or equal to 75,000.
That's all the constraints we want, we'll Click OK. Here we see the three constraints listed, two of those can grow but within a certain limits, one constraint but within a certain limit. We want this cell E14 to be equal to 265000 lets Click Solve and we get a pop-up box, Solver found a solution. There certainly will be times where the message will say, Solver did not find the solution. And sometimes you'll see a number in the appropriate cell that's very close. The example here, we've got a total, and we've got a new number for Sales, it's 137,687 a new number for Shipping and a new number for Cost of Goods.
And we've got our total here So we've got a couple of options, we could restore the original values; we could keep these and Return to Solver Parameters Dialog. If you're working with this and you decide to go back to original values, if you do want to experiment, you might want to make a copy of this worksheet before going here. But if you're fairly confident about what this feature can do or you've been told that it's a good one, you might just want to Click OK, possibly keeping the results here. What I'm going to do for the moment is Restore the original values and return to Solver Parameters Dialogue box by Clicking OK.
So our numbers are back to what they were. What I didn't point out earlier and something you might want to look into, and this may involve some knowledge about Solver that I'm not familiar with. This is a very sophisticated feature and we didn't talk about the Solving Method.You might not be familiar with the terms GRG Nonlinear, Simplex LP. Each of these has a description here and possibly as you'd make these choices, you might also want to explore Options. And there are different choices here, different choices for Nonlinear, different choices for Evolutionary, Mutation Rate, Population Size, Random Seed.
And so, familiarity with those terms will help you make different kinds of decisions about how to make this feature work for you. And if you want more advice I'm simply going to close this here, it should have to go to the site www.solver.com. You'll get more information on this. This is a classic Excel Add-in. it's been in Excel for any number of years, but this website gives you a lot of detail on how to make this work most efficiently. I think you can see from the small example. This is a sophisticated tool that allows us to do a considerable amount of financial analysis.
In this worksheet called, Scenario, we're looking at a 2014 Budget Projection. As I scroll to the right, we had monthly totals, quarterly totals, and a grand total in column R. And scrolling left toward and then downward, we see the items being tracked here. Sources of Revenue, Cost of Goods, our Expenses, and at the bottom in row 35 and Net Profit. And the lower right-hand corner cell R35, the Gross Profit for the entire 12-month period projected. That same number is reflected in the upper left-hand corner, as I press Ctrl+Home at 450,000.
We also see it here. A brief look at the formulas here, the February formulas for the entries we see here are based on the January entries with a 1% change. March at 1% change on February. Second quarter, our numbers are based on 3% changes. Ultimately, we might have a percent change in every cell here, but all the entries for example in the May column involve using the April numbers on a 3% change. April is based on March; March on February and so on.
Let's imagine that we might want to show these numbers with different results. And one option surely can be and relatively straightforward, we can make multiple copies of this sheet. A simple Ctrl+Drag, we could create a new sheet that's identical and then change the numbers.But it might be easier to work with if we had a quick, easy method of showing different scenarios within the same worksheet. So let's imagine that this is our first look at the numbers and we want to hold on to this, this 450,000 number.
But we're also thinking of showing different scenarios with lower sales or higher sales, maybe both or maybe a combination of what happens if we have a higher cost of goods, lower cost of goods. So we might have five or six different scenarios in mind. Let's create a couple. But let's first begin by storing or holding on to this scenario. And the feature that we're about to use is on the DATA tab, What-if Analysis, Scenario Manager. There are No Scenarios defined.
Now let's add a Scenario and let's just call this normal or typical or Normal is good enough.And we really don't want to change any cells here right now. But let's say looking ahead to our other scenarios, we might be changing cell B5 and also cell B10, put a comma we're going to change those two cells. And just those two although we certainly could change more, in fact we can change up to 31 different cells. Let's Click OK.
Now, right now we wanted to keep these settings 137,000; 76,000 for the two cells in question.We'll simply Click OK, simply to define the so called, Normal Scenario. Now, let's add a new scenario, and we'll call this one LowSales, or maybe LowSales-HighCost of Goods. Type it anyway you want. Changing cells, let's keep the same ones we had before although we didn't change them before, now we will, Click OK.
So the LowSales-HighCost of Goods model is going to project not 137,000 as our Sales, but let's say 130,000. And let's say our Cost of Goods have gone up to 80,000 under this scenario,probably the worst of all scenarios, these two numbers. We Click OK and we've just defined this. Now we're not seeing it yet, and we still see the 450,000 total, so let's show this scenario.Show and there it is 308,000.
We can show the Normal again, if we wish go back here; show that as we can bounce back and forth. There are other ways to do this too. Let's add a third scenario. This is going to go on the opposite direction, and we'll call this HighSales-LowCostofGoods. Perhaps the best of all possible worlds, Click OK. And this time, instead of 137,000, earlier we had 130,000 for the other direction; let's go up to 145,000.
And for the cost of goods, let them drop to 70,000. Click OK; we've just defined this scenario.Do you want to view it, of course, Click Show and there it is. Under this scenario we'll make $646,000. Now, we can always get to these scenarios, remember they are all on the samesheet, simply by going to the DATA tab and choosing What-if Analysis, go to Scenario Manager and pick the one we want to see and then Show it. But at any given time it could get a little slow, and what if we've got five or six of these? Having to keep this dialog box open or getting to it.
There might be a better way to do this. Oh, there is. If you Right-Click anywhere within the Quick Access Toolbar, typically this is located above the ribbon although it could be below, Right-Click anywhere there and go to Customize Quick Access Toolbar. And in the list here, you might see popular commands. Scrolling up and down, you don't see Scenario but there are other choices here. We also see commands not in the ribbon, of course they're alphabetized.And in this list here, you might see Scenario. There it is right there, Click this.
Because it has an arrow to the right that implies it's going to have a drop-arrow and it will. Then add this to the Quick Access Toolbar and then Click OK. So now without activating that command that we just saw, anytime we're on this worksheet, we've got this button here, we can Click the drop arrow. Let's view the HighSales-LowCostofGoods Scenario, there it is. Or at a different time, perhaps during a presentation, let's view the so called, Normal or Starting Point Scenario, there it is. Or of course the other one is there too.
And in some situations maybe we'll have five or ten a few more even. So this capability allows us to essentially store different scenarios within the same worksheet. So we don't need multiple worksheets here. And so in the example as we've just seen here, we've created three different scenarios easily accessible by a way of that button, or by a way of the command on the DATA tab.
In this worksheet called DataTable row 3 contains different interest amounts and they're incremented by an eight of a percent. The formulas in all of these cells except for cell C3, and in column B we've got amounts. Maybe we're considering borrowing money, and these are incremented by 25,000. And what we'd like to create here is ultimately a grid containing the monthly payments with these different interest rates and amounts being borrowed. In cells B1 and B2, we have got 3% and 200,000.
Let's put in a formula here in B3. We're going to be using the Data Table feature. It's found on the Data tab under What-if Analysis. Let's first set up a formula in cell B3. Using the function PMT, this allows us to calculate the monthly payment based on an interest rate, in this case 3%. We want to divide this by 12 since we're paying our interest monthly, the term here 360 months. We'll show you later how we might want to change that.
We're setting up here what might be called a master formula, and we want to borrow 200,000. If we do press ENTER now, the amount that we see will be expressed negatively. So let's put a minus in front of the B2 here, so what we are about to see will be the monthly payment on a loan of 200,000 at 3% over 360 months and there is that amount. Now, think of this as a master formula. The Data Table feature is going to ask us what we need to use for a percent change.
We will be referring to this 3% cell, but as we select these cells, Data Table is going to fill all these in rapidly. Data tab, What-If Analysis, Data Table. It asks us for a Row Input cell. Which row entries, meaning the percents here are we referring to? We refer to cell B1. The column input cell, that's cell B2, okay, and there's our list.
Now, although we have a formula here, we really don't need these input cells anymore, they can be empty. We could just highlight these and press Delete. It's not going to change our results at all, we could get rid of the color if we wanted too, and as we look at the results we might say, well, what are these numbers going to be if it's 15-year loan? Let's put in the number 180 in cell A1, and as we put in the 180 and press Enter, we've got a new set of numbers. And so each of these cells here is reacting to the cells out of row 3 for the percent change, the cells out of the column B for the amount being borrowed.
And so, we can change these results rapidly. And furthermore, since we earlier had set up some formulas here, now I did this ahead of time, and this isn't always a requirement, and isn't always a characteristic of a Data Table. But if we want to show these numbers starting at 2.5%, I'm going to type in 2.5%. I've already got formulas in place there that take into account that we want each of these to be an eight of a percent higher. So now we range from 2.5% all the way up to 4%, and we could do the same thing down the left hand side, each of these cells has formulas.
And again, this is not necessarily the characteristic of a Data Table. You might want to set this up yourself this way. So what we can also do here to make this look more interesting is select these cells, and then as we do the quick analysis in the lower right hand corner invites us to make some changes. Click it. How about Data Bars, how would they look here? Maybe not so good, how about Color Scales, well that could be interesting. How about Icon Sets? Maybe that could be interesting as well. It divides the data into thirds; we may or may not want to use that.
If we do make this choice, we will have to make the columns wider, we could do that. So I'm able to decide on Color Scale. And if you are familiar with Conditional Formatting you might want to jump over to the HOME tab and then go to Conditional Formatting, and get a preview as to how other Color Scale options might appear. Maybe we'll choose this one. But even though we could have done this with formulas and in a different way, the Data Table concept here facilitates the idea that we created a Data Table. We can make changes to row 3 here to change the percents, or the column B entries to change the amount, or go back to cell A1, maybe change that to 360 that way it was earlier.
I've got flexibility here by way of Data Table, found on the Data tab, in the What-If Analysis group, Data Table.
|Section 15: Introduction to Macros|
When you find yourself repeating certain command sequences in Excel, sometimes the thought is, here I go again, same thing all over. Do I have to go through those same six steps, those same eight steps? Ultimately the answer is no. There is a feature in Excel called a Macro. A Macro is simply the ability to take a single step that encompasses many, many steps. Let's start with just a small idea. You're the manager of an HR group, and what you do occasionally is you review this data like what we're seeing here.
And when you flag a cell because you think it's incorrect or needs adjustment, what you do, and admittedly this might be overkill. You go to the Home tab; you provide a color background, let's say yellow here. You might make the font red; make it bold and going overboard a bit, maybe even going into Borders, Thick Box Border. It's just to illustrate the idea that we've taken four or five actions here and we like that look. And we like to use it elsewhere too. Now if it's nearby, we could copy the format, that might not take too long, but we might be a few hundred rows away at one time or another.
Wouldn't it be great if we could simply hit a keystroke shortcut, or how about a button up in the Quick Access toolbar at the top of the screen that would achieve that same objective. Well, we could create what's called a Macro to achieve that effect. Let's take some other just small examples at least at first. When someone leaves this organization, what you usually do is not delete the record, because you want to hold onto it for while but maybe you do like to indicate that someone has left simply by applying strikethrough. And you can get to strikethrough a number of ways, but invariably what it means is after selecting the data, you can go to the Font tab and click the dialog box launcher, or you might press Ctrl+1.
In either case, you'll end at Format Cells, probably on the Font tab and there's a choice called Strikethrough, you click OK, there it is. Now a bit later, you might discover there's actually keystroke shortcut for that, its Ctrl+5. Now we don't call that a Macro, but in a certain sense it is. It's a single action. And in this case, it applies strikethrough or removes it. In a certain sense we can also make the case for saying that nearly every button in the ribbon menu system is like a little macro.
If you didn't know how to make a cell bold, you might have to go, by way of the dialog box launcher or by Right-Clicking on the cell possibly. Going to Format Cells, Font tab if it's not already selected and there's Bold. Probably almost nobody does this, most people learn on day one of Excel, there's a button up there, B, that too is like a macro, we just don't call thosemacros but they represent the concept of a single action that the takes the place of many actions.
And let's think a little bit larger here, what if this list of some 700 rows or so is within a dynamic organization and there's a lot of growth here, a lot of change, people come and go. What if it's up to you, two or three times a week to provide updated lists for a number of people? And one of the things you might do is sort this by department and then, print three copies. And then maybe you sort it by employee name and print a few more copies. And another thing you do in that same sequence is apply a filter to show only the hourly people, and you print that list and send it off to the interested people.
And you might imagine a few other sequences like that. Maybe sorted in different way and printed, or maybe you apply subtotals and collapse that list and copy it and paste it; and you can imagine any number of different things that you might be doing with a list like this. Now you probably didn't really time how long that would have taken, but maybe it takes you ten or twelve minutes and you do that two or three times a week. If you remember the steps or if you've written them down, if it's pretty clear to you what those steps are, you could then activate or turn on what's called a macro recorder.
And in the process go through all the steps that you've gone through manually. And thereafter, since the macro will be saved, you can simply start the process by clicking a keystroke shortcut and maybe that ten or 15-minute process takes a half a minute. Printing might take a little bit of time but otherwise it's extremely fast. And the more you know about the macro concept, the more you're tuned into the idea that automating certain aspects of repetitious work is going to be helpful to you. Here's another example.
There's a PricingSheet in this workbook. It would be handy if we knew where the formulas are, and there are couple shortcuts for that already. If you click on a single cell anywhere in this worksheet, on the Home tab you could go to the Find & Select button on the right and choose Formulas, and all the formula cells are highlighted. Now if you always want to apply color when you do that will involve a few more steps, maybe you'll click this drop-arrow here and you got a particular color you like use there, fine.
That's only a few steps, but that too could be automated. Imagine in any worksheet at any time if you said, I'd like to know which cells have formulas. Hit a keystroke shortcut and suddenly we'd see these cells highlighted. Now admittedly, the steps here aren't that many and you got to go to the Home tab, you got to go to Find & Select, you've got to select Formulas, and then you got click here and so on. We're not really counting the steps, but it's interesting how we do find even four and five step sequences sometimes just a little annoying.
A keystroke shortcut would be great here. A companion to this would take substantially longer.Suppose you say, I'd like to highlight just the cells that have pure numbers, not the ones with formulas, but the one with numbers. Process here as you might guess starts in that same location, Find & Select and then, this time Go To Special, not quite so obvious. And here, we can choose Constants, but then we would want to uncheck the box for Text, Logical, Errors, and click OK.
And here too, maybe apply a different color. Now, in no way am I saying this is a shortcut that everybody needs. In other words you have to identify what repetitious actions that you usefrequently are the ones that you found annoying or the ones that you'd really like to speed up. I think for some people, these would be really helpful. If you're a worksheet troubleshooting kind of person, if you find yourself often doing that sort of thing, these steps here if turned into macros could really save you a lot of time. So the idea of a macro will vary widely depending upon whose thinking about what those shortcuts are, what potential shortcuts are really needed.
There's so many different ways to use Excel, but the idea of setting up a process to record our actions, so that we can then get to them quickly in the future is called a macro and it's an incredibly powerful tool for Excel users.
After you have identified a certain sequence of actions that you would like to automate in the form of a Macro, it's time to learn how to actually record a Macro. The process has begun already because you've either written down or you know those steps you want to take. For example, we've decided that it's really handy to be able to click on a cell here and when it's questionable, we want to apply a special format. A format that might include using some of the features on the HOME tab like a background of yellow, a red font and a few other features as well, we don't want to go through those steps manually each time.
So, the process of recording a macro also begins with this thought. Is the macro we are about to record, designed only for this workbook or might we want to use it elsewhere? Let's imagine for the moment that we're only going to use this macro in the current workbook. A bit later we might change our minds about that. It is something we need to think about though because certain macros could be used in any workbook. Let's start by going to the VIEW tab in the ribbon. The right most button, Macros has a drop arrow, let's click it, and we see the choice Record Macro.
And here's a dialog box. We need to give our Macro a name. Ideally it should be a meaningful name, the name cannot begin with a number, it cannot contain spaces, some special characters work some don't. It's best to stay away from them. The naming of this macro ideally would be something like Apply Format or Highlight Cells, something like that. How about Highlight Cells, that's an easy one. Now I cannot put a space in but I can use either underscore or maybe I'll simply switch case, either way. So underscore is okay, that's fine, Highlight_Cells.
We can give it a longer name if we wanted too. Macros often have keystroke shortcuts and that's a favorite way, particularly when you're just getting started with this feature. Long term, sometimes you say, well I've got so many macros, I forget which one is which. I don't remember the keystroke shortcut and so on. So at times you might grow weary of this concept. But what can we choose for a shortcut key? We have only 52 choices, any of the lowercase letters, any of the uppercase letters. Now if I think C here for cells sounds like a good idea maybe, but a lot of you now that Ctrl+C, one of the most widely used keystrokes of all in Excel is for Copy.
If we use Ctrl+C for this Macro, we will no longer be able to use Ctrl+C for Copy, so that isn't a good choice. Do you use Ctrl+H, that means Replace, well you might, but if you don't, well you could possibly use that one. There might be a tendency here to use uppercase letters. And all I need to do is hold down the Shift key. So I'm about to use Ctrl+Shift+H. I'm holding down Shift key right now, pressing the letter H. The word Shift pops in automatically. Now earlier, I'd mentioned this idea of using the macro in this workbook only.
If that's the choice we will click the arrow here and choose This Workbook. If you do want a Macro to be available for all workbooks at all times, you store it in a location called Personal Macro Workbook and we'll get to that in a bit. This time we're saying the macro we're writing, at least for awhile, it maybe always will be stored in this workbook. Now there's a description panel here and for small macros, typically we don't say anything, but longer macros, it's really helpful to provide a description.
And you will find it useful yourself when you look a Macro that you wrote last year or a while ago, and certainly others if they happen to be using this macro will want to know what the Macro does. Sometimes the name doesn't do it enough justice. So you can certainly provide a description here. So in this case, the macro is short enough. The macro name itself does a pretty good job of describing it. We're about to click Okay, but before we do, let's talk about what is about to happen. When we click OK, we will be in a mode of recording a series of actions.
And so the idea is we want to stay focused. We should know exactly, what it is we want to do, perhaps we've written down the steps. In a lower left corner of the screen, in the status bar, we see the word READY, but we're about to see something else there as we click OK. Now in the lower left corner, there's a little box next to the word READY. If we slide over it, the pop-up reads A macro is currently recording, click to stop recording. And we can certainly stop the process right there. We can also go back by way of the VIEW tab in the ribbon to the Macros arrow and choose Stop Recording up there as well, but we're in recording mode right now.
So if I'm scrolling up and down here, that's going to be in the macro. It's practically meaningless, we won't see it, but the idea is we don't want to be taking actions here that will be in the macro. Behind-the-scenes, what we don't really need to get into is the fact that everyaction we take is actually translated into the programming language VBA, Visual Basic for Applications, and learning about that feature takes a good deal of time and requires a different course. In this course, all we need to focus on is the idea that when you turn on the macro recorder, the steps that you are recording can be used later instantly with a keystroke shortcut.
And ultimately maybe even a new button in the Quick Access toolbar. So we're in this recording mode right now, let's apply those features that we want to have happen every time we use this macro in the future. So we go to the HOME tab and we've used this recently, so the yellow is still there, we want to use the fill color yellow, fine. We want to use red font, that's good; we want to make it bold, it's good and let's say we apply the Border feature as well here, the Thick Box Border. Remember we want that to happen every time we run this macro in the future.
And those are all the steps that we need. The question might have entered your mind; will this work if we highlight multiple cells? Well, it will. You don't necessarily know that ahead of time, but it will, and we can use this on one cell or different cells even. So we've essentially recorded all there is to record, and we're done. So we can end this by either going to the box in the lower left corner, that's certainly straightforward. Less straightforward but just to show that it's there, VIEW tab, the arrow for Macros, Stop Recording either way.
So we've recorded this. Let's go to a different location, let's try this Ctrl+Shift+H, it works. Can we try it on a few cells? Again, recognizing the reason we set this up is because we recognize that some of these cells need to be looked into. I think both of those are incorrect. Maybe Ctrl+Shift+H, looks like the feature works on multiple cells as well. So I've seen an example here of how recording a series of actions can easily be executed by pressing that keystroke shortcut.
And you will hear the term Playback, Execute, Run; they all mean the same thing. The idea of making the macro, do what it needs to do with just a single action.
When you want to use a Macro, you will hear the term Playback the Macro, Execute it, Run it, Activate it. The whole idea is you've created a macro and you want to make it work; make it do what it's supposed to do. And when you're getting started with Macros, the preferred method tends to be a keystroke shortcut, but there are other ways as well. Let's create a new macro here and also consider the idea that sometimes the macro you want to create and then Run is stored in such a way that it will always be available regardless of which workbook is open.
In this worksheet called PricingSheet, there are a lot of cells with formulas, a lot of cells with just pure numbers. And let's imagine that we want to highlight just the cells with numbers. So let's create a macro here, VIEW tab, drop-arrow or for Macros, choose Record Macro. And we're going to call it something along the lines of HighlightValueCells. Macro names cannot have spaces, this time it will just use upper and lower case value cells.
Keystroke shortcut, V is the key letter perhaps, we're thinking of value, how about Ctrl+Shift, I'm holding down the Shift key as I type the letter V. If we want our macro to be available in any workbook, in other words if we create this macro and then close the current workbook, we still want to be able to use this macro. So we don't want to store it in this workbook. The other term that might surprise you, Personal Macro Workbook. If it were called Global Macro Workbook, perhaps it might be a little more pertinent in terms of what it means.
If you store a macro in this location, it means that in the future, regardless of which workbook is open, you will be able to use Run, Activate, Execute, whatever the term you're using this particular macro. Personal Macro Workbook might not even exist right now. Ultimately simply by clicking OK and recording this macro, you are in effect creating this if you haven't already created it. And the name of that will be Personal.XLSB, its complete name of file extension.
That's perhaps a little bit of trivia but at the same time; I want to emphasize the idea that the Personal Macro Workbook is a separate workbook. And once you create it and we can simply do as we're about to do it here by a recording a macro and placing it there, this workbook is always available in the future regardless of which file is open. It is stored on the current computer you're using. And so what happens sometimes is you will want to copy certain macros elsewhere, but we simply want to record this macro so that it's available to all workbooks.
And all we want to do in this macro is to highlight the cells that have values, so we'll click OK.And the process begins for this command sequence; go to the HOME tab in the ribbon and the extreme right button, Find & Select. This is a feature that many people might use, not everybody, so we always want to be reminded that macros although we might think of them as being ideal for us, aren't necessarily for everybody. Go To Special, choose the Constants button and if we're only concerned with numbers here or values, let's uncheck the box for Text, and Logical, and Errors.
And as we click OK, we see the cells that are highlighted. If we want to make sure that they stay highlighted by a way of a color, then we'll go to the HOME Tab, the Fill Color Font, the arrow to the right and choose the color that we think will work best in this particular example.And we like the light green, maybe a little bit lighter, something like that. Recognize now that all these cells are selected and they have color, a slight difference and is not critical. If I click in cell A1, it will no longer be highlighted, but the color will be there.
So that's all we want our macro to do. Every time we execute this macro, we want to select all the cells that have numbers and apply a light green background, and then go to cell A1, although we could go to any cell. So we finished recording the macro. We can stop by pointing to the box just to the left to the word READY in the lower left corner of the screen in the status bar, Stop Recording. Let's try this in a different worksheet. We got the active cell anywhere here; we want to highlight those value cells and the keystroke shortcut I used, Ctrl+Shift+V. It works great.
What happens if we start to record a few more macros and a few more and a few more? We probably run out of meaningful keystroke shortcuts, if the letters were that meaningful, but we might want to be able to get to our macros in a different way. So I'll also confront this idea.What if we didn't want the macro to run? We ran it by mistake, maybe I did that here. Can we undo a macro? No we can't.Now the Undo arrow might look active, and you might click the drop arrow here, and if you've just recorded the action here, you might say, oh, I'll just undo it.Well that takes us back to here.
And we can't really undo what happened here. What we did here ultimately was just to apply color, so we certainly manually could do that. But the idea that you can't undo a macro is a critical thought. What if your macro deleted data? And I would strongly suggest that at least for a while, you don't write macros that delete data. You can't just casually say, well I'll come back and undo it. What you might need to do is close the file and not save it. What if you've done a lot of other good things in the meantime? Well you're going to loose all those too. So you want to be really careful with the idea that you can't undo what a macro does.
In other words, you can't reverse the steps Now in this case, lets imagine that I don't want the color there, we'll just get rid of the color. So I'll highlight the cells and easily by way of the HOME tab, go to the Fill Color bucket, choose No Fill. Now after doing some other things and coming back here, now I do want to run my macro. The one that highlights the number cells, maybe I forgot the keystroke shortcuts. So what do we do, if we forget a keystroke shortcut?Go to the VIEW tab, choose the drop arrow for Macros, go to View Macros.
I have only one macro here; there it is. I'll click it and we have the option to the right Run. It certainly isn't fast, but it's our fallback method. If it's one of those longer macros that manipulates a lot of data and takes two or three minutes, well we've saved a good deal of time, no question about it. Here's another thought. What if we want to change the keystroke shortcut? Once again go back to the same location on the VIEW tab click the drop arrow forMacros, View macros, here's the macro we're working with. We go to Options and change the keystroke shortcut.
And this is also where we go, if we didn't initially have a keystroke shortcut, we can assign it now, or if we want to delete the keystroke shortcut. Maybe we want to create another macro that's going to use this shortcut key. And so we see, we can easily change the keystroke shortcut or add it or delete it. In this case, we don't want to do any of those things. For certain macros, perhaps like this one that we think we might want to use often, we can also get to this in a different way. We can add a button to the Quick Access toolbar.
This is the set of buttons typically above the ribbon in the upper left-hand corner of your screen. It possibly is below the ribbon. No matter, where it is, if you Right-Click it, you can then choose Customize Quick Access Toolbar. And then in this dialog box called Excel Options, Choose commands from, click the drop arrow and choose Macros. Now you might or might not be seeing what I'm seeing here on the screen. Those are system type macros, but somewhere in here, you should see if you have created a macro, the name of the macro that you created.
There's the one I just created, let's add this to the Quick Access Toolbar. Maybe you're a little picky and you say, I don't like that icon. Well, you can come down here and modify, you've got 181 choices and I don't think anyone of them suggest exactly what we're trying to do here but I'll just choose a green box. There we go, OK. And as I click OK, look in the upper left corner of the screen, we now have that icon. We might leave it here for a long, long time, maybe forever if we use this often. It's going to be there no matter which workbook is open, provided the next exit from Excel is a normal one, so it will be there all the time.
And whether this workbook is open, it won't make any difference whatsoever. So we might go over to this worksheet right here and click this button, it highlights the cells that have numbers.It works easily and dates by the way are considered values, that's why they're highlighted here.Once again, can I undo? We'll not really to do. I can certainly remove the colors in other ways here; I can highlight all this data here, I'll go across here and get rid of the colors that way. It's like I also got rid of the colors up here, we won't worry about that.
But again, it brings back the idea that you can't really undo what a macro has done. You can certainly take manual steps to undo the effect. Now there's another possibility here for running macros. Even though this macro that we just saw is ideally designed to work in this workbook, that workbook, there could be situations where you want a macro to run right here by way of a button. And so we could create a button and there are any number of different ways to do this.One way can be, we can go to the INSERT tab and go to the Shapes icon for example and I'll just pick one of these at random, how about a rounded rectangle and I'm just going to draw this on the screen here; and type in Highlight Number Cells.
It doesn't have to match the wordings exactly of the macro, so Highlight Number Cells. And do all the formatting things that you might want to do with this. You can make it bold, bigger, all that sort of thing, not too critical there, but just to show we can do that too. But the key step next would be to Right-Click here and Assign Macro. So we're going to assign a macro to this button. For the moment we have only one, there it is right there. We'll click OK. Now we're still in edit mode, we might want to shrink this, do other things with it, whatever.
As we click away from it, in the future, anytime we slide the mouse over it, we see the pointer finger. This will activate the macro and what's it going to do here? It highlights the value cells and makes them green. And so we see one more way to activate a macro. We can also do this with icons and pictures too; this is just a simple example with a shape. So the way we make a macro work, and again the terms Playback, Run, Execute are all used synonymously is simply to use a keystroke shortcut, probably the most common way when you're getting started.
But ultimately by way of a button in the Quick Access Toolbar as we saw, and most recently by way of an icon or a shape on the worksheet.
|Section 16: Exercise Files|
You can download Exercise Files at the library. Open the folder Exercise Files and then, for example, if you are viewing Chapter 5, simply open the folder and then the file, and you'll see various worksheets within each file and usually there's a separate worksheet for each movie within the file.
I'm an Excel developer and trainer. I also run a consultancy business specializing in custom Excel apps and data analytics. Over the years, I've been asked to run so many training sessions on Excel that I finally decided to put my content online. My focus is on helping people get the most out of Excel with the minimum effort, pain, and the least boredom.
Let's face it, using Excel isn't always the highlight of everyone's day. For many it's a constant pain because they have to go to ask the office guru every time they need to do a VLookup, or a Pivot Table. Perhaps they don't even know what a Pivot Table is! My courses are all about helping you to get the most out of Excel, so that you can be faster and more effective with your spreadsheets, without boring you with technical manuals or long form lessons.
There is so much opportunity to improve efficiency, automate tasks, get better data insights, reduce errors, and create happier staff, simply by improving the level of competence in Excel across organizations. I have seen countless Excel classes that focus just on what the various buttons in Excel do and how to use them. I never felt this was enough. Excel users need to know WHEN and WHY they should use these tools, not just HOW. There's also huge scope for to coach Excel users away from common pitfalls and bad practice. I created these courses to provide more comprehensive, training for Excel users of all levels from beginners to seasoned experts.