Questions tagged [google-sheets]
For questions about Google's web application to create and edit spreadsheets on a web browser or app. Don't use it for the Google Drive Excel files editor. Please consider adding a demo spreadsheet to your question; this will greatly increase the chance that you get a satisfactory answer.
6,654
questions
32
votes
9answers
47k views
Text to columns conversion in Google Spreadsheets
How do you do a text-to-columns conversion in Google Spreadsheets?
For example, I have the following data string in one cell:
5,233,6,2,6,7,2,2,6,6
I want to break it apart by the comma separator ...
95
votes
14answers
225k views
How do I calculate a time delta in my Google Spreadsheets?
I've got a Google Spreadsheet that looks roughly like this:
Date | Start time | End time | Minutes
------------+-------------+-------------+-----------
1/11/2012 | 11:39 | 12:41 ...
65
votes
17answers
132k views
How to prevent cut and paste data changing references in formulas?
Google Sheets has a feature where if you have a reference to a data cell in a formula, and you cut the data from that cell and paste it in a new location, the reference in the formula is updated to ...
13
votes
5answers
47k views
How can I convert a table in a Google Docs document to a spreadsheet?
I've tried selecting the table in the document, copying it, and pasting into a new spreadsheet, but this results in a one-column spreadsheet with all of the columns flattened into one.
I don't see a ...
9
votes
2answers
14k views
What is the equivalent key in Google Spreadsheet to Excel's F2 for editing a cell?
In Excel
When you want to edit the highlighted cell, and you don't want to move back to the previous cell when pressing the <- key, you press F2.
Pressing F2 ensures that when you're editing the ...
2
votes
1answer
4k views
Can I precisely position charts in Google Sheets?
I am using Google Sheets to create a "Monthly Report" spreadsheet for a product, and am attempting to make an attractive, compelling experience for the consumers of the report.
In this spreadsheet, I ...
3
votes
3answers
19k views
How to have auto updated current time cell in GoogleDocs / Excel
I am trying to create a spreadsheet that will allow me to track my clients' timezones.
I am using the useless NOW() function to calc current time, and then factor in the time zone value (+/- a #) as ...
2
votes
1answer
7k views
Copy data down into next set of blank cells, if any, for whole column?
I have some text that I've taken in from a pdf, however the columns are not lined up the way I need them to be:
I want to duplicate the column A and D values down into any empty spaces/rows. So in ...
1
vote
1answer
7k views
Circular dependency detected even while using IFERROR
I have the following function in the cell B2:
ARRAYFORMULA(SUM(IFERROR(Purchases!B3:B, 0)))
And the following function in cell C2:
ARRAYFORMULA(SUM(IFERROR(Purchases!C3:C, 0)))
Basically, the ...
2
votes
1answer
6k views
Apply Formula to Newly Inserted Row
I have a spreadsheet that keeps a running balance of a credit card with the newest transactions at the top (just like credit card web sites).
An example of the formula for each cell in the Balance ...
117
votes
3answers
233k views
How can I create a fixed column header in Google Spreadsheet like the first sheet?
How can I create a fixed column header in Google Spreadsheet like the first sheet?
3
votes
1answer
390 views
Getting a new “publish to web” URL for a published Google doc
When I publish a Google spreadsheet to the web, it generates a URL that I can share with others. I know I can stop publishing. When I re-publish, the URL is the same.
I no longer want ALL viewers who ...
2
votes
1answer
211 views
Update a cell based on two matching fields in separate tabs within a range
I'm trying to update a cell in Google Spreadsheets where its value comes from another sheet and based on the date range entered with it.
Here is what the two worksheets look like in the file.
...
2
votes
3answers
2k views
Missing sort in Google Spreadsheets menus
I have a Google spreadsheet that has the A column as an ID field (incrementing numbers 1...1000; no formula, just numbers). That column is frozen as is the first row of headers.
I sorted by column B ...
13
votes
2answers
20k views
Can you specify qualifier and delimiter characters when exporting from Google Spreadsheet?
Can you specify qualifier and delimiter characters when exporting from Google Docs?
Right now, when I go to a spreadsheet and do File » Download As, it downloads something that is comma delimited and ...
5
votes
2answers
6k views
Using data from another sheet in Google Spreadsheet
I'm trying to write a SUM function in sheet B, summing all the data from sheet A in specific rows. I can't find a good explanation of how to do this.
42
votes
4answers
37k views
Forcing uniqueness in a Google Spreadsheets column
I have created a Google spreadsheet (acting as a database) that has numerous columns, name being one of them, and I wanted to make sure the name field is always unique and no row can be created if the ...
7
votes
1answer
630 views
Including option descriptions for a Google Docs Spreadsheet form
Usually, the options for a form are pretty self-intuitive, but sometimes, you need to explain each of the individual options. In close proximity to the actual question. However, the options for a ...
3
votes
1answer
4k views
How to freeze a row height or prevent line breaks in Google Spreadsheets?
My question is very similar to a previous question here, but I don't want the scroll bars.
When I paste a link in a cell it usually breaks the cell in multiple lines, increasing the row height. Even ...
12
votes
5answers
25k views
How do we set up notifications for documents shared to us in Google Sheets?
My friend has shared a file with me on Google Drive and I would like to be notified whenever changes are made to the file (pretty reasonable request I'd say):
You can set notifications to find out ...
15
votes
2answers
86k views
Sum data based on cell color in Google Spreadsheets
I have seen this question and altough I face the same problem, the answer to that question is not helpful to my case.
I have large spreadsheets which are changed frequently. So, according to the ...
39
votes
5answers
63k views
Multi user filter issue in Google Spreadsheets
When two or more users view the same Google Spreadsheet and one of them filters the data by a column, that filter is immediately applied to all other users as well.
Is there a way to avoid this and ...
11
votes
2answers
15k views
Disable Default Word Wrap in Google Spreadsheets
Does anyone know how to make it so that Word Wrap is OFF by default in Google Spreadsheets?
7
votes
2answers
13k views
Chart of averages on Google Sheets
I have day by day data. I would like to display a line chart with this daily data as well as weekly average bar chart.
How can I display daily as well as weekly trend chart by only having daily data ...
3
votes
1answer
4k views
Can I auto archive my Google Spreadsheet?
We are using Google Spreadsheets for call logs, which is shared with more than 20 users and we all add entries simultaneously.
I would like to know if there is any feature to automatically archive ...
4
votes
1answer
452 views
Offline capability in web app with sync when online
I need to give a survey to people on a tablet device. The survey runs on a browser which might often disconnect from the internet, hence I need to have the device sync whenever its in the wifi ...
79
votes
7answers
328k views
Is it possible to add a checkbox/button equivalent?
What I'm looking for is a one click enable/disable or toggle in some form. The closest I've come is manually editing a cell to enter a 0 or 1, but I'm hoping there's a nicer pattern.
I know there's ...
13
votes
2answers
14k views
Sum of all cells in Google Spreadsheet where matching row fits a value
I want to return the sum of all cells in ColumnA only if the value of a cell on the same row is equal to a specific value.
For example, sum C4 if D4 = "Bob". Assuming there are six rows whereby "Bob"...
10
votes
3answers
14k views
How can I stop Google Sheets formatting a Forms response as a date?
I have a Google Forms with a question that contains a drop-down box with the following values:
- Fewer than 10
- 11-25
- 26-50
- More than 50
If the user selects 11-25, Google Sheets renders the ...
5
votes
2answers
5k views
Create a 7 Day Running Total and Running Average
I have a Google Spreadsheet tracking figures day by day. I want to add a column with a 7 day running total (sum of last 6 rows plus current row) and a column with a 7 day running average (average of ...
7
votes
3answers
10k views
How do I add a link in a Google Sheets to a specific paragraph or header in a Google Docs?
How do I add a link in a Google Sheets to a specific paragraph or header in a Google Docs?
1
vote
2answers
1k views
Is there a way to add a row to a Google Spreadsheet via a script?
I'd like to be able to call a script, which will add a row to a Google Spreadsheet, and manage stuff like user login. For example,
gdocs_addrow --id <doc-id> name="John Doe" age="31"
I know ...
26
votes
11answers
129k views
How to alter text orientation to vertical in Google Sheets?
Using MS Excel I can go to Format of the cell and choose the text orientation from horizontal to vertical fairly easily.
How do I alter text orientation to vertical in Google Sheets?
4
votes
2answers
6k views
Problem using the TRANSPOSE() function
I need some help with a Google Sheets. Let's say I've got a column and in the column I have three rows containing the values A, B, and C:
Column:
A
B
C
I need to transpose the column so that it ...
4
votes
2answers
6k views
How do I link text to a URL in a Google Docs spreadsheet? [duplicate]
Possible Duplicate:
How do I add a hyperlink to a cell in Google Spreadsheets?
When I paste a URL into a cell in a Google Docs spreadsheet, it turns into a nice blue, underlined hyperlink. It ...
2
votes
2answers
2k views
How to calculate and chart cumulative flex time?
Being a (productive) geek, I want to keep track of the trend of my flextime. I keep a personal document of my hours worked in Google Sheets, having one column for hours worked every day. By ...
10
votes
1answer
9k views
Retrieve a cell having a column and a row header in Google Spreadsheets
I have a spreadsheet in Google Docs like this:
Johan Mark Gil Pablo
Course 1 45 50 80 50
Course 2 250 300 50 270
Course 3 19 50 ...
62
votes
5answers
269k views
Adding a datepicker in Google Spreadsheet
Is it possible to insert a datepicker in every cell of a column in Google Spreadsheet so that anyone can click (with a single click) on that cell and get a datepicker calendar to select a date?
-1
votes
2answers
130 views
Create a formula based on the number of columns in Google Spreadsheets
I have two rows of data
Row B = price of product
Row C = quantity of producst
I need to get the summary price:
(B1xC1)+(B2xC2)+(B3xC3)+..+(Bn+Cn)
I don't know n (quantity of columns) because n is ...
4
votes
2answers
6k views
URL syntax and display options?
I recently came across a Google Sheets that had a slightly different syntax in the URL. I found these tags after the Spreadsheet Key:
&single=true&gid=0&range=B5%3AB5&output=html
I ...
4
votes
2answers
2k views
How do I populate a cell in Worksheet A with aggregate data from Worksheet B?
I'm working on a spreadsheet that allows some basic collaborative hour tracking.
Worksheet A contains an entity called Blocks where each block has a BlockID.
Worksheet B contains line items. Each ...
12
votes
5answers
58k views
Conditionally formatting a cell based on another cell's value in Google Spreadsheet
I did some searching on this and came across this post.
However, this solution uses an onEdit() trigger. I would like to see a solution that uses a formula approach, so in the OP's example from that ...
1
vote
2answers
997 views
How do I select Column1's value if an input value is between the integers in Columns2:3 for a range?
In a Google Sheets I have two tables:
Date BMI BMI Outcome
2011-06-21 22.1 ?
2011-06-22 21.8 ?
2011-06-23 21.5 ?
and
Name From To
Severely underweight ...
7
votes
1answer
961 views
Google Forms erroneous deletes rows in second sheet
I created a form in Google Docs with quite some questions. For a good overview I created a second sheet to pick the important parts from the survey. There I'm also able to add extra columns to add ...
2
votes
1answer
139 views
Can I add a search box that will open a new page on some 3rd party site to Google Docs?
I'm trying to make a document/spreadsheet containing a few resources I regularly use and perform searches in. Can I create something I can perform searches directly from a document that will open up ...
42
votes
4answers
130k views
Display sheet name in Google Spreadsheets
Is there any way to display a sheet name as the result of a function in a Google Spreadsheets (exactly like this question, except in Google Sheets)?
4
votes
4answers
5k views
Returning the SUM of a range of cells from user-input START to END
In Google Docs, I am trying to sum the values of selected rows in a table, from position START to END.
Imagining this sample data:
Index VALUE
1 300
2 400
3 750
4 850
5 ...
20
votes
5answers
34k views
Build a cell reference without knowing Row # (number)
This can't be a more basic spreadsheet question but I have always been a SQL kinda guy...
How do I build a formula where a value will be pulled from another column on the current row. Can't be the ...
2
votes
1answer
9k views
Embed a Google Sheets in a forum post
I want to embed a Google Sheets in a forum post so that it is easily viewed by people. The spreadsheet should be read-only in the embedded status.
Any way to do it?
3
votes
2answers
5k views
Is there a way to search and replace a regular expression in Google Docs spreadsheets?
The problem is that I entered a lot of formulas like =round(x+y) into a Google Docs spreadsheet. Now I need them exported to MS Excel and the latter only accepts =round(x+y,0) as a valid format. ...
