Last updated on Jun 20, 2026, 7:07 PM
| Problem Where It Works | Formula | Example | Use Case |
|---|---|---|---|
| How do I calculate the total sales amount from multiple rows? | SUM | =SUM(A1:A10) | Add values |
| How do I find the average score of all students in a class? | AVERAGE | =AVERAGE(A1:A10) | Calculate average |
| How do I find the middle value in a dataset without being affected by outliers? | MEDIAN | =MEDIAN(A1:A10) | Find middle value |
| How do I identify the lowest value in a list? | MIN | =MIN(A1:A10) | Lowest value |
| How do I find the highest value in a range? | MAX | =MAX(A1:A10) | Highest value |
| How do I count cells containing numbers only? | COUNT | =COUNT(A1:A10) | Count numbers |
| How do I count all non-empty cells? | COUNTA | =COUNTA(A1:A10) | Count non-empty cells |
| How do I count blank cells in a spreadsheet? | COUNTBLANK | =COUNTBLANK(A1:A10) | Count blank cells |
| How do I count all records matching a specific value? | COUNTIF | =COUNTIF(A:A,"Paid") | Count matching values |
| How do I count records matching multiple criteria? | COUNTIFS | =COUNTIFS(A:A,"Paid",B:B,"Yes") | Multiple conditions |
| 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 |
| How do I analyze historical stock performance? | GOOGLEFINANCE Historical | =GOOGLEFINANCE("GOOG","close",TODAY()-30,TODAY()) | Historical prices |
| How do I calculate workdays with custom weekends? | NETWORKDAYS.INTL | =NETWORKDAYS.INTL(A1,B1,"0000011") | Custom workdays |
| How do I calculate future business dates with custom weekends? | WORKDAY.INTL | =WORKDAY.INTL(A1,30,"0000011") | Business dates |
| How do I calculate age in months between two dates? | DATEDIF | =DATEDIF(A1,B1,"YM") | Age/month calculation |
| How do I display trend charts inside cells? | SPARKLINE | =SPARKLINE(B2:M2)' | Mini charts |
| How do I analyze the distribution of values into groups? | FREQUENCY | =FREQUENCY(A:A,{10,20,30,40}) | Distribution analysis |
| How do I perform matrix multiplication in Google Sheets? | MMULT | =MMULT(A1:C3,E1:G3) | Matrix multiplication |
| How do I create pivot-style reports from horizontal data? | TRANSPOSE + QUERY | =QUERY(TRANSPOSE(A1:Z2),"SELECT *") | Pivot-like reporting |