Last updated on Jun 23, 2026, 2:16 PM
| Problem Where It Works | Formula | Example | Use Case | YouTube |
|---|---|---|---|---|
| How do I analyze historical stock performance? | GOOGLEFINANCE Historical | =GOOGLEFINANCE("GOOG","close",TODAY()-30,TODAY()) | Historical prices | https://youtu.be/0-KVFAWoX5M?si=ohMjM1Cnc2ER6lOb |
| How do I calculate workdays with custom weekends? | NETWORKDAYS.INTL | =NETWORKDAYS.INTL(A1,B1,"0000011") | Custom workdays | https://www.youtube.com/watch?v=iHbMHlEDv5M |
| How do I calculate future business dates with custom weekends? | WORKDAY.INTL | =WORKDAY.INTL(A1,30,"0000011") | Business dates | https://youtu.be/1BcqNE7nwRA?si=sBFfISyEs-SVmJf1 |
| How do I calculate age in months between two dates? | DATEDIF | =DATEDIF(A1,B1,"YM") | Age/month calculation | https://youtu.be/5LbgjGAxzhw?si=E0gInOqWVRJt0Qfv |
| How do I display trend charts inside cells? | SPARKLINE | =SPARKLINE(B2:M2)' | Mini charts | https://www.youtube.com/watch?v=LAps3XqUB8o |
| How do I analyze the distribution of values into groups? | FREQUENCY | =FREQUENCY(A:A,{10,20,30,40}) | Distribution analysis | https://www.youtube.com/watch?v=YJ-KVogg0-o |
| How do I perform matrix multiplication in Google Sheets? | MMULT | =MMULT(A1:C3,E1:G3) | Matrix multiplication | https://youtu.be/O5GMndAUWSs?si=b2qkQUbD1zajtdg9 |
| How do I create pivot-style reports from horizontal data? | TRANSPOSE + QUERY | =QUERY(TRANSPOSE(A1:Z2),"SELECT *") | Pivot-like reporting | https://www.youtube.com/watch?v=h80wt_uhi4M |
| How do I count all records matching a specific value? | COUNTIF | =COUNTIF(A:A,"Paid") | Count matching values | https://youtu.be/rt8VZB-mytw?si=gbRSMyCF20mY54GH |
| How do I count records matching multiple criteria? | COUNTIFS | =COUNTIFS(A:A,"Paid",B:B,"Yes") | Multiple conditions | https://youtu.be/fmpAMoDiqIU?si=B3RkfBHoMtmQY0AV |
| How do I sum values for a specific category only? | SUMIF | =SUMIF(A:A,"Sales",B:B) | Conditional sum | |
| How do I calculate totals based on multiple conditions? | SUMIFS | =SUMIFS(C:C,A:A,"Sales",B:B,"East") | Multi-condition sum | |
| How do I find the average for a specific group? | AVERAGEIF | =AVERAGEIF(A:A,"Sales",B:B) | Conditional average | |
| How do I calculate averages using multiple filters? | AVERAGEIFS | =AVERAGEIFS(C:C,A:A,"Sales") | Multi-condition average | |
| How do I return different values based on a condition? | IF | =IF(A1>100,"High","Low") | Conditional logic | |
| How do I handle multiple conditions without nested IFs? | IFS | =IFS(A1>90,"A",A1>80,"B") | Multiple conditions | |
| How do I check if all conditions are true? | AND | =AND(A1>50,B1="Yes") | All conditions true | |
| How do I check if at least one condition is true? | OR | =OR(A1>50,B1="Yes") | Any condition true | |
| How do I reverse a logical condition? | NOT | =NOT(A1>100) | Reverse logic | |
| How do I hide spreadsheet errors from users? | IFERROR | =IFERROR(A1/B1,0) | Handle errors | |
| How do I display a custom message when no match is found? | IFNA | =IFNA(VLOOKUP(E2,A:B,2,FALSE),"Not Found") | Handle N/A | |
| How do I replace complex nested IF formulas? | SWITCH | =SWITCH(A1,"A",100,"B",80) | Replace nested IF | |
| How do I perform a modern lookup in Google Sheets? | XLOOKUP | =XLOOKUP(E2,A:A,B:B) | Advanced lookup | |
| How do I find a value using a lookup key? | VLOOKUP | =VLOOKUP(E2,A:B,2,FALSE) | Vertical lookup | |
| How do I search data arranged horizontally? | HLOOKUP | =HLOOKUP("Jan",A1:M2,2,FALSE) | Horizontal lookup | |
| How do I find the closest matching value? | LOOKUP | =LOOKUP(100,A:A,B:B) | Simple lookup | |
| How do I return a value from a specific row number? | INDEX | =INDEX(B:B,5) | Return value by position | |
| How do I find the position of a value in a list? | MATCH | =MATCH("John",A:A,0) | Find position | |
| How do I perform a more flexible lookup than VLOOKUP? | INDEX+MATCH | =INDEX(B:B,MATCH(E2,A:A,0)) | Flexible lookup | |
| How do I display only rows that meet specific conditions? | FILTER | =FILTER(A:C,C:C="Paid") | Filter records | |
| How do I create SQL-like reports in Google Sheets? | QUERY | =QUERY(A:D,"SELECT A,B") | Database-like query | |
| How do I sort data automatically by a column? | SORT | =SORT(A2:C10,2,TRUE) | Sort data | |
| How do I display only the top results from a dataset? | SORTN | =SORTN(A2:B100,10) | Top N results | |
| How do I remove duplicate values from a list? | UNIQUE | =UNIQUE(A:A) | Remove duplicates | |
| How do I convert rows into columns? | TRANSPOSE | =TRANSPOSE(A1:D4) | Swap rows & columns | |
| How do I apply a formula to an entire column automatically? | ARRAYFORMULA | =ARRAYFORMULA(A2:A*B2:B) | Bulk calculations | |
| How do I generate automatic numbering in a sheet? | SEQUENCE | =SEQUENCE(10) | Generate sequence | |
| How do I extract only specific columns from a table? | CHOOSECOLS | =CHOOSECOLS(A:E,1,3) | Select columns | |
| How do I return selected rows from a dataset? | CHOOSEROWS | =CHOOSEROWS(A:E,1,3) | Select rows | |
| How do I convert a list into multiple rows automatically? | WRAPROWS | =WRAPROWS(A1:A10,3) | Wrap into rows | |
| How do I convert a list into multiple columns automatically? | WRAPCOLS | =WRAPCOLS(A1:A10,3) | Wrap into columns | |
| How do I format numbers as currency, dates, or percentages? | TEXT | =TEXT(A1,"$0.00") | Format values | |
| How do I combine first and last names into one cell? | CONCATENATE | =CONCATENATE(A1," ",B1) | Join text | |
| How do I merge text from multiple cells? | CONCAT | =CONCAT(A1,B1) | Combine text | |
| How do I combine a range into a comma-separated list? | TEXTJOIN | =TEXTJOIN(", ",TRUE,A1:A10) | Join with delimiter | |
| How do I join values using a custom separator? | JOIN | =JOIN("-",A1:A5) | Join range | |
| How do I extract the first few characters from text? | LEFT | =LEFT(A1,5) | Left characters | |
| How do I extract the last few characters from text? | RIGHT | =RIGHT(A1,4) | Right characters | |
| How do I extract text from the middle of a string? | MID | =MID(A1,3,5) | Middle characters | |
| How do I count the number of characters in a cell? | LEN | =LEN(A1) | Character count | |
| How do I remove extra spaces from imported or copied text? | TRIM | =TRIM(A1) | Remove spaces | |
| How do I convert all text to uppercase automatically? | UPPER | =UPPER(A1) | Uppercase | |
| How do I convert all text to lowercase automatically? | LOWER | =LOWER(A1) | Lowercase | |
| How do I capitalize the first letter of every word? | PROPER | =PROPER(A1) | Proper case | |
| How do I replace specific words or characters in text? | SUBSTITUTE | =SUBSTITUTE(A1,"old","new") | Replace text | |
| How do I replace text at a specific position? | REPLACE | =REPLACE(A1,1,3,"ABC") | Replace by position | |
| How do I locate the position of a character in text? | FIND | =FIND("@",A1) | Find position | |
| How do I search for text without case sensitivity? | SEARCH | =SEARCH("google",A1) | Case-insensitive search | |
| How do I check whether text contains numbers? | REGEXMATCH | =REGEXMATCH(A1,"[0-9]") | Pattern match | |
| How do I extract numbers from mixed text? | REGEXEXTRACT | =REGEXEXTRACT(A1,"\d+") | Extract pattern | |
| How do I clean and standardize messy text? | REGEXREPLACE | =REGEXREPLACE(A1,"\s+"," ") | Regex replace | |
| How do I separate comma-separated values into columns? | SPLIT | =SPLIT(A1,",") | Split text | |
| How do I convert text-formatted numbers into actual numbers? | VALUE | =VALUE(A1) | Text to number | |
| How do I create a valid date from year, month, and day values? | DATE | =DATE(2026,6,19) | Create date | |
| How do I automatically display today's date? | TODAY | =TODAY() | Current date | |
| How do I insert the current date and time automatically? | NOW | =NOW() | Current date/time | |
| How do I extract the year from a date? | YEAR | =YEAR(A1) | Extract year | |
| How do I get the month number from a date? | MONTH | =MONTH(A1) | Extract month | |
| How do I extract the day from a date value? | DAY | =DAY(A1) | Extract day | |
| How do I determine the day of the week from a date? | WEEKDAY | =WEEKDAY(A1) | Day number | |
| How do I find the week number of a date? | WEEKNUM | =WEEKNUM(A1) | Week number | |
| How do I calculate the number of days between two dates? | DATEDIF | =DATEDIF(A1,B1,"D") | Date difference | |
| How do I add months to a date automatically? | EDATE | =EDATE(A1,3) | Add months | |
| How do I find the last day of a month? | EOMONTH | =EOMONTH(A1,0) | End of month | |
| How do I calculate working days between two dates? | NETWORKDAYS | =NETWORKDAYS(A1,B1) | Working days | |
| How do I find a future workday excluding weekends? | WORKDAY | =WORKDAY(A1,10) | Future workday | |
| How do I create a valid time value? | TIME | =TIME(10,30,0) | Create time | |
| How do I extract the hour from a timestamp? | HOUR | =HOUR(A1) | Extract hour | |
| How do I extract minutes from a time value? | MINUTE | =MINUTE(A1) | Extract minute | |
| How do I extract seconds from a timestamp? | SECOND | =SECOND(A1) | Extract second | |
| How do I round numbers to a specific number of decimal places? | ROUND | =ROUND(A1,2) | Round value | |
| How do I always round numbers upward? | ROUNDUP | =ROUNDUP(A1,0) | Round up | |
| How do I always round numbers downward? | ROUNDDOWN | =ROUNDDOWN(A1,0) | Round down | |
| How do I remove decimals and keep only whole numbers? | INT | =INT(A1) | Integer part | |
| How do I find the remainder after division? | MOD | =MOD(10,3) | Remainder | |
| How do I convert negative numbers to positive values? | ABS | =ABS(A1) | Absolute value | |
| How do I calculate exponential values? | POWER | =POWER(2,3) | Exponents | |
| How do I calculate the square root of a number? | SQRT | =SQRT(A1) | Square root | |
| How do I generate a random decimal number? | RAND | =RAND() | Random number | |
| How do I generate random integers within a range? | RANDBETWEEN | =RANDBETWEEN(1,100) | Random integer | |
| How do I multiply several numbers together? | PRODUCT | =PRODUCT(A1:A5) | Multiply values | |
| How do I round numbers up to the nearest multiple? | CEILING | =CEILING(A1,5) | Round up multiple | |
| How do I round numbers down to the nearest multiple? | FLOOR | =FLOOR(A1,5) | Round down multiple | |
| How do I pull live stock market data into Google Sheets? | GOOGLEFINANCE | =GOOGLEFINANCE("GOOG") | Financial data | |
| How do I translate text automatically in Google Sheets? | GOOGLETRANSLATE | =GOOGLETRANSLATE(A1,"en","es") | Translate text | |
| How do I scrape specific information from a webpage? | IMPORTXML | =IMPORTXML("https://example.com","//title") | Scrape web data | |
| How do I import tables from a website? | IMPORTHTML | =IMPORTHTML("https://example.com","table",1) | Import tables | |
| How do I pull data from another Google Sheet? | IMPORTRANGE | =IMPORTRANGE("URL","Sheet1!A:C") | Import other sheets | |
| How do I display images directly inside spreadsheet cells? | IMAGE | =IMAGE("https://example.com/image.jpg") | Display image | |
| How do I create mini charts inside a single cell? | SPARKLINE | =SPARKLINE(A1:A10) | Mini charts | |
| How do I create a filtered report using SQL-like syntax? | QUERY | =QUERY(A:F,"SELECT A,B,C WHERE D='Paid'") | SQL-style reporting | |
| How do I summarize sales by category automatically? | QUERY + GROUP BY | =QUERY(A:C,"SELECT A,SUM(B) GROUP BY A") | Sales summary | |
| How do I sort report results automatically? | QUERY + ORDER BY | =QUERY(A:C,"SELECT * ORDER BY C DESC") | Sort reports | |
| How do I display only the top records from a dataset? | QUERY + LIMIT | =QUERY(A:C,"SELECT * LIMIT 10") | Top records | |
| How do I rename column headers in a QUERY result? | QUERY + LABEL | =QUERY(A:C,"SELECT A,SUM(B) GROUP BY A LABEL SUM(B) 'Total'") | Custom reports | |
| How do I show only completed tasks dynamically? | FILTER | =FILTER(A2:F100,F2:F100="Completed") | Dynamic filtering | |
| How do I filter data using multiple conditions? | FILTER + Multiple Conditions | =FILTER(A:F,B:B="USA",C:C="Active") | Advanced filtering | |
| How do I create a sorted list of unique values? | UNIQUE + SORT | =SORT(UNIQUE(A:A)) | Sorted unique values | |
| How do I show the top 10 highest values? | SORTN | =SORTN(A2:B100,10,0,2,FALSE)' | Top 10 results | |
| How do I return a custom message when a lookup fails? | XLOOKUP | =XLOOKUP(E2,A:A,C:C,"Not Found") | Modern lookup | |
| How do I perform a flexible lookup without VLOOKUP limitations? | INDEX + MATCH | =INDEX(C:C,MATCH(E2,A:A,0)) | Flexible lookup | |
| How do I perform a two-way lookup in a table? | INDEX + MATCH + MATCH | =INDEX(A:Z,MATCH(E1,A:A,0),MATCH(F1,1:1,0)) | 2D lookup | |
| How do I calculate an entire column automatically? | ARRAYFORMULA | =ARRAYFORMULA(B2:B*C2:C) | Entire column calculations | |
| How do I auto-calculate only when data exists? | ARRAYFORMULA + IF | =ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C)) | Auto calculations | |
| How do I perform lookups for an entire column at once? | ARRAYFORMULA + VLOOKUP | =ARRAYFORMULA(VLOOKUP(E2:E,A:C,3,FALSE)) | Bulk lookups | |
| How do I apply custom logic to every value in a range? | MAP | =MAP(A2:A,LAMBDA(x,x*10)) | Apply custom logic | |
| How do I calculate totals for every row automatically? | BYROW | =BYROW(A2:D10,LAMBDA(r,SUM(r))) | Row calculations | |
| How do I calculate averages for every column automatically? | BYCOL | =BYCOL(A2:D10,LAMBDA(c,AVERAGE(c))) | Column calculations | |
| How do I accumulate values into a single result? | REDUCE | =REDUCE(0,A1:A10,LAMBDA(a,b,a+b)) | Accumulation | |
| How do I create running totals automatically? | SCAN | =SCAN(0,A1:A10,LAMBDA(a,b,a+b)) | Running totals | |
| How do I generate a custom array without manual entry? | MAKEARRAY | =MAKEARRAY(5,5,LAMBDA(r,c,r*c)) | Dynamic arrays | |
| How do I reuse a calculation within a formula? | LET | =LET(x,SUM(A:A),x*0.1) | Reusable variables | |
| How do I create my own custom function? | LAMBDA | =LAMBDA(x,x*2)(10) | Custom functions | |
| How do I return only selected columns from a dataset? | CHOOSECOLS | =CHOOSECOLS(A:F,1,3,5) | Extract columns | |
| How do I return only selected rows from a dataset? | CHOOSEROWS | =CHOOSEROWS(A:F,2,4,6) | Extract rows | |
| How do I show only the first few rows of a table? | TAKE | =TAKE(A:F,10) | First rows | |
| How do I remove headers or unwanted rows? | DROP | =DROP(A:F,1) | Remove headers | |
| How do I convert a single column into multiple rows? | WRAPROWS | =WRAPROWS(A1:A20,5) | Reshape data | |
| How do I convert a single column into multiple columns? | WRAPCOLS | =WRAPCOLS(A1:A20,5) | Reshape columns | |
| How do I combine multiple columns into one table? | HSTACK | =HSTACK(A:A,B:B,C:C) | Merge columns | |
| How do I combine data from multiple sheets vertically? | VSTACK | =VSTACK(Sheet1!A:C,Sheet2!A:C) | Combine tables | |
| How do I convert a table into a single column? | TOCOL | =TOCOL(A1:D10)' | Convert to one column | |
| How do I convert a table into a single row? | TOROW | =TOROW(A1:D10)' | Convert to one row | |
| How do I extract numbers from text strings? | REGEXEXTRACT | =REGEXEXTRACT(A1,"[0-9]+") | Extract numbers | |
| How do I validate email domains in Google Sheets? | REGEXMATCH | =REGEXMATCH(A1,"gmail\.com") | Email validation | |
| How do I remove non-numeric characters from phone numbers? | REGEXREPLACE | =REGEXREPLACE(A1,"[^0-9]","") | Clean phone numbers | |
| How do I create a dynamic comma-separated list? | TEXTJOIN + FILTER | =TEXTJOIN(", ",TRUE,FILTER(A:A,B:B="Yes")) | Dynamic lists | |
| How do I import sales data from another spreadsheet? | IMPORTRANGE | =IMPORTRANGE("URL","Sales!A:F") | External sheet data | |
| How do I build reports from external spreadsheets? | IMPORTRANGE + QUERY | =QUERY(IMPORTRANGE("URL","Sales!A:F"),"SELECT Col1,Col3") | Remote reporting | |
| How do I extract webpage headings automatically? | IMPORTXML | =IMPORTXML("https://example.com","//h1") | Web scraping | |
| How do I import website tables into Sheets? | IMPORTHTML | =IMPORTHTML("https://example.com","table",1) | Import tables | |
| How do I track live stock prices in Google Sheets? | GOOGLEFINANCE | =GOOGLEFINANCE("NASDAQ:GOOG","price") | Stock tracking |