As of May 31, 2023, we have updated our Code of Conduct.

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.

Filter by
Sorted by
Tagged with
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 (...
JS1's user avatar
  • 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 ...
ceving's user avatar
  • 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. ...
julia's user avatar
  • 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 ...
Humble_Cook's user avatar
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 ...
Grayson's user avatar
  • 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, ...
Cauthon's user avatar
  • 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 ...
Christian Meyer's user avatar
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 ...
soupless's user avatar
  • 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 ...
Mrquestion's user avatar
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 ...
Bruce Banner's user avatar
-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 ...
Bharel's user avatar
  • 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 ...
Siebe Albers's user avatar
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 ...
Carol's user avatar
  • 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 ...
NateM's user avatar
  • 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 ...
manc's user avatar
  • 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 ...
Patrick Yan's user avatar
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;...
Mateus's user avatar
  • 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 ...
user avatar
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/...
iDevlop's user avatar
  • 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;...
Mauricio Gracia Gutierrez's user avatar
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 ...
user311167's user avatar
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") ...
Starnes Student's user avatar
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 | ______|...
ho4mwr's user avatar
  • 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 ...
Jacob's user avatar
  • 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 ...
Rohit Kumar's user avatar
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 ...
Desiato's user avatar
  • 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 ...
Anto C's user avatar
  • 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 ...
Fir's user avatar
  • 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 ...
Jess's user avatar
  • 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 ...
Abhideep Sinha's user avatar
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 ...
Jan's user avatar
  • 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 ...
Ava O.'s user avatar
  • 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 ...
jxh's user avatar
  • 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 ...
Gabby's user avatar
  • 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 ...
Chris's user avatar
  • 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. ...
PC Welcome's user avatar
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 ...
Bryce's user avatar
  • 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 ...
Victor's user avatar
  • 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 ...
Clement The's user avatar
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 ...
Genny's user avatar
  • 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 ...
Genny's user avatar
  • 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 (...
Jose Dlr's user avatar
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 ...
Leithal's user avatar
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 ...
OlorinIstari's user avatar
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 ...
Ricardo Garcia2's user avatar
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 ...
LWC's user avatar
  • 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 ...
dansumption's user avatar
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 ...
Adam's user avatar
  • 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 ...
ethanxyz_0's user avatar
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,...
tph510's user avatar
  • 5