From the course: Excel 2016: Tips and Tricks

Enter data or formulas in nonadjacent cells simultaneously

From the course: Excel 2016: Tips and Tricks

Start my 1-month free trial

Enter data or formulas in nonadjacent cells simultaneously

“

- [Voiceover] Although, it's easy to copy and paste data, and it certainly eliminates a lot of drudgery associated with data entry, there are techniques for entering data in multiple locations quickly, and sometimes it's in non-adjacent cells. Imagine I've got a sheet of paper with a list of sales across different cities here, and as I look at the list, I see that in these cells here, we need to put in a city name, maybe it's going to be Boulder, and also farther down the list, though I could certainly type it once, copy it to the other locations, but if we use the Control key to select those locations first, and each time you have to let go of the left mouse button as you do this, then type in the name, but don't press Enter, press Control+Enter, and sometimes you've got a series here. Now, we could enter the name here, maybe it's going to be Boston, it's going to be in these four cells. But, why not just highlight these ahead of time, type Boston, Control+Enter. This works with numbers. It doesn't have to be in the same column, doesn't have to be in the same row, or anything like that. We want to clean up the formatting here, probably make all that bold or not bold. If it looks as if the two entries right here are going to be 50, and there's one over here that's going to be 50, so I've highlighted these, let go of the left mouse button. With the Control key, highlight this cell, and there's another cell over here that's going to have the number 50 as well, too. So, each time we're letting go of the left mouse button, pointing to a different location, and clicking. Type in 50, press Control+Enter. So, we can do this with numbers. We can do this with text. We can also do it with formulas. I'd like to know the median of these numbers right here, as well as all the others, and also the ones over here. Now, they happen to be in the same row, but even that's not a requirement. Now, it certainly would be unusual to see these in different rows. But, I'll bring this down just to make a point. What we're really about to do in each of these cells here is tabulate totals from the six cells above, and we might want to do the same thing over here, and if we do, let's highlight these, and it makes no difference which group we highlight first. Maybe, I'll highlight these first. Drag across, let go of the left mouse button, and now with the Control key, highlight these. I'm going to type the function that I need as if I'm only concerned with cell B8. So, I'll type =median. I want to know the median of these numbers. Left parenthesis. I'll highlight these cells right here. If I were to press Enter, I'd have the answer only for column B. But, if I press Control+Enter, I'm going to get a median for every one of these columns here, for those cells that are highlighted. So, over here for example, double clicking here, we see what's going on. Same thing here. Easy to setup. Of course, it would have been more logical and more sensible for the data to be up here. We could certainly move it there. But, had the data looked like this from the beginning, of course, we could have done the same sort of thing we did when the data was rearranged irregularly. So, any time we're putting in functions, or trying to do the same relative kind of thing, or in the previous examples where we're trying to put in data in multiple locations, but not adjacent, we use the Control key to select the areas first, we type the entry; whether it's text, or number, or formula, and we don't press Enter. we press Control+Enter. That's a really time saving tool for data entry.

Contents