Tag Info

Hot answers tagged

26

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 &


25

Just hit SHIFT+CTRL+V or Paste Special... from the Edit menu, unselect Paste all, and select Text or Numbers:


14

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 ...


14

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 ...


13

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.


11

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.


11

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 ...


8

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 ...


8

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, ...


7

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)


6

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.


6

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 ...


6

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 ...


6

Copy > Paste Special > Check/Select "Transpose"


6

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 ...


5

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.


5

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.  


5

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.


5

=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.


5

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)


5

you can try FLOOR function, floor(A1,1) for integer part of A1, A1-floor(A1,1) for decimal part of A1.


5

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. ...


5

From the "Data" menu, choose "Text to columns".


5

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 ...


4

There are (at least) two solutions: Using a pivot table; Using the COUNTIF function together with a list of distinct values. 1. Pivot table The following pivot table layout... will give you the following output: 2. COUNTIF and distinct values Another approach involves the COUNTIF function. You can create the "result table" in two simple steps: ...


4

Try following (B3 is used as the first cell in this example): Get remaining rows from current cell to the end of the column: 65536-ROW($B$3)+1 Select range from current cell to the end of the column: OFFSET($B$3,0,0,(65536-ROW($B$3)+1),1) Then count: =COUNTA(OFFSET($B$3,0,0,(65536-ROW($B$3)+1),1)) NOTE: If you are using LibreOffice Calc v3.3.3 or ...


4

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), ...


4

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 ...


4

this is problem. In my case, I use two approaches - first is to insert the graph in Draw as metafile, disconnect it to get the particular elements and then edit the labels. Or create graph without labels, insert it as image in Draw and add labels. Second method is to create entire graph in other program - I use Scidavis, which is free and opensource: ...


4

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.



Only top voted, non community-wiki answers of a minimum length are eligible