Tag Info

Hot answers tagged

26

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


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 &


17

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


15

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.


15

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


13

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)


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

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)


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

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


7

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


7

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

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

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


6

Copy > Paste Special > Check/Select "Transpose"


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

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

=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

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


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

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

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


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

As given in reference: Date Arithmetic, this adds one to the month: =DATE(YEAR(A1), MONTH(A1)+1, DAY(A1))


4

If you click on the y axis line itself, you can get a selector at both ends of the y axis. (This is frustratingly difficult, as you often end up selecting the chart, or one of the ranges, or something else). Once you have selected the y axis, you can right-click and choose "Format Axis..." On the 'Scale' tab, check the "Reverse direction" check box. I ...


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

You're nearly right. You can adapt your formula slightly like this: {=SUM(IF(A1:A5=B1:B5,1,0))} Type this in without the curly {} brackets, and press Ctrl+Shift+Enter so that Excel knows it's an array formula. The curly brackets represent the fact it's an array formula.



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