- Formulas
- New Arrays in Cells (Beta 2026)
- Formatting
- Pivots
- OfficeScript instead of Macros
- Misc Office Tools
- Misc Office Links
Formulas
https://support.microsoft.com/en-au/office/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb
https://support.microsoft.com/en-au/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188
https://support.microsoft.com/en-au/office/logical-functions-reference-e093c192-278b-43f6-8c3a-b6ce299931f5
https://support.microsoft.com/en-au/office/lookup-and-reference-functions-reference-8aa21a3a-b56a-4055-8257-3ec89df2b23e
https://support.microsoft.com/en-au/office/date-and-time-functions-reference-fd1b5961-c1ae-4677-be58-074152f97b81
https://support.microsoft.com/en-au/office/text-functions-reference-cccd86ad-547d-4ea9-a065-7bb697c2a56e
https://support.microsoft.com/en-au/office/statistical-functions-reference-624dac86-a375-4435-bc25-76d659719ffd
https://support.microsoft.com/en-au/office/math-and-trigonometry-functions-reference-ee158fd6-33be-42c9-9ae5-d635c3ae8c16
Subtotal instead of sum
https://support.microsoft.com/en-au/office/subtotal-function-7b027003-f060-4ade-9040-e478765b9939
- Represents a partial sum or total of a specific group or section within a larger set of data.
- Used when you want to calculate a total for a portion of the data, before calculating the overall total.
- Example: “The subtotal of the first three items is $100”.
- In Excel, the SUBTOTAL function can be used to calculate subtotals, even when rows are hidden, making it useful for filtered data.
Example: =SUBTOTAL(9,C1,C2:C3): Sums the ranges
=SUBTOTAL(function_num,ref1,[ref2],...)
The SUBTOTAL function syntax has the following arguments:
- Function_num: Required. The number 1-11 or 101-111 that specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.
| Function_num\ (includes hidden rows) | Function_num\ (ignores hidden rows) | Function |
|---|---|---|
| 1 | 101 | AVERAGE |
| 2 | 102 | COUNT |
| 3 | 103 | COUNTA |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUCT |
| 7 | 107 | STDEV |
| 8 | 108 | STDEVP |
| 9 | 109 | SUM |
| 10 | 110 | VAR |
| 11 | 111 | VARP |
- Ref1 Required. The first named range or reference for which you want the subtotal.
- Ref2,… Optional. Named ranges or references 2 to 254 for which you want the subtotal.
Xlookup instead of just Vlookup
- Xlookup
Rounding
ROUNDUP: =ROUNDUP(number, num_digits)
ROUNDDOWN: =ROUNDDOWN(number, num_digits)
ROUND: =ROUND(number, num_digits)
| Formula | Description | Result |
|---|---|---|
| =ROUND(2.15, 1) | Rounds 2.15 to one decimal place | 2.2 |
| =ROUND(2.149, 1) | Rounds 2.149 to one decimal place | 2.1 |
| =ROUND(-1.475, 2) | Rounds -1.475 to two decimal places | -1.48 |
| =ROUND(21.5, -1) | Rounds 21.5 to one decimal place to the left of the decimal point | 20 |
| =ROUND(626.3,-3) | Rounds 626.3 to the nearest multiple of 1000 | 1000 |
| =ROUND(1.98,-1) | Rounds 1.98 to the nearest multiple of 10 | 0 |
| =ROUND(-50.55,-2) | Rounds -50.55 to the nearest multiple of 100 | -100 |
Search Cell for Text
=ISNUMBER(SEARCH("substringtosearchfor",A2)): search another cell for text and return a true or false
Timestamp and Timezone conversions
Convert ISO 8601 format timestamp: =(DATEVALUE(MID(A2,1,10))+TIMEVALUE(MID(A2,12,8)))1
Also add some time (10 hours to convert to AEST): =(DATEVALUE(MID(A2,1,10))+TIMEVALUE(MID(A2,12,8)))+TIME(10,0,0)
Custom Formatting
Don’t forget the Custom Formatting
Date Stuff
=DATEDIF - older formula
=LET(target,DATE(2026,11,5),NETWORKDAYS(TODAY(),target)-1&" working days until "&TEXT(target,"mmmm d yyyy")) - pretty number of working days until date but only specified once
=NETWORKDAYS(TODAY(),EOMONTH(TODAY(),MOD(3-MONTH(TODAY()),3))) - days left in this (calendar) quarter
=NETWORKDAYS(TODAY(),DATE(YEAR(TODAY()),12,31)) - days left in year
Python
=PY("print(""hello"")",1): adding python to excel
Introduction to Python in Excel
Get started with Python in Excel
New Arrays in Cells (Beta 2026)
Put multiple values in one cell with lists and arrays in Excel
Formatting
Custom Formatting
Review guidelines for customizing a number format
Custom Excel number format
d/mm/yyyy h:mm: date and time with 24h time
d/mm/yyyy h:mm AM/PM: Date and time with 12h time (Non-Standard)
0 "days left or other suffix text": adds text after value without it being in the string
Undocumented Uses
I have seen a couple of instances in the last few years of really strange format codes that are undocumented and do odd things. Some of this may have been due to Copilot generating stuff, or Excel for the web
Pivots
- Add PivotTable Stuff
OfficeScript instead of Macros
- Add OfficeScript
Misc Office Tools
Spreadsheet Compare
There is a special external utility to compare two spreadsheets and its very in depth, very cool and often forgotten.
“SPREADSHEETCOMPARE.EXE”
“C:\Program Files\Microsoft Office\root\Client\AppVLP.exe” “C:\Program Files (x86)\Microsoft Office\Office16\DCF\SPREADSHEETCOMPARE.EXE”
Overview of Spreadsheet Compare
Compare two versions of a workbook by using Spreadsheet Compare
Misc Office Links
TBA