Useful formulas
Here are some formulas that exist in Google Sheets but not in Excel:
- IMPORTRANGE: Allows you to link the data between two or more different Google Sheets.
- QUERY: Allows you to retrieve data from a specified table using SQL-like syntax.
- JOIN: Concatenates two or more strings together.
- SPLIT: Splits a text string into separate columns using a specified delimiter.
- REGEXEXTRACT: Extracts matching substrings according to a regular expression pattern.
- FLATTEN: Flattens a range into a one-dimensional array.
- ARRAYFORMULA: Enables the use of a formula across an entire range of cells, without needing to copy and paste the formula into each individual cell.
Best practices
Set all key sheets
- Right centre
- Decimals
- Date in DDD-MM-YY format
- All cells conditional formatting formula —> =ISFORMULA(A1) —> Set to blue text
- All fonts are Nunito
Useful shortcuts
- Ctrl-1 to Ctrl-5 - for formatting cells
- Adding borders around
- Auto colour
- Hardcoded values - blue
- Formulas - black
- Ref to other sheets - green
- Ref to other workbooks - red
- Ref to third party sources - dark red
Exceptions
- Negative numbers have a negative sign (not parenthesis)
For financial modelling - See Wall Street Prep training