Spreadsheet component of the LibreOffice suite

**29**

votes

**2**answers

46k views

### How to color rows based on cell value in Open/Libre Office

How do you automatically set the background color of an entire row based on the value of a specific column in that row in Open/Libre Office? There are similar questions for Excel, but this doesn't ...

**22**

votes

**5**answers

16k 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 ...

**21**

votes

**1**answer

17k 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 =...

**17**

votes

**3**answers

14k 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 ...

**12**

votes

**4**answers

16k 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.

**12**

votes

**2**answers

10k 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 ...

**10**

votes

**4**answers

8k views

### How to add month to date in LibreOffice calc

This seems like an obvious question.
I have a date column, and I want to create a formula to increase it by one month for each column.
10/2013, 11/2013, 12/2013, 1/2014, 2/2014, ...
How do you add ...

**9**

votes

**3**answers

6k 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 ...

**8**

votes

**3**answers

8k views

### How do I get a chart in LibreOffice Calc to have regular time intervals when the data is not regular?

I have an area chart in LibreOffice Calc where I would like the X axis to be measured in one month intervals spaced evenly apart.
The data going into the graph, however, is not regularly spaced at ...

**7**

votes

**1**answer

12k views

### How do I get data or percentage displayed in libreoffice pie chart?

I need to get a pie-chart with values - can libreoffice do it?

**7**

votes

**2**answers

3k 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 ...

**6**

votes

**3**answers

6k views

### Working with lots of data in LibreOffice

I have 150000 observations, which I am trying to work with in LibreOffice (new name for OpenOffice.org). The main thing I want to do is to have some formula calculated for all of these observations. ...

**6**

votes

**1**answer

10k 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 ...

**6**

votes

**2**answers

7k views

### LibreOffice conditional formatting based on string value: strange behavior

Here is what happen when I apply conditional formatting:
(yes should appear green, no should appear red)
I have made sure AutoCalculate is enabled.
Is it a known bug, or am I misunderstanding ...

**6**

votes

**2**answers

2k 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.
I use cell color and cell borders to group and distinguish groups of cells in my spreadsheet.
I have bold horizontal borders every 4 rows. This is a ...

**6**

votes

**5**answers

12k 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 ...

**5**

votes

**5**answers

44k views

### How to separate number by its decimal point in Excel/Calc?

I've got a number in a spreadsheet like so: 28.686279
When I open this sheet in either LibreOffice Calc or Microsoft Excel, is there a formula function that returns the "whole number", I.e. 28. Also, ...

**5**

votes

**2**answers

4k views

### Move rows with the keyboard in Calc

How might one move a row to a different location with the keyboard? I have found this guide for the mouse but due to disability I have trouble using the mouse for this.
Note that I am not looking to '...

**5**

votes

**2**answers

10k views

### VLOOKUP returns #N/A

I have a table of data in a sheet (blad1.A2:I115). The first column (A) is a list of numbers that is being referenced. The last column (I) has the value I want to get returned.
This is what I tried:
...

**5**

votes

**3**answers

3k 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 ...

**5**

votes

**2**answers

6k 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 ...

**5**

votes

**5**answers

12k views

### Quickly fill blank cells in a list in LibreOffice Calc

For example:
Cell A1 = 2/3/2012
A2 is empty
A3 is empty
A4 = 3/7/2012
A5 is empty
A6 is empty
A7 is empty
What I want to do is:
select the ...

**5**

votes

**1**answer

327 views

### Default font and cell padding when opening files in LibreOffice/OpenOffice Calc

I've seen solutions for setting a default font when creating new files. What about a default font (and cellpadding) when opening an existing CSV file? LibreOffice seems to always default to Liberation ...

**4**

votes

**2**answers

3k views

### What does Libre Office do to an existing Excel sheet to bloat its size?

I try to avoid using Libre Office on existing Excel created workbooks because of the potential for unpleasant results. In this case Libre Office bloated the size of the workbook for some reason ...

**4**

votes

**2**answers

2k views

### LibreOffice super/subscripts in axis labels

Is it possible to use superscripts or subscripts in plot axis labels in LibreOffice Calc? If not, is there a good work-around that will produce the same effect?

**4**

votes

**2**answers

368 views

### How does the group feature in LibreOffice Calc work?

LibreOffice Calc has as Group feature. The official help site just tells you where to find the button and what hotkey to use, but doesn't explain what grouping actually does:
https://help.libreoffice....

**4**

votes

**4**answers

7k views

### Finding min/max values in column where cell matches something

I have a spreadsheet laid out kind of like this:
Date Project#
2012-01-01 130
2012-01-02 153
2012-01-03 153
2012-01-04 130
2012-01-05 130
2012-01-06 130
And I want to get ...

**4**

votes

**1**answer

2k views

### LibreOffice: Make all references in selection absolute

Suppose I have a range of cells which contain relative references in localc. Is there a quick way to select this range and change every reference to an absolute one i.e. adding Dollar signs in each ...

**4**

votes

**2**answers

1k views

### How can I split the wedges of a pie chart to show extra information per wedge in consecutive rings?

I'm plotting disk partitioning and available space.
I have a pie chart showing the partitioning: e.g. 30% /, 70% /home.
In the second ring I want to show that 90% / is full, 10% of / is empty. 10% /...

**4**

votes

**2**answers

5k views

### How to rearrange or reorder the contents of a Sheet (LibreOffice Calc)

How can I rearrange or reorder the contents of a sheet in LibreOffice Calc?
To be more specific, here is my precise situation:
I have a spread sheet of 1 column and 130 rows. Each row contains a ...

**4**

votes

**1**answer

752 views

### Openoffce Calc - Substitute space with line break by function

in my calc document for example cell A1 has the value
A1:"AB 0123"
and in cell B1 I need the same value with a line break instead of the space like
B1:"AB\n0123".
How can I archive this?
I ...

**4**

votes

**2**answers

1k views

### LibreOffice: stop automatic formatting of text into a formula?

In LibreOffice, I'm entering some text into cells, which happens to look like a math problem:
-5 / -10 / -15
The program assumes I want to calculate this, and sticks an equals sign automatically in ...

**4**

votes

**2**answers

952 views

### Does libreoffice have a math concept like 'infinity'?

Does libreoffice calc have special numbers corresponding to -infinity and infinity similar to python's numpy?
They would obey rules like:
x = some number
infinity + x = infinity
-infinity + x = -...

**4**

votes

**1**answer

15k views

### Can you use infinity in a cell reference in LibreOffice (or any other spreadsheat)? [duplicate]

Possible Duplicate:
Can you reference an entire column in OpenOffice Calc (like A:A in Excel)?
I'm using a Sum of a column, and it keeps growing, so I have to update the reference to which ...

**4**

votes

**1**answer

107 views

### Removing functions from a column, but keeping data (convert to normal text)?

I have a column that contains the results of a few different formulas. I want to convert all of these cells to normal text, getting rid of the formulas.
I know I'm missing something obvious here.

**4**

votes

**1**answer

1k views

### How to make a LibreOffice Calc XY (scatter) plot with named dots?

I have a simple XY scatter plot made from a 2-column spreadsheet. When I hover over a dot, I can see the ID of the dot (e.g. "Data point 8456").
I would like to know if I could have the content of a ...

**4**

votes

**2**answers

2k 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 ...

**3**

votes

**2**answers

5k views

### Excel/Calc/Whatever: Flip my axes - ie reflect my table across the diagonal

Lets say I have a table and I want it flipped across the diagonal. So instead of X by Y its Y by X...
Before:
A B C
RED 1 2 3
BLUE 4 5 6
Becomes:...

**3**

votes

**5**answers

7k 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 ...

**3**

votes

**3**answers

5k 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

**3**

votes

**2**answers

98 views

### Perform Sum over Conditional Products

I do have a list like this
╔═════╦═══╗
║ 1.5 ║ A ║
║ 2.5 ║ B ║
║ 2.5 ║ ║
║ 5.2 ║ A ║
║ 6.4 ║ C ║
╚═════╩═══╝
and want as result:
╔═══╦═════╗
║ A ║ 6.7 ║
║ B ║ 2.5 ║
║ C ║ 6.4 ║
╚═══╩═════╝
So ...

**3**

votes

**2**answers

7k views

### How can I plot a grouped histogram with LibreOffice Calc?

I would like to produce a grouped categorical histogram with LibreOffice Calc.
With grouped I mean an histogram with N columns instead of a single one for each category.
Something like this:
Any ...

**3**

votes

**3**answers

2k 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 ...

**3**

votes

**1**answer

3k views

### Can two rows be reordered into one column in OpenOffice/LibreOffice Calc?

Is it possible in Open/LibreOffice Calc to take the values from a set of two or more rows, and display them in one column? In order words, make a separate column which derives its values from the ...

**3**

votes

**2**answers

98 views

### How to add a Table of Contents to a LibreOffice Calc file?

Adding a ToC in LO Writer is no problem, but how to insert a ToC in a ods file? For a workbook with tables longer than one page to be distributed as printout (not as file), it would be nice to have a ...

**3**

votes

**1**answer

9k 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 ...

**3**

votes

**1**answer

32 views

### Is there a way to add and/or logic into the criteria field?

Using LibreOffice Calc with the expression SUMIF, is there a way to make the criteria field look for 2 types of values using OR logic? Or do I have to make 2 separate SUMIF's and just add them up?
A ...

**3**

votes

**1**answer

16 views

### How to sum the values of one column depending on the value of another?

with LibreOffice Calc I'm trying to set something up so it sums the values of column D IF the corresponding row in column A contains certain words. So far I know sumif works, but how do I set it up so ...

**3**

votes

**2**answers

116 views

### Match cell content based on line break

I take data from a helpline call in site and transfer it to Linux Libreoffice Calc (LC).
Column C cells have 2 sets of data - two telephone numbers - 1 being the caller and 1 being the forwarded ...

**3**

votes

**1**answer

493 views

### Open only the first 10 lines of large CSV file in LibreOffice Calc

I will start receiving many large CSV files, of which only the first few lines are of interest to me. I was hoping to open them by piping only the first few lines to Calc, but this doesn't work:
$ ...