[MUSIC PLAYING] ROB HAMILTON: Well, hello, and boomshakalaka. This is Rob Hamilton from soul Canyon Training and Development. I want to take a few short minutes here and show you some really cool things about Excel that you may not know, things you may have heard about, but you don't necessarily know how to use some of the cool features associated with them. 

The first thing I want to talk about is Autofill. For those of you who don't know it by its name, Autofill is basically-- let's say you type Monday into a cell, and you want the other days of the week. The Autofill handle is right here in the lower left-hand corner of this box called the marquee. If you simply click and drag that down until it says Sunday, there you have the other days of the week. It also works with months, for example. So there's January, and then you pull the Autofill handle down until you get to December. And there are the months of the year. 

It works a little bit differently with numbers. With numbers, when you pull the Autofill handle, it wants to copy the number. But right after you do an Autofill, if you hit that little Autofill Options box and change it to Fill Series, it'll switch it to a series of numbers. But a faster way to get there is to actually highlight the number. When you want a series of numbers instead of it to copy the number, just hold the Control key and pull Autofill, and you'll get it to fill in a series of numbers. 

If, with a label, like Monday, you want a bunch of Mondays-- and who doesn't? Just go ahead and hold the Control key while you pull the Autofill handle, and you'll get a bunch of Mondays. So that's kind of cool, right? 

Now, here's a couple other things that are really cool about the Autofill handle. You can create your own list sequences. Like, for example-- let's just get rid of this stuff, get a blank slate here again. Let's say we want 30, 60, 90. So all you have to do is do 30 and 60, highlight both those numbers, pull the Autofill handle, and you'll get 30, 60, 90, and so on. Like odd numbers-- 1, 3-- highlight both of those, pull the Autofill handle, and you get a series of odd numbers. 

So the cool thing about Autofill is a lot of people know that you can pull the Autofill handle, and it will copy a series. A lot of people, when they pull the Autofill handle and have that experience, they're a little bit disappointed that they can't do the same thing with numbers. But with Control Autofill, you can. So there's Autofill. There's Control Autofill. And then there's also the ability to create your own sequences of numbers. 

Here's another cool thing about the Autofill handle. Let's say we want to create a formula in this cell to make the forecast 2019 salaries 10% higher than the 2018 salaries. So we'd say equals and then reference the 2018 salary times, with the asterisk, 1.1. 

Now, if we want to copy that down so we can get 10% increase on all the different salaries, one way to do it is we could drag the autofill handle down to the end of the list and have it stop there. That's OK for eight line items, but that literally gets to be a drag when you have 1,000 line items or 1,500 line items. 

So here's a quicker way to get that copy to the bottom of the list. Go ahead and do it once. But instead of dragging the Autofill handle down, double-click on the Autofill handle, and it'll copy that down to the end of the list. Whether it's 8 items long, 50 items long, or 1,000 items long, instead of dragging the Autofill handle down, just double-click on the Autofill handle, and it will copy it down. So that's kind of cool. 

Next thing I want to show you is Flash Fill. Flash Fill is something that comes with 2016, the most current version of Microsoft Office-- Microsoft Excel, in this case. And Flash Fill is a really cool way to take existing data and configure it a little bit differently. So let's do a couple of things here. 

Let's say we want a Full Name field. So we're going to create the Full Name field by taking the first name on a particular line item, putting a space, and then the last name. Now, you might think that would be kind of a drag to have to do that all the way down. And you might think, well, maybe that's something cool that Autofill would do. No, it's not. That just copies Elizabeth Ridenhauer all the way down. 

So there's a really cool feature that comes with 2016 called Flash Fill. If you just go ahead and create one and then get right under that and use the keyboard shortcut for Flash Fill, which is Control-E, hold the Control key, press E, it does that thing for the rest of the records. If you want for Social Security numbers, not to display the whole Social Security number-- don't worry, these aren't real-- but display them like this, xxx-xx- and then the last four numbers of the Social Security number, just do one. Get underneath and do Control-E, and you'll have the rest. Kind of cool, right? Well, that is Flash Fill. Flash fill is a new feature with 2016, and it does stuff like that. 

Now, last thing I want to show you is AutoSum. AutoSum-- if you're going to add up all these numbers, the best way to do it would be to write the sum function-- equals sum. And then highlight the range of numbers. So that's I2 through I9. And then press Enter. A faster way to do it is to use the AutoSum button. 

Right up here on the Home ribbon tab, over on the right side, you have the AutoSum button. Also, on the Formulas tab, you have the AutoSum button. I've just put the AutoSum button right here on my Quick Access toolbar. You can just go ahead and click the AutoSum button. It does all that writing for you, right? 

Cool thing about AutoSum that not a lot of people know is that if you have multiple cells selected and you click AutoSum, it will do multiple AutoSums for you, which is kind of cool, right? But the last thing I want to show you about AutoSum is that you don't have to use the button to automatically write the sum function. You can use the keyboard shortcut Alt equals. Get below or to the right or wherever you want the AutoSum to be, hold the Alt key, and press equals. 

If it highlights the right numbers, all you have to do is press Enter. And there you go. And then you can drag that over, and you can have the other column taken care of. Or as I showed you before, you can just highlight both those cells, Alt equals, and there you go. That is the sum of the 2018 and the 2019 salary. 

So a deeper dive into Autofill, an introduction to Flash Fill, and some different ways of using the AutoSum feature with a keyboard shortcut. Hope you enjoyed those. If you like those tips, I have a lot more for you. At the end of this tutorial, you're going to be able to click a link and go over and check out our Excel Essentials training program. 

Give it a look. If you like the way I train and you like my tutorials, there's a whole set that will take you from basic Excel skills all the way through advanced. Just check it out, and it'll be easy to pick that up if you'd like to do it. So that's it for now. This is Rob Hamilton from Soul Canyon training and Development. A big boomshakalaka. Until next time. 

[MUSIC PLAYING]