Spreadsheet component of the LibreOffice suite

**1**

vote

**1**answer

794 views

### How do I generate a ranking/sort cells automatically in LibreOffice Calc?

For example I have a table with a caption and some numerical data, such as:
Mary | Sue | Mike
-----------------
100 | 20 | 30
-----------------
I want to generate a ranking, i.e.
1. Mary
2. ...

**0**

votes

**1**answer

1k views

### LibreOffice/Excel: SUM an adjacent cell if the value in the conditional cell is equal to N (SUMIF(?))

Disclaimer: I have no idea what I'm doing.
In LibreOffice Calc (3.6.2.2), I'm trying to SUM all of the values in G:x (where range is G2:G529), where B:x is equal to a given number N.
The data set ...

**-1**

votes

**1**answer

172 views

### Ways to delete or isolate entire rows in a huge data collection

I'm working with a huge datafile (about 400,000 rows and 8 columns) in Libre Office. I need to get rid of certain rows so I selected all cells with the desired value (with Find & Replace) and ...

**3**

votes

**2**answers

808 views

### How do I select non-adjacent ranges in libreoffice calc?

I'd like to perform an operation on a set of non adjacent ranges in LibreOffice calc.
I can type: C4:GA4 into the "name" box in order to select a single range. Is there a way to select non adjacent ...

**1**

vote

**1**answer

626 views

### LibreOffice Calc Date Insert - Ctrl + ;

For some time now I've been able to insert the date into LibreOffice Calc cells by pressing Ctrl; combination like in Excel.
With regard to this I have two questions:
Is it possible to make it ...

**2**

votes

**3**answers

734 views

### In libreoffice calc, how can I use the fill handle to copy formulae but not the cell/border formatting?

I'm using LibreOffice 3.6.4.3 on Ubuntu 12.10.
I use cell color and cell borders to group and distinguish groups of cells in my spreadsheet.
I have bold horizontal borders every 4 rows. This is a ...

**0**

votes

**1**answer

245 views

### Libreoffice Calc SUM returns ###

In Libreoffice Calc, sometimes the SUM function returns ### in the cell when referencing other sums. For example:
=SUM(x1+y1)
where
x1=sum(a1:d1)
y1=sum(a2:d2)
gives
###
in the output cell. ...

**0**

votes

**1**answer

92 views

### How to make text in cell visible

Each field contains text, but how can I display it?
See C9 — it contains text, like all other cells:
How can I make it visible?

**1**

vote

**0**answers

155 views

### Formatting text in a LibreOffice Calc cell

Currently I'm working on the layout of a calendar for the new year 2013. I found a template that was designed in LibreOffice Calc.
How can I layout the text in the cell of a Calc program? What can I ...

**1**

vote

**1**answer

2k views

### Libreoffice calc relative and absolute cell reference on copy-paste

I'd like to create a simple Calc sheet, where column A contains the days, B contains how many hours of work i had done and C the price for that day. The price is simply a multiplication of hours (B ...

**0**

votes

**1**answer

2k views

### How to extract unique data from cells in a single column with thousands of similar values?

I have a single column in an Excel sheet with around 25,000 cells (numbers). Most of them are identical that in the end there are only - I guess - maybe 200 different numbers.
Is there a way to ...

**1**

vote

**0**answers

351 views

### Copy formula across row incrementing sheet number, down column incrementing number by 2

I have information on four different sheets I would like to copy to this summary sheet.
I want to know how do I copy the formula across the row and down the column on this summary sheet(say this is ...

**2**

votes

**1**answer

157 views

### Create pie chart with single sector separated from the rest?

Is it possible to use LibreOffice (I'm currently using 3.5.4) to create a pie chart where a single sector of the chart appears removed from the rest?
If so, how to do it?
Alternatively, can someone ...

**0**

votes

**1**answer

520 views

### Libreoffice Calc - Fill with Sum is not working

I want to add 2 columns of data and output the sum into a third column, with a =sum(a1+b1). With many rows of data, I try dragging down the highlighted cell to Fill the rest of the output column with ...

**1**

vote

**1**answer

504 views

### Libreoffice Calc: how do I copy formulas, but not values from a row?

My spreadsheet has rows with cells that contain either numerical values or formulas that use these values (they access only values from the same row). Each row corresponds to an event, when an event ...

**1**

vote

**1**answer

345 views

### Open source alternative for working with large xlsx files [closed]

I am new and I hope the this is the right place for the following question:
I would like to collaborate with people using large xlsx files (many rows).
Which open source alternative can handle these ...

**0**

votes

**1**answer

273 views

### calc: finding the number of numerical values in a given interval

I want to build a histogram of a set of values. So I need to get the number of values that are in a considered interval.
Say my values are on a sheet named 'LIST' in column A. On current sheet, I ...

**1**

vote

**1**answer

1k views

### Strip text from number in Libre Office Calc

I want to convert the string in column C into number. I want to convert "11,00 TL" to 11. How can do this?

**3**

votes

**1**answer

615 views

### LibreOffice Calc/Writer: How to get the dates for specifc weekdays per month?

I'm stuck with a problem in LibreOffice Calc or Writer:
I try to get a table of dates for every Monday, Wednesday and Friday per month, something that I can load every new month wich sets the dates ...

**2**

votes

**1**answer

2k views

### How to count how many rows contain particular values in a particular set of cells in libreoffice and excel

This Calc file : http://dropcanvas.com/521xc/107 have a sheet called "Etat" and a sheet called "Total".
In Etat.F20 I have a formula that counts how many rows in Total have the value "Bug" in the D ...

**0**

votes

**1**answer

137 views

### Libreoffice equation does not get saved in xlsx

If I create equations in a LibreOffice Calc document, and save it as xlsx it wont get saved.
Is there a way to create compatible equations in the first place or to convert them to Microsoft Office?

**2**

votes

**1**answer

102 views

### Tagging or defining automatically in an Excel or Libre office spreadsheet?

I want to review and sort my expenditures in a spreadsheet where I have all my transactions listed. Is there a way to automatically label or tag to categorize the transactions like in a budget ...

**0**

votes

**2**answers

109 views

### Printing the second page from a Calc sheet

I've created a simple invoice document in LibreOffice Calc and it consists of 2 pages. I have defined the print range for my document as:
$A$1:$D$33,$D$34:$D$35
My first page holds the actual ...

**2**

votes

**0**answers

542 views

### How to make a LibreOffice Calc XY (scatter) plot with named dots?

I have a simple XY scatter plot made from a 2-column spreadsheet. When I hover over a dot, I can see the ID of the dot (e.g. "Data point 8456").
I would like to know if I could have the content of a ...

**1**

vote

**1**answer

717 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

**1**answer

363 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

**0**answers

305 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

**1**answer

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

**0**

votes

**1**answer

194 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

**1**answer

159 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

**1**answer

150 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

**2**answers

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

**0**

votes

**1**answer

566 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

**2**answers

341 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

**0**answers

168 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

**1**answer

270 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

**2**answers

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

**-1**

votes

**1**answer

832 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

**0**answers

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

**3**

votes

**3**answers

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

**2**

votes

**1**answer

655 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

**1**answer

972 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

**3**answers

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

**2**answers

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

**1**

vote

**1**answer

209 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

**2**answers

271 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

**1**answer

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

**1**

vote

**1**answer

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

**0**

votes

**1**answer

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

**3**

votes

**2**answers

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