Join Dennis Taylor for an in-depth discussion in this video Accelerate data entry, part of Excel: Tips and Tricks.
- [Instructor] Data entry is not one of our favorite things to do in Excel, at least for most people. But there are some shortcuts associated with certain kinds of data. In this worksheet, sheet one, in our chapter five files, pretty much empty except for the word name in column A. We'll be putting in some names down below here. In column B I'm going to put in 2018 Salary, and as I'm typing this I'm thinking, this column doesn't have to be as wide as the heading. I'd like to have the word salary appear in the same cell. So instead of typing salary right now, I'll press Alt + Enter, that introduces a line break.
Then I'll type salary. I don't have the salaries in column B below this, but I know that it's not going to be very wide. And the column won't even have to be this wide either. And sometimes even though we're making the road taller, we're losing a little bit of space there. We made the column narrower. And anytime you work with lists in Excel, it is going to be more efficient if you're titles are in a single row. So rather than putting 2018 in one cell, and salary below it, this is the better way to do it. And as you're typing, pressing Alt + Enter introduces a line break.
And maybe over here we're going to put in the 2018 tax code. Type 2018, Alt + Enter, these codes are going to be one or two characters, so I don't want tax code to display to the right here. I'll type in tax, and I'll press Alt + Enter, and then type code, and Enter. And this might look better if I click row one, and then on the Home tab use one of the vertical alignment buttons. I could use one that puts the data on top, that's top align, or middle align, either one.
That's certainly going to be faster. Now getting to the names over here, I'm going to have some recurring names perhaps in this list. If I do, and maybe I'm just putting in last names here. I'll put in Adams, then Lee, then Perez. What if I start to type Adams again? I type A, if that's what I want, I'll simply press Enter. In other words Excel is sensing that there's data above this. I'm going to type Smith, I'm about to type Sanders, I put in an S.
Excel thinks I might want to put in Smith, I don't, I'll type in Sanders. I put in Lee, what if I want to put in Smith again? I type S, don't see anything happening, but as soon as I put in the M, I will. So a situation like that, that's going to be handy. This only works with text, and it only works within a column. That could be handy at times. If I right click in the next cell here, we could choose Pick From Drop-Down List, that's certainly not as fast as what we saw before, but we could make our choice this way.
And another way to get to this too is to press Alt + down arrow. And we see our list, use the arrow keys. I think you could sense here too that if the list gets fairly large this isn't goin'a be too efficient. But, in some cases, that will work as well too. Now at different times we're putting in data in a range. What if we've got a range of cells where we want to put data in? Suppose it's something like this. Looking at our sheet of paper where we've got a bunch of numbers, we could do what sometimes had been referred to in the past as heads down typing.
I want to put in some numbers moving rightward here. I've got the data highlighted ahead of time. So I'm going to put in a 34 here and then press Tab. And then a 56 Tab, 67 Tab, 65 Tab, 78 Tab, each time I push Tab it moves to the right, but it automatically moves down to the next row as needed. I'm just pressing Tab each time. So that's kind of handy at times. A different kind of list, maybe it's more vertically oriented. We can do the same kind of thing. I'm going to be putting in some entries here. But I'll be moving down the list.
Same journal idea, but instead of using Tab, here I'll use Enter. And as I get to the bottom here, you can guess what's goin'a be happening probably. I'm just typing the numbers and Enter and it automatically jumps to the top of the next column. So it certain cases that could be handy as well too. Another scenario with unrelated data could be something like this. I want to put in some serial numbers, maybe I'm starting in this cell right here. I'm starting with 1001.
I want to put in a sequence. Even before I finish typing, I could point to that fill handle in the lower right hand corner, mouse turns into a thin plus, and I'm holding down the Control key. And as I drag downward, you see the pop-up on the right. I'll let go of the mouse at some point, and we see a sequence of numbers like that. If you drag any number, no matter what it is, downward, or rightward, with the Control key held down. You automatically get next number, next number, next number.
So in certain scenarios that's going to be really handy. If you want a series, suppose I have some serial numbers here, but I want to leave room for additional entries later, I might put in 1010, and then below it 1020. The incremental difference there is 10. Highlight both of these, and this time without any key being held down, drag from the corner and we get a series of entries this way as well too. So all these different techniques, many of them unrelated to each other, are fast ways of entering certain kinds of data in a worksheet.
- Creating charts with keystroke shortcuts
- Expanding and collapsing ribbon and full-screen views
- Display shortcuts
- Efficiently navigating between workbooks and worksheets
- Selecting entire rows, columns, regions, and worksheets
- Data entry and editing shortcuts
- Rapidly creating formulas
- Operational and formatting shortcuts
- Data management techniques