# Your favorite Excel "Trick"



## starl (Mar 12, 2020)

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 (Mar 13, 2020)

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


----------



## FormR (Mar 16, 2020)

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


----------



## Joe4 (Mar 16, 2020)

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


----------



## Oaktree (Mar 17, 2020)

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 (Mar 17, 2020)

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 (Mar 17, 2020)

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


----------



## snjpverma (Mar 18, 2020)

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 (Mar 18, 2020)

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


----------



## DataBlake (Mar 19, 2020)

snjpverma said:


> 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


----------



## My Aswer Is This (Mar 23, 2020)

Here is a trick you may not know about:
Tired of making Drop down Lists.

A trick I use is:
Enter a value in C1 Like "Alpha"
Then if you Enter "Allen" in "C2"
And "Allice in "C3"

And now you want "Alpha" in "C4" just Press The alt key then down arrow key. Release alt Key and select the value from the shown list and presto you have the value you selected in the active cell.
I'm using Excel 2013


----------



## starl (Mar 23, 2020)

My Aswer Is This said:


> I'm using Excel 2013


Still works in Office 365


----------



## danielcarrington (Mar 28, 2020)

Taul said:


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



Probably one of the best tricks ever, not only in Excel but in all programs.

Also Ctrl + Arrow or Ctrl + Shift + End combos for quick selections.


----------



## Sialoquent (Mar 28, 2020)

Ctrl+T  for me.


----------



## Faysal Farooqui (Mar 28, 2020)

CTRL + D
Copy the above cell


----------



## MARK858 (Mar 29, 2020)

Faysal Farooqui said:


> CTRL + D
> Copy the above cell


You should also like CTRL + R as well then, copy cell to the right.


----------



## Faysal Farooqui (Mar 29, 2020)

That is also true, but I have to use Ctrl+D in most of my tasks, due to manual data entry.


----------



## snjpverma (Apr 1, 2020)

One of my favorite tricks while working with Pivot Tables. 
Select the cells that you want to deselect from filter and press Ctrl + minus.
Your filter has been applied.  

In the below screenshot, I wanted to filter out Bellen and Carlota from my Pivot. So I selected them and pressed Ctrl +minus sign.





Output Screenshot, after pressing the keys. As highlighted, we can see that the filter has been applied and the two selected cells are no more visible (Deselected from filter).


----------



## Zack Barresse (Apr 9, 2020)

Soooo many to choose from. The tricks I use are generally keyboard shortcuts. Some of my most used are:

*Worksheet*
CTRL+T : create Table
SHIFT+Spacebar : Select entire row (or Table row)
CTRL+Spacebar : Select entire column (or Table column

*VBA*
SHIFT+F2 : Goto Definition (like Starl said)
CTRL+SHIFT+F2 : Go back to last position
CTRL+F9 : Set next executed line (without stepping)


----------



## h2o2sail (Apr 10, 2020)

CTRL + d or CTRL + r to fill down or fill right without using the mouse.  Simple but mostly unused by those trying to fill cells with the same formula.


----------



## CephasOz (Apr 11, 2020)

Ctrl + ; for the current date, and Ctrl + : for the current time.   (Also Ctrl + D and Ctrl + R.)   I had never before come across the Shift + F2 that @starl mentioned - so useful!


----------



## ashakantasharma (May 30, 2020)

Pressing ALT for accessing all features without a mouse.


----------



## Leandroarb (Jun 3, 2020)

Hi everybody!!!

Array functions and loops!


----------



## B___P (Jun 9, 2020)

/ to have access to ribbon...


----------

