4 ms·
CTRL+SHIFT+L is faster to set filters. A few other useful ones: F2 to edit a formula CTRL ENTER to apply the current formula to the selected range ALT
by cm2187 10y ago
CTRL+SHIFT+L is faster to set filters.
A few other useful ones:
F2 to edit a formula
CTRL ENTER to apply the current formula to the selected range
ALT = to insert a SUM( ) where the range is automatically selected
CTRL : to insert the current date
SHIFTLOCK F9 to evaluate a fragment of a formula while editing it
CTRL ARROW to navigate through a block of numbers (and SHIFT to select them)
But you will only be a pro if you start using array formulas.
F2 a cell (or selected range) to start editing the formula, then CTRL SHIFT ENTER to apply it as an array formula.
Array formulas are useful for two things:
1) make several cells behave as a single vector / matrix. So you can do matrix algebra. From simple things like {TRANSPOSE()} to actual maths.
2) do super flexible aggregations. Like enter in a single cell:
={SUM(A1:A10*A1:A10*(B1:B10>C1:C10)*(D1:D10="A")*(MONTH(E1:E10)=ABS(F1:F10)))}
Which reads "do the sum of the square of elements in col A WHERE Col B greater than Col C AND Col D is "A" AND the month of Col E is the absolute value of Col F.
You can also earn money at the office, like when I bet with a colleague I could tell him how many times a column changed value in a single formula:
={SUM(1*(A1:A10<>A2:A11))-1}
Array formulas are slow but very powerful.
- ZenoArrow 10y agoThanks for the tips, didn't know about Ctrl+Shift+L. Regarding array formulas, I wouldn't recommend them. Especially as you can use SUMPRODUCT for the same purposes as array formulas... http://ww2.cfo.com/accounting-tax/2010/12/spreadsheets-hate-arrays-then-hijack-sumproduct/ http://ww2.cfo.com/accounting-tax/2010/12/spreadsheets-hate-...