As experienced financial modellers, we know (almost) every excel trick and shortcut in the book. We spend almost 80% of our day on excel, and over the years have discovered some very handy tricks we’d like to share.

If you’re not so familiar with excel, or want to become a self-confessed excel nerd like us, read on for our top tips we think everyone should know!

‍

1. Conditional Formatting

Excel is an excellent and easy way of storing important information, however as it begins to accumulate, it becomes harder and harder to sort through. Conditional formatting is a fantastic excel feature that helps you set the parameters of your data. This excel trick helps to find what you are looking for quickly and makes large pieces of data (like tenancy schedules!) easier to read.

Let’s assume you are looking to keep track of something that is depreciating by 5%. Click on ‘Conditional Formatting’, and highlight the column where the data is entered. Set the parameter to “less than 5%”.

All of the cells that conform to your search parameter (less than 5%) will be highlighted.

‍

2. Freeze Panes

Excel is excellent in its ability to hold rows and rows of data…. But what about you’ve been scrolling through for so long that you’ve forgotten what you were actually looking at, because your headers are up at the top? Fix this by freezing the top panes to keep the headers in place, while you continue to scroll through the data. To do this, click the cell below the header you’d like to freeze. Go to the ‘View’ tab and click on ‘Freeze Panes’.

It astonishes us how many spreadsheets we open where those pesky headers are nowhere to be seen, so this is a great excel trick we always do!

‍

3. Print Optimization

Ever tried to print something out on excel and it… didn’t quite work? It makes presenting numbers to clients a nightmare. The excel trick? When you go to print something, follow these steps:

• Print Preview
• Fit to One Page
• Adjust Margins
• Print Selection
• Printing Headers
• Choose Portrait vs. Landscape
• Spreadsheet Design

You can also click on the little button on the bottom right of the screen called ‘Page Break Preview’ and it will show you exactly what will be printed on each page. Simply drag the blue lines to adjust.

‍

4. Recover Lost Files

We’ve all been there. You’ve spent the past hour filling in and analysing data, and suddenly, your computer crashes. Your file is gone. Next time this happens, remember excel has an auto-save feature that saves your work (but only every twenty minutes). To do this excel trick, head to the ‘File’ menu, go to ‘Info’, then to ‘Manage versions’. You’ll see two options, click on ‘Recover unsaved workbooks’. You’re welcome!

If, like us, you use OneDrive, then you can also set your file to ‘Auto-Save’ by sliding the bar across on the top left, which means your file will never be more than a few minutes out of date. Do be careful to save your file as a new version before you start making changes if you want to be able to compare it to the previous version though.

‍

5. Paste Special

This handy shortcut opens up a world of possibilities… Sometimes you want to copy and paste data as values, sometimes formulas. Sometimes you just want to copy the formatting. Sometimes you want to transpose the data so it appears horizontally not vertically. Well here’s the secret:

Copy the data (Ctrl + C or Right Click + Copy).

Then paste the data to your heart’s content (Alt + E + S + Select your options or Right Click + Paste Special + Select your option)

‍