Questions tagged [libreoffice-calc]

Spreadsheet component of the LibreOffice suite

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

LibreOffice Calc: How to get total for HH:MM:SS cells for graveyard using 24 hour clock

I have a week. From B28 to H28 I have start time. B29 to H29 I have End time the next morning. From B30 to H30 I have actual time from each day with this formula: =(24-B28) +B29. This gives me the ...
user avatar
0 votes
0 answers
14 views

Applying a template to each sheet

I can create a template (.xltx), and then create new workbooks (.xlsx) based on it. But it seems, when I do that, the first sheet of the workbook alone is based on the template, and the later sheets ...
user avatar
  • 1
0 votes
1 answer
30 views

Error "Unrecognized database format" when opening a .mdb file

I have a .mdb file which I do not know which version of ms access was used to create it. When I try to open it, I get "Unrecognized database format" error. I tried using MS ACCESS 2003 and ...
user avatar
  • 61
0 votes
1 answer
46 views

How to apply a calculation on a table column with a range of value per cell

I have a large table where in the first column for some row rather than just a single value there is a range e.g. I want to apply a calculation on all the data elements, but since in the first column ...
user avatar
  • 205
0 votes
0 answers
15 views

LibreOffice calc: create multiple chart series from column values

Sorry for the basic Calc question, but I'm very inexperienced with spreadsheet charts and need some help. I've tried searching for the answer but surprised that I can't find anything that answers what ...
user avatar
0 votes
0 answers
33 views

How to use one table (ods) file as a base for another table file?

I have an accounting ledger table (.ods) file which should be the base for other table files. This table file should be the base for other table files such as: 2023.ods 2024.ods 2025.ods These are ...
user avatar
0 votes
0 answers
22 views

How do I use a `TRUNC` function in a LibreOffice Basic script?

I have to write my own custom function that converts number of days since Jan 1, 1900 to a date. I am recreating some legacy code that does this but uses 32 days a month, 384 days a year (I am not ...
user avatar
  • 170
0 votes
0 answers
37 views

How to do nested if statements in Librecalc?

Are nested if statements supported in Librecalc? According to this article about Excel, if statements should be nestable. However, if I enter this formula into a Librecalc spreasheet: =If(1>2, &...
user avatar
  • 7,600
0 votes
1 answer
10 views

troubleshoot: LibreOffice on Debian testing (gnome), insert current date shortcut non-functional

I can't seem to be able to figure this one out. I often want to insert the current date in a libbreoffice calc spreadsheet. But when I press the ctrl+; shortcut for this, I seem to switch to a ...
user avatar
  • 151
0 votes
0 answers
17 views

How to follow precedent into another sheet in LibreOffice Calc

I am trying to analyze complex data sheet full of formulas referring to another cells using Tools - Detective - Trace Precedents/Dependents. It works fine until the source for the formula calculatiion ...
user avatar
  • 111
0 votes
4 answers
157 views

Excel: How to convert a column letter to the corresponding column number without using COLUMN() and INDIRECT()?

The standard solution seems to be =COLUMN(INDIRECT(letter&"1")) but there should be a better solution that avoids the volatile INDIRECT function. Sample Column Letter Corresponding ...
user avatar
  • 101
0 votes
1 answer
29 views

How to remove large white blocks from Excel preview in Google Drive?

Does anyone know how to remove these large white blocks from the preview of an Excel file in Google Drive? The file itself is somewhat old and has gone through many large changes, including ...
user avatar
  • 5,646
3 votes
2 answers
179 views

When filtering data, is it possible to put in a cell the value of the filter?

I have this spreadsheet (link to google drive --- notice that the online app is not showing the filtering, you need to download it...): It works very well and lets me see the total when I filter ...
user avatar
  • 186
0 votes
0 answers
17 views

What's (if any) the formula to get multiple matching values in LibreOffice Calc? [duplicate]

Suppose I have the following data: A B 1 idx1 VAL1 2 idx2 VAL2 3 idx3 VAL3 4 idx4 VAL1 5 idx5 VAL4 6 idx6 VAL1 7 idx7 VAL1 8 idx8 VAL2 9 idx9 VAL5 10 idx10 VAL3 Is there a LibreOffice Calc ...
user avatar
  • 167
2 votes
1 answer
27 views

How to return in a cell all the matching values present in a column?

Suppose I have the following data: A B 1 idx1 VAL1 2 idx2 VAL2 3 idx3 VAL3 4 idx4 VAL1 5 idx5 VAL4 6 idx6 VAL1 7 idx7 VAL1 8 idx8 VAL2 9 idx9 VAL5 10 idx10 VAL3 For each "VAL[n]" ...
user avatar
  • 167
2 votes
1 answer
52 views

How to populate a cell with a comma separated list version of a list-like text?

Suppose I have the following data in a cell (note: list-like text with minus signs and line breaks): - val1 - val2 - val3 How can I populate another cell with a comma separated list version of the ...
user avatar
  • 167
0 votes
1 answer
67 views

LibreOffice Calc: How to make AutoFilter changes permanent?

When I apply an AutoFilter to my LibreOffice Calc spreadsheet, it's natural to have some rows removed, like in this screenshot. However, it's been clunky dealing with it, and I don't like this ...
user avatar
2 votes
2 answers
78 views

How to return multiple match results and output them to a cell as concatenated text?

Suppose I have the following data: A B C D 1 val1 Str1 2 ValM Str2 3 val2 Str3 4 ValM Str4 5 ValM Str5 6 val3 Str6 7 ValM Str7 8 val4 Str8 9 val5 Str9 10 ValM Str10 I would like to: find ...
user avatar
  • 167
1 vote
0 answers
22 views

Graphing coordinates in Calc using direction, distance, and speed

Suppose I have the following data: Location Direction (Degrees) Distance Speed Origin N/A N/A N/A A 090 5 50 B 180 15 75 I can make a chart using the following data to show the path something ...
user avatar
  • 177
0 votes
0 answers
23 views

concatenate text to create math that can be calculated

I'm not sure if the title is descriptive enough! I've used the CONCATENATE formula to create the following: =$'2022'.a17 It goes like this: =CONCAT("=$'";TEXT(A20;"YYMM");"'....
user avatar
1 vote
1 answer
51 views

Cumulative count of non-empty cells that aren't contiguos

I want to write a formula that gives the cumulative count of non-empty cells that aren't contiguous. Like an index count column for lack of a better term. A - Count B - mix of empty and non-empty ...
user avatar
  • 655
0 votes
2 answers
141 views

How to get the type of a cell in Libreoffice Calc?

I would like to know if a cell is a date, a number, a string or what. It would help a lot, exactly what/how should be done with it. I think, librecalc knows it, because it affects the behavior of the ...
user avatar
  • 2,372
0 votes
1 answer
368 views

How to generate a column of unique IDs (UUIDs) in LibreOffice Calc?

How to insert a column of unique codes (UUIDs) in LibreOffice? If that's not possible with LibreOffice itself, how could one generate UUIDs in a way so that they can be simply copy and pasted into a ...
user avatar
0 votes
1 answer
59 views

How to combine the values from multiple rows in a column (separated by commas) where there are duplicates in another column

I have a large dataset where I want to combine the values of multiple rows in one column where there are duplicates in another column. See my Example below: CURRENT DATA Product Type Email Product 1 ...
user avatar
  • 135
0 votes
2 answers
89 views

How to paste numbers with a different separator in LibreOffice Calc

I tried selecting the cells and setting their format as “Text, English (USA),” and set the language in the status bar to “English (USA)” as well, but when I paste data from this page, e.g. Food kcal ...
user avatar
3 votes
1 answer
330 views

Include an "at" symbol ("@") in a text cell

I'm organizing some stuff in a spreadsheet. None of it should be interpreted as anything other than than text, but it does contain a lot of non-alphanumeric characters. If I include the character @ in ...
user avatar
0 votes
2 answers
312 views

How to solve freezing of LibreOffice 7 on Mac OS X Sierra issue?

Using LibreOffice 7 (Calc) on Mac OS X High Sierra. It constantly freezes (turning colored wheel) so I have to force quit it. How should I proceed to find the cause? Is there a log somewhere that I ...
user avatar
  • 954
0 votes
1 answer
29 views

LibreOffice Calc - do not apostrophe-escape dates when opening a file

I have switched system, and the old system had locale en_DK with reasonable ISO formats, the new one currently has en_US with "imperial" stuff, bad date formats etc. While I try to figure ...
user avatar
  • 111
0 votes
0 answers
61 views

How to make LibreOffice Calc correctly interpret the desired number format for Time

I am appending to a CSV file programmatically with Python, which currently contain timings in the format: 0:1:10 (e.g. representing 0 hour, 1 minute, 10 seconds). When I open the CSV file in ...
user avatar
  • 1,327
1 vote
0 answers
20 views

How to set custom alpha in paired t test at libre office calc?

In libre-office calc I can perform a paired t test with alpha 0.05. In Excel I can change this value to e.g. 0.01. But Libre-office calc I couldn't find any setting how to change the intial alpha ...
user avatar
0 votes
1 answer
45 views

How to use a non-contiguous range for diagram?

(Similar to question How do I select non-adjacent ranges in libreoffice calc?, but not identical) I have a time-based series of data (date&time, value), and I have a diagram showing the data. The ...
user avatar
  • 314
0 votes
0 answers
32 views

Libre Calc - color code a number of columns based on one column

I have a number of columns of data (one for each month) that I would like to color code based on the average value for all months. So each row in each column should be compared to the row in the '...
user avatar
2 votes
1 answer
86 views

Libreoffice: Fill down without affecting format

If I fill cells in libreoffice by dragging on the small black rectangle (on the bottom right corner of a cell), it doesn't only fill the formula (which was my intention) but also the format (font, ...
user avatar
  • 143
1 vote
1 answer
149 views

LibreOffice Calc: Use different max value for color scale conditional formatting in each row

I have a dataset like the following (only much larger): Each row represents a task with a maximum number of points available in column B. Every column starting at C represents a Person with their ...
user avatar
0 votes
0 answers
77 views

LibreOffice Calc button control: delayed response to mouse left-click only

I have the following problem when using LibreOffice Calc 7.2.2.2 (x86) on Windows 10 pro build 19043.1288 (I had uninstalled the 64-bit version and replaced it with the 32-bit version in the hope that ...
user avatar
  • 111
1 vote
1 answer
240 views

How to highlight the min and max value in a row wise manner?

In LibreOffice Calc (6.0) I try to highlight the minimum and maximum of each row in group of column (col A to E)? Any idea how to solve this?
user avatar
1 vote
1 answer
45 views

Libreoffice Iterative Calculation is not automatically converging

I have a circular set of equations with a corrector function built in to converge on a solution. When I change the input value, the dependent cells begin to calculate but stop prematurely. Should I ...
user avatar
  • 508
0 votes
2 answers
238 views

Why does index match get wrong result in Microsoft Excel?

The formula that I use is =INDEX(F:F,MATCH(K2,A:A)*(B:B=L2),0)
user avatar
0 votes
2 answers
66 views

"Update Links when Loading" - what exactly it updates?

Tools > Options > LibreOffice Writer > General > Update Links when Loading What is this setting is actually used for? Which links does it update? (For example, when I moved my document ...
user avatar
  • 833
1 vote
1 answer
79 views

Date + 14 days. The date is generated by a regular expression

I have a speadsheet in which the first column is like Column 1 ------------------------- Foo 1234 (22.09.21) Baaaar 5678 (20.10.21) And a second column contains dates only and is generated from the ...
user avatar
  • 833
0 votes
1 answer
105 views

Remove metadata from XLS in LibreOffice

Having a XLS file I created for my father back in the day. I am using a copy of this for myself to generate some documents I export to PDF. Issue is that the file's metadata put my fathers name as the ...
user avatar
0 votes
0 answers
46 views

How to make a conditional format apply to all cells in Libreoffice Calc?

I've made a conditional format color an entire row green when a formula is true. However, it's only applying to a specific range of rows, and I'm wa to make it apply to all rows. My formula is: AND($...
user avatar
  • 7,600
0 votes
1 answer
126 views

How to edit a column in Libre Office Calc to set all URLs as clickable links?

I have a column with many URLs, some of them are not clickable (as blue), I need to select one by one, then ctrl+x, ctrl+v, then enter to transform URLs as text to HTTP links. It's very annoying, I ...
user avatar
0 votes
1 answer
42 views

Combining several rows to one data range to circumvent the 1024 column restriction

I want to work with a larger data set (deaths per week since 2000), exceeding the 1024 columns restriction of LibeOffice Calc. My source has got 52 (or 53) numbers per row for each year. I'Ve manually ...
user avatar
  • 101
0 votes
1 answer
220 views

LibreOffice Calc "Find" command matches row indices

I wanted to search a certain value in a column, so I naturally selected the column, CTRL+F, and entered the value to the box. However, LibreOffice matches all cells with a row index containing my ...
user avatar
  • 133
0 votes
2 answers
293 views

How to *format* time duration as hh:mm:ss in LibreOffice/Excel?

I would like to copy the time duration (in seconds) to a cell, then have it displayed as (hh:)mm:ss or similar. For example: Input: 0.49 Display: 0.49 or 0.49s or 490ms (any will do) Input: 857.26 ...
user avatar
  • 133
0 votes
1 answer
105 views

How to create a summary report from a LibreOfficeCalc PivotTable

This is a question about the feasibility of solving a problem with LibreOffice/Calc. I have a table of data of many years with basically the following columns: date/time ( Y/M/D time ) quantity Now, ...
user avatar
0 votes
1 answer
460 views

Get minimum value from column B where column A is less than or equal to cell value

In LibreOffice Calc I have created a die rolling sheet. In A6:B1005 I list the values in column A and number of die rolls with that value in column B. Example with a d10: Die Value Number of rolls 1 ...
user avatar
1 vote
1 answer
86 views

How to reject LibreOffice Calc autocomplete suggestion?

LibreOffice Calc likes to aggressive suggest cell content based on a prefix. It is easy to accept the suggestion by hitting ENTER. But how to I reject it, which is something that is needed even more ...
user avatar
0 votes
0 answers
21 views

LibreOffice Calc — line chart double zero unit at XY-axis crosspoint

The line chart in LO Calc has two zeroes, one for Y-axis and one for X-axis (see attached pic). This is wrong in my opinion and looks bad. I've been trying to figure out how to get rid of one of the ...
user avatar

1
2 3 4 5
13