Dennis demonstrates how to enter the same data in multiple cells simultaneously and how to use AutoComplete to reduce typing.
- [Instructor] Because so many mistakes occur when typing let's eliminate some of those by making data entry simpler and easier. In Column A we've got a list here on this worksheet it's called Multiple Cell and Auto Entries. Some of these city names will repeat as you've got a sheet of paper in front of you with a list of the various cities here, eventually we'll have some ID numbers associated with them, but in these first two cells right here, and also down in row six. How do we highlight these ahead of time? If we can do that, then we can make a single entry and cover all these repeating entries.
So, if you hold down the Control key, I'm going to click cell A6 and also imagine that sheet of paper I'm looking at, same city is going to be in rows 11 and 12, so again the Control key is being held down as I highlight these cells. All these are for the city of Chicago, so I'll type Chicago. But, rather than typing that and then copying it to the other locations, I'll simply press not Enter but Control Enter. This saves me some time and eliminates that drudgery of repeating and that's where often mistakes occur when we're typing.
Same thing could be true with numbers, too. In these two cells right here, and now using the Control key in this cell and in these two down here even though we don't have the city names in just yet, these are all going to be the value of 75 so I'll type 75, and once again, not press Enter but Control Enter. So making multiple entries in different cells at the same time saves time, is more accurate, and in the long run eliminates some of that drudgery of typing. And in a similar situation over here in our data, starting in Column D, recently a number of these Engineering and Maintenance people have been offered full-time jobs.
This person here, a contract person, so using the Control key, I'm highlighting the cells here of those people who have just been offered full-time jobs and have accepted, they've been hourly and contract people and half-time people, for all these cells here, I want to see the phrase "full-time." So they're selected, I'll type in full-time F-U-L-L space T-I-M-E the way its entered in all of these, and rather than pressing Enter, once again, Control Enter and all of those are changed at the same time.
Now, we can also do this with formulas. I want to total here all those cells above, I also want to total in columns V through Z, so I've selected these cells, let go of the left mouse button, using the Control key, highlight these. And rather than writing one formula and copying it to different locations, here I'm going to simply press the shortcut for Auto Sum. Many of you know that on the home tab, there's a button on the far right side called Auto Sum, in the formulas tab it's over on the left side. We could either press that button, or we can use the keystroke shortcut Alt Equal.
There's more about Auto Sum in a later movie in this course, but Alt Equal or the Auto Sum button, either way gives us our results here quickly. Now what if instead of that, what if we wanted to do an average? We could type in average, or what if we wanted to do a median, we could type in median, too. Now, I do want sum here, but then I change my mind: I want median. The active cell is still in V8, so I'll type equal median. This time, because there's no keystroke shortcut for it, I'll act as if I'm only doing the median for these cells right here.
But once again, I will not press Enter, I will press Control Enter. And that saves the time of copying a single formula to different locations. There's also a feature built into Excel called Auto Complete. In column AB, I'm going to be tracking various salesperson's results, I haven't put in the other headings just yet, those might be for different months or different days of the week, something like that, but this salesperson is named Perez, and the next one is Jones. Now the way we're tracking the data here, we will be having repeat entries.
We've got eight or ten salespersons in our group, we don't want to be retyping these names all the time. The next person's name is Johnson, soon as I type the J though, look what happens. I ignore what I'm seeing and it starts to type Johnson, that's fine, Enter. Now I want to put in Sanders, different name. Let's say the next one is going to be Perez, I see the letter P, I'll simply type that, press Enter, I don't have to type in the whole name, and you could imagine these names being a lot longer, too. I want to put in Johnson now, but type J, nothing happens, O, nothing happens, H, there we are, we see Johnson, Enter and so on.
This only works in columns, it only works with text, but it's really handy. Another way to get to these entries is to right click here, and go to pick from dropdown list. Notice, I didn't do anything special to create this list, but there's Perez and so on. And as this list grows, you might, at some point, decide, well maybe this isn't that efficient, but it saves us typing, we're not making typing mistakes as we do this. Another option here, too, is to press Alt Down Arrow. That activates the list, we can use it with our arrow keys and come to the appropriate person there.
And of course, from time to time, we might be adding new names and they could be longer names than this too, and they might even be full names, but again, you want some consistency there in how you type in those names. Bit it's an easy feature to get to. If somehow this doesn't work, you'll need to go to the File tab in the ribbon, go down to Options, then choose Advanced, and right here, "Enable auto complete for cell values." If this is unchecked, that's the reason why the feature's not working. So, it's automatically there unless you've turned it off and it's a handy feature to have.
It only works in columns, as I suggested, and only with text. So, prior to this, we saw a technique for entering a formula in multiple cells at the same time, also putting in data as we did in column I, in multiple cells at the same time, and also the entries in columns A and B. The idea of saving time, increasing accuracy and eliminating errors by simply making similar entries all at once.
Dennis begins with the basics—how to display data so that errors can be easily spotted. Next, he offers handy tips to ensure data is entered correctly the first time, using the AutoFill feature and using AutoCorrect shortcut codes for frequently used entries. Dennis provides easy ways to validate your data, which is particularly helpful when multiple team members are contributing to the same spreadsheet. This includes restricting the data that can appear in a spreadsheet by setting value limits, pre-populating data with drop-down lists, and other methods. He also shows how to avoid mistakes in formulas, how to hide data that doesn't need to be seen, and how to use workbook protection to prevent errors, and more.
- Streamlining data entry steps
- Using Excel shortcuts and automation features
- Protecting worksheets and workbooks
- Validating data
- Basing entries on formulas
- Detecting errors in formulas
- Using Range Names
- Finding mistakes in large, complex spreadsheets