Spreadsheet component of the LibreOffice suite

learn more… | top users | synonyms

0
votes
1answer
991 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
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
2answers
1k 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
1answer
224 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
322 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
885 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
1answer
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
1answer
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 ...
4
votes
2answers
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 ...
0
votes
1answer
306 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
517 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
8k 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 ...
6
votes
3answers
4k 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
558 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
459 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
3k 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
2k 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 much info-columns, I have to ...
1
vote
3answers
2k 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.
4
votes
2answers
1k 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
644 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 ...
3
votes
2answers
5k 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
1k 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
221 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
291 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
103 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 ...
2
votes
1answer
2k 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
77 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
882 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?
12
votes
4answers
6k 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
110 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
541 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 ...
1
vote
2answers
3k 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 ...
3
votes
2answers
4k 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 ...
2
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?
3
votes
1answer
3k 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
163 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
540 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
965 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 ...
2
votes
1answer
5k 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
875 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 ...
14
votes
1answer
11k 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
977 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
780 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
972 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
731 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 ...