I don't like using the mouse. I guess that makes me a PC guy (as opposed to a Mac guy). I find it more efficient to keep my fingers on the keyboard at all possible times. My favorite keyboard shortcuts for moving around a spreadsheet involve moving using the control + arrow keys to zip from end-to-end in a contiguous set of cells.
If you are at the top of a contiguous list and want to get to the bottom simply hit "control + down arrow" you'll be there. Moving side-to-side is the same, just "control + arrow key" for any direction. Note that if there is a break in the continuity of the cells your cursor will stop at the break. This is how
Selecting contiguous cells works in much the same way. If you want to quickly select a contiguous group of cells then hit "control + shift + arrow key". The "shift" key is used for highlighting cells in excel and adding it to the control + arrow has you highlighting your cells. This is great for making mass changes like copying and pasting contents or formulas. You can highlight a region as well. Say you have a block of cells that is 4 columns by 100 rows that you want to copy. Just put your cursor in the top-left cell of that range and hit "control + shift + right arrow". Then, without letting go of the control and shift keys, hit the "down arrow". Viola!
Once you start using these you'll never go back to click and drag.
Favorite keyboard moves: cover a lot of territory in a spreadsheet quickly w/o using the mouse
Answers
John,
To select an entire region of contiguous cells, it's quicker & easier to use "control + asterisk". (You can use the asterisk on the alphanumeric part of the keyboard, if you're hands are already there, or you can use the asterisk on the numeric keypad, either one.) Also, you don't have to navigate to the top-left cell of the region first.
You may want to experiment some with the "End" key, as well. Using "End" in conjunction with your arrow keys, the "Home" key, and the "Page Up/Down" keys, will open up even more options for quick navigation.
- Ric
5
Ric, I love it!!! That Control+Asterisk is very cool!
I think "End + arrow" gets you the same result as "Control + arrow", or am i missing something?
John, it's the same. But I've gotten used to using the End key moreso than the Ctrl key. I think it's just a matter of personal preference when the # of keystrokes and the amount of hand-waving around are the same.
I love using keyboard short cuts in any of the applications. They are fantastic!!!
but I have to also use the SHIFT key (in Excel 2007). Still, very handy!
When building a spreadsheet with assumptions that drive the spreadsheet, I find I need to use the absolute value. While in the formula and your cursor is somewhere on reference to the cell, just hit F4 and it will put the $sign in front of the row and column reference - poof, an absolute value.
Thanks for the Control + Asterisk shortcut. That will be most helpful.
A couple of my favorite shortcut keys in excel are:
1. Ctrl + Shift + !: Changes the format of the selected cell(s) to the Number format
2. Ctrl + D: Fills Down
3. Ctrl + R: Fills Right
4. Ctrl + Z: Undo Changes
1
I use Number format all the time. This will save me big time. Thanks Jennifer!
Ctrl + Y will re-do an undone change.
Ctrl + Tab will let you jump directly to other open spreadsheets, just like Alt + Tab will let you jump directly to other open applications.
F4 is the re-do button, for your last re-doable action.
Quick way to stack words within the same cell, type first word, then hit Alt + Enter and type the second word. You get, for example:
YEARLY
REVENUE
the common
Ctrl C (to copy)
Ctrl V (to paste)
If you hit ctrl-pageup, you move to the right in your existing work book to the next available tab, ctrl-page down takes you to the left.
John, I hate to differ, but I use the mouse. No, I am not a Mac fan, after all I am an
How about…
Ctrl-Home: upper left corner cell of the spreadsheet
Ctrl-End: the most lower right cell of the populated portion of a spreadsheet
1
Right-handed but using a mouse in your left hand for efficiency's sake. Now that's innovative!
My friend and I are nerds like you guys. We get excited when we find new keyboard short cuts. One of our favorites is alt+e+s+v. This is the paste special function. I use it all the time!
1
Michael,
This is the keyboard approach to Paste Values. It's a sub-category of Paste Special keystrokes (alt+e+s). Another one is alt+e+s+t, which is Paste Format.
Although both approaches keep you from having to reach for the mouse, I have found it to be just about (almost) as easy to add the Paste Values button to the menu (using the Customize function) and reach for the mouse anyway.
(You can add the button to the menu in the old versions of Excel that didn't have the Ribbon. I have no idea how it might be done in the newer version with the Ribbon. And don't get me started on that Ribbon thing anyway. That's a whole 'nother post, and I just don't want to go there.)
Ok - everyone, please share your favorite # formats.
FIRST, MY PET PEEVE is to see (R1C1 / 1000) in cells... when people want to represent $$s in 000's...
Instead use the following custom # format (note the comma):
$#,###,;($#,###,);-
or even more exotic:
$#,###,\K;($#,###,)\K;-
Others anyone?
1
And if you want your negative figures to print in Red, try:
$#,###,;[Red](#,###,)
or
$#,###,\K;[Red]($#,###,)\K
A lot of the tips discussed here work within cells as well. (BTW, F2 is a shortcut to edit cell contents). Once you are in the cell, you can use short cuts like Ctrl+C, Crtl+V and move the cursor around in the cell with keys like End, Home, Shift to anchor, Ctrl+Tab+arrow to scroll a word at at time. You can even format contents within the cell differently (eg. some words Bold, others not).
The Hidden 'No to All' Option When Copying Files (Windows XP)
(Copied from the FENG Newsletter...)
When you copy or move a large number of files to another folder where you had previously copied some of the same files, you may get a message saying that some of the file(s) you are copying already exist in the destination folder. The choices you are given are 'Yes', 'Yes to All', 'No' and 'Cancel'. Choosing 'Yes' allows you to choose, one by one, which files to replace. Choosing 'Yes to All' copies all files and replaces all those ones with matching filenames.
Haven't you ever wished you could copy only the new files and not overwrite files already in the destination folder. If you are copying dozens, or hundreds, of files, clicking No each time would be a major pain.
Wouldn't it be nice if there was a 'No to All' option? There is....but you just can't see it. Simply hold down the Shift key when you click the No button and only files with names not already in the destination folder will be copied. All others will be ignored.
This tip is from Francis Hayes, The Excel Addict. You can find more tips that every Excel (and PC) user should know at...
http://www.TheExcelAddict.com
As
I'd like to share a few tips to make your life easier for travelling between multiple tabs in complicated Budgeting and FP&A sheets which contain several assumptions:
- Trace precedent: Ctrl + [
- Trace dependent: Ctrl + ]
- Jump back to source cell: i.e. Once you are in a precedent/dependent cell in another tab and want to jump back to where you were earlier, press F5 + Enter
This will allow you to self-review sheets quicker, deliver a product you are more familiar with and the best perk of all - go home earlier!
Best,
Ankur
Alt+; allows you to select "visible" cells. I use it a lot on filtered tables or when I've hidden rows then need to copy that new view to a new worksheet.