From the course: Excel Tips Weekly

Accelerate data entry with a variety of special techniques - Microsoft Excel Tutorial

From the course: Excel Tips Weekly

Start my 1-month free trial Buy for my team

Accelerate data entry with a variety of special techniques

“

- [Instructor] Excel is any number of different shortcuts for reducing some of the drudgery of data entry, and five of them are listed in the green panel we see on the left side of this worksheet. The first one, preselect an entry range. Imagine I've got a sheet of paper in front of me, and I want to fill in these cells right here, so I highlight them ahead of time, and for me, it's going to be easier if I put in the January numbers first, so I'm going to type a number here. For January it's going to be 6, and then I'll press Enter, and 7, Enter, no surprise so far here. 8, Enter, 4, Enter, 5, Enter, 6, and as I press Enter here, what happens? Because these cells are preselected, when I get to the bottom of the list and press Enter, automatically the active cell moves to the top of the next column, so I'll put in those numbers there. Same thing happens, one by one, so that's really handy for certain kinds of data. If I have another list, think of it as being oriented horizontally, and I'm going to put in the numbers for Ohio first, then Michigan and so on, I'll highlight the cells, as before, but this time I'll press Tab each time after I press Enter. Same general idea, and so on and so on, so that's going to be handy with certain kinds of lists. Now, in cell J2 I've got a price here, and I'm going to be putting in lots of other prices here for items. Most of them are under $100, but I don't want to be typing the decimal point each time, so I'm going to turn on a setting. It'll stay on indefinitely until I turn it off by way of the File tab up in the ribbon, down to Options, and under Advanced, Automatically insert a decimal point. It's going to be 2, That's probably the most common choice for most people. Click OK, and now, as I type an entry, the next entry is 9.95. I'll just type 995, Enter. The next one is $20.50. 2050, Enter. 4599. I'm not typing the decimal. They come in automatically. At some point, I've got to remember that that's on because if I don't and I start working with other kinds of data, that decimal's going to pop in when I don't want it, so when I'm finished using that feature, back to File, then Options, and again, on the Advanced tab, turn off the Automatically insert a decimal point option. You can imagine with certain kinds of scientific data that might be three decimals or four decimals, and you can even use a negative entry for numbers to the left of the decimal. Now, a feature called autocomplete, you don't even have to do anything; it kicks in automatically. Anytime you've got a list of information, of text in a column, when you start to make an entry in the next available cell, Excel analyzes what preceded it, so if I'm about to put in Morgenstern right now, I'm going to type M, and there it is, Enter, and now I need to type Richardson again. Yes, and if I've got a situation where these entries are likely to be recurring, I'll simply type the first letter, but there could be some conflicts, and I'll put in another small name here, Smith, and now I want Sanders, so I type S. I don't want Smith. I don't have to wipe that out. I'll just type Sanders, and so on, so that's going to be handy up to a point. Depends on how much variation you have and how much repetition there's likely to be, but if I type a W right now 'cause I'm about to type a new name, it says Washington. Now, that could be the one I wanted, but if it's not, I've got to put in the other name there. Maybe it's Weldon or something, so on. Another way to get to this, not necessarily faster, but logical, in the next available cell, right-click, pick from dropdown list. There it is. I want Richardson. Another way we could do this is with Alt + down arrow. Alt + down arrow. Use the arrow keys, so on and so on. There it is. Again, not necessarily faster, but at least logical. This only works with text. It only works with columns. I think you could see in some situations till you get to 20 or 25 names somewhere out there, maybe it becomes a bit unwieldy, but it's really handy otherwise. Now, in column N is a list that I need frequently, and rather than having to jump to this particular workbook and copying and pasting it, I can make this available all the time so that I can only type in the first entry and drag from the corner and get all the others. The feature's referred to as a custom list, and I also have a list in column P that I want. Now, this is a list of all the states, but it's organized more or less geographically, east to west, and let's say I frequently need this order. If I create a custom list for this, I can then sort the data in this order, and I can also make the entry in a column just by typing in the first name, so (indistinct) do it for one of these. Highlight these cells here. 'Member, the impetus for this is I need this frequently, so it's highlighted. Go to the File tab of the ribbon, down to Options. Advanced, scroll almost to the bottom. Edit Custom Lists. Import. There it is. The other ones you see over there, days of the week, months of the year and their abbreviations, those are built-in, but we've imported a new one. We click OK. It's going to be there indefinitely, but how might it come in handy? A few days from now or weeks from now in a different worksheet, different workbook, I'll click in a cell. I'll type ADC. You can type any of the entries, but typically you'd start from the top. ADC. Drag from the lower right-hand corner. I'll get all the others. If I drag a bit too much, it'll just start repeating. Get rid of that, so that's going to be really handy. The same thing would work if I dragged across a row too, same idea, and I could be doing the same thing for this kind of, or the set of entries here in column P, or entries, just like the alphabet, we could make that a custom list, so those are really handy too. Available only on this computer. Of course you can, if you're using Excel on a different computer, do the same kind of thing there too. There are also some autocorrect shortcuts. You see some of them listed over here in this orange panel. If I type left parenthesis, C, right parenthesis, and followed by Space, Enter, or any other character, there it is. Turns into the copyright symbol, and we see some of the other entries there too, and another aspect of this too is here's a word that I use frequently, let's say, and I'm really tired of typing it all the time. It's 18 letters, so I'm going to copy it right now. Control + C and Escape, and here too, go to File, Options, but this time Proofing, AutoCorrect options, and I'm going to put the word telecommunications right here with Control + V to paste it in, but I've developed a code for it, and I've thought it out a little bit. I'm going to use the letter Q followed by T because that will almost never appear. Now, you might think, "Well, it's the abbreviation for quart." Well, yeah, but I don't ever use that, so what I'm saying now is I'm going to use QT, and as I type it, it's going to turn into telecommunications if we add this to the list, okay? Okay, so anytime I'm typing, if I type QT followed by Space, Enter, Tab, automatically it pops into place. I could use it in the middle of a sentence. If I type capital QT, Space, there it is that way too, and what if I use Daily Sales Report or this department here? If I need these frequently, I'll develop a similar kind of code, a two-letter code, typically, but anything you want. It's just a great shortcut, and it will work in Microsoft Word as well too, so these are all various handy features to reduce some of that drudgery of data input and making some of that day-to-day work with Excel much more efficient.

Contents