Spreadsheet component of the LibreOffice suite

learn more… | top users | synonyms

1
vote
0answers
36 views

How can I remove " from my string..?

My string has " and I dont want " then what to do..? My string is like : "Hey My name is Viper I want remove " from string How
0
votes
1answer
12 views

How to copy tab-contained text from Excel without quotes?

I've noticed that a cell that contains a tab is copied with quotes embedded. I'm looking for copying such kind of a cell without the quotes. Illustrative Example I've 5 cells; A1, A2, A3, A4, and ...
1
vote
3answers
43 views

How to write a function once and have it update in many cells?

It often occurs that I write a function in one cell and then copy it into many cells. The copies then refer to their own correct rows. But if I change the function in the first cell the copies are ...
1
vote
1answer
16 views

Libreoffice: Import columns of spreadsheet as fields

I have a LibreOffice Calc spreadsheet with Columns A, B, C. I would like to create a document in LibreOffice Writer with format: A1 B1 C1 A2 B2 C2...
0
votes
1answer
16 views

Compare/check multiple columns in multiple sheets and find the unmatched values

I have two sheets in libreoffice calc, both sheets have same columns, i need to compare these sheets column values(multiple columns) and need to check rows are equal, if not equal then need to ...
0
votes
1answer
25 views

Why does this LibreOffice spreadsheet display a number as “TRUE”

I copied the problem into this sample spreadsheet Cells A1 and A2 should both display numbers, but one displays "TRUE" instead of a number. If I go to "clear direct formatting" on A2, it will display ...
0
votes
0answers
52 views

oocalc unknown node setting file

When I run oocalc, I get a warning: W: Unknown node under /registry/extlang: deprecated This looks like a Python warning to me. However, I do not see the warning when running Python scripts. My ...
1
vote
1answer
22 views

Search for specific key word and replace entire cell contents with something else in LibreOffice Calc

I would like to be able to search for a specific word, lets say "House" in a calc sheet, and then replace the entire cell content with something else like "Car". It is important to notice, that the ...
1
vote
1answer
24 views

LibreOffice: Autocomplete dates with month and year, no day

I'm using Libreoffice 4.4 and I want to drag down a column in format Month Year. For example: May 2015. I do not need days. I tried to drag down after doing this schema for 3 cells but it doesn't ...
0
votes
1answer
15 views

How to add double quotes around data in tables using Calc

I am trying to load my data into a CRM, but am having a lot of trouble. The data simply wont load at the same time, but all the files work fine individually. Support advised that I make sure I'm using ...
2
votes
4answers
4k 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 ...
1
vote
1answer
16 views

Comment visibility in open/libre office spreadsheets

I'm getting moderately perturbed by how difficult it is to see whether there's a comment in a cell in LibreOffice. As it stands a tiny box appears in the upper right-hand corner in the cell. Does ...
3
votes
3answers
18k views

Is it possible to copy & paste a pivot table in a spreadsheet?

I have a spreadsheet where I use some pivot tables to summarize and highlight some relevant data. Since I have to use multiple pivot tables, all with nearly the same data input but with slight ...
2
votes
1answer
23 views

LibreOffice Calc/Spreadsheet – Delete unusual characters from cells so that I can export a valid CSV file

I received a spreadsheet for work and it has cells that are full of line breaks and excessive whitespace. Unfortunately, I need to export the spreadsheet to CSV format and these two are breaking the ...
0
votes
0answers
40 views

Libreoffice-calc: Apply a macro to a csv-file on the command-line

With Libreoffice/Openoffice it is rather straightforward to do these steps: Import csv file Copy macro from another file Run macro Remove macro Save file in csv and xls formats (no macros, just ...
1
vote
2answers
1k 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 ...
1
vote
1answer
51 views

Libreoffice-Calc: Count number of rows and ouput total below list

How to write a macro that counts the number of columns that contain data and prints the result at the bottom of the list? For example, if the data is: |1|2|3| |a| | | |1| |3| The final table ...
0
votes
1answer
94 views

Libreoffice: How to count words and characters in cells

I would like to count the words and characters of a range of cells, excluding all cells that start with "§". The text is arbitrary UTF8-text, for example: "my test string" = 3 words, 14 characters ...
1
vote
0answers
23 views

How to reference cells by name in Libreoffice 4.2

In the Libreoffice version that came with Ubuntu 12.04, I could select a column, name it, and then construct a formula that referenced this name. e.g. Say I had two columns A=Price and B=Quantity. If ...
0
votes
0answers
24 views

Concatenate a reversed range in Libre Office

I'm kind of new with programming, but I have to make an excel document, that I can use interactively because the output of one of my programs doesn't fit with the input of the others... I have lot of ...
1
vote
1answer
76 views

LibreOffice Calc performance tuning

I have 350 thousand rows in a LibreOffice Calc table (it's a 35 meg CSV file). I only need to view it, not edit it. Yet, it is very slow at scrolling, filtering, and selecting the cells. What can I ...
2
votes
1answer
53 views

How to color rows based on two cell values in Open/Libre Office?

I am trying to apply conditional formatting to an entire row based on two cell values. The style is to be applied only if both cells have a particular value. I tried applying formula of the form: ...
0
votes
1answer
33 views

How to lock a cell for editing in LibreOffice Calc?

How can I lock a cell, to prevent edits to its value for the foreseeable future?
0
votes
1answer
40 views

LibreCalc Date-function returns wrong month

I have a spreadsheet with three columns for "Date", "Currency", and "Amount". Date Currency Amount Month 06.02.2015 EUR 188 March 30.01.2015 EUR 285 January I ...
0
votes
1answer
86 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 ...
0
votes
0answers
29 views

Disable spell checking/auto correct in Libre Office Calc for a specific file?

Is it possible to disable spell checking and/or auto correct in the Libre Office Calc application on a file by file basis? Normally in calc I want spell checking turned on, but for some files it just ...
1
vote
1answer
27 views

Applying reference to another sheet to all cells

I have LibreOffice Calc file which has 2 sheets. I wanted to reference Sheet1 from Sheet2, so I wrote function =Sheet1.A1 in Sheet2 cell A1. How do I apply this to all cells on Sheet2? (Sheet2.B5 = ...
0
votes
0answers
12 views

Styling multiple files on Libre Office Calc [duplicate]

Here's what I do now: Get CSV file from extrenal source Open it in Calc Apply style (set colors, fonts, etc.) Export as PDF Is it possible to get rid of step 3? Somehow loading data to already ...
3
votes
1answer
336 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
850 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 ...
1
vote
2answers
287 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 ...
3
votes
2answers
3k 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 ...
0
votes
1answer
26 views

Time format of Pomodoro in LibreOffice Calc

I'm using LibreOffice Calc in Windows 7 and I'd like to write a formula that, given a decimal number, converts it to hours and minutes. For instance I have this: cell E3 is =($B$3*25/60) i.e. it ...
0
votes
2answers
99 views

Change color of pasted email addresses in LibreOffice Calc

I use LibreOffice Calc on a Linux system. Sometimes when I paste text into a cell (in particular, email addresses) they display as blue font on grey background. But I want black font on white ...
6
votes
4answers
5k views

How to add month to date in LibreOffice calc

This seems like an obvious question. I have a date column, and I want to create a formula to increase it by one month for each column. 10/2013, 11/2013, 12/2013, 1/2014, 2/2014, ... How do you add ...
0
votes
1answer
100 views

How to plot IP address frequency distribution chart on Excel or Calc?

I have one column of data: IP Address 1.2.1.2 1.2.1.2 1.3.4.1 1.3.4.1 1.3.4.1 1.3.4.1 1.3.4.1 1.10.0.5 1.10.0.5 1.10.0.5 2.1.2.1 2.1.2.1 12.1.2.3 12.1.2.3 12.1.2.3 12.1.2.3 12.1.2.3 12.1.2.3 12.1.2.3 ...
1
vote
2answers
4k 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
2answers
12k 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 ...
0
votes
2answers
1k 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 ...
0
votes
0answers
45 views

Create a pie chart from amount/category pairs

From a table like Amount | Category 2 A 1 B 5 A how to create a pie chart with one sector per category found in the second colum and the summed up values for each ...
0
votes
0answers
19 views

Position cells within view and copy functions/formulas from existing cells in Libre Calc?

I would like to keep a specific range of rows visible in Libre Calc. As new rows of data are entered, I would like the sheet/page to scroll, keeping the current row visible. As a comment made me ...
-1
votes
1answer
322 views

How to remove Open Office spreadsheet duplicates in a column while keeping rows together?

I have a spreadsheet with a list of column data of contacts, first name, last name, address, phone number, email... I want to remove the duplicate email addresses but the entire row may not have ...
1
vote
1answer
309 views

How to find and remove external links in (Libre|Open)Office Calc spreadsheet

I have a LibreOffice Calc spreadsheet which pops up a message asking me whether I wish to update external links. I did not add external links myself, but did copy data from another Calc window at one ...
1
vote
2answers
208 views

Parsing dates in LibreOffice Calc - is there a better/easier way?

I have a string of this particular format: DD/MMM/YY HH:MM AM/PM e.g. 03/Nov/14 9:00 AM I could not find figure out how to make Calc recognize it as a date so I had to do the following for a ...
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 many info columns, I have to ...
2
votes
1answer
103 views

SELECT until endless in LibreOffic Calc

I am used to =COUNTA(B3:B999) and change it when the number of rows exceeds 999, but I was wondering whether there is an endless setting. Attempt =COUNTA(B3:B) did not work.
0
votes
0answers
90 views

LibreOffice calc; is it possible to automatically make cells coloured according to the HTML code entered into them?

I am creating a LibreOffice calc spreadsheet designed to be basically be a customized version of the table entitled "Default element colors, by atomic number:" on this page ...
0
votes
0answers
23 views

Show deleted cell values (Track changes in Libreoffice Calc)

I was looking for this feature for a while now: If I turn tracking the changes on and delete the value in a cell it just highlights the border around the cell in red, meaning it has changed. Is it ...
0
votes
1answer
84 views

LibreOffice Calc Error 518

I'm trying to achieve the following in LibreOffice (did it with Excel already, but importing this excel-created file produces the same error). I have 3 columns (A) (B) (C) ECTS Mark ...
0
votes
1answer
30 views

Calculating total salary

I store my working time in Libre Office Calc like this: Start End Hours 10:00 15:00 05:00 11:00 14:00 03:00 09:30 15:30 06:00 Total 14:00 Now I want to calculate my salary, i.e. ...