Spreadsheet component of the LibreOffice suite

learn more… | top users | synonyms

3
votes
1answer
10 views

Libreoffice, how to copy from Writer and pasting unformatted in Calc, by default?

I have the revision of a translated text, made by the client, I am the third party reviewer and have to fill up an .xls module with all the source texts, the translated one, and the amended by the ...
1
vote
1answer
10 views

stacked area chart with secondary data range

What I have is this: and I am trying to achieve this: Using Calc 5, how do I manage the second column to be recognized as secondary data range?
0
votes
1answer
35 views

Using libreOffice Calc function in macro

I am trying to create a basic macro function in calc to perform a hexadecimal to binary conversion with more then 10 bits. But I've got a lot of troubles. First, where can I find actual ...
2
votes
1answer
21 views

How can I access the spelling check correction in LibreOffice Calc?

LibreOffice Calc 5.0.4.2 on Ubuntu Linux 15.10 underlines misspelled words but does not display spelling check suggestions. Is there a way to add spelling check suggestions to the right mouse click ...
1
vote
0answers
20 views

How can I supress zero values in a LibreOffice line line chart? [duplicate]

I'm making a line chart counting users of various releases of a piece of software over the years. This is a very spiky line chart, where as new versions come into existence and then retire, lines ...
1
vote
1answer
31 views

Manually “drawing” line graph chart and export data

I would like to manually create a line graph and then sample the created line to generate a data series from it. Basically I have an idea, what a plotted values should look approximately and would ...
1
vote
1answer
19 views

MDETERM gives Err:502 on non-contiguous range of cells

I have the following setup: A 3x3 matrix in the cells A1:C3 and a 3x1 vector in D1:D3. I would like to calculate the determinant of a new matrix formed by the 1st column of the original matrix, the ...
0
votes
1answer
23 views

Get the value of one cell based on the value of another

Edited with additional context: Embedded in a LibreOffice-5 Calc spreadsheet I have a two column array where one column contains dates and the other column values. I wish to select the row based on ...
0
votes
2answers
31 views

Conditional Formatting based when equal/not equal to another cell in the same row

I have a spreadsheet like this in LibreOffice A | B | C | D -------+----------+---------+--------- 1 | A | 1 | B 2 | C | 2 | C 3 | D ...
4
votes
2answers
131 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: ...
3
votes
2answers
89 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 ...
0
votes
1answer
33 views

Is it possible to automatically separate line/scatter plot data into different series based on an additional column?

My data looks something like this: H, 2015-12-11T19:44:34, 3270 W, 2015-12-11T19:44:34, 2150 H, 2015-12-11T19:49:35, 3370 W, 2015-12-11T19:49:35, 2290 ... As a one-off, I can get ...
1
vote
1answer
32 views

Connect data points around missing data with different style of line on graph

I have an libreoffice-calc spreadsheet with some results of daily measurements. Some days are missed for some reasons. I want to create a graph showing this data, and I want to highlight all missed ...
0
votes
1answer
22 views

Using INDIRECT in cell validity

I have a spreadsheet contain 3 sheets. I want to set validity of column E of the first sheet based on text in the column D. The relation between these two columns are placed in the second sheet as ...
3
votes
0answers
31 views

Disabling spell checker in OpenOffice Calc (or LibreOfffice Calc)

Is there a way to disable the spell checker in OpenOffice Calc (or LibreOffice Calc) without disabling it for the other programs in the office suite? I would prefer to just disable it for a range of ...
1
vote
0answers
14 views

Libre Office - merge sheets

In one file I have two sheets, which share some of the columns. I'd need to merge the sheets based on column name, if the column does not exist, add it. Ie. I have Sheet 1 ...
1
vote
0answers
23 views

Change color for all set borders

Having a table with some complex borders. How can I change the border color for all borders in the table? Know I can change one by one, but that is a hassle. For example: +-----+--+ |1A 2A|3A| |1B ...
0
votes
2answers
51 views

LibreOffice Calc IF Function returns True when should return cell

I have been successfully using the IF function up until the last few days. I used this statement to return one of 2 values depending on whether or not an item is shaped: ...
1
vote
0answers
68 views

OpenOffice / LibreOffice ugly font rendering

I'm using Calc to generate some graphs of data I have collected. But somehow the font looks increadibly ugly in all of LibreOffice. I'm using Noto Sans CJK JP Light in this chart but pretty much every ...
1
vote
0answers
36 views

LibreOffice Calc changes letter from uppercase to lowercase under certain circumstances

I want to enter SXer into a field, however the X gets automatically converted to x. If I only write SX, the X stays the way it is. If I write SXEr, all letters stay as they are. But somehow I can't ...
1
vote
0answers
26 views

Libreoffice Calc adds extra commas or semicolons to formulas and doesn't return the answer

I am using LibreOffice 3.5.4.2 Build ID: 350m1(Build:2) On a Debian Jessie machine. Whenever I input any inbuilt calc/excel function, it adds random commas (sometimes semicolumns) between ...
0
votes
0answers
12 views

How to create a new Libreoffice-Calc document from template with specific name and save it immediately via commandline?

I'm looking for a way to create a new spreadsheet from a template, name it and save it immediately with the help of the command-line. I tried the -n switch, but it creates a temporary file called ...
0
votes
0answers
33 views

LibreOffice (Calc) | Search & Replace | unable to look for 'comments'

CTRL+F enables the SearchBar (quick search) CTRL+H brings up Find & Replace dialog (more options) I'm unable to find a way to look for comments that are within with a cell (with ability to ...
1
vote
1answer
20 views

LIBREOFFICE calc file can't be open by different computer

I have a .ods file on my USB drive and want to edit it at home and at work. But all documents I create are "stuck" to the computer I created it on. I have tried to save the file with password for ...
0
votes
0answers
32 views

Libre Office Calc Issue: Summarizing columns with too many rows

I'm writing a script that reads data taken from a database. THe data is modified (de-duplicated) and put into a .csv file I have a problem as I want to find the sum of a particular column in excel. ...
1
vote
1answer
41 views

Spreadsheet formula to display most recent prices

I have created a LibreOffice Calc spreadsheet with columns A-C. In column A, I have a list of parts purchased (in chronologically ascending order). In column B, I have a list of corresponding ...
2
votes
3answers
78 views

How do you properly align average and standard deviation outputs in a spreadsheet?

I am not sure of the best way of formulating this question right now so I will use an example using random numbers. I start with values assigned to IDs, so that each ID can be n=1, n=2, ... etc ID ...
0
votes
1answer
60 views

Select data from different sheets in LibreOffice Calc

I have three sheets 1,2 and 3. There are three columns on sheet 2 and 3: name (A), surname (B), age(C). How can I select men whose age is >=30 from sheet 2 and 3, order them by age and show the result ...
0
votes
1answer
16 views

interrogating Bank data - in OpenOffice 4.1.1

I would be very grateful for any assistance with the following... I have a worksheet of raw bank transaction data...I want to interrogate the data to display payments to the same source over the ...
0
votes
1answer
16 views

Edit some Google Files on the desktop without installing Google Drive

I would like to edit a Google Tabel on my Desktop with out installing the Drive Client (I really only need this file) and with out using the browser. What can I do? Is there a plug-in for excle or ...
0
votes
1answer
31 views

Reference a cell with input from another cell

The final objective is to reference the last populated (higher row number) cell from a column (its value). I found a function that returns the row number of the last populated (higher row number) ...
0
votes
0answers
15 views

Currency aware summation in LibreOffice Calc

I have a spreadsheet with columns of the form: Date A B Total 01/01/15 $100 £200 xxx 08/01/15 $110 £195 xxx 15/01/15 $115 £205 xxx where A and B are ...
0
votes
1answer
18 views

Shift down cells on enter in Libreoffice Calc?

In Libreoffice Calc, how does one shift down cells on press Enter, and if I want to go down cells by pressing ↓? Is there any setting that controls this?
1
vote
1answer
25 views

How to rearrange columns in libreoffice calc as per column totals?

I have the final row as total which has the sum for each column.I wish to rearrange the columns such that the columns from left to right will be arranged descendingly as per column total.
1
vote
1answer
69 views

Is there a way to save a filter in libreoffice calc?

I keep switching between unfiltering and filtering on these three checkboxes Is there a way I can save this filter so that I could click only once and have my sheet filtered instead of clicking on ...
0
votes
0answers
19 views

How to show the total number of results in a “Find All” search in LibreOffice Calc?

LibreOffice Calc provides the useful search function to "Find All" instances for a search term. It outputs a result window which looks as follows: What I miss is the total number of found search ...
0
votes
2answers
49 views

Entering notes into LibreOffice Calc and OpenOffice Calc

In LibreOffice Calc and OpenOffice Calc, there is often a need to quickly enter several rows of text that will not be part of any calculation area. This is often done to write notes relevant to the ...
1
vote
1answer
2k views

Switch Calc (or Excel) formulas to English language

I'm not using an American/English version of Calc/Excel so, every time I want to post here (or on other "web-places") or when I want to try formulas posted by others I need to "translate" them somehow ...
0
votes
0answers
29 views

LibreOffice Calc Automatic Detection of Duplicates

Is there any way you can automatically detect a duplicate of any previous cell, the very moment you entered the text? like when you input the word "data" it will be automatically highlighted because ...
3
votes
2answers
44 views

Libreoffice cell/axis format: Insert a newline?

The goal is to plot a value against date/time in the format DD/MM HH:MM but to have the time and date on separate lines. I'm currently using source formatting but this isn't necessary. A couple of ...
1
vote
1answer
23 views

How can I disable dates in the Calc?

I see that all spreadsheets are urgent to recognize your 13.12 as a date. Particularly, I am struggling with Calc, who is said to use English language to import dots as decimal separators. But, ...
0
votes
1answer
76 views

Dynamic array/matrix generating within formula

The following formula: =TRIM(A1:A20) would make no sense; you typically should decide a column, write =TRIM(A1) on first cell and copy until =TRIM(A20) ... BUT ... suppose you want/need to use it ...
0
votes
1answer
21 views

How do I look up data from another table?

In this fictional example, I have two tables; one with employee information and pay rate: The second with dates and the corresponding number of hours the employee has worked on that day (note that ...
0
votes
0answers
33 views

LibreOffice Calc equivalent to Excel “Custom Views” feature

Custom Views are an Excel feature that comes very handy when, for example, you want to save a filtered version of a worksheet and then pass to another vision of the same worksheet with different set ...
1
vote
1answer
393 views

LibreOffice Calc equivalent to Excel “Evaluate Formula” feature

Excel has an "Evaluate Formula" feature (it's located in the Excel Formulas ribbon among the Formula Auditing group, as shown in the picture below). Is there an equivalent to that Excel feature in ...
0
votes
2answers
70 views

How to give duration in excel or libre calc?

I have duration data like 5:23, etc... in m:ss format. I already typed the data, but I cannot create a diagram, because none of the mentioned excels understand it. If I change the data type to user ...
2
votes
2answers
103 views

A Conditional Format to change the background color but keep the Date format

I have and OpenOffice Calc document that has a "table" with Text, Numeric and Date columns. I have a conditional format making every odd row have a different background color so it will be easier to ...
0
votes
1answer
53 views

How to undo autocorrect in libreoffice calc

I would like to prevent libreoffice calc to make autocorrecion in some cases in some cell. For example, sometimes libreoffice decides that some text I enter is a hyperlink and it formats the cell as a ...
0
votes
0answers
31 views

Switch off autocorrect in libreoffice calc for specific document, column, cell

There is a way to completely turn off autocorrect in libreoffice calc. However, I would prefer turn off autocorrect in less scale: document, worksheet, column/row, cell. How it can be obtained?
0
votes
1answer
43 views

How to copy tab-contained text from Excel without quotes?

I've noticed that a cell that contains a tab is copied with quotes embedded. I'm looking for copying such kind of a cell without the quotes. Illustrative Example I've 5 cells; A1, A2, A3, A4, and ...