Spreadsheet component of the LibreOffice suite

learn more… | top users | synonyms

1
vote
1answer
78 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?
17
votes
4answers
8k 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 ...
1
vote
1answer
641 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
6k 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
641 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
1answer
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
7k 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 ...
16
votes
1answer
13k 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
909 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
811 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
296 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
7k 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
278 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
379 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
4k 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
272 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
273 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
11k 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
23k 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
503 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
787 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
132 views

Incremental search in libreoffice-calc

Is there an incremental search feature in libreoffice-calc similar to that one in emacs or firefox?
3
votes
1answer
1k 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 ...