The Eight Most Impactful Excel Shortcuts That You Should Master

by Matthew Kuo on July 26, 2015

in Excel, Excel Shortcuts

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the most popular Excel books on Amazon
 

fast-fingers-1529553-640x480

If you’ve ever gone online to research improving your Excel skills, you’ve undoubtedly come across a post or two listing all of Excel’s keyboard shortcuts.  In the latest version of Excel, Microsoft has made it easier than ever to learn shortcuts, by assigning shortcuts to nearly every function and making the discovery of the input sequence very transparent.

While memorizing Excel shortcuts will generally improve your productivity, not all shortcuts are created equal.  Shortcuts that you never use are not inherently not very useful and not worth memorizing.  Your focus should be on the shortcuts that have the most impact – either by the amount of time it saves you, the frequency that you’ll use them, or the behavior it encourages.

If you’ve already started using Excel or just haven’t utilized shortcuts heavily before, review the top eight shortcuts below.  For any that you don’t know already, I would suggest memorizing them and incorporating them into your Excel working style – I guarantee that doing so will provide an immediate boost to your productivity.

Sorting Tables

ALT → A → S → A    (Sort Ascending)

ALT → A → S → D    (Sort Descending)

Sort Shortcut

Knowing how to sort tables quickly is an extremely useful shortcut because of how often you’ll use it.  Sorting is generally the first task you’ll perform to orient yourself with data and easily see if there are duplicates within the field you just sorted.  Additionally, any time before you finalize a table to be presented to others, you’ll likely want it to have some type of sorting logic applied to at least one field.

AutoFilter

ALT → A → T

    or

CTRL + SHIFT + L

AutoFilter Shortcut

The AutoFilter shortcut is a powerful shortcut because, like sorting, you’ll use it frequently whenever you need to conduct data analysis.  With AutoFilter on, you can quickly limit your view of the data based on a set of filtering criteria.  Knowing this shortcut is also useful when you inherit a workbook and need to quickly remove AutoFilter, as leaving it on can easily cause errors.  Just type in the same shortcut sequence to remove the AutoFilter labels.

Inserting and Deleting Rows and Columns

ALT → I → R    (Insert Row)

ALT → I → C    (Insert Column)

ALT → H → D → R    (Delete Row)

ALT → H → D → C    (Delete Column)

Columns Rows Shortcut

As with any spreadsheet tool, Excel’s interface is intended to be extremely flexible, allowing you to place data, charts, tables, and even objects any way you like on the spreadsheet itself.  Because of this flexibility, one thing you’ll be doing often is inserting and deleting rows.  There have been countless times when I’d built a spreadsheet, realized that I needed either more or less space within the sheet, and manually performed the action by right clicking the mouse.  Had I been using these shortcuts all along, I would have saved myself a ton of time.

Inserting a PivotTable

ALT D P F

    or

ALT N V ENTER

PivotTable Shortcut

PivotTable is another great analysis tool that is sometimes underutilized either because people don’t know about the feature or they get confused by the steps required to insert a PivotTable.  Using this shortcut allows you to insert a PivotTable the quickest way possible.  By having this shortcut in hand, you’ll undoubtedly start using PivotTables more frequently and eventually become more proficient with the tool itself.  To prevent errors, just make sure that you are selecting a proper table (all headers filled with heading values, no random breaks of rows or columns) before you start the shortcut sequence.

Formatting Numbers

ALT → H → N → A → ENTER    (Accounting format)

ALT → H → K    (Comma separated format)

CTRL + SHIFT + 4    (Currency format)

CTRL + SHIFT + 5    (Percentage format)

CTRL + SHIFT + 1    (Two Decimal Place format)

Number Formatting Shortcut

If making your Excel files visually appealing is even slightly important to you, then changing the format of number in Excel is something you’ll do constantly.  Using shortcuts is a way to make this process much more efficient.  There are a couple of other number formatting shortcuts available in Excel, but I’ve listed the ones you’ll likely use most frequently.

Note in the examples shown above that the first three formats add a small indent to the right side of the cell while the last two do not.  This is one difference between the comma separated and two decimal place format.

Inserting Comments

ALT → R → C

Comments Shortcut

I’ve stated before that not including documentation is one of the biggest mistakes people make both when creating and reviewing Excel files.  Always put yourself in the shoes of the person you may be handing off your file to and think about what information would be important to that person.  The comments shortcut allows you to add valuable context to your file without interfering with any of the data elements.  By knowing the shortcut combination, you’ll start to add comments more frequently and the workbooks you build will become more transparent.

Workbook Navigation

CTRL + PAGEDOWN    (Select next tab)

CTRL + PAGEUP    (Select previous tab)

Tab Navigation Shortcut

During my consulting career, there have been countless times when I’ve been I’ve been tasked with reviewing a client’s Excel workbook to prepare for a meeting.  Because of the nature of my work, these workbooks would often have a ton of tabs.  It was important for me to be able to get through the workbook quickly, and doing so with shortcut keys was always faster than using the mouse.

Cell Selection

CTRL + DIRECTIONAL KEYS    (Move to end of filled in values)

CTRL + SHIFT + 8    (Select entire table based on current cell selection)

SHIFT + SPACE    (Select entire row)

CTRL + SPACE    (Select entire column)

Cell Selection Shortcut

Similar to moving between tabs, cell selection, or moving around within the tab itself, is also a very important skill to have.  Being able to navigate and select cells quickly will improve both your efficiency at Excel and your confidence in using the tool.  Additionally, if you’re ever put in a situation where your colleagues are watching you use Excel, it’s a great idea to have these shortcuts handy if you need to impress them.

Coming up with just eight Excel shortcuts is difficult because there are so many good ones out there that were left off of this list (like ALT → E → S → V → ENTER).  What are your favorite, most impactful, Excel shortcuts?  Let me know in the comments section.

{ 14 comments… read them below or add one }

Roger Chen July 26, 2015 at 11:51 pm

I didn’t know some of these, Matt. Thanks!

Reply

Matthew Kuo July 27, 2015 at 2:02 am

No problem, Roger. Thanks for reading!

Reply

David Yang July 27, 2015 at 5:36 am

Picked up a couple new ones, nice!

Reply

Matthew Kuo August 2, 2015 at 2:14 pm

Thanks for reading! 😉

Reply

MF July 28, 2015 at 6:38 pm

When we talk about shortcuts in Excel, we often think of Ctrl+ or Alt+ key combination. Actually the Menu key is also handy for certain shortcut.
Here’s my sharing:
http://wmfexcel.com/2015/04/25/keyboard-shortcut-tip-filter-by-selection-with-menu-key/

Reply

Ted July 30, 2015 at 4:40 am

Great read! I would like to add some, if you don’t mind.

AutoFilter off (Clear filter):
ALT -> A -> C

Selecting entire row
SHIFT + SPACE
Deleting row (if an entire row has been selected)
CTRL + MINUS SIGN on keypad
Adding row (if an entire row has been selected)
CTRL + PLUS SIGN on keypad
Selecting entire column
CTRL + SPACE
Adding/Deleting columns goes the same has for rows, select entire column and CTRL +/-
Selecting entire worksheet
CTRL + SHIFT + SPACE

Renaming worksheet
ALT -> H -> O -> R

I hope these help!

Reply

shivam April 20, 2016 at 1:09 am

of curse! you are absolutely right!!!!!!!!

Reply

Ted July 30, 2015 at 4:44 am

Also, if you assign functions to the Quick Access Toolbar you can use any function of Excel with ALT + a number.

For example, I have assigned Paste Values on the 4th spot and Paste Formatting on the 5th spot. So if I want to copy only the values and formatting of a data set, I just copy it and paste with ALT -> 4 -> 5.

Reply

Henric Andersen August 10, 2015 at 9:48 pm

Hi Matt

Thanks for your good ideas! 🙂

May I ask you how you have made the small animated videos? Very nice and useful!

Kind regards
Henric

Reply

Matthew Kuo August 16, 2015 at 2:01 pm

Thanks Henric,

I used ScreenToGif. It was a simple, lightweight and free gif making program that allowed you to add captioning. In the future, I might go for something more robust, but for now here’s the link.

https://screentogif.codeplex.com/

Reply

wzr August 20, 2015 at 11:41 pm

Shift + F2 to edit comments is much quicker.

Reply

ChandraShekhar March 15, 2016 at 9:20 am

I have a table of 9 columns . Want to return a value of column 6 with matching 3 attributes that are there in first three columns? Can I use INDEX MATCH MATCH MATCH MATCH

Reply

shivam April 20, 2016 at 1:07 am

thanks you so much for this shortcut keys!!!!

& pls help to learn the marco in easy way??

Reply

satyadev sharma October 24, 2016 at 12:12 am

Thanks u so much sir for this shortcut keys

Reply

Leave a Comment

Previous post:

Next post: