Your favorite Excel "Trick"

[starl]

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.

 

[Taul]

Hi,
Alt+PrtSc (Alt + Print Screen)
Useful for capturing the active window or active userform when creating a set of instructions.

 

[FormR]

CTRL+SHIFT+L to toggle autofilter on and off.

 

[Joe4]

CTRL+SHIFT+* to select the Current Region.
Useful when wanting to select the entire Contiguous Range.

 

[Oaktree]

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]

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

    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]

Ctrl+T, Pivot Table and Power Query ==> Tables

 

[snjpverma]

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.

 

[SteveOranjinSteve]

It depends. Right now I'd Say turning Vlookup into Yes or no.

 

[DataBlake]

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