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
3
votes
1answer
2k views

How can we view the whole spreadsheet instead of just the form in Google forms?

I have been given a link to a Google docs form where I was supposed to enter some data. The URI looks something like this: https://docs.google.com/spreadsheet/formResponse?formkey=foo Is there ...
1
vote
1answer
9k views

Google Docs Spreadsheet - Parse Error

Not sure why this formula does not work. I just want the value to be zero if 60-D15 is negative =MAX(60-D15,0) also tried =MAX(D15,0) =MAXA(D15,0) Any clues?
40
votes
4answers
34k views

Cumulative Sum without Script

For this question, I've created a Google Apps Script function, that calculates the cumulative sum of a range. Martin Hawksey on Google+ made a comment about a more efficient way of calculating the ...
1
vote
1answer
267 views

Transpose an address list to rows

I have a good old list with addresses: Because I want to re-arrange the list (and make it more 2013), I need to transpose the list like this: I've tried to use the TRANSPOSE formula, but that doesn'...
8
votes
2answers
10k 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?
-1
votes
1answer
336 views

Filter function overwrites formula with '--'

I am using the filter formula below. The formula works fine the first time, but once it filters the result properly, it corrupts the same formula in next row below with --. =iferror(FILTER(...
6
votes
2answers
3k views

Count Occurrences of Words

I'm having the following set of data: A simple COUNTA would yield n=4. Using the above-mentioned formula will give also n=4. The answer needs to be however n=6: Jacob Jan (1x), Jacob (2x), Jan (3x) ...
1
vote
1answer
2k views

Selecting a sequence of sheets in Google Docs / Spreadsheets

I have a Google spreadsheet where each sheet is a student group, say, Group1, Group2, Group3 etc. Each sheet is organized identically. I would like to summarize some information from all sheets into ...
-1
votes
1answer
1k views

In Google Sheets, how can I fit the data to the cell? [closed]

In some cells on my sheet the data fits the cell. On some rows there is a lot of extra space after the data in each cell. I have tried deleting after at the end of the data, but it does not help. ...
1
vote
4answers
3k views

How can I convert a number according to a range?

I'm working with Google Apps (spreadsheet) and I need to convert results (ranging from 0 to 36) to a maximum of 7 according to the following table. if score is (0-5) result should be 1 if score ...
0
votes
3answers
3k views

Editing of a VLOOKUP formula that references a pivot table

After inserting a VLOOKUP formula in a Google spreadsheet, where the array parameter references columns of a pivot table, how do I edit the formula again? When I enter the formula, it just displays ...
4
votes
2answers
5k views

How to add a custom font size to Google Spreadsheets dropdown menu?

How do I add a custom font size for a Google Spreadsheet dropdown menu default values? There are some defaults like 14pt, 18pt, 24pt and 36pt, but I would like to have a font size like 30pt, is it ...
4
votes
2answers
6k views

Which of the concatenation options are the best to use?

I want to accomplish the following: string = TEST; cell reference = B2, value = ING; RESULT = TESTING Which of the following options are the best to use?: CONCAT: =CONCAT("TEST",B2) & ...
43
votes
3answers
101k views

Auto-updating column in Google Spreadsheet showing last modify date

Trying to figure out a way to have column two auto-update to show a timestamp of the last update. I realize the doc shows a timestamp of the last modification, but we have a lot of clients accessing ...
3
votes
2answers
147 views

Referencing a Range Name from a different spreadsheet [duplicate]

I would like to create a spreadsheet that contains name, phone number, address and email address of many individuals. I will define the data as a range named "Index". Then I would like to create a ...
3
votes
3answers
4k views

How to apply a function to all the rows preceeding the current one in an arrayformula?

So I have the following situation: In the first column I have certain values, in the second column, given a certain row, I want it to contain the result of a function applied to all the preceeding ...
1
vote
2answers
98 views

Formula to return time difference but not display 0

I have a Google Sheets with start (C) and end (D) timestamps. I am calculating the time difference using =(D1-C1). I have copied the formula to empty columns which returns a 0 time difference. Is ...
5
votes
2answers
4k views

Google Spreadsheet Comments

I have a Google Spreadsheet with several boxes with the little orange triangle indicating a comment associated with that cell. Unfortunately, I haven't made any comments, so cannot delete these boxes....
7
votes
3answers
4k views

Import CSV, show formatted hyperlinks, =Hyperlink(..,..)

Based on a csv import, I would like to show formatted hyperlinks. There is a formula =HYPERLINK( "www.google.com" ; "Search Google" ) that does this, but is there any way to include this in a csv ...
4
votes
1answer
2k views

Save data from Google Spreadsheet history

I used a Google form and inadvertently deleted a few rows. I need to use the rows again. I see them with history but I can't save them and I can't print them. How can I restore the data?
8
votes
1answer
485 views

Non-deterministic parameters to custom function

I have a problem with Google Spreadsheets: I create a custom function using the Script Editor and it only runs OK if the parameters I pass are known (constant) values. If the parameters are non-...
3
votes
2answers
10k views

Is is possible to have part of a google doc hidden publicly but able to be edited and “pushed” to LIVE?

So I have a public schedule for my business, but need to edit (which sometimes takes a few days) and then make "public"... Can I do that? I know I can "hide" but when I reopen, it's live and everyone ...
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 ...
5
votes
2answers
8k views

Compute number of occurrences in a column of a spreadsheet

I have a Google Spreadsheet with a single column that holds string values (Twitter screen names) such as "user1", "user1", "UserX", and I would like to count those values so that I can easily craft a ...
2
votes
1answer
8k views

Convert a date in Google Spreadsheet?

I have a Google Spreadsheet with a date row in UTC time, such as 12/6/2012 11:14:32 I want GMT+2 local time, so how can I convert the date in Google Drive? And, if not possible, is there a way to do ...
1
vote
4answers
3k views

Counting unique rows in spreadsheets

How do you run the COUNTUNIQUE function on several columns? Running COUNTUNIQUE(A1:C3) gives me 8, because there are 8 unique values, I want it to return 3 because I have 3 unique rows. | A | B | ...
4
votes
6answers
8k views

Sum until empty line function in Google Spreadsheet

I'm using this data layout a lot: A B C 1 Name1 Count Total 2 A 1 =SUM(B2:B4) 3 B 2 4 C 1 5 6 Name2 Count Total 7 D 1 =SUM(B7:B8) 8 E 2 It is ...
3
votes
1answer
362 views

Google Spreadsheet text cell formatting question

I'm trying to create some sort of automated formatting for certain columns (except headers of course.) to take a string of numbers (and previous formatting; which should be stripped.) and format it as ...
2
votes
1answer
1k views

In Google Docs, how to make a column with several numbers in each cell sortable?

I am making an expenses spreadsheet in Google Docs and I need to list the numbers of the attachments for each entry. Sometimes this is just one number, sometimes it is several. I can make this ...
4
votes
5answers
4k views

Translating a Sheets document in Google Drive

I'm trying to translate a spreadsheet I've uploaded to Drive and converted to a Sheets. However there is no "Translate" option for me in the Tools menu, as per this article. My Tools menu: What am I ...
1
vote
1answer
239 views

Forwarding table content received in Gmail to a spreadsheet in Google Docs

I am managing to receive a fixed structured table from a questionnaire in a web form. So I want to have this table automatically forwarded to a specific spreadsheet, so that I can make my own ...
5
votes
1answer
11k views

Creating a function that runs when a Google spreadsheet is opened

I want to create a function which runs automatically when the spreadsheet is opened. This function will make the selected cell the last cell without text in a certain column. Basically, I don't want ...
0
votes
2answers
3k views

Google spreadsheet sorting messes up cell comments

I'm running into this weird problem with Google spreadsheets: if the cells have comments on them and I sort the spreadsheet, the rows sort but the comments stay in the same position, and don't move ...
144
votes
3answers
227k views

How to group data in a Google Spreadsheet?

I have the following table in a Google Spreadsheet: Name | Value A | 10 B | 100 A | 20 B | 200 C | 1000 How can I create another table based on a query: select name, sum(value), ...
1
vote
2answers
108 views

Filtering Forms feedback

Does anyone know how to filter respondent feedback into different sheets on the spreadsheet? For example, each sheet collects information for separate classes and I want the form information to be ...
6
votes
2answers
11k views

How to sumif with multiple criteria

My sheet looks like this: 1|A | B 2|amount | source 3|173.00 | bank1 4|173.00 | cc 5|173.00 | cash 6|173.00 | bank2 7|173.00 | cc 8|SUM This is simplified but what I want to do is Sumif A3:A7 ...
1
vote
1answer
2k views

Stop lines in GDocs chart overlapping/obscuring other lines

How can I stop lines in GDocs chart overlapping/obscuring other lines? See for example the green line in the graph below, which starts up high, butthen goes behind other lines such as the yellow one.
3
votes
1answer
341 views

Google Spreadsheets Calculation/Reinput Automation?

I have a little basketball stats sheet, and a cell that has a total of a specific player's statistic they have accumulated throughout the season. But there is no way I know of to update this cell ...
3
votes
1answer
164 views

Published Google Docs spreadsheet: one data set with 3 sheets each sorted differently

Is it possible on a published Google Docs spreadsheet to have 3 individual sheets, each sorted differently from the common data, and only update the common data?
3
votes
2answers
2k views

Table of Contents in Google Sheets

I'm curious to know if there's a way to create a Table of Contents within a Google Sheets. I would like to be able to set the active cell in the spreadsheet based on what cell (or contents of the cell ...
2
votes
2answers
329 views

Javascript string() function in Google AdWords

Is there a simple way to implement the Javascript string() function in the basic Google AdWords interface? I want to pass some variables returned from a campaign management script to a spreadsheet (...
0
votes
1answer
2k views

How can I prepare a sparkline graph (line/bar)?

How can I prepare a SPARKLINE graph (line/bar) in a Google Sheets? With the Google Drive help it doesn't work for me. Reference: Google Drive Help.
1
vote
2answers
6k views

How to use LEFT function in COUNTIF

I have a column that sometimes has B and sometimes B - somecomment. I want to count all instances of B. I expected it to be something like =COUNTIF(B14:B943, LEFT(B14:B943,1) = "B") But no dice. ...
8
votes
3answers
18k 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 ...
39
votes
6answers
173k views

Google Spreadsheets automatically removes leading zeroes

I have a column comprised exclusively of numbers. Whenever I add 050, the sheet automatically changes it to 50. I have tried prepending a space, but it makes no difference. What can I do to avoid ...
12
votes
6answers
30k 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 ...
2
votes
1answer
917 views

Pre-populate Google Docs form field with incremental value

I have a Google Docs spreadsheet that I use to track my jobs by job number. Is it possible to have the "job number" field pre-populated with the next job number? Currently I have to open the ...
2
votes
2answers
2k views

How do I restrict input when the number of entries for a condition is met?

I am using a Google spreadsheet to gather the Registration form entries for our tournament. We can only have ten teams per age group. How do I "close" the Registration when the number of entries for ...
-1
votes
1answer
1k views

IMPORTXML is not working properly [closed]

In my Google Spreadsheets I tried to pull out the data from booking.com. I retrieved Hotel name in column but does not work with every column. I referenced the formula correctly but it automatically ...
6
votes
3answers
1k views

Sorting data in Google Spreadsheet by cell entry

I have a Google Spreadsheet that lists various events. Students have entered their name next to events that they would like to sign up for. It looks like this: Each student may sign up for more than ...

1
134 135
136
137 138
142