**19**

votes

**5**answers

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

**10**

votes

**2**answers

2k views

### Date based sum in Excel / Google Docs spreadsheets

I have a bunch of rows with a date and a dollar amount (expenses).
I want to produce a list of the days of the month and what the balance of the expenses is. So, for example the 5th entry in the ...

**5**

votes

**5**answers

889 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 ...

**5**

votes

**10**answers

12k views

### Can you convert an address to a zip code in a spreadsheet?

Given a column of street addresses with city and state but no zip in a spreadsheet, I'd like to put a formula in a second column that yields the ZIP code. Do you know a way to do this?
I'm dealing ...

**5**

votes

**2**answers

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

**3**

votes

**6**answers

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

**3**

votes

**3**answers

5k views

### How to freeze more than 5 column in google spreadsheet?

How to freeze more than 5 column in google spreadsheet?

**3**

votes

**4**answers

4k views

### How to open google spreadsheet's extra sheets in new window or tab?

How to open google spreadsheet tab in new window or tab?

**3**

votes

**1**answer

1k views

### Spreadsheet, PHP Script, or Linux program for employee shift scheduling

I have been looking everywhere for a few weeks now but can't seem to find anything so I come here to ask for assistance. Right now I am doing everything via a very simple Google Docs spreadsheet.
I ...

**2**

votes

**5**answers

5k views

### In a spreadsheet (Excel, Google Docs, OpenOffice), how do I transpose data from rows to columns, but only non-null cells?

I work with all three spreadsheet programs listed above, and I believe my question can be answered the same or similarly for all three.
On my sheet, I have a top row full of column headers. The ...

**2**

votes

**1**answer

643 views

### In Google Docs, how to create a form that can store the data entered into it in an excel sheet automatically?

The title pretty much says it all.
I need to create a poll wherein I need to ask the users some questions.
How can I do it using Google Docs? How will I access the data entered after that?

**2**

votes

**1**answer

79 views

### How to look a value and sum it into a certain cell

I have 2 columns, Space & Rows. I want to iterate over the Rows columns and sum only the corresponding value of Space into a certain cell within the same page.
The pseudo code for this would be ...

**2**

votes

**3**answers

2k views

### In a spreadsheet, is it possible to separate rows into groups that will not change order when filtered?

I am trying to make row subgroups in my spreadsheet rows so that I can sort my data but have rows in any particular subgroup stay together. This is probably easiest to show with a picture.
Sorting ...

**2**

votes

**1**answer

119 views

### Major FIREFOX Redirect Error Problem (Error Code 11156)

I would appreciate some help please. When I try to log into some website, im being redirected to the members only page (e.g. dashboard, main account, etc) but instead of successfully landing there as ...

**2**

votes

**0**answers

126 views

### Store Values from a Dynamic Cell Elsewhere in the Spreadsheet - Building a Database with Excel/Google spreadsheets

I have a list of values that updates as new data becomes available on a website using importXML (cells A1-A25). I want to store those values elsewhere in the spreadsheet so that when the values ...

**2**

votes

**0**answers

126 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 ...

**1**

vote

**4**answers

175 views

### Count the number of rows with equal values in two specified columns

I have two columns in MS Excel/LibreOffice Calc/Google Spreadsheets with numbers. I would like to count the number of rows which have identical values in both columns.
In the following example:
...

**1**

vote

**2**answers

2k views

### How do I calculate the sum of 2 columns, using the max from each row?

I have data in two columns. I would like the sum of the two columns, using the maximum value for each row.
For instance
C1 C2
1 2
4 3
11
10
5 7
The result would be ...

**1**

vote

**3**answers

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

**1**

vote

**2**answers

366 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

**1**

vote

**2**answers

3k views

### excel / google spreadsheet: count substring occurrences within a cell

I've been scratching my head trying to figure out a way to count the number of occurrences of a substring within a cell.
For example:
| | A |
| 1 |John,John,Tom,David|
What ...

**1**

vote

**3**answers

113 views

### Generate an alphabetic index from a spreadsheet

I have a text file of lecture topics from my physics class that looks like this:
1/14,Galilean relativity,Einsteinian relativity,Minkowski space,Henri Poincare,,
1/16,Lorentz transformations,Velocity ...

**1**

vote

**2**answers

16k views

### Pull row of data from one place in spreadsheet to another

I have a list of names and their assorted properties.
I am trying to find a way to quickly move an entire row's worth of data into a different sheet so I can manipulate it.
Currently I can ...

**1**

vote

**1**answer

2k views

### Google Spreadsheets: Counting number of matches between two rows

In a spreadsheet in Google Spreadsheets, I have multiple-choice answers I would like to grade.
One row (user 0) contains the answer key, and the other rows contain user answers.
User Q1 Q2 Q3 ...

**1**

vote

**1**answer

22 views

### How do I copy rows based on single cell value in Google Sheets?

I have two sheets in Excel, which is duplicated on Google Sheets. Sheet 1 is a list of names with various bits of information for example
Name Number Outcome
Colin Smith ...

**1**

vote

**2**answers

172 views

### Auto-fill rows from columns in another sheet?

I'm using Google Doc's Spreadsheet at the moment and I'm attempting to create the same view in another sheet.
First Sheet:
(yes I need these merged cells)
Second Sheet:
Unfortunately Google ...

**1**

vote

**1**answer

29 views

### How to xml to xml parse

I am trying to automate this process.
I have link to this xml file. it has many parameters, but I am only interested in 3 of it's parameters. I want to use those 3 parameter and create new xml file ...

**1**

vote

**1**answer

519 views

### “Array result was not expanded because it would overwrite data”

I'm trying to use Google Spreadsheets but I get this error when an IMPORTXML would overwrite some rows of cells that already has something in them:
Array result was not expanded because it would ...

**1**

vote

**2**answers

31 views

### How focus on the document after find?

I'm filling a column after finding a row via a search on the name column. So I do CTRL + F, type the name, and click the the cell with mouse to fill it. Is there a way to do this without using the ...

**1**

vote

**1**answer

85 views

### HTML tags in Google Spreadsheet

I am experiencing issues with Google Spreadsheet.
The thing is that me and my colleagues are using one spreadsheet as a table for translations, there is a column for English and then German and ...

**1**

vote

**1**answer

595 views

### Spreadsheet formula to count consecutive values in a column

I have a column with "Y" and "N" values, and need a formula to count the last consecutive occurrences of "Y". So if I the column looks like this:
Y
Y
Y
N
Y
N
Y
Y
the desired result would be 2
Y
Y
...

**1**

vote

**1**answer

104 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 ...

**1**

vote

**0**answers

37 views

### ArrayFormula + Average giviing me div/o error

I have the formula on B2
if(D2="", "", AVERAGE(D2:AF2))
This gives me the average of all numbers in the row, and takes off the ugly error when there is no numbers. I tried spreading this formula to ...

**1**

vote

**1**answer

40 views

### Convert duration text to duration value

I need help on Google Sheets/Excel.
I extracted sports results data with the IMPORTHTML function of Google Sheets.
All the cells in the Result column contain a duration in the form of "m:ss.00" but ...

**1**

vote

**1**answer

57 views

### Pad numbers to specific length in Google Spreadsheet

I have a column with integer numbers like this: 1, 2, 3, 4, 5 and I want to create another column with these values padded to length of three like this: 001, 002, 003, 004, 005.
Is it possible to pad ...

**1**

vote

**0**answers

41 views

### Equivalent for FILTER() formula in Libre Office

In a Google Spreadsheet, I create a report sheet by entering a formula like this in the top left cell.
=FILTER(Sheet1!A2:E, Sheet1!E2:E > 0)
This gives me all the rows in the Sheet1 from row ...

**1**

vote

**0**answers

117 views

### How to chart time of day in Google Spreadsheet with midnight crossover?

I'm struggling with the best way to chart a series of times in Google Spreadsheets that involves a midnight crossover. The data in question is a graph of a bedtime, when the time to bed is after ...

**1**

vote

**1**answer

319 views

### Can I add rows to a Google Drive spreadhseet from a local text file?

Is there a way to automatically add rows to a Google spreadsheet documents by editing a local text file?
Maybe via DropBox or GoogleCL?

**1**

vote

**1**answer

281 views

### Alternative, more efficient scraping method for a noncoder, than Google doc's importxml and xpath?

I've searched throughout the net for a simple solution, but it seems everyone has their own unique method (coding language) of achieving this. I'm only just beginning to learn Linux, and my coding ...

**1**

vote

**1**answer

149 views

### Google Spreadsheet. Retrieve only one cell from ImportData-result [closed]

I have this function:
=ImportData("http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=7&c=2013&d=0&e=7&f=2013&g=d&ignore=.csv")
...which returns how a given stock was ...

**0**

votes

**3**answers

2k views

### phpmyadmin import database table from google spreadsheet

I'm managing a small website, which allows people to register as members. Previously I used a google form to manage member registration, but now as the number of users becomes quite big I am ...

**0**

votes

**2**answers

42 views

### Excel - What's the best conditional formula when there are lots of conditions?

In Column A on my data set there are group identification numbers. They are integers ranging from 0 to 30.
In Column B is my data.
The groups form 10 cohorts, and the cohorts don't map onto the ...

**0**

votes

**1**answer

277 views

### Insert text to the beginning of every Cell in a Column

I'm trying to find a way to insert text in to every Cell in a specific Column.
e.g. Column C has 50 rows: I need to insert 0049 to the beginning of every Cell.
I didn't had any luck finding/creating ...

**0**

votes

**2**answers

662 views

### Spreadsheets: How do I perform functions on a set/array described by a row of (count, value) pairs?

I have excel 2000, google docs, and the latest open office calc available to me. I'm after a solution in any of those, ideally.
I have a multiset of data described by rows of (count, value) pairs. ...

**0**

votes

**1**answer

426 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 ...

**0**

votes

**2**answers

33 views

### Double column value in a spreadsheet

I need to double the value of a column (ex: C) into another (ex: F) for every row in a spreadsheet.
How can I achieve this?
This is probably simple, but I can't get my head around it.
Thank you.

**0**

votes

**1**answer

43 views

### How I hide the chat window in Google Drive Spreadsheet?

I sometimes accidentally open the chat window when using Google Drive's Spreadsheet app. I couldn't figure out how to close it. Any ideas?

**0**

votes

**2**answers

29 views

### Show image in cell based on cell value

I am creating a weekly income comparison table. I want to compare A5 and A10 cells and display appropriate image in B7 cell. The image will be either UP or DOWN arrow (which reflects increase or ...

**0**

votes

**1**answer

163 views

### How do you do a conditional format for a date range

I have a list in a spreadsheet of upcoming game releases. I'd like to do conditional formatting on the Release Date field so that if the game is being released this month / week, I can have different ...

**0**

votes

**1**answer

65 views

### Formula works in excel and not in Google Spreadsheet

The following formula works in Excel but not in Google Spreadsheet:
=INDEX(Sheet1!B1:P1,MATCH(MAXA(Sheet1!B11:P11), Sheet1!B11:P11,0))
Would there be alternatives?
Thanks,
Bing