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.
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.
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 sentenceTOTW 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.
I should have said that works on whatever cells you have currently selected.
Stephen, here's the code I use. It's interactive, so you can choose the number of digits to round to, and whether to apply to formulae, values or both:
Dim rngCell As RangeDim intDigits, intRoundFormulae, intRoundValues As Integer
intDigits = InputBox("How many digits do you want to round to?", "ROUNDING LEVEL", 2)intRoundFormulae = MsgBox("Do you want to round formulae?", vbYesNo)intRoundValues = MsgBox("Do you want to round cells containing values (i.e. not formulae)?", vbYesNo)
For Each rngCell In Selection
If intRoundFormulae = vbYes Then
If Left(rngCell.Formula, 1) = "=" Then rngCell.Formula = "=round(" & Right(rngCell.Formula, Len(rngCell.Formula) - 1) & "," & intDigits & ")"
If intRoundValues = vbYes Then
If rngCell.Formula <> "" And Left(rngCell.Formula, 1) <> "=" Then rngCell.Formula = "=round(" & rngCell.Formula & "," & intDigits & ")"
Stick it in your personal macro workbook and give it a shortcut key (e.g. Shift + Ctrl + R)
Malcolm, could you share that code with me? Use of =ROUND doesn't seem to be recommended but between you and I, I use it a lot as well!
Really helpful, Joanna, thanks!
For E405 read C400 - not sure what I did there.