Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

163
votes
6answers
482k 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?
122
votes
2answers
225k 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?
101
votes
3answers
206k 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?
86
votes
2answers
126k 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 ...
81
votes
5answers
89k 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?
80
votes
3answers
122k 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), ...
77
votes
7answers
313k 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 ...
69
votes
16answers
93k 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 ...
68
votes
12answers
162k 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 ...
68
votes
9answers
130k 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 ...
60
votes
6answers
172k 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 ...
54
votes
4answers
159k 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
6answers
129k 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 ...
46
votes
2answers
184k 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
183k 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?
43
votes
10answers
72k 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 ...
43
votes
7answers
60k 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 ...
42
votes
4answers
92k 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 ...
42
votes
12answers
46k 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: ...
41
votes
13answers
107k 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)
40
votes
5answers
61k 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 ...
38
votes
4answers
106k 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.
37
votes
4answers
101k 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
2answers
29k views

How to paste a comma separated string over multiple cells in Google Spreadsheets

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?
36
votes
13answers
91k 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 ...
36
votes
2answers
29k 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 ...
36
votes
5answers
52k 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 ...
35
votes
11answers
43k 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:...
33
votes
5answers
128k 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 ...
32
votes
2answers
22k 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 ...
32
votes
3answers
76k 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 ...
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
10answers
81k 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
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.
31
votes
3answers
25k 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 ...
31
votes
1answer
55k 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 ...
30
votes
3answers
50k 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 ...
30
votes
5answers
40k 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
8answers
42k 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
2answers
108k 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. ...
28
votes
1answer
159k 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
206k 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 ...
26
votes
3answers
54k views

Google Spreadsheets: 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
120k 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
75k 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 ...
25
votes
4answers
31k 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 ...
25
votes
2answers
56k views

Google Docs date in “dd/MM/yy” format [duplicate]

Is there any way to use date in format dd/MM/yy? The Google Docs support only MM/dd/yy and it cause us problems to use it (we can't sort the date column).
25
votes
8answers
18k views

What limitations does Google Sheets have in relation to Microsoft Excel? [closed]

Office licenses are expensive. What features of Microsoft Office are missing from Google Sheets that would be of value in ordinary personal or business use?
25
votes
9answers
39k 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 ...
24
votes
8answers
81k 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-...