Questions tagged [libreoffice-calc]
Spreadsheet component of the LibreOffice suite
691
questions
3
votes
2
answers
3k
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 ...
- 33
2
votes
1
answer
6k
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 ...
- 481
0
votes
1
answer
250
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?
- 343
2
votes
1
answer
338
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 program....
- 21
0
votes
2
answers
187
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,649
10
votes
3
answers
14k
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 ...
- 223
5
votes
1
answer
4k
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:
...
- 267
0
votes
2
answers
1k
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 ...
- 135
0
votes
1
answer
1k
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 (...
- 3
1
vote
1
answer
997
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 ...
- 35
1
vote
1
answer
1k
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:
=...
- 35
0
votes
1
answer
411
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 ...
- 246
4
votes
2
answers
4k
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 ...
- 225
3
votes
2
answers
2k
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 ...
- 6,606
1
vote
2
answers
597
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
1
answer
1k
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 ...
- 31
0
votes
1
answer
796
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 ...
- 1,549
3
votes
2
answers
3k
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,...
- 3,994
4
votes
1
answer
3k
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 ...
- 7,523
2
votes
0
answers
143
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 ...
- 121
4
votes
4
answers
14k
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 ...
- 195
3
votes
2
answers
2k
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 ...
- 195
2
votes
3
answers
3k
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 ...
- 173
3
votes
4
answers
4k
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 ...
- 33
5
votes
2
answers
11k
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,129
1
vote
1
answer
590
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 ...
- 111
1
vote
2
answers
2k
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. "...
- 111
2
votes
1
answer
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.
- 21
17
votes
2
answers
19k
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-...
- 277
0
votes
1
answer
3k
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 ...
- 113
7
votes
2
answers
9k
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 ...
- 11.1k
0
votes
1
answer
432
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.
...
- 1,123
2
votes
1
answer
2k
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 ...
- 1,123
8
votes
4
answers
73k
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 ...
- 81
18
votes
3
answers
19k
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 ...
- 828
2
votes
0
answers
860
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 ...
- 121
0
votes
1
answer
718
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. ...
- 103
9
votes
2
answers
22k
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 ...
- 295
2
votes
1
answer
4k
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 ...
- 465
1
vote
3
answers
5k
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.
- 1,549
7
votes
4
answers
5k
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 ...
- 1,149
2
votes
1
answer
1k
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 ...
- 7,045
5
votes
3
answers
16k
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:
...
- 1,196
3
votes
1
answer
8k
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 ...
- 135
1
vote
1
answer
384
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 ...
- 13
0
votes
1
answer
3k
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 ...
- 828
0
votes
1
answer
217
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 ...
- 483
3
votes
1
answer
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 ...
- 828
1
vote
1
answer
163
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 first ...
- 828
1
vote
1
answer
2k
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?
- 828