Hot answers tagged libreoffice-calc
You can remove the leading single quote (which actually isn't part of the string in the cell) using a regex-based search and replace: Search for all characters between the start and end of the string ^.*$ replace with match &
Just hit SHIFT+CTRL+V or Paste Special... from the Edit menu, unselect Paste all, and select Text or Numbers:
It's even easier than pnuts' solution. You don't need to select the cell that holds the value that should be relevant for conditional formatting. Just select all the cells that should get conditionally formatted, and use a formula-based rule. Now, if your formula uses a cell address with fixed column (e.g. '$D5'), OpenOffice will adapt it for every selected ...
It's a matter of formatting the cells. You will have to apply the format code [HH]:MM:SS. It's already predefined: (Screenshot from LibreOffice 3.4.3)
Yes Right-click on the chart and choose edit. Right-click on the pie and choose insert data labels. Right-click on a data label and choose format data labels. You can choose Placement, set to Inside. You can check the box to show value as percentage.
Yes - you can define the range of selected cells manually, and after that, "fill" the formula into all selected cells. This may take some time if the formula is applied to 150,000 data sets, so maybe you should proceed stepwise (applying for rows 1 to 50,000, 51.000 to 100,000 and so on - or use a database as proposed by m0skit0). The cell that's currently ...
Format menu -> Styles and formatting. You should get a dialog that looks similar to this: Right click on Default, and choose modify. In the fonts tab you should be able to choose the new default font.
From the "Data" menu, choose "Text to columns".
If you want to use the customized document as standard template (so it's used if you select menu File -> New -> Spreadsheet), the following steps are required (assuming you've created a fresh spreadsheet document and changed the default font as Kirk has described): Select menu File -> Templates -> Save...; In the Template dialogue, enter a ...
I was on the wrong path. What I wanted was Data->Subtotals. Then you pick the column to use as a control-break (in my case, Invoice_Date), the column that you want to subtotal on (in my case, Invoice_Amt), and what you want to calculate. (I wanted SUM(), but you could do AVG, MIN, MAX, etc)
You can't use the page preview options to scale the PDF output. Output scaling has to be done using the Sheet properties in the Page Style dialogue (Select the Menu Format -> Page... -> Sheet Tab). There, you have different options to make the Calc output fit to a certain number of pages. In the following screenshot, the Scale options are highlighted, ...
Updated instructions for LibreOffice 4.0.x: The process is still as per tohuwawohu's answer, yet the specific steps have changed a little: To save as template, go to menu File > Save as template... Select "My Templates" in the Template Manager, and then click on the button "Save". See image on the next step. A new window will appear asking for a name ...
Yes, this is possible. You need to select the XY (Scatter)chart type. If you start with this data table: ..., select it, start the chart wizard and select XY (Scatter), the result preview already shows that the data points are córrectly spaced: (NB: i didn't customize the wizard's defaults yet - Calc did recognize the columns correctly). The only ...
TRUNC() is designed to remove the decimal part of any number immediately and without any modification to the non-decimal part. So, as per LinYan's answer, you just need to use: TRUNC(A1) to obtain the integer part of the value in A1 A1-TRUNC(A1) to obtain the fractional part of the value in A1 Unlike FLOOR(), TRUNC() works on both positive and ...
=edate(a1;1) edate returns the date that is the specified number of months after or before the specified date. First argument of edate :start date. Second argument of edate : number of month. If negative, edate calculates the date before.
Grab the Open XML SDK 2.0 and run the "Open XML Productivity Tool for Microsoft Office". This is a neat application that lets you perform several analyses on the XML data within an .xlsx or .docx, and should make it easy for you to see where the bloat is by using its comparison tool to compare two documents' contents on an XML level. There are a few ...
If your strings are of unequal length you can use the mid() and len() commands combined. In your example the command would be: =MID(A1,2,LEN(A1)) Drag it down as in the answer above. How does it work? MID returns a sub-string of a string specified as: MID([string], [start index], [end index]). If the [end index] varies, using LEN(A1) specifies the length ...
Just realized the answer by myself: Just click on the cell for which you want cells above and to the left of to be frozen. Then, click on Window->Freeze.
Copy > Paste Special > Check/Select "Transpose"
I found the solution: the formula in column C should be like this: (C3 cell) =B3*$A$30 This way, on copy-paste only the B3 reference is rewritten, the A30 is left unchanged. The $A$30 syntax can be achieved with Shift+F4 too.
One way would be to use a Pivot Table. Select the data table including the headings then choose Data/Pivot Table/Create/Current Selection. In the pivot table dialog click and drag the Invoice_date to the Row Fields section and the Invoice_amt to the Data Fields section. The default is to Sum the Invoice_amt. By default the pivot table will be put on a new ...
Ehm, I am risking to say something silly here, but why don't you just Get total number of rows: ROWS(A:A) Convert to coordinate of the last cell using INDIRECT: (INDIRECT("A"&(ROWS(A:A)))) And use it in your SUM formula SUM(A2:(INDIRECT("A"&(ROWS(A:A))))) I cannot guarantee that this is going to work, as I am currently logged into my ...
you can try FLOOR function, floor(A1,1) for integer part of A1, A1-floor(A1,1) for decimal part of A1.
Even easier, but overwriting the original values: Copy the Constant using Ctrl+C Select the values that should get changed; Use "Paste Special" (Edit -> Paste Special or Ctrl+Shift+V), select "Add" as Operation: Result: the values are replaced by the result of the addition with the constant.
For example, imagine A1 is 167.583 : int(A1) would give 167 and mod(A1,1) would give 0.583 .
You have the choice to embed or link to an object when you insert content into your document from another source. Paste Special also gives you a choice to link objects from the clipboard. Each method has it's own benefits. With embedding, all the content is contained within a single file. This simplifies distributing documents or moving them around, as ...
Very often the time value is presented as a string ie. appears as '01:00:00 when formatted as time. There are two solutions to this: To delete the single quote. This is extremely tedious if there are more values To create another cell which uses a formula to convert to timevalue. If eg A1 contains '01:00:00 then create another cell with =TIMEVALUE(A1), ...
Select the cells you want to change. Key shift-F4 to cycle through the different forms of reference, A1 -> $A$1 -> A$1 -> $A1 -> A1. This changes all cells in the selected range. This works with non-contiguous ranges too. Tested on Libreoffice 3.4.5 on linux and 3.5 on Windows.
Yes, you need other software, such as MS Excel. What you are looking for is a missing feature in Libre Office Charts. Many people would like to have the ability to plot a date on the X-axis when using area charts, but the software only supports the use of ORDINAL numbers. This is a glaring defect in the software that has been present for a long time. ...
In Microsoft Excel 2007: Copy (not cut) the data to be "flipped". Click in the upper left corner of a non-overlapping region where you want the data to be flipped into. (You might want to do this in a new worksheet.) Click on the arrow (triangle) below the Paste icon. Click on Transpose.
Only top voted, non community-wiki answers of a minimum length are eligible