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
10
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 / ...
10
votes
3answers
7k views

How do you refresh a filter view in Google Sheets?

Right now it seems like the easiest way to refresh a filter view in google sheets is to remove the filter view and then apply it again. What is the fastest way to refresh a filter view in Google ...
10
votes
4answers
4k 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: ...
10
votes
7answers
20k views

Is there a way to link a “Choose from a list” style question in a Google Form to a list of data (e.g. in a spreadsheet)?

Is there a way to link a "Choose from a list" style question in a form to a list of data (e.g. in a spreadsheet)? This would mean that as new options were added to the list in the spreadsheet (or ...
10
votes
2answers
7k views

Script to change individual text color within a cell

I have a Google spreadsheet with hundreds of employee names for a schedule. Each cell has multiple names within that cell that look like this: First Last First Last First Last First Last First Last ...
9
votes
2answers
20k views

Adding one column to a string to dynamically create a URL in the next column

I have a spreadsheet of product ID codes that I'd like to turn into links, and from everything I've read, the concat or concatenate functions in Google Sheets should work for that, but so far ...
9
votes
8answers
41k views

How to lock/protect cells in Google Spreadsheets

I created very simple spreadsheet application in Google Spreadsheet for my mom. But I really need to lock/protect the cell with formulas so she cannot edit them. Every time I told her to try new ...
9
votes
3answers
12k 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 ---...
9
votes
2answers
31k 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 ...
9
votes
4answers
16k 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 ...
9
votes
3answers
31k 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?
9
votes
3answers
21k 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,...
9
votes
7answers
28k 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 ...
9
votes
7answers
75k 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 ...
9
votes
2answers
69k views

Create a popup in Google Spreadsheet

How can I create a popup in Google Sheets using the script editor? I'd like to show a popup with a personalized notification. It should start like this: function onOpen() { var ss = SpreadsheetApp....
9
votes
3answers
11k 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
9
votes
3answers
33k 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 ...
9
votes
2answers
35k 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 ...
9
votes
2answers
36k 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 =...
9
votes
5answers
30k 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 ...
9
votes
3answers
20k 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?
9
votes
4answers
37k views

How to limit the length of data in a cell in Google Sheets?

Is it possible to put a limit to the length of cell content while entering data into a cell using Google Apps Script or any other way?
9
votes
2answers
13k views

What is the equivalent key in Google Spreadsheet to Excel's F2 for editing a cell?

In Excel When you want to edit the highlighted cell, and you don't want to move back to the previous cell when pressing the <- key, you press F2. Pressing F2 ensures that when you're editing the ...
9
votes
1answer
13k 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 ...
9
votes
5answers
40k views

Count the number of words in a string in Google Sheets

I have a simple Google spreadsheet with a number of text strings. I just want the number of words contained in each cell. Is there an in-built Google spreadsheet function I may use?
9
votes
6answers
17k 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 ...
9
votes
1answer
9k views

How to see which formulas reference a given cell in Google Sheets?

Is there a way to see which cells reference a given cell in their formulas, in Google Sheets? For example, if cell C1 contains =SUM(A1:A5), and cell D1 contains =SUM(A5:A8), I'd like to somehow be ...
9
votes
2answers
10k 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?
9
votes
2answers
10k views

Google Spreadsheet Sort Two Columns

So I've found the following answer in regards to auto-sorting by one column, which works great How can I make some data on a Google Spreadsheet auto-sorting? However, my use case requires the sorting ...
9
votes
2answers
43k views

Inserting a New Sheet in a Google Spreadsheet

How can I insert a new blank sheet (a new tab) into an existing Google Spreadsheet? I'm able to duplicate those that already exist on the bottom, but I can't find the menu option to insert a sheet. ...
9
votes
1answer
54k 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?
9
votes
4answers
22k views

How to automatically refresh a filter view in Google Sheets when new data comes in via Google Forms?

I have a filter view in Google Sheets that does not refresh when new data comes in via Google Forms. To refresh the filter view, I can remove the filter and apply it again, or even just click "OK" in ...
9
votes
1answer
5k 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,...
9
votes
1answer
18k views

Min/Max of a row only if value in other row is X

I'm a bit vague as to what I am looking for/asking about, but here is an example: A | B 22 | 5 15 | 5 2 | 10 19 | 10 5 | 10 =min(A2:A6, if(B2:B6=5, 5)) ==> 15 =min(A2:A6, if(B2:B6=5, 10)) ==&...
9
votes
4answers
14k 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 ...
9
votes
1answer
4k views

How to re-use Google Apps Scripts in new Google Spreadsheet

I'm having an issue. Whenever I create a new spreadsheet, I'm not able to add a script I've already created. The list for recent projects or scripts is always blank. Anyone have a resolution or do I ...
9
votes
4answers
6k views

Is it possible to use named ranges in Google spreadsheet queries so that the columns references are kept up to date?

Using Google Spreadsheets, you can write queries. However, if you have column letters in quotes, then they aren't updated as column order changes. Is there a way to write these queries so they don't ...
9
votes
1answer
25k views

Can I protect the formatting of a Google Sheets?

I’ve got a Google Sheets that has lots of formatting and formulas, in various columns, some columns are formula driven, and some columns that are user input driven. The spreadsheet is shared with ...
9
votes
2answers
14k 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 ...
9
votes
1answer
17k views

Using cell reference with COUNTIFS formula

I am trying to get this formula to work =countifs(J6:J365, "=David & Victor", K6:K365, "=g3") where G3 is a date value (e.g., 02/24/14). I am trying to count the instances in my spreadsheet ...
9
votes
1answer
2k 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 ...
9
votes
1answer
18k 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
25k 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
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
2answers
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 ...
9
votes
1answer
10k views

Export all sheets in a spreadsheet to csv in Google Apps

Is there a way to export all sheets in a google spreadsheet to csv as individual files ?
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
5answers
2k 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 ...
8
votes
4answers
30k 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?

1
3 4
5
6 7
126