Tag Info

Hot answers tagged

23

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


20

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 &


13

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


12

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.


10

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.


10

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


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

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

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


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


6

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


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

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


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

Just realized the answer by myself: Just mark the third column and click on Window->Freeze.


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

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

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


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.


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

I found a workaround. Hopefully somebody will have a better answer, but if not, perhaps this will help anyone else having the same problem. Here's the workaround (this was with LibreOffice 3.4.4): Open the .xls file Say "No" to the question Save the file as an OpenOffice.org 1.0 spreadsheet (.sxc) (not .ods, that won't work, see the update at the end of ...


4

Well, I'm using the German version of OO-calc, and there is a function in the group table called 'SVERWEIS', which is 'S.-Verweis' or 'Spalten~' alias 'column-link'. In column D, you write SVERWEIS(C4, E3:F5,2)*B3 for line 3, but you change it a little, to make the table absolute: =VLOOKUP(C4, $E$3:$F$5,2)*B3 and copy it down the column, at the bottom ...


4

Preface: I'll assume you're familiar with the Calc way of referring to Cells, which is ColumnLetterRowNumber and ranges are expressed like this: A10:C12 mean all cells between A10 and C12, so 3 rows and three columns Ok, I assume your data is in A1:B100 (or however many rows you have). Now, in D1 enter Project#, in E1 enter Start date and in F1 enter End ...


4

Have you seen Filter for unique values or remove duplicate values? Select the range of cells, or make sure the active cell is in a table. On the Data tab, in the Sort & Filter group, click Advanced. In the Advanced Filter dialog box, do one of the following: To filter the range of cells or table in place, click Filter the list, in-place. To copy ...


4

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.


3

In LibreOffice 4.1: Date: Ctrl; Time: CtrlShift;


3

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)



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