Questions tagged [spreadsheet]
Questions about spreadsheet software in general and interoperability between spreadsheet programs.
708
questions
84
votes
3
answers
31k
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 ...
- 4,373
81
votes
9
answers
328k
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 ...
- 27.8k
27
votes
4
answers
42k
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 ...
- 7,358
22
votes
1
answer
68k
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 ...
- 587
21
votes
1
answer
20k
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 =...
- 211
21
votes
5
answers
95k
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 ...
- 313
17
votes
7
answers
179k
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 ...
16
votes
1
answer
29k
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 ...
- 1,922
13
votes
1
answer
15k
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-...
- 589
12
votes
5
answers
72k
views
How to fix Excel and prevent it from converting copy-pasted or imported strings to dates even when cells are formatted as text
Every time I copy-paste some numerical data written as fractions or separated by hyhens into Excel, it always converts numbers that could possibly be dates into dates. There's no way of getting them ...
- 338
11
votes
7
answers
34k
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,431
10
votes
2
answers
26k
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.
...
- 203
10
votes
10
answers
121k
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 ...
- 4,116
10
votes
5
answers
173k
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 ...
- 9,516
9
votes
3
answers
50k
views
Cannot delete created excel table
I have created an excel table. However, I now want to delete or change it because my range changed.
However I can't delete it in the excel Name Manager:
Any suggestion why I cannot delete it?
Would ...
- 217
9
votes
4
answers
150k
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.
- 684
9
votes
2
answers
2k
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 ...
- 9,516
9
votes
1
answer
19k
views
Apple numbers: How can I create a filtered column (B) with only unique values from column (A)?
I'm using Apple Numbers.
I have a table with data like:
A | B
---------+---------
Spain |
U.K |
Spain |
Germany |
U.K |
Italy |
And I would like to set table B to include ...
- 203
8
votes
4
answers
9k
views
Formula to see if a series of numbers is increasing in value?
I have a spread sheet with thousands of sets of numbers. That is too say, there are ten columns and each column has a number - and there are thousands of rows of these numbers.
What I want to do is ...
- 225
8
votes
1
answer
6k
views
How to find cells that reference the current cell in LibreOffice Calc spreadsheet?
I am trying to understand a spreadsheet created by someone else. It has many complex formulas.
When I select a cell, is there a way to highlight other cells that have a formula that references the ...
- 3,060
8
votes
3
answers
12k
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 ...
- 8,439
8
votes
2
answers
52k
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), ...
- 93
8
votes
3
answers
9k
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 ...
- 83
8
votes
2
answers
8k
views
Handle pasting of decimal with dot (1.7) when locale is expecting comma (1,7)
I've a datasource where the dot is used as decimal separator, i.e. : 1.49 means "one and almost-half". Cannot change it.
This number must be copy-pasted to a numeric cell in Excel or Calc. The file ...
7
votes
3
answers
2k
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 =...
- 237
7
votes
4
answers
13k
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 ...
- 1,159
7
votes
2
answers
13k
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?
- 1,064
7
votes
5
answers
3k
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 ...
- 597
7
votes
1
answer
1k
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...
- 904
7
votes
1
answer
11k
views
7
votes
5
answers
84k
views
Inserting specific number of rows in Excel
I, for the life of me, can not figure out why M$ has not provided this feature. If I want to insert 597, for the sake of argument lets say blank, new rows, all available information tells me to either
...
- 717
6
votes
6
answers
2k
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?...
- 3,340
6
votes
4
answers
40k
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 ...
- 61
6
votes
1
answer
32k
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 ...
- 1,202
6
votes
2
answers
25k
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 ...
- 366
6
votes
2
answers
13k
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.
- 937
5
votes
8
answers
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 ...
- 214
5
votes
4
answers
56k
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 ...
- 2,640
5
votes
3
answers
99k
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. ...
- 163
5
votes
6
answers
30k
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?
- 1,259
5
votes
1
answer
18k
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(...
- 113
5
votes
2
answers
9k
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 | ...
- 310
5
votes
2
answers
63k
views
Highlight the cell if greater than value in the next column
So I tried experimenting with conditional formatting, but unable to find a way to highlight the cells in the column if their value is greater than the one in the next column under the same row.
For ...
- 325
5
votes
1
answer
45k
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 ...
- 51
5
votes
2
answers
5k
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 ...
- 2,488
5
votes
3
answers
18k
views
Decimating rows in spreadsheet
Is there such an option to preserve only every 10th row in my spreadsheet? It can be Excel or Calc. Quality standards want some noise to be added.
- 6,215
5
votes
1
answer
35k
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 ...
- 209
5
votes
2
answers
29k
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 ...
- 51
5
votes
4
answers
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 ...
- 615
5
votes
3
answers
92k
views
Put a checkbox in a cell in Excel v15 for Mac
I am trying to make a checklist in Excel.
Basically I am trying to make a cell:
Into a checkbox that can be checked or unchecked, and saved with the spreadsheet:
It's super-duper easy to do it in ...
- 222