Home

"I wish you'd shown me this years ago!" A short discussion on some Excel Shortcuts

%3CLINGO-SUB%20id%3D%22lingo-sub-315086%22%20slang%3D%22en-US%22%3E%22I%20wish%20you'd%20shown%20me%20this%20years%20ago!%22%20A%20short%20discussion%20on%20some%20Excel%20Shortcuts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-315086%22%20slang%3D%22en-US%22%3E%3CP%3E%22I%20wish%20you'd%20shown%20me%20this%20years%20ago!%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20phrase%20I've%20probably%20heard%20more%20than%20any%20other%20when%20I%20teach%20Excel.%20I've%20run%20a%20few%20classes%20at%20my%20workplace%2C%20and%20when%20I%20explain%20some%20of%20the%20keyboard%20shortcuts%20that%20I%20use%2C%20people%20are%20often%20impressed%2C%20and%20realise%20how%20much%20time%20they%20could%20have%20saved.%20Things%20like%20holding%20down%20Ctrl%20%2B%20Shift%20and%20using%20the%20arrow%20keys%20to%20navigate%20rather%20than%20scrolling%20with%20the%20mouse%2C%20or%20the%20Alt-key%20sequences%20(like%20Alt%2C%20A%2C%20T%20to%20enable%20the%20quick%20filter)%20are%20unknown%20to%20many%20that%20I've%20encountered.%20I%20thought%20it%20would%20be%20helpful%20to%20share%20some%20of%20them%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20most%20used%2C%20as%20I%20mentioned%20above%2C%20are%20navigating%20by%20using%20Ctrl%20%2B%20arrow%20keys%2C%20and%20Alt%2C%20A%2C%20T%20to%20enable%20filters%20on%20a%20table.%20I%20frequently%20need%20to%20create%20pivot%20tables%20in%20my%20documents%20too%2C%20and%20so%20Alt%2C%20N%2C%20S%2C%20Z%2C%20Enter%20is%20another%20favourite.%20Plus%2C%20if%20the%20data%20table%20is%20laid%20out%20nicely%20with%20no%20empty%20rows%20and%20no%20empty%20headers%2C%20you%20don't%20even%20need%20to%20select%20the%20whole%20table%20first%20-%20just%20have%20any%20cell%20in%20the%20table%20selected.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECtrl%20%2B%20A%20to%20select%20all%20cells%20is%20helpful%2C%20and%20I've%20been%20surprised%20at%20how%20many%20people%20weren't%20aware%20of%20that%20one.%20Pressing%20it%20once%20will%20expand%20the%20selection%20to%20the%20%22borders%22%20of%20the%20group%2C%20i.e.%2C%20it%20will%20stop%20at%20empty%20rows%20and%20columns%2C%20and%20then%20pressing%20it%20a%20second%20time%20will%20expand%20the%20selection%20to%20the%20entire%20sheet.%3C%2FP%3E%3CP%3EI%20need%20to%20do%20a%20lot%20of%20data%20exploration%20and%20%22story%20telling%22%2C%20which%20involves%20creating%20lots%20of%20similar%20pivot%20tables%20from%20the%20same%20data%2C%20so%20I%20like%20copying%20sheets.%20The%20quickest%20way%20to%20do%20that%20is%20to%20hold%20down%20Ctrl%2C%20then%20click%20and%20drag%20on%20the%20sheet%20name.%20That%20even%20work%20to%20copy%20a%20sheet%20to%20another%20workbook%20which%20has%20been%20nice%20when%20I'm%20collating%20data%20from%20a%20few%20reports.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20use%20conditional%20formatting%20a%20lot%20-%20columns%20of%20numbers%20coloured%20red%20to%20green%20(which%20I%20think%20is%20a%20bad%20scale%2C%20by%20the%20way%2C%20since%20it's%20difficult%20for%20red-green%20colour-blind%20people%2C%20so%20I%20prefer%20blue-red).%20Navigate%20to%20the%20top%20of%20the%20columns%2C%20press%20Ctrl%20%2B%20Shift%20%2B%20Down%2C%20then%20hit%20Alt%2C%20H%2C%20L%2C%20S%2C%20Enter.%20That'll%20please%20the%20boss.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20could%20go%20into%20a%20lot%20more%20detail%20but%20these%20are%20the%20main%20ones%20I%20find%20myself%20using.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20features%2Fshortcuts%20have%20you%20learnt%20that%20save%20you%20heaps%20time%2C%20or%20you've%20shown%20someone%20and%20they've%20been%20very%20impressed%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-315086%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320115%22%20slang%3D%22en-US%22%3ERe%3A%20%22I%20wish%20you'd%20shown%20me%20this%20years%20ago!%22%20A%20short%20discussion%20on%20some%20Excel%20Shortcuts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320115%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3EUsing%20Excel%2C%20the%20thing%20you%20want%20to%20do%20may%20be%20done%20in%20several%20different%20ways.%20I've%20found%20that%20those%20things%20which%20might%20feel%20like%20a%20shortcut%20to%20one%20person%20may%20not%20be%20to%20another.%20For%20example%2C%20your%20tip%3CBR%20%2F%3E%22or%20the%20Alt-key%20sequences%20(like%20Alt%2C%20A%2C%20T%20to%20enable%20the%20quick%20filter)%22%3CBR%20%2F%3EWhich%20involves%20pressing%20the%20keys%20separately%2C%20might%20be%20replaced%20with%20(Shift%2BCtrl%2BL)%20which%20can%20be%20in%20completed%20simultaneously.%3CBR%20%2F%3EAlso%2C%20your%20tip%20%22I%20frequently%20need%20to%20create%20pivot%20tables%20in%20my%20documents%20too%2C%20and%20so%20Alt%2C%20N%2C%20S%2C%20Z%2C%20Enter%20is%20another%20favourite.%22%20could%20be%20shortened%20to%20(Alt%20%2B%20D%2C%20P).%20But%20it's%20always%20good%20to%20share%20tip%20and%20one%20of%20my%20favourites%20is%20selecting%20a%20date%20in%20a%20cell%2C%20moving%20the%20mouse%20pointer%20over%20the%20Fill%20Handle%20(the%20black%20square%20in%20the%20bottom%20right%20hand%20corner%20of%20that%20cell)%20and%20using%20the%20Right%20Hand%20mouse%20button%20to%20drag%20down.%20A%20menu%20box%20pops%20up%20showing%20a%20lot%20more%20options%20like%2C%20fill%20without%20weekends%20etc.%3CBR%20%2F%3EI've%20always%20told%20my%20students%20to%20try%20doing%20what%20they%20already%20know%20like%2C%20moving%20the%20tab%20but%20try%20holding%20the%20Alt%2C%20Ctrl%2C%20Shift%20%2B%20Alt%2C%20Shift%20%2B%20Ctrl%2C%20or%20Shift%20%2B%20Ctrl%20%2B%20Alt.%20It's%20amazing%20the%20things%20that%20are%20buried%20deep%20in%20Excel.%20%3B-)%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
DylanP
Occasional Visitor

"I wish you'd shown me this years ago!"

 

This is the phrase I've probably heard more than any other when I teach Excel. I've run a few classes at my workplace, and when I explain some of the keyboard shortcuts that I use, people are often impressed, and realise how much time they could have saved. Things like holding down Ctrl + Shift and using the arrow keys to navigate rather than scrolling with the mouse, or the Alt-key sequences (like Alt, A, T to enable the quick filter) are unknown to many that I've encountered. I thought it would be helpful to share some of them here.

 

The most used, as I mentioned above, are navigating by using Ctrl + arrow keys, and Alt, A, T to enable filters on a table. I frequently need to create pivot tables in my documents too, and so Alt, N, S, Z, Enter is another favourite. Plus, if the data table is laid out nicely with no empty rows and no empty headers, you don't even need to select the whole table first - just have any cell in the table selected.

 

Ctrl + A to select all cells is helpful, and I've been surprised at how many people weren't aware of that one. Pressing it once will expand the selection to the "borders" of the group, i.e., it will stop at empty rows and columns, and then pressing it a second time will expand the selection to the entire sheet.

I need to do a lot of data exploration and "story telling", which involves creating lots of similar pivot tables from the same data, so I like copying sheets. The quickest way to do that is to hold down Ctrl, then click and drag on the sheet name. That even work to copy a sheet to another workbook which has been nice when I'm collating data from a few reports.

 

We use conditional formatting a lot - columns of numbers coloured red to green (which I think is a bad scale, by the way, since it's difficult for red-green colour-blind people, so I prefer blue-red). Navigate to the top of the columns, press Ctrl + Shift + Down, then hit Alt, H, L, S, Enter. That'll please the boss.

 

I could go into a lot more detail but these are the main ones I find myself using.

 

What features/shortcuts have you learnt that save you heaps time, or you've shown someone and they've been very impressed?

1 Reply
Hi,
Using Excel, the thing you want to do may be done in several different ways. I've found that those things which might feel like a shortcut to one person may not be to another. For example, your tip
"or the Alt-key sequences (like Alt, A, T to enable the quick filter)"
Which involves pressing the keys separately, might be replaced with (Shift+Ctrl+L) which can be in completed simultaneously.
Also, your tip "I frequently need to create pivot tables in my documents too, and so Alt, N, S, Z, Enter is another favourite." could be shortened to (Alt + D, P). But it's always good to share tip and one of my favourites is selecting a date in a cell, moving the mouse pointer over the Fill Handle (the black square in the bottom right hand corner of that cell) and using the Right Hand mouse button to drag down. A menu box pops up showing a lot more options like, fill without weekends etc.
I've always told my students to try doing what they already know like, moving the tab but try holding the Alt, Ctrl, Shift + Alt, Shift + Ctrl, or Shift + Ctrl + Alt. It's amazing the things that are buried deep in Excel. ;-)

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies