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 data as markdown table to your question; this will greatly increase the chance that you get a satisfactory answer.
8,723
questions
22
votes
3
answers
66k
views
Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries)
I'm compiling a database of business address info, including key staff for each business. The Google spreadsheet I've inherited for this project includes a column for 'key staff' (in column B), with ...
- 331
22
votes
2
answers
48k
views
Repeat last action in Google Spreadsheets
I want to know if there is a shortcut in Google Docs Spreadsheets for repeating the last action, like F4 in MS Office.
I found that there was no shortcut for this on Google Forums but the post were ...
- 1,783
22
votes
6
answers
27k
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 ...
- 229
22
votes
6
answers
127k
views
How to create a checkbox column, that if selected will create a strike-through across the entire row in Google Sheets?
I have a Google Sheets. I want to set it up so that the first column is checkboxes, and if the checkbox is selected, the entire row gets a strike-through.
How can I accomplish this?
I followed this ...
- 633
22
votes
3
answers
37k
views
How to convert 1.33 hours to 1:20 (hours:minutes)?
How to add minutes in Google Spreadsheet?
I've got a Google Spreadsheet that looks roughly like this:
min | min | min | min | min | total min | total time
20 | 20 | 20 | 20 | 20 | 80 ...
- 323
22
votes
2
answers
99k
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 ...
- 323
21
votes
2
answers
34k
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 ...
- 447
21
votes
1
answer
153k
views
Apply conditional formatting if other cell is not empty
2 Columns.
C2 | D2
C3 | D3
C4 | D4
C5 | D5
I want it to apply conditional formatting to D2 if C2 has information in it, and D2 is blank. (will be a date). If C2 is empty, then D2 should ...
- 273
21
votes
4
answers
43k
views
Concatenate several columns into one in Google Sheets
I want to concatenate/combine columns A, B and C into column D as displayed.
The number of filled entries in A, B and C will vary over time, so absolute cell addresses cannot be used.
What formula ...
- 333
21
votes
7
answers
78k
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?
- 669
21
votes
5
answers
49k
views
Having units in Google Spreadsheets?
I want to be able to input plain numbers like 200, or 3000 and have the cell display 200g, 3kg, etc...
But still be able to add the values in the cells. So if I add the two above I'd get 3.2kg on the ...
- 321
21
votes
4
answers
72k
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 ...
user79865
21
votes
3
answers
51k
views
Cell reference with colour formatting
Is it possible to reference a cell in Google Sheets so that cell where it gets displayed also displays it using the same text and cell colour formatting?
=A1
Will only reference cell's value. But if ...
- 2,205
20
votes
3
answers
194k
views
How to format the date in this Google Apps Script
I've come across this rank checking script. I'm not sure exactly where I found it but it's built on Google Docs and a script which automates the rank checking process. I understand a bit of coding ...
- 309
20
votes
2
answers
28k
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?
- 1,520
20
votes
3
answers
99k
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 ║ ║
╚═══╩══...
- 30.7k
20
votes
3
answers
72k
views
2-axis line chart in Google Sheets
I've set up a Google Sheets to compare one set of statistics against another. The statistics are for traffic vs. an action. The actions only happen once a day and not every day whereas the traffic has ...
- 6,823
20
votes
2
answers
34k
views
How to open a Google Sheets file and go immediately to the end of the file
I have a Google Spreadsheet with 100 lines.
Every time I open that file, I want to insert a row on the end of my table.
How can I jump to the last line of the table when I open the doc?
Because ...
- 311
20
votes
3
answers
50k
views
How to import JSON data into a Google spreadsheet?
Google Sheets has an IMPORTXML function to import data from various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.
Is there a similar way to import JSON formatted ...
- 4,282
20
votes
7
answers
80k
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 ...
- 706
20
votes
1
answer
75k
views
How to use ARRAYFORMULA with QUERY in Google Sheets
I have a query that needs to be run for every row in a list. It works nicely when written for a single row:
QUERY(MaraRankData,
"select J,I,H,G,F,E where
A='"&E3&"' and
B='"&...
- 355
20
votes
2
answers
25k
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 &...
- 3,122
19
votes
5
answers
273k
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 ...
user108487
19
votes
6
answers
5k
views
How do I cancel editing a cell, in Google Spreadsheets?
I sometimes double-click on a cell to see the formula or whatever, or I just accidentally start typing in the cell. How do I cancel the edits? Esc and Ctrl+Z don't work, so I typically hit Enter and ...
- 970
19
votes
2
answers
55k
views
Get value of cell on the left of current one
Which is the correct formula to get the value of cell on the left of current one. I've tried =OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1) but it populates the error of circular dependency.
- 321
19
votes
1
answer
11k
views
Conditional format rule "Text Contains" with a "+" misinterpreted as a formula
I'm trying to conditionally format a bunch of cells containing strings. If the string contains either a - or a +, the cell should be highlighted in red or green, respectively. However, when just ...
- 1,209
19
votes
4
answers
38k
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
- 347
19
votes
1
answer
84k
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 ...
- 52.3k
19
votes
2
answers
38k
views
Pull CSV data from URL to Google Spreadsheet
I have a site which allows authenticated users (read: in-house users) to download certain data from the site in CSV format, e.g. http://example.com/activities.csv . Can I create a Google Spreadsheet ...
- 293
19
votes
7
answers
81k
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 (...
- 293
19
votes
7
answers
117k
views
IMPORTRANGE with preserved formatting
I am importing a certain range of data from a master sheet and would like to maintain the formatting for those cells.
It seems as though this only works within a sheet: Cell reference with colour ...
- 191
19
votes
5
answers
40k
views
Can Google Sheets have a discontiguous named range?
I have a data set with multiple values that are not adjacent. I wish to make a named range of these values.
Simplified example data:
A B
1 ABC 536936062976506
2 XYZ ...
- 1,202
19
votes
2
answers
39k
views
Give a name to a cell in Google Sheets
Excel has a little box on the upper left corner that displays the name of the cell (for example A8, C20, BC104, ...) and this name can be changed to a string of choice (like Force, mass, acc, ...) so ...
- 451
19
votes
2
answers
73k
views
How to change the values of a pie chart to absolute values instead of percentages in Google Sheets
I want to be able to change the percentages under each of the fruit names to absolute numbers (the respective quantities). I tried changing the 'slice label' but that only added the numbers over the ...
- 425
18
votes
10
answers
119k
views
How can I replace blank cells with zeros in Google Spreadsheet?
I have a spreadsheet where someone else entered the data. They left blank cells instead of recording 0 values. How can I replace the blank cells with 0s?
I tried find and replace with regular ...
- 291
18
votes
3
answers
29k
views
How do I change a cell to the color of the hexadecimal value of a cell in Google Spreadsheets?
Based on my data, I calculated values of hexadecimal color codes. I want some cell (I don't care whether it's an empty cell at the end of the row or the same cell) to be the color based on the code.
...
- 183
18
votes
4
answers
79k
views
CONCATENATE a string with an ArrayFormula
I have the following string in column H and I.
H2="http://"
I2="aaa"
H3="http://"
I3="bbb"
I want to have a auto-generated J column, which results like:
J2="http://aaa"
J3="http://bbb"
So I put ...
- 181
18
votes
2
answers
34k
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 Google ...
Chris Fox
18
votes
8
answers
30k
views
How to convert non-clickable links into clickable in Google Sheets?
My colleague has a spreadsheet full of links. She copy/pasted them from a variety of different sources.
For a number of reasons, I want to convert these from just plain copy/pasted links to HYPERLINK ...
- 323
18
votes
4
answers
65k
views
In Google sheets, how do I reduce the bullet point indent size?
In my Google sheets presentation, I have a table. In some of the cells of that table, I would like to create a bullet-pointed list. Unfortunately, the default bullet point indent size pushes each item ...
- 289
18
votes
3
answers
8k
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 ...
- 365
18
votes
3
answers
63k
views
How to chart the 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 11.34....
- 335
18
votes
4
answers
46k
views
How to use ARRAYFORMULA and SUM together to get a column with the sum of the preceeding columns?
In Google Sheets, I have the following data:
| A | B | C | ... | N | TOTALS |
1 | 123| 425| 324| ... | 234| =SUM(A1:N1) |
2 | 123| 425| 324| ... | 234| =SUM(A2:N2) |...
- 281
18
votes
12
answers
41k
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 (...
- 433
18
votes
3
answers
17k
views
Statically positioned chart (ignore scroll)
I have a Google Sheets document with four columns of data. I have a chart on the right side. When you scroll down to see all the data, the chart scrolls up off the screen.
Is there a way to make it ...
- 486
17
votes
4
answers
49k
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?
- 273
17
votes
4
answers
65k
views
How to skip blank cells when copying a range from source to destination by Formula
I have three levels of hierarchical data in Google Spreadsheets that looks something like this:
As data is being entered in here, I want it collect in three separate sheets like this:
The ...
- 315
17
votes
6
answers
92k
views
Advanced date logic in Google Spreadsheets
I have the following 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 ...
- 323
17
votes
2
answers
17k
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 ...
- 560
17
votes
4
answers
13k
views
How to export / import a Google Apps Script as a file into a new spreadsheet?
I fear this may have an obvious answer, but I've been knocking my head over this and can't find it!
When creating a new spreadsheet, we can easily create a script within it. I like to call it a macro....
- 1,383