Excel Tip of the Week #235 - Shortcuts from a financial modeller

This week we have a guest author - Joanna Hayes from Amberside.

Joanna is a manager at the advisory firm Amberside, where she has spent the last 7 years leading financial modelling, training, and transaction support mandates.

In this Basic User post, Joanna will take you through some of her favourite keyboard shortcuts from her experience as a financial modeller.  Over to Joanna!

For those who use Excel regularly at work, keyboard shortcuts are essential for making you more efficient and, more importantly, showing colleagues what a spreadsheet wizard you are. Financial modellers are very keen on shortcuts, and for good reason – less time moving your hand from keyboard to mouse, more time thinking about coding.

Here are a selection of favourites to help you perform some quick tasks, in a financial model or otherwise. Try learning one new shortcut a week and repeating it until it is ingrained in your muscle memory.

Moving around the workbook

Being able to navigate around a large workbook quickly is very helpful and there are many shortcuts you can use for this. Here is a simple one to get you started:

Ctrl & [ - go to the first cell reference in the formula

F5 > Enter – go back to the last cell you were at

If you are interrogating complex formula, there are add-ins such as “Explode” that you can use to assist with navigation too.

Inserting new rows of formula

Use this when you already have a formula written but would like to replicate it to apply to the row beneath it.

Shift & Space bar - Highlight row (beneath the row you are copying)

Ctrl & + - Insert new row

Ctrl & d - Copy the formulae from the row above

It’s best to write formulae that can be applied consistently to all rows in a block – this will save you time reinventing a new formula for each row.

Cell anchoring and formula consistency

Cell anchoring is important when writing formula in models, as the same formula needs to be able to copy across the row while maintaining the correct cell references. You can use a simple shortcut to check formula consistency across a row.

F4 – (when editing a formula) this will cycle through the $ anchoring options for you

Ctrl & shift & right arrow - Highlight the data to check

Ctrl & \ - Checks for inconsistencies in the row

If you don’t see the message “no cells were found” then it will take you to the first inconsistent formula in the row. Checking for consistency will help towards keeping your spreadsheets robust and error free.

Insert comments

Use this to add a cell comment over an assumption you have used, for easy reference later on and as a good audit trail.

Shift & F2 - Insert comment

Ctrl & ; - Insert the date

Type your comment

Your colleagues will appreciate the informative note.

Save As

Version control can be important when working in a complicated Excel file, so make sure you save the file as a new iteration regularly so that you can go back to earlier versions if needed.

F12 – Save As menu

Type the new version number into the name

Enter – close the menu

You’ll be less likely to suffer from a crashed computer losing your work.

Inserting a graph to sense check outputs

Models are very complicated and are changed and updated frequently. It’s a good idea to sense check the outputs regularly and one very quick way to see this is by displaying as a graph to highlight any outliers in the data.

Ctrl & shift & right arrow - Highlight the data to display

F11 – creates a new sheet with a graph

Spotting errors will take half the time.

Once you have tried these and discovered the benefits of shortcuts, try reading TOTW #229 (Alt key shortcutting) for some more theory behind shortcuts.

Thanks to Joanna for her submission!

Previous post - Inserting a calculated value into a sentence
TOTW index 
Next post - Creating numeric patterns

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.

Anonymous