Notes about oocalc.
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
|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|
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…
Do the following prevent hyphens being auto-replaced with a dash character:
- Tools -> AutoCorrect Options…
- Untick ‘Replace dashes’.
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.
Select column. Data -> Filter -> Standard Filter…
Condition <> empty Options -> No duplications
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!
If you have a condition like SUMIF, you must use double quotes. Single quotes won’t work.
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).
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:
steph@bpc ~ $ file -bi example.csv text/plain; charset=utf-8