LibreOffice Calc Tips
2 minute reading time
I’ve been working with LibreOffice Calc (or Excel) spreadsheets recently and wanted to share some of the things I’ve learned.
Absolute vs Relative Cell References
The main difference between referencing a cell relatively vs absolute is that the absolute reference includes a
$ in the front. This is important if you want to drag a formula across multiple cells.
Referring to a sheet name
To refer to cells in another sheet, first begin the reference with the sheet name, then a period, follow by the cells you wish to reference in that sheet. If the sheet name has spaces in it, then you need to wrap it in quotes.
Referring to a column
To refer to a single column, you need to repeat the column name separated by a colon.
Get row that matches a query
For this we’ll use the
MATCH function. It takes three parameters:
- The value to match
- The range of cells to query over
- Which comparison function to use. Use
It will then return the first row number that matches the query.
MATCH("Bob", B:B, 0)
Query a value based on another from that row.
To do this, we will need to combine both the
INDEX function and the
MATCH function. The
INDEX function takes three parameters:
- The range of cells to reference
- The row number
- The column number
MATCH function as the second argument, and you can reference another column of a row based on a query.
INDEX(A:B, MATCH("Bob", B:B, 0), 1)
Refer to a value in a nearby cell
OFFSET function you can refer to a cell relative to another. Its parameters are:
- Reference Cell
- Row Offset
- Column Offset
# To see the value in the row above A5 (A4) OFFSET(A5, -1, 0, 1, 1)
Strings separated by
& are concatenated together.
"Hello " & "World."