Spreadsheet component of the LibreOffice suite

learn more… | top users | synonyms

4
votes
1answer
34 views

Removing functions from a column, but keeping data (convert to normal text)?

I have a column that contains the results of a few different formulas. I want to convert all of these cells to normal text, getting rid of the formulas. I know I'm missing something obvious here.
0
votes
2answers
48 views

How do I make a date field in Excel/LibreOffice Calc that is 180 days from today?

So I have two columns, one is the current date (currently using a 09/06/15 format) and I have another column that is meant to be for a date 180 days into the future. How can I create a formula that ...
0
votes
1answer
45 views

Is there a way to have groupings of bars in a bar chart in?

I know this may be bad style, but I want a bar chart that groups certain bars together. For example if I was comparing the speed of two models of cars, and considering the speed under wet and dry ...
1
vote
0answers
78 views

Equivalent for FILTER() formula in Libre Office

In a Google Spreadsheet, I create a report sheet by entering a formula like this in the top left cell. =FILTER(Sheet1!A2:E, Sheet1!E2:E > 0) This gives me all the rows in the Sheet1 from row ...
2
votes
0answers
78 views

How to customize default CSV format in LibreOffice?

Every time I export my data to CSV (and I do it often) I have tick the "Edit filter settings" checkbox, choose UTF-8 for the encoding, and semicolon for the fields separator manually. I do this ...
0
votes
0answers
67 views

How can the background of a cell in LibreOffice Calc be set to a hex value contained another cell?

How can I make a cell in LibreOffice Calc have a background colour equal to a hex value specified in another cell? For example, if cell A1 has the value e51c23, how can I set the background of cell ...
1
vote
1answer
59 views

How to switch the “input field” between “formula mode” to “numerical mode”?

In the below link, I have made a simple example where A1=1, A2=2 and A3=3 and then at A5 the formula "=A1+A2+A3" is written. What I now wonder, is there any special keyboard shortcut or icon-shortcut ...
1
vote
1answer
122 views

How to set different orientation for different sheets in OpenOffice.org Calc?

I have document with two sheets sheet1 and sheet2 and i want set orientation of sheet1 to portrait and orientation of sheet2 to landscape. This can be done in Excel, but in OpenOffice when i set ...
1
vote
2answers
3k views

How to get VLOOKUP to return the *last* match?

I'm used to working with VLOOKUP but this time I have a challenge. I don't want the first matching value, but the last. How? (I'm working with LibreOffice Calc but an MS Excel solution ought to be ...
0
votes
1answer
33 views

Format text list with corresponding key into spreadsheet

I have a text file with dates arranged as such. name1 2010-01-02 (i) 2010-05-07 (i) 2010-06-12 (i) name2 2010-01-02 (i) 2010-05-07 (i) 2010-06-12 (i) name3 2011-01-05 (i) 2011-05-05 (i) 2011-06-14 ...
2
votes
1answer
263 views

How do I convert dates from Open Office format to MS Excel format

I am trying to save a file in Libre Office as an excel file but Excel sees the data displays it as a negative number using a series of ###### symbols. I believe the problem is to do with the two ...
0
votes
1answer
63 views

How to make LibreOffice Calc look like Google Sheets? [closed]

I'm trying to transition from Google Docs to LibreOffice for privacy concerns. Does anyone have any advice of how I could make it more minimalistic and clean like Google Sheets?
1
vote
1answer
631 views

LibreOffice graph axis label

I have made a graph out of about 20 lines of dates and 20 lines of values, but the graph doesn't show all the dates, which really bugs me, it just shows 'month' difference: I would like to have ...
0
votes
2answers
63 views

Convert named months date text to date

I have a date text with named months like this 06:29 Mar. 17 2014 I tried this but obviously I need to convert Mar to a number =DATEVALUE(RIGHT(G1, 4) & "-" MID(G1,7,3) & "-" MID(G1,12,2)) ...
6
votes
2answers
870 views

Libreoffice: sum of column except one cell

I want to calculate the sum of the A column excluding A1, because I want the result there. Illustration: A B C 1 =SUM(A2:Ainfinite) 2 1234 3 5678 ... I ...
3
votes
2answers
73 views

Match cell content based on line break

I take data from a helpline call in site and transfer it to Linux Libreoffice Calc (LC). Column C cells have 2 sets of data - two telephone numbers - 1 being the caller and 1 being the forwarded ...
1
vote
0answers
45 views

Return an array from a lookup

I have a table with column A containing vendors and column B containing product codes. One vendor has many product codes. In another place I need an array of product codes for a specific vendor. The ...
1
vote
0answers
157 views

updating links in LibreOffice Calc xlsxfile results in err:509

I have just started to use Libreoffice so I am totally new to the system and immediately encountered a problem with a calc document. The document has originally been made in Excel (but on a Mac) and ...
0
votes
1answer
147 views

Search for non-characters (character replacement indicators)

I have a text file (actually a .csv exported from .xlsx in LibreOffice) with lines that have gibberish when opened in LibreOffice Calc. In VIM they appear as the following: Joe User, joe@user.com, ...
1
vote
1answer
32 views

In LibreCalc, how to delete rows based on WHERE condition

This question told me to ask my excel question here: http://meta.stackexchange.com/questions/90919/which-stack-exchange-site-is-best-for-microsoft-excel-questions I'm actually using libre calculator. ...
1
vote
1answer
26 views

Is entry in colum a (at least once) related to entry in colum b?

I'm stuck with a seemingly simple spreadsheet (LibreOffice) problem. Assuming I have three columns given as Name, Day and Mood; how can I find out if person x was happy at least once? In a second ...
1
vote
1answer
42 views

How to use third column as identifier in line-graph

I want to draw a diagram(line-graph) from a three column sheet Input | Date | Value | Identifier | | ------------- | ------------- | ---------- | | 01-04-2014 | 1 | ...
0
votes
1answer
165 views

LibreOffice Calc time formatting trouble

I'm calculating a time sheet with LibreOffice with this SUM column. 00:00 08:00 08:00 08:00 08:00 08:00 00:00 04:00:00 PM The printing format of the bottom row is Time 876613:37:46 but that's not ...
1
vote
1answer
1k views

Changing the units on the x-axis in a LibreOffice Calc chart

I've used the LibreOffice chart wizard to make a chart, and I would like the x-values to be different to the default of 1,2,3... (in this case, 500,1000,...). How can I do this?
2
votes
1answer
585 views

LibreOffice calc: How to apply multiple tables to a single chart

I'll say it right off the bat - I am a complete Excel/Calc amateur. What I want to do is apply 3 tables, each with 2 columns of data into a chart with 1 XY-line for each. My current data looks ...
3
votes
1answer
2k views

LibreOffice Calc - How can I create a custom legend?

I'd like to use a custom legend for a diagram. Is that possible? If it's helpful for anyone: Xubuntu 13.10 LO 4.1
1
vote
2answers
1k views

Interpolate values between two cells

I've got a spreadsheet that looks like this in Libre Office Calc: +-----------+ | | A | | 1 | 444 | | 2 | | | 3 | | | 4 | | | 5 | | | ...
-2
votes
1answer
51 views

How to compare number with number in other column and get corresponding value

If suppose I have data like column1 column2 504231 504109 504109 504201 504201 504299 then I used the following formula ...
1
vote
1answer
473 views

How to compare numbers in columns

I have data in libreoffice like ColumnA ColumnB 504231 504109 504109 504201 504201 504299 like so much data Then I want output like ColumnA ColumnB 504231 504109 504109 504201 504201 ...
-1
votes
1answer
46 views

How to add the current page number in a cell of a calc sheet

I would like to add the current page number in a cell of that page and to refer back this cell for this page number in a cell somewhere in the same sheet / in another sheet. Is there a way to do so?
1
vote
0answers
170 views

Why is libre office calc inserting a ' at the beginning of the cell?

This is the most ridiculous thing i've encountered.. I've imported a csv, I end up with nice neat clean rows of numbers, no formatting, nothing weird, no " or ' left over, just numbers. When I go to ...
0
votes
2answers
30 views

keyboard equivalent of dragging the little black square to generate sequence

What is the keyboard way of generating a sequence? The pic below shows libre office calc. In order to generate the sequence of numbers 1 to 10. I simply enter few numbers of the sequence (in ...
1
vote
1answer
1k views

Libreoffice: format field yyyymmdd to date

How can I format a field value like 20140226 to 2014-02-26 in LibreOffice? If I use the format cell option I get strange values.
1
vote
2answers
266 views

Use libreoffice-calc to create charts displaying the results of an exam

I would like to create charts from data in a libreoffice-calc document, displaying the results of an exam. For example, one column would contain numbers from 0 to, say, 100 (points reached), and I ...
-1
votes
1answer
378 views

Change background fill of cells in LibreOffice worksheet containing specific text

How can I change the background fill of all the cells of a LibreOffice spreadsheet worksheet that contain a specified text? In Excel that could be done by configuring the settings in the find and ...
0
votes
0answers
103 views

Is it possible to recover cells with hidden content wiped out upon opening with LibreOffice

Some of my .xls Microsoft Excel files contain cells with hidden information formatted using custom code ;;; to deter casual viewing. I made the switch to LibreOffice a year ago and converted all the ...
2
votes
1answer
419 views

Applying Conditional Formatting to all computed values in LibreOffice Calc

In LibreOffice Calc, how can I use the Conditional Formatting feature to apply a style to all cells which contain a computed value (via formula, as opposed to a directly input value)? LibreOffice ...
-2
votes
1answer
40 views

How can I type certain days (such as Monday, Wednesday) in an easy way for the next 5 months without typing them one by one?

I am trying to input in a libreoffice calc file only Mondays and Wednesdays for the next 5 months, but I dont wanna do it typing the dates one by one. So..I thought I would type 4 dates like this: ...
3
votes
0answers
176 views

Default font and cell padding when opening files in LibreOffice/OpenOffice Calc

I've seen solutions for setting a default font when creating new files. What about a default font (and cellpadding) when opening an existing CSV file? LibreOffice seems to always default to Liberation ...
1
vote
0answers
126 views

Disable prompt when opening a CSV file in LibreOffice/OpenOffice Calc

Every time I open a CSV file, LibreOffice presents me with a prompt asking to confirm Character Set, Language, and Separator options (tab, comma, semicolon etc). This prompt is not present in ...
1
vote
1answer
28 views

Replicate a Calculation Sheet for each customer

I created a LibreOffice sheet, with the detailed list of expenses. And a second list with the names and debts of each neighbour. However, I'd like to generate a personalise replica of the first ...
1
vote
0answers
346 views

How to force LibreOffice Calc to treat time values as numbers

I want to do something very simple in LibreOffice Calc: I have a set of simple CSV files that contain lists of durations, formatted as HH:MM:SS. I need to make a SUM of those lists. With some ...
2
votes
1answer
674 views

LibreOffice pivot: How to count 1 per line?

I have a list of fruit occurrences like this: banana banana apple orange apple And I want LibreOffice to give me a number of occurrences per fruit, for instance: banana | 2 apple | 2 orange | 1 ...
0
votes
1answer
326 views

Libre Calc error on a float in decimal notation. Err. 519 (#value error)

There is a problem with my libreoffice. I doesn't calculate cells with decimal points, possibly seeing them as text. I have checked my locale settings (you can see them in the picture above) that ...
3
votes
1answer
328 views

Open only the first 10 lines of large CSV file in LibreOffice Calc

I will start receiving many large CSV files, of which only the first few lines are of interest to me. I was hoping to open them by piping only the first few lines to Calc, but this doesn't work: $ ...
1
vote
1answer
2k views

Hour and minute count as decimal

I am working in LibreOffice Calc. I have a paycheck which for each day tells me the number of hours I worked, in this format 4:25, ie 4 hours and 25 minutes. 1. First of all, I want to type 4:25 ...
1
vote
2answers
1k views

Decimating rows in spreadsheet

Is there such an option to preserve only every 10th row in my spreadsheet? It can be Excel or Calc. Quality standards want some noise to be added.
0
votes
2answers
101 views

Calculate # of shared elements in cells

I am struggling to get a calc formula to do the following calculations: A B D G H A A B C 2 1 A D E 2 1 A F G 1 2 B H I 1 1 As in the example, I need for the ...
0
votes
1answer
146 views

What is the Excel equivalent of Find & Replace catch-all “*” in libreoffice calc?

In LibreOffice Calc, I am trying to find and replace all characters after a blank space to delete, and similarly to find and replace all characters before a blank space to delete, but the "*" is not ...
4
votes
1answer
5k views

How to scale a long LibreOffice Calc sheet to fit on two pages when exporting to PDF?

I have Libreoffice and I want to export a long calc sheet to a pdf document with the fit-to-page function from the page preview in the file menu but it doesn't seems to work. I want to fit my sheet ...