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&AType 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 / columnF2 - Edit Cell
Alt W F F for freeze panes
Shift F2 - CommentsThe 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 valuesEOMONTH(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
ALT E S = God Mode in MW2
Obviously the OGs
Ctrl + w: close tab
Ctrl + shift + w: close window of tabs
Alt + tab: window selector
Ctrl + n: new window
Quickly moving rows in an excel sheet:
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
https://s3.amazonaws.com/wsp_sample_file/Excel-shortcuts-2016-Windows-M…
However, I prefer: alt e s t for paste special, instead of using ctrl+alt+v++
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
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
I find alt E S more tactile —- personal preference
Alt ES is easier to do one handed, that’s why I prefer it over Ctrl Shift V
Alt E S only requires your left hand. Means right hand can stay on your ten key or mouse. Just less overall movement leads to fewer unintentional formula busts.
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
Mobile app contributing to that formatting btw
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
Two thoughts:
Agree with both points - my muscle memory/autism meant when I tried those it slowed me down but if you're coming fresh definitely do the above.
Never merge cells in excel
It's the closest I get to any M&A activity though
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.
This is the real pro-move here, allows to do some really commonly used actions quickly
Bumping the thread. Great work op, SA this summer and I’ve been hoping for something like this
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.
Alt F T F B Enter will remove them for the whole sheet
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.
Alt + E + I + R = CTRL R -> but with this trick you can also go left, down or up with L / D / U
Not a shortcut but one for nerds like me - is there a way to show/hide groupped rows/column with your keyboard only?
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
The quick access toolbar (alt+ numbers) for both excel and PPT is massively underrated.
Good thread
F2 - Edit Cell
Shift F2 - Comments
The indirect() function comes pretty handy if you want to quote data from multiple sheets with same format
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 :)
bump
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.
Alt + F4
Best command. Easily the most useful out of the lot.
came here to share this useful tip as well
bump
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
EOMONTH(Date, No of Months) - For dates in in the future
Alt + E + S = Paste special
Alt + A + V + V = Create Data Validation List
ctrl + ] for going to a linked cell, hit F5 + enter for going back to the original cell
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.)
Ctrl + Alt + Left Arrow
Ppt / Word: Alt + shift + up/down moves entire bullets up or down
Holy shit thank you
Get Macabacus - it’s an absolute must-have and I can’t see my life without it.
Is it worth the cost?
It's ~10/month per user, which yeah, it's worth it, but your company will generally pay for it too.
Did you mean macbook by any chance bud?
Nah he meant Macabacus, and he's not wrong about how handy it is
https://macabacus.com/
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.
We have it as default at PJT and I love it - incredibly powerful and pre-loaded with hotkeys/quick formatting to firm standard. Also has a tracing mechanism that is much better than factory settings, as well as easy-output/chart refresh for PPT slides.
However I would recommend against getting it unless you are going to a firm that uses it because you will memorize the macabacus shortcuts that will be useless if you're not using macabacus.
Helpful info, thanks!
So glad I saw this post
So helpful, thanks!
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>
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.
Ctrl + d (carries formula down)
Ctrl +r (carries formula right)
Alt+h+o+I (sit adjust column width)
Very helpful, thank you
follow
Bump
Holy shit
Lol
wow clutch - printing and posting on my wall lol
Bump
Bump
highlight row/column with cltr + space / shift + space respectively, then cltr + shift + plus to add a row/column
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!
I swear this forum taught me more than my undergraduate
Following
Open custom # formats: Ctrl + 1 + C + C
love this
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)
"Shit and space bar for row highlight"
I wouldn't recommend this...
Just learned more than my school could ever teach me.
F4 repeats your last action, so any long ALT+xyz string can be instantly recreated in new highlighted ranges by pressing F4
That's super useful
This is super helpful. Thank you!
Helpful!
Windows + D
Back to desktop
Following. Very useful tips! Hopefully there would be some for those using Macs as well!
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.
Bookmark
These are all great - another useful hack I haven't seen yet is:
ALT + H + O + M = copy a sheet from one Excel workbook to another Excel workbook
Then in the new Excel workbook using ALT + A + K you can either (i) kill the external references to the original workbook, which automatically replaces them with hardcodes or (ii) change the external references source from the original workbook to the new workbook. This (ii) is a huge time saver if the old and new workbooks are different versions of the same file (assuming the # of rows and columns didn't change between versions).
.
Shift+F11
insert a new sheet
The one I haven't seen yet is adding "document location" to your quick access toolbar. That gives you the file path of the document you're working in.
If you're sharing an excel (or ppt) with a reviewer, you can quick access the document location (quick access, then CTRL C), copy it in your email via a hyperlink (highlight text for your hyperlink and CTRL K, then CTRL V), and that let's you share the master version seamlessly.
hit F5, then Alt+s, then O, then X, then G, then E, and press enter. This will select all of the hardcoded aka data entry (i.e. non-formula) cells.
All of the highlighted cells should be blue (hardcodes). If you see something highlighted and it's not blue, look at it. If you see something blue, and it's not highlighted, look at it.
This is also the really quick way to turn an all-black font sheet into a properly formatted sheet.
Ut aut sed labore iste blanditiis omnis. Qui labore temporibus facilis et quisquam autem est. Sunt quae perspiciatis nulla et quae porro nostrum. Suscipit voluptatem sint totam necessitatibus.
Ut voluptatibus est corrupti enim odio et. Qui necessitatibus et corrupti ut vel. Distinctio quia incidunt nam impedit.
Quisquam tenetur voluptatem sint iure temporibus perspiciatis soluta. Laborum corrupti vitae et. Dicta ut voluptas aperiam minus nostrum hic repellendus. Tempore et eos expedita voluptatem officia molestiae cum.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...
Mollitia voluptas similique molestiae deleniti ab explicabo. Aliquid architecto in est eum fuga reprehenderit. Cum dolores quasi quisquam sit.
Delectus et quis sapiente neque nihil voluptate perspiciatis doloremque. Qui et quia porro nesciunt aut deleniti.
Velit est sed eius odio. Aliquid et consequatur et optio exercitationem quam. Quam modi ut vitae dolores quia rerum et. Quis magni rerum debitis ut quibusdam voluptatem dolorem. Rerum odio est non quis numquam optio suscipit velit.
Ea reprehenderit voluptatibus odio perferendis. Distinctio eveniet dignissimos perferendis voluptate ut nulla mollitia. Earum omnis quasi voluptate culpa nisi tenetur. Molestiae saepe doloremque nihil est. Sed et sint sint omnis.
Tempore cumque doloremque voluptatum corporis beatae voluptas distinctio. Voluptatem eius est quo voluptas. Vel qui ullam aut dolorum eius.
Sunt molestias dolor necessitatibus nam tempora reiciendis sit rerum. Et inventore labore est omnis expedita et rerum. Saepe quidem veniam eum dolore alias maiores facere. Praesentium accusantium est dolores molestias magni incidunt cum. Est ad quis fugiat quas. Iste dolorum et non et veritatis repellendus ut. Debitis labore eos dolor voluptate.
Non dolorem eum cumque veritatis qui sit quia. Ullam odio aut ducimus vero quidem perferendis reiciendis accusamus. Ipsam sint sit autem sapiente molestiae. Assumenda consequatur atque aut.
Eveniet non temporibus culpa qui odit. Nisi voluptatem illum consequatur dolores illum. Temporibus excepturi cupiditate temporibus nihil consequuntur vel.
Fugiat veritatis perferendis voluptas et qui veniam sit. Ut ipsa rerum illum velit illum quae. Incidunt quae inventore autem error id autem qui tempora. Quisquam harum dolores ipsam dolorem voluptatem. Ipsum corrupti nobis totam blanditiis. Esse est non sunt sit unde.
Corrupti pariatur eligendi iusto. Asperiores consectetur sed dolores sit a voluptas. Omnis quo perferendis sed consequatur. Ipsam quisquam alias facere ea et a quam. Alias est corporis totam atque numquam non laudantium.
Qui beatae velit sit mollitia harum consequatur accusantium. Ut reiciendis veniam dolorem et. Ipsa dolorem deleniti blanditiis quia placeat voluptates. Sint omnis qui corrupti maiores iste laudantium. Cum aliquam facilis deleniti sit maxime eaque eum. Aut quisquam ab quisquam ut minus dolor dolor.
Et est ipsum et ut. Et cupiditate sapiente voluptas sed. Adipisci praesentium officia nihil similique. Et sunt expedita ad et et minus. Voluptatem repellendus exercitationem ratione sequi minima tenetur. Exercitationem totam ut officia eos itaque. Similique nisi magni aperiam fugit voluptate sed dolor.
Voluptas consequatur et facilis tenetur perspiciatis consequatur. Consequatur qui sit ducimus eum.