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,681
questions
2
votes
2
answers
44
views
How to automatically add 00 minutes to hours in Google Sheets?
We have cells that store time in the H:MM format (24-hour time without a leading zero for hours). However, I don't like typing values such "8:00". Ideally, I'd like to type just a single ...
- 123
-2
votes
1
answer
87
views
Google Sheets double click fill down not working
If I select a cell and then double-click on the blue dot at the bottom-right of the cell, the fill-down function does not work, nothing happens.
Sometimes it does work, in other cases/columns.
The ...
- 183
1
vote
3
answers
36
views
Google Sheets Query: Selecting column, can only order descending
The following query works for me in google sheets:
=QUERY(A3:A,"SELECT A order by A desc")
Changing desc to asc however does not, instead returning #REF!:
=QUERY(A3:A,"SELECT A order ...
- 119
0
votes
1
answer
30
views
QUERY function to return records with multiple criteria
I have a dataset on Google Sheets similar to below:
Name
Team
Account
Date
Jennifer
East
Meta
5/1/2023
Steve
East
Alphabet
4/1/2023
Claire
West
Google
4/23/2023
Roger
West
Apple
6/23/2023
Bob
...
- 19
1
vote
1
answer
23
views
Conditional Formatting to Highlight Cells in Row if any number is present, but NOT if a formula or blank cell is present
I am looking for a conditional formatting rule that will highlight cells in a given row that contain a number only, but NOT a formula and NOT blank cells.
Example of the sheet:
Number of cases ...
- 11
1
vote
1
answer
22
views
Google Sheets QUERY function to return records with multiple filter criteria based on cell reference
I have a dataset on Google Sheets similar to below.
Name
Team
Account
Date
Jennifer
East
Meta
5/1/2023
Steve
East
Alphabet
4/1/2023
Claire
West
Google
4/23/2023
Roger
West
Apple
6/23/2023
Bob
...
- 19
-1
votes
1
answer
43
views
Apply the same conditional formatting custom rule to multiple ranges on the same sheet
I have created a weekly tracker on Google Sheets, with the days organized in columns K through AI. Each day has three columns dedicated to tasks, assigned personnel, and notes. I have successfully ...
- 9
-1
votes
1
answer
33
views
How do I filter text in a column in google sheets?
I have a column that contains text and numbers in the same cell.
I want to return all the rows from a column that contain both the number 1 and the number 2.
I tried using =regexmatch(E1,"1|2&...
0
votes
2
answers
83
views
Google Sheets, multi row tabs at bottom or locked tab
Is there a way to have multiple rows of tabs for the sheets at the bottom of the editor? Or failing that, is there a way I can lock one tab so it is always available and never "slid off the ...
- 101
1
vote
2
answers
40
views
Highlight duplicates from four or more separate range in the same sheet
I want to highlight duplicate value in four separate range for a lessons schedule using conditional formatting in google sheets, in this example there are four classes,
what i'm trying to achieve are ...
0
votes
2
answers
179
views
What happens when you share a Google Doc with a non-Google user? What do you have to do to make it work? [closed]
If you create a Google Doc, click Share and then enter a non-Gmail-address and uncheck "Notify user" (I don't want to share the link using clear text e-mail in this specific case), ...
- 679
1
vote
1
answer
23
views
How do I conditionally format all cells that begin with an equals sign? [duplicate]
Is it possible to use conditional formatting in Google Sheets to only format cells that are formulas (i.e. begin with an = sign)? For example, I would like a cell containing =F2*30 to be highlighted ...
- 113
-1
votes
1
answer
29
views
How do I use formulas to find the midpoint and other milestones between two dates?
I am trying to make a Google Sheets document for work that will contain the date someone signed, the date that the wedding is occurring, when the first and second payments are due, and when to send ...
1
vote
1
answer
42
views
Can I use Google Sheets to help me with Mendelian Inheritance Tables?
I have a spreadsheet in Google Sheets that looks similar to the following:
Parent
Trait 1 A
Trait 1 B
Trait 2 A
Trait 2 B
Trait 3 A
Trait 3 B
A
0
0
0
1
1
1
B
0
0
0
1
0
1
It is meant to calculate ...
2
votes
2
answers
46
views
In Google Sheets, is it possible to collate + auto sort multiple columns of data into a single column?
I used this site to learn how to sort a two-column list, {string, number}.
I can't find a way to combine multiple two-column lists into a single, two-column list.
For example, in Major League Baseball ...
- 21
1
vote
2
answers
76
views
When sharing a spreadsheet in "view only," frozen rows are included in "Sort A-Z"/"Sort Z-A"
Resources:
You can see a copy of my spreadsheet in "View Only" mode here. To replicate my issue, just sort any column -- U is the one I've been doing.
You can find a copy of my spreadsheet ...
- 11
0
votes
2
answers
41
views
Google Sheets: how to use Conditional formatting to make cells with formulas bold, and cells with user-typed data normal
I'd like a conditional formatting rule to mark cells with formulas one way, and cells with user-typed values instead of formulas another way. This way you can quickly see which cells are filled by ...
- 365
1
vote
1
answer
42
views
How do I add values from different tabs into one table efficiently?
This is likely an easy feat for those how to work Sheets but I have no idea how to do it myself. I am looking at a quarterly report of airline complaints, where the source publishes one csv per ...
- 13
-1
votes
1
answer
25
views
Can someone help me with how to group by the incidents in this data for each day [closed]
datetime
instance_id
2022-04-12 7:09:10 UTC
9e27bf765fdc2986c2f92c6919872380
2022-04-12 7:07:59 UTC
d96f725b82f1c12f177e9d1b9c1bb643
2022-04-12 7:07:50 UTC
...
0
votes
1
answer
33
views
How can I use COUNTIFS to compare different date formats?
I'm trying to calculate this for a column.
=countifs(another_sheet!E:E, A2,
datevalue(another_sheet!A:A),datevalue(B2))
another_sheet!A:A has timestamps that look like 4/23/2023 14:13:43
Whereas ...
- 123
3
votes
2
answers
73
views
How can I maintain leading zeros in a formula?
I have an array formula as follows:
=ARRAYFORMULA("NUMBER-"&SEQUENCE(33,16,1,1))
The array of numbers from SEQUENCE run from 1 to 528, but I want all the numbers to have matching ...
- 31
0
votes
2
answers
127
views
Google Sheet to fill in the inflation numbers?
Is it possible so that, in the year 1992, I put in a salary of $38,000, and in the year 2023, I put in the salary of $123,000.
Now in between 1992 and 2023, there are many rows, for the year 1993, ...
- 147
-2
votes
2
answers
34
views
How do I copy names marked with an asterisk * from one column to another another?
I've got a list of student names in Column A. I need to copy the names that are followed by an asterisk * (I've highlighted them in the screenshot) into the * Seniors column D. Is there a formula I ...
- 1
-1
votes
2
answers
78
views
Google apps script or formula to solve convert to proper case, with exceptions
CLARIFICATION: I asked for a script because there are some other things I'm doing as well and I figured I would combine them all in a single script. But if a formula is the best way to do it, I can ...
- 1
1
vote
0
answers
32
views
How can I link many addresses using a Google Sheets formula to create a Google Maps URL? [duplicate]
This answer showed how to link two addresses with a formula and open a route with a Google Map link:
=hyperlink("https://www.google.com/maps/search/?api=1&query=" & D3 & " ...
- 11
0
votes
1
answer
62
views
Search an entire Google Sheet for a value and return true/false
I need to search every sheet in a Google sheet for a value - currency - and return true if it's found. The code below is returning false for everything
function searchForNumber(value) {
var sheet = ...
0
votes
1
answer
63
views
Query Problem: Function QUERY parameter 2: NO_COLUMN: Col64
I have been using this formula in a document for quite some time and have had no problems with it. Now however when I opened it up, it suddenly stopped working. When I revert the document to an old ...
1
vote
1
answer
26
views
How to apply or change a dropdown choice on multiple cells in google sheets
Here I am trying to apply the "Replied" choice for the selected range of cells instead of choosing one by one for each cell:
1
vote
1
answer
59
views
Unable to create an ARRAYFORMULA that will AVERAGE the results of a QUERY
I have a sheet data that tracks client transactions.
Each row is a transaction
The columns are:
LAST NAME
PHONE NUMBER
SUB
TOTAL
TIP
PERCENTAGE
The TIP and PERCENTAGE columns are calculated based ...
- 11
0
votes
1
answer
56
views
How to apply a custom color scale to a column in Google Sheets based on the values in another column?
I have a spreadsheet with two columns: column I contains numeric values, and column H contains strings. I want to apply a custom color scale to column H, but the colors in the color scale should be ...
- 109
-1
votes
1
answer
37
views
How to get total event time of Google Calendar events into Google Sheets based on certain conditions?
In my company we work with project sheets where each project lead maintains an overview of the spent hours (compared to total hours) of each project, per person working on the project. We use ...
- 137
0
votes
2
answers
50
views
7x3 dataset - How do you create unique sets in Google Sheets?
Here's my google sheet
B3:D9 is a simplified dataset. I need a formula to create the unique sets in F3:H6.
Sample Data
B
C
D
Rock
Green-1036
Paper
Red-994
Rock
Brown-781
Scissors
Yellow-1271
...
- 33
0
votes
1
answer
1k
views
Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: D
I have data in a sheet called "PASTE HERE" that I want to import into another sheet in the same workbook. I don't want to use IMPORTRANGE because then copying the workbook would necessitate ...
- 1
2
votes
1
answer
46
views
IF function condition with REGEXMATCH returns wrong result
The value of the cells are:
D
F
R
Marco Marques (+19091234567)
06/19/2023_Purchase: (Footlocker) Spiderman All Terrain Clog Size 12
win
The output: Not Counted
I don't understand why it returns &...
- 21
2
votes
1
answer
107
views
How to apply ArrayFormula on a RegexReplace Query formula so that it autopopulates for all values in Column A?
I'm trying to create a single string for each product that includes different unit prices tied to purchase quantity. For example, a product might have a unit price of £10, but the unit price drops to £...
0
votes
1
answer
105
views
Clear cells based on checkbox repeated Google Sheets
I have checkboxes in columns A, E, H, L, O, S, V, and Z
I have a script that is working which allows me to check the box in a2 and it deletes data in e2 and f2. I then am able to reverse the script so ...
- 1
0
votes
0
answers
21
views
How can I lock images in Google Sheets so they can appear on the same positions despite different screen resolutions?
Currently, I'm doing a floor map template for my company, so they can easily update it every time each team is assigned to different desks.
For this, I resized all cells with the same height and ...
2
votes
2
answers
47
views
In Google Sheets, how to achieve the “unwinding” of rows in a table that contains columns with multi-valued data
In Google Sheets, how can I achieve the following “unwinding” of rows in a table that contains columns with multi-valued data:
Column A
Column B, multivalued
First
A,B
Second
C,D
It transforms the ...
- 150
1
vote
1
answer
309
views
How to protect a range in Google sheet using a macro from Google Apps script?
I recorded a macro using the traditional way (GUI) to protect a range. The following script was generated.
function protect() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('...
- 121
0
votes
1
answer
28
views
How to add a secondary vertical axis to a timeline chart in Google Sheets?
I have a timeseries chat plotting two different series, see here: https://docs.google.com/spreadsheets/d/1TGoHPodslxw01PVf8Yp4SI15saedY8iID3FegJg6lmg/
I would like to have 2 different Y-axes, one for ...
- 103
0
votes
1
answer
81
views
How do I craft a URL to open/display a google sheet filtered by a certain parameter?
Sample Google Sheet:
NAME
AGE
COLOR
Robert
22
Red
John
21
Blue
Ann
20
Purple
John
21
Green
Call with URL: http://{URL to Google Sheet above}/parameters
What parameters should I use here to ...
0
votes
2
answers
24
views
Google sheets - copy adjacent cells if value is identical
I have a table like this
Letter
Selected letter
Number
A
B
1
B
D
2
C
3
D
4
I'd like to have as a result:
Selected letter
Number
B
1
D
4
I tried to compare the two firsts columns to ...
- 101
0
votes
0
answers
19
views
Script to determine time after a dropdown status is changed (utilizing the edit history)
I have a Google sheet-based simple CRM for leads from Facebook ads. When I import leads into the sheet, I assign them the status of Raw in a dropdown column for status. My team then contacts them and ...
-1
votes
1
answer
38
views
Run existing macro on a new Google Sheet with least number of clicks
How to execute an existing macro (or any other kind of script) on a new Google Sheet with the least number of clicks/keystrokes?
Or even automatically when opening it?
Context: My bank sends me a new ...
- 8,704
0
votes
1
answer
22
views
Can I use responses of a previous google form in a new google form?
I have a Google form that is collecting the Event date, event name, employee name, and his/her assistance in that event(Yes/No). After collecting those answers, we want to use the data to create ...
- 1
3
votes
1
answer
28
views
Can I sort cells in Google Sheets with a key function?
I have some data in a Google Sheets document that I would like to sort by computing some value from two columns and using that as the sort key.
Say my data is like this:
A
B
C
D
1
...
...
8700
5
2
....
- 131
0
votes
1
answer
208
views
Google sheet doesn't recognize dates
In the below sheet Column A represents Date. But some dates not recognized by google sheets. For example from cell A5 to A51 not recognized. I copied Column A to Column G and changed the date format ...
- 21
1
vote
2
answers
36
views
Add cell value in query
When doing a multi-sheet query, I would like to bring in the value of a cell (A2) from each source sheet to a new column in the results.
The query:
=QUERY({CCL!A4:M; HOOD!A4:M; AAL!A4:M; BAC!A4:M;
...
0
votes
1
answer
50
views
Transpose Customer details
enter link description hereIn the below pic column A represents the Customer number and column B represents the customer details. I need to transpose the customer number and customer details as same ...
- 21
2
votes
1
answer
29
views
Recursively search cell for each value within an array and return position within array
I have a bunch of transaction descriptions, and I'd like to check against an array for any matches (case insensitive, in any position within the cell) and return the associated position within the ...
- 121