Skip to main content

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.

1,779 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
2 votes
1 answer
40 views

Getting the slope of the 10 cells above current

I'd like a formula for Google Sheets, but I'm having trouble figuring it out. Say I'm on B74, I want the equivalent of =SLOPE(B36:B63,A36:A63). However, I want something that will work even when I add ...
borgius's user avatar
  • 21
2 votes
2 answers
211 views

Spreadsheet formula to calculate most common items in ordered items lists

I am searching for a correct google spreadsheet formula to go through the list of ordered items and summarise the resulting winning items. Like is you have an Oscar ceremony and you get votes from ...
shershen's user avatar
  • 163
2 votes
1 answer
64 views

RCV Polling Data from Google Forms goes the wrong direction in Google Sheets

I am making an RCV voting system in Google Forms, however, when the results go into Google Sheets, they come out like this: Timestamp 1 2 3 4 239029339 Candidate A Candidate B Candidate D Candidate ...
Coding Cat's user avatar
2 votes
2 answers
671 views

A Script that Allows a Checkbox to Recognise a Value via Formula and Copy to Another Cell

Is it possible for: a set of checkboxes to recognise lists (grouped in one cell) on entry (via formula) to then have the same checkboxes copy the list to another cell as it returns "TRUE" ...
Stephanie's user avatar
2 votes
2 answers
93 views

How to pull data from one sheet to another

Very inexperienced google sheets user here. I have a sheet with employee data. One row and many columns of data for each employee. Some employees appear more than once (i.e. in multiple rows) I need a ...
M. Schultz's user avatar
2 votes
0 answers
689 views

Sheets - display ISO8601 string as localized date

In Google Sheets, is there a way to have a cell display its value as a localized date (and time) but actually still have and accept its value as an ISO8601 date string? The use case is that I have ...
MJV's user avatar
  • 121
2 votes
2 answers
344 views

Using REGEXEXTRACT to retrieve specific content from two separate rows in the same column

I Included a Google Sheets link =REGEXEXTRACT(A2:A3, "19..") To me, it would seem as if this would check both cells for any date starting with 19 and extract that into the cell But, it ...
Marshall 's user avatar
2 votes
2 answers
321 views

Aggregate data to show text in one cell

I need to put together multiple entries into one where the result is text and has to be in one cell. From this data in A:B This is a sample; the actual list in Column A may be extremely long. A B 1 ...
Abul's user avatar
  • 21
2 votes
1 answer
630 views

How do I copy formatted formula results in Google Sheets?

I have formula cells outputting numerical results that are then formatted with a " km" (kilometres) post-fix. For example, the formula returns 21.4, which is then formatted as 21,4 km. I ...
Julius's user avatar
  • 243
2 votes
1 answer
39 views

Script that reads rows and columns in a sheet and returns strings with frequency of occurrence

first time caller! I have a single column that contains comma separated names (lots of names) -- I am trying to find a script that can read the comma separated string entries in cell and give me a ...
dkay's user avatar
  • 21
2 votes
0 answers
24 views

Customize point style in scatter chart [Google Sheets]

Is there a way to add new point shapes (import new symbols) in Google Sheets charts? Also, can I customize there color fills, or remove the infill color?
Kajo's user avatar
  • 121
2 votes
2 answers
688 views

Running average of the last [n] values in a column

I'm looking to create a formula in Google Sheets that will automatically calculate the running average for values in G2:G, based on the most recent n rows. I want to calculate this across three ...
troyt's user avatar
  • 21
2 votes
1 answer
246 views

Matrix multiplication-esque concatenation?

I'm making a simple recipe maker in google docs, using row vectors and sumproduct I can get it to calculate the protein and calories for a given recipe. The way it's set up it can be hard to read the ...
Greggan's user avatar
  • 21
2 votes
1 answer
168 views

Google Sheet Script for automatically entering date/time stamp when the adjacent column

I've found a script on here for entering a timestamp in a column based on edits to the adjacent column: Date and time stamp automatically added in column if(e.source.getActiveSheet().getName() == &...
Alan's user avatar
  • 21
2 votes
0 answers
417 views

How does one automatically generate a hyperlink for a cell containing a path?

I'm familiar with the HYPERLINK formula in Google Sheets to create links in a column, using a value in separate column as seen in this screenshot in row #2. I want the user to have the ability to ...
Anil's user avatar
  • 121
2 votes
1 answer
213 views

Trying to pull Duplicate data from multiple columns

https://docs.google.com/spreadsheets/d/1VSiKZbD_PoQTfbav7spalkyBgrt4T8juaLlKOM7LgLM/edit#gid=0 I have two columns, A and B. Throughout this project, the same name will appear in each column. I want ...
user247176's user avatar
2 votes
0 answers
431 views

How to Group Set of Rows & Skip One Every Time

I'm currently working in a Google Sheet that has about 12,000 rows. Because of the layout, I want to group every 5 rows, then skip one, then group the next 5. Right now, I have as script that is ...
Anthony Madle's user avatar
2 votes
0 answers
1k views

Get page number and page total (eg. Page 1 of 4) into the footer of a google sheet when exporting as PDF

In Google Drive Sheets you can use the custom header and footer tool to add custom headers and footers when you export a spreadsheet as a PDF. I would really like to get into the footer a statement ...
sam's user avatar
  • 6,989
2 votes
1 answer
555 views

How do I copy data from multiple Google sheets and paste into master Google sheet while maintaining format?

Each day I paste assignments to employees in each employees respective sheet. These assignments have hyperlinks in portions of the data. At the end of each day I copy their assignments for that day ...
Garrett Boulware's user avatar
2 votes
0 answers
203 views

Create a pivot table with a column for each assignee, containing rows of task descriptions

I have a two-column table with the first column being "task description" and second column being "assignee". This is something I'm unable to influence. Crude representation: I'd like to be able to ...
Puzomor Croatia's user avatar
2 votes
2 answers
201 views

Google Sheets SUMPRODUCT and IFS on different sheets

=ARRAYFORMULA(SUMPRODUCT((Products!B2:B=D2)*(IFS(H2="x",Products!D2:D,I2="x",Products!E2:E,J2="x",Products!F2:F)*(E2:E)))) I have a spreadsheet with an Orders sheet and a Products sheet. Product ...
Jeremy's user avatar
  • 121
2 votes
1 answer
53 views

Displaying specific column header for cells, based on the cell value and its row header

I have a very large table looking somewhat along the lines of the below example: Date1 Date2 Date3 Date4 Date5 ShiftA ShiftB ShiftA ShiftB ShiftA Person1 x x x ...
Alexander Mattsson's user avatar
2 votes
1 answer
114 views

How to make a cell autopopulate the date dependent on another cell

I'm trying to make a recruitment sheet for an online community I help run. What I'm currently attempting to do is that when we record a person has joined the Discord to column B, it'll automatically ...
Nomadik's user avatar
  • 43
2 votes
0 answers
54 views

How to use COUNTIF and UNIQUE

I have a Google Sheet with two columns. One containing a list with order numbers - the order numbers can be listed several times/duplicates. The other column consists of the a free text with the name ...
Andreas's user avatar
  • 21
2 votes
1 answer
3k views

google sheets checkbox conditional formatting

I have a schedule where I use inspector name, job number and then checkboxes to book the inspector on the visit date What I'm looking to do is if an inspector is booked in a job on lets say jan 3rd ...
Pablo Briones's user avatar
2 votes
0 answers
1k views

Google Sheets: Filter Row if it does not contains HYPERLINK

I have a sheet which has a column where most cells contain a hyperlink, I am looking for a way to find cells which do not contain a hyperlink and delete those rows. Filter by condition -> Text ...
user235827's user avatar
2 votes
1 answer
2k views

Auto sorting by multiple columns in Google Sheets

I have a data-set which I would like to have sorted with first column 6 and then column 7. From this post: How can I make some data on a Google Sheets auto-sorting? I have gotten ideas about making a ...
Jesper Homann 's user avatar
2 votes
1 answer
210 views

how to get max high of the given days excluding weekly off days

Can any one help how to get maximum high of given days excluding weekly off days (sat/sunday) from google finance. I am using formula like: =query(GOOGLEFINANCE("NSE:"&Y3,"high",(TODAY()-55),...
Prodip 's user avatar
2 votes
2 answers
712 views

Conditional Formatting in Google Sheets

The shorter question to spare you from reading more than you need to I have a few different formulas for use in conditional formatting with custom formula in Google sheets. They are all roughly 1100 ...
superluminal's user avatar
2 votes
0 answers
117 views

Custom number formats in Google Sheets: Issue displaying negative numbers

Context: Looking to display large numbers used in financial reporting & analysis in an abbreviated easier-to-read format. I currently have the GS custom number format as follows: [>=999950]#,...
ElBel's user avatar
  • 121
2 votes
1 answer
371 views

Conditional Formatting based on another sheet, tying a value to a colour, Google Sheets

Does anyone know how I'd be able to make a specific value always appear with the same formatting & colour in my sheet, based on another tab in the sheet? The spreadsheet I'm trying to do this on ...
Katie S's user avatar
  • 31
2 votes
1 answer
1k views

Importing data into Google Form response sheet with date, list, grid

I have a normal Google Form that is filling answers into a response spreadsheet but I want to import pre-existing data stored on another Google Sheet (100 entries) that match the Google Form responses ...
Taylor Lewis's user avatar
2 votes
1 answer
413 views

In google sheets, want to copy cell values from 1 sheet to another if a specific cell has any text

I have this working well: =if('Lee''s sheet'!$AP2="x",'Lee''s sheet'!B2,"") which will populate cell values into a cell, from 1 sheet to another if there is an "X" marked in column AP. I want to ...
Johnny Two Times's user avatar
2 votes
0 answers
2k views

How can I create a violin plot in Google Spreadsheets?

How can I create a violin plot in Google Spreadsheets? Example of violin plot:
Franck Dernoncourt's user avatar
2 votes
1 answer
189 views

How to generate standard competition ranks (not dense) for my Google Sheet?

What I have is a leaderboard sorted according from high to low scores. I would like to assign standard competition ranks (1224) formulaically. All 7 people who have the highest score (30) should be ...
Rahul V J's user avatar
2 votes
0 answers
81 views

IMPORTRANGE, where range shifts often, to different spreadsheet

What I have: a client information spreadsheet that contains company information including their last order date following the status of their account (active/follow-up). What I need: the last order ...
lily.v.n.'s user avatar
2 votes
1 answer
655 views

Google Spreadsheet sum when filtering

I have spreadsheet that looks like this: Now when I filter on a company I want the total to display the sum of the whole of B column. Since I am very new to spreadsheets I was hoping someone could ...
Marc Rasmussen's user avatar
2 votes
1 answer
107 views

Reference formulas from one sheet into multiple sheets

I would like to reference (pull in) formulas (not the result of the formula) from one sheet into multiple sheets so that they calculate on the sheet they are referenced. For example, if I have =...
Rwhite118's user avatar
2 votes
0 answers
85 views

Google Sheets: move a chart and still have it update from original data/arrays?

Can you move or copy a chart in Google Sheets into another EXISTING sheet and have it still update form the original data arrays? I know you can move a chart into its own sheet, but I'm looking to ...
hrrywllm's user avatar
2 votes
2 answers
20k views

How can you disable "Show Edit History" for a single cell?

I turned on Show Edit History for one cell in a Google Sheet and now I want to turn it off for that one cell. I can't see any way to do so. If I right-click on the cell I see Show Edit History but it ...
paulSFO's user avatar
  • 41
2 votes
2 answers
1k views

Google Sheets formula for unique values across columns

I'm familiar with the UNIQUE function in Google Sheets and how it works on rows (see Examples 1 & 2 below). Is there a function in Google Sheets that operates similarly on columns? In Example 3 ...
Robbie's user avatar
  • 180
2 votes
2 answers
53 views

Auto Sort different ranges

I have a google sheet that I would like to sort different ranges by the 2nd column in each range. Example: A B C D E F G p 7 1 h 9 1 q 6 2 j 3 2 g 9 3 p 7 3 I need the range A:C sorted by B ...
Erin's user avatar
  • 21
2 votes
1 answer
903 views

Google Sheets countif attendance sheet, quarterly report

I have a Google Sheet that I use to track attendance at an open lab that I run. Each quarter I put together a report where I count the number of people that attended that quarter. If I just use the ...
amidoingthisright's user avatar
2 votes
1 answer
3k views

Check for regexmatch in multiple ranges in Google Sheets

I have a sheet that auto-populates with a sub-set of articles (each a row with a column representing various information such as title, year of publication, author name, etc...) on the basis of a set ...
Shadow43375's user avatar
2 votes
2 answers
849 views

Google Sheets countif range with date/timestamp

I have a lot of requests coming in with the following timestamp (Created time Column) and I would like to quickly be able to count how many requests came in during each hour. I found I can get a code ...
Cheryl McCann's user avatar
2 votes
1 answer
413 views

When I insert a row, indirect does not update

I have a summary sheet and I would like that every time I add a new sheet everything gets referenced to the summary sheet. I have used indirect to do this, the only flaw is that once I add a row, the ...
Yasmeen Elladen's user avatar
2 votes
1 answer
3k views

How can I change the formula of one cell based on the value in a different cell?

COLUMN D: Dropdown list of values COLUMN E: IF function returning different values depending on COLUMN D contents I have the functionality described above working properly. My problem is that I want ...
headroom's user avatar
2 votes
1 answer
40 views

Adding mm:ss times when expressed as TEXT

I have a series of numbers that, for a reason out of my control I can not change, are expressed as plain text strings on google sheets. The numbers are supposed to be minute second. mm:ss. For the ...
Wish to remain Anonomous's user avatar
2 votes
1 answer
532 views

SUMPRODUCT inside ARRAYFORMULA in Google Sheets

I have a SUMPRODUCT equation that works fine on one row: =SUMPRODUCT(O4:O10-O3:O9,E2-P4:P10,N(E2>P4:P10)) However, instead of copying it for each E row I want to use ARRAYFORMULA to automatically ...
oh wow's user avatar
  • 21
2 votes
2 answers
496 views

How do I get the count number of weekdays in a datetime column in Google Sheets?

I have a timestamp in mm/dd/yy hh:mm:ss format from January to March. How do I count "how many days are Monday" in the range of A1:A199? (Or How Many are Tuesday, Wednesday, Saturday? This gives me a ...
rosa's user avatar
  • 85

1 2
3
4 5
36