40
votes
Is there a way to scroll, by pixel, instead of by cell, in either Excel or OpenOffice Calc?
I see you've gotten a lot of unhelpful answers, and this one probably adds to that list. It is not possible to do this in Excel. There was a conversation with an Excel MVP in 2013 on this topic that ...
- 1,359
23
votes
LibreOffice pivot table group by month
I'm not sure whether this was possible in LibreOffice that was current in the time when this question was asked but now this is quite easy to do, so if anyone stumbles upon this question this might ...
- 331
16
votes
Accepted
How to find and remove external links in (Libre|Open)Office Calc spreadsheet
I also got that popup on startup:
I had the same problem and found out today that there is an option in the Edit menu called Links.... Select that option and you get a dialog with the list of ...
- 1,448
14
votes
How to create a formula for every row in a column in Google SpreadSheet?
The suggested answers work well for small sheets but I had thousands of rows and using the mouse or the keyboard to select them was simply too time consuming.
The ARRAYFORMULA method works but it's ...
- 316
13
votes
How to highlight whole row and column under cursor in OpenOffice Calc?
Not exactly as requested, but SHIFT+SPACE / CTRL+SHIFT+SPACE selects current row / column respectively (at least in LibreOffice Calc - i didn't test it with OpenOffice!). To unselect, just move the ...
- 10.1k
13
votes
Accepted
In LibreOffice Calc is there a hotkey to edit the current cell?
F2, Same as in Microsoft Excel.
Once done you can press Enter to switch to next row.
- 246
11
votes
OpenOffice Calc weighted average
Since this is the second result on my Google Search of "LibreOffice Weighted Average" I may aswell give it an answer:
The Weighted Average can be calculated by the use of the function:
=SUMPRODUCT(...
- 503
8
votes
Accepted
Get DayName from Date in OpenOffice Calc?
You could simply format the cell to display the day name instead of an integer.
In the 'Format Cells' dialogue box (right click and choose 'Format Cells...' to open) enter:
NNNN
NNN
or
NN
in the '...
- 330
6
votes
How to find and remove external links in (Libre|Open)Office Calc spreadsheet
Every time I opened up a Calc Sheet I had copied, I received the same pop up "This file contains links to other files. Should they be updated?"
The spreadsheet I was opening was copied from another ...
- 71
6
votes
How to create a formula for every row in a column in Google SpreadSheet?
Very similar to ceoliphant's answer but a little more straightforward, simply add one formula to C1:
=ARRAYFORMULA(iferror(A:A*B:B*1.6))
- 241
6
votes
How to create a formula for every row in a column in Google SpreadSheet?
I found all of these solutions very frustrating and confusing also.
I will warn you though, this will replace whatever is currently in the cells, but as it is a formula this should not be a problem.
...
- 365
6
votes
Highlighting every other row in LibreOffice (or OpenOffice) without conditional formatting?
From menu: Format → AutoFormat Styles.. . You could add your own.
Another option is to use macros (as in ngulam's answer).
Color2Rows is an extension that adds a button to the tool bar, for ...
- 658
5
votes
How to create a formula for every row in a column in Google SpreadSheet?
After you write your forumla, you can double click the bottom, right corner of the selected cell with the blue box, to copy the data down the column down as long as a neighboring cell has data.
This ...
- 332
5
votes
Accepted
Can you reference an entire column in OpenOffice Calc (like A:A in Excel)?
No, but it will be available in a future version of Calc: https://bugs.documentfoundation.org/show_bug.cgi?id=44419
Edit: Actually, it's already downloadable in "LibreOffice Fresh".
Also this ...
- 7,410
5
votes
LibreOffice Calc equivalent to Excel "Evaluate Formula" feature
I'm answering my own question with what I know so far, anyone who can provide better solutions/news about new Calc features, really welcome!
As far as I know, what comes closest in Calc to Excel "...
- 1,914
5
votes
Accepted
Issue with IF statement in Libreoffice Calc
From what I can see, you have a missing quote:
=IF(L7>11,"$500,IF(L7>11,"$1200",IF(L7>15,"$2000",$0)))
^ ^
...
- 1,448
4
votes
Get DayName from Date in OpenOffice Calc?
If you only want to display day name and retain it's value - use user defined format of NNN.
If you want to actually change it to text - use TEXT(WEEKDAY("2008-06-14"; 1);"NNN")
- 7,042
4
votes
Accepted
Fill right in OpenOffice/LibreOffice
It turns out there's no hot-key for it (by default anyway). But you can get it by going to menu Edit → Fill → Right.
In LibreOffice version 5 it is menu Sheets → Fill Cells.
- 1,315
3
votes
Is there a way to scroll, by pixel, instead of by cell, in either Excel or OpenOffice Calc?
I had the same issue. Chosing Excel because of the controlled cells for text.
My solution was to keep the heights of the cells in the sheet, thus scrolling floats smoothly. The cells with text were ...
3
votes
Inserting an image into a OpenOffice.org Calc cell
Insert image in the normal way, then right click, Anchor > To cell.
There you have it. I don't understand why is it necessary to put in background, it's just working fast and fine.
- 31
3
votes
Get DayName from Date in OpenOffice Calc?
You can achieve this by converting your result into a text. To do this you write:
=TEXT(7,"DDDD")
The output is:
Saturday
A solid alternative would be:
=TEXT(WEEKDAY("2008-06-14", 1), "DDDD")
...
- 141
3
votes
Accepted
Pasting Hex Colors Into OpenOffice
I have LibreOffice instead of OpenOffice but I believe the process should be the same since they are mostly the same program:
Copy the table
Go to Calc and Use Edit->Paste Special... or press Ctrl + ...
- 11.1k
3
votes
Alternate If() function in Excel/OOCalc
User tohuwawohu posted an answer that led me directly to mine. I don't think tohuwawohu quite understood what I wanted, perhaps through my fault, but his macro suggestion helped me to understand. I ...
- 133
3
votes
Enter the same data into multiple, non-adjacent cells in OpenOffice.Org Calc
AFAIK, you can't do this for non-adjacent cells in OpenOffice or LibreOffice Calc (this is different from Excel - there, you can use CTRL+Enter on non-contiguous ranges). For adjacent cells, use ALT+...
- 10.1k
3
votes
Accepted
Adding a percentage to every cell in a column in LibreOffice calc
If that update is to be static, as in "just change the numbers in the first column";
Type 1.03 in any cell
Select the cell with 1.03 in it, hold CTRL and hit C
Select the cells with numbers in the ...
- 8,434
2
votes
Compound Interest in Open Office Calc
There are many financial functions in OpenOffice.org Calc, but you need to be very clear about exactly what kind of "compound interest" transaction you are trying to calculate.
The IPMT(), PPMT(), ...
- 265
2
votes
How to create diagram in spreadsheet with dates on x-axis?
I suppose by now your boys are already in school. But if you are still logging their growth (or for other similar problems), I have an not bad solution. Here is an example chart that I made based on ...
- 297
2
votes
How to find and remove external links in (Libre|Open)Office Calc spreadsheet
Had the same problem myself, and after reading the discussion here, I tried copying the sheets I wanted to a new file (in my case this was easy as I only had one sheet), and that fixed it for me. It ...
- 195
2
votes
How to get VLOOKUP to return the *last* match?
(Answering here as no separate question for sorted data.)
If the data were sorted, you could use VLOOKUP with the range_lookup argument TRUE (or omitted, since it's the default), which is officially ...
- 231
2
votes
Using ISBLANK formula to write a variance
You want e4 to remain blank until data is entered in d4?
try entering into e4:
=if(d4="","",d4-c4)
If you could have a blank in c4 then try
=if(or(c4="",d4=""),"",d4-c4)
If you're using open ...
- 395
Only top scored, non community-wiki answers of a minimum length are eligible