more-arw search

Q&A Forum

Favorite keyboard moves: cover a lot of territory in a spreadsheet quickly w/o using the mouse

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 Excel makes this work in the first place. The cursor moves until it sees a change in state of open to filled or filled to open.

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.

Answers

Ric Roper
Title: Accountant
Company: In-between
(Accountant, In-between) |

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

John Kogan
Title: CEO/CFO
Company: Proformative, Inc.
(CEO/CFO, Proformative, Inc.) |

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?

Ric Roper
Title: Accountant
Company: In-between
(Accountant, In-between) |

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.

Topic Expert
Deborah Godfrey
Title: Budget Administrator, Business Manager, ..
Company: Seeking Employment
(Budget Administrator, Business Manager, Project/Program Manager, Seeking Employment) |

I love using keyboard short cuts in any of the applications. They are fantastic!!!

Mark Von Der Linn
Title: Principal
Company: www.VDLconsulting.com
(Principal, www.VDLconsulting.com) |

but I have to also use the SHIFT key (in Excel 2007). Still, very handy!

Ken Kaufman
Title: CFO
Company: Community Dental Partners
LinkedIn Profile
(CFO, Community Dental Partners) |

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.

Jennifer Eversole
Title: Partner & Knowledge Enthusiast
Company: Management Stack, LLC
LinkedIn Profile
(Partner & Knowledge Enthusiast, Management Stack, LLC) |

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

John Kogan
Title: CEO/CFO
Company: Proformative, Inc.
(CEO/CFO, Proformative, Inc.) |

I use Number format all the time. This will save me big time. Thanks Jennifer!

Ric Roper
Title: Accountant
Company: In-between
(Accountant, In-between) |

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.

Shane Patrick Connolly
Title: Chief of Staff
Company: Council District 10, City of San Jose
(Chief of Staff, Council District 10, City of San Jose) |

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

Ed Rusk
Title: CFO
Company: Chattem Chemicals, Inc.
(CFO, Chattem Chemicals, Inc.) |

the common
Ctrl C (to copy)
Ctrl V (to paste)

David Weinstein
Title: Corporate Controller
Company: Simclar, Inc.
(Corporate Controller, Simclar, Inc.) |

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.

Terry Delahay
Title: Accountant
Company: Formacoat, LLC
(Accountant, Formacoat, LLC) |

John, I hate to differ, but I use the mouse. No, I am not a Mac fan, after all I am an accountant (read cheap)! However, when I first got my first mouse back in 1989, I trained my left hand to use the mouse. This allowed my right hand to do what it was made to do… the ten key. This was before Windows came into use. Back in the DOS days, the cursor did not move from cell to cell as you entered data unless you created a macro. Remember Lotus 123… Quattro Pro maybe? Freeing my right hand allowed me to enter a number and I would move the cell pointer with the mouse using my left hand. It was great! Now I use the slider bars on the bottom and right hand side of the screen to get me to where I need to go. There have been some great keystrokes mentioned above though. Keep ‘em coming!

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

John Kogan
Title: CEO/CFO
Company: Proformative, Inc.
(CEO/CFO, Proformative, Inc.) |

Right-handed but using a mouse in your left hand for efficiency's sake. Now that's innovative!

Michael Mosbauer
Title: Derivatives Specialist II
Company: BNY Mellon
(Derivatives Specialist II, BNY Mellon) |

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!

Ric Roper
Title: Accountant
Company: In-between
(Accountant, In-between) |

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.)

Keith Taylor
Title: CFO
Company: Lyris, Inc.
(CFO, Lyris, Inc.) |

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?

Jeffrey Levine
Title: Controller
Company: T.G.I.C. Importers, Inc.
(Controller, T.G.I.C. Importers, Inc.) |

And if you want your negative figures to print in Red, try:
$#,###,;[Red](#,###,)
or
$#,###,\K;[Red]($#,###,)\K

Mark Von Der Linn
Title: Principal
Company: www.VDLconsulting.com
(Principal, www.VDLconsulting.com) |

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).

Keith Taylor
Title: CFO
Company: Lyris, Inc.
(CFO, Lyris, Inc.) |

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

Ankur Kulkarni
Title: CFO
Company: Cochlear South Asia
(CFO, Cochlear South Asia) |

As CFO, I've had the fortune of writing annual financial plans and pitchbooks for Private Equity investors, containing upto 40 tabs sometimes! That trained me to make better use of excel than ever!
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

Topic Expert
Shannon Mathews
Title: Controller
Company: Aldrich Services LLP
(Controller, Aldrich Services LLP) |

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.

3104 views
Products and Companies

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.