One of productivity apps in Google Drive, that allows you to create and edit worksheets

learn more… | top users | synonyms

2
votes
2answers
4k views

Google Docs speadsheet auto-fill: control what increments [duplicate]

Possible Duplicate: 'Lock' part of formula when copying from one cell to another Is there a way to control which variables in a formula get incremented when you use auto-fill in a ...
0
votes
2answers
2k 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 ...
0
votes
2answers
632 views

Is there a limit on functions in Google Spreadsheets?

As to answer a question on SuperUser, I was making a sample spreadsheet on google docs. Now I made it first in Excel 2010 on my computer to copy/paste it later on. When I tried to copy a formula that ...
7
votes
4answers
4k views

Google Spreadsheet: sum values with same name

I have a google spreadsheet with a long list of names and amounts. Many of the names are duplicates. For example John | 10 Bill | 2 Susan | 3 Frank | 4 Sally | 10 John | 2 Susan | 2 John | 3 Is ...
1
vote
1answer
399 views

How come I can't input a formula into my Google Spreadsheet?

Here are my steps: focus a cell in my Google Spreadsheet, hit = and then highlight the field D3, hit +, highlight the field D4, hit +, highlight the field D5, hit Enter. However, the field just ...
2
votes
1answer
4k views

JOIN formula in Google Spreadsheets

I want to use the JOIN formula in Google Spreadsheets to transform all values in one column into a list, with , around each value. Has anyone used this formula before? The only information I can ...
0
votes
1answer
814 views

Change all values of a column at once in Google Docs

How can I change all values of column A (currently empty) to be 5? In Excel, I could do Paste Special > Multiply. Is it possible in Google Docs?
2
votes
1answer
2k views

How do I remove the '0' from a field with an equation?

I have a Google Spreadsheet that I enter in new values everyday and want to have the equation run down the entire column so I don't have to drag the previous day's box down to copy the info. It would ...
1
vote
1answer
535 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 ...
4
votes
3answers
13k views

Advanced date logic in Google Spreadsheets

I have the folowing situation: Today is 4/9/12. I have a cell with a date in the sheet that is 4/13/12. This cell should turn red since it is within 7 days from the current date. What is a ...
0
votes
1answer
487 views

How to reference Google spreadsheet inside your mail message?

I would like to embed a Google spreadsheet directly into my email, so that it reflects the document changes. How do I do that?
21
votes
1answer
29k views

How does one add a new line in a cell in a Google doc spreadsheet?

I pressed return to make a new line in a cell of my Google docs spreadsheet. That just moved to the next cell. How does one add a new line in a cell in a Google doc spreadsheet?
1
vote
3answers
3k views

Is there a way to generate number of days in a month in Google Docs Spreadsheet?

The title kind of says it all but just to give you the use case, I have a spreadsheet where I track monthly ad revenue and want to end up with a daily revenue column. Here's a simplified example of ...
3
votes
2answers
2k views

A Way to Add Text Before Your Hyperlink in Google Spreadsheets?

Is there a way to add text before the hyperlink? So for example: This section needs reworking. You can find the section HERE. HERE would be the text replacement for the actual link. The actual ...
12
votes
2answers
33k views

Link to specific sheet in Google spreadsheet

I have a complicated Google spreadsheet with many sheets and a table of contents. Is there some way to create a link to the sheet names so that with a click one can go directly to the sheet? That is: ...
4
votes
1answer
1k views

Normalizing and grouping dates in Google Spreadsheet

I have a spreadsheet pulling together various readings taken of hours heater has run, electricity units use for that period, and outside temperature. For the last 3 months, the electricity usage and ...
2
votes
1answer
523 views

Send Google spreadsheet with embedded HTML in Gmail

When I try to publish content from Google Spreadsheet, it puts it into an HTML iframe. This is found under File > Publish to Web. How can I send this iframe content in a mail, sent over Gmail?
1
vote
1answer
2k views

Copying email addresses from Excel to Gmail into the BCC line

I used to be able to copy a column of email addresses (with a comma at the end of each) from Excel into the BCC line of a Gmail message. Now when I try to copy the column of emails, only the first ...
2
votes
2answers
794 views

How to write a logical function that checks several columns and rows

I have three columns in my Google Spreadsheet: C (Dialog) - D (Declined) - E(Answered) All the columns are dates. Example: C D E 14/02/2012 28/02/2012 ...
2
votes
1answer
3k views

Hyperlink within a Google Spreadsheet?

I have a Google spreadsheet with many tabs. I would like to put a link in a cell on one tab that when clicked take me to another cell on another tab. Is this possible?
1
vote
1answer
525 views

How to put the name of a cell into another cell in Google Spreadsheet

In Google Docs Spreadsheet, is there a simpler way to put the name of a cell reference into another cell than this? =concatenate(char(code("A")+COLUMN(D3)-1),row(D3))
5
votes
1answer
4k 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 ...
0
votes
2answers
1k views

Return an Offset Cell Value, Where Last Occurrence in Range is Matched

I have a worksheet like this: || A | B | C | ---++--------+-------+-----| ---++--------+-------+-----| 1 || 1 | a | | 2 || 2 | b | | 3 || 3 | X ...
4
votes
1answer
573 views

How do you conditionally join text in Google Sheets?

So, I thought this would be relatively straightforward for an advanced user, but this one is troubling my brain. I have a schedule of people: 1 2 3 4 5 6 7 8 John D. a a ...
5
votes
2answers
2k views

Downloading charts from Google Spreadsheets in high resolution

I have a Google spreadsheet 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 ...
5
votes
4answers
11k views

How to make a query with Google Spreadsheet, that checks for a date

I have a range that goes from B4:H124. All the cells in column B are dates. I would like to retrieve the data from a row (B:H), where the DATE equals to the content of a specific cell, lets say Q4. ...
2
votes
2answers
1k views

How to count cells that are below a certain date and check if other cells in the current row are empty?

I need to count how many cells in a specific column are below a certain date. Also, at the same time, the counter should only be increased, if some other cells next to each cell being counted are ...
5
votes
4answers
5k 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 ...
1
vote
1answer
1k views

How to query for count of dates in Google Docs?

I have a Google Spreadsheet of line items. One of the columns is a Date field. How can I query (or use a formula) to, for example, get the count of records where the Date field is within February of ...
7
votes
3answers
9k 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 ...
3
votes
1answer
2k views

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

I want to create a simple boxplot 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 boxplot gadgets. Do I have ...
-1
votes
1answer
782 views

Simple row references in Google Spreadsheets and resorting data

This is my scenario Users are entering data into rows with several columns Last column is calculated on values of few previous columns (so it's row number independent). While entering new and ...
2
votes
2answers
957 views

Google spreadsheet charting count of items?

I have a spreadsheet that is filled by a form. It contains values like the following: Timestamp Person Category 2012-02-13 10.31.22 a x 2012-02-13 11.06.37 b y 2012-02-13 ...
1
vote
0answers
1k views

Change value in one cell through a formula in another cell

I am trying to capture a timestamp for when a spreadsheet is first edited, and ONLY keep that first timestamp. However, NOW() updates each time a spreadsheet is edited. I found a working formula ...
5
votes
2answers
7k views

Google spreadsheet—calculating shaded cells

I am using Google spreadsheets to create a matrix (time/activity) as a daily schedule for staff to sign into. Is there a formula to calculate the number of cells in a column that are shaded (or not ...
1
vote
1answer
4k views

Filter rows in Google spreadsheets

I have a Google spreadsheet. There is a colour column. Every row has a colour, Red, Yellow, Green etc. I want to be able to filter out say all rows that are Red, all rows that are yellow, all rows ...
3
votes
2answers
651 views

Format credit card numbers as four digit blocks in Google Spreadsheets

I have this credit card number in a Google Docs spreadsheet of the following format: XXXXXXXXXXXXXXXX How can I make it display like XXXX XXXX XXXX XXXX or XXXX-XXXX-XXXX-XXXX where they're in ...
0
votes
1answer
2k views

Google Docs ImportXML can't scrape anymore?

ImportXML lets you get information from filetypes such as xml, html, csv, tsv, as well as RSS and Atom feeds. This example in Google Docs Help for importXml function - =importXml("toysrus.com";; ...
2
votes
0answers
213 views

In Google Spreadsheets the xPath method with Google Maps don't work anymore?

I've used this query to calculate distance between two locations, now it won't work anymore, and returns the error xPath query didn't get any result (translated from Italian, I don't know if is the ...
3
votes
2answers
10k views

Sum column only if another column has an empty value

I am trying to get a sum of column values in a Google Spreadsheet only if there is no value in the adjacent column. Consider the following example: A | B ------- 40 | 50 20 | 20 30 | 10 | ...
1
vote
1answer
1k views

Sum of the values of the 10 cells above me?

I'd like a formula for Google Docs spreadsheets that will return the sum of the values of the 10 cells above the current cell. That is, if I'm on C31, I want the equivalent of =SUM(C20:C30). However, ...
4
votes
2answers
7k views

How can I calculate how often one string appears in a column compared to another string?

I have a column in my Google Spreadsheet which contains either OK or NOK (acceptance testing). How can I calculate the percentage for how often OK appears compared to NOK.
4
votes
2answers
3k views

How do I format a cell to be in alternative currency without decimals in Google Spreadsheets

I am really scratching my head, but it seems like this is not possible in Google Spreadsheets. I want to format a number cell in kroner without any decimals. All I get is "kr 38,434.00" I want this ...
1
vote
1answer
588 views

Duplicating a set of columns with various widths within the same spreadsheet

I'm trying to duplicate a set of 5 columns multiple times on the same sheet. For example, column A is wide, columns B & E are medium width and Columns C & D are narrow. I want to duplicate ...
6
votes
2answers
13k views

Adding one Google Spreadsheet 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 Docs should work for that, but so far everything ...
2
votes
1answer
3k views

Hyperlinks in Google spreadsheet docs

In Google spreadsheets, it is possible to add a hyperlink to a cell as: =hyperlink("www.google.com";"search the web") This will show the text search the web which will hyperlink to Google. ...
3
votes
1answer
4k views

Google Spreadsheet fill value in multiple cells

In MS Excel, after selecting a range of cells it is possible to enter a value and press CTRL+ENTER to have that value set in all selected cells. Is there a keystroke in Google Spreadsheet that would ...
6
votes
4answers
11k views

How can I refer to two different columns in a COUNTIF function?

I'll try my best to explain. Say I've got a sheet that looks like this (in this contrived example, let's say I'm sending marketing emails to various people, I know their ages and zip codes, and I want ...
13
votes
5answers
11k views

Text to columns conversion in Google Spreadsheets

How do you do a text-to-columns conversion in Google Spreadsheets? For example, I have the following data string in one cell: 5,233,6,2,6,7,2,2,6,6 I want to break it apart by the comma separator ...
8
votes
4answers
18k views

How do I calculate a time delta in my Google Spreadsheets?

I've got a Google Spreadsheet that looks roughly like this: Date | Start | End | Minutes ------------+-------------+-------------+------------------------- 1/11/2012 | 11:39 ...