Spreadsheet component of the LibreOffice suite

learn more… | top users | synonyms

1
vote
1answer
107 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
2k 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
249 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
59 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
593 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
59 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
765 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
72 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
43 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
153 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
0answers
9 views

How to create readable libre office expressions

When an expression gets longer, it's nice to format it on multiple lines, much like we do for source code. In Libre office Calc, new line can be be entered in cell using Ctrl+Enter. But once you ...
0
votes
1answer
144 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
31 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
156 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
522 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
968 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
439 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
42 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
163 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
948 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
255 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
371 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
101 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
406 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
171 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
124 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
27 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
317 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
630 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
306 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
315 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
100 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
141 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 ...
3
votes
1answer
4k 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 ...
0
votes
1answer
90 views

Are spreadsheet programs inherently unsuitable for “one row=one case” data?

Often when I'm working with large data sets, I structure the data in a way where every row equals one specific case. For example, in a psychological experiment, I input the data so that all data ...
0
votes
1answer
81 views

Macro to manage sport ranking and calendar?

I need to write a macro (or a different easy way) to manage ranking and calendar for a curling tournament. The event will follow the Shenkel system: first match determined by general draw after ...
1
vote
2answers
2k views

Move rows with the keyboard in Calc

How might one move a row to a different location with the keyboard? I have found this guide for the mouse but due to disability I have trouble using the mouse for this. Note that I am not looking to ...
0
votes
1answer
917 views

Currency conversion in LibreOffice Calc?

I have a *ods.-spreadsheet in LibreOffice. Some fields contain numbers in currency format (JPY), but they could also be in general number format. I want to create corresponding fields which display ...
3
votes
1answer
151 views

How do I conditionally format a cell that contains a dropdown list

Hi Guys I want to know how to conditionally format a cell that contains a drop down list of 3 values. I want each value to represent a different coloured background when selected in the drop down ...
1
vote
1answer
104 views

How can i reset my Libre Office Calc setting

I am using Libre Office Calc for my Excel work. Image 1: Image 2 I can not see the format color in on Libre office installed @My System. but on my Colleague System who uses the same Libre ...