Spreadsheet component of the LibreOffice suite

**2**

votes

**1**answer

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

**0**

votes

**2**answers

7k views

### LibreOffice Calc SEARCH and FIND functions

I am trying to process some data in Calc. One of the steps involve finding if a certain string is part of one of the column. I tried using FIND and SEARCH functions. Both behave in the same way and I ...

**6**

votes

**3**answers

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

**2**

votes

**0**answers

520 views

### LibreOffice Calc, how to filter rows based on interval

Simple question: I would like to filter a spreadsheet on LibreOffice 3.4.4 Calc, based on values of a second spreadsheet column. How can I do that ?
I would like intersection and, if possible, ...

**0**

votes

**1**answer

442 views

### apply seach criteria to whole cells

I am trying to use MATCH() and/or LOOKUP() in LibreOffice Calc. I have the "Search criteria = and <> must apply to whole cells" and the "Enable regular expressions in formulas" options selected. ...

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

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

**1**

vote

**3**answers

2k views

### Why can't I use fill down in OO Calc?

I cannot use Ctrl+D in Calc. It grayed out, disabled. Look at the image.
And there are just test numbers I wanted to fill down.
Can somebody help? The same is in LibreOffice Calc.

**4**

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

**2**

votes

**1**answer

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

**3**

votes

**2**answers

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

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

**1**

vote

**1**answer

211 views

### ignore or override current document page formatting with predefined settings automatically libreoffice

When opening a document made by someone else, I would like the margins to automatically be set to 0.4 cm, the page orientation to landscape and page size to A3.
My dad gets emailed a spreadsheet ...

**0**

votes

**1**answer

267 views

### How do I get numbers before and after a colon within a cell in Calc?

I have some cells which contain times entered in by users, which are written in the following format: mm:ss. There are never any hours. The seconds always have leading zeroes. The minutes may or may ...

**0**

votes

**1**answer

103 views

### Referncing a cell, based on a cell's value in Spreadsheet

I understand how referencing a cell works, example being
=Sheet1.A1
However, if I'm in Sheet2 and cell B1 contains Sheet1, and cell C1 contains A1.
Is there any way to write the reference such ...

**1**

vote

**1**answer

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

**1**

vote

**1**answer

77 views

### Can I use two cells in the same column as a Y axis in Libre/Open Office?

I have data arranged as following:
What I'd like to do is create a graph that looks something like the following (which I generated with graphics software):
So, for example, to generate the ...

**1**

vote

**1**answer

790 views

### Is it possible to create a chart in OpenOffice calc using columns and rows?

Is it possible in OpenOffice Calc to create a single chart where one set of data is derived from columns, and another set of data is derived from rows?

**11**

votes

**4**answers

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

**0**

votes

**1**answer

108 views

### Retrieve info from a column with some empty cells from ODF Spreadsheet

I have this ODF Spreadsheet on LibreOffice open, at it has one column with a lot of data that I wish to retrieve, however, I also have, a lot of white cells. Something like this:
emailA
emailB
...

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

**1**

vote

**1**answer

513 views

### LibreOffice Calc: Formatting Time is adding ' char

I have some data in calc like so:
05:00:27
01:27:02
07:29:57
06:22:01
I select the cells, and right click 'format cells', and choose Time -> 876613:37:46. When I do this, it adds an apostrophe ...

**1**

vote

**2**answers

2k views

### LibreOffice Calc: How to remove a character from multiple columns

For some reason, when I import data from a certain unnamed source (toggl.com), some of the time columns have an apostrophe in them.
When I have this data loaded in Calc, is there a quick, easy way ...

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

**2**

votes

**2**answers

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

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

**0**

votes

**1**answer

154 views

### Math function: What's the “this line” placeholder?

When I want to multiply a field I can say =J9*K9.
But what if I want this over a selection of rows? I've selected Line 6-25 and for all I want to say =J{THIS_LINE_NUMBER}*K{THIS_LINE_NUMBER}*.
How ...

**4**

votes

**2**answers

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

**0**

votes

**1**answer

897 views

### Creating a column of working days in LibreOffice Calc

In LibreOffice Calc, I would like to create a column of working days, for example:
[...]
2012-01-30 [Monday]
2012-01-27
2012-01-26
2012-01-25
2012-01-24
2012-01-23 [Monday]
2012-01-20
2012-01-19
...

**2**

votes

**1**answer

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

**1**

vote

**2**answers

1k views

### Table exported in XLS format doesn't show anything in Libre-Calc

A web application exports some tabular data in XLS format. The XLS file shows the content alright when opened in MS-Office but shows blank sheet when opened in Libre-Calc.
My versions:
Ubuntu 11.10
...

**1**

vote

**1**answer

831 views

### Calc - How to combine rows from multiple sheets into 1 main sheet

I have several statements separating my various bank accounts in Calc, they all use the same columns and have a Date column which I sort them by.
I want to create a 'Overview' sheet which contains ...

**14**

votes

**1**answer

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

**0**

votes

**1**answer

914 views

### Double valued drop-down menu in libreoffice-calc

Suppose I have a spreadsheet like this in libreoffice-calc:
Now I want to have a drop down list in A3 to choose between the strings in E3 (Text a) and H3 (Text b) such that automatically cell B3 ...

**1**

vote

**1**answer

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

**1**

vote

**1**answer

3k views

### Expert-level tutorials on LibreOffice Calc

This question was posted on the LO Facebook page:
Folk, Any one has Libre office expert level tutorial for CALC ? I need it to present in my company since we are migrating to MS Office to Libre ...

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

**2**

votes

**1**answer

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

**1**

vote

**1**answer

678 views

### How to copy & paste from a cell in Libre Office Calc

I have an Excel format spreadsheet that was sent to me by a colleague. It's a list of test usernames for an app we're building.
When I try to copy one of the usernames or passwords from the cell in ...

**0**

votes

**1**answer

2k views

### Adding times in the format hh:mm:ss with sumif in Libreoffice-calc?

Suppose I have a column which contains time-deltas in the form hh:mm:ss. Now I want to add those times using sumif and get the total time in the format hh:mm:ss, too. Is there a way to do this in ...

**1**

vote

**1**answer

244 views

### LibreOffice Calc function localization

I'm using LibreOffice 3.4.3 on Lubuntu. The locale I'm using is es_AR, I installed LibreOffice with sudo apt-get install libreoffice, then installed the Spanish language package with Synaptics ...

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

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

**0**

votes

**1**answer

3k views

### Copy chart from Calc spreadsheet to Writer document without linking in Libreoffice?

I've copied a chart in one of my Libreoffice 3.4.3 Calc spreadsheets into a Writer document in Mac OS X 10.6.8. However, I've noticed that the chart in the Writer document is linked to the one in the ...

**1**

vote

**1**answer

235 views

### Rearrange cells in a Open Office spreadsheet, from data in cols to data in rows

I have this spreadsheet (Open Office) and all the data is filled in so they form a col,
and I would like to change this so they fill a row instead.
Kind of mirror the doc in both axis.
This is how ...

**2**

votes

**1**answer

317 views

### Are there nonlinear optimization implementation for OpenOffice Calc?

Are there nonlinear optimization implementation for OpenOffice Calc?
I found one: http://extensions.services.openoffice.org/project/NLPSolver . But the methods provided there are evolutionary ...

**1**

vote

**1**answer

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

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

**2**

votes

**1**answer

231 views

### how to get Libre Office Calc to tell me when there are invalid characters

I have a spreadsheet in XLSX format that was generated by a python program. it turns out that the python program had left some invalid characters in the file. When I open the file in Excel, it at ...

**1**

vote

**2**answers

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