Spreadsheet component of the LibreOffice suite

learn more… | top users | synonyms

1
vote
1answer
2k views

LibreOffice / Excel: how to paste data with newline/endline characters inside a single cell

I'm trying to paste some data into a spreadsheet, but since the data contains \n characters within it, the cells below are overwritten. I want to paste the entire bit of data into a single cell. Eg: ...
0
votes
1answer
543 views

Rows disappear when removing duplicates libreoffice

I pasted a lot of data in Column A. Then I removed duplicates: Data > Filter > Standard Filter > More Options > No Duplication Now I'm stuck in row 2632, i.e. I can´t scroll up to rows ...
0
votes
0answers
462 views

How to fill in formulas automatically in LibreOffice Calc?

How can I get LibreOffice Calc to automatically fill in formulas from the current row into the next row? I have a spreadsheet for keeping track of my personal finances and I'd like to just press ...
0
votes
1answer
731 views

remove white space before delimiter with sed

I have data of the following format that I want to input into LibreOffice Calc: data | num | num | num | num For some reason LibreOffice does not think the string "3214 " is a number by default (...
1
vote
1answer
639 views

Find a Certain Cell based on other Cells in Excel/Calc

I have a spreadsheet: Beans B-kg Chips C-kg 1.4oz/12 0.47544 6.5oz/20 3.679 1.48oz/12 0.502608 7oz/12 2.3772 1.86oz/12 0.631656 8oz/20 4.528 and a ...
1
vote
1answer
412 views

Convert Text into a Formula in Calc

I have a bunch of cells in a spreadsheet like the following: 1.48oz/12 I am trying to replace the oz/ with * and then calculate it. So far I have: =REPLACE(G4,FIND("oz/",G4),3,"*") which yields: ...
0
votes
1answer
230 views

LibreOffice Calc STDEV and arrays

was reviewing an "excel challenge" and found an question that I can obtain an answer with excel 2003+ but not in LibreOffice Calc 3.5.4.2. I'm interested in why the solution doesn't work in Calc but ...
4
votes
2answers
3k 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 ...
2
votes
2answers
927 views

Numbers imported into Ubuntu Libreoffice calc from text or csv files are coming in as text

I working in Libreoffice 3.6.0.2 in Ubuntu. I have a text file that has some columns of text and columns of numbers. When I import them into Calc either by copying and pasting or saving as a csv and ...
1
vote
2answers
439 views

how to take a variable number of rows and place each row in seperate columns in libre office

I have about 1,200 rows and I am trying to put them into columns after every twenty rows. How would I do this with out manually copying and pasting a hundred times? Also, I would like to add the ...
3
votes
1answer
303 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 ...
0
votes
1answer
572 views

Where is openoffice.org add-on located after installed (Ubuntu)?

I installed a tool which now appears in Openoffice Calc under Tools->Add-ons. I thought that this tool would appear in ~/.openoffice.org/3/user directory, but I cannot find it. I am especially ...
3
votes
2answers
2k 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 columns,...
0
votes
1answer
1k views

How to make LibreOffice Calc to connect points in a holed series chart with lines?

I've got a time series data table with one function (row) value available for every month and the second for every 3rd month (every quarter), having holes (empty cell values) for every 2nd and 1st ...
1
vote
0answers
106 views

Extracting “tag1, tag2” values from a column in LibreOffice-calc

I've got a LibreOffice calc spreadsheet with columns containing some freeform text, followed by a column with "tags" which I've entered (coded interview data). For example: 1234,"some explanation ...
4
votes
4answers
7k 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
1answer
893 views

Changing the cell range of conditional formatting in LibreOffice Calc

Is there any way of changing the range of conditional formatting in LibreOffice Calc (version 3.6)? I have a set applied already to a range of cells, but there is no obvious way of changing which ...
0
votes
2answers
1k views

LibreOffice Calc - more than million (empty) rows

I've pasted the data from an outer source into LibreOffice Calc. There are ~3000 legit rows and around a million empty after. This makes problems with vertical scrollbar because it is very small now ...
3
votes
3answers
2k 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
2answers
5k 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 Becomes:...
1
vote
1answer
281 views

Delete cell content in Libre (Open) Office based on the cell value

I have a huge csv file (tens of thousands of rows) that I need to filter based on different criteria. After trying to find a proper CSV editor, I decided to use LibreOffice Calc. CSVed is great, but ...
1
vote
2answers
700 views

Formatting a cell in Excel - how to show a symbol in a specified position

When formatting a cell in Excel (or LibreOffice), how can I have a certain symbol displayed in a specified position? For instance, I want a cell formatted so that the sixth symbol is a hyphen, eg. "...
2
votes
1answer
2k 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
2answers
6k views

LibreOffice pivot table group by month

Im having data like this: Date Customer Amount Month 09-01-12 A 20 =A2 (formatted so it shows month name) 10-01-12 B 10 =A3 (formatted so it shows month name) 18-01-...
0
votes
1answer
2k views

How flexible is the 'indirect' function?

My curiosity pushes me to ask this question. If I were to have a series of functions that referenced a different column in a worksheet but all ended on the same row of data is there a way to point ...
6
votes
2answers
7k 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 ...
0
votes
1answer
374 views

How can I set the font style of a hyperlink?

When using LibreOffice on a mac, I can only set the cell font style of a hyperlink that has been visited (opened in a browser). I cannot set the font style of a cell that has an 'unvisited' hyperlink. ...
2
votes
1answer
937 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
2answers
19k 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 ...
8
votes
3answers
8k 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
0answers
709 views

LibreOffice Calc, how to filter rows based on interval

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, complement filters. So ...
0
votes
1answer
597 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
2answers
7k 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
1answer
3k 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 many info columns, I have to ...
1
vote
3answers
3k 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.
5
votes
3answers
3k 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
898 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 ...
5
votes
2answers
10k 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
316 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
495 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
110 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 ...
3
votes
1answer
3k 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
88 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?
22
votes
5answers
16k 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
113 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
2k 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
747 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
4answers
7k 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 ...