Excel and LO Calc Quick Copy
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 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.