Accelerate data entry with five special techniques

- [Instructor] Excel has any number of different techniques for reducing the drudgery of data entry. It can be pretty boring sometimes when we gotta do it, there are some techniques. Here are five of them listed in Column A. We'll be covering these. Pre-select an entry range. I'm about to type in some numbers here. I've got a sheet of paper in front of me. It's gonna be easier if I highlight these cells first and now as I type the entries here, they're going to be numbers, they don't have to be numbers but they are in this case. I type a number and press Enter. And another number and press Enter, and so on. As I do this, as I'm approaching the bottom here, I'm still pressing Enter and look what happens.

Automatically Excel re-positions the active cell at the top of the next column. And I could complete that column too. And you guessed it, as soon as I come down here and press Enter, over there. The same general idea here. I want to highlight the data ahead of time but this time, based on the information, I want to put the information moving across the range first. So after highlighting the data, I start to type the numbers. This time I press Tab. And you can probably figure out what's gonna happen here.

As soon as I press Tab on that last entry, the active cell jumps over to here. So that can facilitate certain kinds of data entry. In other words, we're not looking necessarily at the data, we're just typing the data and pressing Tab. There in those yellow cells, I did the same kind of thing except I pressed Enter. And although pretty obscure, there were two other variations here. You could highlight the data in a different way and press Shift + Enter and that would cause the active cell to fill in a range starting from here moving upward and then here, and so on.

And then also, if you're using Shift + Tab, you could do the same sort of thing here in a backward way. But I think almost anybody would be using either the Enter key or the Tab key as we've seen in these two examples. Automatic decimal point. I've got a big list of numbers I've got to type in here. Let's say they're all prices, mostly under $1,000 but they all have decimals and I don't want to type the decimal every single time. So I'm gonna turn on a feature and while this feature is on, it's going to apply to not only just the worksheets in this workbook but all worksheets in all open workbooks.

And if I don't turn off the feature as I open and close Excel it's gonna be available all the time. And where is it located? Off the File tab in the ribbon. Down to Options, and then under Excel Options, choose Advanced, and right here, Automatically insert a decimal point. Notice that the two is down there. That's probably the most common choice for most people. Two. Click OK. So, I'm going to type an entry. Now, the column hasn't been formatted, that's not a prerequisite although I might want to do that. Three, four, five, Enter.

Five, six, seven, Enter. Three, four, five, six. You see what's happening? Automatically the last two characters are considered pennies. There we are. Get into three, four, five digits, whatever. Same thing. Even if we get into a lot more digits it's still going to be the same. Same general idea. Formatting, of course, would make these display a little bit more readibly. But that's easily set up. If this were scientific data, you might want to have that set at three decimals, or four decimals, you could easily imagine that. But this in effect indefinitely.

At some point when we no longer need this feature, we'll go back to the File tab in the ribbon, choose Options, back to the same place, choose Advanced and here, uncheck the box for automatically inserting a decimal point. There's a feature called AutoComplete that you might have encountered without really knowing what it's called. I'm about to put in some salesperson's names here. The names will be repeating so the first salespersons name is Ames, next one is Smith, next one is Perez.

Now, Ames pops up again so I type A and what do we see? Well, there it is. Good, I'll just press Enter. I type S, there's Smith. Fine. I am about to type Sanders, I type S, I don't want Smith so I'll type Sanders. P, there's Perez, good. Now, I'm about to put in Smith again but I type S and nothing happens. But as soon as I press M, we see what's happening. And now I need to type Sanders, I type S and I don't see anything. I type A and you see what's happening. This only works with text and the text must be contiguous but it certainly is handy in some situations.

Now, those of you who are familiar with data validation know that you could set up a pick list. That might be helpful too. That's beyond the scope of this tip. I'm going to go to the next sheet in this workbook. And here's a list of all the departments in the company that I work for. And I often need this list. Now, rather than copy and pasting it, wouldn't it be great if I could just type in an entry like ADC and then be able to copy all the other entries without retyping or copy and pasting any of these? So, I'm going to turn this list into what's called a custom list.

Now, I'm not really converting the data here, I'm using this as the source. It's all highlighted. I'll go to the File tab and choose Options. And once again, go to Advanced, and drag all the way to the bottom. And there we'll see Edit Custom List. Create lists for use in sorts and fill sequences. Now the list that I have happens to be sorted. That is by no means a prerequisite, it can be in any order that makes sense to me. In this case, alphabetizing it does make sense so that's the way it is.

Edit Custom Lists. And built-in custom lists are the days of the week and their abbreviations, the months of the year and their abbreviations, three letters in each case. In the background I've got this data highlighted. If I didn't have it there I'd have to be typing it in this section here that says list entries and I would type ADC, Enter, Admin Training, Enter, and so on to get it in there. But it is highlighted so I can simply Import it and click OK, and click OK.

Now, that list will remain in my Excel settings indefinitely. At some later point, maybe I won't need it, I'll get rid of it. But as I open and close Excel, that's always going to be there. So this means in any workbook, on any worksheet, at any time, if I need that list, I can type in any of the entries, here I am back on a separate sheet. Department. Now, if I want all of the department names there, I'll simply type the first one, ADC, and point to the lower right-hand corner, click and drag.

Now, I forgot how many there are but it's roughly 25 so I'll probably drag a bit too much here, that's okay. And it starts to repeat again. Just get rid of that. Now, when you forget which entries are in here, you might, at a later time, be in a different worksheet, maybe you've typed in, for example, operations. Maybe in a totally different context. You might want to repeat that a few times by dragging downward but because this is in a custom list, if you drag from the corner downward as I just did, you'll get these other entries.

If you really did want to have that word repeated three times, hold down the Ctrl key and drag that fill handle in the lower right-hand corner down to here. Be sure to let go of the mouse first. It's unlikely that would happen but it certainly could. So this list is available all the time. If it was to be in a separate order, maybe it could have been in a hierarchical kind of order, you certainly could have stored it that way. But anytime you want this list, you're likely to start with the top entry. In other words, just type that and drag it down. But that's really handy. And a lot of you know, if you type Jan or January, or any month, actually, or its abbreviation, from the corner you can click and drag downward and that certainly facilitates data entry.

Most of the time it's gonna be six or 12 months there so you can easily do that. And if you drag too far, you just have to get rid of the extra entry down there. Same thing happens with days of the week and full spelling. So those are already built in but you can add your own custom lists. And if this list is not in alphabetical order, if it's in a hierarchical order, then if you've got data where these departments are being used, you could sort the data based on the order of this list, not alphabetically. So that's another handy feature of that capability. So once the custom list is created, it's there indefinitely until you decide to take it out.

There's another feature called AutoCorrect. Suppose I find myself typing telecommunications a lot. That's an 18 letter word. Surely it could be faster. I want a code for that. And there are some other phrases that I type a lot. Maybe I'm involved in some financial dealings. I type balance of payments a lot. I don't want to keep typing that all the time. I'm gonna go to the File menu again and choose Options, and then Proofing. Change how Excel corrects and formats text as you type. It's called AutoCorrect.

And you'll see built in here already some shortcuts. If you type, for example, (c), you automatically get the copyright symbol. And there's one for the Euro, there's one for registered, there's one for trademark. And scroll through this list every so often and you'll see some other shortcuts. But most of this list is about common misspellings and a lot of them are typos. Most of you know how to spell about but there are some common typos there. And you might go through life misspelling accommodate, Excel will always correct it.

It's got a double C and a double M in it and there are three bad spellings of accommodate in here. But, we can create our own shortcuts. So I'm gonna put the word on the right first. Telecommunications. Now, if I know that I'm always going to be using it capitalized I'll make this be capitalized, but just to show that we've got some flexibility I'll type it in here in lower case, but the shortcut that I make for it will be the entry that I will type that causes that to appear. The letter Q does not appear that often in words and nearly always is followed by the letter U.

So I'm gonna use a code qt. I'm typing that in lower case as well. I'm gonna add that to the list, click OK. And I might do a few more too. Let's go back again. AutoCorrect. How about balance of payments? Maybe that's something I use frequently. I don't want to type these anymore. And every time I use that I do want the B and the P to be capitalized. And I'll just use qb for this one. And that's in place. Add, OK.

That stays there indefinitely. In a different context maybe I'm about to make an entry, I want to type The Telecommunications Department. And I want this to be capitalized here so I use a capital Q, then t. And as soon as I type either punctuation, space, or Enter, Telecommunications appears. That sort of thing. Balance of payments. Maybe I want that to be a heading on the other worksheet over here. I'll type qb. Enter. Balance of Payments.

You could imagine how that could save you a lot of time if there's certain words and phrases that you use frequently. And you can use this in Microsoft Word as well too. In fact, these settings should be set up there too, provided your Office installation is a standard one. So, a ton of time could be saved here by typing these shortcuts. And be careful with the shortcuts. I saw an individual once use in as a shortcut for invoice but in, of course, appears often as a single word in certain phrases so that was not a great idea.

So, we've seen different techniques here. Again, looking at the list here, Pre-select an entry range, we did that with the yellow and the tan cells. We showed you how to automatically insert a decimal point. Remember, you have to go to File, Options and make the change there. AutoComplete kicks in automatically but only in columns and only with text. A custom list. You can create a custom list. It'll be available indefinitely. And AutoCorrect. We saw how to get to some of those settings. And as a reminder, some of those built-in AutoCorrect features like (c) automatically puts in that symbol.

And (r) for registered. And one more, (tm). So, great features here for simplifying and accelerating data entry.