It seems like every office job needs at least passable spreadsheet skills. And, in an increasingly competitive job market, passing isn’t enough anymore. People who regularly need the software to get their work done need to become power users. That means going beyond simple tricks on web apps like Google Sheets and on to advanced techniques in the best spreadsheet application out there: Microsoft Excel. There are so many things you can learn with Excel that it would take hours or days to learn and weeks to master. But, to start you off, here are some of the best of the best Excel shortcuts that will impress prospective and actual employers, both in the form of keyboard commands and practical advice for getting the most out of the Microsoft program.
1. Use shortcuts to quickly format values
Ever need to change the format of a number or, more to the point, a set of numbers? By using “Ctrl + Shift + !” you make the numbers in the selected cells display two decimal points. Meanwhile, “Ctrl + Shift + $” adds a dollar sign and “Ctrl + Shift + %” adds a percentage sign. Those tricks have the potential to save you a huge amount of time, if used effectively.
2. Generate random values with RAND
Sometimes when using a spreadsheet you need a random number to use as a sample, often when calculated odds and percentages. And I mean entirely random, which something you picked yourself wouldn’t be. By entering “RAND() a number between 0 and 1 which no one could guess will be generated. But be warned: new values are generated every time the workbook recalculates.
3. Jump from worksheet to worksheet
A simple one a lot of people don’t know. Go from one worksheet to another immediately with either the command “Ctrl + PgDn” or the command “Ctrl + PgUp”.
4. Double click to copy down
Instead of holding and dragging the mouse down to copy a formula or value for your data set, you can just double click the box at the bottom right-hand corner of the cell.
5. Lock cells with F4
There are some numbers that you always need to stay the same, no matter what else changes with your spreadsheet. To make sure those key values aren’t accidentally changed, click on the cells you want to remain constant and hit the F4 key. If you continue hitting F4 you’ll get more options. Those are locking the cell, locking the row number, locking the collar column letter, and removing the lock.
6. Don’t overly obsess over Excel shortcuts
The last of the Excel shortcuts is, ironically enough, to stop using so many shortcuts. Keyboard shortcuts, specifically. They can be great timesavers, for sure, but it’s common for an Excel user to want to execute a specific action but not know the shortcut for it. They’ll then waste a substantial amount of time searching for how to do it on the internet when their time would probably be better served doing it the old-fashioned way, cell by cell. If you search for a random Excel shortcut in the middle of working on a sheet, there’s no way you’re going to remember it the next time the opportunity comes up to make use of it. The better strategy is to dedicate some time to a manual or article like this one that spotlights keyboard shortcuts. By testing out the Excel shortcuts as you read about them, they’re more likely to stick in your brain then when you’re doing a one-off action. A popular problem with life hacks is to spend so much time life hacking that you actual waste it overall. Don’t let that happen to you.
O’Keefe, Matt. “Excellent Excel Shortcuts That Very Few People Know” Lifehack June 2017