10 Extremely Useful Keyboard Shortcuts for Excel Power Users
I have to start with the fact that I am a big Excel fan.
After five years of using Excel daily, it still finds a way to surprise me with its features. When I started my career in auditing (public accounting), I was unaware of how much it would affect my productivity and performance.
To meet the deadlines, I had to crunch a lot of numbers in a short period. And I mean a LOT of numbers. So, finding tricks to save time became a necessity that eventually turned into passion.
Knowing what I know today, I must admit that putting Excel as a skill on my resume five years ago was a lie. TBH, from what I have learned working with colleagues and clients from various industries, I have to say the same goes for them.
So let us try to change that! Over the next few blogs, I will share some of my favorite Excel tips & tricks and best practices, and hopefully, you will find them helpful. 😊
In the world of Excel power users, using the tool without a mouse is a matter of honor. If you plan to become a power user, this first blog, dedicated to shortcuts and working using only the keyboard, is for you.
1. AUTOFIT ROW & COLUMN
This one can help you with messy export spreadsheets. Sometimes, exports from different ERP-s, for example, can look like a nightmare. Luckily for us, Excel has a super-useful feature of selecting all the data and double-clicking on the column/row headers to autofit them. But did you know the keyboard shortcut for this? It can come in pretty handy in a lot of cases.
CTRL-A - or just select a part of the spreadsheet you want to autofit
ALT-HOI - autofit row
ALT-HOA - autofit column
Autofit feature can quickly help you turn a messy spreadsheet into something more presentable
2. FAST AUTOSUMMING
In the finance world, it is all about the totals. When working in spreadsheets, you always have to sum everything up to the minimum possible numbers. In Excel, you can find the AutoSum feature in the Home and Formula tabs. This brings us to two shortcuts that are essentially doing the same thing:
ALT MUS / ALT HUS
ALT SHIFT =
If you have more than one column or row, it works for all of them simultaneously. Take a look:
Quickly sum up entire tables with the Autosum shortcut
3. ROW/ COLUMN SELECTING AND HIDING
When you want to select/hide the whole column/row faster, you can use the following shortcuts. They can come in pretty handy in horizontal or vertical analysis.
CTRL SPACE – selecting the whole column
CTRL 9 – hiding a column
CTRL SHIFT 9 – unhide a column
SHIFT SPACE – selecting the whole row
CTRL 0 – hiding a row
CTRL SHIFT 0 – unhide row
Quick row/column selection and hiding/unhiding
4. ADDING CURRENT DATE AND TIME
The current date is often used with the EOMONTH function for depreciation/ amortization calculation. I will write more about that topic in future blogs. In the meantime, if you want to quickly add the current date or exact time in the spreadsheet, use:
CTRL SHIFT ; - for date
CTRL SHIFT : - for time
Quickly add the current date and time to your spreadsheets with keyboard shortcuts
5. FLASH FILL
Flash fill is an amazing Excel feature that helps you automatically fill the values by finding a pattern based on your example. It can be faster and easier to use than the Text to Columns feature. This shortcut can be useful in a variety of different scenarios, and it is really simple:
Flash fill shortcut is a great example of Excel magic in action
There are a bunch of different formatting Add-ins for Excel, and usually, every company has some kind of approved font - color scheme mix for reporting. If you don’t have to follow strict rules in this regard, and you want to make your tables more visually appealing, try the Excel AutoFormat feature:
Select the table
Press ALT O A
Quickly apply some make-up to your tables with the Autoformat shortcut
7. GO TO SPECIAL - REMOVE BLANKS
Go To Special has a wide application. I use it to remove the blanks in data sets or fill them with the data from the previous row. As I mentioned before, sometimes exports from different ERP-s can look like a nightmare, so we can use this shortcut to take care of that in a couple of clicks:
REMOVE THE BLANKS
CTRL G and select SPECIAL (you can use TAB)
Shift cells up