Questions tagged [libreoffice-calc]
Spreadsheet component of the LibreOffice suite
34
questions
42
votes
6
answers
34k
views
How to remove a plain text protecting single quote from all the selected cells in LibreOffice Calc?
I've imported a CSV file having the first column to be date-time values in ISO 8601 format like
2012-01-01T00:00:00.000Z
for the first moment of the year 2012.
Then, willing to make LibreOffice ...
- 7,523
14
votes
5
answers
26k
views
How to set the default font in LibreOffice Calc?
How do you set the default font used in new documents of LibreOffice Calc? I am using LibreOffice 3.3.2.
Some answers below have been updated for LibreOffice 4 as well.
- 10.2k
73
votes
4
answers
165k
views
How to color rows based on cell value in OpenOffice and LibreOffice
How do you automatically set the background color of an entire row based on the value of a specific column in that row in OpenOffice and LibreOffice?
There are similar questions for Excel, but this ...
- 9,152
17
votes
7
answers
56k
views
How to get VLOOKUP to return the *last* match?
I'm used to working with VLOOKUP but this time I have a challenge. I don't want the first matching value, but the last. How? (I'm working with LibreOffice Calc but an MS Excel solution ought to be ...
- 18.3k
7
votes
2
answers
37k
views
How do I create a line graph which ignores zero values?
I want to create a graph showing trends over time, calculated from a formula of various data.
However, some data is missing, resulting in zeroes in the calculated data which the chart uses.
When ...
- 1,243
43
votes
4
answers
48k
views
LibreOffice Calc: How to get total for HH:MM:SS cells
I'd like to take a group of cells in the HH:MM:SS format, and add them up. I don't want to have the value wrap on the 24 hour clock. I just want an absolute total of the values. (The sum function ...
- 699
33
votes
2
answers
31k
views
How do I create a sum for each group of data in Libre Office Calc?
I have a simple spreadsheet like this:
Invoice_Number Invoice_Date Invoice_Amt
1000 1/1/12 1
1001 1/1/12 2
1002 1/2/12 ...
- 1,427
12
votes
2
answers
7k
views
Libreoffice: sum of column except one cell
I want to calculate the sum of the A column excluding A1, because I want the result there. Illustration:
A B C
1 =SUM(A2:Ainfinite)
2 1234
3 5678
...
I ...
- 780
3
votes
1
answer
466
views
How to return in a cell all the matching values present in a column?
Suppose I have the following data:
A
B
1
idx1
VAL1
2
idx2
VAL2
3
idx3
VAL3
4
idx4
VAL1
5
idx5
VAL4
6
idx6
VAL1
7
idx7
VAL1
8
idx8
VAL2
9
idx9
VAL5
10
idx10
VAL3
For each "VAL[n]" ...
- 177
3
votes
3
answers
4k
views
Sum up to N highest-value out of a series?
I have a spreadsheet where each row is a set of numerical competition scores. Each row can be thought of as a single competitor in the overall competition, and each column is a competition event. ...
- 29.9k
2
votes
1
answer
14k
views
How to copy a column with conditional format by "value" - Libreoffice 4.1
I have a column that contains the data and a set of rules for the conditional format of the cells.
i.e. the conditional format changes the background colour of some cells if they are negative.
I ...
- 19k
0
votes
1
answer
142
views
Conditonal sum in Excel
I would like to write the formula (in OpenOffice, but I imagine it won't be any different than Excel) to perform the following calculation:
-> Go through the values in column B from B1 to B30 and add ...
34
votes
4
answers
43k
views
How to set decimal separator in Libre Office Calc?
How do I set the decimal separator in Libre Office Calc?
I want the decimal separator to be dot (.), not comma (,):
I want to set this system-wide and as default (don't want to set this for each ...
- 12.7k
22
votes
1
answer
20k
views
What is in LibreOffice Calc the equivalent to Excel's "Paste Special > Value"?
In my professional life, I'm limited to use Microsoft Office. In Excel there is an option ows to render the result of a formula to be pasted as a "fixed value".
For instance A1 = "I",B1 = "ride", C1 =...
- 221
15
votes
1
answer
29k
views
How to scale a long LibreOffice Calc sheet to fit on two pages when exporting to PDF?
I have Libreoffice and I want to export a long calc sheet to a pdf document with the fit-to-page function from the page preview in the file menu but it doesn't seems to work. I want to fit my sheet ...
- 1,914
10
votes
2
answers
5k
views
In LibreOffice Calc, how can I use the fill handle to copy formulae, but not the cell/border formatting?
I'm using LibreOffice 3.6.4.3 on Ubuntu 12.10 (Quantal Quetzal).
I use cell color and cell borders to group and distinguish groups of cells in my spreadsheet.
I have bold horizontal borders ...
- 221
9
votes
2
answers
2k
views
Default cell background in Calc
Whenever I create a new document with Calc the cell background is set to white. Therefore the first thing I must do before start working is modifying the background to an eye-friendly colour (usually ...
- 358
9
votes
3
answers
8k
views
Permanently and globally stop openoffice calc from autocorrecting dates/time
When I type "15:45" or "21/12" openoffice Calc changes it to "15:45:00" and "21-12-2011".
How do I disable this globally and permanently?
I know you can use "Format cell..." and then select text, but ...
- 2,737
9
votes
2
answers
13k
views
LibreOffice Calc: conditional formatting based of column / cell value
Unfortunately I cannot grasp how the conditional formatting works. I have a sheet like that:
I have defined 2 cell styles "Low" and "High" and I want cells in columns A-E to have one of these styles ...
- 350
8
votes
1
answer
10k
views
Use a Python function as formula in LibreOffice Calc cells
Since LibreOffice Calc supports macros, and the builtin functions are somewhat limited, I'd like to write my own functions in Python and then use them as formulas in individual cells. Ideally, I would ...
- 1,990
7
votes
4
answers
5k
views
Why does LibreOffice Calc ask about updating links in Excel 2007 .xls file when Edit | Links is disabled?
(I don't believe this is duplicate of LibreOffice Calc prompting to update “links to other files”. Why?. See below for why.)
The title basically says it. I have an .xls file originally created with ...
- 1,149
7
votes
5
answers
5k
views
Can I retrieve and access a simple JSON object via HTTP in LibreOffice Calc?
I've located a web API resource that I'd like to access from LibreOffice Calc.
It returns a simple JSON/JavaScript object that's basically just a handful of name value pairs, one of which I want to ...
- 4,573
6
votes
2
answers
10k
views
Calculate sum of products in LibreOffice Calc
I am not that proficient in using Excel/Calc and I have a simple question.
I have a table of my studies, my grades and the respective credit points on those.
study grade cp
a 2.3 8
b ...
- 6,644
5
votes
2
answers
9k
views
LibreOffice Calc prompting to update "links to other files". Why?
I have a spread sheet that recently started asking "This file contains links to other files, should they be updated". I have been using this file for a while and don't know why it started, what it ...
- 6,606
5
votes
2
answers
5k
views
How do I select non-adjacent ranges in libreoffice calc?
I'd like to perform an operation on a set of non adjacent ranges in LibreOffice calc.
I can type: C4:GA4 into the "name" box in order to select a single range. Is there a way to select non adjacent ...
- 151
5
votes
2
answers
6k
views
Extracting named groups of regular expression in LibreOffice Calc
In Python 3, it is quite easy to extract text from named groups as shown in the following example:
import re
myStr = r"4000/2000/5000/7000"
reObj = re.compile(r"""(?P<g1>\d+) # a capturing ...
- 182
5
votes
5
answers
11k
views
How to convert text to numbers for all cells in a spreadsheet?
I have a spreadsheet containing values that are entered as text by having an apostrophe ' in front. I tried the search and replace function, but for some reason it couldn't find those cells containing ...
- 1,091
5
votes
1
answer
2k
views
LibreOffice equivalent to SQL "count(*) group by some_column"
I have a spreadsheet
A | B
-----------
alice | 100
bob | 100
carol | 200
dave | 100
edith | 200
I want to basically count(*) group by B, the result being
100 | 3
200 | 2
How to do this in ...
- 2,148
3
votes
4
answers
4k
views
How do you find the maximum value in a column, skipping to every nth value?
Using LibreOffice's Calc program (nearly the same functionality as Microsoft Excel) I have a set of numbers in column T, which represent numbers for the week (with labels in S). Each week's worth of ...
- 33
3
votes
0
answers
353
views
Switch off autocorrect in libreoffice calc for specific document, column, cell
There is a way to completely turn off autocorrect in libreoffice calc. However, I would prefer turn off autocorrect in less scale: document, worksheet, column/row, cell. How it can be obtained?
- 385
3
votes
4
answers
11k
views
Adding a constant in LibreOffice Calc
How do you add a constant to a group of cells in LibreOffice Calc?
For example, here are four cells:
47 28 35 62
Now add 3 to each cell:
50 31 38 65
- 981
2
votes
1
answer
1k
views
How to count the number of cells in a range containing a specific (sub)string with Excel/Calc?
Using Excel/Calc I have the following situation:
a range of cells containing values; assume it's A1:B10,
and a string I want to look for; for example, assume it is contained in C1.
Now what I want ...
- 1,986
1
vote
1
answer
2k
views
Get cell range for a function argument from the text of another cell in libreoffice-calc
Consider some libreoffice-calc function called MYFUNC which takes a cell range for example A2:B40 as an argument, so I can call MYFUNC(A2:B40).
Now I want to be able to write A2:B40 as text into ...
- 465
-2
votes
1
answer
164
views
How to compare number with number in other column and get corresponding value
If suppose I have data like
column1 column2
504231 504109
504109 504201
504201
504299
then I used the following formula =IF(ISNA(VLOOKUP($A2,$B$2:$B$5,1,0)),"",VLOOKUP($A2,$B$2:$B$5,1,0)...
