Welcome to SpreadSheet Sage
Tax Bracket Lookup
Find which tax bracket an income falls into
=XLOOKUP(B2,TaxTable[Lower],TaxTable[Rate],,-1)Uses XLOOKUP with approximate match (mode -1) to find which tax bracket the income falls into and return the corresponding rate.
Invoice Aging Buckets
Classify invoices as Current / 30 / 60 / 90+ days overdue
=IF(TODAY()-B2>90,"90+ Days",IF(TODAY()-B2>60,"60 Days",IF(TODAY()-B2>30,"30 Days","Current")))Payment Status Flag
Flag invoices as Current, Late, or Very Late
=IF(TODAY()-B2>60,"Very Late",IF(TODAY()-B2>30,"Late","Current"))Late Fee Calculation
Percentage-based late fee for overdue invoices
=IF(TODAY()-B2>30,A2*0.015,0)Days Outstanding
Calculate exact days since the invoice date
=TODAY()-B2Overdue Percentage
What percentage of items are overdue
=COUNTIF(A:A,"Overdue")/COUNTA(A:A)VLOOKUP with Error Handling
Look up from another sheet, "Not Found" on failure
=IFERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE),"Not Found")INDEX-MATCH (Better VLOOKUP)
Flexible two-way lookup, works in any direction
=INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0))XLOOKUP (Modern Lookup)
The modern VLOOKUP replacement—simpler, more powerful
=XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B,"Not Found")VLOOKUP with Wildcard
Find a partial match using * wildcards
=VLOOKUP("*"&A2&"*",B:C,2,FALSE)Reference a Named Range
Dynamically reference a named range from a cell value
=SUM(INDIRECT(A1))SUMIFS — Multi-Criteria Sum
Sum amounts matching multiple conditions
=SUMIFS(C:C,A:A,"Sales",B:B,">1000")COUNTIFS — Multi-Criteria Count
Count rows matching multiple conditions
=COUNTIFS(A:A,"Overdue",B:B,">="&TODAY()-30)Revenue by Quarter
Sum revenue within a date range (e.g., Q1 2024)
=SUMIFS(C:C,B:B,">="&DATE(2024,1,1),B:B,"<"&DATE(2024,4,1))Sum Across Multiple Sheets
Add up the same cell across worksheets
=SUM(Jan:Dec!B2)AVERAGEIFS — Conditional Average
Average values meeting multiple criteria
=AVERAGEIFS(C:C,A:A,"Sales",B:B,">0")SUMPRODUCT — Weighted Calculation
Multiply corresponding values and sum the results
=SUMPRODUCT(A2:A10,B2:B10)End of Month
Last day of a given month, or offset by N months
=EOMONTH(A2,0)Working Days Between Dates
Count business days (excludes weekends)
=NETWORKDAYS(A2,B2)Format Date as Text
Display a date as "Jan 15, 2024"
=TEXT(A2,"MMM DD, YYYY")Fiscal Quarter from Date
Determine which quarter (Q1-Q4) a date falls in
="Q"&ROUNDUP(MONTH(A2)/3,0)Add or Subtract Months
Shift a date forward or backward by months
=EDATE(A2,3)Payment Due Date (Net 30)
When is payment due based on invoice date
=A2+30Calculate Age / Time Elapsed
Years, months, or days between two dates
=DATEDIF(A2,TODAY(),"Y")Round to Cents
Round to exactly 2 decimal places
=ROUND(A2*B2,2)Monthly Loan Payment
Monthly payment for a loan
=PMT(B2/12,C2*12,-A2)Future Value of Investment
What an investment will be worth
=FV(B2/12,C2*12,-A2)Straight-Line Depreciation
Annual depreciation for an asset
=SLN(A2,B2,C2)Percentage Change
Percent increase or decrease between values
=(B2-A2)/A2Combine Text (First + Last Name)
Join cell values with a separator
=A2&" "&B2Remove Extra Spaces
Clean up leading, trailing, and double spaces
=TRIM(A2)Find and Replace in Cells
Replace specific text within a cell
=SUBSTITUTE(A2,"Inc.","Incorporated")Capitalize Each Word
Convert to proper case
=PROPER(A2)Join Range with Delimiter
Combine a range into one cell with separator
=TEXTJOIN(", ",TRUE,A2:A10)Full Cleanup (CLEAN + TRIM)
Remove non-printable characters AND extra spaces
=TRIM(CLEAN(A2))Handle Division by Zero
Prevent #DIV/0! with a fallback
=IFERROR(A2/B2,0)Check if Cell is Blank
Different result for empty vs filled cells
=IF(ISBLANK(A2),"Missing",A2)Handle #N/A Specifically
Catch only #N/A errors, let others through
=IFNA(VLOOKUP(A2,B:C,2,FALSE),"Not in list")Safe INDEX-MATCH
Bulletproof lookup: INDEX-MATCH + IFERROR
=IFERROR(INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0)),"Not Found")IF with AND (All Must Match)
Check that ALL conditions are true
=IF(AND(A2>1000,B2="Active"),"Priority","Normal")IF with OR (Any Can Match)
Take action if ANY condition is true
=IF(OR(A2="Late",A2="Very Late"),"Follow Up","OK")SUMIF + Dropdown Selection
Sum based on a dropdown selection
=SUMIF(A:A,E1,C:C)Cascading Lookup (Try Multiple Sources)
Look in Sheet1, then Sheet2, then default
=IFERROR(VLOOKUP(A2,Sheet1!A:B,2,FALSE),IFERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE),"Not Found"))XLOOKUP with Match Modes
Modern lookup with exact or approximate matching
=XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B,"Not Found",0)Interactive Report with Dropdown
User picks a category, sum updates automatically
=SUMIF(A:A,E1,C:C)Sum a Named Range
Use meaningful names instead of cell references
=SUM(Revenue)Dynamic Dropdown Source
Dropdown list that grows as you add data
=OFFSET(A1,0,0,COUNTA(A:A),1)What-If Analysis (Data Table)
See how changing one input affects results
=A2*$B$1Find Missing Items Between Lists
Items in List A that are not in List B
=FILTER(A2:A100,COUNTIF(B2:B100,A2:A100)=0)Match Amounts Across Lists
Pull amounts from a second list to compare side-by-side
=XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B,"Missing")Flag Over/Under Payments
Classify each line as Match, Overpaid, or Short
=IF(C2="Missing","",IF(B2=C2,"Match",IF(B2>C2,"Overpaid","Short")))Check If Item Exists in Another List
Simple yes/no existence check
=IF(COUNTIF(Sheet2!A:A,A2)>0,"Found","Missing")Reverse Reconciliation Check
Check the other direction — items they have that we don't
=XLOOKUP(A2,'Our List'!A:A,'Our List'!B:B,"Not in our records")Extract Unique Values
Remove duplicates from a list automatically
=UNIQUE(A2:A100)Sort a Range Dynamically
Auto-sorted list that updates when source changes
=SORT(A2:A100)Filter Rows by Condition
SQL-style WHERE clause for your data
=FILTER(A2:C100,B2:B100>1000,"No results")Clean Dropdown List
Alphabetized, deduplicated list for Data Validation
=SORT(UNIQUE(A2:A100))Self-Cleaning Data Engine
Filter + deduplicate + sort in one formula
=SORTBY(UNIQUE(FILTER(A2:A100,B2:B100=E1)),UNIQUE(FILTER(A2:A100,B2:B100=E1)))Tax Bracket Lookup
Find which tax bracket an income falls into
=XLOOKUP(B2,TaxTable[Lower],TaxTable[Rate],,-1)Progressive Tax Calculation
Base tax + marginal rate on amount over bracket floor
=VLOOKUP(B2,TaxTable,3,TRUE)+(B2-VLOOKUP(B2,TaxTable,1,TRUE))*VLOOKUP(B2,TaxTable,2,TRUE)Sales Tax Calculation
Calculate sales tax rounded to the penny
=ROUND(A2*B2,2)1099 Threshold Flag
Flag vendors paid $600+ for 1099 reporting
=IF(SUMIF(A:A,A2,C:C)>=600,"Issue 1099","")Effective Tax Rate
Actual average rate paid vs marginal bracket rate
=IFERROR(D2/B2,0)Running Total (Cumulative Sum)
Continuously growing sum down a column
=SUM($B$2:B2)Running Total with Group Reset
Resets when the category changes
=IF(A2=A1,C1+B2,B2)Cumulative Sum by Category
Group-aware running total that works unsorted
=SUMIFS($C$2:C2,$A$2:A2,A2)Year-to-Date Total
Sum from January 1 through today, auto-updating
=SUMIFS(C:C,B:B,">="&DATE(YEAR(TODAY()),1,1),B:B,"<="&TODAY())Quarter-to-Date Total
Sum from quarter start through today
=SUMIFS(C:C,B:B,">="&DATE(YEAR(TODAY()),CEILING(MONTH(TODAY())-1,3)+1,1),B:B,"<="&TODAY())Overtime Hours (Weekly)
Hours over 40 in a workweek
=IF(E2>40,E2-40,0)Overtime Hours (Daily)
Hours over 8 in a single day
=IF(E2>8,E2-8,0)Overtime Pay Calculation
Regular pay + OT at 1.5x in one formula
=(MIN(40,E2)*F2)+(IF(E2>40,E2-40,0)*F2*1.5)Convert Time to Decimal Hours
Turn 8:30 into 8.5 for payroll math
=(C2-B2)*24Gross Pay with Overtime
Single formula for total gross pay
=IF(G2>40,(40*F2)+((G2-40)*F2*1.5),G2*F2)Flag Duplicate Values
Mark ALL occurrences of duplicated values
=IF(COUNTIF(A:A,A2)>1,"Duplicate","")Flag Only Repeat Occurrences
Mark 2nd, 3rd, etc. but not the first instance
=IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","")Count Occurrences
How many times does each value appear?
=COUNTIF(A:A,A2)Extract Values Appearing Once
List items that have NO duplicates
=FILTER(A2:A100,COUNTIF(A2:A100,A2:A100)=1)Cross-Column Duplicate Check
Does this value also exist in another column?
=IF(COUNTIF(B:B,A2)>0,"Also in Column B","Unique to A")Current Month Total
Sum for the current calendar month, auto-updating
=SUMIFS(C:C,B:B,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),B:B,"<"&DATE(YEAR(TODAY()),MONTH(TODAY())+1,1))Month-over-Month % Change
Percentage change from last month to this month
=IFERROR((E2-D2)/D2,0)Same Month Last Year
Total for this month one year ago
=SUMIFS(C:C,B:B,">="&DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B:B,"<"&DATE(YEAR(TODAY())-1,MONTH(TODAY())+1,1))Quarter-over-Quarter Delta
Difference between this quarter and last quarter
=SUMIFS(C:C,B:B,">="&DATE(2024,4,1),B:B,"<"&DATE(2024,7,1))-SUMIFS(C:C,B:B,">="&DATE(2024,1,1),B:B,"<"&DATE(2024,4,1))Dynamic Current Month Sum
Uses EOMONTH for precise month boundaries
=SUMIFS(C:C,B:B,">="&EOMONTH(TODAY(),-1)+1,B:B,"<="&EOMONTH(TODAY(),0))Negatives in Parentheses
Accounting-standard display for negative numbers
=IF(A2<0,"("&TEXT(ABS(A2),"#,##0.00")&")",TEXT(A2,"#,##0.00"))Abbreviate to Thousands (K)
Show 1,500,000 as 1,500.0K
=TEXT(A2/1000,"#,##0.0")&"K"Abbreviate to Millions (M)
Show 4,200,000 as 4.2M
=TEXT(A2/1000000,"#,##0.0")&"M"Dynamic Quarter Label
Generate "Q1 FY2024" from any date
="Q"&ROUNDUP(MONTH(A2)/3,0)&" FY"&YEAR(A2)Suppress Zeros (Show Blank)
Hide zero values for cleaner reports
=IF(A2=0,"",A2)ROUND vs ROUNDUP vs ROUNDDOWN
Three rounding behaviors compared
=ROUND(A2,2)Round Lines Then Sum
Round each line item first, then total — audit-safe
=SUMPRODUCT(ROUND(A2:A10*B2:B10,2))Sum Then Round
Sum raw values, round only the final total
=ROUND(SUM(C2:C100),2)Round to Nearest Increment
Round to nearest 5, 10, 25, etc.
=MROUND(A2,0.05)Fix Penny Discrepancy
Assign the rounding remainder to one line
=B2-SUM(C3:C10)Outstanding Checks Total
Sum of checks written but not yet cleared the bank
=SUMIF(D:D,"",C:C)Cleared Items Total
Sum of items that have cleared the bank
=SUMIF(D:D,"<>",C:C)Adjusted Book Balance
Starting balance + deposits - checks - fees
=B1+SUMIF(A:A,"Deposit",C:C)-SUMIF(A:A,"Check",C:C)-SUMIF(A:A,"Fee",C:C)Bank Rec Variance
Difference between bank and book — should be zero
=E2-F2Deposits in Transit
Recorded in books but not yet on bank statement
=SUMIFS(C:C,A:A,"Deposit",D:D,"")