Questions tagged [libreoffice-calc]

Spreadsheet component of the LibreOffice suite

Filter by
Sorted by
Tagged with
0
votes
0answers
9 views

Libre office Basic shortcut keys within a macro

I've created a macro that opens a folder based on which cell is selected. I've made it so that the macro is linked to a push button, but this can be cumbersome to find if the sheet is large. I set up ...
0
votes
1answer
20 views

In LibreOffice Calc, why would sort buttons and menu items suddenly become disabled?

I was creating a spreadsheet in LibreOffice Calc. Several times, I used the "Sort Ascending" and "Sort Descending" toolbar buttons. All was well, and everything was working as ...
0
votes
1answer
18 views

How to get LibreOffice Calc to recognize dates in YYYY/MM/DD format?

I've been given a large tab-separated-values (TSV) text file that contains dates in the format YYYY/MM/DD. I tried copy and pasting the dates into LibreOffice Calc, but it didn't recognize the strings ...
0
votes
1answer
14 views

LibreOffice Calc: How to use OFFSET Function when Selection Intervals should increase?

Assumed we have this table style in sheet 1: A B C D E F G H 1 0.1 0.2 0.5 0.7 0.4 0.5 0.9 1.0 2 3 In sheet 2 there have to be averages made of cells ...
0
votes
0answers
8 views

Enabling wrap text by default on libreoffice calc with long CSV entries

I have a CSV file for i18n and entries take much longer than my screen space. Apparently the text wrap is stored inside the document, but I guess this doesn't work with CSV. Is it possible to ...
0
votes
1answer
30 views

MS Excel How to Hide or Change Color of text in a cell with no value

I created a multi-page spreadsheet file originally in LibreOffice Calc (herein after "Calc") and I am now working with it using Microsoft Excel. This is a tool I use to create a week-based ...
0
votes
1answer
42 views

Why is Libreoffice calc 7.0.4.2 slow in Windows 10?

When opening Libreoffice Calc 7.0.4.2 as of today, in Windows 10 Pro 20H2, with Nvidia GPU 1070 Max-Q and i7-7700HQ, even on a blank sheet, the following actions induce a ~0.7s lag or more: clicking ...
0
votes
0answers
22 views

Libre Office Calc - Stack columns of text

I had this one column of text with about 900 sentences. I'm trying to list the most frequent words, so the first step I took was splitting it all into columns. Is there a way to stack all columns in a ...
0
votes
1answer
17 views

LibreOffice Calc: SUMIFS based on each row condition

Assume I have the following data: I want to do a calculation based on each currency and amount. Something like: So money in (for USD) will be the sum of column H where the amount is positive, and ...
0
votes
2answers
28 views

Compare and sort data

I have 2 blocks of data; Column A and B are one set and column D and E the second. I need to compare column A to Column D and for every match copy from Column B to column E. Problem is that column A ...
0
votes
0answers
9 views

In LibreOffice, how to ignore spell checking words containing a period (like stackexchange.com)?

Out of the box, LibreOffice's spell-checker successfully ignores strings like stackexchange_com, but it marks strings like stackexchange.com as spelling errors. How does one configure LirbreOffice to ...
0
votes
3answers
57 views

Is there a way to define a range with infinite row or column number in a spreadsheet program?

When defining a named range in a spreadsheet program, is there a way to define a range that covers for example A1:A[*infinity*], so I will be sure that the range will always include all rows, whether ...
0
votes
0answers
18 views

Trying to use code countif+countif/countblank

I tried couple of things but everything gives me an Error, does anyone know why? =COUNTIF(COUNTBLANK(A12:D12),0) But when I try to put them in two different cells it actually works =COUNTBLANK(A12:...
0
votes
0answers
6 views

When LO saves AutoRecovery information, does it keep any of the older ones? and for how long?

I have a bunch of files in /backup starting with "lu" and then some numbers. Are these the ones that are saved and kept after the latest AutoRecover is saved? If so, how long are these kept. ...
0
votes
0answers
14 views

Libreoffice calc multiple horizontal window splits

On a libreoffice calc spreadsheet I would like to have two horizontal splits to yield three window areas. I have the top several lines split off and frozen to provide headings, title and unit ...
0
votes
1answer
30 views

How to incorporate an existing library of python functions into libreoffice calc so they can be used like user written BASIC function in cells

I'm a noob at using macros but have written a couple of user function in BASIC that I can use just like functions in LO Calc cells. I was looking to write more but found that a Caleb Bell has has ...
1
vote
1answer
15 views

LibreOffice equivalent to SQL “count(*) group by some_column”

I have a spreadsheet A | B ----------- alice | 100 bob | 100 carol | 200 dave | 100 edith | 200 I want to basically count(*) group by B, the result being 100 | 3 200 | 2 How to do this in ...
1
vote
1answer
27 views

Libreoffice: Get adress range from cell

I'm not sure what the correct terms would be, so I can't effectively search for a possibly already answered question, but here it comes: I'm looking for a way to have in cell A1 a formula (lets say ...
0
votes
1answer
28 views

used in a worksheet cell, a number from a result string of an html API query

How I can make the example below appear in a cell: 1 / the libreoffice calc cell queries a url: "https://api.binance.com/api/v3/ticker/price?symbol=BTCUSDT" 2 / the libreoffice calc cell ...
2
votes
1answer
22 views

Libreoffice: Generate same figure with data in another sheet

I have several sheets with data (corresponding to different years) arranged in the exact same way. I have created figures on sheet 1. Is there a way to have the same figures generated with the data in ...
0
votes
1answer
38 views

Fix file associations in LibreOffice Calc

How do you fix the file extension associations with LibreOffice Calc? I have a CSV-like file with a *.dsv file extension. Ubuntu has that file type set as a CSV document. When I explicitly try to open ...
1
vote
0answers
40 views

LibreOffice calc: how to filter one or several tags in a multiple tags column

I have libreoffice-calc file like this: books author tags Book 1 Author 1 ghost; romance; france Book 2 Author 2 romance; mystery Book 3 Author 3 biographie; england Book 4 Author ...
1
vote
0answers
75 views

How do I show percentage in a Libreoffice columns chart from fraction data?

I would like to know if there is a way to create a chart in Libreoffice Calc where I get percentage values on the chart from fractions. Example: Group 1 4/10 6/10 Group 2 5/10 5/10 I ...
1
vote
0answers
20 views

SUM IF with multiple conditions in the same collumn

How to make this work in libre office Calc? =SUM(SUMIF(Aug.N$2:N$400, {"ED","EDS","CS"},Aug.AD$2:AD$400))
0
votes
0answers
45 views

How to set the color of the input bar in LibreOffice Calc spreadsheets (for dark theme/mode)?

In LibreOffice Calc, I have been able to almost configure a usable dark theme/mode. To accomplish this, go to Tools > Options... > LibreOffice > Personalization and set the dark theme, and ...
1
vote
1answer
34 views

LibreOffice Calc: set pdf print dimension according to selected cells?

I want to export a pdf of a LibrOffice Calc table, but would like that the pdf output has the same size as my table. Imagine I only have text in cells A2:B4. How can I make sure that the pdf output is ...
0
votes
0answers
24 views

Does LibreOffice Calc have a function similar to LET()?

In another question, @ReddyLutonadio mentions that Microsoft Excel has a =LET() function, which from my understanding allows one to assign names to variables. One use case is avoiding lengthy ...
1
vote
1answer
41 views

Neater way to ignore errors in LibreOffice Calc?

Let's consider any (potentially lengthy) function that can produce an error. For discussion's sake, I'll use =1/0. I can ignore the resulting error by using the following formula: =IF(ISERROR(1/0),&...
0
votes
1answer
42 views

LibreOffice Calc: Exclude rows from INDEX() reference

Working on Sheet2, I can use the following formula to look up the "Sheet2.B1"th match in Sheet1, column B corresponding to Sheet1, column C: {=INDEX($Sheet1.$B$1:$B$9,SMALL(IF($Sheet1.$C$1:$...
1
vote
1answer
27 views

Make some rows read-only

I want to make some rows in a LibreOffice Calc file read-only. I select the rows and then go to Format > Cells... then to Cell Protection tab but this does not appears to be the place. The ...
0
votes
1answer
126 views

Phone number format in LibreOffice Calc

I want to format some cells for a phone number in the form 111-111-1111. I do so as in the screenshot below. This works as far as it goes, however, it falls short in that the user can enter more than ...
0
votes
1answer
221 views

Compute median on filtered lines

I have a spreadsheet with category and value columns. I can use the averageif(), minifs() or maxifs() functions on a range to get the average/min/max of value for rows in a given category. But is ...
0
votes
0answers
31 views

How to merge and combine 2 spreadsheets in Google Sheets or Libre Calc, both sheets have matching ID numbers

I have 2 spreadsheets, each one has an ID number that is the same, I am looking to add the info from one to another. I would like to take the information from spreadsheet A which is an ID number, and ...
0
votes
1answer
17 views

LibreOffice Calc change i to I automatically?

Whenever I type i (lowercase), it will change to I (uppercase) automatically. This doesn't happen with other char, only i. Is there a way to fix this default behavior in LibreOffice Calc ?
0
votes
1answer
33 views

How to generate a combination of cells in LibreOffice?

I want to generate a cell with a combination of data I've insterted in specific cells in LibreOffice. Can anyone comment? This is what I'm after ( I'd like a slash in between the data): example
2
votes
1answer
165 views

Libreoffice 6.4 Calc uses only one CPU core, and most computers have now from 8 to 32 of them. In a 200,000 rows spreadsheet anything takes minutes

My computer based on a i7-3820 goes at the same speed than a 286 PC/AT in a spreadsheet of 220,000 lines, when using LibreOffice Calc 6.4 : Centering a column that holds lines of two characters ...
1
vote
0answers
34 views

In a spreadsheet, can you force pasting from the system clipboard?

I've noticed what seems to be new behavior the past several months in a few spreadsheets on Windows 10. Actions Taken: Select and Copy a cell in a spreadsheet. Observed in Windows 10 / Microsoft ...
1
vote
1answer
383 views

How do I fill a row based on the number of filled columns in the row above?

Here's my current spreadsheet: Rows 1 and 3 are the input, and I want to fill row 4 automatically based on how many columns there are in row 3. Example end result: The "discount factor" is ...
2
votes
2answers
155 views

Libre Office - Single Column Pie Chart

I want to create a pie chart, for a single column that looks like this: I tried to select the range, click on Insert -> Chart -> Pie-Chart, but regardless of what I try, I always get an empty ...
0
votes
0answers
46 views

Libreoffice Calc: fill a cell when another one is populated

In a previous version of Libreoffice Calc I had this function working: =IF(A1="";IF(B1="";"";"FILL");A1) Now, with version 6.4.4.2 I have A1 filled with 0. I ...
0
votes
0answers
25 views

How to display the last update of an external data source?

Following these instructions, I was able to insert external data (from WorldOMeter) into one of the sheets of my LibreOffice Calc spreadsheet. I set the refresh interval to 43200 seconds (12 hours), ...
1
vote
2answers
321 views

Detect duplicate values in an unsorted column in LibreOffice calc? (Q&A)

I have a table, where one column is intended to represent unique IDs. Is it possible, to check with a formula, if the uniqueness criterion is fulfilled? For instance, let's say the table is currently ...
0
votes
1answer
72 views

Censoring/pixelizing sensitive information with MS Paint?

I need to post a screenshot for a bug report or superuser/stackoverflow question, that contains sensitive information, and have only MS Paint available at the PC at hand. Since layout issues are part ...
0
votes
1answer
93 views

Conditional formatting with Libreoffice Calc activated with a letter and the condition being applied to more than one cell

Is there a way to do condition formatting based on a letter and having that format apply to more than one cell? The desired result is shown below, where typing either A, B, or C in column "B"...
0
votes
1answer
34 views

Spreadsheet: How can I merge every nth line with a previous line?

I have a spreadsheet where each cell contains multiple key value pairs. Every value is on a new line. The content of every cell is the following: cell content I need to somehow merge the every second ...
1
vote
1answer
1k views

The maximum number of columns per sheet was exceeded

When i tried to open some MS Excel files in LibreOffice Calc i get this warn. I searched solution of this issue but i did not find useful things. How can i increase number of column limit?
0
votes
1answer
14 views

Plotting of timeseries (ISO8601) in LibreCalc

I have a CSV file with: 2020-30-05T23:23 10.000 2020-31-05T09:46 20.000 2020-31-05T13:45 30.000 2020-31-05T19:10 40.000 2020-31-05T23:22 50.000 2020-01-06T09:45 60.000 2020-01-06T17:...
0
votes
2answers
67 views

evaluate text present in specific format in another column in Excel [duplicate]

I have a text written as "12*14" in C4 and and number "1000" in B4. I want to calculate D4 as 1000-12*14 I have tried following, But both are not working here. B4-NUMBERVALUE(C4) B4-VALUE(C4)
0
votes
1answer
33 views

How to swap rows by just swapping the first cells?

Say I have this table: Occasionally I need to swap and add rows to reorganize my table. In this example, I put the row Cat at the bottom, and insert row Horse: And then later decide to move row ...
1
vote
1answer
170 views

how to change address based on a value in another cell in Libreoffice Calc?

I need to pull data from the source spreadsheet $Units_Weekly, from a changing column. The start is column O ----> $Units_Weekly.$O2 Column name changes in A50. The next column name will be P, it'...

1
2 3 4 5
12