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
CTRL G and select SPECIAL (you can use TAB)
Choose blanks
CTRL –
Shift cells up
Quickly removing the blanks in the spreadsheet can be very useful
FILL THE BLANKS
CTRL G and select SPECIAL (you can use TAB)
Choose blanks
SHIFT =
Select the cell
CTRL ENTER
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