Your favorite Excel "Trick"

starl

Administrator
Joined
Aug 16, 2002
Messages
6,081
Office Version
  1. 365
Platform
  1. 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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi,
Alt+PrtSc (Alt + Print Screen)
Useful for capturing the active window or active userform when creating a set of instructions.
 
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!
 
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.
 
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

  • 1584550983823.png
    1584550983823.png
    1.9 KB · Views: 23
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,212,929
Messages
6,110,741
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top