Transcripts
1. Trailer: Hi. My name is Al Chen. Welcome to my Skillshare series, Intro to Excel. Now, I used to think using Excel was meant for data nerds and people that didn't have social skills and after I started working, I realized Excel is something I will use every single day. In class two, we're going to focus on only keyboard shortcuts. So, this is using keystrokes to be faster and more productive, and also just to navigate the interface without using the mouse which is subject to human error and makes you a lot slower versus using keyboard shortcuts. Excel is a useful tool no matter what line of business you're in, whether you're working for a advertising company, a nonprofit. You'll come across using a spreadsheet at one point or another. So, please take this series and be better and faster in Excel.
2. Getting Started with Shortcuts: Welcome to unit one, Getting Started with Shortcuts. In this unit, we will look at how to set up your Excel environment to best utilize keyboard shortcuts on the Mac. This lesson, this whole class is actually just about keyboard shortcuts and before we get into the actual shortcuts, I'm just going to have a quick lesson on getting started with using keyboard shortcuts and how they can be really useful for you using Excel. Now, if you just back up for a second, when you're using Excel, the best method to use it as actually using the keyboard versus the mouse, where using the mouse, it's subject to human error, you might click on things you don't mean to click on, and it's just not as fast as using the keyboard. We're just going to do a few basic operations in Excel with the mouse and then with the keyboard, and show you the difference between the two and how powerful keyboard shortcuts can be. So, if we look at the first step, we're going to just do a few basic number formatting operations. I want you to take the number in L4 and just apply a quick percentage or comma format to that number. So, you can easily do that by just going here on the ribbon and clicking on the comma or the percentage, and now I want you to do a bold or underline and that's also really simple by just going here and pressing the B or the U. Now, let's say I wanted you to deal with all of that using just the keyboard. Now, the reason why the keyboard is so much faster is that you don't have to move your mouse all over the ribbon and all through the charts and the menus and things like that to make the operation work. So, if we wanted to add the number format, we're going to do the shortcut in a future lesson, but it's just control, shift, and 1 or control, shift, and 4 for the number, for the currency format; and bolding and underlining things, you probably have them before in the past, which is just command B and command U. I'm doing all this without touching the mouse at all, I'm just using the keyboard. You can see that it's a lot faster than having to move your mouse around and going to different parts of the ribbon like I showed you earlier. So, just to demonstrate how powerful keyboard shortcuts are in terms of analyzing data and doing data analysis, a lot of new interviewees at banks and financial institutions they actually will ask you how good you are at Excel using keyboard shortcuts, and I've been in situations where a manager will ask me to do something in Excel and he'll unplug the mouse or tell me I can't use a trackpad and tell me I have to do everything in Excel building the model, doing some formatting using only a keyboard shortcuts just to see how disciplined I was with using keyboard shortcuts. So, we're going to first set up our environment in Excel so that it's best used for shortcuts. There's a few kind of small settings that you should change to make sure that you can use the shortcuts to the best advantage in Excel. So, the first thing you want to do is go to the System Preferences for Mac, if you're using a Mac, and this is by clicking the Apple icon in the top left, go to System Preferences, and then go to keyboard, which is in this section right here, and then you're going to see a bunch of things relating to your keyboard setup. Now, the one thing you want to click on is shortcuts. So here, you're going to see all the shortcuts related to using things in Mac OS. Now, the one setting that you want to be aware of is the all controls radio button at the bottom. For full keyboard access, so you just want to click on the All controls radio button, and that allows you to use the tab key to move around different menus when you're in Excel. The tab key is one of the most powerful keys in Excel because it lets you to move from menu to menu, from selection box to radio button a lot faster than using the mouse. So, just make sure that in your System Preferences, you click on keyboard, shortcuts, and then the All controls radio button, and then just exit out of the System Preferences. The next step is to go to tools and then customize keyboard, and this is actually in Excel itself not the Mac System Preferences. So go to Tools and then Customize Keyboard at the very bottom there. Here is where you'll see pretty much every single excel operation that you can do along with the keyboard shortcuts for doing that operation. So, if you're looking for instance the format meant for that category and the Increase Font Size shortcut, you'll see that the command + shift plus the period sign is the keyboard shortcut. So, if you find yourself doing something in Excel a lot, let's say you're doing Tools and Language operation a lot in Excel, you can actually assign your own keyboard shortcut to that operation so that you can access that operation without using the mouse. So, this is actually one feature that's available only on the Excel on the Mac and not on the PC. So, definitely utilize this feature of Excel on Mac to further customize the keyboard shortcuts that you use in Excel. Finally, there are tools that are available to help you learn the shortcuts. Obviously, if you don't know how to do something in Excel in terms of keyboard shortcuts, you can look it up on Google for what the shortcut is. There's also a lot of help forums and kind of cheat sheets that you can download. One product that we developed is called from KeyCuts is this silicone rubber keyboard cover, and it's a very thin kind of keyboard cover that goes on top of MacBook Airs and Pros as well as the retina displays on 13-inch to 15-inch models. It goes right on top of your keyboard and actually shows you the 20 most commonly use keyboard shortcuts for formatting and data manipulation. So, for instance, if you want to see the formula here, it's just the control key plus the letter U. So, we're going to walk through a lot of these keyboard shortcuts in this class, but just keep in mind that there are other tools that you can use to help you learn shortcuts. If you go to www.thekeycuts.com, you'll see our keyboard cover product in case that's something you're interested in using to help you learn the shortcuts. So, that's lesson one for Getting Started with the Shortcuts, and we'll move on to the actual shortcuts themselves.
3. Moving Around: Welcome to unit two, Moving Around. In this unit, before we talk about formulas, we're going to learn how to move around faster and more efficiently within Excel. In class one, we talked about kind of selecting things and the workbook structure and how you use worksheets in a workbook. But to move around in itself in a fast and efficient way, will help you build your models faster and just help you move around the worksheet in a more methodical way. So, we're going to talk about just moving around and getting into a worksheet using keyboard shortcuts. So the first keyboard shortcut we're going to talk about is to see the formula in a cell. So this class is meant for Excel on a Mac, but I've also included the Windows shortcuts just in case you are using a Windows PC or laptop to utilize these shortcuts as well. But we're going to focus on just the Mac shortcuts in this class. So, if you'll see here in columns E and F, you have numbers 3 and 3 that are hard-coded. In cell G3, we have a sum of these two numbers. As you can see if I double-click, it's just a sum of E3 plus F3. If I click on the formula bar, it's also the same thing, the sum of those two numbers. But again, we want to use just the keyboard and not the mouse to double-click or clicking on the formula bar. So, I'm going to press the Ctrl key with my left hand and the letter U with my right, and you'll see that you can access the formula that way and to get out of edit formula mode, you can just press the Escape key. So again, Ctrl+U and then Escape to see the formula, then once you're in the formula edit mode, you can edit whatever you want. But then once you're done either press Enter or you press Escape. The next shortcut is moving backwards and forwards through worksheets. So, the keyboard shortcut for here is Function plus Command with your left hand, and then an Up arrow and Down arrow with your right hand. So this allows you to move from worksheet to worksheet in a much faster way than using a mouse like I'm doing right now with the cursor. So again, we're going to hold down Function and Command with your left hand, and then down arrow and Up arrow with your right hand. You can see I can cycle between all different worksheets in my workbook in a faster way than using the mouse. Another option is actually to hold down the Option key and left and right, but I prefer to use Function command because it's more consistent with other shortcuts we're going to be learning in this lesson. Now, the next shortcut is to go to the topmost to the left of a worksheet. So it's kind of similar to the last one, we're holding down Function and Command with our left hand. But now we're going to press the left arrow with your right index finger, and you'll automatically go to cell A1 of whatever worksheet you're in. So, let's say sometimes you're all the way in cell, let's say, we're in cell right now a 1,048,000, and instead of scrolling all the way to get back to cell A1, you can just press Function command with your left hand, and then left arrow key with your right hand and you back in cell A1, which is where you're doing most of your formula building and charts and dashboards and so on and so forth. It's a useful shortcut, just to get back into the first cell. The next shortcut is to go up and down a worksheet fast. Now this is probably something you're used to using, whether you're using an Internet browser you're going up and down a page, it's really simple you just hold down the Function key with your left hand and then the Up and Down Arrow keys with your right hand, and you basically skip one page of data and rows at a time. So that's a quick shortcut to quickly view a lot of data if you have a long data set and you want to quickly go through pages and pages of rows. Finally the last shortcut is moving to the last value in a row or column. This shortcut basically lets you skip over all the empty cells in your dataset. It's a really simple short cut to use but a really powerful one if you start to use it more regularly. All you need to do is hold down the Command key with your left hand and then all the arrow keys with your right hand. So, for instance, if you look in cell E8, through J11, there are all these A's scattered around the worksheet. You can obviously use your arrow keys to move from cell to cell, but let's say you wanted to skip over all the empty cells. So, if you hold down the Command key with your left hand and that's you're pressing the arrow keys, you actually skip over all the empty cells until you get to the next cell with a value. So, this is a quick way to navigate a huge dataset if you have a bunch of empty spaces or there's a big gap between your data, you can just hold the Command key plus the arrow keys and I'll let you skip around the different parts of your worksheet. So this lesson, again, was all about just moving around efficiently in Excel and moving between worksheets really fast. So, once you've mastered this, we can move on to actually using keyboard shortcuts to filter and sort data and edit formulas.
4. Selecting Stuff: Welcome to Unit Three, Selecting Stuff. This unit is all about selecting data using keyboard shortcuts so you never have to touch a mouse ever again when you're selecting data. The first lesson we talked about moving around and navigating Excel in a more efficient way and this section, this lesson, is all about selecting data in a more faster and automated fashion. So, let's go on to the first shortcut. We're going to first learn how to select a whole column or row in Excel using only the keyboard. So, the Windows and Macs shortcuts are the same thing here and it's essentially Control plus the Spacebar or Shift plus the Spacebar. So, if you're trying to select a whole column or row in Excel, you normally hover over the top letter in Excel and you'll click on the column to select that whole column or row, right? Same here with selecting the rows. Now, instead of doing that, to select the whole column you press Control with your left hand and Spacebar with your right hand and you'll select the entire column in one go. Conversely, pressing Shift and Spacebar, you'll select the entire row in one swift keyboard shortcut. Pretty useful shortcut for selecting data. Now, the next shortcut is kind of a mouthful is while you're in a cell, selecting all adjacent cells to the last cell with value. Now that sounds really complicated but am I show you what it means in Excel will be a lot more easy to understand. So, the shortcut involves holding down the Command key and the Shift key with your left middle finger and your left index finger, and then using the Arrow keys to select data that you want. So, we have all these a's here right that we want to select in a fast way. So, if I go to the very first cell in that range which is G5, I'm going to hold down Command and Shift with my left hand and now I'm going to press the right Arrow key once, and you'll notice that it quickly selects all the columns to the right in that range but it stops at column J. If I press the down Arrow once while still holding down the Command and the Shift key, it now selects all the rows below up until the last cell with the value. So, if you just kind of hold down Command and Shift and just press the Arrow keys right now, like over and over again, you'll notice that it selects all the columns and rows over to the right and to the bottom, left, down, top, wherever that you're pressing on the Arrow keys. So, this is definitely a useful shortcut to get used to. It's going to take some while to understand because you're going to be moving all over the place like this, but once understand how to use the Shift and Command shortcut, it's going to save you so much time with selecting data. Now, one caveat is if you let go of the Command key, I'm just going to hold down Shift and Command left to go to the Command key with my left hand and now I press the Arrow keys, you'll notice that it selects one column and one row at a time by pressing the Arrow keys. So, that's another thing to keep in mind is when you hold down the Command key, it tells Excel I want to select everything up until that one cell with the value, but if you let go of the Command key and just hold Shift, Shift does the single row and single column selection when you press the Arrow keys. So, right now I'm just pressing Shift with my left hand and then the Arrow keys with my right hand to select various cells in that range. The next shortcut is grouping and ungrouping rows or columns after you've selected the rows or columns. So, grouping is basically allows you to collapse or a group columns together so that you can hide them from you're maybe it's you're trying to present the data to a client or presenting the data to upper management and you don't want them to see what some of the data that you've entered into the worksheet. So, the first thing we're going to do is select all the columns we want to group. In this case, we're going to select all of J, H and I. I'm going to do this by pressing first Control Spacebar we learned that to select the whole column and then I'm going to hold the Shift key with my left hand and the right Arrow key with my right hand and press right twice. Notice that I selected all three columns with just the keyboard. And now I'm going to press Command, Shift, and the letter K to group these columns. And what you'll see is that it creates this little minus symbol at the top of Excel and when you click on this minus symbol, it collapses the groups columns and now it goes from column F to column J. But if you press that plus sign again, it just expands those grouped columns. Now, to quickly ungroup and group you just have to hold down Command and Shift like I showed you here and just press J and K. So, I'm just cycling between grouping and ungrouping these three columns by pressing Command, Shift, J, and K. Again, this applies to grouping and ungrouping rows as well. The next shortcut is deleting a selected row or column. So, let's say I want to delete all of column H, useless data, I don't want it. So, I want to delete this from my dataset. I'm going to press Control plus Spacebar and column H. Now, I have all of column H selected. Then, I'm going to press Control plus the minus sign. So, Control with my left hand and the minus sign with my right hand, it automatically deletes the entire column from your worksheet. I'm just going to do undo to get my data back but just think of minus as subtraction or deletion. You're just deleting things from your worksheet or workbook. The opposite of delete is obviously inserting a new column or row. Sometimes you want to insert new columns in between some data that you have. So, the first thing you want to do is select the column that you want to insert data before. So, I want to insert a new column in between column G and H as you see here. So, I'm going to select column H. I'm going to press Control with my left hand, the letter I with my right hand, and there you go, you'll insert a completely blank new column that you can start playing with and adding more data to. This happens a lot sometimes if you're building a dashboard and your audience wants to see the data broken out by months instead of years. So, in that case, you will have to go back in your data set and insert a bunch of empty columns into your worksheet and you can quickly just press Control I a bunch of times and you'll insert new columns into the worksheet to play around within Excel. So, that wraps up Lesson Three in terms of selecting things efficiently in Excel. Learning these shortcuts will definitely make your lives a lot faster because when you're moving data around and manipulating data, you need to know how to select the data fast so that you can then manipulate it, and filter it, and sort it, so on and so forth.
5. Autofilters & Lists: Welcome to unit four, auto filters and lists. In this unit we're using keyboard shortcuts to quickly filter and sort our data using the spacebar and arrow keys and never having to touch the mouse. So we've talked about moving around fast in Excel, we've talked about selecting things fast in Excel. Now let's actually get into the nitty-gritty of filtering a list, sorting a list of data. All these things that you're typically going to do when you're analyzing a bunch of data in Excel. So the first thing we're going to do is create an Auto Filter in Excel. This is after we move the cursor to the first row in your dataset. So we had this list here which you've seen which has a list of characters; Will Smith, Ashley Banks, Philip Banks and the shows that they appear in as well as the rating for those shows. So, in order to filter this list of data, you've probably have done, click on Data and then click on the Filter icon here. A much faster way to do this is just by pressing Shift Command F in the Mac. So Shift Command with your left hand and then F with your right hand and there you go. You have the drop down arrows that you can use to filter your list by whatever value it is that you want to filter on. Now, in order to actually access these drop-down arrow menus, the filter menu, you can press the option plus the down arrow key keyboard shortcut to actually access the menus. So let's say I want to filter on just the show saved by the bell. I'm going to press. I'm first going to go through G three which contains the field I want. I'm going to press option with my left hand, down arrow with my right hand and there you go, brings up the filter menu that you've seen just by clicking on the drop down arrow. Now, how do you actually access all the different items within this filter menu by using the keyboard? So this is where we use the Tab key and that setting that we changed earlier in the system preferences in Mac, that allows you to use the Tab key here in this menu. So if I press the Tab key am pressing the tab key now with my left hand and notice that the blue outline kind of shifts between a few different boxes. So I want again filter on only the show saved by the bell here. So the first thing we'll do is I'm going to wait till my blue highlight goes to the choose one box right there. I'm going to press the space bar now with my left hand and now I want this to be equals because I want my show to equals saved by the bell. Now I'm going to press enter and now the blue outline automatically moved to this field which is allowing me to kind of enter in what my filter will be. Hit spacebar here or down arrow, and then now I can actually access all the different individual unique shows that were in my original list. So again I want to only filter on the show saved by the bell. So I'm going to scroll down with my arrow keys, saved say by the bell. Hit enter and your lists now will be filtered by just that field you wanted. So in this case Saved By The Bell and now to get out of this filter menu, I'm just going to press escape with my left hand and there you go. Where we can now see all the filtered data by using just the keyboard shortcuts. So again the key thing here is to always use the Tab key to get around your Filter Menu and then using the spacebar Key and a down arrow keys to actually access different kinds of selections within the filter menu. So again equals and then I'm going to press the drop down arrow and I'm going to go to saved by the bell, Enter and then escape to get out of it and now I have my sorted list. To get out of the filter menu, if you want to quickly, let's say you filtered your menu, you've filtered or listed a bunch of things and you want to quickly get out of the filtered state, you just press Shift Command F again. So Shift Command with my left hand and then F with my right hand and you'll quickly see your data's un-filter now and just allows you to clear out the data as if it were fresh. The next shortcut is sorting a list by ascending or descending order. So unfortunately there's actually no shortcut for the Mac. There is one for the Windows PC version of Excel, but for the Mac you'll actually have to assign a special shortcut which you can do through tools, customize keyboard and you can find the menu item for ascending and descending order and assign your own custom keyboard shortcut for using the operation. But Excel doesn't come with a built-in kind of keyboard shortcut for sorting a list by ascending or descending order. So that's why there is a frowny face in that cell. The last shortcut in this section is going to be bring up the advanced sort menu. So you know how to do advanced so you can sort things by alphabetical, ascending descending order. But we want to do this in a more automated way by using keyboard shortcuts. So let's go to our data again. We're going to go to the first row and the keyboard shortcut here is shift plus command with your left hand and then the letter R with your right hand. It'll bring up this advanced sort menu which you've might have seen before and here's where you can use the tab key and the spacebar key to start accessing different parts of the menu here. So I'm going to press the Tab key a few times and you'll notice on the menu that there's like a really light blue highlight that shows up around some of these buttons here. Now if I press Shift Tab, it actually with my left hand, it moves backwards in the selection process. Again, tab moves forward and shift tab moves backwards. So let's say I want to sort by just the show family matters, right? So I'm going to hit the tab key until it gets to the column selection there and then I'm going to now that I see the blue highlight around the column, I'm going to press the spacebar and here I can say, okay, I want to filter, I want to sort my list by only show and I'm going to press Enter here. Now do I want to sort the list by alphabetical order or descending alphabetic order. Let's say I want to do it by descending alphabetical order. I'm going to press the Tab key two more times, 1, 2 and now you notice that the highlight moves around the order menu part of the sort menu and now I'm going to press spacebar again and do Z to A instead of A to Z. I'm pressing the down arrow once. I'm going to hit Enter and then now I have my sort all set up. So I can just press Enter for okay. Now you'll see that my list has been sorted based on show from in descending alphabetical order. So again the keyboard shortcut is Shift Command with your left hand R for right hand and then using the tab key to move around the different buttons or menu items within the actual menu. Then once you get onto the actual menu, you hit spacebar to actually access the different kind of fields within that menu. So that wraps up this lesson in terms of auto filters and lists. Again, this is quick ways to filter your data so that you can find the data you need and find it in a sorted fashion, and so the shortcut Shift Command F, Shift command R, they allow you to quickly create the filter and also quickly create the sort menu so that you can get the data that you need instead of using the key, instead of using a mouse to click on all these different buttons on the ribbon.
6. Paste Special: Welcome to Unit 5, paste special. In this unit, we're learning how to use keyboard shortcuts to access all the different radio buttons within the Paste Special menu. Paste special is a really special operation, no pun intended in Excel, that lets you copy and paste certain parts of data, that you want in your workbook or worksheet. The Paste Special menu is, you'll notice a pattern here is that it's brought up by pressing Control plus Command plus the letter V key. Now, let's try to do a paste special values by only using the keyboard. If you're using the mouse on the Mac, you probably are used to doing something like this, where you select the data, you press Command C to copy the data, you know it's copied when you have the little ants marching around the cells. I'm going to move over to column J and you can do a paste special values by doing edit, paste special, and then hitting the values here with the radio button and hitting "Okay." That's all fine and handy, but you want to do this with only the keyboard. How do you do this by using keyboard shortcuts? Let's do this from step 1. Step 2, I'm going to select the data here, six , nine, and eight. You notice quickly that these are just basic addition formulas, that add up these previous numbers, three plus three, four plus five and two plus six. I want to do a paste special values and put these numbers, six, nine and eight only in column J. The first step is select your numbers, that you want to copy over, press Command C with your left hand, and then move over to the column that you want to do the paste and here is where you do the shortcut. Control Command V brings up the Paste Special menu. I normally do control command with my left hand, and then V with my right hand and that brings up the Paste Special menu box, which you've seen before. Once you're here, you want to quickly find a way to move the radio button from all to paste the values. Instead of clicking on values here, once you're in the Paste Special menu, press Command and the letter V. I do Command with my left hand, V with my right hand, and automatically moves the radio button to the values selection, and then once I press Enter, it just says okay and now you'll see that all the numbers six, nine and eight moved over. None other formatting, nor the formulas. So now we have just the paste special values. Now let's try to do the Paste Special formats. Again we have six, nine and eight copied. We're going to do a Command C to copy the formats. Actually before that, go into column K and put it in a few numbers here, let's do like 10, 20, 30. Our goal here is to basically copy the red font and the yellow fill color to column K. How did we do this? We first select six, nine, and eight, we want to copy this formatting of red font and yellow fill color to the numbers 10, 20, 30. Doesn't sound too hard, right? Command C with your left hand, and then move over to the formats column, and then press Control Command V. Again, we saw this with Paste Values, Control Command with your left hand, V with your right hand. Now you want to press Command with your left hand and T with your right hand to move the radio button to formats. Once you do that, press Enter, and there you go, you'll have your actual formatting from column H moving out to column K. Formulas is kind of the same concept. Again, you're seeing a pattern here, Control Command V for bringing up the Paste Special menu and Command F actually does a paste special formulas. So how did we do this? We first select our data that we want to copy the formula from. Again, it's six, nine, eight and these are, again formulas that add up the previous two cells. I'm going to do a Command C. Then it's copied when it has the heads marching around. I'm going to move over to column L now. Control Command with my left hand, V with my right hand, and then I'm going to press Command with my left hand and F with my right hand. There you go. It moves the radio button over to formulas. Then you press Enter, and you'll notice that it only copied over the formulas of the previous, which is adding up previous two cells and not the formatting, not the numbers, just the formulas. The final shortcut in the paste special section we're going to talk about is, transposing data. Transposing data is useful when you want to switch the axes of your data. For instance, let's say I want to transpose this data here and I want to make the values foremost formulas along the left-hand side and I want the numbers and stuff to show on the right hand side. This will be more clear when I show you what this means in Excel. Let's first select this data. I'm going to press Command C to copy. You'll notice the little heads marching around the selection. I'll move over to a blank part of the worksheet and now press Control Command V. Again, we do this a lot to bring up the Paste Special menu, and then let's do a paste special values first. So let's do a Command V. You'll notice that the Command V move the radio button to values. Now I want to check-off the transpose checkbox with the keyboard shortcut. Now I'm going to press Command E, E for Edward. You'll notice that when I press Command E, it checks off the transpose. Now if I press Enter, you'll notice that basically it shifts the orientation of the data so that values, formats, and formulas are now along the rows and the values themselves are now along the columns. Transposing basically lets you shift it to axes using a paste special and then Command E allows you to quickly apply that operation. Again, this lesson was all about using the Paste Special menu. There's obviously other shortcuts you can use to access the Paste Special menu box. The letters that corresponds to each of these operations, you might have to look up on Google, but the most common ones used are formulas, values, formats, and also transpose.
7. Formulas Fills: Welcome to unit six: Formula fills. In this unit, we're using keyboard shortcuts to quickly create formulas on the fly in Excel. So, let's get right into it. The first shortcut is filling a formula down or to the right. Now, is the same thing for both Windows and Mac, it's just the Control key, and the letters D and R. I normally do this with my left hand only, but if you wanted to use your left hand or right hand it's up to you, but I generally find it pretty fast just to use your left hand for both the shortcuts. So, let's see how this works in action. Now, right here we have some basic data, we have the numbers three, four, two, three, five, six etc. I want you to do a sum formula in I5, and write the formula for the sum of the previous two cells. So, I want this to be a sum, left vertices of G five and H5. So, then the result should be six. Great. Now, let's say I want to apply this formula to cells I6, I7, I8, I9 etc. Now, obviously I don't want to rewrite the formulas over and over again, because that's going to take a lot of time. I can reuse the work I've done in cell I5, and create this formula on the fly for all the other cells below by using a formula fill. So, using your mouse you can do this by obviously holding down this bottom right plus sign, and just dragging it down like that, and you'll notice that you can get the sum formula by just doing a drag and drop. Now, how do you do this with the keyboard shortcuts? So, the first thing you want to do is, the first cell has to be the formula that you want to copy. So, it's going to be the number six I5, and now I'm going to press Shifts with my left hand, and down arrow a few times at my right hand, to select all the cells below that number six that I want to apply that formula fill to. So, again Shift, and the down arrow, and now I have all these empty cells that I want to apply that sum formula to. So, here's what I'm going to apply the fill formula down thinking about filling the formula down below whatever you have, to create the formula on the fly. So, I'm going to press Control with my left pinky, and normally do D with my left middle finger. You'll see that it automatically creates this formula on the fly, and if you look into this press Control U, you'll see that it is does exactly that. It shows you the sum formula that sums up the two numbers previous to that. Now, the same applies to filling formula to the right. So, let's say I have this sum formula in G12 equals sum left parentheses, I want to sum all these numbers up here. Great! I have number 26. Now, let's say I want to apply this formula to this cell on H12. It's a similar concept as filling formula down, we're going to fill formula right now. So, I'm going to stay in the cell that has the formula G12, I'm going to hold Shift with my left hand, and hit the right arrow key once, and now I know that I have an empty cell that I want to fill the formula to that I originally typed in cell G12. So, now I'm going to press Control with my left hand, and then R also with my left hand, and it automatically creates the formula on the fly, and if I do a Control U to see what's in that cell, there you go it has a sum formula that we originally created in cell G12. So, as a quick way to create formulas in the fly, sometimes you will have really complicated formulas and you don't want to retype them, and you want to apply that formula to various cells within your workbook. The filling formula feature is a really useful tool because you don't have to use your mouse, and it's a quick way to create the formulas. The next and final formula, sorry keyboard shortcut, in this lesson is to create an autosome of a range of values. So, let's get rid of these numbers here, let's say I want to quickly sum up these numbers. Right? Now, in the previous shortcut we just did equals sum, the left parentheses, and then I had to select the number of cells I wanted to sum, by selecting this and going up here. A quick way to do that, is to press command Shift and the letter T and then enter. So, command Shift T is on by pressing command with your left index finger, Shift with your left ring finger, then the letter T with your right hand. You'll notice Excel smartly knows that you want to sum all these numbers above wherever your cursor was. So, in this case I want to sum everything from G5 to G11 which is what I wanna do. I press Enter, and there we go, it automatically creates some formula for me I didn't have to write out the sum formula, I didn't have to select any cells. Excel just knew right away that I want to sum all those numbers, and it just did all the work for me. Another great way to save time when you're summing a lot of numbers all together at once. This applies to summing numbers across columns too. So, let's say I want to sum the numbers three and three in sell I5. Again I press Command Shift with my left hand, T with my right hand, and automatically creates the sum formula across these two columns. I hit Enter, and it has the right result that I want. So, that's really it for this lesson, we wanted to focus on how to create formulas on the fly in a really fast and efficient manner, and this definitely is a lot faster than using a mouse, and selecting the cells that you want to apply the formula for because, you might accidentally go too make cells down, or it might not select the ones you need. So, make sure to use keyboard shortcuts like fill formula and autosum too quickly sum up your data.
8. Formatting: Welcome to Unit seven, formatting. This unit is focusing on only formatting your data within Excel using keyboard shortcuts. Formatting data as we learned in class one is really important because we want to be able to clearly see data in our spreadsheet, and if you don't know how to format the numbers correctly in a fast way then you might end up taking a lot of time just clicking around different menus to quickly format the data that you want and the number formats and the alignments and the borders that you need. So we're going to talk about some really fast and easy shortcuts to add number formats and borders and outlines to your data. So the first shortcut is simply adding a comma to a big number that you have in Excel. So, I have these two numbers here and you can see that they're very big and they have decimal places and there's no commas, I can't tell if this is 2 million or 200,000, it's obviously 200,000 but if I want to make this more clear for someone to read I could use control plus shift with my left hand and number one with my left hand as well. So, I normally use this with one hand control shift with my pinky and ring finger on my left hand, and then one with my middle finger. If I press that control shift here and then one, you'll notice that Excel creates this number format with the comma so that you can quickly see where the hundreds and thousands places are. Another option again is you use both hands control shift with your left hand and then one with your right hand and it will quickly create that dollar format. Now similarly we can also add dollar signs and percentages to our numbers too. So let's say I want to make this $129.99 number into currency format. Instead of using the home screen and pressing the currency symbol here, I'm going to say in this cell and press control and shift with my middle and index finger on my left hand, and then press number four with my right hand, and you will see Excel automatically created the dollar currency format that I want. Let's also do the percentages for cell F5, 0.67 we want to show that as 67 percent. I'm going to press control and shift my with my left hand, five with my right hand and there you go we have 67 percent. A quick way to remember or see rather, the number formats is four has dollar sign symbol in it, five has the percentage sign in it so, that's a quick way to remember which number formats are used by which numbers. The next shortcut is learning how to align things center, left and right. So on the Mac, it's actually a really simple shortcut which is command plus letter E for center formatting something, command L for left aligning something or command R for right aligning something. So let's try to center align this test data in cell E6. So I normally just press command with my left hand and then E with my left hand as well, and then you'll see Excel automatically center aligns the data there. Again we can do Command L for left align, R for right align and it's just a matter of pressing the command key with E, L and R to cycle through the different alignment options in Excel. The next shortcut is adding borders to your data. So adding borders really is like an aesthetic thing but allows you to segment your data from other data on your worksheet. So the quick way to add an outline border around your data is pressing command option with your left hand and the number zero with your right hand. So let's say I want to add a border around this data right here, all I have to do is press command with my left index, option key with my middle finger or it's the alt key, and then I press the number zero. Then if you move away from your selection you'll see that it applies a thin black border outline to your to your data. To get rid of the outline, you simply select the data you have again and press command option with your left hand and then the negative sign which is right next to the zero key, so it's easy to remember. So if I press command option minus sign, Excel gets rid of any borders around that selection. Now same thing applies for adding a right, left or up or down mortar. You can simply press command and option plus the various arrow keys to get the border you want. So let's say I want to apply a top border to just these two cells E4 and F4. I'm going to press command with my left hand option and just the up arrow key with my right hand. You'll see that Excel just helped me add a top order to my data. So this is a really useful shortcut to know when you want to quickly add borders to your dataset. The final shortcut in this section is how to bring up the format menu. Once you bring up the format menu using the tab key and spacebar key and arrow keys will help you navigate the menu, and let's see what that means. So, let's say I want to convert this data. I want this data here to have only cells E4 and F4 to have currency format with no decimal places, right. So I'm going to first select these numbers and I'm going to bring up the format menu with the command one keyboard shortcut. I normally do this by pressing command with my left thumb and then one with my middle finger on my left hand, it'll bring up the format cells menu which you've seen before in Excel. Now instead of using your mouse to select all these different menu items in the format cells menu, learn how to use the Tab key to get around different parts of the menu. So, again we saw this with the advanced sore and advanced filter keyboard shortcuts. If I press the Tab key, you notice that there's a light blue highlight that moves around the menu. See how categories first highlighted now, I press Tab again, okay is highlighted, then cancel is highlighted and then numbers highlighted. So once you get to the section that you want to move around in, you just stop pressing the Tab keys. So I'm going to stop the Tab key once I get to the category. So now once I'm in the blue outlines in the category, I'm going to start pressing the down arrow key to actually access these different formats. Now I said earlier I want to apply a currency format with no decimal places, right? So I'm going to go to the currency list item and now I want to basically edit the decimal places so that I only have zero decimal places. So now if I press Tab key one more time, the Tab key shifts the focus, the blue outline to the decimal places. Now I can press either zero or the down arrow key twice to get the number of decimal places I want which is in this case zero. Now I have the right currency format, I press enter and there you go. Now you have your currency format with no decimal places. So the key concept here is once you bring up the Format Cells menu with command one, learn how to use the Tab key to move around the different menu selections within the menu. So for instance once I'm on the number, you see that number is highlighted right here, I can use the right arrow key to actually access different parts of this menu by moving over to let's say font, pressing space bar, I can press Tab again to actually move to the different parts of the menu and access different menus using the Arrow keys. That wraps up formatting for this lesson. Again, using the shortcuts will help you apply number formats, border outlines and alignments. Just play around with the format cells menu as well because that will be one of the most used menus when it comes to formatting your dataset.
9. Special Shortcuts: Welcome to unit eight, the special shortcuts. In this unit, we're going to look at some special shortcuts like redo that will make your lives even faster using Excel. So, the first shortcut is actually only available on the Windows machine, autofitting columns and rows and unfortunately, there's nothing for the Mac and I think you can actually use the customized keyboard menu item within Excel to find one for the Mac, but there is no actually native Excel Mac shortcut for autofitting a column or row. The Windows shortcut is essentially Alt key and then O, C, A for autofitting a column. So, that is something to be aware of when you're using Excel on the PC, but nothing exists for the Mac. Now, redo format is another shortcut that's really meant for formatting, obviously and it's a little different from Paste Special because it allows you to redo formatting over and over again with a stroke of one keyboard shortcut. So you're probably used to using Command Z to undo something. You probably created a formula you didn't mean to create, you wrote something you didn't mean to write, and you press Command Z to undo something, while redo, which is Command Y on the Mac, is almost just as useful as undoing something. So, how do we use redo to redo formatting that we've done in a previous cell? So, let's look at this example in cell E5, we have the words hello world with like a red font, a blue fill color, and a black border outline. So let's say, I want to apply that same kind of formatting to, this is Excel text here. So, how do I do that? The first thing I'm going to do is press Command C. We know how to do that for copy. Once I do that, you'll see that and start marching around the cell. So, I know I didn't copy something. Now, we're going to do a paste special format and we learned in a previous lesson that this is Control plus Command with your left hand and V with your right hand and that brings up the Paste Special menu. Now, to do a paste special formats, I'm going to press Command with my left hand and T with my right hand and the radio button moves to Format. Now, I'm going to press Enter, Okay. There you go, I copied over the red font color, the blue fill, and the black border outline to the cell. Now, let's say I want to copy this format over to cells I5 and L5 as well. Now, I don't want to do a paste special again because that took so many keyboard strokes, where I had to press Control, Command V, Command T, Enter, too many shortcuts and my fingers are going to get tired, I don't want to do it. So, this is where you can use the Command Y keyboard shortcut to redo something that you've done in the past. So, let's do that right here. So, I'm going to press Command with my left hand and Y with my right hand and you'll notice that all it did right there was apply that same formatting from this cell G5 to I5. I read data formatting operation I did in the past. Let's do the same thing to L5. Command Y, I want to do this now with my left hand only, Command Y and there you go. I can do this all over in my Excel file that I want because Excel knows that my last formatting operation was this red font, blue fill color with black border outline, and I'm just redoing this operation over and over again all over my Excel file. Next shortcut is this special shortcut, which is cycling through absolute and relative cell references. Now, this allows you to basically add the dollar sign to the column, the row, or both. So, let's look at what this means in Excel. I'm going to be in column cell G6. If you look at the cell contents, just as do a Control U, it's just the sum of E and F, E6 plus F6. Now, let's say I want to create an absolute reference for both E6 and F6. Now, while I'm still in edit cell mode, I'm going to move over to E6, you see my cursor is blinking now within E6 and now, I'm going to press Command with my left hand and T with my right hand. You'll notice that Excel automatically added the dollar signs to the letters E and the number six. If I press Command T again, it'll just show the number six with a dollar sign, if I press T again, it just shows letter E, and if I press T again, Command T, nothing shows up. So, if you just keep on pressing Command T here, basically it cycles through an absolute and relative reference for that given cell. Once you have what you want, you can just let go and then move over to other parts of the formula and press Command T to also do absolute and relative references for those cells as well. Once you're done, you just press Enter. The next shortcut is for increasing and decreasing font size. Now, this is a special again, shortcut that is not available on the PC. So, for you Mac users out there, you're in for quite a treat. This is something that only Mac users have and it's a simple shortcut of doing Command and Shift with your left hand and then the period and comma keys. Why it's period and comma, I don't know, but this is what's available on the Mac. So, let's press Command with your left index finger, Shift with your left middle finger, and then press the period a few time, and you'll notice that you'll increase the font size really big. Now, let's do the opposite, let's decrease the font size. So, Command with your left hand, Shift with your left hand, and then press the comma sign, you'll notice you'll quickly decrease the font size, and you can cycle between increasing and decreasing by just holding down Command and Shift with your left hand and then pressing period and comma to go back and forth back and forth. The final shortcut is not really a shortcut, but for those on the PC, do a quick Google search for the Quick Access Toolbar. You can actually access most common operations that you use in Excel by pressing the Windows key plus a number sign. This is not available on the Mac unfortunately, but for those on the Windows platform, do a quick Google search for Quick Access Toolbar, and that's another way you can quickly save time using keyboard shortcuts. So, this wraps up this lesson. It's a more section on special shortcuts to make your lives faster. These are shortcuts that analysts, bankers, and people that we've consulted with use on a day-to-day basis to make their lives faster when it comes to formatting, locking in certain cell references, and also just increasing and decreasing font sizes to help you make your formatting in Excel a lot faster.
10. Final Exercise: This is the final lesson in the keyboard shortcuts class. We're going to take everything we learnt from lessons one through eight, around formatting, around Paste Special et cetera, borders. We're going to quickly see how we can create some data, make some raw data, and make it look presentable to your client, your manager, to your boss, whoever it may be. So, let's look at some data right now in cells B3 to cells F9. You'll notice that it's basically a bunch of revenue numbers by these kind of travel categories. So, we have Air Travel Revenue, Cruises & Charters Revenue, Specialty Travel Revenue and it's broken out by the first six months in 2011. Our goal is to convert this data, and make it look a little more presentable so it looks like this data right here. This data as you can see, let's resize this a little bit. This table is that we've highlighted here, looks a little more of well-formatted. It has some border outlines, it has the number of formats that are in millions because these numbers are so big and hairy. You want them to look a little nicer. So, we have one decimal place, and we have one dollar sign with the currency format, with one decimal place and we also have the sums which are really important to have because you want to know whether the sum of each month is and for each kind of category. So, how do we convert? I want you to kind of think about how you would convert this data into this table below. So, why don't we just basically recreate this table? So, I'm going to walk you through how I do it in Excel keyboard shortcuts. Your order of operations might be different from mine but the operations are still uniform nonetheless. So, let me walk you through how I would do this in terms of converting this raw data into this output as our final exercise. So, the first thing we want to do, I noticed that the months are actually along the top and the revenue categories are along the the rows. We learned the Paste Special lesson that this is a matter of using the Paste Special transpose operation. So, the first thing we're going do is copy the data. So, I'm going to select all this data right here, command C to copy and I'm going to move down here and a blank area of Excel and I'm going to do a Paste Special transpose. So, this is Control Command V, Command E for transpose checkbox right here, enter and there you go. Now we have the months along the top and we have the Travel Revenue categories along the sides, which looks more like the output we have here. So, now let's try to do some more formatting and format building to make sure we have this in the right format you want. So, we want these data formats and not be the traditional month, day, year format. We want to show the actual letters in the month. So, I'm gonna bring up the format cells menu, I'll first select this data by pressing Command One and then I'm going to make sure I've got my highlight over the number menu. I'm going to press Tab until I get to my type and then I'm going to select the format I want. In this case, I want to have the three letter format plus the year, so it's this Mar-01 format. Hit enter and there you go. Let's make this bold, so I'm going to do a Command B. We want this to be in millions. So, let's do this in millions. Just type that out, there's not a shortcut there, just type it out. Command B to bold as well, and here all these numbers are actually in millions. We actually want to make these numbers in millions and right now it's just written now in the long format. So, we're going to do a Paste Special divide which we didn't go over in the Paste Special menu but we'll do it right here. What a Paste Special divide does, it applies some operation to the numbers or data that you're trying to do a paste special on. So, in this case, we know we want to convert these numbers right here into millions. So, how do we do that? We have to divide each number by the number one million. Right? So, I'm going to write the number one million down here one, two, three, one two three. I'm going to do a command C to copy and now I want to do a paste special divide which tells Excel I want to divide all these numbers here by number one million. So, Control Command V for Paste Special, command I for divide, you notice that the rear button move over to divide. I'm going to press enter, and there you go now I have these in a million decimal places. So, now let's try to apply currency format to this. So, we can get rid of all these decimal places as well as add a dollar sign. I'm going to press command one. Now I'm going to move over to the category of currency, decimal places is just one decimal place. Hit enter and there you go now. It's looking a little nicer, neater without all those decimal places. I'm going to delete this for now and here we're going to go,we are going to write the word total, because we want that column to be the total. Want this also to be a total and we're going to do the auto sum formula feature which you learned in a previous lesson and that's Shift Command T which automatically builds a sum for us. I hit enter and I want to bold this, right? Now, how do I apply the same formula to all these other cells to the right? We will learn that in the formula fill lesson. So, we're going to stay on the cell with the formula we just built. I'm going to select all the cells to the right by holding the shift key with my left hand and the right arrow key a few times and now I'm going to press Control with my left hand and R with my left hand as well. Bam! You have all these formulas built on the fly ready for you to use. No typing necessary. We're going to do the same thing across the categories Shift Command T, Enter and we're going to build this as well. We're going to fill the formula down this time. So, I'm going to select all the cells below that cell with a formula Control and the letter D and there we go. I should want these to be unbolded and the last thing we need to do actually is just apply the border all lines. So, let's think about this I want to apply a border around this entire dataset, right? So, I'm going to select my entire range and we learned that it's Option Command zero for applying the whole top border or the border outline. Right. There we have it. There's a border around our data and finally, we want to have these individual bottom and top orders for the months and the total line. So, I'm going to apply a bottom border to this line right here by selecting the data and then pressing option command and down arrow. Now I have that top, the bottom border and here I'm going to apply a top or in the total line by pressing, first/second data, Option, Command and top arrow and there we have it. So, that was the final exercise for tying to gather everything we learned the first eight lessons about how to use keyboard shortcuts and hopefully you were able to follow along with this final exercise. If not just kind of rewind and look at some of the steps that we took to get to the final output, which is this table right here and hopefully you can use these keyboard shortcuts to make your lives faster when it comes to formatting and writing formulas. All right. You finished class two. You're superfast in Excel. What are you going do with all these awesome skills? Class three is all about analyzing some real data. Finish off this three part series by doing real analysis on some real data and you'll be on Excel wizard by the end of this class.
Al Chen, Excel Trainer & Coda Evangelist
