25

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

12

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 complicated, forces me to rewrite formula style and consider possible errors).
The solution is so simple it can be done in 2 seconds:
Write your new ...

12

Highlight all of the cells in the table (and only those cells) then click the Convert to Range button in the Table Tools/Design tab (screenshot below).
Now that this data is no longer grouped as a table, it will appear in the Name Manager as separate columns/rows (depending on how you defined the range) with the delete button no longer greyed out.

9

This happens because of the limited precision of floating point numbers representation on computers and cannot be fixed because it is an intrinsic problem of the way number are represented on a computer. There might be workarounds, such as round() or a clever implementation of the formulas to reduce error propagations.
Excel uses 8-byte (64 bit) floating ...

9

You can absolutely do this with Tables, as well as Powerpivot and Data Explorer. The easiest, and my preferred method currently, is to create a parameterized query in Excel. To do this use the Query Wizard and at the end add constraints for each parameter you want to pass. Once this is done, you can edit the Connection Properties and assign the Parameter ...

9

In Google Sheets:
=INDEX(SPLIT(A1,","),2)
Where the 2 is the index you want.
In Excel:
=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),(2-1)*999+1,999))
Or this array formula:
=INDEX(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),(ROW(INDIRECT("1:" & LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*999+1,999)),2)
Being an array formula it needs to be confirmed ...

8

Select both rows (using the Shift key or by dragging the mouse - don't use the Ctrl key)
Copy
Right-click → Paste Special
In Region operation, choose Flip Vertically

8

I have consulted multiple sources, and there is no fix for Microsoft Office.
However, I installed LibreOffice, and it handles CSV files the exactly correct way. I made LibreOffice the default program for opening CSV files, and the problem is now solved.

8

In a quick test, I was able to accomplish what it sounds like you are wanting with the following.
Using CTRL + Left Mouse Click, Highlight one rows worth of cells that you want included in the validation; F, J, and N in your example.
Go to the Format menu and choose Conditional Formatting
In the Conditional format rules pane, change the "Format cells if..." ...

7

Excel isn't changing the cell's value.
This is evidenced by the fact your sum operation is returning the correct result.
Instead, Excel is formatting the cell in 24-hour time notation. In this notation, 32 hours is actually "1 day and 8 hours". In this case Excel only shows the hour portion, not the days, hence the output 8:00:00. But formatting never ...

answered Feb 13 '18 at 16:37

I say Reinstate Monica

23.4k1818 gold badges8484 silver badges123123 bronze badges

6

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.
For me it was simple.
Click the cell whose formula you want to copy once (select it)
Copy the cells contents (Ctrl+C on Windows, cmd+C on macOS)
Hold Shift+...

6

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 quick three color tables (head color plus 2 alternating colors over other rows). Source ask.libreoffice.org, tested still working in Version: 5.1.4.2
There is ...

5

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 saves a lot of time when you have a 7,000 row sheet you are working with.

5

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

5

The way I understand your description of the problem, you have a list of employee names in column A ("Name") and a list of hours for each employee in column B ("HoursWorked"). Some employees are listed multiple times. You want to get a list of each employee once along with their total number hours.
The easiest way to do this would be a pivot table:
Select ...

answered Apr 23 '13 at 6:13

Stephen Jennings

22.6k55 gold badges6868 silver badges104104 bronze badges

5

You need to use FALSE for the range_lookup value.
From VLOOKUP help
Range_lookup A logical value that specifies whether you want
VLOOKUP to find an exact match or an approximate match:
If TRUE or omitted, an exact or approximate match is returned. If an
exact match is not found, the next largest value that is less than
lookup_value is ...

5

Select the Pivot Table
On the Analyze Ribbon - Click on Select-> Entire Pivot Table
Right Click Copy in the pivot table area
Go to the location where you want to place the copy of the pivot table and right click and select Paste Special ->Keep Source Column Widths (W)
This should give you what you need. Hope this helps

5

Troubleshoot the formula step by step. Start with a simple
=IF(F1>0,TRUE,FALSE)
and copy down. If the result shows TRUE for all rows, then your source data is the problem. You may have text that looks like numbers.

5

There is a Libreoffice extension named OpenOffice.org2GoogleDocs that is aimed at this purpose.
It works by downloading the files from Google Drive to the computer hard disk in a user-chosen directory; it can download files from Google Drive and upload to that cloud service documents on the user's system. The whole process is made by downloading files, ...

5

Your value type in D6 might be a number and you might be comparing it with a text value, try removing the quotes around the number 30. This might solve your problem. (I don't have excel around).

5

Enter the first number of the sequence
Select the range to fill (SHIFT+Down)
Use Alt to navigate the menu: Alt+e, i, e, Enter
This is a shortcut for Edit->Fill->Series
As a mnemonic, adapt the children's song "Old Macdonald," substituting IO (Input/Output) for the Enter key:
Old Macdonald had a series of animals, (alt) E I E IO

5

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.

5

What you are looking for is called "Cartesian Product" or "outer join".
The solutuion as VBA. Copy the following code into the right-hand side of the Visual Basic Editor. You will see that your macro now has a name "cartesianproduct": (Source)
Sub cartesianproduct()
Dim startrange As Range
range1 = Application.InputBox(Prompt:="Please Select First Range", ...

5

4,8,2
Formulas for each number.
=LEFT(A1, SEARCH(",",A1,1)-1)
=MID(A1, SEARCH(",",A1) + 1, SEARCH(",",A1,SEARCH(",",A1)+1) - SEARCH(",",A1) - 1)
=RIGHT(A1,LEN(A1) - SEARCH(",", A1, SEARCH(",", A1) + 1))
Source: https://www.ablebits.com/office-addins-blog/2016/06/01/split-text-string-excel/
Note: This will only work for three comma delimited numbers.

5

LO Calc has built-in functions for that equivalent to Excel's audit functions. In Calc, it's called Detective, and is available from the Tools tab of the menu. Tracing precedents and dependents also have keyboard shortcuts, which are shown in the Detective menu.
Shift-f5 will place arrows from the current cell to all cells with formulas that reference ...

5

The cell reference acts as a variable:
=COUNTIF(A1:A10,C11)

4

It's the number of days passed from Friday, December 29, 1899 and your date.
References & proofs:
Wolfram|Alpha - calculation proof
System time (Wikipedia) - note that on other systems / software this date is different.
Data types in VB - scroll down to "Notes for Date data type".
Microsoft Support - "Office spreadsheet" proof. Note that Excel's ...

4

Placed in cell A1, the formula
=MOD(ROW(A1),10)
will repeat with values 0-9 when you fill it down. You can then use filtering to delete the rows that you don't want. Note that the result of the formula will then change if you delete (rather than clear) the rows. (Although filtering doesn't update until you ask it to.)
Alternatively,
=MOD(ROW(A1),10)=0
...

4

On the Global Client List, go to the cell you want and type = (DO NOT HIT ENTER)
Then go to the sheet with the info (Client 1) and click on the cell you want to copy and hit enter.
Do the same for another Global Client List cell and Client 2, etc.

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

#### Related Tags

spreadsheet × 605microsoft-excel × 409

worksheet-function × 175

google-spreadsheets × 75

microsoft-excel-2010 × 65

libreoffice-calc × 61

openoffice × 51

openoffice-calc × 44

libreoffice × 35

microsoft-excel-2013 × 24

iwork-numbers × 23

microsoft-excel-2007 × 21

microsoft-office × 20

csv × 19

microsoft-excel-2016 × 15

sorting × 13

database × 12

conditional-formatting × 12

macros × 11

google-docs × 11

vba × 10

cells × 10

microsoft-excel-2003 × 9

gnumeric × 9

pivot-table × 8