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

**3**

votes

**3**answers

27k views

### Is it possible to copy & paste a pivot table in a spreadsheet?

I have a spreadsheet where I use some pivot tables to summarize and highlight some relevant data.
Since I have to use multiple pivot tables, all with nearly the same data input but with slight ...

**0**

votes

**2**answers

62 views

### Using conditions to write to new row on new sheet to return value

I would like to be able to use a data-entry page or form to keep track of monthly fulfillment of orders. We have some number of SKUs, lets say 10, and various number of orders against each SKU. ...

**3**

votes

**2**answers

20k views

### Dynamically filtering data before importing from SQL Server in Excel

I'm trying to figure out whether there is a way to filter the data from an SQL Server connection before it is imported into Excel. The initial import doesn't matter (when the connection is set up), ...

**0**

votes

**1**answer

45 views

### Excel/Calc: Pivot table with (almost) identical row: how to identify them?

I need to examine via pivot a datasource from a sheet. This datasource sheet is something like this:
| Field 1 | Field 1 | Field 3 |
| AAAA | BBBB | CCCC |
| DDDD | EEEE | FFFF |
| GGGG ...

**1**

vote

**1**answer

4k views

### How do I add values in column B if column A contains the same employee surname more than once?

State of Data: I have column A which has names of employes and column B has number of hours. How do I add the hours if column A contains same employee surname more than once?
I have been using what I ...

**2**

votes

**0**answers

139 views

### Unable to enter data into cells in sc

I'm trying to use sc, the classic Unix spreadsheet calculator, mainly because I want to be able to look at some csv files nicely formatted in my terminal. I installed sc 7.16 but am seeing some ...

**0**

votes

**1**answer

279 views

### Retaining cell formatting in a formula

I'm looking for a way to pass along text formatting when combining cells in a formula. The image attached shows a similar instance of the result I would like.
If I combine A1 and A2 and A3 with the ...

**0**

votes

**1**answer

218 views

### Vlookup not working as expected for soccer data

i have a vlookup function that is supposed to give me some data from another worksheet. the worksheet contains soccer data, specifically about the european cups of the past 3 years (9-10, 10-11,11-12, ...

**2**

votes

**2**answers

116 views

### Why is this simple Excel calculation not netting Zero?

It's supposed to be zero but for some reason the result produces a peculiar series of digits at the end.
Also, here's the .xlsx of it: http://83.212.111.36/Book1.xlsx

**0**

votes

**2**answers

545 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

16k 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

283 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

361 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

14k 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

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

559 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

5k 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

309 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

2k views

**2**

votes

**0**answers

175 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

962 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

114 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

923 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

168 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

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

248 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

6k 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

487 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

35 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

77 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

856 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

4k 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

955 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

1k 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

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