**46**

votes

**7**answers

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

**9**

votes

**11**answers

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

**9**

votes

**4**answers

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

**6**

votes

**2**answers

4k views

### Extracting email from text string in excel/google spreadsheet

I'm looking to extract email addresses from text strings in Google Docs/Excel and email them automatically, where the content of the cells are currently unknown (until updated by user).
For example, ...

**5**

votes

**5**answers

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

**5**

votes

**3**answers

2k views

### Pad numbers to specific length in Google Spreadsheet [closed]

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

**4**

votes

**6**answers

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

**4**

votes

**2**answers

2k views

### “Trying to connect” error in Google Docs on Safari

Beginning a few months ago, I started receiving a significant number of "Trying to connect" errors when using Google Docs in Safari.
I can load the page fine, and I can even start using it, but ...

**4**

votes

**0**answers

529 views

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

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

**3**

votes

**3**answers

10k 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

5k 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

**2**answers

67 views

### COUNT all “P” from a row

I have a presence row in a sheet (Google or MS Excel, does not really matter), having values "A" or "P" for each name.
I need to count how many "P" I have, to make the cell RED if there is any "P" in ...

**3**

votes

**3**answers

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

**3**

votes

**1**answer

2k 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

**4**answers

4k 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:
...

**2**

votes

**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 2+4+11+10+7....

**2**

votes

**1**answer

193 views

### Select all cells in a row

How can I select all cells in a row in Excel / Google Spreadsheet?
I know I can select all cells in a column using
A:A
But how can I select all cells in a row?
This doesn't work:
1:1 // select ...

**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

**2**answers

329 views

### How to create a data series for exponential decrease diagram

I want to create a pricing model this way:
If you need this product for 50 people, you should pay 2500 (units) per person.
.
.
.
.
If you need this product for 10,000 people, you should pay 300 (...

**2**

votes

**1**answer

852 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

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

**2**

votes

**1**answer

41 views

### How to “Round half to Even”?

I need to "Round half to Even" (Bankers' rounding) a number in Excel. So:
4961,25 => 4961
9738,75 => 9739
4934,5 => 4934
4935,5 => 4936
How can I do it? It must also work "online", ...

**2**

votes

**2**answers

20 views

### Combine multiple email addresses (in separate cells) into one cell (separated by comma) Google Sheets or Excel

I'm trying to combine multiple cells with email addresses into one cell where emails are separated by commas.
I've used this formula:
=P2 & ", " & Q2 & ", " & R2 & ", " & AD2 ...

**2**

votes

**1**answer

51 views

### Google Sheets to Excel

I have this array formula.
=ArrayFormula(IF(P3:P="",,IFERROR(VLOOKUP(P3:P,sort({{C:C,L:L,A:A};{D:D,M:M,A:A}},3,0),2,0),UPLUS(V3:V))))
It works fine in google sheets. But when I converted my google ...

**2**

votes

**1**answer

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

**2**

votes

**1**answer

99 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

**1**answer

46 views

### Excel autonumber range

Most of you know about this little feature of Excel, as well as almost any other spreadsheet software like Google Spreadsheets called Autonumbers. Example.
What I'd like to know if it's possible to ...

**2**

votes

**1**answer

234 views

### HTML tags in Google Spreadsheet [closed]

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

**2**

votes

**1**answer

3k views

### Remove line breaks within cells in a range (\n) by Google Apps Script [closed]

In a Google Spreadsheet I would normally do the following:
Go to Find and Replace
Check "regular expressions"
Find \n and replace with null
I have searched and for the life of me I'm unable to ...

**2**

votes

**1**answer

262 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

252 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

188 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

4k 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

2k 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

497 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

**3**answers

152 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

48k 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

99 views

### Count number of cells that match certain criteria with formulae

Suppose I have column with dates:
2015-01-10
2015-08-12
2014-01-02
2015-01-28
2016-10-14
I want to count how many dates fall to January. I would supopse that formulae is like this COUNTIF(A1:A20,"...

**1**

vote

**2**answers

340 views

### Is there a tool to put a phone number into E.164 format?

I'm looking for both a quick and dirty, as well as easily repeatable, solution to strip non-numeric data from a column in a spreadsheet for import to a MySQL database. Example:
(123)456-7890 fred ...

**1**

vote

**1**answer

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

43 views

### Google Sheets - Conditional Formatting based on column heading of cell value [closed]

I've had a problem that's been bugging me for days and I can't quite figure it out. Scenario:
Names: This is an unsorted sheet where there are a bunch of names.
Team: This is another unsorted sheet, ...

**1**

vote

**1**answer

83 views

### Excel If cell is blank show nothing else carry out this IF statement

I have the following formula
=IF(N37="Y",5,-5)
At the moment it shows -5.00 if the cell N37 is blank however I have it to display nothing until the cell contains N or Y.

**1**

vote

**1**answer

215 views

### How to simplify Excel/Google Sheets formula with redundant method calls?

I have an Excel (Well, technically Google Sheets) workbook containing product inventory warehouse storage information. I figured out how to construct queries so it grabs the needed information from ...

**1**

vote

**1**answer

75 views

### Creating a table similar to pivot table

I'm not sure how to explain it, but I hope this example makes it clear.
Data:
Yes/No | Place1 | Place2 | Place3
----------------------------------
Yes | Thing1 | Thing2 | Apple
No | Apple | ...

**1**

vote

**1**answer

24 views

### How to make a formula to calculate how many of each unique item are in a column in the last 365 days

I have two columns A:A (dates) and B:B (items)
.....
I need to find a formula to calculate how many of each unique item are in column B : B in the last 365 days.
In the example the right value in the ...

**1**

vote

**2**answers

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

64 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

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