• Lucija Krušić

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:


CTRL E



Flash fill shortcut is a great example of Excel magic in action


6. AUTOFORMAT

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

  1. CTRL G and select SPECIAL (you can use TAB)

  2. Choose blanks

  3. CTRL –

  4. Shift cells up


Quickly removing the blanks in the spreadsheet can be very useful


  • FILL THE BLANKS

  1. CTRL G and select SPECIAL (you can use TAB)

  2. Choose blanks

  3. SHIFT =

  4. Select the cell

  5. CTRL ENTER

  6. C/P as values (optional but recommended)

Filling the blanks shortcut in action


8. ADD/REMOVE FILTER


Filters are used a lot when analyzing data and crunching the numbers, and adding and removing them fast can save a lot of time. This shortcut is very useful when you have more than one active filter. In that case, it is usually faster and easier simply to remove it and add it again:

CTRL SHIFT L – add/remove filter

ALT ↓ - activate filter



Adding and removing filter shortcut



9. ADD/REMOVE BORDERS

It is always better when the data is clean. For better visualization, you can use table borders. If you want to add/remove all borders fast, select the cells and type:

CTRL SHIFT & - add

CTRL SHIFT – - remove


Using add/remove borders shortcut can make your data much easier on the eyes


10. CREATE A TABLE

Excel tables have a lot of advantages, such as automatically filling down formulas and formatting the whole column.

Excel tables are also great for pivoting if you need to add or remove data. To make managing and analyzing a group of related data easy, you can turn a range of cells into an Excel table pretty fast with this shortcut.

CTRL T


Quickly making a table can be very useful


 


If you think that your financial models are overgrowing spreadsheets, and you need a better financial modeling tool, Farseer might be for you:

- Financial modeling in Farseer is centralized, fast, consistent - you won’t need to worry about errors and mistakes.
- Natural language formulas are intuitive, error-proof and cannot be deleted by accident.
- Sharing and exporting your work is granulated - you can share only parts of your model relevant to your user
- Built-in hierarchy makes modeling transparent by default
- Reorganize models by simply dragging and dropping entire spreadsheets to a specific location in the model 


LEARN MORE ABOUT FARSEER HERE


472 views0 comments