Questions tagged [google-sheets]

For questions about the Google's web application to create and edit spreadsheets on a web browser. 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 chances to get a satisfactory answer.

Filter by
Sorted by
Tagged with
9
votes
1answer
16k views

Google Sheets auto-format the highest value in a row of cells

I have a set of data like so: A|B|C|D 1 9|3|5|4 2 7|6|6|6 3 3|6|2|7 4 6|9|4|7 And I would like to auto-format the highest value in each row. So, for instance in row 1, A1 would get the auto-format,...
9
votes
8answers
24k views

Function to find distance between cities

I'm working with Google Docs and want to create a spreadsheet of the following form: Point A Point B Mileage Los Angeles Miami 292100 Palo Alto San Francisco ...
9
votes
1answer
12k views

Google Docs Chrome extension - what does it do / why does it exist?

In some Chrome instances, several extensions (apps?) called Google Docs, Google Sheets, etc. are installed (I've seen various subsets of these across different Chrome instances). What are these for / ...
9
votes
2answers
2k views

Analyse frequencies of date ranges

I have a Google Spreadsheets where I would like to compute occurrences of date ranges. I have a column date_utc+1 which contains almost random date data. What I would like to do is put the date ...
9
votes
1answer
9k views

Cross-reference sheets on Google Spreadsheets

I can just do B12 to reference a field on the same sheet I'm working on, but how do I reference B12 on another sheet?
9
votes
5answers
9k views

How to indent spreadsheet cells in Google Sheets?

It's a fundamental feature that I need in my financial work but I can't seem to get this working in Google Sheets, or it doesn't support it. I've also looked into Zoho Sheet, but they also don't ...
8
votes
3answers
28k views

Google Sheets Function to get a shortened URL (from Bit.ly or goo.gl etc.)

I have a Google spreadsheet that builds Google Analytics links. I'd like to shorten those links. I tried the Bit.ly example but even their example does not work. How can I do this?
8
votes
2answers
23k views

CONCATENATE a string with an ArrayFormula

I have the following string in column H and I. H2="http://" I2="aaa" H3="http://" I3="bbb" I want to have a auto-generated J column, which results like: J2="http://aaa" J3="http://bbb" So I put ...
8
votes
3answers
48k 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?
8
votes
4answers
20k 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 ...
8
votes
7answers
61k views

Adding a header and footer to a Google Spreadsheet

I'm in the process of changing to Google Drive from MS Office for all aspects of my work. Pretty much everything I need is there, but the one thing we can't work out how to do is how to get a header ...
8
votes
2answers
19k views

Pull CSV data from URL to Google Spreadsheet

I have a site which allows authenticated users (read: in-house users) to download certain data from the site in CSV format, e.g. http://example.com/activities.csv . Can I create a Google Spreadsheet ...
8
votes
1answer
6k 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 ...
8
votes
3answers
61k views

How to format the date in this Google Docs 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 ...
8
votes
3answers
17k views

How do I mix formats in one cell?

I've got a string like ="Daily Avg: " &AVERAGE(C3:C13) I'd like to be able to apply number formats within my cell's formula. Is this possible without custom scripts? I've searched Google's ...
8
votes
3answers
19k views

Are Excel formulas and Google Spreadsheet formulas the same?

I want to be sure all my Google Spreadsheet work can be converted OK to Excel. How many of the Excel formulas match the formulas of Google spreadsheets?
8
votes
3answers
30k 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 ...
8
votes
1answer
51k 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 ...
8
votes
3answers
8k views

Which formula extracts the domain name from an email address (bob@example.com -> example.com)?

Which Google Spreadsheets formula do I use to get the domain part from an email address? Example: bob@example.com → example.com
8
votes
3answers
27k views

2-axis line chart in Google Sheets

I've set up a Google Sheets to compare one set of statistics against another. The statistics are for traffic vs. an action. The actions only happen once a day and not every day whereas the traffic has ...
8
votes
2answers
29k views

How can I apply a script to only one specific sheet within a spreadsheet?

I have a spreadsheet with two sheets called Robin and Lucy. I've made/found/mangled a script to sort the data on the sheet each time I add some data to column A: function onEdit(event){ var sheet =...
8
votes
3answers
11k views

A spreadsheet formula to assign letter grades based on numeric ranges

I'm trying to figure out how to write an IF statement that will be 'true' if a value is in-between two values. Example: If value is between 60 and 69.9 return "D" If value is between 70 and 79.9 ...
8
votes
5answers
4k views

Split a column of strings with a single formula returning an array

I have a column of strings like this: +---------+ | a bb | | ccc d | | ee ffff | +---------+ (exactly one space in each string), which I'd like to split by the space into two columns: +-----+-...
8
votes
3answers
14k views

How to sort a Google Sheets Query by a custom ordering

I have a table of choir members with a “name” and a “voice type” column. How can I write a query that orders them by decreasing pitch level of the voice type (i.e. soprano > contralto > tenor > bass)? ...
8
votes
2answers
11k views

Google Sheets says “spreadsheet too large” when it isn’t

I have an XLSX spreadsheet. It has 68251 rows and 94637 cells, and it's on-disk size is 1510842 bytes. When I try to upload it, Google claims that it exceeds the 400,000 cell limit. The cell count ...
8
votes
4answers
33k views

How to sum ​​values ​​in a range of dates

I have a Google Spreadsheet in the format below — here's an example. Column A → Dates Column B → Numbers I'd like to sum values ​​in column B in a range of dates in column A. Example: Sum ...
8
votes
1answer
11k views

How to return only the first row containing the value you are searching for in a query?

=QUERY('F/O DEFVSREC'!$1:$1000, "select C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V where B='"&A35&"'label C'', D'', E'', F'', G'', H'', I'', J'', K'', L'', M'', N'', O'', P'', ...
8
votes
1answer
4k views

How to move a standalone chart back to an existing sheet?

I regret moving my chart to a new spreadsheet in Google Sheets. How do I move it back? Using the revision history to undo the edit is not an option, since it has been a while since the original move,...
8
votes
2answers
18k 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 ...
8
votes
4answers
21k views

Prevent the cell from expanding to the size of cell contents in a Google Docs spreadhseet

How can Google Docs Spreadsheet be prevented from expanding to the size of the cell contents? Example: The cell expands vertically to fit the cell contents: The cell expands horizontally to fit the ...
8
votes
2answers
35k views

How to Refresh a cell in Google Spreadsheet?

I have a column in Google Spreadsheet that calls an outside webservice using URLFetch in a Google Script. The cell uses two parameters in that cell's row. After a change occurs where those two ...
8
votes
2answers
9k views

Deleting all comments in Google Sheets at the same time

There are over 200 comments in our shared Google Sheet files and we need to clean all the comments faster. Deleting the comments one by one by clicking Resolve is a waste of time. Is there another way ...
8
votes
1answer
5k views

Retrieve a cell having a column and a row header in Google Spreadsheets

I have a spreadsheet in Google Docs like this: Johan Mark Gil Pablo Course 1 45 50 80 50 Course 2 250 300 50 270 Course 3 19 50 ...
8
votes
2answers
19k views

Ignore formula if cell is blank

I'm attempting to create a formula that essentially counts the days between today's date and the date entered in a cell. So far I believe it works. See below: =DATEDIF(H3, Today(), "D") My dates ...
8
votes
1answer
1k views

Why does Google Spreadsheets says Zero is not equals Zero?

After spending hours trying to figure out an error in my personal accounting spreadsheet, I could pin it up to one formula. While this results in Zero =((0,6-0,3-0,4)+(-0,3+0,8-0,4)) comparing it ...
8
votes
2answers
8k views

Styling Text Within a Cell in Google Spreadsheets

In Excel it is possible to style text within a cell, by pressing Crtl+1. You can create for example the following sequence: 1,2,3,4. Is that possible in Google Spreadsheets?
8
votes
6answers
4k views

How can I get Google Sheets to treat my form-submitted strings as plain text and preserve leading zeros?

I have Google Forms where users enter information, some of which contains leading zeros (e.g. zip codes, ID numbers). However, when this data is saved to the Google Sheet, it doesn't treat it as ...
8
votes
4answers
3k views

How can I change Google Spreadsheets to have iso 8601 dates as default?

I would love to set my default date-setting 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: ...
8
votes
4answers
5k 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.
8
votes
2answers
16k views

SUMIF date range and filter by value on Google Spreadsheet

How do I SUMIF two date ranges and filter with a value? The date range formula I've been using is: =sumif($A:$A,"<="&D$5,$C:$C)-sumif($A:$A,"<"&D$4,$C:$C) Which works great for ...
8
votes
1answer
2k views

URL to create a new Google document from a template

Can you use a URL to create a new Google Document/Spreadsheet/Presentation from a template? Related questions (but don't answer this specific problem): URL to create a new Google document Craft URL ...
8
votes
3answers
10k views

How does one filter pivot tables by date range in Google Spreadsheets?

I have a Google spreadsheet that keeps track of time spent on graphic design projects. I am trying to run pivot table reports to understand where we can optimize projects. In these pivot tables I ...
8
votes
1answer
984 views

Can I jump to next cell containing invalid data in Google Sheets?

I have a Google sheet containing lots of data, and some of it doesn't respect the data validation rules (and the little red triangle appears). Since I have a lot of data, it's quite a long process to ...
8
votes
6answers
14k views

What are the refresh characteristics of the Google Spreadsheet Import functions?

The imported data is not always up to date with the source pages when I load the spreadsheet. When this happens, F5 refresh does not help. Nor does Ctl-F5. Apparently there is some kind of caching ...
7
votes
3answers
23k views

Does text overflow a Google Spreadsheet cell if the cell next to it is empty?

In Microsoft Office Excel a long text string is readable over multiple columns whenever the next (right) column is empty. Is that also possible within Google Spreadsheet?
7
votes
4answers
15k 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 ...
7
votes
5answers
1k views

How do I cancel editing a cell, in Google Spreadsheets?

I sometimes double-click on a cell to see the formula or whatever, or I just accidentally start typing in the cell. How do I cancel the edits? Esc and Ctrl+Z don't work, so I typically hit Enter and ...
7
votes
2answers
19k 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 ...
7
votes
3answers
26k 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 ...
7
votes
6answers
16k 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 ...