Excel Names collection – like Hashmap+symbolTable

If the Names collection has 55 pairs, then for each you can change the key and you can change the value.

Each pair also has a scope like a variable scope.

Therefore the Names collection is a hybrid between a hashmap and a symbol table (holding global variables).

If you use a name to hold a value (and save the file), it retains the value even after power-down.

Advertisements

MSExcel | special LEDs

These special LEDs “radiate” useful information, but many users don’t recognize them.
–Formula ribbon -> showFormulas
Affects entire workSheet, but not entire workBook
–status bar quick stats, showing sum, average by default. customizable by right-click
–in function wizard, bold args are mandatory
— single $ vs 2 separate $s in a formula
$K33 means FIX column K when we copy the formula;
K$33 means FIX row 33

MSExcel | time-savers

–select a row of cell populated with formulas -> double click on the right-bottom handle ==> will auto drag downward

 

–rename a tab, quickly — double click the tab

–to see (long) list of sheets Vertically, right click on the tab-navigator buttons

–to clear format on a cell — Home ribbon -> clear button

— sort quickly — click any cell in the key column -> Data ribbon -> ascendingSort button ==> will sort the entire “natural table”

–VIEW ribbon -> arrangeAll -> TILE ==> 2 workbooks side by side …. easiest way to move tabs between workbooks

–to copy formatting from source cell to target cell

click source cell -> home ribbon -> formatPainter -> click target cell

 

 

MSExcel | cursor-jump by ctrl-arrow

Very useful when we have large blocks of data.
 –ctrl-anyArrow ==> jump to boundary… always lands on {next populated cell or the physical limit of the “universe”}
This is the basic technique.
–ctrl-shift-anyArrow ==> range-select
–to select current “block”
click on the top left “starting point” -> ctrl-shift-right -> ctrl-shift-down
Alternatively, click anywhere in a block -> ctrl-*
–Range.End() pitfalls

Rule – End(xxx) under VBA automation == Ctrl+Arrow
Rule – it always transports you i.e. jumps to a populated cell, never an unpopulated cell.

Jargon – An “Edge” cell is a populated cell with at least one unpopulated neighbour.

Happy path – If you are in the middle not edge of a populated region, then End() would jump to the Edge

If you are on an unpopulated cell, then you jump to the next edge cell.

If you are already on the right-edge, end(right) would first (implicitly) jump to the RHS neighbour (unpopulated), then do the actual jump.

MSExcel | find/replace

–can change all red cells to blue background
–can limit the replacement to within a (disjoint) selection
–look-In “values”
needed if the search “needle” appears in a formula’s evaluated output but not in the formula’s “source code”