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,634
questions
0
votes
1
answer
51
views
How to put multiple hyperlinks inside a Google Sheets cell
I tried multiple option, all returned me an error.
When I want to put one link in a cell this works fine :
=HYPERLINK("mylink.com";"link1")
Here is what I tried for multiple links (...
- 103
0
votes
1
answer
34
views
How to use OFFSET in ARRAYFUNCTION?
I have the following function, which calculates the average of the previous 7 rows of the column on the left.
=AVERAGE(OFFSET(C8;-6;-1;7))
The problem: whenever I update the data column I have to ...
- 145
2
votes
1
answer
30
views
data validation case sensitive string in google sheets
I'm trying to formulate a data validation code in Google Sheets for a simple 6-letter string - any 3 capital letters and any 3 lowercase letters. I believe I should be using the RegexMatch function. ...
- 21
1
vote
1
answer
36
views
How do you work with the Color Class array?
In Google Sheets using Apps Script, I want to read in the font color in many cells, change some values (change colors) in the array, and write them back to the cells.
I have previously used ...
- 11
0
votes
2
answers
32
views
Automatic Numerical Incrementation [closed]
I'm trying to get automatic incrementation working for a dynamic range of data. I've got this convoluted formula that works in some of the cells but not others, returning a #Value error that doesn't ...
- 21
2
votes
3
answers
99
views
Google Sheets - smart filtering
So I need to do something which might be a standard feature in spreadsheets, but I have no idea how.
I have a landing page that processes T-shirt purchases by families. Each row represents an order, ...
- 123
0
votes
2
answers
20
views
IMPORTHTML in several columns and SUM
I want to calculate the average historical dividend yield of three stocks. For this I import the data from a table with IMPORTHTML.
When I now calculate the sum, the value is 0.
I have played around ...
0
votes
2
answers
46
views
What's the workaround to copy the link for a cell when using IMPORTRANGE?
I want to know what the correct way is to use IMPORTRANGE while it also gets the correct link for another spreadsheet. What ends up happening is that the link directs to the current file instead of ...
- 101
0
votes
1
answer
23
views
Is it possible to modify with a formula the same cell where you introduce the value in Google Sheets?
So imagine we have column A, B and C. And I want to sum A1 + B1
You can do type in C1 "=SUM(A1,B1)" and you will see the result in C1
But, is there any way we can introduce the formula in A1 ...
- 109
0
votes
1
answer
22
views
Adding rows in Google sheet to create a chart
I am trying to create a Pie chart using Google sheet data such as this:
Vendor
Amount
Airbnb
$500
Airbnb
$200
Airbnb
$100
McDonalds
$ 15
...
-1
votes
1
answer
56
views
How does ArrayFormula work?
I'm trying to understand when does ARRAYFORMULA decide to expand a range, and when does it decide to repeat it.
According to this StackOverflow question:
The rules for ARRAYFORMULA are:
If a ...
- 101
0
votes
1
answer
22
views
How to copy/paste conditional formatting in Google Sheets (without including the original cells)
When I copy and "paste the conditional formatting only" option from one column to another (e.g. A1:A5 to B1:B5) the result is that for B1:B5 the conditional format range includes A1:A5.
How ...
1
vote
1
answer
34
views
Split Text to columns in Google Sheets with multiple full words as delimiters - "to" or "for"
I need to split a text when the delimiter is "on" or "to" or even "for", removing the delimiter from the results:
Ex:
Creating more value for business =
Creating more ...
- 11
0
votes
1
answer
20
views
Query to group files together by a unique box number and return the min/max range
I have a Google sheet that list of files with a corresponding box number(Col1)labeled "Box Number". I want to be able to gather all files by each unique box number and then return the min ...
- 11
2
votes
2
answers
50
views
Sum of all numbers from many columns that match text from multiple rows in another column
I have some data from analytics that I need to combine somehow and trying to find the best way of doing this. I have data similar to the following:
URL
Sessions
/
59
/?variable=1
2
/?variable=2
37
...
- 135
-1
votes
1
answer
33
views
Start sequence on a variable column without "array not expanded" error?
In one sheet, I have columns for each month. In another sheet, I define start countdown in: with a cell 04/2023 and # of months as 4.
The desired result is starting under April 2023, I want to see a ...
- 127
1
vote
1
answer
48
views
SUMIF column by comparing two date columns
Please help with this formula.
=ARRAYFORMULA(if(A3:A$900="";"";IF(DATEVALUE(History!C$3:C$900)<D3:D900;SUMIF(History!A$3:A$900;A3:A900;C$3:C$900)*SUMIF(History!A$3:A$900;A3:A900;...
- 23
-1
votes
1
answer
19
views
Keep merged cells across 2 Synced Sheets?
So, on Google Sheets, Im trying to reference merged cells across 2 sheets.
1 Sheet has merged cells (ie. A2:A7) but when I reference it on the second sheet, they unmerge and the data is only shown in ...
Caleb Handy
1
vote
2
answers
23
views
Google sheets equivalent to Excel Clear All?
In Excel, selecting a range and pressing the Del key is equivalent to 'Clear Contents' and leave the formatting unchanged. But there is also a 'Clear all' option that clears contents/formatting/...
- 300
1
vote
1
answer
22
views
VLOOKUP of Text is not working
Here Is how my google spreadsheet looks like
I'm trying to find the the Artifact ID (Column K) using the artifact name that is in G
So on A2 I'm trying to use this formula
=VLOOKUP(G2;$K$2:$L$39;1;...
0
votes
1
answer
20
views
How to retrieve accidentally deleted data from within a Google Sheets file on a private Facebook Group - please help
Someone accidentally deleted the data within a shared Google Sheets document on our Facebook group. Is there any way we can get it back i.e. revert to a previous version of the document? Is this ...
0
votes
1
answer
29
views
How can I link two addresses using a Google Sheets formula to create a Google Maps URL?
It seems that it is easy enough to get the URL to one address using the following formula:
=HYPERLINK(CONCATENATE("https://www.google.com/maps/search/?api=1&query=",L13),"MAP")
...
0
votes
1
answer
33
views
Google sheets - sum values in a key-value column pair for each key and display the results
Given:
A | B |
______|____|
key1 | 3 |
______|____|
key2 | 5 |
______|____|
key3 | 7 |
______|____|
key2 | 9 |
______|____|
Get the output:
D | E |
______|____|
key1 | 3 |
______|...
- 3
0
votes
1
answer
29
views
How to sum 5 largest numbers of 6 numbers across multiple sheets
I have a spreadsheet where we are keeping scores for golf across 6 weeks but we are only taking the best 5 scores from the 6 weeks. Each week (1-6) has its own sheet (tab)
I need to get the sum of 5 ...
- 1
-1
votes
2
answers
25
views
Subtotal of duplicate items in cell mentioning the duplicate items the last time
Currently, I am using the sum function to total matching item names by manually selecting matching cells. I would appreciate help automating the calculation of subtotals by item name.
Link to my ...
1
vote
0
answers
19
views
Edit and examine existing filter views in Google Sheets [closed]
I inherited a monster sheet that is pretty much constantly used by multiple persons. It contains several filter views.
Is there any way to find out what exactly each filter view does without manually ...
- 111
1
vote
1
answer
47
views
Trouble with a formula in Sheets to forecast shipment arrival
I have big trouble making my formula works and I am pretty sure that this is simple.
So simple that I am litteraly struggling on it for weeks now.. So I give up :'(
Let me explain:
I am using sheets ...
- 13
0
votes
1
answer
31
views
Trying to use "IF" function to validate a set of rules
Trying to get some help with an issue I'm having.
I'm trying to run some raw data (Column A) through a series of checks (Column B,C & D) to get my final result in Column F.
Added a screenshot of ...
- 1
0
votes
2
answers
30
views
In Google Sheets, how do I add an extra leading zero to a list of numbers that are all written as #345 for example?
Formatting issue- my spreadsheet had numbers from #001 up to #850, but a new addition has taken the highest value past #1000, so now all of my old values need to change to four digit numbers. Is there ...
- 1
0
votes
1
answer
28
views
How to divide the values of a table in google slides by a number using App Script? [closed]
I have added a table from a google sheet to google slides using app scripts. However, I want to divide the values of the table by 1000000. Is there a way to divide the table values of the table in ...
1
vote
1
answer
25
views
How to QUERY - group SUMed results? [duplicate]
I'm new to query and can't figure out how to get grouped results of sum in two columns.
There's data set in the picture of names and points which are in two columns and query formula to sum and group ...
- 11
1
vote
1
answer
128
views
Google Sheets: Create a formula to generate unique sequential IDs for items in a column that don't already have IDs
I am cataloging items in a spreadsheet using Google Sheets. Every item needs a unique ID, which is a sequential code formatted like ABxxxxxx, where AB000000 < ABxxxxxx < AB999999, starting from ...
- 13
1
vote
1
answer
77
views
ARRAYFORMULA and MAXIFS: does it work like SUMIFS?
I have an existing formula that is sort of a cumulative MAX of another column: it has a range covering only the rows above it, inclusive. So B2 is =MAX(A$1:A2), and I can run this down or auto-fill ...
- 13
0
votes
1
answer
23
views
Create a Chart for Membership Data
Was wondering how to best set up data to create charts for memberships.
I want to create a chart that compares # of New Members vs. # of Cancelled members. In the data, I have one columns that ...
- 1
0
votes
1
answer
48
views
Buttons don't work on iPad with Google Sheets - the onEdit(e) workaround isn't working. Suggestions?
I have a button that works with a function Yeah_Go(), but only when using on a browser on a laptop. When I try to press the button on an iPad Google Sheets mobile app, it doesn't work. Instead, the ...
- 103
-2
votes
2
answers
21
views
i want that webp urls are arranged in the same order as jpg urls
webp urls have the same names as jpg urls. i want that webp urls are arranged in the same order as jpg urls. they're in Y2:Y17 in google sheets. jpg urls are in Y2:Y9 and webp urls are in Y10:Y17. ...
1
vote
0
answers
14
views
Is it possible "conditionally format" an entire row? Visually group similar rows? [duplicate]
With Google Sheets is it possible to color rows by a given column.... for example to alternate colors based on key in this spreadsheet:
Key
Blah
Blah
Blah
1233
Blah
Blah
Blah
1233
Blah
Blah
Blah
...
- 695
0
votes
2
answers
88
views
Copy Cell Value Based on Value on Another Cell [closed]
I have three columns:
I want to copy the values in "Col B" and paste it to "Col C" if that particular row's "Col A" value is "COGS" - if the value in "Col ...
- 9
1
vote
1
answer
25
views
Two Different Answers for the Same Simple Formula in Google Sheets
I have a spreadsheet with a very simple formula: =C4/D4
Sometimes, this very simple formula gives the wrong value.
Cell C4 contains the value 390062 formatted as a number with thousands separator
...
- 13
1
vote
2
answers
88
views
Google Sheets Conditional format when cell contains any word from another cell (partial text match)
I am currently using =regexmatch(A1,"(?i)N02|N22|J25") in column A to highlight the cells that contain these terms.
But I don't want to hardcode it as the search terms keep changing.
The ...
- 335
3
votes
1
answer
110
views
Google Sheets - Refer Filter range to a cell?
I have this formula (simplified because it is long):
=FILTER(RANGE1,(C3=TRUE), RANGE1<>"")
In place of range can I refer to the cell , AC2, that would contain the range name?
That is ...
- 335
0
votes
1
answer
27
views
Conditionally format the same cell in Google Sheets?
I will like the format the same cell in google sheet, to be more specific, any positive increase to the previous value (same cell) will turn green, and any negative decrease to the previous value (...
- 5
0
votes
1
answer
25
views
How to allow limited access to Dropdowns in Google Sheets
I work in an office. We use Google Sheets. I designed a sheet with three dropdowns to allow people to change info filtered by those three dropdowns.
I am the editor, and so are two other people.
I ...
- 1
0
votes
1
answer
24
views
Time series plot with category names as text
Let us say I have data like this for a spend summary sheet:
Date
Amount
Category
03/31/2023
20.0
Food
03/31/2023
23.12
Coffee
03/31/2023
21.01
Movie
04/01/2023
10.0
Coffee
04/01/2023
12.0
Coffee
...
- 103
0
votes
0
answers
10
views
How to open a specific tab based on todays month [duplicate]
I have a google document that i made a tab for each month like this, Aug, Sept, Oct, Nov, Dec, Jan, Feb, Mar, April, and May. I would like to be able to open the the document and automatically open ...
1
vote
1
answer
63
views
How to do an arrayformula that transposes a list while multipling the entries? [closed]
I have a list of rows, and I like to transpose them into a list of columns.
But the trick is how do I repeat each of the new columns 4 times (1 row will become 4 columns)?
Alternatively, how do I add ...
- 1,182
0
votes
1
answer
28
views
Google Sheets conditional formatting gradient not applied for values >= 1 and < 2
I have a Google sheet where several columns use a gradient (using traffic light colours - from red to yellow to green) to indicate values from min to max.
This works fine for values less than one, and ...
1
vote
1
answer
23
views
Single Google Sheet COUNTIFS function that counts across multiple columns with multiple conditions
I want a sheet that counts across multiple columns
Here's my Google Sheet
H M P U
Submitted 3/3/23 John Validation
In progress John In progress
I want to use a ...
- 449
0
votes
1
answer
109
views
Wrong grand total when using Calculated Field
I have a simple pivot table, but I'm using Calculated Field values.
The raw data used for the pivot table is like a transaction log, as shown below:
DATE
STOCK
QTY
PRICE
TOTAL
03/2023
AAPL
1
100
100
...
- 53
0
votes
1
answer
15
views
Trying to round a number to the nearest millions
I have several hundred rows of revenue data that start from row 1 and goes all the way down the sheet in column A. Something that look like this...
A
B
$135,345,000
$200,000,130
$383,430,000
$4,...
- 5