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

learn more… | top users | synonyms

1
vote
2answers
1k views

Multicolumn spreadsheet: extract unique values in Column 1, given same values in Columns 3 and 4?

Here's a problem: given a (large) spreadsheet with userIDs in Column 1, a time/date pair in Column 2, a course code in Column 3, and a course title in Column 4: I want to produce a report showing how ...
2
votes
2answers
6k views

How can I print Excel spreadsheet files automatically?

My company has computer-generated reports that come out as Excel spreadsheets which are emailed to the Billing Department. They would like these reports to be automatically printed as they are ...
0
votes
1answer
272 views

Creating a chart with two date columns

I have some data from Google Analytics relating to traffic to one of my websites. I would like to compare the traffic from now to the same time one year ago. However, to be difficult I do not simply ...
4
votes
3answers
9k views

How do I delete excess cells in Excel 2007?

I am not the original creator of the spreadsheet so I am not sure how the person caused this. The issue is basically the excel spreadsheet has an excess amount of empty cells that won't delete and ...
0
votes
2answers
661 views

Software for quickly tagging photo catalogue and export to spreadsheet?

We have 3000 photos that we want to quickly flip through and tag into different categories, and export that information into a spreadsheet. The software can be very simple (I am not doing any editing, ...
1
vote
2answers
308 views

Advanced spreadsheet auto-formatting

Is it possible to autoformat spreadsheet like this IF A1 = C1 then A1:E1 cells background becomes light gray I use OO Calc, but it's ok if you know how to do it in MS Excel or Google spreadsheet
3
votes
1answer
1k views

How do I make a random selection in a spreadsheet using more than one criteria

I have a spreadsheet created in OpenOffice (V3.3.0 in Windows 7 32 bit) that I use as a database of my DVD collection. After asking a question in SuperUser, I have it so it can pop up a random title ...
2
votes
1answer
2k views

How do I use conditional formatting in a spreadsheet based on dates?

I have a spreadsheet in OpenOffice V3.3.0 (used in Windows 7 32 bit). One column contains dates (in the format: 14 August 2011 ) and I want to use conditional formatting on those cells depending on ...
2
votes
5answers
649 views

How do I transpose and combine cells in Excel?

Do you know how to generate the following? Excel table: +-----------------------+ | A | 1 | 2 | 3 | 4 | 5 | +-----------------------+ | B | 1 | 2 | 3 | 4 | 5 | +-----------------------+ I want to ...
6
votes
5answers
951 views

Excel Formula to Calculate Duration of Time Based on a String of Text Such As 9:00am — 12:30pm

I'm trying to develop an Excel formula that would calculate the actual time duration for a string of a time range of the following format: 1:00am – 3:00am According to this article it seems it should ...
1
vote
2answers
5k views

Is importing one Excel spreadsheet into another Excel spreadsheet possible?

Can I import an Excel spreadsheet into another spreadsheet (two separate files) by linking in certain cells from one to show in other cells in a different file? I'm not sure if I can do this or where ...
10
votes
1answer
12k views

How to sort an Excel spreadsheet columns by row contents?

How do I sort the columns of an Excel spreadsheet? The Sort command from the Data menu only allows me to sort rows by the value of a column. I want to sort the columns alphabetically by the contents ...
0
votes
1answer
154 views

Make a differentiated barchart using numbers

I have a question about Numbers. I want to make a bar chart that shows the membership of my organisation. I want to divide between unitary organisations and membership organisations, so i want ...
1
vote
1answer
213 views

How to sum the multiplications of rows, being the multiplier variable in a spreadsheet

I have this spreadsheet: I want to calculate the total income, but since the different rows are in different currencies the conversion must be done first. The currency values are mapped to codes in ...
0
votes
0answers
35 views

Chart from groupings

Given such a spreadsheet: col1 | col2 | col3 -----+------+---- alfa | foo | 10 beta | foo | 20 gamma| bar | 12 delta| bar | 42 I'd like to generate a chart grouping values from col2 and ...
4
votes
0answers
150 views

Swap rows in gnumeric

Is there any convenient way to swap two rows in gnumeric? Currently I'm doing it the hard way: insert an empty row, move rows, and remove empty row. I hope that there is a better way...
0
votes
1answer
80 views

Add another worksheet in an Excel worksheet

Is it possible to include an additional set of worksheets (i.e. "tabs") into an already existing worksheet in Excel?
3
votes
3answers
2k views

Adding a constant in LibreOffice Calc

How do you add a constant to a group of cells in LibreOffice Calc? For example, here are four cells: 47 28 35 62 Now add 3 to each cell: 50 31 38 65
1
vote
1answer
123 views

Not showing negative results in Numbers.app

How can I make iWork Numbers not show me negative results? For example: The thing is that in A1 I have number 101 and I am doing -A1 from B1. B1 is empty (because I am waiting for the result) so in ...
0
votes
5answers
1k views

Good tools to visualize spreadsheets?

I have a few CSV and XLS files that I would like to analyse or "visualize" online (like in charts etc). I tried Google Fusion Tables, but I want to know if there are any alternatives. Also suggest ...
0
votes
2answers
375 views

Excel Pivot Table or VLOOKUP

Students have an ID, Name, and English Level in one spreadsheet. They have an ID and achievement on a standardized test on another spreadsheet. I need to match the ID's and put the English level ...
1
vote
2answers
410 views

Image file to spreadsheet

I want to take an image file (eg tiff) and convert it to a spreadsheet (csv, or whatever) such that each pixel becomes a cell with a numeric value for that pixel. I have googled it, and everyone is ...
2
votes
1answer
524 views

Copy only cell content in OOCalc

In OOCalc, when I copy a cell with ctrl-c ctrl-v, it copies everything, also the style, from source to destination. However, I want to copy only the content, leaving the formatting of the destination ...
1
vote
1answer
4k views

How to conditionally highlight cells in OpenOffice Calc?

I have a big chunk of data in an OpenOffice spreadsheet. To make real information standout more, I would like cells with an absolute value over a certain threshold to be highlighted in red. Is there ...
0
votes
1answer
171 views

Software to calculate average payments of the house

I want to calculate average payment of the house for every month, the average expense of each month. However, most of the fee is across months, they charge every 3 or 2 months. These are normal bills, ...
5
votes
8answers
2k views

Excel spreadsheet bogged down, Is there an alternative software?

I work for an organization with a yearly budget that is an Excel spreadsheet consisting of about 25 large sheets that are linked to one another. There is a big summary sheet that draws it's numbers ...
0
votes
1answer
705 views

How to add a line between every two rows in a spread sheet?

I was wondering in Openoffice Calc spreadsheet, how to add a line between every two rows? I want to print a spreadsheet, with a line separating every two rows. Thanks and regards!
0
votes
1answer
4k views

Excel formula to find frequency of occurrence of unique words in a column

I have a spreadsheet with a column with repeating number of different kinds of words like A1 A2 A3 A1 AA A2 A7 A8 A1 Now I want to plot a graph showing the frequency of occurrences of a word. The ...
0
votes
3answers
121 views

Re-ordering data in a cell in spreadsheet

I have an Excel spreadsheet where data is time stamped, but the time stamp is as follows (in one cell): Fri Mar 11 If it was 11 Mar Fri, the data would be easier to sort. How do I change the ...
1
vote
3answers
184 views

Excel plot chart from data as given in the example

I have a huge spreadsheet with simple data as follows 13:04:28 A 13:04:37 A 13:06:56 B 13:07:03 B 13:07:10 C 13:07:18 C 13:07:23 C 13:07:28 C 13:07:33 C Basically ...
3
votes
2answers
296 views

How do I access the search functionality in Open Office?

I am very new to oo and plodding slowly; never used a spreadsheet, etc.etc. I downloaded a document of botanical names for flowers/herbs, etc. The page was set in 3 columns and was 138 pages long (!) ...
4
votes
1answer
534 views

Edit spreadsheet object in Word document with protection

I am trying to create a template in Word to be used on a SharePoint site. Using Word 2003, is there a way to enable protection on a document that has an Excel Spreadsheet (97-2003) object on it but ...
0
votes
1answer
401 views

Excel filter by range

I am setting up a workout schedule. I have multiple rows with workout times, who is instructing the class, and who the class is suitable for. The suitable is a range, based on the following (anyone ...
7
votes
1answer
1k views

OpenOffice.org calc: how to dynamically update a data range for a graph?

I have an OpenOffice.org spreadsheet that contains a number of graphs. As I append more data to this spreadsheet, I want to update all the data ranges in those graphs, basically changing A1:A100 to ...
0
votes
3answers
767 views

Do multiple e-mail addresses need to be separated by a comma?

In CSV format, in an Excel spreadsheet or in a Word document, both having multiple email addresses with each address being in its own row and in a single column, is a comma REQUIRED after each ...
12
votes
5answers
36k views

How to create a formula for every row in a column in Google SpreadSheet?

I use three columns. A, B and C. In column C I have a formula every row =A1*(1.6*B1) and then for the next row I have =A2*(1.6*B2) in C2. How can I do so I don't have to type in the new formula in ...
1
vote
2answers
940 views

Data validation - populating a drop down menu using a defined name

I am using data validation to create a drop down list on a worksheet. What I'd like to be able to do is set up a list of values as a defined name (Insert > Name > Define...) and then enter that ...
1
vote
1answer
3k 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
1answer
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
2answers
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
3answers
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
1answer
938 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
4answers
730 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
1answer
762 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
6answers
14k 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
1answer
1k 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
3answers
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
3answers
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
2answers
4k 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
1answer
670 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 ...