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

**1**

vote

**1**answer

4k views

### How do I count the times each number appears in columns of numbers?

I am sure this must be easy, but I am inexperienced. About the best way to think of my problem is to think of it as trying to sort and then count lottery numbers. To stay simple, let's do a Pick 3 ...

**2**

votes

**1**answer

1k views

### Quick/Small Spreadsheet Software

There is some minimalist spreadsheet (excel-like) software? I imagine something like NotePad (small and quick) just to write some tabular data quickly. No toolbars, no font formatting, not "a lot" of ...

**1**

vote

**2**answers

6k views

### Big Excel File Freezing/Running Slowly

My co-worker has a very large Excel file (over 7 MB) that suffers from the problems of (A) running slowly (B) taking forever to open/save/close and (C) freezing the computer, requiring a restart.
I ...

**1**

vote

**3**answers

3k views

### Combine 3 columns into one in Excel 2011 for Mac

I am trying to manipulate a bunch of string elements I have put in columns in Excel. I then filled the preceding column with data, and then the following column as well. Now I want to delete the ...

**3**

votes

**1**answer

987 views

### Open Source Spreadsheet with Sensitivity Analysis

Is there any good Open Source spreadsheet program that has a solver for linear programs (either included or via an extension) that does a proper sensitivity analysis (shadow prices etc)? It seems like ...

**4**

votes

**4**answers

790 views

### Spreadsheet forumula questions to pick a random DVD

I have a list of my (279) DVD’s in an OpenOffice Spreadsheet (version 3.3.0) on my Windows 7 laptop and I want to create a formula to randomly pick a film to watch (for occasions when I can’t make up ...

**4**

votes

**1**answer

774 views

### Custom CSV (.csv) filter for OpenOffice.org or LibreOffice?

Is it possible to create a some kind of 'custom CSV filter' for OpenOffice.org or LibreOffice spreadsheet program. What I need is to have the program to use predefined CSV settings for loading and ...

**1**

vote

**6**answers

16k views

### In Excel, copying cell *values* (not formulas)… yet keeping the cell formats

I have an Excel spreadsheet with multiple worksheet tabs for collecting data... and a final tab with a lot of formulas, to summarize the other tabs for a given date range.
The goal is for the ...

**2**

votes

**1**answer

2k views

### In Excel, doing a “Freeze Panes” on the right-most column instead of the left-most?

My understanding of Excel's "Freeze Panes" feature is that you can only freeze the left-most column(s), or top-most row(s).
However, I have a table where a very important column needs to be located ...

**0**

votes

**3**answers

3k views

### How do I add content to Excel cells?

How can I add a T to the content of each cell in a certain column (that contains 10000 cells) without typing it manually?

**1**

vote

**3**answers

3k views

### How can I compare columns in Excel?

If I have two columns of data with 10000 cells in each, and only three of the rows are not identical in both columns, how can I locate the different ones (without comparing each pair of cells)?

**2**

votes

**2**answers

5k views

### Excel - Dynamic row reference based on the row I paste a formula into?

I have a simple, oft-used formula that I paste as plain text into spreadsheets I receive. It looks something like this:
=IF(D8="FOO", "BAR", "BAZ")
It looks in D8 for the word "FOO". If it finds it ...

**2**

votes

**1**answer

710 views

### Two Spreadsheets, Merge Data?

I was wondering what is the best way to merge two spreadsheets which have the name id's e.g:
One sheet has:
apple 5
dog 3
cat 2
Second sheet:
dog 4
apple 7
cat 3
I would ...

**1**

vote

**2**answers

58 views

### Spreadsheet like software, where I can add files/documents to Cells

Does anyone know of a spreadsheet type software like excel for instance where I can add files/documents to cells. The reason being is I would like to add information in the adjacent columns regarding ...

**1**

vote

**2**answers

135 views

### Versionable text spreadsheet format, ideally with GUI editor

I'm looking for a spreadsheet format that whose diffs won't look insane if I version it.
Ideally this would have a decent editor to go with it (GUI preferably), and should be available ...

**1**

vote

**1**answer

103 views

### How to visualize historical data

I have inherited a horrible spreadsheet with 4 years of data about when people participated in a given activity, by month. Each person would likely participate once every 18 months or so, but ...

**2**

votes

**1**answer

41 views

### Rearrange cells in a column to a different column with same heading (Excel 2002)

I need the values in one column to be rearranged for each set into another column.
This picture will help explain:
Follow the red lines, that is where I want my data to go.
This shouldn't be too ...

**0**

votes

**1**answer

75 views

### How do I select a set of specific cells and then copy their entire row?

Okay this is hard to explain.
I have a massive spreadsheet with sets of data in it. Each set has 'type' column (eg: system, memory, video card, etc). I need to be able to search within this column ...

**0**

votes

**1**answer

2k views

### Microsoft Excel Exercises for learning purposes

I'm not sure if it belongs this here, but if you feel it doesn't just please remove it. I'm looking for a good source of exercises in excel that is good in teaching a friend of mine that wants to ...

**0**

votes

**1**answer

1k views

### Autosort microsoft excel worksheet

Can I configure (possibly with scripts) Excel to automatically resort a table every time an entry is updated?

**0**

votes

**1**answer

372 views

### How do I simplify this if statement to just add 1 every time (and possibly go with no limit)?

=if(C24 ='Ability Base'!$A$2;sum('Ability Base'!$B$2);
if(C24 ='Ability Base'!$A$3;sum('Ability Base'!$B$3);
if(C24 ='Ability Base'!$A$4;sum('Ability Base'!$B$4);
if(C24 ='Ability ...

**3**

votes

**1**answer

5k views

### Excel: #NAME? appearing when linking/referencing other spreadsheets/workbook cells

I'm updating a spreadsheet that refers to data in another worksheet =NameofWorksheet for some reason I get #NAME? in some of the cells, even when the actual path is correct.
The only way to overcome ...

**2**

votes

**2**answers

3k views

### how to find lowest 2 or more values

I have rows of data and would like to sum all but the lowest 2 or 3 or 4 entries.
What is the easiest way to do this? Does it involve a macro.
I prefer open office spreadsheet, but could also use ...

**2**

votes

**1**answer

165 views

### Conditional Formatting in Excel

I have a worksheet with a series of rows A1 - H1 etc and a drop down list which lives in H1, I would really like to change the cell styles of all the cells between A1 - H1 based on the value in H1.
...

**1**

vote

**2**answers

435 views

### Not counting values if strikethrough in iWork- Numbers

I'm building a holiday expense planner and I'm trying to get the sum of a column in a table, however as I pay things off I want to be able to strikethrough the amount and then it minus it from the ...

**2**

votes

**3**answers

631 views

### Excel data representation: show me all people who did not pass the exam

Background
I have an excel spreadsheet with the results of a pass/no-pass exam. Students are allowed to take the exam as often as they want until they either pass, or give up trying.
student ...

**1**

vote

**1**answer

139 views

### Inserting scientific data into open office Calc

i have some scientific data that i want to insert into oo.org calc. when i do this, instead of displaying 0.35678 in a cell it displays 0.35 (only the first 2 digits after the decimal). but on ...

**2**

votes

**3**answers

222 views

### Spreadsheet RDBMS

I'm looking for a software (or set of software) that will let me combine spreadsheet and database workflows. Data entry in spreadsheet to enable simple entry from clipboard, analysis based on joins, ...

**13**

votes

**2**answers

4k views

### Excel Conditional Formatting Escaping a Question Mark

I would like to use a conditional formatting rule in an excel file that would color any box with a question mark in it red. It seems that Excel is using a question mark as a wild card and will turn ...

**0**

votes

**1**answer

1k views

### Make cells automatically shrink to fit text in OpenOffice spreadsheet

Is there a way to get the widths of cells to automatically shrink themselves to fit the longest line in a column?

**4**

votes

**5**answers

14k views

### Using excel, how can I count the number of cells in a column containing the text “true” or “false”?

I have a spreadsheet that has a column of cells where each cell contains a single word. I would like to count the occurrences of some words. I can use the COUNTIF function for most words, but if the ...

**1**

vote

**2**answers

88 views

### I hyperlinked a cell in excel 2003, formula issues?

I have a budget spreadsheet using excel 2003. I have My deposit, then all of my bills, the total, then a cell that has the difference(between the amount of deposit and the total of the bills). The ...

**0**

votes

**1**answer

292 views

### MS Excel convert cell matrix (1x15 -> 3x5)

What is the formula for displaying cells in a 3 cell increment at a time, like A1, A4, A7, A10?
Moreover, would it be possible to transform below a list (1x15) of data into a table(3x5). Thanks.
...

**0**

votes

**3**answers

922 views

### Any examples of a complex, changeable system modelled with a spreadsheet?

When I asked this question using games as the example (hoping it would be more likely to have been done), it was closed as being off topic so let me ask it this way...
Has anyone used a spreadsheet ...

**1**

vote

**1**answer

4k views

### Excel cell references not updating when referenced cells are sorted

There are two tables, each with 75 entries. Each entry in the 2nd table calls an entry in the first table a parent. One of my 2nd table columns contains the "Parent Price", referencing the Price ...

**0**

votes

**2**answers

646 views

### Spreadsheet formula: lowest 100 values in a range

Is there any way I could sum up the lowest 100 values within a range?
I know that min() would give you the lowest value but i need something to return the 100 lowest values.
I just used 100 ...

**1**

vote

**3**answers

755 views

### How can I detect if a range of cells contains only identical values or not in Excel?

I have a range of cells and I want a formula that says if all these contains the same values or not.
Usually for two cells I would use if but I want a solution that would work for ranges. I don't ...

**1**

vote

**1**answer

304 views

### Store Solver's metadata in OpenOffice's SpreadSheet

How can you save the metadata of OpenOffice 3.0's Solver?
I do not want to add the conditions each time I open a file in OO's SpreadSheet.
Excel by contrast saves the Solver's Limiting conditions.

**3**

votes

**2**answers

12k views

### How do I test if a cell in OpenOffice Calc contains #N/A?

I'm using the VLOOKUP-function to lookup values in a column based upon a search query. But since it's not a given that a value always exists for my search parameter, sometimes VLOOKUP returns #N/A
...

**0**

votes

**3**answers

263 views

### Apply a formula to particular cells in Microsoft Excel

Say I have the following sheet:
| A | B | C |
1 | 5 | 1 | 5 |
2 | 8 | 8 | 99 |
3 | 77 | 3 | 3 |
4 | 2 | 7 | 4 |
5 | | | |
6 | Base:| 2 | |
...

**3**

votes

**1**answer

783 views

### Read data from separate CSV file in OpenOffice Calc

I have a very large CSV file (many thousands of rows) that I want to work with. Due to the size of the file, I don't want to import it into openoffice. Rather, I want to create a spreadsheet that ...

**1**

vote

**4**answers

972 views

### How to prevent truncation of 0 in Excel sheet?

I am copying tabular data from a website: [http://www.infodriveindia.com/TradeResources/STDCode.aspx?STDCode=&City=&State=andaman][1]
But it pastes the numeric value by truncating the ...

**2**

votes

**1**answer

1k views

### Looking for Open Source, locally-hosted, web-based personal finance or spreadsheet software [closed]

I'm looking for a web-based personal finance management solution, but one that I can host locally, so not something like Mint.com or the like. I'd even be willing to look at a web-hosted spreadsheet ...

**2**

votes

**3**answers

23k views

### How do I open or convert a Microsoft Works .XLR spreadsheet to an Excel compatible format?

I have been sent a Microsoft Works XLR spreadsheet and Excel will not open it.
Is there any way of opening an XLR file directly in Excel?
Can anyone recommend a (free) tool for converting to an ...

**3**

votes

**2**answers

2k views

### Count cells based on text content (Gnumeric)

I have a spreadsheet with columns that are basically booleans, they contain either "Yes" or "No", I want a total count of all the cells in a range that are "Yes".
I imagine this would be done ...

**0**

votes

**1**answer

244 views

### Is there a freely available source for VisiCalc documentation?

I am looking for a freely available online document with instructions on how to use VisiCalc

**1**

vote

**3**answers

766 views

### How do I read old star office 5.x spreadcheet documents today? (*.sdc)

I found a couple of old spreadsheet documents that I created a couple of years ago that I would like to read again.
I think I used the old Star Office 5.2 to create those Spreadsheets.
They all ...

**3**

votes

**3**answers

4k views

### Excel annoyance: How to maintain formulas in a ledger?

A common use of Excel is to create a spreadsheet which functions as a transaction ledger of some kind, where one or more fields in each row is calculated by adding a value or values from the current ...

**5**

votes

**2**answers

6k views

### In Open Office, make 1st row of data “scroll lock” so it is always visible as you scroll down?

In Open Office, how do you make the first row of data "scroll lock" so it is always visible as you scroll down?

**6**

votes

**4**answers

20k views

### Select each nth row in Excel

I have an Excel spreadsheet with thousands of rows. I want to select every 7th row from that spreadsheet. (By "select," I mean delete all of the other rows, or copy the selected rows into a new ...