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,790
questions with no upvoted or accepted answers
2
votes
1
answer
62
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 ...
- 21
2
votes
2
answers
659
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"
...
- 33
2
votes
2
answers
90
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 ...
- 31
2
votes
0
answers
680
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 ...
- 121
2
votes
1
answer
316
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 ...
- 21
2
votes
2
answers
274
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
...
- 21
2
votes
1
answer
594
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 ...
- 243
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?
- 121
2
votes
1
answer
664
views
Average the last N values in a Sheets column using Arrayformula
Experts, I need your help...
I'm looking to create a formula (not necessarily an arrayformula), that will automatically calculate an average. This sounds easy, but surprisingly, it isn't.
Let me ...
- 21
2
votes
1
answer
240
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 ...
- 21
2
votes
1
answer
164
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() == &...
- 21
2
votes
1
answer
211
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 ...
- 21
2
votes
0
answers
399
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 ...
- 120
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 ...
- 6,919
2
votes
1
answer
537
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 ...
2
votes
0
answers
197
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 ...
2
votes
2
answers
198
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 ...
- 121
2
votes
1
answer
52
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 ...
2
votes
1
answer
113
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 ...
- 43
2
votes
0
answers
53
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 ...
- 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 ...
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 ...
- 21
2
votes
1
answer
1k
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 ...
2
votes
1
answer
198
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),...
- 19
2
votes
2
answers
703
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 ...
- 129
2
votes
0
answers
114
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]#,...
- 121
2
votes
1
answer
369
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 ...
- 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 ...
- 21
2
votes
1
answer
412
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 ...
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:
- 18.3k
2
votes
1
answer
186
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 ...
- 21
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 ...
- 21
2
votes
1
answer
593
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 ...
- 121
2
votes
1
answer
103
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 =...
- 21
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 ...
- 21
2
votes
2
answers
19k
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 ...
- 41
2
votes
2
answers
989
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 ...
- 180
2
votes
2
answers
52
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 ...
- 21
2
votes
1
answer
884
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 ...
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 ...
- 455
2
votes
2
answers
810
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 ...
2
votes
1
answer
403
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 ...
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 ...
- 31
2
votes
1
answer
38
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 ...
2
votes
1
answer
556
views
Google Form response is modifying a cell on another row of linked sheet
I have a sheet that is recording form responses on a linked Google sheet. The issue is that sometimes the form will modify a cell on the row above in addition to the row that is being inserted (see ...
2
votes
1
answer
528
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 ...
- 21
2
votes
1
answer
9k
views
Change default zoom on a Google Sheet?
I use a shared Google sheet to display apparatus issues for a fire department. This is displayed on a tv using an Asus Chromebit. Every time someone logs into the sheet it resets the zoom to 100% ...
- 21
2
votes
2
answers
486
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 ...
- 85
2
votes
1
answer
393
views
Google Forms sending me duplicate email on automatical email
So, I've used this code, just reworked for my needs:
Can Google Forms send emails automatically?
The problem I have is that the account that sends those emails also receives them. Is there a way to ...
- 21
2
votes
1
answer
1k
views
Run a Script When Tab Programmatically added to a Google Spreadsheet
I'm trying to pull data from an Adwords report to a Google Sheet through an Adwords script. This will be done on a daily basis. Every time the Adwords script runs, a new tab is created (and an old one ...
- 21