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
17
votes
1
answer
51k
views
In Google Sheets how do I duplicate a sheet along with its permission
In a Google Spreadsheet called Attendance there is a sheet called Template. The user duplicates this sheet, renames the sheet with current date and uses this sheet to mark attendance for students.
The ...
- 433
17
votes
4
answers
9k
views
How to add a line break in a custom date format in Google Spreadsheets?
Google Spreadsheets give us a tool to create and use a customized data format for date values.
While the tool allows us to use arbitrary characters, like slashes and commas between the date fields (...
- 769
17
votes
4
answers
13k
views
How can I change Google Spreadsheets to have ISO 8601 dates as default?
I would love to set my default date format to be ISO 8601 (relevant xkcd) for all of my Google Spreadsheets. I'm well aware that I can manually change the date format for a given spreadsheet by:
...
- 903
17
votes
3
answers
8k
views
How do I decimal-align numbers in Google Sheets
In Google Sheets, I want to decimal-align numbers without adding trailing zeros in the fractional part.
Instead of: I want:
+------+ +------+
| 56.0| | 56 |
| 27.0| | 27 |
| 83.0| ...
- 3,122
17
votes
1
answer
101k
views
Sum data based on cell color in Google Spreadsheets
I have seen this question and altough I face the same problem, the answer to that question is not helpful to my case.
I have large spreadsheets which are changed frequently. So, according to the ...
- 1,315
16
votes
3
answers
35k
views
Google Sheets Increase month by 1 but leave everything else the same
I am using Google Sheets and have 700+ rows that have an assortment of dates but the month is the same on all of them (monthly billing cycle)
each month I am having to manually go through each line ...
- 161
16
votes
2
answers
95k
views
Using FILTER or IMPORTRANGE to bring in and filter data from another spreadsheet
I want to bring in data from a separate sheet and filter it.
I can get IMPORTRANGE to work like this:
=IMPORTRANGE("URL","A:J)
But that doesn't filter. I can only get FILTER to work in the same ...
- 315
16
votes
5
answers
42k
views
How to make Google Sheets jump to today's row when opened?
I have a sheet that has many rows to put data. Since there are too many rows, I want to have the cursor jump to a particular row when the sheet is opened.
In other words: The date column has the ...
- 277
16
votes
5
answers
92k
views
How to auto increment a field
In my spreadsheet I have a column called S+ No. How to auto increment this for each row, like:
S1,S2,S3,S4
In short, how to make it dynamic?
- 261
16
votes
4
answers
25k
views
How to add a note or comment into a formula in Google Sheets?
Microsoft Excel
Microsoft Excel has this feature where one can add a note into a formula with the N() function. It looks like this:
Source: Add Comments to a Formula in Excel for Your Future ...
- 1,730
16
votes
4
answers
6k
views
Is there any way to script a Google Docs spreadsheet?
I have a really complex spreadsheet in Google Docs, and while it works, having the ability to script it a bit (à la VBA in Microsoft Office) would make it a lot cleaner. I heard that this is doable in ...
- 1,976
16
votes
1
answer
100k
views
Find cell with highest value of a cell range
I have a row of percent values. In the last of the row (Column E) I want the text to appear, that is in the very first row above the values:
A B C D E
------------------------...
- 287
16
votes
2
answers
20k
views
Refer to Sheet with value in Cell?
The usual way to reference a sheet is here but how do you reference a sheet when the sheet's name is a value in a cell?
For example,
A1 = "Sheet number two"
B1 = A1&"!B4"
Here I want B1 to ...
- 263
16
votes
4
answers
37k
views
Can you specify qualifier and delimiter characters when exporting from Google Spreadsheet?
Can you specify qualifier and delimiter characters when exporting from Google Docs?
Right now, when I go to a spreadsheet and do File » Download As, it downloads something that is comma delimited and ...
- 341
16
votes
2
answers
6k
views
Is there such a thing as a "git blame" for Google sheets
I know that one can easily see the revision history of a Google Sheet. But then that's the same as running git log.. It shows shows a chronological order of changes to the file.
I'm actually ...
- 1,179
15
votes
4
answers
45k
views
Is there a way to generate number of days in a month?
The title kind of says it all but just to give you the use case, I have a spreadsheet where I track monthly advertisement revenue and want to end up with a daily revenue column.
Here's a simplified ...
15
votes
1
answer
122k
views
Google Sheets Query Get Row When Any of These Cells are Not Empty
So like my title states I would like to select via a query only the rows where certain cells are not blank/empty.
I have 4 pools that I have a form for collecting their chemistry. In separate sheets ...
- 517
15
votes
5
answers
37k
views
Is there a shortcut key for changing Google Sheets text color to the "last color"?
I often find myself changing text colors to the same color for five minutes at a time which is really unnecessarily mouse-intensive.
- 271
15
votes
2
answers
29k
views
How to know if Google Sheets IMPORTDATA, IMPORTFEED, IMPORTHTML or IMPORTXML functions are able to get data from a resource hosted on a website?
Google Sheets has several import functions
IMPORTDATA
IMPORTFEED
IMPORTHTML
IMPORTXML
Sometimes the above functions returns errors like #N/A Imported content is empty and would like to be sure that ...
- 44.6k
15
votes
1
answer
19k
views
Select subrange of an array-returning formula like GOOGLEFINANCE()
In Google Sheets, GOOGLEFINANCE returns an array of values, like so:
The formula is entered in A1 but the result extends into other rows and columns. I would like to "select" (not sure what the exact ...
- 549
15
votes
1
answer
62k
views
Diference between onEdit or onChange trigger? [closed]
From what I've gathered online, onEdit only works when you edit a cell (not when a row is added or when a note/comment is added) and onChange will capture that a change has occurred and trigger when ...
- 489
15
votes
1
answer
35k
views
Treat \n as a newline character for multiline formulas
Given some sheet at cell A1 if I write ="foo\nbar" then foo\nbar will be rendered as shown:
If I manually add a newline using the shortcut cmd + opt + enter then the expected output as ...
- 491
15
votes
6
answers
30k
views
Change date format [YYYYMMDD] to [MM/DD/YYYY]
I generated a report that formats date as 20160509 and my Google Sheets locale is already set to United States. I've played around with various options within Format > Number > Date Format but I can't ...
- 253
15
votes
4
answers
41k
views
How to find URL of the current sheet
In Google Sheets, how do I create a hyperlink to this sheet?
I have a sheet which I'm about to download as PDF for archive. It would be useful to include a link in the sheet, pointing to itself, so ...
- 253
15
votes
2
answers
53k
views
Importing only certain rows between spreadsheet documents
Document 1 has many rows of data.
How do I import into document 2 only those rows that have a certain value in column C?
- 151
15
votes
3
answers
22k
views
Can I set the number format to show exactly 2 decimals only when needed in Google Spreadsheet?
I want to set the number format as below:
If the number have more than 2 decimals, round it to 2 only (e.g. 12.3456 -> 12.35).
If the number is whole number, just let it be (e.g. 12 -> 12).
- 161
15
votes
4
answers
27k
views
Compare two lists and display missing items in a Google Spreadsheet
I'm trying to find a solution to compare two separate lists (invited - attended) and create a 3rd list (absent).
The function must look at the key in "Attended List (D,E,F)" and compare it with the ...
- 2,857
15
votes
6
answers
42k
views
Is it possible to hide row numbers in a Google Sheets?
When looking at a numbered list that starts at the number 1, the row numbers are potentially confusing. (Assuming one has row 1 as column heading labels, item 1 will be on row 2, item 2 on row 3, and ...
- 1,405
15
votes
1
answer
99k
views
Compare date value to current date in Google Sheets
How can I compare the date in a row to a current date, so that if it's greater than today the next steps in my process need to be executed?
15
votes
2
answers
4k
views
Keyboard shortcut for jumping to Google Sheets formula bar
I couldn't find this shortcut in Sheets support. Basically I want a keyboard shortcut that makes me jump to the formula bar.
- 1,179
15
votes
2
answers
51k
views
Conditional formatting with multiple criteria for a range of cells
I'm trying to use conditional formatting to highlight a cell if it's value is not empty, AND if it's value is not equal to -------.
I've tried every normal formula I use to check if a cell is empty ...
- 1,209
15
votes
2
answers
42k
views
Format cells as plain text [duplicate]
I'm gathering some data in a Google Spreadsheet, there's a column for phone numbers and when trying to enter a phone number (e.g., +54 11 4785 7992) the spreadsheet automatically tries to make a ...
- 1,783
15
votes
1
answer
26k
views
Is it possible to delete the revision history in a Google Spreadsheet or Doc?
Is it possible to delete the revision history in a Google Spreadsheet / Doc?
I've added some information into a shared Google Doc that I own that I don't want in there, I've deleted the data, but ...
- 6,823
15
votes
2
answers
22k
views
Using query to find the entries in one column which are not in the other
I am using Google Sheets to track changes in a group's roster. Cells B2:B240 contain the old list of names, and C2:C240 contains the new list. Both lists are alphabetized. There is a lot of overlap ...
- 153
15
votes
2
answers
19k
views
Sum of all cells in Google Spreadsheet where matching row fits a value
I want to return the sum of all cells in ColumnA only if the value of a cell on the same row is equal to a specific value.
For example, sum C4 if D4 = "Bob". Assuming there are six rows whereby "Bob"...
- 159
14
votes
4
answers
39k
views
Lock the formula of a cell, even if the cells it references change
I have this, in D1:
=sum(D3:D)
I want D1 to always be the sum of the rest of column D (D2 is a header)
I sometimes move a row, from eg 8 to 3, and that causes D1 to change to sum(d4:d).
I want ...
- 245
14
votes
3
answers
67k
views
How can I create a unique list of data across multiple rows AND columns?
Google Sheets has a UNIQUE() function which when given a column of values, will output a column of all unique values.
This works well for column-specific data, however, you have multiple columns and ...
- 297
14
votes
4
answers
22k
views
How do I prepend a string to a column of cells in Google Spreadsheets?
I imagine this is laughably simple but I just don't know what to do. I have a column (A) of cells with strings in them and I want to simply prepend each string in every cell with a web address:
A
---...
- 243
14
votes
7
answers
38k
views
Summing up spreadsheet data when a column contains “#N/A”
I am using Goggle Spreadsheet to work up some historical stock data and I use a Google function (=googlefinance=…) to import the historical closing prices for a stock, then I work with that data ...
Doris
14
votes
4
answers
43k
views
How to interpolate data in a range in Google Sheets
I have an array with data:
X Y
3 50
5 60
9 120
11 130
18 90
20 150
The data is entirely non-linear. X is guaranteed to be sorted.
Now for any given value,...
- 733
14
votes
6
answers
63k
views
How can I add N more columns?
As of this writing, the default number of columns in Google Sheets is 29 columns.
I need more than 29 columns.
I would like to add N columns where N is something like 10 to 30 columns.
My request ...
14
votes
3
answers
35k
views
How can I alphabetize a unique result in Google Spreadsheets?
I'm generating a list based on using the UNIQUE function.
How can I get the output to be alphabetized automatically?
- 8,037
14
votes
3
answers
46k
views
Get column number by referencing column title
In Google Sheets I have a number of columns that I have given titles to in Row 1. Using a formula, I want to return the column number (i.e. 5, 6, 7, ...) for the column that has a specific title. For ...
- 243
14
votes
1
answer
43k
views
Formula result doesn't show, leaves blank cell
I have a spreadsheet and between B2:B101, I have a number on each one. I am trying to get a rank on E2:E101, regarding to their given number. I tried using =rank, and it successfully shows the result ...
- 241
14
votes
3
answers
51k
views
How do I eliminate extra line breaks in a Google Spreadsheets cell?
I have a spreadsheet in which some cells have multiple carriage returns. I would like to eliminate all the extras, so that four carriage returns shrinks down to just one.
This question is similar to ...
- 251
14
votes
2
answers
11k
views
Sum on condition?
I have set up a form with Google Spreadsheet where I want to collect grocery expenses for different people. How can I sum the expenses for different persons? Each row has a column with the person's ...
- 243
14
votes
3
answers
48k
views
Is it possible to freeze multiple separate rows in Google Sheets?
Currently, Google Sheets can be used to "freeze" sections of a document so that those sections always persist when scrolling, as demonstrated in this video. However, a limitation of this is that only ...
- 476
14
votes
3
answers
40k
views
How can I create a formula to generate a range in Google Sheets?
ARRAYFORMULA({1,2,3})
Generates three cells with the values 1, 2, 3 in them.
A bit more complex:
ARRAYFORMULA(VLOOKUP(A2,Sheet1!A:F,{1,2,3,4},false))
Uses columns 1..4 from the vlookup and fills ...
- 243
14
votes
6
answers
80k
views
Insert image from Google Drive into Google Sheets
How do I insert/link to images existing in a folder in Google Drive into a Google Sheets spreadsheet?
There is the image() function, I imagine using it something like this:
image("googledrive://...
- 431
14
votes
2
answers
30k
views
How to left align currency symbol but right align number?
How to left align dollar sign and right align the number, like Excel currency format?
For example:
$ 3,546
$ 44,345
Also, is there a way to create a custom format?
- 487