Spreadsheet component of the LibreOffice suite

learn more… | top users | synonyms

4
votes
2answers
2k 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
1answer
739 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
2answers
7k 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
1answer
2k 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
1answer
252 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
1answer
351 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
1answer
106 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 ...
2
votes
1answer
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
1answer
80 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
1answer
1k 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?
18
votes
4answers
9k 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
1answer
111 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 ...
4
votes
1answer
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 ...
2
votes
1answer
652 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 ...
2
votes
3answers
4k 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 ...
6
votes
2answers
7k 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
2answers
1k 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
1answer
4k 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
1answer
173 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
2answers
670 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% ...
1
vote
2answers
1k 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 ...
5
votes
1answer
8k 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
2answers
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
1answer
1k 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 ...
17
votes
1answer
14k 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
1answer
1k 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
1answer
935 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
1answer
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
2answers
4k 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
1answer
1k 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
1answer
825 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
1answer
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
1answer
313 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 ...
4
votes
1answer
8k 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 ...
6
votes
3answers
4k 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
1answer
4k 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
1answer
281 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
1answer
390 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 ...
2
votes
1answer
4k 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 ...
8
votes
3answers
5k 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
1answer
277 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
2answers
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 ...
1
vote
1answer
283 views

How to sum the multiplications of rows, being the multiplier variable in a spreadsheet

I have this spreadsheet: I want to calculate the total income, but since the different rows are in different currencies the conversion must be done first. The currency values are mapped to codes in ...
10
votes
4answers
12k 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.
3
votes
3answers
3k 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
2answers
6k 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
5answers
24k 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, ...
1
vote
1answer
514 views

Any way to make interactive graphs in LibreOffice like Google Doc's Trend chart?

Google Docs has a nice trend viewer that can be created just like any other chart: Is there any way to get an interactive graphic like this in OpenOffice? Manually adjusting the axes is really ...
1
vote
2answers
809 views

How to make LibreOffice Calc to not to hide unfit content of a cell when the folowing cell contains data?

If I enter a long string into a cell it (the string) is normally displayed in its whole, taking a part of the next cell area. Line A (1) on the picture below is an example. If I enter a long string ...
1
vote
1answer
134 views

Incremental search in libreoffice-calc

Is there an incremental search feature in libreoffice-calc similar to that one in emacs or firefox?