New answers tagged google-sheets
0
votes
Accepted
Fill a column with dates for two days per week
To get Mondays and Thursdays in the next 4 weeks starting on 2023-10-02, use this:
=let(
dates, sequence(4 * 7, 1, "2023-10-02"),
filter(
to_date(dates),
match(weekday(dates, ...
- 12.6k
0
votes
FLATTEN data from SPLIT without helper columns
TOCOL
Google Sheets has now added the TOCOL function which transforms an array or range into a single column.
Using TOCOL instead of FLATTEN greatly reduces the length of this formula because it can ...
- 2,984
1
vote
Adding (and updating) time stamp when cell color is changed in Google Sheets
The execution log of the script project contains this error:
Error: Please do not run the script in the script editor window. It runs automatically when you hand edit the spreadsheet.
The error ...
- 12.6k
1
vote
Google Sheets, how to sort query results manually (Not in query)
Keep your primary sheet as a main "level 1" sorting logic and
querying sheet.
Use ORDER BY in your first sheet.
Hide your first sheet.
Then pull your data to a second sheet with a simple ...
0
votes
Clear cell value if cell in adjacent column is blank
A Jedi’s strength flows from the documentation
Delete data from cells it may not; only return empty or filled values, Google Sheets can. 1
This is the way 2
1 Yoda 2 Din Djarin
...
- 2,984
0
votes
Is there away on google sheets to paste text and automaticly create new rows with every line break or etc?
Basic Formula
=SPLIT(A2, ";")
Will SPLIT the string in A2 into a new column at each ;
Multi-Row Formula
=BYROW(A2:A6, LAMBDA(r, SPLIT(r, ";")))
Will SPLIT the strings in ...
- 2,984
1
vote
Looking up two criteria in a table, one vertical, one horizontal and finding a value
Basic Formula
Works for a single date/row.
=INDEX(range,
MATCH(name, nameCol, 0),
MATCH(date, dateRow, 0))
Error Handling
Add the IFNA function to hide errors when no value is found.
=IFNA(...
- 2,984
0
votes
Is there away on google sheets to paste text and automaticly create new rows with every line break or etc?
This sounds like my question, but I admit to not understanding the answers given. I had selected and copied ctrl-c about 20 lines from a Google calendar invite - a list of procedure steps. I wanted ...
0
votes
Alternate to nested if statements (alternate posing of question)
My answer is a variation on @erik-tyler's answer
Using FILTER
=JOIN(" ", INDEX(FILTER({C2:T2; E2:V2}, 1/(1/E3:V3)>0),,1))
Your approach to organizing data works against you. You could ...
- 2,984
3
votes
Insert space after every character of a text string
I solved my problem with the following formula:
=TRIM(
REGEXREPLACE(
A1, "(.)", "$1 "))
Credit to @Erik Tyler for suggesting the TRIM function which provided a much ...
0
votes
Formula that looks for any matching substrings between two cells
@doubleunary's answer is a good one.
The approach below uses SPLIT to convert the individual cells into arrays of substrings
Substrings to Arrays
=LEN(
FILTER(
SPLIT(A1," "),
...
- 2,984
2
votes
Accepted
Formula that looks for any matching substrings between two cells
One way to do that is to create a regular expression, like this:
=regexmatch(A1, substitute("\b(" & B1 & ")\b", " ", "|"))
When B1 is foo bar, the ...
- 12.6k
0
votes
Formula to autofill value from a list on a separate sheet
The accepted answer works well. I show a couple of alternatives below as well as VLOOKUP Single-Formula which extends the approach in the accepted answer to calculate the results for multiple rows.
...
- 2,984
1
vote
Making a bubble chart in Goggle Sheets for year-on-year comparison - ie year versus month?
As I've indicated in the comments, I don't think you can easily get a bubble chart that meets the requirements in the picture because of scaling issues, however to get the general layout requested, ...
1
vote
Accepted
Google Sheets complex formula or script is needed
Use rows() and filter(), like this:
=if(
and(
rows(filter(B4:B6, len(A4:A6))) = rows(filter(B4:B6, B4:B6 >= 100%)),
rows(filter(B8:B10, len(A8:A10))) = rows(filter(B8:B10, regexmatch(...
- 12.6k
2
votes
XPath Query to Retrieve Element Value and Name
Things to have in mind about using xPath on IMPORTXML
It supports xPath 1.0. An old version of the IMPORTXML help article mentioned the supported version; the current version points to a third-party ...
- 43.9k
2
votes
Accepted
XPath Query to Retrieve Element Value and Name
Alternatives
Here are alternative approaches to get the data in your preferred layout:
IMPORTDATA
=IMPORTDATA(url, delimiter)
Imports from a URL in CSV or TSV format.
=LET(url, "https://home....
- 2,984
0
votes
Accepted
onEdit updates correctly, but then overwrites following columns after pasting (editing) multiple rows
The Range.offset() function returns a range that is the same size as the original range, rather than a single cell or column, unless you specify optional parameters. You can specify those optional ...
- 12.6k
2
votes
Accepted
I have multiple unequally sized rows that I want to combine into a single matrix. How can this be done?
You could try something as simple as =tocol(code_f3!A1:ZZZ,1) to put all the results (minus blanks) into a single column. ZZZ is the maximum number of columns Google Sheets allows (18278).
1
vote
How can I find the first empty cell in a row?
Needed a formula that could figure out the sum of a list of numbers in the same column below the current cell up to the next blank cell, came up with this, I know this is an old post but I hope it ...
- 11
0
votes
How can I retrieve a 2D array by its header?
Use filter(), like this:
=filter(B2:D, A2:A = E2)
See filter().
- 12.6k
1
vote
Excel style table in Google Sheets
To implement something like Excel's Structured References, try this: The following Apps Script will add a function to created Named Ranges in the format SheetName_ColumnHeader for the contiguous range ...
- 21
0
votes
Accepted
Runner's time generates error when divided by distance run regardless of the number format
Rubén is correct you are entering values that don't match a valid number format so Sheets is interpreting them as text.
When you then perform your division calculation on the text Sheets tries again ...
- 2,984
0
votes
Stuck with a google sheets IF formula for generating boolean strings
Use filter() and join(), like this:
=join(" OR ", filter("""" & B8:B & """", len(B8:B)))
See filter() and join().
- 12.6k
0
votes
Google Form Information and Google Sheets
Choose Insert > Sheet and copy form responses to the new sheet with an { array expression } like this:
={ 'Form Responses 1'!A1:N }
You can then use plain vanilla spreadsheet formulas in the new ...
- 12.6k
1
vote
Runner's time generates error when divided by distance run regardless of the number format
Enter 00:09:07.7 as in Google Sheets, duration values should include hours.
You might also have to set the cell format to duration as Google Sheets might show durations as time by default.
- 43.9k
0
votes
How to specify the entire sheet as range in Google Sheets?
=CELL("address",Sheet2!A:A)&":"&ROWS(Sheet2!A:A)
references Sheet2 as a whole.
It returns a string like Sheet2!$A$1:1000 .
This means address of Sheet2 all rows (1000 in ...
- 212
1
vote
Accepted
How can I query columns instead of rows in a =QUERY?
One option is to use TRANSPOSE. This function change columns to rows.
NOTE: When feeding QUERY with an array of values, like the result of transpose, instead of using the column letters as field names,...
- 241
0
votes
Accepted
Hiding imported data in Google Sheets
Import all the data into a new 'Import' sheet, and refer to that sheet in your formulas. Then hide the 'Import' sheet.
Note that a determined user can always discover all contents in a spreadsheet ...
- 12.6k
0
votes
Using multiple IF and OR statements with calculations
Two Approaches
Reverse Order
A more efficient approach that is based on your current IF function formula. Included a version for (a) the IFS function, and another with (b) multiple IF functions.
...
- 2,984
0
votes
Return last value that isn't an error in each row
Another Approach
Joel's solution works. This is simply an alternative.
Basic Formula
=INDEX(r,,XMATCH("*",IFNA(r),2,-1))
In the Basic Formula, replace r with a reference to a single row of ...
- 2,984
2
votes
Accepted
Change cell color if data is changed from "initial value" in Google Sheets?
Try an onEdit(e) function simple trigger, like this:
/**
* Simple trigger that runs each time the user manually edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function ...
- 12.6k
1
vote
Automate range name creation in Google Sheets
Give this a whirl. It takes the DataRegion of your current selection (basically the contiguous used area you're in) and makes a Named Range for each column in the format sheetName_header.
function ...
- 21
1
vote
How to filter combined search outputs with OR logic
To search each row in REFS!A3:AA for partial and case-insensitive matches of any of the text strings in AA1:AD1, use regexmatch(), like this:
=ifna(
filter(
REFS!A3:AA,
regexmatch(
...
- 12.6k
1
vote
How to shuffle a range but prevent refresh?
Assuming the range to shuffle is in A2:C, put this formula in cell H2:
=let(
refresh, H1,
freeze_, lambda(_, _),
randomNumbers_, lambda(r, c, randarray(r, c)),
freeze_(randomNumbers_(999, ...
- 12.6k
0
votes
Cumulative Percentage Subtraction Google Sheets
This is an excellent candidate for the REDUCE function.
Core Formula
=REDUCE(score, SEQUENCE(absences),
LAMBDA(s, a, s*(1-a*pct)))
Exception Handling (Shortform)
=IF(score>0, IFERROR(
[...
- 2,984
0
votes
How to filter combined search outputs with OR logic
Not Search
SEARCH locates the position of a text string within another text string, not a value in a range.
Example:
=SEARCH("bob", "jimbob")
=4
Solution
To FILTER a range based ...
- 2,984
0
votes
How can I replace blank cells with zeros in Google Spreadsheet?
Simpler Formula
Since you are dealing with numbers and blanks, the accepted answer =IF(ISBLANK(A3),0,A3) is more complex than necessary.
Simply adding zero to the cells will achieve the same result:
=...
- 2,984
0
votes
How can I replace blank cells with zeros in Google Spreadsheet?
Select an empty cell -> "filter by cell value" (it will only display empty cells in the column) -> fill in the value you need in the first cell -> drag it down all the way to the ...
1
vote
Accepted
Google Sheets: Change text color of multiple cells if checkbox is checked?
Use this conditional formatting custom formula rule for the range B2:D:
=$E2
See GimelG's explanation and Lance's explanation of how absolute and relative addresses work in a conditional formatting ...
- 12.6k
0
votes
Failure to autsort Google Sheets
The code in the question errors out with Exception: Cell reference out of range because range.offset(NUMBER_OF_HEADER_ROWS, 0) will always get a range that is partially outside the data range that ...
- 12.6k
0
votes
Add values in a row based on the date values in a sepperate row
Using SUMIF as suggested by @JohnSUN is a good approach and also slightly shorter (4 chars) than the alternative below.
Your original approach would work if you tweak it to FILTER row 4 based on the ...
- 2,984
0
votes
How do I change the number format for the horizontal axis labels in a histogram?
Strangely, in order for me to see this setting, I had to click the number on the axis. Only then did the setting to change the number format. From there, you can choose to use the format of the source ...
- 949
1
vote
0
votes
How can I group/count occurences of a column
You can use COUNTIF to count the occurrences.
After that, copy the count column as values.
Last, remove the duplicates
- 111
1
vote
Creating rule for two cells equaling same value
Use this conditional formatting custom formula rule for the range A1:D:
=isnumber($C1) * (max($C1:$D1) <= 0)
- 12.6k
0
votes
How to use nested IF statements in Google Sheets?
You were close you just needed to nest your IFS functions in IF functions or an additional IFS.
In both formulas below, 1 is a shorthand for TRUE for the last IFS condition.
Nesting in Two IF ...
- 2,984
0
votes
Filter function with multiple variables google sheets
Filters values in A2:A6 if either condition is true:
=FILTER(A2:A6,
(B2:B6<B10)+
((C2:C6<B10)*(D2:D6="Y")))
In the FILTER function conditions:
+ is equivalent to OR
* is ...
- 2,984
1
vote
How do I ignore a few rows below the header (including header) when sorting data in sheets?
One easy workaround would be to move the notes above the label row so that it is no longer part of the filtered area.
I do not think there is any turnkey way to keep notes where they are without ...
- 12.6k
Top 50 recent answers are included