Join Dennis Taylor for an in-depth discussion in this video Entering data or formulas in non-adjacent cells simultaneously, part of Excel 2010 Power Shortcuts.
- View Offline
Excel is filled with tons of great shortcuts and one of my favorites is one that allows you to enter the same data in multiple locations at the same time. In Column R suppose we're gathering sales or contact data from different cities. It so happens that some of these city entries might be repeating. So maybe in these two cells here, but not the next two, and then maybe the one down here. So I am about to hold down my Ctrl key as I select some other cells. And I am looking at my sheet of paper here that has the city name that's reappearing here multiple times.
It's going to be for example Denver. I'm going to type Denver. Now as I type this it looks as if it's only going into one location, but I am going to press Ctrl+Enter, and the same information goes in all of those. It's a lot simpler than typing in the entry once and then trying to copy it to different locations. So we can select non-contiguous cells. Type an entry, press Ctrl+Enter, and whatever we've typed goes into all of those cells. It works with text. It works with values. Maybe as I am looking at the data here or maybe this cell right here, and now holding down the Ctrl key, these two cells here and this one and this one are going to have the value 100, so I'll just type in 100. Press Ctrl+Enter.
And this feature also works with formulas. I might want to put in some totals here, as well as over here. We can highlight these in no particular order. Maybe I'll highlight this first. Oh! Then it occurs to me, I want to put the same kind of total in columns B through F. So holding down the Ctrl key here I'll highlight these cells as well. And I am about to type a function that's going to tabulate information just for column B. I might do Sum or Average. Maybe I'll do a median, =median, the middle value. So I am typing this as if I am only doing this for the current cells, highlight this data and normally I would press Enter if I were doing this for only one cell, but I am going to press Ctrl+ Enter, and the same relative formula goes with all of these cells.
There is a median there, there is a median there, and surely over here as well. This can be a great time-saver whenever you are either entering data or making corrections. Also, you'd realize there is a mistake here. This 52 should have been a 50. It so happens there is a number over here that should have been a 50 also. Maybe it's this one right there, so, Ctrl+Click. So those two cells are highlighted. We are going to type 50 and press Ctrl+Enter. This is a great time-saver. I use it often. I think you might also.
- Creating charts with keyboard shortcuts
- Converting formulas to values by dragging
- Repeating Undo and Redo actions
- Displaying formulas instantly
- Navigating quickly through worksheets and across workbooks
- Formatting numeric, and date/time data in a flash with keystroke shortcuts
- Inserting dates or times instantly
- Grouping rows or columns to create collapsible regions
- Building data-entry shortcuts with Auto-fill
- Displaying unique items from large lists