Google Sheets formulas that just work

All formulas

• Google Sheets IMPORTRANGE Builder — Generate the Formula from a Link Free IMPORTRANGE builder for Google Sheets. Paste the source spreadsheet link, pick the tab and range, and get the exact =IMPORTRANGE() formula with a copy button — no need to dig the key out of the URL.
• Google Sheets Conditional Formatting Formula Builder — Copy the Custom Formula Free conditional formatting custom-formula builder for Google Sheets. Highlight a whole row by a cell value, flag duplicates, blanks, overdue dates or numbers in a range — pick the rule, copy the exact formula.
• Google Sheets COUNTIF & SUMIF Builder — Generate the Formula, Copy, Done Free COUNTIF, COUNTIFS, SUMIF and SUMIFS builder for Google Sheets. Pick your range and condition, get a ready-to-paste formula with a copy button — no syntax to remember.
• How to Add Days to a Date in Google Sheets Add or subtract days from a date in Google Sheets. Copy =A2+30, plus how to add working days with WORKDAY and add months with EDATE.
• How to Calculate Time Difference in Google Sheets Find the time between two times in Google Sheets. Copy =B2-A2, format as duration, plus how to get total hours as a decimal and handle times past midnight.
• How to Capitalize the First Letter in Google Sheets Capitalize names and text in Google Sheets. Copy =PROPER(A2) for title case, plus a formula to capitalize only the first letter of a sentence.
• How to Check if a Cell Contains Specific Text in Google Sheets Test whether a cell contains a word in Google Sheets. Copy the ISNUMBER(SEARCH(...)) formula, plus how to return Yes/No and match case exactly.
• How to Count Cells with Two Conditions in Google Sheets Count rows that match two or more conditions in Google Sheets with COUNTIFS. Copy the formula, plus date ranges and matching text with wildcards.
• How to Count Words in a Cell in Google Sheets Count words in a cell in Google Sheets. Copy the SPLIT-based formula that handles empty cells and extra spaces, plus a character-count version.
• How to Generate a Random Number in Google Sheets Generate random numbers in Google Sheets. Copy =RANDBETWEEN(1,100), plus RAND for decimals and how to freeze a random value so it stops changing.
• How to Get the Day of the Week from a Date in Google Sheets Show the weekday name from a date in Google Sheets. Copy =TEXT(A2,"dddd"), plus the short Mon form and WEEKDAY for a number.
• How to Round to the Nearest 5 in Google Sheets Round to the nearest 5, 10 or 0.25 in Google Sheets with MROUND. Copy the formula, plus CEILING and FLOOR to always round up or down.
• How to Sum Across Multiple Sheets in Google Sheets Total the same cell or range across several tabs in Google Sheets. Copy the 3D-style formula, plus a cleaner SUM-per-sheet approach.
• "Array Arguments Are of Different Size" in Google Sheets — Fix It This error means two ranges in your formula have different row or column counts. Learn why ARRAYFORMULA, FILTER and math on ranges throw it and how to fix it.
• "You Must Connect These Sheets" IMPORTRANGE Error — How to Fix It IMPORTRANGE shows #REF! "You must connect these sheets" until you grant access once. Learn exactly where to click and why it happens.
• How to Average Cells That Meet a Condition in Google Sheets Average cells by a condition in Google Sheets with AVERAGEIF. Copy the formula, plus averaging by text, by a number test, and multiple conditions with AVERAGEIFS.
• How to Calculate a Weighted Average in Google Sheets Calculate a weighted average in Google Sheets with SUMPRODUCT divided by SUM of the weights. Copy the formula, plus percentage-weight and grade examples.
• How to Count Blank Cells in Google Sheets Count empty cells in Google Sheets with COUNTBLANK. Copy the formula, plus counting non-blank cells, ignoring formula blanks, and counting blanks with a condition.
• How to Count Cells Greater Than a Number in Google Sheets Count cells greater than a value in Google Sheets with COUNTIF and a comparison string. Copy the formula, plus greater-than-or-equal, cell reference and between examples.
• How to Find the Most Frequent Value in Google Sheets Find the most common value in Google Sheets — text or numbers — with INDEX, MATCH and COUNTIF, or MODE for numbers. Copy the formula plus tie and top-N examples.
• How to VLOOKUP with Multiple Criteria in Google Sheets Look up a value on two or more criteria in Google Sheets. Copy an XLOOKUP, a helper-column VLOOKUP, and a FILTER formula that match several conditions at once.
• Google Sheets Regex Tester — REGEXEXTRACT, REGEXMATCH & REGEXREPLACE (RE2) Free live regex tester for Google Sheets. Type your text and pattern to see matches highlighted instantly, then copy the exact REGEXEXTRACT, REGEXMATCH or REGEXREPLACE formula. Uses the RE2 syntax Google Sheets actually runs.
• Google Sheets QUERY Builder — Generate the QUERY Formula & Copy Free Google Sheets QUERY formula builder. Pick your range, SELECT columns, WHERE filters, ORDER BY and LIMIT and get a ready-to-paste =QUERY() formula with a copy button.
• Google Sheets IF Formula Builder — Nested IF & IFS Generator Free Google Sheets IF formula builder. Add your conditions and results and get the exact nested IF or IFS formula — with text/number handling and a default value. Copy and paste.
• Google Sheets Percentage Calculator — % Change, % of Total, Increase/Decrease Formulas Free Google Sheets percentage calculator: type two numbers for an instant % change, then generate the exact formula for percent change, percent of total, and increase or decrease. Copy and paste.
• Google Sheets Date Difference Calculator — Days, Months, Years Between Dates Free calculator for the difference between two dates in Google Sheets. Type two dates for an instant answer, or build the exact DATEDIF / NETWORKDAYS formula with a copy button.
• Google Sheets XLOOKUP / VLOOKUP Builder — Generate the Lookup Formula & Copy Free XLOOKUP, VLOOKUP and INDEX/MATCH builder for Google Sheets. Pick your lookup value, columns and match type and get a ready-to-paste lookup formula with a copy button.
• "Array Result Was Not Expanded" in Google Sheets — How to Fix It "Array result was not expanded because it would overwrite data in [cell]." Here's why the #REF! spill error happens and how to clear the cells so your formula can fill down.
• "Did Not Find Value in VLOOKUP Evaluation" — How to Fix It Google Sheets error "Did not find value 'X' in VLOOKUP evaluation." Here's why VLOOKUP can't find your value — exact-match, text vs number, trailing spaces — and how to fix each one.
• Google Sheets Formula Not Calculating (Showing as Text) — Fix It Your Google Sheets formula shows as text instead of a result, or won't update. Here's why — cell formatted as text, missing equals sign, or recalculation setting — and how to fix each.
• "Wrong Number of Arguments" in Google Sheets — How to Fix It Google Sheets says "Wrong number of arguments to FUNCTION. Expected between X and Y arguments." Here's what the error means and how to fix a formula with too many or too few arguments.
• How to Calculate a Running Total in Google Sheets Build a cumulative running total in Google Sheets. Copy =SUM($B$2:B2), plus a one-cell array version and a running total by group.
• How to Calculate Age From a Birthday in Google Sheets Turn a date of birth into an age in Google Sheets. Copy =DATEDIF(B2,TODAY(),"Y"), plus age in months and exact years-months-days.
• How to Convert Text to Numbers in Google Sheets Fix numbers stored as text in Google Sheets. Copy =VALUE(A2), plus multiply-by-1 tricks and how to convert a whole column at once.
• How to Count Days Between Two Dates in Google Sheets Count the number of days between two dates in Google Sheets. Copy =B2-A2, plus how to count workdays only and whole weeks.
• How to Extract Numbers From Text in Google Sheets Pull digits out of a mixed text string in Google Sheets. Copy =REGEXEXTRACT(A2,"\d+"), plus extracting decimals and all numbers.
• How to Sum If a Cell Contains Specific Text in Google Sheets Sum values where another cell contains a word in Google Sheets. Copy =SUMIF(A2:A,"*apple*",B2:B) with wildcard matching and multiple conditions.
• How to Calculate Percentage of a Total in Google Sheets Work out each value's percentage of the total in Google Sheets. Copy the formula, plus percentage change and how to lock the total so it fills down.
• How to Combine Two Columns in Google Sheets Merge two columns in Google Sheets into one. Copy the & formula, plus TEXTJOIN for many columns and how to fill the whole column at once.
• How to Count Cells With Text in Google Sheets Count text cells in Google Sheets with COUNTIF and a wildcard. Copy the formula, plus how to count a specific word or partial match.
• How to Count Unique Values in Google Sheets Count distinct values in Google Sheets. Copy =COUNTA(UNIQUE()), plus how to count unique text only and count unique with a condition.
• How to Remove Duplicates in Google Sheets Remove duplicate rows or values in Google Sheets with one formula. Copy =UNIQUE, plus how to dedupe across multiple columns and keep the first row.
• How to Remove Extra Spaces in Google Sheets Clean up whitespace in Google Sheets with =TRIM. Copy the formula, plus how to also remove line breaks and fix a whole column at once.
• How to Split Text Into Columns in Google Sheets Split text into multiple columns in Google Sheets with =SPLIT. Copy the formula, plus how to split on commas, keep the original, and split a whole column.
• How to Sum a Column in Google Sheets Add up a whole column in Google Sheets with =SUM. Copy the formula, plus how to sum with a condition, sum visible rows only, and skip the header.
• AVERAGEIF in Google Sheets Average cells that match a condition in Google Sheets with AVERAGEIF. Copy the formula, plus AVERAGEIFS for multiple conditions.
• COUNT Function in Google Sheets Count numeric cells in Google Sheets with COUNT. Copy the formula, plus COUNTA, COUNTBLANK and count-with-condition variations.
• COUNTA in Google Sheets Count non-empty cells in Google Sheets with COUNTA. Copy the formula, plus how it differs from COUNT and how to count only text.
• HLOOKUP in Google Sheets Look up values horizontally in Google Sheets with HLOOKUP. Copy the formula, plus exact-match tips and when to use XLOOKUP instead.
• LEN Function in Google Sheets Count characters in a cell in Google Sheets with LEN. Copy the formula, plus counting words, ignoring spaces and validation tricks.
• MAX Function in Google Sheets Find the largest value in Google Sheets with MAX. Copy the formula, plus MAXIFS for conditional max and how to get the max date.
• MIN Function in Google Sheets Find the smallest value in Google Sheets with MIN. Copy the formula, plus MINIFS for conditional minimum and how to ignore zeros.
• ROUNDUP and ROUNDDOWN in Google Sheets Round numbers up or down in Google Sheets with ROUNDUP and ROUNDDOWN. Copy the formulas, plus rounding to whole numbers and nearest cent.
• SUMPRODUCT in Google Sheets Multiply and sum ranges in Google Sheets with SUMPRODUCT. Copy the formula, plus weighted averages and conditional-count tricks.
• TODAY Function in Google Sheets Insert and use the current date in Google Sheets with TODAY. Copy the formula, plus days-between, NOW and static-date shortcuts.
• TRIM Function in Google Sheets Remove extra spaces in Google Sheets with TRIM. Copy the formula, plus CLEAN for hidden characters and how to fix broken VLOOKUPs.
• UPPER, LOWER and PROPER in Google Sheets Change letter case in Google Sheets with UPPER, LOWER and PROPER. Copy the formulas, plus how to convert a whole column at once.
• Circular Dependency Error in Google Sheets — How to Fix It A circular dependency means a formula refers back to its own cell, directly or through a chain. Learn how to find and break the loop in Google Sheets.
• #DIV/0! Error in Google Sheets — Fix Division by Zero #DIV/0! means a formula tried to divide by zero or an empty cell. See the causes and how to return a clean result instead.
• #ERROR! in Google Sheets — Fix Formula Parse Errors #ERROR! is Google Sheets' formula parse error — the formula is written in a way Sheets can't read, often a missing bracket or wrong separator.
• #N/A Error in Google Sheets — Why Lookups Return It and How to Fix #N/A means a lookup couldn't find a match. Learn why VLOOKUP, MATCH and XLOOKUP return #N/A and how to fix or hide it cleanly.
• #NAME? Error in Google Sheets — Fix Unknown Names #NAME? means Google Sheets doesn't recognize a name in your formula — a misspelled function, missing quotes, or an undefined named range.
• #NUM! Error in Google Sheets — Causes and Fixes #NUM! means a formula produced an invalid or impossible number, like the square root of a negative or a result too large to display.
• #REF! Error in Google Sheets — What It Means and How to Fix It The #REF! error means a formula points to a cell that no longer exists. See the common causes — deleted rows, bad ranges, IMPORTRANGE — and how to fix each one.
• #VALUE! Error in Google Sheets — Causes and Fixes #VALUE! means a formula got the wrong type of value — usually text where it expected a number. Here are the common causes and quick fixes.
• How to Add Leading Zeros in Google Sheets Pad numbers with leading zeros in Google Sheets using =TEXT(A2,"00000"). Keep ZIP codes and IDs at a fixed length. Copy-ready formulas.
• How to Calculate an Average in Google Sheets Average a column in Google Sheets with =AVERAGE(A2:A). Plus AVERAGEIF for a conditional average and how to ignore blanks and zeros.
• How to Calculate Percentage Change in Google Sheets Work out percentage change between two numbers in Google Sheets: =(B2-A2)/A2, formatted as a percent. Includes growth and decline examples.
• How to Convert a Date to Text in Google Sheets Turn a date into text in Google Sheets with =TEXT(A2,"yyyy-mm-dd"). Choose any format like month names or day of week. Copy-ready.
• How to Get the Last Value in a Column in Google Sheets Return the last non-empty value in a Google Sheets column with =LOOKUP(2,1/(A2:A<>""),A2:A). Works even with gaps. Copy-ready formulas.
• How to Multiply Two Columns in Google Sheets Multiply two columns row by row in Google Sheets: =A2*B2, or fill the whole column at once with =ARRAYFORMULA(A2:A*B2:B).
• How to Rank Numbers in Google Sheets Rank a list from highest to lowest in Google Sheets with =RANK(A2,$A$2:$A$100). Includes ascending rank and breaking ties with RANK.AVG.
• How to Separate First and Last Name in Google Sheets Split a full name into first and last name in Google Sheets. Copy the SPLIT and REGEXEXTRACT formulas that handle middle names too.
• How to Sum by Month in Google Sheets Total values that fall within a month in Google Sheets using SUMIFS with date bounds, or SUMPRODUCT on the month. Copy-ready formulas.
• How to VLOOKUP from Another Sheet in Google Sheets Pull data from a different tab with VLOOKUP: =VLOOKUP(A2,Sheet2!A:B,2,FALSE). Includes IMPORTRANGE for another spreadsheet file.
• COUNTIF vs COUNTIFS in Google Sheets — When to Use Each COUNTIF vs COUNTIFS in Google Sheets: COUNTIF counts with one condition, COUNTIFS with several (AND). Copy-paste examples and the common mistakes.
• FILTER vs QUERY in Google Sheets — Which Should You Use FILTER vs QUERY in Google Sheets: FILTER is simple for row filtering, QUERY is a mini SQL for sorting, grouping and aggregating. See when each wins, with examples.
• IF vs IFS in Google Sheets — When to Use Each IF vs IFS in Google Sheets: IF handles one test with two outcomes, IFS checks many conditions in order without nesting. Copy-paste examples and the gotchas.
• INDEX MATCH vs VLOOKUP in Google Sheets — Which Is Better INDEX MATCH vs VLOOKUP in Google Sheets: INDEX/MATCH looks left, survives column changes and is faster on wide tables. See when each wins, with copy-paste examples.
• SUMIF vs SUMIFS in Google Sheets — When to Use Each SUMIF vs SUMIFS in Google Sheets: SUMIF adds with one condition, SUMIFS with several. See the argument-order trap and copy-paste examples for both.
• TEXTJOIN vs CONCATENATE in Google Sheets — Which to Use TEXTJOIN vs CONCATENATE in Google Sheets: TEXTJOIN adds a delimiter and skips blanks over a whole range, CONCATENATE just glues text. Copy-paste examples.
• UNIQUE vs Remove Duplicates in Google Sheets — Which to Use UNIQUE formula vs the Remove Duplicates menu tool in Google Sheets: one is a live formula, the other edits your data in place. See when each is right, with examples.
• VLOOKUP vs XLOOKUP in Google Sheets — Which One to Use VLOOKUP vs XLOOKUP in Google Sheets: XLOOKUP looks left, handles missing values and doesn't break when you insert columns. See when each one wins, with copy-paste examples.
• DATEDIF in Google Sheets Calculate the difference between two dates in Google Sheets with DATEDIF. Copy the formula, plus age in years, months, and combined-format variations.
• FILTER Function in Google Sheets Filter a range in Google Sheets with the FILTER function. Copy the formula, plus multiple conditions, OR logic, and sorted-output variations.
• LEFT, RIGHT and MID in Google Sheets Extract characters from text in Google Sheets with LEFT, RIGHT, and MID. Copy the formula, plus first-word, file-extension, and dynamic-length variations.
• MATCH Function in Google Sheets Find the position of a value in Google Sheets with MATCH. Copy the formula, plus exact-match, INDEX/MATCH, and approximate-match variations.
• REGEXEXTRACT in Google Sheets Extract part of a string in Google Sheets with REGEXEXTRACT. Copy the formula, plus number, email-domain, and between-markers variations.
• REGEXMATCH in Google Sheets Test text against a pattern in Google Sheets with REGEXMATCH. Copy the formula, plus contains-any, case-insensitive, and use-inside-IF variations.
• ROUND in Google Sheets Round numbers in Google Sheets with ROUND, ROUNDUP, ROUNDDOWN, and MROUND. Copy the formula, plus round-to-nearest and round-to-whole variations.
• SORT Function in Google Sheets Sort a range in Google Sheets with the SORT function. Copy the formula, plus multi-column, descending, and sort-by-another-column variations.
• SUBSTITUTE in Google Sheets Replace text in Google Sheets with SUBSTITUTE. Copy the formula, plus remove-a-character, nth-occurrence, and multiple-replacement variations.
• TEXT Function in Google Sheets Format numbers and dates as text in Google Sheets with the TEXT function. Copy the formula, plus currency, percent, date, and leading-zero variations.
• TEXTJOIN in Google Sheets Join a range of cells in Google Sheets with TEXTJOIN. Copy the formula, plus skip-blanks, custom-delimiter, and join-with-condition variations.
• UNIQUE in Google Sheets Get a de-duplicated list in Google Sheets with UNIQUE. Copy the formula, plus sorted, counted, and multi-column variations.
• ARRAYFORMULA in Google Sheets Apply a formula to a whole column in Google Sheets with ARRAYFORMULA. Copy the formula, plus IF, blank-guard and join variations.
• AVERAGE and AVERAGEIF in Google Sheets Find the mean in Google Sheets with AVERAGE, AVERAGEIF and AVERAGEIFS. Copy the formula, plus condition and ignore-zeros variations.
• DATE and TODAY Formulas in Google Sheets Work with dates in Google Sheets using DATE, TODAY, EDATE and EOMONTH. Copy the formula, plus add-days and days-between variations.
• IF Function in Google Sheets Write IF then formulas in Google Sheets. Copy the formula, plus nested IF, IF with AND/OR, and blank-if-empty variations.
• IMPORTRANGE in Google Sheets Pull data from another spreadsheet in Google Sheets with IMPORTRANGE. Copy the formula, plus filter and QUERY-combined variations.
• Multiplication Formula in Google Sheets Multiply cells and columns in Google Sheets. Copy the formula for cell-by-cell, whole-column, percentage and PRODUCT multiplication.
• QUERY Function in Google Sheets Filter, sort and total data in Google Sheets with QUERY. Copy the formula, plus where, order by, group by and label variations.
• Find and Remove Duplicates in Google Sheets Find, flag, highlight and remove duplicates in Google Sheets. Copy the COUNTIF, UNIQUE and conditional-formatting formulas.
• SPLIT in Google Sheets Split text into columns in Google Sheets with SPLIT. Copy the formula, plus comma, space, multi-delimiter and keep-empty variations.
• SUMIF in Google Sheets Add numbers that meet one condition in Google Sheets with SUMIF. Copy the formula, plus text-criteria, wildcard, and greater-than variations.
• SUMIFS in Google Sheets Add numbers matching multiple conditions in Google Sheets with SUMIFS. Copy the formula, plus date-range and cell-reference variations.
• VLOOKUP in Google Sheets Look up a value in Google Sheets with VLOOKUP. Copy the formula, plus exact-match, another-sheet, and multiple-column variations.
• Concatenate in Google Sheets Join text from several cells in Google Sheets with CONCATENATE, the & operator, and TEXTJOIN with a separator. Copy-paste formulas.
• COUNTIF in Google Sheets Count how many cells match a condition in Google Sheets with COUNTIF. Copy the formula, plus variations for text, numbers, greater-than and wildcards.
• COUNTIFS in Google Sheets Count rows that meet several conditions at once using COUNTIFS in Google Sheets. Copy the formula plus date-range and OR patterns.
• IFERROR in Google Sheets Wrap any formula in IFERROR to replace error values with a blank or your own text in Google Sheets. Copy-paste examples.
• IFS Function in Google Sheets Replace nested IFs with the IFS function in Google Sheets. Copy the formula and see how to add a catch-all default.
• INDEX MATCH in Google Sheets Combine INDEX and MATCH in Google Sheets for lookups that work in any direction. Copy the formula and a two-way (row + column) version.
• Percentage Formula in Google Sheets Calculate percentages in Google Sheets: percent of a total, percentage change, and add or subtract a percent. Copy the formulas.
• Subtraction Formula in Google Sheets How to subtract in Google Sheets: subtract one cell from another, subtract a whole column, or subtract a percentage. Copy-paste formulas.
• SUM Formula in Google Sheets Add up numbers in Google Sheets with SUM, SUMIF and SUMIFS. Copy formulas for a column, a range, and conditional totals.
• XLOOKUP in Google Sheets Use XLOOKUP in Google Sheets to find a value and return a match from another column — with a copy-paste formula and a not-found fallback.
• Google Sheets Formulas Cheat Sheet — 34 Copy-Paste Formulas One-page Google Sheets cheat sheet: 34 ready-to-paste formulas for lookups, counts, sums, text, logic and dates — each with a Copy button and a link to the full guide.