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
- More Date Stuff
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)
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