Notes about oocalc.
Regex find
You can use regular expressions only if you tick ‘Regular expressions’ in the ‘Other options’ section of the Find & Replace dialog, accessed via Edit -> Find & Replace…
Reference: List of Regular Expressions
Some keyboard shortcuts
Key combo | Action |
---|---|
ctrl-home | Go to first cell (top left) |
ctrl-end | Go to last cell (bottom right) |
ctrl-left | Go to first value in row |
ctrl-right | Go to last value in row |
ctrl-up | Go to first value in column |
ctrl-down | Go to last value in column |
F2 | Edit a cell without having to click on it |
Reference: Calc/Shortcut Keys for Spreadsheets - LibreOffice Help
Turn off auto-replace
You’ll find a load of options at Tools -> ‘AutoCorrect Options…’. If you want to turn off all auto-replacement then browse around the tabs and disable/enable most things.
Here are the ones that bother me the most…
Hyphens
Do the following prevent hyphens being auto-replaced with a dash character:
- Tools -> AutoCorrect Options…
- Untick ‘Replace dashes’.
Date contractions
Do the following to stop date contractions 1st, 2nd, 3rd, 4th etc being auto-replaced with superscript st, nd, rd or th:
- Tools -> AutoCorrect Options…
- Localized Options tab.
- Untick ‘Format ordinal numbers suffixes (1st -> 1^st)’.
Filter column to find unique cells
If the sheet has a lot of data e.g. 30000 rows then select a column or row otherwise the whole sheet will be auto selected and the app will slow to the point of despair.
Standard filter
Select column. Data -> Filter -> Standard Filter…
Condition <> empty Options -> No duplications
Advanced filter
I did this first before I found that a standard filter could be easier / faster. I’ve left this here for future reference, at which point I’ll review.
Data -> Filter -> Advanced Filter..
Set a range e.g. $N1:$N32324 Options -> No duplications.
Performance is shit with large sheets. Also I would get ‘This range does not contain a valid query’ errors randomly, even if I used the mouse to select a range!
Advanced filter
Quotes
If you have a condition like SUMIF, you must use double quotes. Single quotes won’t work.
=SUMIF(A1:A100,"foo",B1:B100)
If you use single quotes you’ll get a sum of 0.
Source cell range from different sheet
- Copy the range in sheet 1.
- Paste Special in sheet 2, selecting the Link option.
Show rows hidden by a filter
If you apply a filter and it hides a load of cells, select all cells (i.e. selection includes cells before and after), then do Format -> Row -> Show.
Export a graph/chart
One way of exporting a spreadsheet graph or chart is to copy it into a LibreOffice drawing and export from there.
- Copy the chart to the clipboard.
- Create a new drawing. File -> New -> Drawing.
- Paste into the drawing.
- Export the drawing (as whatever format you want).
Reference: How do I export the charts in a spreadsheet as PNGs?
Flip a bar chart
If you have a bar chart that shows largest to smallest and you want it to show smallest to largest…
- Right-click the chart and select Edit.
- Right-click on the labels of the axis you’d like to flip and select Format Axis.
- On the Scale tab select ‘Reverse direction’.
- Click OK.
Reference: [Solved] Is there a way to flip the data?
Weird characters that look like Chinese
If you open a document and it contains a single cell with odd characters (e.g. 慲獮捡楴湯ⱳⰬⰬਬ堬塘⁘塘) then you probably need to open the file again and select the correct encoding.
On linux you can determine the encoding of a file as follows:
test@pc ~ $ file -bi example.csv
text/plain; charset=utf-8
Reference: [Solved] Problem with Calc and Weird Symbols in the Output