Spreadsheet component of the LibreOffice suite

learn more… | top users | synonyms

-1
votes
1answer
206 views

How to check if a date is in the future

I want to make a spreadsheet reporting daily and weekly goals for a certain quantity and their achievement, one reporting sheet per calendar week. Imagine that I open it on Wednesday, then I want to ...
2
votes
3answers
4k views

libreoffice what is a fast way to paste special > text and numbers only (aka paste special values)?

I copy and then paste values in excel quite a bit (when I use Excel). When I use LibreOffice you can do the same thing (see related question here) but it is oh so tedious! It gets especially tedious ...
1
vote
1answer
376 views

Attempting to open TSV file in Libreoffice Calc results in being opened by Writer

I have been working with TSV files for months, but I have one file that refuses to be opened in LibreOffice Calc. It instead brings it up in writer. This behavior is consistent across command line and ...
2
votes
1answer
943 views

LibreOffice Calc - apply style to neighbouring cells

I have a column with data and I applied a style to it with the help of Conditional Formatting. I now wish to apply the outcome of that style to the neighbouring column that holds other data. Here is ...
0
votes
0answers
88 views

Libreoffice won't let me have a read-only ComboBox

I have a database table with some entries. I want to display all the fields of this table as a ComboBox. I want the user to be able to select this any entry in this ComboBox without modifying the ...
1
vote
2answers
1k views

Calculate conditional average on disjoint cells

I would like to know if libreoffice calc has the functionality to calculate an conditional average on disjoint cells. An example would be: Calculate the average for every second cell in Row1 if Row2 ...
2
votes
1answer
843 views

Getting date of last change as a cell value in Open Office Calc

I have a few Open Office spreadsheet files (let us call them source files), each containing the financial info of a particular office of ours. I also have a summary spreadsheet, that reads some key ...
2
votes
1answer
2k views

how to get Libre Office to adjust row height to accommodate text entry?

I need for LibreOffice to automatically adjust row height for text entries. I have tried to acomplish this by checking the wrap text option, which I thought would be the solution, but it is not. How ...
1
vote
3answers
2k views

Sum up to N highest-value out of a series?

I have a spreadsheet where each row is a set of numerical competition scores. Each row can be thought of as a single competitor in the overall competition, and each column is a competition event. ...
0
votes
1answer
2k views

Auto fit column value without increasing row height in LibreOffice

I m using Libre Office Calc on my Ubuntu 12.10 desktop and I need a help. I am writing some data from other resource and save it for each row. I have only 2 columns which values are lengthy and rest ...
2
votes
2answers
2k views

How do I mark weekends red in LibreOffice Calc?

I have a list with dates in it, and I would like to mark the dates representing weekends as red (for easier telling which day of the week it is without having to manually look up the day of the week ...
5
votes
5answers
11k views

Quickly fill blank cells in a list in LibreOffice Calc

For example: Cell A1 = 2/3/2012        A2 is empty        A3 is empty        A4 = 3/7/2012        A5 is empty        A6 is empty        A7 is empty What I want to do is: select the ...
0
votes
1answer
72 views

Using LibreOffice and having problems, copying details

A staff member in our office is having trouble pasting details from a spreadsheet {In any format} to a template in .ott format. When pasting and details from the spreadsheet, it removes some of the ...
12
votes
2answers
9k views

How do I create a sum for each group of data in Libre Office Calc?

I have a simple spreadsheet like this: Invoice_Number Invoice_Date Invoice_Amt 1000 1/1/12 1 1001 1/1/12 2 1002 1/2/12 ...
1
vote
2answers
2k views

Is it possible to display a range of cells in a different sheet in LibreOffice calc?

I've been looking around for a while, but could not find a answer. My budget sheet contains a meta-sheet called "settings". In "settings" there is an area e.g. C10:D20 that I would like to display in ...
2
votes
1answer
7k views

Lookup the nearest value in a column?

I have a column (A) containing numbers between 0-200. By comparing the value of A against the values of C, I want to pick the nearest cell and thereby return the value of D. So, if A is 160 I want to ...
2
votes
1answer
2k 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. ...
1
vote
1answer
2k 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
1answer
346 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 ...
4
votes
2answers
2k 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 ...
2
votes
1answer
2k 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 ...
6
votes
2answers
2k 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
1answer
331 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
1answer
130 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
0answers
168 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 ...
2
votes
1answer
7k 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
1answer
3k 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
0answers
601 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
1answer
216 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
1answer
1k 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
1answer
850 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 ...
0
votes
1answer
692 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
1answer
2k 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
2answers
1k 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
1answer
4k 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
1answer
150 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
1answer
130 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
2answers
145 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 ...
4
votes
1answer
1k 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
1answer
2k 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
1answer
525 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
0answers
453 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
1answer
717 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 ...
1
vote
1answer
612 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
1answer
385 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
1answer
222 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
2answers
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 ...
1
vote
1answer
908 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
2answers
438 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
1answer
298 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 ...