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,719
questions
37
votes
5
answers
66k
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 ...
- 481
37
votes
3
answers
79k
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
36
votes
9
answers
256k
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 ...
- 521
36
votes
11
answers
52k
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:...
- 30.7k
36
votes
2
answers
146k
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.
...
- 563
36
votes
5
answers
59k
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: ...
- 465
36
votes
16
answers
140k
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...
- 769
35
votes
12
answers
36k
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 ...
- 480
34
votes
5
answers
59k
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 ...
- 746
34
votes
2
answers
105k
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....
- 442
34
votes
4
answers
142k
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 ...
34
votes
12
answers
77k
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 ...
- 17.1k
34
votes
3
answers
148k
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 ...
- 733
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 "...
- 577
33
votes
2
answers
68k
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
......
- 511
33
votes
6
answers
48k
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?
- 733
32
votes
7
answers
170k
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 ...
- 4,282
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.
- 481
32
votes
4
answers
100k
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 ...
Jaytee
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 ...
- 1,461
32
votes
6
answers
139k
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 ...
- 653
31
votes
6
answers
102k
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 ...
- 319
30
votes
6
answers
229k
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 ...
- 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 ...
- 6,823
29
votes
2
answers
60k
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 ...
- 411
29
votes
8
answers
96k
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 ...
- 391
29
votes
1
answer
101k
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 ...
- 393
29
votes
2
answers
97k
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).
user9646
29
votes
2
answers
53k
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 ...
- 427
28
votes
3
answers
70k
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 ...
- 281
28
votes
13
answers
142k
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 ...
- 442
27
votes
4
answers
127k
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.
- 387
27
votes
5
answers
47k
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 ...
- 403
27
votes
1
answer
46k
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 ...
- 373
27
votes
3
answers
49k
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 ...
- 371
26
votes
11
answers
132k
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?
- 462
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 ...
- 746
26
votes
4
answers
67k
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 ...
- 6,823
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?
- 1,128
26
votes
4
answers
23k
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 ...
- 363
25
votes
8
answers
78k
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?
- 706
25
votes
2
answers
37k
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 ...
- 359
24
votes
9
answers
65k
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 ...
- 6,043
24
votes
1
answer
152k
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 ...
- 359
24
votes
9
answers
64k
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 ...
- 369
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 ...
- 1,202
23
votes
4
answers
35k
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 ...
- 351
23
votes
5
answers
45k
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 ...
- 333
23
votes
3
answers
9k
views
Complete reference documentation for Google Spreadsheets formulae?
As a programmer, I find myself somewhat frustrated when trying to use Google Spreadsheets, because there seems to be no definitive description of the syntax that I can use for formulae. For instance, ...
- 527
22
votes
4
answers
78k
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 ...
- 5,468