Your favorite Excel "Trick"

starl

Administrator
Joined
Aug 16, 2002
Messages
5,949
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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
666
Office Version
2019, 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.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,971
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,949
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
5,761
Ctrl+T, Pivot Table and Power Query ==> Tables
 

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,383
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
779
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,187
Messages
5,485,255
Members
407,490
Latest member
leogaleleo84

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top