flevyblog

Flevy Blog is an online business magazine covering Business Strategies, Business Theories, & Business Stories.
MANAGEMENT & LEADERSHIP STRATEGY, MARKETING, SALES OPERATIONS & SUPPLY CHAIN ORGANIZATION & CHANGE IT/MIS Other

Tips from a Consultant: Some Not-so-Obvious Excel Shortcuts

There are 3 things that separate the struggling Excel monkey from the seasoned one.

  • Keyboard Shortcuts.  Knowing your keyboard shortcuts will dramatically accelerate the speed and efficiency of your work.  It also makes you look like a real pro when your colleagues see you jump from cell to cell, sheet to sheet without ever touching the mouse.
  • Pivot Tables and Formulas.  
  • Visual Basic Programming.  A little bit of VB programming knowledge goes a long way. VB can be used to automate calculations, which, in many cases, becomes necessary, as the model becomes too bulky to “calculate” within a bearable amount of time.

This article will discuss the first group–shortcuts.  I’ve compiled a short list of “not-so-obvious” Excel shortcuts.  These are also the shortcuts I find myself using most often.

Navigation Shortcuts

Ctrl + arrow This is the shortcut I use most frequent. Hold down “CTRL” and hit the arrow key to jump to the edge of the next data region (in the direction of the arrow key). If you’re unfamiliar with this shortcut already, test it out. It’s a bit difficult to articulate in words.
Ctrl + Shift + arrow Achieves the above, while highlighting your selection. This is most often used in conjunction with other shortcuts (e.g. preceded with Copy and proceeded with a Paste).
Ctrl + Page Up
Ctrl + Page Down
Jump to the previous sheet (to the left).
Jump to the next sheet (to the right).
Ctrl + Home Jump to the top-left cell of the sheet. In most cases, this is “A1.” Exceptions include if rows/columns are hidden or the sheet has frozen panes.

Custom Quick Access Shortcuts

One of the greatest improvements in Excel 2007+ is the ability to customize the Quick Access Toolbar. The Quick Access Toolbar refers to the section at the top-left corner of the Excel interface (see screenshot below). You can customize this toolbar by right clicking on it and selecting “Customize Quick Access Toolbar…” From there, you can add, remove, and re-order shortcuts. I’ve only added 2 shortcuts here.

excel_quickaccess

formatpainter Alt + 1 This is the “Format Painter” shortcut.
I often use this shortcut in conjunction with “Ctrl + Shift + down arrow” to extend the formatting of a cell down the column.
pastevalues Alt + 2 This is the “Paste Values” shortcut.
Prior to this trick, accessing the “Paste Values” function was a fairly cumbersome process.
pasteformulas Alt + 3 This is the “Paste Formulas and Number Formatting” shortcut.
This functions allows you to copy and paste everything, except the cell formatting (e.g. borders, background color, font color).  This is useful if you already have your spreadsheet nicely formatted and just wanted to overwrite or extend some existing formulas.

Random Shortcuts

Ctrl + click sheet name + drag tab Many times, you will need to duplicate a full sheet. The easiest way to do this is to hold down “Ctrl,” click the sheet name (i.e. click the tab), then drag it.
Ctrl + click sheet names + make edits Sometimes, you may want to make the same changes across multiple spreadsheets. Prior to knowing this method, I would replicate the change across the multiple sheets manually.  With this shortcut, you can make changes across the sheets simultaneously.  Once you have the multiple sheets selected (note the color of the tab will lighten), pick any one of these sheets and make your edits.
Alt + H, M, C
(Letters pressed consecutively)
Merge and center selected cells.
Alt + H, O, R Rename sheet.

Use a shortcut that’s missing from our list?  Please share in the comments section below.

A great way to learn Excel–including pivot table usages, advanced formula nesting, and VB programming–is by studying robust models that have been built by other business professionals.  You can find Excel models of all shapes and sizes on Flevy here.  Here are a few examples:

For a comprehensive list of Excel shortcuts, check out this page: http://shortcutworld.com/en/win/Excel_2010.html.  Also, if you are an avid user of PowerPoint, read my list of Not-so-Obvious PowerPoint Shortcuts.

About David Tang

David Tang is an entrepreneur and management consultant. His current focus is Flevy, the marketplace for premium business documents (e.g. business frameworks, presentation templates, financial models). Prior to Flevy, David worked as a management consultant for 8 years. His consulting experience spans corporate strategy, marketing, operations, change management, and IT; both domestic and international (EMEA + APAC). Industries served include Media & Entertainment, Telecommunications, Consumer Products/Retail, High-Tech, Life Sciences, and Business Services. You can connect with David here on LinkedIn.

, , ,




  • Aniljain123

    Shift+Ctrl+% sign (or ’5′ on your keyboard) applies % sign to the value in the cell. Very handy command

  • MichaelMcBain

    Small error in your handy article. Ctrl-Home takes you to the top *left* hand cell, which you correctly identify as A1,not the top right

    Two shortcuts I use a lot are Ctrl-space for selecting a whole column and Shift-space to select a whole row.

    • David Tang

      Ah, great catch, Michael! Just made the correction.

      Also, thanks for the share.

Complimentary Business Training Guides


Many companies develop robust strategies, but struggle with operationalizing their strategies into implementable steps. This presentation from flevy introduces 12 powerful business frameworks spanning both Strategy Development and Strategy Execution. [Learn more]   What does it take to achieve Operational Excellence? Read this guide and learn to build an integrated Business Execution System from Six Sigma Master Black Belt Frank Adler, co-founder of Operational Excellence Consulting. [Learn more]
The Fiaccabrino Selection Process (FSP) is a proven methodology for increasing sales and reducing turnover, developed by "Mr. Roche," Charles Fiaccabrino. Mr. Fiaccabrino is a Sales Executive with over 50 years of experience. [Learn more]   Learn this time-tested approach to Change Management authored by Ron Leeman, recipient of the Change Leader award by the World HRD Congress in 2012. Ron Leeman has been Change, Process and Project professional since 1974. [Learn more]
Recent Articles by Corporate Function

  

  

  

  

  


The Flevy Business Blog (http://flevy.com/blog) is a leading source of information on business strategies, business theories, and business stories. Most articles have been contributed for management consultants and industry executives with over 20 years of experience. If you would like to contribute an article, please email our editor David Tang at [email protected].

Flevy (http://flevy.com) is the marketplace for premium business documents, such as management frameworks, presentation templates, and financial models. Our documents are of the same caliber produced by top tier consulting firms, like McKinsey, Bain, Accenture, BCG, and Deloitte. Learn more about Flevy here.


Connect with Flevy:

   
  


About Flevy.com   /   Terms   /   Privacy Policy
© 2015. Flevy LLC. All Rights Reserved.