Stack Exchange Network

Stack Exchange network consists of 174 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.

154
votes
6answers
454k 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?
112
votes
2answers
206k 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?
95
votes
3answers
200k 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?
79
votes
5answers
88k 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?
77
votes
2answers
107k 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 ...
76
votes
7answers
304k 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
3answers
106k 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), ...
68
votes
16answers
90k 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
9answers
129k 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
12answers
148k 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 ...
56
votes
4answers
141k 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 ...
53
votes
6answers
127k 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 ...
49
votes
4answers
145k 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 ...
45
votes
2answers
178k 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: ...
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 ...
39
votes
5answers
54k 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 ...
39
votes
5answers
164k 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?
38
votes
9answers
63k 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 ...
38
votes
4answers
75k 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 ...
37
votes
4answers
94k 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
11answers
42k 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: ...
36
votes
5answers
49k 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 ...
34
votes
11answers
89k 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)
33
votes
13answers
82k 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 ...
33
votes
4answers
87k 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.
32
votes
11answers
41k 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:...
32
votes
9answers
44k 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 ...
30
votes
4answers
15k 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.
30
votes
2answers
20k 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 ...
30
votes
2answers
24k 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 ...
30
votes
1answer
53k 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 ...
28
votes
5answers
111k 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 ...
28
votes
9answers
70k 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?
28
votes
6answers
201k 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
2answers
100k 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. ...
26
votes
11answers
118k 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
1answer
152k 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 ...
26
votes
3answers
68k 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 ...
26
votes
3answers
23k 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 ...
26
votes
3answers
45k 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 ...
26
votes
1answer
68k 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
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
8answers
34k 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...
24
votes
2answers
22k 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?
24
votes
3answers
54k views

How can I copy cell values rather than references from one sheet to another in Google Sheets

How can I copy cell values rather than references from one sheet to another? Right now using Filter, Query and ArrayFormula creates references which create empty cells and disallow sorting of the ...
24
votes
4answers
31k 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.
24
votes
2answers
51k 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).
24
votes
8answers
33k 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 ...
22
votes
9answers
70k 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-...
21
votes
5answers
68k 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 ...