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 data as markdown table to your question; this will greatly increase the chance that you get a satisfactory answer.
1,016
questions
4
votes
2
answers
2k
views
How to share a Google Sheets demo spreadsheet
I find a lot of -especially new- users on the site are having difficulties on sharing a demo sheet so they can be easily helped if any at all.
So, what is the right way to share a demo test sheet?
- 12.5k
15
votes
2
answers
28k
views
How to know if Google Sheets IMPORTDATA, IMPORTFEED, IMPORTHTML or IMPORTXML functions are able to get data from a resource hosted on a website?
Google Sheets has several import functions
IMPORTDATA
IMPORTFEED
IMPORTHTML
IMPORTXML
Sometimes the above functions returns errors like #N/A Imported content is empty and would like to be sure that ...
- 43.9k
1
vote
1
answer
2k
views
Finding the longest time duration in Google Sheets
I'm trying to find the longest time value (hh:mm:ss) and I'm looking for it in column I, starting at row 5. I've already tried using the LARGE function but that outputs a decimal, not a time.
=LARGE(...
- 21
203
votes
8
answers
661k
views
How do I link a cell in Google Spreadsheets to a cell in another document? [closed]
I have a monthly spreadsheet that relies on figures from the previous month. I'd like to import these values dynamically rather than cutting and pasting them. Is this possible? How do I do it?
- 2,565
74
votes
8
answers
140k
views
How can I set conditional formatting on a particular cell that depends on another cell's value?
In my example I would like to conditionally format column B cells. Those marked with x should be formatted according to value in column A (in the example the value is 1):
A | B
1 | x
2 |
3 |
1 | x
...
- 2,195
22
votes
3
answers
65k
views
Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries)
I'm compiling a database of business address info, including key staff for each business. The Google spreadsheet I've inherited for this project includes a column for 'key staff' (in column B), with ...
- 331
44
votes
3
answers
105k
views
Auto-updating column in Google Spreadsheet showing last modify date
Trying to figure out a way to have column two auto-update to show a timestamp of the last update. I realize the doc shows a timestamp of the last modification, but we have a lot of clients accessing ...
- 451
110
votes
14
answers
277k
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 ...
- 2,037
41
votes
11
answers
158k
views
Is there any way to create a timestamp in a Google Spreadsheet?
I use PSPad as a text editor, which allows you to press Alt + D to insert a timestamp, e.g.:
2010-07-17 23:45:44
Is there a way to do this in a Google Spreadsheet?
- 3,187
16
votes
2
answers
20k
views
Refer to Sheet with value in Cell?
The usual way to reference a sheet is here but how do you reference a sheet when the sheet's name is a value in a cell?
For example,
A1 = "Sheet number two"
B1 = A1&"!B4"
Here I want B1 to ...
- 263
91
votes
10
answers
341k
views
How can I make some data on a Google Sheets auto-sorting?
Let's say I want Google Sheets to automagically sort some data, for example column C.
How do I do that?
I know that I can manually sort data by right-clicking and selecting Sort data, but this is ...
Diego Petrucci
42
votes
6
answers
36k
views
Cumulative Sum without Script
For this question, I've created a Google Apps Script function, that calculates the cumulative sum of a range. Martin Hawksey on Google+ made a comment about a more efficient way of calculating the ...
- 22.7k
20
votes
3
answers
50k
views
Cell reference with colour formatting
Is it possible to reference a cell in Google Sheets so that cell where it gets displayed also displays it using the same text and cell colour formatting?
=A1
Will only reference cell's value. But if ...
- 2,195
17
votes
1
answer
49k
views
In Google Sheets how do I duplicate a sheet along with its permission
In a Google Spreadsheet called Attendance there is a sheet called Template. The user duplicates this sheet, renames the sheet with current date and uses this sheet to mark attendance for students.
The ...
- 433
21
votes
4
answers
72k
views
Extract the link text and URL from a hyperlinked cell
Suppose I have a hyperlink in cell A1: =hyperlink("stackexchange.com", "Stack Exchange")
Elsewhere in the sheet, I'd like to have formulas that get the link text and URL from A1, separately. I found ...
user79865
21
votes
7
answers
78k
views
Can I add an autoincrement field to a Google Spreadsheet based on a Google Form?
Is it possible in Google Forms to give a unique value to each row it inserts into the spreadsheet as well as the timestamp?
- 669
8
votes
5
answers
10k
views
Split a column of strings with a single formula returning an array
I have a column of strings like this:
+---------+
| a bb |
| ccc d |
| ee ffff |
+---------+
(exactly one space in each string), which I'd like to split by the space into two columns:
+-----+-...
user79865
5
votes
3
answers
9k
views
Recalculate google sheet on demand:
I have the following formula in a sheet:
=if(E1="HOLD",,query(Cust_Orders!B6:Z5000,"Select Y,G,I,H,K where H>0 "))
With E1 being a drop down with values HOLD and FETCH
The recalculation when I ...
4
votes
3
answers
7k
views
Forms Data Manipulation In Google Sheets
I'm trying to develop a solution using Google Forms with Google Sheets that will collect Google Forms data and then automatically divide that one form submission's data into multiple rows of data in ...
- 2,827
2
votes
1
answer
6k
views
Dynamic date range query
I'm trying to create two cells that will update my query: A >= date"A1" and A <= date "B1".
My goal is to create a search page for the data, where the end user could insert a date or the =today() ...
- 21
2
votes
1
answer
4k
views
Transpose Ranges repeated by row
I would like to convert several range of columns into rows, and keeping the association to the original col/row source
Sample source sheet
---------------------------------------------------
| Data ...
- 43
7
votes
3
answers
40k
views
Unable to parse query string for function Query, when using IMPORTRANGE
This is the problem I'm seeing:
Error - Unable to parse query string for function QUERY parameter 2: NO_COLUMND
The formula is of the form:
=query(importange("...", "Sheet!A:V"), "select D where ...
- 107
84
votes
5
answers
95k
views
Embed Google Spreadsheet in Google Document
Is it possible to embed a Google Spreadsheet into a Google Document à la Microsoft Excel and Microsoft Word? How about a chart from a Spreadsheet?
- 52.1k
77
votes
15
answers
114k
views
In a Google Spreadsheet, how can I force a row to be a certain height?
In a Google Doc spreadsheet I would like to have each row the same height no matter how much text is in each cell.
I have tried "resize row" but no matter how many pixels I put in, the row ...
- 3,187
17
votes
1
answer
100k
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 ...
- 1,315
13
votes
6
answers
79k
views
Insert image from Google Drive into Google Sheets
How do I insert/link to images existing in a folder in Google Drive into a Google Sheets spreadsheet?
There is the image() function, I imagine using it something like this:
image("googledrive://...
- 421
13
votes
1
answer
56k
views
Show URL used to edit responses from a Google Form in a Google Spreadsheet by using a script
I'm trying to make the edit URL for a Google Form automatically populate in the response Google Sheet that it's attached to. I've seen this already and want to use it, but I'm having issues trying to ...
- 141
1
vote
1
answer
2k
views
Hide/Unhide rows based on values in particular cells
I am trying to Hide/Unhide rows based on values in particular cells, have the following code which works in isolation but not together. Below only makes B121 be active while B55 stops working. If i ...
- 11
48
votes
9
answers
85k
views
Stop a '+' from generating a formula
I'm having a bit of a struggle. I'm creating a spreadsheet which uses plus signs (+) regularly. I want a semi-permanent fix for +'s turning into addition formulae.
- 585
34
votes
4
answers
141k
views
How to automatically insert a new row and retain functions/formulas from last row?
I have a table with cells that have functions/formulas, like this one:
I need a script that creates a new row, copying with it the functions/formulas of the last used row. I find this script which ...
29
votes
2
answers
52k
views
Change Google Sheets default date format with other locale
I would like to have the "dd/MM/yyyy" (e.g. 09/03/2017) as default date format for my Google sheets. I know how to do this for one sheet:
Format > Number > More formats > More date and time ...
- 427
16
votes
2
answers
94k
views
Using FILTER or IMPORTRANGE to bring in and filter data from another spreadsheet
I want to bring in data from a separate sheet and filter it.
I can get IMPORTRANGE to work like this:
=IMPORTRANGE("URL","A:J)
But that doesn't filter. I can only get FILTER to work in the same ...
- 315
12
votes
4
answers
12k
views
Combine two columns into a list of all possible combinations of entries
I have two columns such as:
a 1
b 2
c
and I need to combine them like:
a 1
a 2
b 1
b 2
c 1
c 2
Is it possible with a formula?
- 155
13
votes
2
answers
61k
views
Convert all text to UPPERCASE in a Google Spreadsheet
I have a spreadsheet I'm sharing with others to collect some data. Each person is just (supposed to be) putting a single character in any of a range of cells.
How can I cause those cells to convert ...
- 52.1k
9
votes
3
answers
23k
views
Are Excel formulas and Google Spreadsheet formulas the same?
I want to be sure all my Google Spreadsheet work can be converted OK to Excel. How many of the Excel formulas match the formulas of Google spreadsheets?
- 227
7
votes
3
answers
16k
views
Colorize a cell in Google Sheets based on cell data
I'd like to colorize a cell based on its contents. 0 = red, 100 = green, and linearly interpolated between.
function LinInt(x){
var ss = SpreadsheetApp.getActiveSheet();
var cell = ss....
- 173
4
votes
2
answers
12k
views
Conditionally format row borders
I'd like to have a medium black top border across each row whose A column cell contains text in a Google Spreadsheet.
I'd also like to have the same top border across each row whose whole row is ...
- 87
2
votes
1
answer
2k
views
Add timestamp to two different columns in Google Spreadsheets
Need help in adding another function using the below script.
I would like to have a timestamp "start" and "end" on two different columns, whenever the other two separate columns are edited. Based on ...
- 23
1
vote
5
answers
2k
views
multiple IF statements with between number ranges alternative
How to efectively deal with multiple IF-statements and with multiple "between number" ranges in Google Sheets' spreadsheet (e.g. C column based on B column)
with a rule set as follows:
01-10
11-20
...
- 26k
43
votes
4
answers
138k
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)?
36
votes
2
answers
145k
views
'Lock' part of formula when copying from one cell to another
I have the following formula in a Google Spreadsheet:
=IF(I11="VSS";G11;IF(I11="MCS";0;G11*Totais!C30))
I would like to freeze Totais!C30 when I click on bottom right of cell and drag down.
...
- 563
20
votes
2
answers
27k
views
How can I change the input format for dates in Google Docs' spreadsheet?
The input string for dates in Google Docs's spreadsheet app seems to be set to en_US, which is quite unnatural to me.
How can I change the input format?
- 1,520
20
votes
7
answers
79k
views
How do I mail merge from Google Spreadsheet to a Google Document?
With Microsoft Excel and Microsoft Word, it is easy to merge rows from a spreadsheet into pages in a Word file. This was traditionally used to make paper mailings. How can I do the same with Google ...
- 696
19
votes
4
answers
38k
views
How Do I Combine Columns In Google Spreadsheets? [duplicate]
I need to combine multiple columns into a single column, so:
one four seven
two five eight
three six nine
Becomes:
one
two
three
four
five
six
seven
eight
nine
- 347
14
votes
4
answers
39k
views
Lock the formula of a cell, even if the cells it references change
I have this, in D1:
=sum(D3:D)
I want D1 to always be the sum of the rest of column D (D2 is a header)
I sometimes move a row, from eg 8 to 3, and that causes D1 to change to sum(d4:d).
I want ...
- 245
13
votes
3
answers
17k
views
Highlight all cells in a range that contain a formula in Google Sheets
I am looking to apply conditional formatting to all the cells in my sheet and highlight cells that contain a formula. I know that I can format cells based on a custom formula, and I know that I can ...
- 418
10
votes
6
answers
30k
views
What are the refresh characteristics of the Google Spreadsheet Import functions?
The imported data is not always up to date with the source pages when I load the spreadsheet. When this happens, F5 refresh does not help. Nor does Ctl-F5. Apparently there is some kind of caching ...
- 219
9
votes
4
answers
5k
views
An arrayformula to find the previous non-empty cell in another column
In Google Spreadsheets, without using Apps Script etc., is it possible to achieve the following with a single formula, such as arrayformula (or maybe mmult or something strange, but no fill-down) at ...
- 353
5
votes
2
answers
7k
views
Google Spreadsheet: Adding formulas to Google Forms responses rows
I have a sheet with form response data in several columns, say A:G. Some of the data are numerical and I'd like to have some calculations done on them, e.g. in columns H through J. I have already ...
- 153
5
votes
2
answers
15k
views
Add a 'creation date' value when a new line is edited
I have a Google Spreadsheets that is used by different people to add tasks for my team. Here's an example. People can add tasks to column B and I will assign them to the right people in the team, ...
- 61