People who code: we want your input. Take the Survey

Questions tagged [google-spreadsheets]

Google Spreadsheets is a part of the Google Docs web app suite. Questions about Google Spreadsheets are off topic and should be asked on webapps.stackexchange.com, unless they specifically involve your computer or browser.

Filter by
Sorted by
Tagged with
80
votes
9answers
299k views

How to create a formula for every row in a column in Google SpreadSheet?

I use three columns. A, B and C. In column C I have a formula every row =A1*(1.6*B1) and then for the next row I have =A2*(1.6*B2) in C2. How can I do so I don't have to type in the new formula in ...
46
votes
1answer
50k views

How to convert a duration into a number of hours in Google Spreadsheets? [closed]

Trying to organize a timesheet. I have a cell with a duration of, say, 84:30:00 (84 hours, 30 mins, 0 seconds), which has a number format of "Duration". How can I calculate the decimal number of ...
39
votes
3answers
38k views

Pad numbers to specific length in Google Spreadsheet [closed]

I have a column with integer numbers like this: 1, 2, 3, 4, 5 and I want to create another column with these values padded to length of three like this: 001, 002, 003, 004, 005. Is it possible to pad ...
18
votes
5answers
75k views

How to Label / Tag Items in a Spreadsheet

I'm a novice at spreadsheets, so I hope you'll forgive me if I'm asking a super obvious question. Is it possible to use tags in Excel/Google Spreadsheet? I'm creating a spreadsheet to log all the ...
13
votes
3answers
14k views

How do I append a character to the end of every line in an Excel cell?

I have a column of Excel cells that's similar to a grocery shopping list and looks as follows: 3 Days Ingredients 2 Apples 1 Jug of milk 3 Bags of mixed vegetables 5 Potatoes Is there a way to ...
13
votes
2answers
3k views

Date based sum in Excel / Google Docs spreadsheets

I have a bunch of rows with a date and a dollar amount (expenses). I want to produce a list of the days of the month and what the balance of the expenses is. So, for example the 5th entry in the ...
12
votes
9answers
48k views

Can you convert an address to a zip code in a spreadsheet?

Given a column of street addresses with city and state but no zip in a spreadsheet, I'd like to put a formula in a second column that yields the ZIP code. Do you know a way to do this? I'm dealing ...
10
votes
1answer
3k views

Equivalent for FILTER() formula in Libre Office

In a Google Spreadsheet, I create a report sheet by entering a formula like this in the top left cell. =FILTER(Sheet1!A2:E, Sheet1!E2:E > 0) This gives me all the rows in the Sheet1 from row 2 ...
9
votes
2answers
29k views

Extracting email from text string in excel/google spreadsheet

I'm looking to extract email addresses from text strings in Google Docs/Excel and email them automatically, where the content of the cells are currently unknown (until updated by user). For example, ...
9
votes
3answers
19k views

How to auto-fill a numbered series WITHOUT a mouse (keyboard only) on Google Sheets [closed]

Is there a way to fill down a series using just the keyboard instead of the mouse? This is that function where you highlight a group of cells, then click on the bottom right corner of the highlighted ...
8
votes
2answers
3k views

How to “Round half to Even”?

I need to "Round half to Even" (Bankers' rounding) a number in Excel. So: 4961,25 => 4961 9738,75 => 9739 4934,5 => 4934 4935,5 => 4936 How can I do it? It must also work "online", using ...
7
votes
3answers
2k views

Spreadsheet (Excel, Google Docs etc), extracting value separated by comma

I have a value in one cell that looks like this 4,8,2 Lets say it's in the A1 cell how I can get the inner 8 (the second comma separated value) from this cell into the cell B1 ? =? Tried with =...
6
votes
6answers
1k views

Does any spreadsheet allow you to create your own functions?

I've always been frustrated when using spreadsheets by the inability to define my own functions when there is no existing function to (cleanly) do what I want. Does any spreadsheet offer this ability?...
6
votes
1answer
31k views

how to highlight the cell with the max value

given a range of non adjacent columns (not sure if it matters if they're adjacent) i.e F1:F100,J1:J100,N1:N100 for each row i'd like to highlight the cell with the maximum value. i tried ...
6
votes
1answer
5k views

Smooth Scrolling in Google Sheets [closed]

Google Sheets currently scrolls by entire rows and columns, which can be a jarring experience (especially if there are large rows or columns). Anyone know how to make the scrolling smooth?
5
votes
2answers
17k views

Is there an Excel function that works the same as filter() from Google Sheets?

I have a list of names that each have a value (C:C, D:D). I want to generate a new column (A:A) that only shows the names that have the values I want, without any spaces. In Google Sheets it's ...
5
votes
2answers
167 views

Determine United Airlines status level using worksheet functions

United Airlines MileagePlus Premier status levels are determined by a combination of criteria: Silver: (PQM>=25000 OR PQS>=30) AND PQD>=3000 Gold: (PQM>=50000 OR PQS>=60) AND PQD>=...
4
votes
3answers
33k views

How to freeze more than 5 column in google spreadsheet?

How to freeze more than 5 column in google spreadsheet?
4
votes
2answers
3k views

How do I calculate the sum of 2 columns, using the max from each row?

I have data in two columns. I would like the sum of the two columns, using the maximum value for each row. For instance C1 C2 1 2 4 3 11 10 5 7 The result would be 2+4+11+10+7. ...
4
votes
2answers
13k views

How to create a data series for exponential decrease diagram

I want to create a pricing model this way: If you need this product for 50 people, you should pay 2500 (units) per person. . . . . If you need this product for 10,000 people, you should pay 300 (...
4
votes
1answer
11k views

Remove line breaks within cells in a range (\n) by Google Apps Script [closed]

In a Google Spreadsheet I would normally do the following: Go to Find and Replace Check "regular expressions" Find \n and replace with null I have searched and for the life of me I'm unable to find ...
4
votes
1answer
33k views

“Array result was not expanded because it would overwrite data”

I'm trying to use Google Spreadsheets but I get this error when an IMPORTXML would overwrite some rows of cells that already has something in them: Array result was not expanded because it would ...
4
votes
2answers
5k views

“Trying to connect” error in Google Docs on Safari

Beginning a few months ago, I started receiving a significant number of "Trying to connect" errors when using Google Docs in Safari. I can load the page fine, and I can even start using it, but ...
4
votes
0answers
1k views

How to chart time of day in Google Spreadsheet with midnight crossover? [closed]

I'm struggling with the best way to chart a series of times in Google Spreadsheets that involves a midnight crossover. The data in question is a graph of a bedtime, when the time to bed is after ...
3
votes
5answers
14k views

Count substring occurrences within a cell

How can I count the number of occurrences of a substring within a cell? For example: | | A | | 1 |John,John,Tom,David| What formula would I use if I want to return the number of ...
3
votes
3answers
3k views

What Excel formats are (most) compatible with LibreOffice and Google Docs? [closed]

I use Excel (and occasionally Numbers), but I want to be able to share with users of Google Docs and LibreOffice (and I may want to switch in the future). What's the most compatible format to save my ...
3
votes
2answers
84 views

I want to count IF both condition are true Logically count by AND excel

I have two Columns both are categorical columns. Like Age_group and Engagement_category. And I want to get count no. of each engagement_category in each Age_group. | Engagement_category | Age_group | ...
3
votes
4answers
8k views

How to open google spreadsheet's extra sheets in new window or tab?

How to open google spreadsheet tab in new window or tab?
3
votes
2answers
3k views

Calculate Median based on counts of value

I have data of how many of a specific CPU we have, and their CPU-marks. Based on this I wish to dynamically calculate Median and Quartile stats. For example, I have a table that looks like Name ...
3
votes
2answers
7k views

COUNT all “P” from a row

I have a presence row in a sheet (Google or MS Excel, does not really matter), having values "A" or "P" for each name. I need to count how many "P" I have, to make the cell RED if there is any "P" in ...
3
votes
1answer
3k views

How to convert to insert mode when editing formulas on Google Spreadsheets? [closed]

Google Spreadsheet forces you to be in overtype mode whenever you edit an existing formula. The only way out I know is to type gibberish. That is, for how every many characters you want to insert, ...
3
votes
3answers
12k views

In a spreadsheet, is it possible to separate rows into groups that will not change order when filtered?

I am trying to make row subgroups in my spreadsheet rows so that I can sort my data but have rows in any particular subgroup stay together. This is probably easiest to show with a picture. Sorting ...
3
votes
1answer
6k views

HTML tags in Google Spreadsheet [closed]

I am experiencing issues with Google Spreadsheet. The thing is that me and my colleagues are using one spreadsheet as a table for translations, there is a column for English and then German and French ...
3
votes
0answers
403 views

How to open .gdocs and .gsheets from browser, when no Google client installed?

On a computer that has Backup and Sync (formerly Google Drive) client installed, file associations are tweaked in a way that when double clicking on a document with a Google extension (for instance ....
3
votes
2answers
3k views

AVERAGEIFS values that are not NA

I have the following AVERAGEIFS that averages column I grouped by the values in column B = AVERAGEIFS(B:B, A:A, A:A) Problem is, sometimes, the column B sometimes contains an NA() error. How do I ...
3
votes
0answers
1k views

Shortcut keys in Google Sheet don't work?

Background: I have version 45.0.2454.85 m of Chrome (the latest when writing this question). I have a Swedish version of Logitech MX 5500 physical wireless Bluetooth keyboard. Problem: My problem is ...
3
votes
0answers
2k views

Histogram of dates in Google Sheets or OpenOffice

I have a list of dates that I would like create a histogram of. date 5/21/2014 5/20/2014 9/16/2014 5/28/2014 6/8/2014 6/8/2014 5/28/2014 5/20/2014 8/11/2014 7/19/2014 8/11/2014 The chart's x-axis ...
2
votes
4answers
8k views

Count the number of rows with equal values in two specified columns

I have two columns in MS Excel/LibreOffice Calc/Google Spreadsheets with numbers. I would like to count the number of rows which have identical values in both columns. In the following example: ...
2
votes
3answers
198 views

Generate an alphabetic index from a spreadsheet

I have a text file of lecture topics from my physics class that looks like this: 1/14,Galilean relativity,Einsteinian relativity,Minkowski space,Henri Poincare,, 1/16,Lorentz transformations,Velocity ...
2
votes
1answer
218 views

Select all cells in a row

How can I select all cells in a row in Excel / Google Spreadsheet? I know I can select all cells in a column using A:A But how can I select all cells in a row? This doesn't work: 1:1 // select ...
2
votes
5answers
5k views

In a spreadsheet (Excel, Google Docs, OpenOffice), how do I transpose data from rows to columns, but only non-null cells?

I work with all three spreadsheet programs listed above, and I believe my question can be answered the same or similarly for all three. On my sheet, I have a top row full of column headers. The ...
2
votes
1answer
1k views

In Google Docs, how to create a form that can store the data entered into it in an excel sheet automatically?

The title pretty much says it all. I need to create a poll wherein I need to ask the users some questions. How can I do it using Google Docs? How will I access the data entered after that?
2
votes
1answer
3k views

Google Spreadsheets: Counting number of matches between two rows

In a spreadsheet in Google Spreadsheets, I have multiple-choice answers I would like to grade. One row (user 0) contains the answer key, and the other rows contain user answers. User Q1 Q2 Q3 ...
2
votes
1answer
30 views

Conditional formatting with different formats in the same cell

Here's the situation: I have a column with a completion rate from 0 to 138, followed by that value in percentage. ie: 66/138 (48%) I would like to use conditional formatting the change the colours of ...
2
votes
2answers
115 views

How to calculate the difference in months between now and beginning of a loan in Excel

I am actually using Google Sheets (Google Drive), but I guess it's always easy to look for a translation of the formula from Excel. So, say I have a mortgage or a loan issued at a specific date in ...
2
votes
2answers
30k views

Highlight the cell if greater than value in the next column

So I tried experimenting with conditional formatting, but unable to find a way to highlight the cells in the column if their value is greater than the one in the next column under the same row. For ...
2
votes
1answer
333 views

Tracking the longest streak of 100+ points in a column?

I have a column of scores. Assume it is column B. How would I go about tracking the most consecutive 100+ point streak within the column?
2
votes
1answer
28k views

How to create a graph with two Y axis with different values in Excel

How do you make a scatter graph with 2 Y axis (one on the left and one on the right)? I have 2 scatter graphs that I want to combine. I need to have 2 lines, both lines have the same dependent ...
2
votes
1answer
3k views

How can I associate the .csv file extension with Google Sheets?

When I click on a .csv file in a browser on my Windows PC, Microsoft Excel automatically starts with the .csv file opened in it. How can I change my machine or browser settings so that Google Sheets ...
2
votes
1answer
58 views

I want to make new column with Logic?

Given: Employee ID and Manager ID. | Employee ID | Manager ID | |:-----------:|:----------:| | E068 | E067 | | E071 | E067 | | E229 | E069 | | E248 | ...

1
2 3 4 5
8