Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [spreadsheet]

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

73
votes
9answers
259k 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 ...
48
votes
3answers
17k 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 ...
21
votes
1answer
19k views

What is in LibreOffice Calc the equivalent to Excel's “Paste Special > Value”?

In my professional life, I'm limited to use Microsoft Office. In Excel there is an option ows to render the result of a formula to be pasted as a "fixed value". For instance A1 = "I",B1 = "ride", C1 =...
20
votes
1answer
59k 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 ...
16
votes
3answers
26k views

OpenOffice: Delete rows based on cell value

I've got a rather large spreadsheet that I need to filter some rows out of. In the spreadsheet, all rows whose M-Column doesn't equal "on" must be removed. I applied a filter, and re-saved the ...
13
votes
6answers
116k 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 ...
13
votes
5answers
47k views

How to Label / Tag Items in a Spreadsheet

I'm a novice at spreadsheets, so I hope you'll forgive me if I'm asking a super obvious question. Is it possible to use tags in Excel/Google Spreadsheet? I'm creating a spreadsheet to log all the ...
9
votes
10answers
84k 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 ...
9
votes
1answer
18k views

How to scale a long LibreOffice Calc sheet to fit on two pages when exporting to PDF?

I have Libreoffice and I want to export a long calc sheet to a pdf document with the fit-to-page function from the page preview in the file menu but it doesn't seems to work. I want to fit my sheet ...
9
votes
6answers
25k 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 ...
9
votes
1answer
10k views

Disable prompts in Microsoft Excel when saving a CSV file [duplicate]

I have to download multiple csv reports from the Internet throughout the day, make a tiny change to them, and save the change. Excel adds 3 completely unnecessary dialogs to what should really be a 5-...
8
votes
3answers
3k views

How to compare two Excel spreadsheets?

I would like to compare two Excel spreadsheets Both have the same columns, in the same order, but not the same rows. I have a spreadsheet with 1,000 rows and 50 columns including a unique ID for ...
7
votes
3answers
1k views

Spreadsheet (Excel, Google Docs etc), extracting value separated by comma

I have a value in one cell that looks like this 4,8,2 Lets say it's in the A1 cell how I can get the inner 8 (the second comma separated value) from this cell into the cell B1 ? =? Tried with =...
7
votes
2answers
12k 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?
7
votes
2answers
42k 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), ...
6
votes
6answers
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 ability?...
6
votes
2answers
20k 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. ...
6
votes
2answers
7k views

How to sum cells depending on the content of a neighbor cell

I have an Excel document with the following columns; Date | Reference | Amount 23/01/11 | 111111111 | £20.00 25/09/11 | 222222222 | £30.00 11/11/11 | 111111111 | ...
6
votes
5answers
2k 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 ...
6
votes
4answers
76k 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.
6
votes
1answer
747 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...
6
votes
2answers
11k 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.
6
votes
2answers
1k views

Is it possible to access and edit Google documents directly from Libreoffice?

I'd like to work with a Google spreadsheet directly inside Libreoffice, without having to export and import it, in order to automatically reflect the changes on the online file. Basically, I'm looking ...
6
votes
1answer
7k views

Formula to add X months to a date?

How can I add X months to an existing date in iWork Numbers?
5
votes
8answers
7k views

Excel spreadsheet bogged down, Is there an alternative software? [closed]

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 ...
5
votes
4answers
12k views

Make Excel 2003 Open Spreadsheets in new instances

In Excel 2003, It seems to be putting each spreadsheet I open into the existing Excel application as a new window. this makes it very difficult in a dual monitor set up to put one spreadsheet on each ...
5
votes
6answers
28k views

Where can I find some dummy data to teach my friend how to use a spreadsheet? [closed]

In particular, tabular data that can be graphed. What's your favorite source of interesting data?
5
votes
1answer
22k views

how to highlight the cell with the max value

given a range of non adjacent columns (not sure if it matters if they're adjacent) i.e F1:F100,J1:J100,N1:N100 for each row i'd like to highlight the cell with the maximum value. i tried ...
5
votes
5answers
90k 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 ...
5
votes
2answers
19k 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 ...
5
votes
1answer
27k 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 ...
5
votes
4answers
6k 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 ...
4
votes
4answers
48k views

How can I enable the creation and editing of hyperlinks in shared Excel worksheets?

I have hyperlinks in some Excel 2010 (both .xlsx and .xls formats) worksheets. If I enable sharing of the workbook I can no longer edit or create hyperlinks - the options no longer exist on the menu ...
4
votes
2answers
3k 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 ...
4
votes
3answers
14k 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
1answer
9k 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 =SUM(ADDRESS(...
4
votes
2answers
29k views

What's the difference between “vlookup” and “lookup” in Excel?

What is the main basic difference between VLOOKUP and LOOKUP in Excel? I read the main point is that you can specify a range of columns in VLOOKUP and then pick one to return the value. However, it ...
4
votes
3answers
16k 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 ...
4
votes
4answers
1k 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
4answers
56k 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 ...
4
votes
2answers
4k 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 ...
4
votes
1answer
285 views

Is it Possible to not copy all 65,537 cells when copying a column in Open Office

I'm using Open Office 3.01 on OS X. When I select an entire column of data by clicking its header, and the copy that to the clipboard, Open Office appears to copy all 65,537 cells to the clipboard. ...
4
votes
3answers
4k views

Highlighting every other row in LibreOffice (or OpenOffice) without conditional formatting?

Besides using conditional formatting, is there any way in LibreOffice Calc (or OpenOffice Calc) to highlight every other row in order to make the spreadsheet easier to read? LibreOffice has a serious ...
4
votes
2answers
18k 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 ...
4
votes
2answers
2k views

WPS Office CSV Delimiter - Linux

I have been using LibreOffice for all my Spreadsheet and Wordprocessing requirements lately but am getting fed up with the performance issues, it has a massive lag and also constantly freezes. I have ...
4
votes
1answer
19k views

Can you use infinity in a cell reference in LibreOffice (or any other spreadsheat)? [duplicate]

Possible Duplicate: Can you reference an entire column in OpenOffice Calc (like A:A in Excel)? I'm using a Sum of a column, and it keeps growing, so I have to update the reference to which cells ...
4
votes
1answer
6k views

OpenOffice Calc - how to insert BLANK in a formula?

In Calc, as in most spreadsheet applications, an IF() function exists that can be used to control behaviour, for example inserting this formula in cell C1. =IF(A1>B1;"A";"B") will display A in C1 ...
4
votes
1answer
1k 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 ...
4
votes
1answer
58 views

libreoffice /open office calc - visualise formula

Let's say I have two alternatives in columns C,D like so: row 6: sample size row 7: sample mean row 8: sample standard deviation and then want to do a t-Test, which requires (amongst other things)...
4
votes
2answers
2k 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 ...