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,625
questions
203
votes
8
answers
660k
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
170
votes
3
answers
276k
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,075
163
votes
2
answers
369k
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,929
147
votes
9
answers
734k
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,817
145
votes
3
answers
135k
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,567
120
votes
3
answers
251k
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,728
111
votes
4
answers
354k
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,265
110
votes
14
answers
275k
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
90
votes
10
answers
335k
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
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?
- 51.9k
83
votes
19
answers
180k
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 ...
- 946
83
votes
6
answers
203k
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
79
votes
7
answers
335k
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,048
77
votes
15
answers
113k
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,147
74
votes
2
answers
74k
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,815
74
votes
9
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
68
votes
3
answers
241k
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: ...
- 893
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,123
67
votes
14
answers
168k
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,027
67
votes
12
answers
96k
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.4k
64
votes
15
answers
188k
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
193k
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
62
votes
5
answers
50k
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
60
votes
5
answers
115k
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 ...
- 609
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
56
votes
5
answers
90k
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,766
53
votes
5
answers
87k
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 ...
- 641
48
votes
9
answers
83k
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.
- 584
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,728
47
votes
7
answers
61k
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
45
votes
12
answers
106k
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
44
votes
4
answers
85k
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
4
answers
118k
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,470
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
137k
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
71k
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 ...
- 529
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.5k
42
votes
6
answers
188k
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,147
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
45k
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,295
39
votes
5
answers
68k
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,470
39
votes
4
answers
87k
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 ...
- 5,923
38
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,418
38
votes
4
answers
155k
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
37
votes
3
answers
76k
views
FILTER(condition OR condition) syntax?
I am using FILTER() to display a value from Sheet 2 where the Name columns match.
My problem is where I want:
=FILTER(Sheet2!A:F, Sheet2!A:A="Combat Medic" OR Sheet2!A:A="Universal")
as OR results ...
- 411