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

WSO Elite Modeling Package

  • 6 courses to mastery: Excel, Financial Statement, LBO, M&A, Valuation and DCF
  • Elite instructors from top BB investment banks and private equity megafunds
  • Includes Company DB + Video Library Access (1 year)

Comments (90)

  • Prospect in IB-M&A
Mar 10, 2021 - 9:22am

Obviously the OGs

Ctrl + w: close tab

Ctrl + shift + w: close window of tabs

Alt + tab: window selector

Ctrl + n: new window

  • Associate 1 in PE - LBOs
Mar 10, 2021 - 10:22am

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 

  • Associate 1 in PE - LBOs
Mar 10, 2021 - 4:26pm

Out of curiosity, why do you prefer that? I've noticed from analysts / associates I know, most prefer that method but ctrl+alt+v seems more efficient given its one keystroke? If I remember correctly some people who would now be more senior tend to prefer the other method as I think ctrl+alt+v wasn't an option in earlier versions of excel

Learn More

300+ video lessons across 6 modeling courses taught by elite practitioners at the top investment banks and private equity funds -- Excel Modeling -- Financial Statement Modeling -- M&A Modeling -- LBO Modeling -- DCF and Valuation Modeling -- ALL INCLUDED + 2 Huge Bonuses.

Learn more
Mar 10, 2021 - 6:01pm

Alt+E+S gives you a range of specials to choose from such as Alt+E+S+C for comments, Alt E+S+W for column width and more so generally more convenient

Laborare Pugnare Parati Sumus

Mar 10, 2021 - 4:16pm

Few more to add to the above:

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

Mar 10, 2021 - 4:51pm

Couple of ones I find useful

Excel:

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)

Ppt:

ALT H G A M - align middle

Alt H G A C - align centre

Alt H G G - group items

Both:

Alt H FP - format painter

Alt H FF - change font

Alt H FS - change font size

Alt H FC - change font colour

  • Associate 1 in PE - LBOs
Mar 10, 2021 - 6:26pm

Two thoughts:

  • In Excel, for the column / row grouping you can also hit shift+alt+left arrow (group) and shift+alt+right arrow (ungroup)
  • In PPT, adding the drop down for align to your quick access toolbar saves some key strokes (e.g, alt + [number based on position on quick access toolbar] + c for align center). Same thing applies for font color, font size, shape / text box size (height, width)
Mar 10, 2021 - 6:26pm

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.

  • 4
Mar 11, 2021 - 9:59pm

brownline9

Alt + A + E enter enter enter = gets rid of green triangles

Does this really work? Just went to my excel and it doesn't seem to work for green triangles from errors.  

This will be helpful especially when you're doing a copy/paste special a table from excel into ppt. the MD doesn't want to see any potential for error (i.e. green triangles). happened to me more than once. 

Array
  • Analyst 1 in PE - Other
Mar 13, 2021 - 12:14am

Alt F T F B Enter will remove them for the whole sheet

  • Analyst 1 in IB-M&A
Mar 10, 2021 - 6:55pm

Add things to your quick access toolbar if the shortcuts are long. For example in PPT, the align function can become Alt + 2 + _ for any align option. 

  • Prospect in IB-M&A
Mar 10, 2021 - 9:34pm

F2 - Edit Cell

Shift F2 - Comments

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

Mar 10, 2021 - 11:30pm

Just a suggestion OP: You should add all the suggestions to one central list at the top (in your post).

I will contribute something I recently learned - Alt W F F for freeze panes :)

Mar 11, 2021 - 1:47am

My current favorite is :

Alt + F + I + S

Saves a lot of time on dates and other series.

The Quick Toolbar is probably the most important for any excel/ppt user. Use this for common yet multi-step formatting shortcuts such as Borders, Number formats, Cell Colors. The best is probably Cell Coloring + Number Formatting + Border Formating + Cell Conditionals macro. It saves me hours everyday.

Mar 11, 2021 - 4:20am

The ones I use every day

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

Array

Mar 11, 2021 - 9:49am

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

PowerPoint

Control + G to group

Control + shift + G to ungroup

Put aligning objects, inserting shapes , inserting text boxes , inserting arrows in your shortcuts (alt 1, alt 2 etc.)

Mar 12, 2021 - 9:08am

BigShmoo

Get Macabacus - it's an absolute must-have and I can't see my life without it.

Helpful indeed, was working with an analyst in a small boutique who was a huge fan of it. If you go in a BB however, you'll have something similar for ppt developed in-house so not worth in this case.

Laborare Pugnare Parati Sumus

Mar 11, 2021 - 1:04pm

Thought I'd drop this in from our Excel Modeling Course for your enjoyment:

<script src="https://fast.wistia.com/embed/medias/wy70q1dmps.jsonp" async></script><script src="https://fast.wistia.com/assets/external/E-v1.js" async></script><div class="wistia_responsive_padding" style="padding:56.25% 0 0 0;position:relative;"><div class="wistia_responsive_wrapper" style="height:100%;left:0;position:absolute;top:0;width:100%;"><div class="wistia_embed wistia_async_wy70q1dmps videoFoam=true" style="height:100%;position:relative;width:100%"><div class="wistia_swatch" style="height:100%;left:0;opacity:0;overflow:hidden;position:absolute;top:0;transition:opacity 200ms;width:100%;"><img src="https://fast.wistia.com/embed/medias/wy70q1dmps/swatch" style="filter:blur(5px);height:100%;object-fit:contain;width:100%;" alt="" aria-hidden="true" onload="this.parentNode.style.opacity=1;" /></div></div></div></div>

Mar 11, 2021 - 9:26pm

Centering Text (basic, but better than merging cells):

Start with the leftmost cell of the range you want to center text across. Enter your text. 

Ctrl+Enter to enter the text and remain on the same cell.

Hold shift and right-arrow over the range you want to select.

Ctrl+1 to open formatting menu.

Right arrow over to "Alignment".

Tab down to the Horizontal dropdown under Text Alignment and select Center across selection (pressing "c" twice will get you there).

Press enter and enjoy never merging cells again!

Mar 13, 2021 - 8:11am

Great list. Some additional shortcuts that come to mind are:

Shift + Alt + Right/Left Arrow (to group/ungroup rows)

Shift + Alt + Down/Up Arrow (to group/ungroup columns)

Shift + F10 (acts as a right click)

Mar 13, 2021 - 1:53pm

"Shit and space bar for row highlight" 

I wouldn't recommend this...

Array
  • 1
Mar 13, 2021 - 4:10pm

F4 repeats your last action, so any long ALT+xyz string can be instantly recreated in new highlighted ranges by pressing F4

Loud and clear.

  • 1
Mar 15, 2021 - 10:20am

Alt+= to auto sum

Alt+ctrl+V paste special 

Shift+ > to highlight rows

F4 to repeat the last action performed 

Ctrl+ pg up,down to navigate through worksheets

Alt+H+FC font colour

Ctrl+U to underline 

Windows + E to open files

Windows, tab ,downward arrow to shut down my laptop without using my mouse 

Alt+H+ FS for font size

F2 / fn +2 on a laptop   to open a cell/ edit 

Alt+ file tab ,using downward arrow to navigate to save,save as etc

Ctrl+A to highlight everything then alt + H + AL to align to the left 

Shift+arrows to highlight specific words ,then Ctrl+B to Bolden, Ctrl+U to underline, Ctrl+I to italize, alt + H+ AC to align to the center if it's a heading 

Thank you very much wall street Oasis, I feel really proud of myself. Not only can I apply these skills in excel but they also work in word and even PPT.

I'm looking forward to more especially after I graduate. 

Start Discussion

Total Avg Compensation

July 2021 Investment Banking

  • Director/MD (9) $911
  • Vice President (36) $363
  • Associates (209) $232
  • 2nd Year Analyst (120) $152
  • 3rd+ Year Analyst (28) $146
  • Intern/Summer Associate (101) $144
  • 1st Year Analyst (442) $133
  • Intern/Summer Analyst (357) $82