We’re rewarding the question askers & reputations are being recalculated! Read more.

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
26
votes
2answers
81k views

IF OR Statements

How do you do an IF ... OR ... THEN statement in a Google spreadsheet? In PHP I would do this: if($C7 == "U.S.A." || $C7 == "Canada"){ ... How to do the same as a spreadsheet formula? At the ...
26
votes
3answers
65k views

How can I copy cell values rather than references from one sheet to another in Google Sheets

How can I copy cell values rather than references from one sheet to another? Right now using Filter, Query and ArrayFormula creates references which create empty cells and disallow sorting of the ...
26
votes
2answers
65k views

Google Docs date in “dd/MM/yy” format [duplicate]

Is there any way to use date in format dd/MM/yy? The Google Docs support only MM/dd/yy and it cause us problems to use it (we can't sort the date column).
26
votes
8answers
19k views

What limitations does Google Sheets have in relation to Microsoft Excel? [closed]

Office licenses are expensive. What features of Microsoft Office are missing from Google Sheets that would be of value in ordinary personal or business use?
25
votes
5answers
83k views

How to insert the current date into a Google Spreadsheet?

I've got a spreadsheet in Google Drive and one column is meant for the date the entry is made. Is there a button to do this? I'm aware of the functions =NOW() and =TODAY() but their description says "...
23
votes
7answers
31k views

How to export a filtered set of rows from a Google Spreadsheet?

Is it possible, when exporting a Google Spreadsheet into a CSV, to apply this operation only to filtered set of rows? I need to get in CSV exactly the rows which are visible on the sheet after a ...
23
votes
6answers
30k views

Stop a '+' from generating a formula

I'm having a bit of a struggle. I'm creating a spreadsheet which uses plus signs (+) regularly. I want a semi-permanent fix for +'s turning into addition formulae.
23
votes
5answers
22k views

Default sheet in Google Spreadsheet

Is it possible to mark a sheet as "Default" so that if anyone opens a Google Docs spreadsheet, the corresponding sheet is opened instead of the first one?
23
votes
5answers
88k views

How to plot time series graph in Google Sheets?

I know it is possible to plot graph or chart in Google Sheets but the problem with current plotting way is that you can only specify the Y, but not the X, and as such there is no way to plot a time ...
23
votes
4answers
84k views

How to automatically insert a new row and retain functions/formulas from last row?

I have a table with cells that have functions/formulas, like this one: I need a script that creates a new row, copying with it the functions/formulas of the last used row. I find this script which ...
22
votes
4answers
100k views

How to calculate difference in hours between two time fields [duplicate]

I have two time-formatted cells and I need to calculate the difference (e.g., 2.75) in hours between the two. 10:30 AM | 1:15 PM Simply =B1-A1 will not do the trick.
21
votes
6answers
83k views

How can I automatically set “last updated” cell in row Google Docs Spreadsheets?

How can I automatically set "last updated" cell in row Google Docs Spreadsheets ? I want to create a column where the cells' value will be automatically set to when that row was last amended. Is this ...
21
votes
3answers
9k views

Complete reference documentation for Google Spreadsheets formulae?

As a programmer, I find myself somewhat frustrated when trying to use Google Spreadsheets, because there seems to be no definitive description of the syntax that I can use for formulae. For instance, ...
20
votes
4answers
39k views

How can I get GOOGLEFINANCE to return only the historical price of a stock and not an array?

I'm trying to figure out the cost basis of some stock that I sold last year and I can't quite get GOOGLEFINANCE to do what I want. The stock was purchased through a DRIP so there are about five shares ...
20
votes
4answers
34k views

Conditionally format by day of the week

How can I conditionally format cells in Google spreadsheets containing dates to have a different background according to the day of the week, e.g highlight the Saturdays and Sundays in a column ...
20
votes
6answers
62k views

Can I add an autoincrement field to a Google Spreadsheet based on a Google Form?

Is it possible in Google Forms to give a unique value to each row it inserts into the spreadsheet as well as the timestamp?
20
votes
5answers
74k views

How do I mail merge from Google Spreadsheet to a Google Document?

With Microsoft Excel and Microsoft Word, it is easy to merge rows from a spreadsheet into pages in a Word file. This was traditionally used to make paper mailings. How can I do the same with Google ...
19
votes
4answers
28k views

Concatenating only filled cells

I currently have a spreadsheet to keep track of scores in a card game. There can be between two and five players. I have the following: | Players | |----------| | Dave | | Paul | | John ...
19
votes
9answers
79k views

How can I get Google Sheets to auto-update a reference to another sheet?

I am using an external application to enter data into a Google Spreadsheet. This workbook is then referenced by a separate workbook (with the IMPORTRANGE(spreadsheet_key, range_string) function) that ...
19
votes
2answers
74k views

How do I create a box plot chart in Google Spreadsheets?

I want to create a simple box plot from 1D data, which doesn't seem to be supported by the default Insert → Charts. There is a histogram gadget but I haven't seen any box plot gadgets. Do I ...
19
votes
2answers
79k views

Create a Google Sheets chart with multiple data ranges with separate key columns

I have this data: X1 Y1 X2 Y2 1 20 1 25 2 20 3 42 3 20 6 50 5 25 8 50 10 30 10 50 I want to create a ...
19
votes
8answers
39k views

How to update Google “My Maps” when Spreadsheet data changes?

In the process of building a Google My Maps map, I found that it's quite handy to import spreadsheets from your Google Drive to use it as a source for your map layers. As using spreadsheets is pretty ...
19
votes
3answers
33k views

How to save temporary variables in Google Sheets formula?

I'm trying to create a formula in a Google spreadsheet which looks like this: if (x < 0, x + 1, x) It means if x is smaller than 0, return x + 1, else return x. However, x itself is an ...
18
votes
2answers
36k views

How to import JSON data into Google Spreadsheets?

There is IMPORTXML function to import data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds. Is there any similar solution to import data in JSON ...
18
votes
3answers
28k views

Can I be notified if a Google Drive (Sheets or Docs) document is changed?

I have a couple of documents in Google Sheets/Docs that, whenever they are changed by people they are shared with, I'd like to be notified (don't care if the difference of the change is sent, just "...
17
votes
9answers
99k views

How to Fill a Column with Sequential Dates in Google Sheets

In Excel, you can right-click drag a date downwards to fill a column with the next dates in the sequence. For example, if you enter 9/1/2014, select that cell, then right-click drag down over the ...
17
votes
4answers
30k views

How Do I Combine Columns In Google Spreadsheets? [duplicate]

I need to combine multiple columns into a single column, so: one four seven two five eight three six nine Becomes: one two three four five six seven eight nine
17
votes
1answer
100k views

Create a column of date in Google Spreadsheets [duplicate]

Possible Duplicate: How to extend series of dates in Google Docs? In Excel, when you enter a date and hold right click button + shift and move the mouse, it will create dates. Is there a way to ...
17
votes
1answer
23k views

How do I specify “everything except the top row” as a range?

I have Sheet1, which is automatically being updated via form entries. I'm making Sheet2, which is a self-sorting version of Sheet1 (it intended to be a copy of Sheet1 but sorted by a column). Ideally ...
17
votes
5answers
40k views

How do I turn a Google Sheets Duration Result from hours:minutes into just minutes?

All I'm ultimately trying to do is to get a Duration result in Google Sheets to show up in a "total number of minutes" format vs in its default format of hours:minutes. For example, if the Duration ...
17
votes
3answers
45k views

Extract the link text and URL from a hyperlinked cell

Suppose I have a hyperlink in cell A1: =hyperlink("stackexchange.com", "Stack Exchange") Elsewhere in the sheet, I'd like to have formulas that get the link text and URL from A1, separately. I found ...
17
votes
3answers
12k views

Is it possible to have plain text and a link in the same cell?

I've got a document that's convenient to keep in a spreadsheet because of calculations, but I also have a significant amount of text that I'd like to keep in the same place. Some of that text involves ...
16
votes
5answers
176k views

How to include multiple IF statements in one cell in Google Sheets

I am attempting to change the value of a cell in Google Sheets based on the value of an adjacent cell. This adjacent cell gets its value from the colour of the cell adjacent to it using the formula ...
16
votes
2answers
27k views

Format a date fetched from another cell for display using CONCATENATE

Given say 2 different dates in A1 and B1, what I want to do is just show the range of dates as a label in A2 by using =concatenate(A1, " - ", A2) However, A1 is in the default format (MM/DD/YYYY) and ...
16
votes
2answers
19k views

How can I change the input format for dates in Google Docs' spreadsheet?

The input string for dates in Google Docs's spreadsheet app seems to be set to en_US, which is quite unnatural to me. How can I change the input format?
16
votes
3answers
80k views

How do I lookup a value from another sheet in Google Docs?

I have a page which looks like this: ╔═══╦════════╦══════╦═══╗ ║ A ║ B ║ C ║ D ║ ╠═══╬════════╬══════╬═══╣ ║ 1 ║ User A ║ 144 ║ ║ ║ 2 ║ User B ║ 5478 ║ ║ ║ 3 ║ User A ║ 2156 ║ ║ ╚═══╩══...
16
votes
4answers
25k views

Build a cell reference without knowing Row # (number)

This can't be a more basic spreadsheet question but I have always been a SQL kinda guy... How do I build a formula where a value will be pulled from another column on the current row. Can't be the ...
16
votes
6answers
66k views

Google Docs Time report - How to add hours?

I use a Google Docs spreadsheet to keep track of the time I spend on projects. It's formatted in 3 columns: the time I started, the time I finished, and the total hours spent for that session (...
16
votes
1answer
10k 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 ...
16
votes
5answers
28k views

Can I use column headers in a =QUERY?

Reading the documentation for the =QUERY function1, 2, 3, some of it seems to imply that I should be able to use column headers directly in my query. For example, the =QUERY function takes a third ...
16
votes
2answers
23k views

In Google Spreadsheet, finding what formulas reference a given value

I would like to find out which cells have formula dependencies in a large spreadsheet. I am looking for a way to do something like OpenOffice Tools>Detective>Trace dependents and Edit>Find &...
16
votes
10answers
10k views

How to copy from Google Spreadsheet without it editing the source data

Every time I copy and paste from a Google Spreadsheet, it adds a double quote before and after everything, and duplicates every single double quote. For example, if I copy: This is a "string" of ...
16
votes
10answers
26k views

Downloading charts from Google Sheets in high resolution

I have a Google Sheets with data from which I've created a few charts using the built-in charting tool. Now, I'd like to download these charts in a format I can use in a PDF document I create locally (...
15
votes
2answers
21k views

Generating Google Spreadsheet hyperlinks from cell contents

I have a column of cells in a Google Spreadsheet with values such as: 37683 36583 38637 32391 What I would like to do is generate a hyperlink for each cell like: http://mywebsite.com?id=xxxxx ......
15
votes
4answers
5k 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 ...
15
votes
4answers
41k views

Keyboard shortcut to merge cells in Google Sheets

Is there a keyboard shortcut to merge cells in Google Sheets? I've looked and can't find it. F4 doesn't repeat merging cells either. I'm on OSX, Apple keyboard. Anyone been able to do this with a ...
15
votes
2answers
29k views

How do I output a string containing quotation marks?

I am not super computer literate. I am trying to use the equations to grade my Google quiz, the problem is that there is a quote in the answer. Is there a different symbol I need to use? This is what ...
15
votes
4answers
16k views

How do I parse an integer in a Google Sheets?

What is the equivalent of parseInt() (JS) or intval() (PHP) in Google Sheets? I want to compute the sum of the numbers in a row in a separate cell. So if I had 3 cups and 5 cups in a row, I would get ...
15
votes
1answer
15k 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 ...
15
votes
3answers
18k views

How to refer to 'this' cell in a conditional formatting formula?

For a field I have conditional formatting with custom formula: =today()>C8+14 and =today()>C8+30 with different styling, basically I want to have a visual styling to highlight older and oldest ...