Skip to main content

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.

Filter by
Sorted by
Tagged with
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 ...
Cristian Lupascu's user avatar
38 votes
9 answers
272k views

How to Fill a Column with Sequential Dates in Google Sheets

In Excel, you can right-click drag a date downwards to fill a column with the next dates in the sequence. For example, if you enter 9/1/2014, select that cell, then right-click drag down over the ...
stim's user avatar
  • 541
37 votes
5 answers
70k views

Conditionally format by day of the week

How can I conditionally format cells in Google spreadsheets containing dates to have a different background according to the day of the week. For example, if a date falls on Saturday or Sunday, set ...
Mike Ellis's user avatar
37 votes
11 answers
38k views

How to copy from Google Spreadsheet without it editing the source data

Every time I copy and paste from a Google Spreadsheet, it adds a double quote before and after everything, and duplicates every single double quote. For example, if I copy: This is a "string" of ...
speeder's user avatar
  • 501
36 votes
11 answers
53k 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:...
Senseful's user avatar
  • 31.2k
36 votes
2 answers
148k 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. ...
Victor Santos's user avatar
36 votes
5 answers
62k views

Short number format in Google Sheets; 1,024 → 1K, 1,816,724 → 1.8M

Any recipes out there for a "smart" number formatting formula for Google Sheets that's "scale-aware," a la Rails ActionView's distance_of_time_in_words method? I would expect to enter a number like: ...
jm3's user avatar
  • 465
36 votes
13 answers
82k views

Can I use column headers in a =QUERY?

Reading the documentation for the =QUERY function1, 2, 3, some of it seems to imply that I should be able to use column headers directly in my query. For example, the =QUERY function takes a third ...
Vidar S. Ramdal's user avatar
36 votes
16 answers
144k 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...
Andy's user avatar
  • 769
35 votes
2 answers
116k views

Setting up decimal separator, thousand separator and date in Google Spreadsheets

Is there a way to change settings of decimal separator, thousand separator and date format in Google Sheets? I would like to have: dots for decimal, space for thousand date in format YYYY-MM-DD....
Przemyslaw Remin's user avatar
35 votes
3 answers
150k views

Create a Google Sheets chart with multiple data ranges with separate key columns

I have this data: X1 Y1 X2 Y2 1 20 1 25 2 20 3 42 3 20 6 50 5 25 8 50 10 30 10 50 I want to create a ...
EboMike's user avatar
  • 743
35 votes
6 answers
150k views

Is it possible to refer to a whole column in Google Spreadsheets using R1C1 format?

In Google Spreadsheets I often use A1 notation to refer to a whole column: A1:A For reasons I explain below, I'd like to use R1C1 format, to refer to a whole column, something like: R1C1:C1 It's ...
s6mike's user avatar
  • 683
34 votes
5 answers
61k views

Concatenating only filled cells

I currently have a spreadsheet to keep track of scores in a card game. There can be between two and five players. I have the following: | Players | |----------| | Dave | | Paul | | John ...
Djave's user avatar
  • 746
34 votes
2 answers
73k views

Generating Google Spreadsheet hyperlinks from cell contents

I have a column of cells in a Google Spreadsheet with values such as: 37683 36583 38637 32391 What I would like to do is generate a hyperlink for each cell like: http://mywebsite.com?id=xxxxx ......
Mark Ireland's user avatar
34 votes
4 answers
143k views

How to automatically insert a new row and retain functions/formulas from last row?

I have a table with cells that have functions/formulas, like this one: I need a script that creates a new row, copying with it the functions/formulas of the last used row. I find this script which ...
Pythonista's Apprentice's user avatar
34 votes
6 answers
75k views

Can I be notified if a Google Drive (Sheets or Docs) document is changed?

I have a couple of documents in Google Sheets/Docs that, whenever they are changed by people they are shared with, I'd like to be notified (don't care if the difference of the change is sent, just "...
DVK's user avatar
  • 577
33 votes
6 answers
49k views

Default sheet in Google Spreadsheet

Is it possible to mark a sheet as "Default" so that if anyone opens a Google Docs spreadsheet, the corresponding sheet is opened instead of the first one?
Rpj's user avatar
  • 733
32 votes
7 answers
183k views

How to return DISTINCT/UNIQUE list of text via Google QUERY?

Given the list of categories and sub-categories: CatA SubCatA CatA SubCatA CatB SubCatB CatB SubCatB CatC SubCatC CatC SubCatC I'd like to return all unique main categories based ...
kenorb's user avatar
  • 4,462
32 votes
4 answers
19k 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.
Robin Alexander's user avatar
32 votes
4 answers
101k 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 ...
user avatar
32 votes
9 answers
49k 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 ...
Evan Plaice's user avatar
  • 1,459
31 votes
6 answers
107k views

How do I turn a Google Sheets Duration Result from hours:minutes into just minutes?

All I'm ultimately trying to do is to get a Duration result in Google Sheets to show up in a "total number of minutes" format vs in its default format of hours:minutes. For example, if the Duration ...
MinnesotaDad's user avatar
31 votes
2 answers
58k views

Change Google Sheets default date format with other locale

I would like to have the "dd/MM/yyyy" (e.g. 09/03/2017) as default date format for my Google sheets. I know how to do this for one sheet: Format > Number > More formats > More date and time ...
agold's user avatar
  • 447
30 votes
8 answers
103k views

Keyboard shortcut to merge cells in Google Sheets

Is there a keyboard shortcut to merge cells in Google Sheets? I've looked and can't find it. F4 doesn't repeat merging cells either. I'm on OSX, Apple keyboard. Anyone been able to do this with a ...
Bleakley's user avatar
  • 401
30 votes
1 answer
109k views

How do I combine a cell value with a string in a Google Spreadsheets formula?

I have the following IF condition in some cell: =IF(A1>A2, "value is <C5>", "value is <D5>") where <C5> and <D5> should be the values of cells C5 and D5. I know it is a ...
idanshmu's user avatar
  • 403
30 votes
6 answers
230k 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 ...
Gregwah's user avatar
  • 311
29 votes
1 answer
184k 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 ...
sam's user avatar
  • 6,989
29 votes
2 answers
66k views

How do I concatenate a string literal of my cell to a string of another cell?

I am trying to make life easier for myself by organising my teaching materials for private tutees of mine from various subjects/educational levels in google spreadsheets. I want to know how I can ...
hello_there_andy's user avatar
29 votes
13 answers
143k views

How can I get Google Sheets to auto-update a reference to another sheet?

I am using an external application to enter data into a Google Spreadsheet. This workbook is then referenced by a separate workbook (with the IMPORTRANGE(spreadsheet_key, range_string) function) that ...
tlewis3348's user avatar
29 votes
2 answers
98k 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).
user avatar
28 votes
8 answers
83k views

Pasting text with line breaks into Google Sheets populates multiple cells

If I copy text with a line break in it, like this: 1st line 2nd line and then paste in into a Google Sheets cell, then it pastes '2nd line' into the cell below. What are the ways to avoid this?
tog22's user avatar
  • 746
28 votes
5 answers
51k views

How to get the intersection of two sets

I have two lists (unique sets) in a spreadsheet and I want to get the intersection of the two. Is there an easy way to do this? Here's an example of what I want to achieve: List 1 List 2 ...
NoamNelke's user avatar
  • 413
28 votes
3 answers
74k views

How do I output a string containing quotation marks?

I am not super computer literate. I am trying to use the equations to grade my Google quiz, the problem is that there is a quote in the answer. Is there a different symbol I need to use? This is what ...
Aubrey's user avatar
  • 281
27 votes
4 answers
129k views

How to calculate difference in hours between two time fields [duplicate]

I have two time-formatted cells and I need to calculate the difference (e.g., 2.75) in hours between the two. 10:30 AM | 1:15 PM Simply =B1-A1 will not do the trick.
Zlatko's user avatar
  • 387
27 votes
1 answer
47k views

Add a script trigger to Google Sheet that will work in Android mobile app

I have a Google sheet that includes a script that should be run when clicking a button. The recommended way to do this (as far as I can tell) is to insert an image and then attach a script to the ...
Probaton's user avatar
  • 373
26 votes
11 answers
133k 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?
Darius's user avatar
  • 462
26 votes
4 answers
75k views

Filter multiple values using filter by condition tool in Google Spreadsheet

I'm trying to filter a long list of URLs to narrow down which ones I'm interested in. I've got my URL list in Google Spreadsheet and I'm using the filter tool to remove data that I don't want. At ...
sam's user avatar
  • 6,989
26 votes
2 answers
88k 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 ...
Djave's user avatar
  • 746
26 votes
2 answers
41k views

How do I specify "everything except the top row" as a range?

I have Sheet1, which is automatically being updated via form entries. I'm making Sheet2, which is a self-sorting version of Sheet1 (it intended to be a copy of Sheet1 but sorted by a column). Ideally ...
abben's user avatar
  • 369
26 votes
4 answers
52k views

How to refer to 'this' cell in a conditional formatting formula?

For a field I have conditional formatting with custom formula: =today()>C8+14 and =today()>C8+30 with different styling, basically I want to have a visual styling to highlight older and oldest ...
Thomson256's user avatar
26 votes
8 answers
20k 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?
Jeffrey's user avatar
  • 1,128
26 votes
4 answers
24k views

Is it possible to have plain text and a link in the same cell?

I've got a document that's convenient to keep in a spreadsheet because of calculations, but I also have a significant amount of text that I'd like to keep in the same place. Some of that text involves ...
Holland Wilson's user avatar
25 votes
4 answers
39k views

Shortcut key to edit cell in Google Sheets

I'm on a Macbook Pro, working quite a lot in Google Sheets. I've deactivated the F-keys (so it's not an option to use F2). I can't find a shortcut for editing a cell with text in it. If it's an empty ...
Zeth's user avatar
  • 381
24 votes
10 answers
68k views

How to stop NOW function from auto-updating?

I'm using Google Spreadsheet's NOW() function as a shortcut to insert the current timestamp now. However, the value in the cell changes whenever the spreadsheet is modified in any way, even when the ...
Pacerier's user avatar
  • 6,233
24 votes
5 answers
46k views

Build a cell reference without knowing Row # (number)

This can't be a more basic spreadsheet question but I have always been a SQL kinda guy... How do I build a formula where a value will be pulled from another column on the current row. Can't be the ...
justSteve's user avatar
  • 343
24 votes
1 answer
157k views

Create a column of date in Google Spreadsheets [duplicate]

Possible Duplicate: How to extend series of dates in Google Docs? In Excel, when you enter a date and hold right click button + shift and move the mouse, it will create dates. Is there a way to ...
TheBoubou's user avatar
  • 359
24 votes
9 answers
65k views

How to update Google "My Maps" when Spreadsheet data changes?

In the process of building a Google My Maps map, I found that it's quite handy to import spreadsheets from your Google Drive to use it as a source for your map layers. As using spreadsheets is pretty ...
kaiser's user avatar
  • 379
23 votes
4 answers
48k views

How to convert Unix Timestamp to human readable?

As the title says, I have Unix Timestamps on a sheet and I need to convert them to normal human-readable dates. So far I'm striking out, Google searches have turned up a few suggestions but none have ...
JVC's user avatar
  • 1,246
23 votes
4 answers
82k views

How can I import a column from one sheet into another sheet of the same Google Sheets spreadsheet?

I don't look for ImportRange because I want to stay within the same Google Sheets spreadsheet. Instead, I want to import a column from one sheet into a column of another sheet within the same ...
orschiro's user avatar
  • 5,538
23 votes
3 answers
68k views

Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries)

I'm compiling a database of business address info, including key staff for each business. The Google spreadsheet I've inherited for this project includes a column for 'key staff' (in column B), with ...
kirk tab's user avatar
  • 341