Your favorite Excel "Trick"

starl

Administrator
Joined
Aug 16, 2002
Messages
5,941
Office Version
365, 2019
Platform
Windows
Keep it short & sweet, but what's your favorite Excel "trick" - it can be a keyboard shortcut, function, tool, etc. If you could show someone one thing in Excel - what would it be?

For example, when I'm training a client on Filters, I show them how they can use CTRL+; (semicolon) to select only the visible cells in a selection.

It's ok if your trick is another way of doing the same thing someone else posted. But since I'm thinking of making this a useful sticky, please no discussions. If you need to, post a new thread linking to the post and I can edit the originating post if needed.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
656
Office Version
2013, 2010
Platform
Windows
Hi,
Alt+PrtSc (Alt + Print Screen)
Useful for capturing the active window or active userform when creating a set of instructions.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
CTRL+SHIFT+* to select the Current Region.
Useful when wanting to select the entire Contiguous Range.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,943
The three that usually turn some heads when I teach Excel are:

1) CTRL + {

Particularly if you have selected a cell with a link to another file in its formula. So long as the other file is listed first in the formula, that keyboard shortcut will open the other file for you without having to edit links or (worse) having to trace a long folder path in the File --> Open window.

2) SUMPRODUCT's awesomeness when SUMIF (or SUMIFS) fails.

3) Data tables for scenario modeling. Understanding that a simple data table can quickly and easily show you the outputs of your best case/most likely/worst case scenarios without having to (gasp!) copy/paste the results for each is very useful!
 

starl

Administrator
Joined
Aug 16, 2002
Messages
5,941
Office Version
365, 2019
Platform
Windows
VBA trick: If you have a sub that's calling other subs (or functions) or have class modules with properties setup, you can use Shift+F2 to jump to that piece of code.
For example, let's say in my main sub, I have
VBA Code:
    If Not cFunctions.ConvertToTable(RunAdvancedFilter, NewTable) Then
        'do nothing, not a big deal for here
    End If
If I place my cursor in ConvertToTable and press Shift+F2, I'll jump right to the ConvertToTable. A quick way of navigating around lots of code.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,660
Ctrl+T, Pivot Table and Power Query ==> Tables
 

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,237
Office Version
2016
Platform
Windows
If a column has data as well as blanks and you want to select the entire range from the top most cell to the last cell with data in a column.
In such scenarios it is not possible to select the use ctrl + shift + downarrow due to the blank cells in between. Example: the below screenshot.

Trick:
No matter on which row your cursor is, just press Ctrl + space (to select the entire column), keeping the Ctrl button pressed, leave the space and press period/fullstop (.) (this will activate the first cell in the range)
This will select the entire column and your cursor will move to A1. Press Ctrl + shift + uparrow and you have the selection as below.



1584551164369.png
 

Attachments

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
751
Office Version
2016
Platform
Windows
Trick:
No matter on which row your cursor is, just press Ctrl + space (to select the entire column)
I'd like to add onto this that you can also press shift + space to select the entire row
and then you can combine the two (ctrl + shift + space) to get really wild and select a region of data
 

Forum statistics

Threads
1,089,220
Messages
5,406,929
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top