Google Spreadsheets is a part of the Google Docs web app suite. Questions about Google Spreadsheets are off topic and should be asked on webapps.stackexchange.com, unless they specifically involve your computer or browser.

learn more… | top users | synonyms

19
votes
5answers
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
2answers
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
5answers
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
10answers
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
2answers
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
6answers
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
3answers
5k views

How to freeze more than 5 column in google spreadsheet?

How to freeze more than 5 column in google spreadsheet?
3
votes
4answers
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
1answer
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
5answers
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
1answer
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
1answer
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
3answers
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
1answer
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
0answers
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
0answers
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
4answers
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
2answers
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
3answers
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
2answers
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
2answers
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
3answers
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
2answers
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
0answers
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
1answer
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
1answer
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
0answers
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
0answers
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
1answer
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
1answer
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
1answer
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
3answers
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
2answers
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
1answer
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
2answers
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
1answer
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
2answers
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
1answer
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
2answers
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
1answer
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
1answer
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