The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Questions tagged [google-sheets]

For questions about the Google's web application to create and edit spreadsheets on a web browser. 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 chances to get a satisfactory answer.

Filter by
Sorted by
Tagged with
178
votes
6answers
519k views

How do I link a cell in Google Spreadsheets to a cell in another document?

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?
128
votes
2answers
253k 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?
107
votes
3answers
214k 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?
96
votes
3answers
141k 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), ...
90
votes
2answers
155k 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 ...
82
votes
5answers
91k 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?
78
votes
7answers
321k 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 ...
73
votes
13answers
177k 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 ...
72
votes
16answers
96k 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 expands ...
69
votes
9answers
131k 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 ...
64
votes
6answers
220k 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 ...
57
votes
6answers
180k 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 ...
53
votes
3answers
41k 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?
53
votes
6answers
130k 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 ...
52
votes
5answers
207k 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?
50
votes
5answers
109k 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 ...
49
votes
13answers
85k 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 ...
48
votes
13answers
133k 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)
48
votes
4answers
131k views

How to check if value is in range of cells?

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 for example.
47
votes
2answers
191k 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: ...
45
votes
5answers
68k 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 ...
44
votes
12answers
52k 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: ...
43
votes
7answers
62k 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 ...
40
votes
2answers
32k 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 ...
39
votes
4answers
111k 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)?
37
votes
13answers
102k 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 ...
37
votes
3answers
85k 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 ...
37
votes
5answers
55k 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 for the other users as well. Is there a way to avoid this and ...
36
votes
11answers
45k views

How to calculate weighted averages in Google Sheets?

I have a Google Sheets where products are listed as rows and attributes as columns. Each product's attribute is rated on a scale of 1-10. My last column is an average of these values (i.e. =Average(B2:...
36
votes
3answers
28k 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 ...
35
votes
3answers
25k 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 ...
35
votes
7answers
162k views

Google Sheets formula for “if contains”

I'm trying to figure out how to identify IF a list of items in one cell contains a value or string. EXAMPLE Cell A1 contains sites, sheets, docs, slides. I want cell B1 to display a 1 'if' cell ...
34
votes
5answers
150k views

Leading zeros in Google Spreadsheet automatically removed

I have a column in which all inputs are numbers. But these numbers are actually not real numbers. Not real in the sense, 050 in this case is very different from 50 The problem is that whenever I add ...
33
votes
8answers
99k 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-...
33
votes
3answers
56k 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 ...
32
votes
4answers
16k views

How do I convert a cell to RFC 3986 standard (URLEncoded) in Google Spreadsheets?

Is it possible to convert text from a certain cell to RFC 3986 (http://url-encoder.org/) in a Google Spreadsheet? Lets say C4 has the value John Doe, then D4 must be John%20Doe.
32
votes
2answers
58k 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 ...
32
votes
9answers
45k 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 ...
31
votes
11answers
102k 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?
31
votes
3answers
102k 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 ...
31
votes
5answers
39k 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 ...
31
votes
7answers
47k views

Hot keys to switch between tabs?

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.
29
votes
2answers
114k 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. ...
29
votes
11answers
56k views

Can I change cell padding in Google Spreadsheets?

I don't want to stretch all rows and columns to make them more readable than they are by default...
28
votes
1answer
167k views

Reference data from another 'spreadsheet' in Google Docs/Drive [duplicate]

Is there a way i can reference data from another Google Spreadsheet in Google Docs/Drive? e.g., spreadsheet-A, sheet 1, column A is a dynamic reference of spreadsheet-B, sheet 1, column E I'm not ...
28
votes
6answers
214k views

Checkbox function to automatically add to total [duplicate]

I'm doing my club's accounting in a Google spreadsheet. I am looking for a way to put a checkbox at the beginning of a line in order to tick off the members who paid their dues and then have a ...
27
votes
4answers
60k 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 ...
26
votes
11answers
122k 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?
26
votes
2answers
58k 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 ...
26
votes
2answers
80k views

IF OR Statements

How do you do an IF ... OR ... THEN statement in a Google spreadsheet? In PHP I would do this: if($C7 == "U.S.A." || $C7 == "Canada"){ ... How to do the same as a spreadsheet formula? At the ...