A productivity application from Google that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

2
votes
2answers
4k 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?
2
votes
1answer
1k views

How do I prevent that references in my formula change when I copy the formula to a new cell?

I'm trying to use submitted Google Form data in one spreadsheet to locate and retrieve data from another spreadsheet (tab in same document). The Google Spreadsheet file is here. The =QUERY statement ...
2
votes
3answers
2k views

IF statement in Google spreadsheet

I am trying to sum up the total of all values in a coloumn that are POSITIVE. Also, I have some subtotals in the coloumn that should be left out from this sum. =SUMIF(E6:E65 ; ">0" ) - ...
2
votes
2answers
2k views

How do you add a custom font size in Google Spreadsheets?

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

Can I sum based on a specific cell text value?

I have a spreadsheet for tracking sales leads. Each row is a lead. One of the columns is called "Status" and the options under this column are limited to a list of 4 options, i.e. Prospect, Proposal, ...
2
votes
1answer
836 views

Sorting Unique rows by a related Sumif column in Google Spreadsheet

I am putting together a worksheet to keep track of expenses. I currently have the following source table called "Expenses": A B C D 1 Date Description ...
2
votes
3answers
5k views

How to have auto updated current time cell in GoogleDocs / Excel

I am trying to create a spreadsheet that will allow me to track my clients' timezones. I am using the useless NOW() function to calc current time, and then factor in the time zone value (+/- a #) as ...
2
votes
2answers
830 views

Pre-fill a cell with today's date

I would like a certain cell of mine to display today's date when I starts a new row. Is it possible?
2
votes
1answer
6k views

Put Google search inside Google Docs spreadsheet cell

Is there a way to put a Google search widget inside a cell of a Google Docs spreadsheet? I want to avoid my users to hit Ctrl + F every time they search. Would be very cool a Google Docs App that ...
2
votes
1answer
22 views

Google Sheets Conditional Formating “IsEqualTo” value in referenced cell

What do I add to the text field directly below the "is equal to" selection if I want the test to be the value of a specific cell? I want the condition to pass and format a cell "IF" it is equal to ...
2
votes
1answer
44 views

Forms Data Manipulation In Google Sheets

I'm trying to develop a solution using Google Forms with Google Sheets that will collect Google Forms data and then automatically divide that one form submission's data into multiple rows of data in ...
2
votes
2answers
29 views

How do I sequence the date in Google sheets without sequencing the year?

I am trying to sequence dates for the beginning of the week. It should read: 21-12-2015 28-12-2015 04-01-2016 and so on. My problem is that when I highlight the first two dates and drag; I get ...
2
votes
2answers
34 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 ...
2
votes
1answer
49 views

Access Google Spreadsheet revision history through API

Is there any way I could access Google Spreadsheet revision history through the API? The reason for that is I have a lot of sheets and simply relying on build-in revision history does not suffice to ...
2
votes
2answers
36 views

Can a button be added to Google Drive (Sheets) toolbar?

I use Google Drive Sheets for spreadsheet budget management. I am frequently using the Insert > Row Above/Below menus. I don't find any shortcut for either menu option in the Keyboard Shortcuts ...
2
votes
1answer
34 views

How can specific columns of a Google spreadsheet be exported as CSV?

I have a Google spreadsheet with 10 columns of data. I would like to export columns A, C, E, F as csv. I do not need to export the entire spreadsheet. Can this be done? I do not mind using Google ...
2
votes
1answer
75 views

Is there a way to dynamically populate a dropdown depending on the value of other cells?

Is there a way to populate a dropdown list differently depending on the values of another cell? For example, the dropdown in A2 contains Movies and Sports. I want the dropdown in A3 to have Horror and ...
2
votes
1answer
22 views

Automatically populate a table with time values, based on the type of event

I am hoping to find a way using the conditional formatting to find an easier way to have a populated registration time (F), event start time (G) and event end time (H) for each event type as shown in ...
2
votes
1answer
41 views

Can Google Spreadsheets conditionally format based on a previous cells content?

Is it possible to conditionally format the background colour of a cell, based on the value in the column preceding it? For example, in the following table (first row/column are titles): | A | B | ...
2
votes
1answer
263 views

Simplifying Google Form Checkbox Responses into Itemized List

This is more of a Google Sheets question than a Forms question. I want to receive form responses from checkbox questions (which unfortunately cannot be separated into separate rows) and flip the data ...
2
votes
1answer
189 views

Google Sheets: Asterisk character (no wildcard) on conditional formatting

I have a sheet with following values on column A: [ A ] [1] Dead line [2] 15 days remaining*** [3] Dead [4] 131 days remaining* [5] 80 days remaining** I would like to use ...
2
votes
1answer
42 views

Division in Google Spreadsheets

How do I accomplish the following: I have a cell C5 with various numerical values. If C5 is less than 30 return 0 in C6 If C5 is greater than 30 return 1hr in C6 if C5 is greater than 60 return 2hr ...
2
votes
1answer
31 views

“Clean” join() without excess delimiters?

I'd like a function in Google Sheets to concatenate elements with a chosen delimiter like join(), but without excess delimiters at beginning, end, and between null elements. I accomplished this with ...
2
votes
1answer
57 views

Formula to show text based on numbers

I am looking for a short way to type this: =if(a1=1,"sometext",if(a1=2,"othertext",if(a1=3,"thirdtext"))) In JavaScript you would do something similar like this: switch(a1) case 1: "sometext" ...
2
votes
1answer
63 views

How do I use a negative DURATION formatted cell from Google Spreadsheets in Google Scripts?

I'm using Google Spreadsheets and I have a sheet in the following format: Start | End | Partial result | Expected | Total 08:00 | 12:00 | 04:00 | 05:00 | -01:00 14:00 | 18:00 | ...
2
votes
1answer
171 views

How to post responses from Google forms to a specific sheet in a Google spreadsheet

I have a form, a Google form, and I'd like to post user's responses to a specific sheet in the Google spreadsheet. Is this possible?
2
votes
1answer
61 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 ...
2
votes
1answer
55 views

Highlight cells, which content can not be parsed as number

How can I highlight cells whose content is not parsable as number? thanks!
2
votes
2answers
644 views

How can I trace precedents in Google Sheet

In Excel we can trace precedents and dependents. Can some one help me with something similar for Google Sheets? I have got the code for dependents from http://webapps.stackexchange.com/a/50149/88163. ...
2
votes
1answer
277 views

Conditional Filters Formula in Google Sheets

So I have a cell C4 in which I have an item list for a data validation. Below, I want to create a filter that will take all the entries in another sheet named All and compare that to the C4 cell. So ...
2
votes
1answer
43 views

Formula which will grab specific cells and concatenate them together but only if they are not blank

So I have this formula currently, but it breaks when nothing is in any of the cells. I'm expecting this of course, as I haven't told it to do anything when ISBLANK is TRUE. So my question is, am I ...
2
votes
2answers
88 views

Turn a table into a list of row/column/value tuples

I want to turn a table like this | | apple | banana | chia | |----------+-------+--------+------| | antelope | 1 | 4 | 2 | | bear | 6 | 3 | 9 | | cheetah | 8 ...
2
votes
1answer
112 views

Can't filter a column to see all cells containing `@` in Google Spreadsheets

I'm trying to filter a column to show cells that contain @ (as in an email address) but when I put @ in the filter box it comes up with a blank. Any ideas? An example spreadsheet here. Here is ...
2
votes
1answer
574 views

Get unique values from range of cells

Let's say I have a column of values (e.g., first names). There may be some duplicate data (some first names are very common). How can I get the unique values? In SQL I would do SELECT DISTINCT ...
2
votes
2answers
336 views

Google Spreadsheet: Count column if criteria is met in the respective column and another column

Is there a way to COUNT the cells in column A if the cell in column A equals "X" and the adjacent cell in column B equals "January"?
2
votes
1answer
143 views

Google Spreadsheet Query for unique and “most recent”

Given a Google spreadsheet like this: I would like to create a query that shows only the most recent row for each unique name, so the results would look like this: What would that query look ...
2
votes
1answer
63 views

google cell format: only 3 digits

Is there any way to do this: I have cell and most of the numbers have 2 significant digits like 3.45. But once in a while I have a number like 143 big number, the rest are under 10. Currently for 143 ...
2
votes
1answer
174 views

Posting more than 2000 lines from IFTTT to a Google Spreadsheet

I have an IFTTT trigger set up whose action is to add a row to a Google Drive spreadsheet. Once the spreadsheet reaches 2000 rows, IFTTT creates a new document with the title: Document Title (1) ...
2
votes
1answer
477 views

How can I COUNT rows with ARRAYFORMULA?

I have a sheet that lists incidents in an Incidents sheet: | Date | Name | Incident | |------------|-------|---------------------| | 01.01.2015 | Vidar | Car broke down | | ...
2
votes
1answer
376 views

Multiply duration (in hours) by decimal value

I have the following data in a Google Spreadsheet: +----------+------+--------+ | Duration | Rate | Value | +----------+------+--------+ | 16:15:00 | 55 | 893.75 | +----------+------+--------+ I ...
2
votes
2answers
2k views

Formatting Duration Values in Google Spreadsheet

I have a Google Spreadsheet. In that spreadsheet, I have the following: Date | Start | End | Duration -------------+-------------+-------------+------------------------- ...
2
votes
2answers
47 views

Suming functions by relations between sheets (Google Sheets)

Table for counting items: ID COUNT 1 10 2 0 3 12 4 1 5 5 6 10 7 9 8 9 9 11 10 0 Table-pedia with info about items: ID NAME FEAT1 FEAT2 1 Nanan R 20 2 Meowth S ...
2
votes
1answer
278 views

Google Sheets formula every x rows, assign incrementing cell value

I have a Google Sheet, with the first row container headers (so row references will be -1) and rows are "grouped" by every 7 rows. See below. I've manually typed the numbers into the Image # ...
2
votes
3answers
2k views

How do I eliminate extra line breaks in a Google Spreadsheet cell?

I have a spreadsheet in which some cells have multiple carriage returns. I would like to eliminate all the extras, so that 4 carriage returns shrinks down to just one. This question is similar to How ...
2
votes
1answer
83 views

Appending text to each column in a Google Drive Spreadsheet

I have a Google Drive spreadsheet that has about 200 columns. Each one is a name, and to each one I want to append -banner.jpg. Is there a way to batch process this? For example: abcde fghij ...
2
votes
1answer
355 views

Preventing concurrent editing of a cell in Google Spreadsheet?

We often have the case of two people overwriting each other's changes by writing on the same cell at the same time. Is there a way to make it so that cells can't be written by more than one person at ...
2
votes
3answers
173 views

Counting number of cells in a range that contain certain text characters

My question simply: Is it possible to generate the data in A3:A5 using a formula? A B C D E F 1 PASSED/TOTAL | EXAMS ATTEMPTED 2 ...
2
votes
2answers
242 views

Incrementing value read from spreadsheet exploding. 1 + 1 = 200 000 000 000

I read a value (in this case 7) from a Google Spreadsheet with a script, increase that value using source++ and write the increment back to the spreadsheet. I expect a series of 7, 8, 9, 10. ...
2
votes
1answer
3k views

Date and time stamp automatically added in column [duplicate]

We have a barcode scanner that we use to track student assignments. I am trying to create a Google Spreadsheet that tracks the assignments for each subject. I have been using the following script: ...
2
votes
1answer
607 views

Arrayformula with dates before and after

Here is what my spreadsheet looks like I enter recurring weekly payments details in G,H,I,J & K (Name,Amount,Day of week, Start Date, End Date respectively). This arrayformula: ...