Handy Excel navigation keyboard shortcuts

City Financial Models • 22 July 2019

Especially handy for Laptops

Excel has a couple of great navigation shortcuts, which can be especially useful if you are using Excel on a laptop, although they work equally well on a desktop.

Ctrl + PgUp/ Ctrl + PgDn

This allows you to switch between sheets within a workbook.

Alt + PgUp/ Alt + PgDn

These shortcuts move the screen to the left or right.

Home

This moves the active cell to column A (if the panes aren’t frozen).

Ctrl + Home

This moves the active cell to cell A1 (if the panes aren’t frozen).

Ctrl + End

This moves the active cell to the bottom right hand cell. This should be the last row and last column of the active area. If this isn’t, then the workbook will take up more memory than it needs to and should be reset. You can do this by deleting the excess rows and/or columns, saving the file and then exiting and reopening the file.

< Older Post Newer Post >

Spill Error - Why doesn’t my Index Match work in Excel 365.

by City Financial Models • 27 May 2020

This something we are starting to see as we work across a number of different versions of Excel. Recently, Microsoft rolled out something called dynamic array formulas in Excel 365, which is a substantial change from previous versions. Dynamic array formulas automatically spill in to neighbouring blank cells. They have also added some very powerful new functions such as FILTER, UNIQUE, XLOOKUP and XMATCH. Even better there is no need to enter array formulas with control + shift + enter (CSE) as in other versions. However, because of the way dynamic arrays work, there will be instances where models built in other versions will have formulas which unintentionally now try to spill or populate into neighbouring cells when opened in Excel 365. Hence the Spill error. To overcome this, the @ character is added automatically to stop formulas unintentionally spilling multiple results into neighbouring cells. Because dynamic arrays are only available in the subscription version at the moment and not in the other versions, these powerful new functions may cause some compatibility issues. In general, the @ character should overcome the compatibility issues with models build in other versions to work in Excel 365, however the dynamic array functions at present will not work in non-subscription versions so should be used with caution if you aren’t sure of your final audience.