"How-To" Solution Recipes
Problem-focused spreadsheet solutions with copyable formulas, sample tables, and step-by-step instructions.
How to Find the End of the Month in Excel & Google Sheets (EOMONTH)
Calculate the last day of the current month, next month, or fiscal quarters using EOMONTH in Excel and Google Sheets.
=EOMONTH(start_date, months) How to Add Months to a Date in Excel & Google Sheets (EDATE)
Add or subtract months, quarters, or years to any date while preserving the exact day of the month using EDATE in spreadsheets.
=EDATE(start_date, months) How to Calculate Compound Annual Growth Rate (CAGR) in Excel
Calculate multi-year revenue growth, annualized investment performance, and CAGR using math exponents and the RRI formula in Excel.
=(Ending_Value / Beginning_Value)^(1 / Years) - 1 | =RRI(Years, Start_Value, End_Value) How to Count Unique and Distinct Values in Excel & Google Sheets
Count total unique items in a list, count distinct values with criteria, and handle blanks using COUNTA, UNIQUE, and SUMPRODUCT formulas.
Modern: =COUNTA(UNIQUE(A2:A100)) | Classic: =SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100)) How to Calculate Business Days in Excel (Excluding Weekends & Holidays)
Calculate project turnaround times, SLA deadlines, and working days using NETWORKDAYS.INTL and WORKDAY.INTL in spreadsheets.
=NETWORKDAYS.INTL(start_date, end_date, 1, holidays_range) How to Extract Text Between Parentheses or Characters in Excel
Extract substrings enclosed in parentheses, brackets, or custom delimiter characters using TEXTBEFORE/TEXTAFTER or classic MID and FIND.
Modern: =TEXTBEFORE(TEXTAFTER(A2, "("), ")") | Classic: =MID(A2, FIND("(", A2)+1, FIND(")", A2)-FIND("(", A2)-1) How to Add or Subtract a Percentage in Excel (Sales Tax & Discounts)
Formulas to add sales tax, apply coupon discounts, and calculate final retail prices in Excel and Google Sheets.
Add %: =Price * (1 + Tax_Rate) | Subtract %: =Price * (1 - Discount_Rate) How to Calculate Age from Date of Birth in Excel & Google Sheets
Calculate current age in full completed years, exact decimal age, or years and months using DATEDIF and YEARFRAC formulas.
=DATEDIF(Birth_Date, TODAY(), "Y") | =YEARFRAC(Birth_Date, TODAY(), 1) How to Calculate Percentage Change in Excel & Google Sheets (Increase & Decrease)
Calculate percentage change, growth rate, and year-over-year differences in Excel and Google Sheets with step-by-step business examples.
=(New_Value - Old_Value) / Old_Value How to Extract First and Last Name in Excel & Google Sheets
Extract first names, middle names, and last names from full name strings using modern TEXTSPLIT or classic LEFT, RIGHT, and SEARCH formulas.
Modern: =TEXTSPLIT(A2, " ") | Classic First: =LEFT(A2, SEARCH(" ", A2)-1) | Classic Last: =RIGHT(A2, LEN(A2)-SEARCH(" ", A2)) How to INDEX MATCH with Multiple Criteria in Excel & Google Sheets
Lookup values across multiple criteria using INDEX MATCH array formulas for universal compatibility in all versions of Excel.
=INDEX(return_range, MATCH(1, (criteria1_range = val1) * (criteria2_range = val2), 0)) How to Highlight Duplicates in Excel & Google Sheets (Conditional Formatting)
Highlight duplicate rows, unique records, and repeated IDs across columns using COUNTIF and custom conditional formatting rules.
=COUNTIF($A$2:$A$100, A2) > 1 How to Remove Leading, Trailing, and Extra Spaces in Excel
Clean up messy text imports, fix VLOOKUP #N/A failures, and strip invisible non-breaking spaces using TRIM and CLEAN in spreadsheets.
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))) How to Capitalize the First Letter in Excel (PROPER, UPPER, LOWER)
Convert text to Proper Case (Title Case), uppercase, lowercase, or capitalize only the very first letter of a sentence using Excel and Google Sheets formulas.
Title Case: =PROPER(A2) | Sentence Case: =UPPER(LEFT(A2, 1)) & LOWER(MID(A2, 2, LEN(A2))) How to Split Delimited Text into Columns in Excel & Google Sheets
Split comma-separated values (CSV), hyphenated IDs, and multi-line cells into distinct columns using dynamic formulas.
=TEXTSPLIT(A2, ", ") | Google Sheets: =SPLIT(A2, ",") How to Calculate Percent of Total in Excel & Google Sheets
Calculate what percentage an individual item or category contributes to a total sum using absolute cell references ($) in spreadsheets.
=Cell_Value / SUM($Range$Start:$Range$End) How to VLOOKUP from Right to Left in Excel (Reverse Lookup)
Perform left lookups using XLOOKUP, INDEX/MATCH, or the classic VLOOKUP with CHOOSE array trick in Excel and Google Sheets.
Recommended: =XLOOKUP(val, lookup_col, return_col) | Classic: =INDEX(return_col, MATCH(val, lookup_col, 0)) How to Perform a Two-Way Matrix Lookup in Excel & Google Sheets
Lookup values at the intersection of a dynamic row and column header in a 2D matrix table using INDEX/MATCH and nested XLOOKUP.
XLOOKUP: =XLOOKUP(Row_Val, Row_Headers, XLOOKUP(Col_Val, Col_Headers, Table_Data)) | INDEX/MATCH: =INDEX(Table_Data, MATCH(Row_Val, Row_Headers, 0), MATCH(Col_Val, Col_Headers, 0)) How to Calculate Weighted Average in Excel (SUMPRODUCT Method)
Calculate weighted average grades, product gross margins, and investment portfolio returns using SUMPRODUCT in Excel and Google Sheets.
=SUMPRODUCT(values_range, weights_range) / SUM(weights_range) How to XLOOKUP with Multiple Criteria (AND / OR Conditions)
Lookup values across multiple criteria using boolean multiplication in XLOOKUP without messy helper columns in Excel and Google Sheets.
=XLOOKUP(1, (Criteria_Range1 = Val1) * (Criteria_Range2 = Val2), Return_Range, "Not Found")