Spreadsheet component of the LibreOffice suite

**12**

votes

**1**answer

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

**9**

votes

**4**answers

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

**8**

votes

**4**answers

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

**7**

votes

**3**answers

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

**6**

votes

**2**answers

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

**5**

votes

**3**answers

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

**4**

votes

**3**answers

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

**4**

votes

**2**answers

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

461 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% ...

**3**

votes

**4**answers

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

**3**

votes

**2**answers

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

**3**

votes

**2**answers

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

**3**

votes

**3**answers

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

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

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

**3**

votes

**2**answers

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

**3**

votes

**2**answers

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

**3**

votes

**1**answer

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

**3**

votes

**3**answers

942 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

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

**3**

votes

**2**answers

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

**3**

votes

**1**answer

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

**3**

votes

**2**answers

1k views

### How to efficiently insert a table from R into a Word document?

I am creating a Word document (.docx) using LibreOffice. I need to include some numeric results I calculated in R. They look like a simple table of numbers, with text captions for the rows and ...

**3**

votes

**2**answers

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

**3**

votes

**1**answer

878 views

### How can I apply a date filter in LibreOffice?

I have a Calc-sheet where the first column is a date and the second column a description of a task. I would like to filter the table so only the task with a date equal to today and the future will be ...

**3**

votes

**1**answer

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

**3**

votes

**2**answers

4k views

### Calculations in terms of degrees minutes seconds in excel , libreoffice/openoffice or any other free spreadsheet

My dad's gotten into astrology, so he needs to do calculations for it - and most books use the Degrees Minutes Seconds nonmenclature - is there any way to set excel or open office calc to handle ...

**3**

votes

**1**answer

336 views

### Find last value in a cell range that meets a condition

I'm looking for a formula in LibreOffice Calc to find the last value in a range that meets a condition. Here is sample data:
A B C D E F
...

**3**

votes

**2**answers

146 views

### How do I convert a delimited list to a multicolumn table in excel?

I have a list like this:
Friut;Taste;Color;Other;Apple;Good;Red and Blue;1;Orange;Really Good;Orange;12
And I want to convert it by selecting every 4 delimitations and turning them into rows like ...

**3**

votes

**1**answer

212 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 = ...

**3**

votes

**1**answer

539 views

### LibreOffice Calc/Writer: How to get the dates for specifc weekdays per month?

I'm stuck with a problem in LibreOffice Calc or Writer:
I try to get a table of dates for every Monday, Wednesday and Friday per month, something that I can load every new month wich sets the dates ...

**3**

votes

**6**answers

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

**3**

votes

**2**answers

674 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

**0**answers

148 views

### Cut and transpose in LibreOffice

When using cut and pasting LibreOffice maintains exact formula used along with moving old references to point to the new location, however when using paste special: transpose it treats the past as if ...

**3**

votes

**1**answer

942 views

### Open Source Spreadsheet with Sensitivity Analysis

Is there any good Open Source spreadsheet program that has a solver for linear programs (either included or via an extension) that does a proper sensitivity analysis (shadow prices etc)? It seems like ...

**2**

votes

**3**answers

2k views

### libreoffice what is a fast way to paste special > text and numbers only (aka paste special values)?

I copy and then paste values in excel quite a bit (when I use Excel). When I use LibreOffice you can do the same thing (see related question here) but it is oh so tedious!
It gets especially tedious ...

**2**

votes

**1**answer

4k 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?

**2**

votes

**1**answer

1k views

### LibreOffice Calc: Fix first columns when scolling to the right

Suppose you have a table in localc where the first two columns contain names of people for example and the other columns some information about each person. If I have too much info-columns, I have to ...

**2**

votes

**2**answers

881 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?

**2**

votes

**1**answer

310 views

### LibreOffice Calc - apply style to neighbouring cells

I have a column with data and I applied a style to it with the help of Conditional Formatting. I now wish to apply the outcome of that style to the neighbouring column that holds other data.
Here is ...

**2**

votes

**1**answer

2k views

### How to count how many rows contain particular values in a particular set of cells in libreoffice and excel

This Calc file : http://dropcanvas.com/521xc/107 have a sheet called "Etat" and a sheet called "Total".
In Etat.F20 I have a formula that counts how many rows in Total have the value "Bug" in the D ...

**2**

votes

**1**answer

438 views

### Control-Click hyperlink to open in libreoffice-calc

When using the libreoffice spreadsheet program 'calc', clicking on a cell containing a hyperlink will immediately open it in a web browser. I would prefer to control-click it to open that link in a ...

**2**

votes

**1**answer

885 views

### In LibreOffice Calc, how can I control whether the column or row is incremented when pasting?

In my LibreOffice Calc spreadsheet, I have a column in one sheet where I want to reference values from another sheet. I want to copy one cell, paste into multiple cells, and have it correctly set the ...

**2**

votes

**1**answer

141 views

### Create pie chart with single sector separated from the rest?

Is it possible to use LibreOffice (I'm currently using 3.5.4) to create a pie chart where a single sector of the chart appears removed from the rest?
If so, how to do it?
Alternatively, can someone ...

**2**

votes

**1**answer

611 views

### Set a default document directory for Libre Office Calc?

How can I set a default document directory for Libre Office Calc? For Libre Office Writer? ... etc.

**2**

votes

**1**answer

554 views

### Libreoffice Calc: Copy&Paste like in a text document

In a text document, when you paste something from the clipboard, the new text is inserted into the document.
In a table document, when you paste, the existing cells are overwritten.
How can I get ...

**2**

votes

**0**answers

96 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:
$ ...

**2**

votes

**0**answers

63 views

### How do I conditionally format a cell that contains a dropdown list

Hi Guys I want to know how do I conditionally format a cell that contains a drop down list of 3 values. I want each value to represent a different coloured background when selected in the drop down ...

**2**

votes

**0**answers

29 views

### Libreoffice calc: pagebreak within row?

When printing a LibreOffice Table,
LibreOffice will automaticaly place the pagebreaks so that they appear after/before a row.
When having many tall rows this results in a heavy paper-usage. How can I ...

**2**

votes

**2**answers

4k views

### Is it possible to copy & paste a pivot table in a spreadsheet?

I have a spreadsheet where I use some pivot tables to summarize and highlight some relevant data.
Since I have to use multiple pivot tables, all with nearly the same data input but with slight ...