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.
8,672
questions
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
175
votes
3
answers
284k
views
How to group data in a Google Spreadsheet?
I have the following table in a Google Spreadsheet:
Name | Value
A | 10
B | 100
A | 20
B | 200
C | 1000
How can I create another table based on a query:
select name, sum(value), ...
- 2,125
163
votes
2
answers
370k
views
How does one add a new line in a cell in a Google Spreadsheet?
I pressed return to make a new line in a cell of my Google Spreadsheet. That just moved the cursor to the next cell.
How does one add a new line in a cell?
- 7,959
147
votes
9
answers
740k
views
Google Sheets formula for "if contains"
I'm trying to figure out how to identify IF a list of items in one cell containing a value or string.
EXAMPLE
Cell A1 contains sites, sheets, docs, slides.
I want cell B1 to display a 1 'if' cell A1 ...
- 2,827
146
votes
3
answers
140k
views
How to paste a comma separated string over multiple cells in Google Sheets?
Say I have a string of: l,f,x,a,s,f
I would want to paste this every comma-separated value over 6 adjacent cells. How would I do it?
- 1,577
120
votes
3
answers
252k
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?
eureka
115
votes
2
answers
235k
views
How to convert rows into columns in Google Spreadsheets?
I have a spreadsheet in Google Docs with two data rows and lots of columns like so:
Is it possible to easily convert these rows into two columns and lots of rows (obviously retaining all information ...
- 3,738
114
votes
4
answers
367k
views
How to check if value is in range of cells?
In a Google Sheets spreadsheet, I want to check if the value of a cell exists in a range of cells. How can I do this?
Just like you would be able to do "if value in list_of_values" in Python ...
- 1,295
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
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
85
votes
19
answers
186k
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 ...
- 966
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
83
votes
6
answers
204k
views
How to add days to a date
I'm using a Google Spreadsheet to perform calculations and I'd like to do some simple date math. For example,
TODAY() add 7 days.
I don't see any functions to do it and some quick stabs in the ...
- 963
80
votes
7
answers
336k
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 ...
- 1,058
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
75
votes
2
answers
77k
views
Summing a column, filtered based on another column in Google Spreadsheet
I have the following data in a Google Spreadsheet:
+------+---------+
| Time | Include |
+------+---------+
| 0:30 | No |
| 1:00 | Yes |
| 0:30 | Yes |
| 0:30 | No |
| 0:30 | Yes ...
- 1,825
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
70
votes
14
answers
172k
views
How to set a Google Docs Spreadsheet cell format to bare text?
I am to store international standard phone numbers (starting with a plus sign followed, usually, by plain numbers) in a Google spreadsheet column. An example of such a phone is
+420123456789
When I ...
- 2,057
68
votes
3
answers
244k
views
Link to specific sheet in Google spreadsheet
I have a complicated Google spreadsheet with many sheets and a table of contents. Is there some way to create a link to the sheet names so that with a click one can go directly to the sheet? That is: ...
- 903
67
votes
6
answers
242k
views
Formatting a cell with word wrap without switching modes?
How can one format a cell to have word wrap in Google Sheets?
I've tried Shift+Enter, but that doesn't seem to work.
One thing I know works is to place the spreadsheet into List Mode and then if you ...
- 2,153
67
votes
13
answers
99k
views
How to pass a range into a custom function in Google Spreadsheets?
I want to create a function which takes in a range... something like:
function myFunction(range) {
var firstColumn = range.getColumn();
// loop over the range
}
A cell would reference it using:
...
- 30.6k
65
votes
5
answers
52k
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 ...
zsimple
64
votes
15
answers
190k
views
Shortcut to insert new row in Google Spreadsheets?
Is there a way to do this without using the menu?
I have seen references to ctrl + space and ctrl + +, but this no longer works (zooms in and out in Google Chrome)
- 797
63
votes
8
answers
195k
views
How to group data by month in Google Sheets
I have a Google Sheet which contains various metrics for each day. Each day has it's own row:
day | cost | impressions | clicks | conversions
2014-02-01 | $45 | 3,540 | 80 | 5
2014-...
- 803
63
votes
5
answers
292k
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?
- 749
61
votes
5
answers
118k
views
Sum values with same name
I have a Google Sheets with a long list of names and amounts. Many of the names are duplicates. For example:
John | 10
Bill | 2
Susan | 3
Frank | 4
Sally | 10
John | 2
Susan | 2
John | 3
Is ...
- 619
58
votes
6
answers
132k
views
ISBLANK() function
Is there any way I can check whether a cell is empty?
There is a function ISBLANK() but it returns false when there's a formula in a cell even thought the formula doesn't return anything (or an empty ...
- 2,195
57
votes
5
answers
93k
views
Link to a cell in a Google Sheets via URL?
One can link to a Google Sheets via key, and via key and worksheet id:
https://docs.google.com/spreadsheet/ccc?key=SOME_KEY
https://docs.google.com/spreadsheet/ccc?key=SOME_KEY#gid=WORKSHEET_ID
Is ...
- 1,786
54
votes
5
answers
89k
views
How can I get GOOGLEFINANCE to return only the historical price of a stock and not an array?
I'm trying to figure out the cost basis of some stock that I sold last year and I can't quite get GOOGLEFINANCE to do what I want. The stock was purchased through a DRIP so there are about five shares ...
- 651
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
48
votes
3
answers
83k
views
How to reverse the order of selected rows?
Is there an easy way to reverse the order of selected rows?
(Or how about all rows, if that's easier.)
Specifically, in this case, there is no column that could be used for sorting to get them in ...
- 3,738
47
votes
7
answers
62k
views
Hot keys to switch between tabs? [closed]
Does anyone know if there´s a shortcut to switch between tabs in Google Spreadsheets?
If you use CTRL+PgUp / CTRL+PgDown like in Excel you switch between Chrome or Firefox's tabs instead.
- 1,763
46
votes
13
answers
109k
views
How to specify the entire sheet as range in Google Sheets?
The best workaround I have found so far is:
worksheet_name!$A$1:$YY
but ideally I would like to be able to simply write, e.g.:
worksheet_name!
So, does anyone know: is there a syntax for ...
user6757
45
votes
6
answers
75k
views
How to export a filtered set of rows from a Google Spreadsheet?
Is it possible, when exporting a Google Spreadsheet into a CSV, to apply this operation only to filtered set of rows?
I need to get in CSV exactly the rows which are visible on the sheet after a ...
- 559
45
votes
4
answers
122k
views
Fill value in multiple cells in Google Sheets
In MS Excel, after selecting a range of cells it is possible to enter a value and press CTRL+ENTER to have that value set in all selected cells.
Is there a keystroke in Google Spreadsheets that would ...
- 2,510
44
votes
4
answers
86k
views
Add 3 hours to Date value (DD/MM/YYYY 00:00:00)
I have cells with dates in this format: DD/MM/YYYY 00:00:00 and I want to add 3 hours because I'm in a different GMT zone.
Is possible to use dates in a formula?
Something like: 30/12/2012 22:15:00 ...
- 551
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
43
votes
3
answers
151k
views
How to turn off Insert/Overwrite in Google Spreadsheets
When editing text in Google Spreadsheets, in the formula bar, or editing the name of the sheet, overwrite is toggled, and pressing the insert key (numberpad or 'regular' insert key, numlock active or ...
- 1,159
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)?
43
votes
7
answers
70k
views
Alternating colors for lines in Google docs spreadsheet
I have a shared Google spreadsheet where several users have already filled out parts.
It would be useful to have alternately colored lines to facilitate correct placement of additional data.
Is ...
- 541
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
42
votes
6
answers
198k
views
How can I automatically set "last updated" cell in row Google Docs Spreadsheets?
How can I automatically set "last updated" cell in row Google Docs Spreadsheets ?
I want to create a column where the cells' value will be automatically set to when that row was last amended. Is this ...
- 784
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
41
votes
4
answers
89k
views
How to save temporary variables in Google Sheets formula?
I'm trying to create a formula in a Google spreadsheet which looks like this:
if (x < 0, x + 1, x)
It means if x is smaller than 0, return x + 1, else return x.
However, x itself is an ...
- 6,023
40
votes
6
answers
177k
views
Google Spreadsheets automatically removes leading zeroes
I have a column comprised exclusively of numbers. Whenever I add 050, the sheet automatically changes it to 50. I have tried prepending a space, but it makes no difference.
What can I do to avoid ...
- 533
40
votes
1
answer
46k
views
How to sum only visible cells on Google Sheets?
As per question title, how to sum only the visible cells on Google Spreadsheets?
- 1,315
39
votes
10
answers
126k
views
How to insert the current date into a Google Spreadsheet?
I've got a spreadsheet in Google Drive and one column is meant for the date the entry is made. Is there a button to do this? I'm aware of the functions =NOW() and =TODAY() but their description says "...
- 3,448
39
votes
5
answers
69k
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 ...
- 2,510
39
votes
1
answer
28k
views
How to see which formulas reference a given cell in Google Sheets?
Is there a way to see which cells reference a given cell in their formulas, in Google Sheets?
For example, if cell C1 contains =SUM(A1:A5), and cell D1 contains =SUM(A5:A8), I'd like to somehow be ...
- 541
38
votes
4
answers
158k
views
How to plot time series graph in Google Sheets?
I know it is possible to plot graph or chart in Google Sheets but the problem with current plotting way is that you can only specify the Y, but not the X, and as such there is no way to plot a time ...
- 1,405