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 a demo spreadsheet to your question; this will greatly increase the chance that you get a satisfactory answer.

Filter by
Sorted by
Tagged with
11
votes
6answers
34k views

How can I refer to two different columns in a COUNTIF function?

I'll try my best to explain. Say I've got a sheet that looks like this (in this contrived example, let's say I'm sending marketing emails to various people, I know their ages and zip codes, and I want ...
11
votes
5answers
31k views

How do I remove the '0' from a field with an equation?

I have a Google Spreadsheet that I enter in new values everyday and want to have the equation run down the entire column so I don't have to drag the previous day's box down to copy the info. It would ...
11
votes
3answers
24k 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 ...
11
votes
2answers
20k views

How can I find duplicates in a column, using formulas?

Has anybody come up with a formula to find duplicates in a column? For example: a b c d b d =somefunction(A1:A6) Output: b d I know there is a =unique() function, it's basically a =not_unique() ...
11
votes
4answers
21k views

How to calculate the next Monday after a given date?

I am looking for a formula that can look at a cell value that contains a date and return the first Monday to occur after that date. Examples (MM/dd/yyyy format): 10/22/2013 (Tuesday) would return 10/...
11
votes
2answers
52k views

Basic date manipulation in Google script

I would like to define a custom function in Google Sheet. This function should do something very simple, yet I have not managed to find how to do it. I am very confused with all the answers I read ...
11
votes
2answers
19k views

Second highest value

I know how to use MAX(A1:A30) to get the highest value in the cell range A1 to A30. But how do I get the second or third highest value?
11
votes
8answers
52k views

How to make a query with Google Spreadsheet, that checks for a date

I have a range that goes from B4:H124. All the cells in column B are dates. I would like to retrieve the data from a row (B:H), where the DATE equals to the content of a specific cell, lets say Q4. ...
11
votes
1answer
69k views

Apply conditional formatting if other cell is not empty

2 Columns. C2 | D2 C3 | D3 C4 | D4 C5 | D5 I want it to apply conditional formatting to D2 if C2 has information in it, and D2 is blank. (will be a date). If C2 is empty, then D2 should ...
11
votes
2answers
14k views

AutoFill dates but skip weekends

I need to auto fill the some dates which is easy and I know how to do. However, I do not know how to do this skipping the weekends. I want it to auto fill skipping weekends and jumping to Monday.
11
votes
6answers
45k 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://...
11
votes
2answers
21k views

Sum column only if another column has an empty value

I am trying to get a sum of column values in a Google Spreadsheet only if there is no value in the adjacent column. Consider the following example: A | B ------- 40 | 50 20 | 20 30 | 10 | ...
11
votes
5answers
15k 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 ...
11
votes
3answers
4k views

Does Google Sheets have sheet-level named ranges?

Microsoft Excel allows sheet-level named ranges. See here for an explanation. Google Sheets does have "named ranges" functionality, but I have only figured out how to use it on a workbook-level ...
11
votes
4answers
2k views

Average of last 7 non-empty non-blank cells in Google Sheets

I have numbers in range D7:D and there are times when some cells are empty. I want the average of the last 7 numbers but to skip blanks. So if in the last 7 there's only 3 that are full, I want it to ...
11
votes
5answers
24k views

How do we set up notifications for documents shared to us in Google Sheets?

My friend has shared a file with me on Google Drive and I would like to be notified whenever changes are made to the file (pretty reasonable request I'd say): You can set notifications to find out ...
11
votes
2answers
23k views

Give a name to a cell in Google Sheets

Excel has a little box on the upper left corner that displays the name of the cell (for example A8, C20, BC104, ...) and this name can be changed to a string of choice (like Force, mass, acc, ...) so ...
11
votes
2answers
87k views

How do I merge two columns together in Google Spreadsheets? [duplicate]

I have two columns [first name] [last name] I need to merge them to one column to see [first name last name]
11
votes
1answer
2k 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.
11
votes
2answers
30k 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 ...
11
votes
6answers
23k 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 ...
11
votes
1answer
30k views

How to use ARRAYFORMULA with QUERY in Google Sheets

I have a query that needs to be run for every row in a list. It works nicely when written for a single row: QUERY(MaraRankData, "select J,I,H,G,F,E where A='"&E3&"' and B='"&...
11
votes
1answer
16k 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 ...
11
votes
2answers
17k 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 ...
11
votes
2answers
13k views

Disable Default Word Wrap in Google Spreadsheets

Does anyone know how to make it so that Word Wrap is OFF by default in Google Spreadsheets?
10
votes
4answers
21k 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 ...
10
votes
3answers
81k views

How to format the date in this Google Apps Script

I've come across this rank checking script. I'm not sure exactly where I found it but it's built on Google Docs and a script which automates the rank checking process. I understand a bit of coding ...
10
votes
3answers
54k 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?
10
votes
3answers
33k 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 ...
10
votes
1answer
7k views

Conditional format rule “Text Contains” with a “+” misinterpreted as a formula

I'm trying to conditioan format a bunch of cells that are strings by having cells with a - red and cells with a + green. However when just putting a + o - in the conditional formatting rules it says ...
10
votes
6answers
21k 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 ...
10
votes
2answers
28k 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 ...
10
votes
6answers
28k 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 ...
10
votes
3answers
12k views

How can I stop Google Sheets formatting a Forms response as a date?

I have a Google Forms with a question that contains a drop-down box with the following values: - Fewer than 10 - 11-25 - 26-50 - More than 50 If the user selects 11-25, Google Sheets renders the ...
10
votes
1answer
34k views

Show URL used to edit responses from a Google Form in a Google Spreadsheet by using a script

I'm trying to make the edit URL for a Google Form automatically populate in the response Google Sheet that it's attached to. I've seen this already and want to use it, but I'm having issues trying to ...
10
votes
7answers
10k views

Keyboard shortcut to open a drop-down list for cell values in Google Docs spreadsheets

I'm working in a Google Docs spreadsheet where I've set up validation lists for a column. When I tab into that column as I'm editing a row, the drop-down handle appears: In order to access values ...
10
votes
3answers
12k 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 ...
10
votes
2answers
7k views

Highlight all cells in a range that contain a formula in Google Sheets

I am looking to apply conditional formatting to all the cells in my sheet and highlight cells that contain a formula. I know that I can format cells based on a custom formula, and I know that I can ...
10
votes
3answers
25k 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 ...
10
votes
5answers
12k 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.
10
votes
3answers
14k views

Can you link directly to a new copy of a Google Docs or Sheets?

Is it possible to craft a URL (or bookmarklet) so that when clicked it opens a fresh "copy" of an existing Google Docs or Sheets? Apparently it used to be possible by appending &newcopy=true to ...
10
votes
3answers
16k views

In Google Spreadsheet, how do I make cells take a random string value from a list of strings?

Suppose I have a list of strings: apple, pear, banana, orange How can I have each cell in a column of cells have a random value from that list?
10
votes
1answer
19k views

IF function in Google Spreadsheets displays Parse error, what is wrong?

What is wrong with this code? = IF (F3+E3+D3 > 0, F3+E3+D3, 0) I want the cell to contain the sum only if it is positive. If it is negative I want zero to be there. Google Spreadsheet gives me ...
10
votes
3answers
11k views

Pass range in Google Apps script custom function without using A1 notation

I'm new to Google Apps script and I would like to create a function for a spreadsheet that sums cell values when the cells meet certain criteria, such as the background color. Moreover, I'd like to ...
10
votes
4answers
23k views

Having units in Google Spreadsheets?

I want to be able to input plain numbers like 200, or 3000 and have the cell display 200g, 3kg, etc... But still be able to add the values in the cells. So if I add the two above I'd get 3.2kg on the ...
10
votes
2answers
6k 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 ...
10
votes
3answers
3k views

How to do a simple set operation in Google docs spreadsheet?

Suppose I have some names in cells B1:B3, and a full list of all the names in A1:A4, is it possible for me to generate a list of names in A1:A4 that don't appear in B1:B3? E.g. Cells A1:A4 have "A", "...
10
votes
3answers
17k views

Can you do a UNION SELECT in a Google Sheet?

I've got a Google Sheet linked to a form which collects replies from three different people on the same subject using the same form and editing three times. This creates a sheet which contains three ...
10
votes
4answers
5k views

“Split” delimited string, preserving blank entries

I am trying to use the SPLIT and JOIN functions in Google Sheets (the new version) to manipulate some data, and I am running into problems because SPLIT is not preserving blank entries. The data ...
10
votes
4answers
7k views

Is there a way to insert a bookmark into a Google Spreadsheet?

I know you can do this with a Google Doc, but how about a Google Spreadsheet? I'd like to have a bookmark and then link into that cell of the spreadsheet from an HTML document.