- Finance
Finance TopicsFinanceOperationsFinancial Planning & AnalysisEquity FinancingDebt FinancingM&A and Joint VenturesCorporate GovernanceStock OptionsMarketingTaxMoreFinance News
Switzerland's second-largest bank, Credit Suisse Group AG, recently announced that it plans to cut 109 - Accounting
Accounting TopicsAccountingAccounting ConceptsAccounting StandardsFinancial StatementsAuditRevenue RecognitionAccounting RegulationsAccounting Close ProcessAccounting Policies & ProcessesTechnical AccountingAccounting News
The financial crisis of 2008 considerably altered debate over economic growth and prosperity. Now, it appears that the events of the last ... - Treasury
Treasury TopicsTreasuryElectronic PaymentsWorking Capital ManagementCurrency & Foreign Exchange (FX)Treasury Services ManagementShort Term InvestmentsTreasury News
In response to the increased use of mobile banking - Technology
Technology News
The United States government will soon begin deploying customized Android OS-powered mobile devices to federal agency and military ... - Careers
Careers News
The chief financial officer of Hibbett Sports, a sporting goods retailer, recently announced that he plans to retire on June 1, and the company ... - Directory & Reviews
- Expert Advice and Guidance
- Exclusive Tips and Tools
- Latest News and Info
- Networking with Peers
- Noise-Free and Professional
- It's all FREE
Excel questions and tips
- Go to Group home page
Group Type: Open- Posts in this group are public
Events
-
Feb-08 - WebinarUnlocking the Value of Dynamic Financial Planning
-
Feb-16 - WebinarCurrent and Emerging Cloud Trends
Browse Our Library of White Papers
Browse our extensive library of free white papers focusing on the latest financial, technology and business issues.
Subscribe to our newsletter
Favorite keyboard moves: cover a lot of territory in a spreadsheet quickly w/o using the mouse
Posted Mon, 01/25/2010 - 11:32am by John Kogan (CEO/CFO)
Details
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.
- Excel
- 1315 reads
© 2012 Proformative, Inc.
Comments
Favorite keyboard moves
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
2 out of 2 members found this useful
Ric, I love it!!! That
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?
Ctrl vs. End
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.
Keyboard short cuts
I love using keyboard short cuts in any of the applications. They are fantastic!!!
1 out of 1 members found this useful
I love it too...
but I have to also use the SHIFT key (in Excel 2007). Still, very handy!
Absolute Value
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.
Shortcuts
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 out of 1 members found this useful
I use Number format all the
I use Number format all the time. This will save me big time. Thanks Jennifer!
More
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.
Another...
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
don't forget...
the common
Ctrl C (to copy)
Ctrl V (to paste)
key board shortcut to move between tabs
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.
Left-Handed Mouse User
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
1 out of 1 members found this useful
Right-handed but using a
Right-handed but using a mouse in your left hand for efficiency's sake. Now that's innovative!
Love this post
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!
alt+e+s+v
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.)
Special # formats...
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 out of 1 members found this useful
Special # formats.....
And if you want your negative figures to print in Red, try:
$#,###,;[Red](#,###,)
or
$#,###,\K;[Red]($#,###,)\K
Editing Within Cells
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).
Not Excel but still an incredible shortcut
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
For complicated Budgeting and FP&A sheets
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
1 out of 1 members found this useful
Select visible cells
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.