22
votes
How to set decimal separator in Libre Office Calc?
From https://help.libreoffice.org/Common/Languages#Locale_setting:
Specifies the locale setting of the country setting. This influences settings for numbering, currency and units of measure.
Go to ...
19
votes
Copy/Paste a formula in Libreoffice without adjusting the cells
Trick:
Cut (ctrl + X)
Undo (ctrl + Z)
Paste (ctrl + V)
Since "cut-paste" works, but not "copy-paste", you can cut-paste and use the undo not to erase the old values.
16
votes
Accepted
How to find and remove external links in (Libre|Open)Office Calc spreadsheet
I also got that popup on startup:
I had the same problem and found out today that there is an option in the Edit menu called Links.... Select that option and you get a dialog with the list of ...
15
votes
how to get Libre Office to adjust row height to accommodate text entry?
Using LibreOffice Calc 5.4.4.2 on OS X, I found I had to do four things to set every row to its optimum height:
Select all cells
In the "Format" menu, turn on "Format/Text/Wrap Text"
In the "Format" ...
15
votes
Accepted
How do I join two tables with LibreOffice Calc?
Here's a straightforward way to do it.
Sheet 1:
Sheet 2:
Sheet 3:
On Sheet 3, Name and Age are just cell references to Table 1. So A2 contains:
=Sheet1!A2
And B2 contains:
=Sheet1!B2
Gender ...
13
votes
Accepted
How to get number value with leading zeros in LibreOffic Calc?
The TEXT function can add leading zeroes without formatting the cell. It returns a string.
Even though it is a string, it can still be treated as a number in other formulas. For example =D2+1 gives ...
11
votes
Accepted
Extracting named groups of regular expression in LibreOffice Calc
In both Excel and Calc, the cleanest solution is to create a general-purpose regular expression macro. To do this in Calc, go to Tools -> Macros -> Organize Macros -> LibreOffice Basic and ...
11
votes
Accepted
Convert the multi-step sum of booleans into a single formula
The function you are after is COUNTIF():
Enter the following formula in G3 and ctrl-enter/copy-paste/fill-right into G3:I3:
=COUNTIF(A3:A8,">"&D3)
COUNTIF() checks each value in the first ...
11
votes
Accepted
How to find cells that reference the current cell in LibreOffice Calc spreadsheet?
LO Calc has built-in functions for that equivalent to Excel's audit functions. In Calc, it's called Detective, and is available from the Tools tab of the menu. Tracing precedents and dependents also ...
10
votes
Accepted
Open a CSV in LibreOffice Calc without converting values to scientific notation
Try these options in the Text Import dialog:
Deselect the 'Detect Special Numbers' checkbox
In the preview area, click the column header where it is labeled 'Standard' and change it to 'Text'. ...
10
votes
Accepted
Default cell background in Calc
Another way would be to modify the overall color settings:
Menu "Tools" -> "Options"
in options: "LibreOffice" -> "Application Colors"
modify the very first color property "General" -> "Document ...
10
votes
The maximum number of columns per sheet was exceeded
Options... ▸ LibreOffice ▸ Advanced ▸ Optional Features ▸ Enable experimental features (may be unstable)
Restart LibreOffice
Options... ▸ LibreOffice Calc ▸ Defaults ▸ New Spreadsheet ▸ Enable very ...
8
votes
Currency conversion in LibreOffice Calc?
Without using a plugin, try using api.fixer.io:
=LEFT(RIGHT(WEBSERVICE("http://api.fixer.io/latest?symbols=EUR,GBP"),9),7)
This will retrieve something like this:
{"base":"...
8
votes
Accepted
Identify cell containing given text in LibreOffice Calc
Just use the MATCH() function. Since it supports regular expressions, you can use MATCH() to search for partial strings.
Here's an example:
The lookup array is A1:A4, the search criterion is .*a.* (...
8
votes
Accepted
Insert the current date and time (DATETIME-function) with a single shortcut into a cell in LibreOffice Calc
Change the format of cells before shortcut is simplest solution?
Format cell after shortcut does not work.
Macro way ...
Sub Main
Dim Doc As Object
Dim Sheet As Object
Dim Cell As ...
7
votes
How to find and remove external links in (Libre|Open)Office Calc spreadsheet
Every time I opened up a Calc Sheet I had copied, I received the same pop up "This file contains links to other files. Should they be updated?"
The spreadsheet I was opening was copied from another ...
7
votes
How to find if 2 columns (or rows) in LibreOffice Calc are identical?
Suppose Jimmy is in cells A1 and B1. In cell C3 use function EXACT, so the formula of cell C3 will be:
=EXACT(A1,B1)
Apply the same formula for every cell in column C. In Calc you can grab the ...
7
votes
Highlighting every other row in LibreOffice (or OpenOffice) without conditional formatting?
From menu: Format → AutoFormat Styles.. . You could add your own.
Another option is to use macros (as in ngulam's answer).
Color2Rows is an extension that adds a button to the tool bar, for ...
7
votes
Accepted
Sum values with same name
That's exactly what a pivot table does. Add labels in the first row, then go to Data -> Pivot Table -> Create. Drag "Name" to Row Fields, and "Amount" to Data Fields.
For complete instructions, see ...
7
votes
Default cell background in Calc
One way is to change the default template:
Create a new blank document
Press F11 to open the Styles editor
Find the Default style and right-click then choose Modify...
Set Background Color as desired
...
7
votes
Accepted
How to highlight the current row and column like a crosshair in Libreoffice?
Lupp has recently created an example spreadsheet that uses macros for this behavior. It's posted at https://forum.openoffice.org/en/forum/viewtopic.php?t=43531#p431848.
However, he remarks at AskLO ...
7
votes
Use a Python function as formula in LibreOffice Calc cells
Some things you asked about are easily solved, others not so much. First of all, the IDE. APSO is an extension that is now ready to use and provides a convenient menu for editing Python macros.
...
7
votes
Accepted
Copy/Paste a formula in Libreoffice without adjusting the cells
when I have done a CUT & paste in Excel in the past, it transfers as is. The copy/paste will change cell references. I have not used LibreOffice.
7
votes
Accepted
LibreOffice Calc: conditional formatting based of column / cell value
I am using LibreOffice Calc 5.1.6.2.
Just like my highly respected preceding poster described:
Select the range of cells to be conditionally formatted (in my case A1:F44);
Select Menu Format -> ...
6
votes
Is it possible to copy & paste a pivot table in a spreadsheet?
Select the Pivot Table
On the Analyze Ribbon - Click on Select-> Entire Pivot Table
Right Click Copy in the pivot table area
Go to the location where you want to place the copy of the pivot table and ...
6
votes
Accepted
Is there any way to extend the column limit in Libreoffice Calc?
Yes, there is.
How to enable support for very large spreadsheets in Libreoffice 7 Calc?
Via the following steps in Tools -> Options...:
First under LibreOffice -> Advanced, check Enable ...
6
votes
Accepted
LibreOffice equivalent to SQL "count(*) group by some_column"
Create a pivot table with column B in the "Row Fields" and in the "Data Fields" list; double-click on the "Data Fields" entry "B" to change the default SUM ...
5
votes
Can I retrieve and access a simple JSON object via HTTP in LibreOffice Calc?
After 4 years, they still haven't implemented the suggested FILTERJSON().
This is obviously not a real solution, but one that works for now, for very simple cases:
Send a request to your API using: =...
5
votes
LibreOffice Calc: how to pad number to fixed decimals when used within formula?
Solved!
I couldn't find this it at first, but I've got it: FIXED(C1,2)
5
votes
Accepted
How to count the number of cells in a range containing a specific (sub)string with Excel/Calc?
I answer myself as I do this at least once a year and I feel the need of having a place to look for quickly when I forget something, plus I think this could be useful for someone else too.
So, ok, I ...
Only top scored, non community-wiki answers of a minimum length are eligible
Related Tags
libreoffice-calc × 693libreoffice × 180
microsoft-excel × 127
worksheet-function × 115
openoffice-calc × 98
spreadsheet × 69
openoffice × 33
charts × 32
csv × 30
conditional-formatting × 23
macros × 11
google-spreadsheets × 11
libreoffice-writer × 11
linux × 10
ubuntu × 10
date × 10
vlookup × 9
formatting × 7
copy-paste × 7
pivot-table × 7
find-and-replace × 7
cell-format × 7
keyboard-shortcuts × 6
pdf × 6
regex × 6