Latest posts tagged with #googlesheetstutorial on Bluesky
youtu.be/xBO_Z5IXSCc
Google Sheets Troubleshooting: Extend Existing DATEDIF() Formula Down a Column
Music courtesy of CORTEXX
open.spotify.com/artist/4fOVG...
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
The Freeze submenu in the View menu has an option to freeze up to the current Row or Column to keep everything until then visible as you scroll around a sheet.
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide #starwars
The Freeze submenu in the View menu has default options to freeze 1 or 2 rows and columns, making it very easy to keep a row or a column visible as you scroll around.
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
youtube.com/shorts/lt506...
With the SPLIT() function simply give it text and what to use as separator and you can split single-cell text into multiple cells.
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide #starwars
youtube.com/shorts/wYz_n...
Data / Cleanup / Remove Duplicates can be used as a trick to remove blanks from a column, so long as there is no actual data repeated
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
youtube.com/shorts/PrEzl...
Allow user input without seeing the formulas using 3 files: An input, formulas, and middle with IMPORTRANGE between them
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
youtube.com/shorts/PsK4B...
When you copy and paste a cell over a range each cell gets the exact value. To increment the value on each cell simply drag the fill handle!
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
youtube.com/shorts/NXiql...
Use COUNTA() as the input to the CHOOSE() index to easily return a value based on how many items are in a range.
Music courtesy of CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
youtube.com/shorts/nQqO7...
While there is a technical definition for a number being Even, Sheets provides the easy functions ISEVEN() and ISODD().
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
youtube.com/shorts/24YKk...
With a column of data generated by a formula where some of the data is incorrect, outdated, or just wrong use a helper column to override
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
youtube.com/shorts/YWeGS...
To highlight entire rows without checked boxes, join all the values in the row and check for the value TRUE in the joined value
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
youtube.com/shorts/BTVau...
Sometimes it makes sense to multiply all checkboxes in a row by a value. Do this with the BYROW() function surrounding COUNTIF()*val
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
youtube.com/shorts/wABxs...
You can easily sort a range on 2 columns at once by using the Advanced Sorting Options from the Data menu
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
youtube.com/shorts/ynGR4...
The Filter Tool on the Toolbar allows you to sort a range by a single column, either ascending or descending.
Music courtesy of CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
youtube.com/shorts/Snipy...
Simple calculations like [range]*-1 can simply be done down a column using the ARRAYFORMULA() function around the calculation.
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
youtube.com/shorts/5kJ-h...
Return 1 row per unique ID (the Rep in this case), the count of each, and the sum of service amounts, all in a single QUERY formula.
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
youtube.com/shorts/x8C2l...
HSTACK appends ranges together so MAP(LAMBDA(HSTACK(COUNTIF(),SUMIF()))) outputs 2 columns of formula output from a single formula!
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
youtube.com/shorts/UGTay...
Sheets automatically recognizes contiguous data for things such as new formulas and Alternating Colors
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
youtube.com/shorts/sXd47...
Use ISBLANK() in an IF() function to check if a cell is blank and value_if_false to do whatever you want to do if the cell isn't blank.
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
youtube.com/shorts/kOL5x...
=IF() evaluates 0 as FALSE and all other numbers as TRUE, so we can use the number returned from LEN() to do T/F if a cell contains data.
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
youtube.com/shorts/S27y3...
Create a Data Validation rule with a custom =COUNTIF() formula to only allow each number to be used once in a range
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide #starwars
youtube.com/shorts/_cZf1...
=LEFT(A1,FIND(":",A1)-1) returns everything before a given character. This works for any character and SEARCH() as well as FIND().
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide #starwars
youtube.com/shorts/1bbk5...
Use the construction =RIGHT(A1,LEN(A1)-FIND(":",A1)) to remove everything up to and including a particular character.
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide #starwars
youtube.com/shorts/8RPXM...
Google Sheets: Track Time Overnight
24 hours is equal to the number 1, so just add 1 to the Clock Out time if the out time is earlier than the in time.
CORTEXX
open.spotify.com/artist/4fOVG...
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
youtube.com/shorts/dqCMx...
Use negative offset_rows and _columns along with [height] and [width] to return a rectangular array of values.
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide #starwars
youtube.com/shorts/8Tzwo...
Sometimes you just need to send a link for someone to make a copy of the spreadsheet. To do so, just replace /edit with /copy!
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide #starwars
youtube.com/shorts/2iUQM...
When referencing a cell using sheet.getRange() the parameters are Row and then Column. So cell B1 is referenced as (1,2) in Apps Script.
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
youtube.com/shorts/HMY52...
Conditional Formatting lets you format cells if the date is after a certain date, which date can be created by formula such as =TODAY()+30
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide
youtube.com/shorts/RMjAO...
Google Sheets: Filter Every Nth Row
-----
Use MOD(ROW(),n) inside the FILTER() function to return every N rows.
Music courtesy of CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide #starwars
youtube.com/shorts/WmgCD...
The optional ignore_empty parameter in TEXTJOIN() makes it useful in some cases where JOIN() alone isn't.
CORTEXX
open.spotify.com/artist/4fOVG4v2qJ38qUbzA4KTY2
#googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide #starwars
