Formulas I often need and quickly want to copy.
Color whole row if value is equal to x
Excel: conditional formatting –> manage rules –> add –> set range that should be colored –>
formula = $<column with x value>1 = "<expected x value>" –> apply style
LO Calc: format > conditional –> manage > add –> formula
is –> $<column with x value>1 = "<expected x value>" –> apply style –> range: range that should be colored
dropdown menu
Excel: select range to insert dropdown menu –> data –> data validation –> list –> set range to take values from –> edit warning if needed
LO Calc: select range to insert dropdown menu –> data –> validity –> list –> set rang eto take values from –> edit warning if needed
search for value somewhere
Excel and LO Calc
=VLOOKUP(<value to lookup>, <range to search in>, <column to return if found relative to first column in lookup>, <TRUE or FALSE for approximate match>)
=SVERWEIS() in german
suppress errors
Excel and LO Calc:
=IFERROR(function, output if error is thrown) =WENNFEHLER() in german
reference cells in other sheets
Excel: =<sheet name>!<cell>
LO Calc: =<sheet name>.<cell>k
remove trailing spaces
Excel and LO Calc: =TRIM(<cell>) =GLÄTTEN() in german
combine values from cells to one
Excel: =CONCATENATE(<cell 1>, <combining character>, <cell 2>)
LO Calc: =TEXTJOIN(<combining character>, <cell 1>, <cell n>)
Misc
| Use | Excel | LO Calc |
|---|---|---|
| get unique values from range | =UNIQUE(<range>) | |
| replace value | =SUBSTITUTE(<cell>, <character to replace>), <replacement>) | |
Range Names
- select range of cells
- click name box field
- enter name for range, press ENTER
More name features in “formula” tab.