In a previous article I showed how it was possible to use the keyboard and mouse together as this can lead to increased productivity. And who in today’s world of work doesn’t want to be more productive? In this article series I am going to expand on ways to use the keyboard and mouse together for enhanced productivity.
Series: Excel Keyboard and Mouse Combos
Select cells that are not neighbors (CTRL + Click)
Sometimes you need to select cells that are not next to each other. For example you might want to selectively copy and paste some values from one column to another. Say you have a list of names and companies, and decide you want to pick three records from this list but they are not next to each other (see the picture below: Sara Hall, Peyton Mason and Gabriella Parker).
You want to copy and paste these to another worksheet. You could do this one record at a time – select the 1st record, copy, switch worksheet, paste, switch back to original worksheet, select the 2nd record, copy, switch worksheet, paste etc. Or you could hold the Control key while you select all three records using the mouse.
Once you make your selection you can then paste it to the other worksheet. Excel will bring all the pasted items together so they appear on adjacent rows. You can also copy columns of data, instead of rows like I did in the picture above.
BUT Excel does not allow you to copy multiple selections that are not lined up, so if you want to select non-neighboring cells to copy, make sure that they line up (i.e. if you are selecting rows then make sure they all start in the same first column, and they end in the same second column – OR – if you are selecting columns they all start in the same first row, and they end in the same second row).
Select cells that are neighbors (SHIFT + Click)
Use the SHIFT key to select cells that are neighbors. You can select neighboring cells by clicking on a cell in one corner of your selection, press and hold the SHIFT key, then click on the cell in the opposite corner of your selection. This means you don’t need to drag the mouse while holding down the left mouse button, which is the other way to make a selection of neighboring cells with the mouse.
On a day to day basis I don’t ever use this combo because I find it easier to click on a cell and drag the mouse to make my selection, but on the other hand you may find that you prefer not to drag the mouse when holding down a button (e.g. maybe it’s better for ergonomic reasons). So I’ve included this section on SHIFT + Click for completeness.
– Bonus Tip: How to quickly select lots of cells in an Excel worksheet –
As we’re looking at how to use the SHIFT key to select cells, I will share a trick to quickly select lots of neighboring cells using the SHIFT key that doesn’t involve the mouse at all. Say you want to select all the cells from A1:H10000 (that’s ten thousand). And you want to fill down formulas that are in row 1 all the way down to row 10,000. This is what you do:
- Click on cell A1 to select the first cell
- Press F5 or CTRL+G to bring up the “Go To” menu
- Type H10000 into the box titled “Reference”
- Press and hold SHIFT, then press Enter
Excel will highlight all the cells in the range A1:H10000, and you can then copy the formulas from row 1 all the way down to row 10,000 by using the shortcut CTRL + D (or selecting from the menu e.g. in Excel 2010 Home→Editing→Fill→Down). Now you could also enter the full range in the Reference box, but I wanted to show you that Excel is versatile and offers more than one way to do things. To enter the full range in the Reference box this is what you do:
- Press F5 or CTRL+G to bring up the “Go To” menu
- Type “A1:H10000” into the box titled “Reference”
- Press Enter
Note: Neighbors vs. Contiguous
Excel documentation uses that fancy technical term “contiguous” to describe cells that are next to each other. It’s derived from the Latin word “contiguus” meaning “bordering upon”. I did a little Google search which returned this:
I personally like the term “neighbor” because in general humans are social beings and calling something a neighbor sounds friendlier than saying it’s contiguous. But in case I ever lapse into Excel speak and start to use the term “contiguous” you will know that I mean neighboring.
Research Pane (ALT + Click)
You can bring up the Office research pane by pressing ALT and clicking on a cell. This is one of those features that I find more of a nuisance than a help as I’m sometimes accidentally ALT + clicking when I don’t mean to. Just like the SHIFT + Click combo, I don’t use the ALT + Click combo on a daily basis.
So what happens when you ALT + Click on a cell? Excel takes the contents of the cell and looks it up. You can choose to look up words in a dictionary, thesaurus, translator and encyclopaedia. But when the internet is just a browser click away it’s no big deal to do all this online.
If you can’t read what’s in the above picture because the words are too small, click on it to download the full-size version that is 789kb in size.
I clicked on the word “Genesis” to see what the Research Pane would bring up. “Genesis – 1st book of Bible, a book of the bible in which the creation of the world is described…” At the end of creation, God had finished the work he had been doing; so on the seventh day he rested from all his work. Well, we’ve come to the end of Part 1 in this series, so I’m going to allow you to rest from all your reading work. And if you haven’t tried any of the above tips in Excel I’d recommend that you do this soon.
What’s coming up in Part 2 of this series?
In part 2 I’ll show you how to move cell selections using the mouse, with neat tricks for inserting or copying selections using a keyboard and mouse combo. By the end of this series you’re going to use the keyboard and mouse in super fast combos that will lift your productivity to new heights (well, that may be a slight exaggeration, but the tips will certainly help you do stuff faster).