Skip to main content
The 2024 Developer Survey results are live! See the results

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
17 votes
5 answers
69k views

How to skip blank cells when copying a range from source to destination by Formula

I have three levels of hierarchical data in Google Spreadsheets that looks something like this: As data is being entered in here, I want it collect in three separate sheets like this: The ...
Moiz Tankiwala's user avatar
17 votes
2 answers
99k 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 ...
Malcolm Lesley's user avatar
17 votes
5 answers
44k 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 ...
Marco's user avatar
  • 287
17 votes
6 answers
93k views

Advanced date logic in Google Spreadsheets

I have the following situation: Today is 4/9/12. I have a cell with a date in the sheet that is 4/13/12. This cell should turn red since it is within 7 days from the current date. What is a ...
Andrew Davis's user avatar
17 votes
4 answers
27k 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 ...
Lernkurve's user avatar
  • 1,860
17 votes
2 answers
17k views

Add all positive values in column

In MS Excel I can add all positive values in a column with SUMIF(A:A,">0") But this isn't working in the Google Docs Spreadsheets. The ">0" part is not parsed and gives an error. Is there ...
Steeven's user avatar
  • 610
17 votes
1 answer
55k 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 ...
Arvind's user avatar
  • 443
17 votes
4 answers
14k views

How to export / import a Google Apps Script as a file into a new spreadsheet?

I fear this may have an obvious answer, but I've been knocking my head over this and can't find it! When creating a new spreadsheet, we can easily create a script within it. I like to call it a macro....
cregox's user avatar
  • 1,383
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 ...
Weslei's user avatar
  • 1,325
16 votes
3 answers
37k 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 ...
Carl's user avatar
  • 161
16 votes
1 answer
124k 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 ...
CamSyl's user avatar
  • 527
16 votes
5 answers
99k views

How to auto increment a field

In Google Sheets I have a column of values that are a combination of the letter S and a series of incrementing numbers. How can I auto increment the sequence of numbers for each row instead of doing ...
iJade's user avatar
  • 261
16 votes
1 answer
38k 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 ...
mbigras's user avatar
  • 521
16 votes
1 answer
68k 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 ...
que syrah sarah's user avatar
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 ...
Benjamin Pollack's user avatar
16 votes
2 answers
34k 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?
B Seven's user avatar
  • 517
16 votes
1 answer
102k 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 ------------------------...
tzippy's user avatar
  • 287
16 votes
2 answers
22k 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 ...
E.Beach's user avatar
  • 263
16 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 ...
Douglas Gaskell's user avatar
16 votes
4 answers
41k 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 ...
engineerchuan's user avatar
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 ...
abbood's user avatar
  • 1,199
15 votes
4 answers
40k 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 ...
Doug Cassidy's user avatar
15 votes
5 answers
40k 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.
pixelearth's user avatar
15 votes
1 answer
32k views

Paste image into a cell

Pasting an image into Google Sheets puts it onto the sheet. I would like to paste the image directly into a cell. You can do this by using the menu Insert > Image> Image In Cell. I would also be ...
Kmaid's user avatar
  • 153
15 votes
2 answers
32k 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 ...
Rubén - Volunteer Moderator -'s user avatar
15 votes
1 answer
21k 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 ...
Timst's user avatar
  • 559
15 votes
6 answers
32k 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 ...
lineplay's user avatar
  • 253
15 votes
3 answers
24k 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).
mobo's user avatar
  • 161
15 votes
6 answers
26k views

Paste format only in Google Spreadsheets with Chrome as the browser

I want a working keyboard shortcut for Google Spreadsheets, that lets me paste just the formatting. Google says the shortcut for copying just the formatting is Cmd+Alt+C, and pasting just the ...
maxedison's user avatar
  • 261
15 votes
3 answers
51k 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 ...
Hashim Aziz's user avatar
15 votes
4 answers
46k 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 ...
tomnexus's user avatar
  • 253
15 votes
2 answers
54k 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?
user2338585's user avatar
15 votes
6 answers
44k 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 ...
Goodbye Stack Exchange's user avatar
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 ...
Mr. B's user avatar
  • 2,887
15 votes
1 answer
104k 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?
francis fernandes's user avatar
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.
abbood's user avatar
  • 1,199
15 votes
2 answers
45k 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 ...
elunicotomas's user avatar
  • 1,793
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 ...
sam's user avatar
  • 6,989
15 votes
2 answers
23k 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 ...
John's user avatar
  • 153
15 votes
2 answers
11k views

Where does the calculation take place?

In Google Sheets I have quite a large number of relatively complex array formulae in many cells that takes ages to complete calculating. Does the speed of calculation depend on my PC resources, or ...
ZygD's user avatar
  • 1,540
15 votes
2 answers
20k 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"...
stephen gilliss's user avatar
14 votes
3 answers
71k 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 ...
RLH's user avatar
  • 297
14 votes
4 answers
23k 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 ---...
Timmy O'Mahony's user avatar
14 votes
2 answers
28k views

How to reverse text on Google Spreadsheets?

When I enter data in a Google Spreadsheets cell, I would like the text to reverse itself. For instance, I would like to put in a cell My text and get it converted to txet yM.
Weslei's user avatar
  • 1,325
14 votes
7 answers
39k 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 ...
user avatar
14 votes
4 answers
46k 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,...
EboMike's user avatar
  • 743
14 votes
3 answers
67k views

In Google Spreadshets, how can you loop through all available sheets not knowing the total count?

I'm keeping stats for my daughter's baseball team. I have a sheet for each game, each one identical in structure. I want to create a summary sheet that totals numbers from the other sheets. How ...
user avatar
14 votes
3 answers
38k 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?
Rebecca Dessonville's user avatar
14 votes
6 answers
68k 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 ...
Trevor Boyd Smith's user avatar
14 votes
3 answers
48k 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 ...
Turgs's user avatar
  • 243