It’s that time of year: January heralds a return to the gym, a healthy eating regime and...reporting. If you’re wading through Excel spreadsheets - as family officers ourselves we’ve been there! - check out our top five Excel hacks:
Tip #1 - INDEX-MATCH vs. VLOOKUP
VLOOKUP is easily understood and used when you are analyzing small sets of data. INDEX-MATCH should be considered for larger sets of data as it brings fewer Excel performance or crashing issues.
Tip #2 – Matrix formulas (KEY: CTRL + SHIFT + ENTER)
Matrix formulas allow you to build formulas that far exceed the current Excel built-in functionality. Matrix formulas should only be used if an Excel built-in functionality doesn’t exist as it does consume more memory.
Tip #3 – Highlight all constants (KEY: F5 +Alt + S + O + X)
Selects all hardcoded inputs within the Excel file. This helps in understanding the inputs vs. formulas.
Tip #4 – Highlight all links to other workbooks (Key: Alt + E + K)
Selects all cells that link to other Excel workbooks. It allows you to check external links and prompts you to determine whether to update them before proceeding with your analysis.
Tip #5 – Slicer for pivot chart/pivot table (Under INSERT > SLICER)
Creates buttons so you can filter your data dynamically to update all charts and tables within the Excel sheet.
With Finlight we wanted to meet the challenges posed by reporting head on. We’d had our own frustrations with Excel and reporting aggregators. Our software allows you to take different reports (be they from custodians, banks, asset managers or brokers) and integrate them. Thus making it easier for you to analyse performance, risks and costs, to benchmark and also to make estimates for the future. Find out more by taking a demo.