Number Format Syntax
Show number as thousands #,##0.0,
Show number as millions #,##0.0,,
Keyboard Shortcuts
CTRL-END: jump to last filled cellCTRL-SHIFT-END: mark from point to last filled cell
CTRL `: switch view mode to show formulas instead of their result
F4: cycle through fixed and relative cell referencing modes
F2: switch to cell content editing
References
To a range A1:C5
To multiple ranges (A1:B2,B2:C4) (however, if used in fromulas, the () are ignored ... this does not create a single range, it creates a list, and the additional fields in the list will erronously be interepreted as the next parameters.
Arrays, Matrices, Vectors
Excel knows something called Array Formulas, Formulas that can return arrays (ie lists) of values. To enter one, enter the formula with SHIFT-CTRL RETURN (esoteric, I know); presence will be indicated by {} around the formula. If not done, only the first value is returned for further processing.On example is IF(), which can return an array of TRUE and FALSE values in array mode.
The syntax to enter arrays as constants (in English language settings) is {1;2;3} for columns, and {1,2,3} for rows.
One can define named ranges, and the defintion can consist of formulas instead of just cell references.
Conditional Formatting
To refer to the current cell in formulas there, use the relative name of the upperleftmost cell, it automatically converts to each current cell. NOTE: all formulas must start with =
Lookups
VLOOKUP - easy if key is on the left hand column, always set false as last argument
https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/
INDEX(array, row_num, [column_num])
MATCH(lookup_value, lookup_array, [match_type]) type = 0 for exact lookup
allows for bi-dimensional lookup, and does not need the key column first.
Find all Cells with a formula, or all cells with hardcoded values: select a range of cells, CTRL-G for GoTo, select special and Formulas or Constants. Matching cells in Range will be selected. Unfortunately this is a manual process, not a programmable approach that just formats these cells in a sheet dynamically.
where vrange, hrange and matrix are ranges in the spreadsheet. The value can either be an indvidual cell (probably in absolute address), or a fixed string (in double quotes).
This works as range=matching returns a vector of FALSEs, with TRUEs where matches are, and the multiplication of the vectors returns a matrix with a 1 only where there was a TRUE in both vectors.
You must cast the results of a range=match comparison to turn TRUE/FALSE into 1/0. This can be done by an arithmethic operation, for example (range=test)*1. Otherwise, functions like sumproduct will not be able to calculate with them.
Logic
Numeric tests (A1>3 etc) treat blank fields as 0s. Ie if A1 is blank, a test >3 will be false, =0 true.
While AND and OR cannot directly deal with Arrays, NOT can, and will invert an array.
MATCH (VERGLEICH) find a search term in a list of cells, returning its index. Set the third argument to 0 to force exact matches.
INDEX: return the index value from an array (or range) at a given row (and column index).
OFFSET: return a range relative to a given cell, offset row and column, row and colum size
COLUMN (SPALTE) and ROW (ZEILE) return the column or row index of a reference. If none is provided, uses the ones from the cell the formula is in.
ADDRESS (ADRESSE) construct an address reference from column and row indices. (The inverse of the two functions above).
INDIRECT (INDIREKT) if you have an address string, returns the contents of the corresponding cell.
VLOOKUP (SVERGLEICH) and HLOOKUP (WVERGLEICH) .. the closest thing you will get to an actual table query, ... another time.
Pivot Tables
You can add derived, calculated fields (instead of providing them explicitly as extra calculated columns in the source table)
