EXCEL cheats megathread

jchu whats good? As a first year analyst that's been WFH I know I’m way behind a normal analyst this far into the 2yr stint, especially when it comes to excel wizardry. Everyone please paste your excel shortcuts that have been huge for you.

Edit: I'll combine everyone's posts into this thread here, also included some PPT and some google sheets (if ya nasty)

ALT + W + N. Let’s you open up a new window in the same excel workbook

Quickly moving rows in an excel sheet:

• Go to a cell in the row you want to move, select the whole row (shift + space bar) and then hit ctrl+x
• Move to a cell in the row directly below where you want to move the other row and select the whole row (shift + space bar again), then hit ctrl + shift + the +/= button (next to backspace normally)

Same concept applies for columns. Also becoming comfortable with everything you can do with paste special (formulas, values, formatting, conditional formatting, the functions to divide / multiply) is critical.

Alt W V G removing guidelines

Alt H A R (or L or C) for aligning text

Alt H O I to auto adjust columns and rows width

Ctrl Shift 1 to convert to number format

Ctrl Shift 5 to convert to %

Alt = to sum column

Shit and space bar for row highlight

Ctrl and space bar for column highlight

Alt A G G - group columns/rowa

Alt A U U - ungroup

Alt H H - cell fill

Alt H W - wrap text

Alt H MC - merge cells

Alt A T - filter

Alt H P - format as percentage (follow with Alt H 0 to add a decimal point)

Alt H N - all text formatting options

Alt H L N - new conditional formatting rule

Alt H V F - paste formulas (yes I prefer Alt H V to Alt E S)

Alt H FP - format painter

Alt H FF - change font

Alt H FS - change font size

Alt H FC - change font color
Never merge cells in excel, but for some it's the closest they get to any M&A

Type 1,000 in a random cell, copy it (Ctrl + C) and then select a range of hard coded cells before hitting Alt + H + V + S to divide / multiply when your MD wants to change it between thousands/millions/billions.

Changing the buttons on the quick access toolbar to Font Color, Fill Color, and the 4 borders. I can just hit Alt + 1,2,3,4,5,6 instead of going through Alt + HFC, or Alt HB, etc.

Alt + A + E enter enter enter = gets rid of green triangles
Alt + E + I + R = CTRL R -> but with this trick you can also go left, down or up with L / D / U
Alt + a + h (hide) and alt + a + j (show), just need to make sure you're selecting a cell or range in the grouped row / column

F2 - Edit Cell
Alt W F F for freeze panes
Shift F2 - Comments

The indirect() function comes pretty handy if you want to quote data from multiple sheets with same format

Alt + F + I + S      Saves a lot of time on dates and other series.
ctrl + space  select column
shift + space  select row
alt + h + o + i  autofit column width
alt + h + e + a  clear everything (including formatting)
alt + h + h + n  no fill
alt + h + b + a/n  set all/no borders
alt + a + t  set filter
alt + a + c  clear filter
alt + a + q  advanced filter (use it for copying unique values in a column)
ctrl + d  paste down
ctrl + r  paste right
Google Sheets has one good shortcut: ctrl + shift + v  to paste values

EOMONTH(Date, No of Months) - For dates in in the future 

Alt + E + S = Paste special
Alt + A + V + V = Create Data Validation List
Control + shift + 7 for borders
Control + shift + - to get rid of borders
F4 to lock and unlock cells in formulas
F12 to save as
Control + ~ to see all formulas (again to go back)
Definitely a fan of control [ and ] to trace references and then f5 to go back
Alt F Z E A to send your current workbook as an email (make sure you save it first!)
Alt F T to open the options menu (generally to turn off data tables/allow circular references
Also, note that in most menus there are underlined words, if you hold alt and hit the underlined letter it will go to that option in the menu and select/unselect it.

Ppt:

ALT H G A M - align middle

Alt H G A C - align center

Alt H G G - group items

the align function can become Alt + 2 + _ for any align option.

Control + G to group

Control + shift + G to ungroup