Questions about spreadsheet software in general and interoperability between spreadsheet programs.

**0**

votes

**2**answers

486 views

### How do I add ROUND function to formula

I have following formula, and need it to round to two decimal places
=IF(ISBLANK(N2),"",10/(N-1))
How do I do this?

**1**

vote

**2**answers

13k views

### How do I calculate the total difference between two columns of figures in Google spreadsheet/Excel?

Usually I would just calculate the difference of each row like this: =B1-A1
Then I would sum up the values of the column like this =SUM(C1:C10) and get my desired value.
I don't want the C-column. ...

**0**

votes

**1**answer

279 views

### Split a file with macro in excel

I know there are 2 similar questions.
I have a very large Excel file separated in sections by 3 blanks lines.
I was wondering if there was a way to split the spreadsheet into other every time there is ...

**0**

votes

**1**answer

24 views

### Excel 2007 removing entries which have data in two columns which may not match exactly

I have an Excel spreadsheet with over 10,000 entries. This spreadsheet contains two columns of dates, and I need to select only those entries where the dates do NOT match in the two columns.
The ...

**1**

vote

**1**answer

320 views

### Paste from text editor to spreadsheet keeping CR in one cell

I have tried this in Notepad and Notepad++, but can't get it to work. What I want to do is close to the opposite of this post: How to copy multi-line text from Excel without quotes? I want to paste ...

**0**

votes

**3**answers

4k views

### How to change the hh:mm:ss format in spreadsheets

Currently, I have a data set which is "mm:ss" and needs to stay / be calculated that way.
The problem is, Microsoft Excel, Google Spreadsheets and OpenCalc all interpret "mm:ss" as "hh:mm" and to add ...

**-1**

votes

**2**answers

12k views

### How do I split single cell contents into multiple columns without seperators?

I have an excel spreadsheet that has the following:
Cell A1 ABCD 1234
I would like to split this to be
CELL A1: ABCD
CELL B1: A
CELL C1: B
CELL D1: C
CELL E1: D
I tried using the ...

**3**

votes

**1**answer

6k views

### Excel 2007 - changes on one sheet affecting other (without cell references)

Some strange behaviour I've encountered twice in Excel is that changes made to one sheet would be duplicated on another sheet in the same workbook. The first time, I typed values into a few cells on ...

**0**

votes

**1**answer

512 views

### Spreadsheet fill down for a formula

I want to add a formula to multiple cells in a column without typing them all out. now don't just tell me about the fill option as that does not do what I want (as far as I can tell).
When I used ...

**2**

votes

**1**answer

4k views

### Lookup the nearest value in a column?

I have a column (A) containing numbers between 0-200. By comparing the value of A against the values of C, I want to pick the nearest cell and thereby return the value of D.
So, if A is 160 I want to ...

**-1**

votes

**1**answer

290 views

### Ways to delete or isolate entire rows in a huge data collection

I'm working with a huge datafile (about 400,000 rows and 8 columns) in Libre Office. I need to get rid of certain rows so I selected all cells with the desired value (with Find & Replace) and ...

**2**

votes

**1**answer

1k views

**2**

votes

**0**answers

170 views

### Merging CSVs with some common columns (though columns are in different places)?

I'm looking to merge CSVs that have common columns, but the columns aren't always necessarily in the same place. The CSVs do have a header row. I just want to try to get the data appended to the ...

**0**

votes

**0**answers

86 views

### Place multiple columns onto one line [duplicate]

Let's say I have a table in a spreadsheet that works like a calendar:
Jan Fev Mar ...
1 5 7 1
2 3 4 9
3 8 3 1
4 2 9 0
5 2 1 2
...
And so on...
I need to make a chart ...

**0**

votes

**2**answers

1k views

### Merging 2 spreadsheets on Excel 2010

I was wondering if it was possible to merge data from 2 different spreadsheets together.
I'm looking at at world development indicators, so I have 1 spreadsheet which has human development index ...

**1**

vote

**1**answer

927 views

### Binary spreadsheets with bitwise operators [closed]

I am looking for a way to easily construct binary spreadsheets using bitwise operators such as SHIFT, AND, OR and XOR.
I've looked at Gnumeric but can't find any function that'd allow you to set the ...

**0**

votes

**1**answer

112 views

### Can Excel have some text above 1st page (and only 1st) of the table? [duplicate]

Possible Duplicate:
How to get the Excel header on each print page to be the first row of the table
Can we print Excel report like this ?
-------------------
Some description
Some ...

**3**

votes

**1**answer

1k views

### Numbers conditional formatting based on a different cell in the same row

I have a Numbers spreadsheet, with "quantity required" and "quantity in stock" columns, then each part is on a row. I'm trying to use conditional formatting to turn a cell yellow when # required = # ...

**1**

vote

**1**answer

2k views

### Excel Offset Function Returns #VALUE! Error When Combined with Row Function

Why does following Excel function return an error? The error occurs only when the reference that is returned is larger than 1 cell. In the formula, the returned reference is 1 row high and 2 rows ...

**3**

votes

**1**answer

872 views

### Sum the first N elements in a spreadsheet column

I'm trying to sum the first N elements in a column, where N is a value in another cell.
I've come up with this:
=sum(B1:indirect(concatenate("B", A10)))
Where A10 contains N. But I don't want to ...

**2**

votes

**2**answers

162 views

### How do I reorganize cells in a spreadsheet?

I want to take a spreadsheet with almost a thousand rows similar in format to this,
Sheet1
Q A1 Bool A2 Bool A3 Bool
Q1 Q1A1 1 Q1A2 0 Q1A3 0
Q2 Q2A1 0 Q2A2 1 ...

**1**

vote

**1**answer

6k views

### Is there a way to delete everything except what is selected in Excel?

I have an Excel template with 20 tabs (worksheets) and plenty of data in each sheet. When a user opens a copy of the template, he will only need to use one tab.
Is there a sneaky way to select that ...

**0**

votes

**0**answers

231 views

### Excel/OpenOffice spreadsheet template for expected and actual values

I would like to create a spreadsheet to generate graphs about cost items (anything with a cost, doesn't matter what) in order to have:
expected values: these would be created using some formulas ...

**6**

votes

**2**answers

5k views

### How to view large spreadsheet in Windows

What would you recommend to a non-technical Windows user for viewing (no need to edit) large spreadsheets (200MB - 5GB).
Format is CSV and I am told Excel chokes.

**3**

votes

**3**answers

4k views

### Deleting multiple selected rows in Excel

How can I delete multiple rows in Excel? If I mark everything and try to delete, it just deletes a single row. Even if I right-click and select delete, it still deletes one row at a time.
I need to ...

**4**

votes

**2**answers

2k views

### Automatically filling colors as per the values in the cell in spreadsheet

Question : Is it possible to fill each cell in a spreadsheet with a color whose grayscale value is same as cell value?
Explanation :
Consider I have a spreadsheet as follows :
All values lie ...

**1**

vote

**2**answers

2k views

### Create shortcut key to insert new row using Excel 2010

Tried to create a macro in Excel to insert a row:
Sub InsertRow()
'
' InsertRow Macro
'
' Keyboard Shortcut: Ctrl+Shift+I
'
Selection.EntireRow.Insert
End Sub
However, this does not insert a ...

**2**

votes

**2**answers

85 views

### What is { } used for in Microsoft Excel 2010?

I came across this function in one of my worksheets and wanted to know what the curly braces are used for.
Here is an example.
{=array(sheet1!a1,sheet2!a3,sheet2!a5,sheet2!a6)}

**0**

votes

**1**answer

479 views

### Rows disappear when removing duplicates libreoffice

I pasted a lot of data in Column A.
Then I removed duplicates:
Data > Filter > Standard Filter > More Options > No Duplication
Now I'm stuck in row 2632, i.e. I can´t scroll up to rows ...

**0**

votes

**1**answer

34 views

### Averaging unique columns in Excel

I'm probably missing something very obvious, but I have the following data:
ACCT 4
ACCT 2
CS 20
CS 10
ENG 20
ENG 10
ENG 30
What I want is to get the average of each department. So it should ...

**1**

vote

**0**answers

75 views

### Conditionally generate Excel sheet

I currently have an excel sheet that is used to record daily a bunch of parameters. Currently, I have a folder for each year, containing a folder for each month, inside of which I have an excel file ...

**1**

vote

**1**answer

827 views

### Numbers imported into Ubuntu Libreoffice calc from text or csv files are coming in as text

I working in Libreoffice 3.6.0.2 in Ubuntu. I have a text file that has some columns of text and columns of numbers. When I import them into Calc either by copying and pasting or saving as a csv and ...

**0**

votes

**2**answers

1k views

### Make comma separated list from column with blanks, fed by checkboxes

I want to make a spreadsheet where user can check boxes on one worksheet, and have those values then be brought over, comma separated, into one cell, on another worksheet. The values of the checkboxes ...

**0**

votes

**2**answers

3k views

### Complex rota hours calculation in excel

OK so there are a number of different shifts in operation here and I want a total hours formula.
problem is the rota is written using a number of different notations for different shifts
some are ...

**1**

vote

**3**answers

2k views

### What Excel formats are (most) compatible with LibreOffice and Google Docs? [closed]

I use Excel (and occasionally Numbers), but I want to be able to share with users of Google Docs and LibreOffice (and I may want to switch in the future). What's the most compatible format to save my ...

**3**

votes

**3**answers

2k views

### How do you find the maximum value in a column, skipping to every nth value?

Using LibreOffice's Calc program (nearly the same functionality as Microsoft Excel) I have a set of numbers in column T, which represent numbers for the week (with labels in S). Each week's worth of ...

**5**

votes

**5**answers

1k views

### Does any spreadsheet allow you to create your own functions?

I've always been frustrated when using spreadsheets by the inability to define my own functions when there is no existing function to (cleanly) do what I want. Does any spreadsheet offer this ...

**2**

votes

**2**answers

2k views

### OpenOffice Calc - Highlighting the higest value in a column

So I've got this spreadsheet open using OpenOffice Calc (ver 3.3.0) and its set up a little like this:
A B C D
1.name quantity price total
2.foo 10 ...

**1**

vote

**1**answer

917 views

### Referencing a single row to multiple rows in Excel

I have a worksheet with a list of sales, one sale per row. In order to import this data into my accounting software, I need to create a separate worksheet that formats the data differently. Each row ...

**3**

votes

**1**answer

902 views

### ADDRESS function nested in SUM returns Err: 502

When working on one problem, I came upon something that confuses me and was hoping for some clarification.
I am trying to use the SUM() function in a very round about way.
The formula
...

**2**

votes

**4**answers

4k views

### How to calculate how much I've worked per day?

I'm having a problem when I try to calculate how much time I've worked per day by using an Excel formula. It returns valid results for when I've worked until 12:00 PM but if I passes it, values come ...

**2**

votes

**1**answer

641 views

### Spreadsheet application that can handle big data OS X

I've been working with Excel for quite a while for some statistical analysis that I do regularly. The size of the data that I'm working with has gotten much larger as of late, however. The layout of ...

**1**

vote

**2**answers

32k views

### Pull row of data from one place in spreadsheet to another

I have a list of names and their assorted properties.
I am trying to find a way to quickly move an entire row's worth of data into a different sheet so I can manipulate it.
Currently I can ...

**0**

votes

**1**answer

788 views

### Reference whole sheet in another sheet - reference dynamically NOT copy + paste

I have multiple sheets relating to each room in a house, I also have a master sheet showing the overview of the whole house, what id like to do is pull all the data from the room sheets into the ...

**1**

vote

**0**answers

92 views

### Creating a script to make a CSV dump usable

So, let's say I have a raw CSV dump. And I want to make a spreadsheet/script to take said CSV dump, take a certain username, and the calls they made, and pipe those calls to a mathematical formula, to ...

**3**

votes

**4**answers

26k views

### Too much text for an Excel cell — how to make the cell scrollable

What do I do when I have more text in an Excel cell than I want to show in the allocated space?
Something making it "scrollable" would be preferred.

**2**

votes

**1**answer

3k views

### Retrieve content of different cells based on input

I have a cell X where the user can enter a number. I then need to take that number N and get the value of another cell Y based on that.
The scenario is this:
The user can enter a product number and ...

**2**

votes

**3**answers

10k views

### How to refer to the last cell in a row in Excel?

Which function can I use to get the value of the last not empy cell in a row in Excel 2010?

**1**

vote

**3**answers

994 views

### In MS Excel 2007, replace numeric value with same value plus function

For example, if Column A contains:
123
456
789
I want to replace values with:
=(123/60)
=(456/60)
=(789/60)
I know I could create another column to do something similar, but would rather know ...

**2**

votes

**9**answers

34k views

### Can't insert cells in Excel 2010 - “operation not allowed” error message

I was working on a spreadsheet in Excel 2010, and all of a sudden when I attempted to insert a new row of cells, I saw that the insert and delete options were grayed out.
I attempted to copy a ...