Search formulas by task, category, or keyword. Each entry includes formula syntax, examples, explanation, use cases, and mistakes to avoid.
Try “paid orders”, “lookup”, “GST”, “attendance”, or a function name.
Check a condition and return one value when it is true and another when it is false.
IF evaluates the logical test first, then returns the matching true or false result.
Return a fallback value instead of showing an Excel error.
IFERROR returns the original formula result when successful and the fallback value when any error occurs.
Add amounts when a related cell matches one criterion.
SUMIF checks each cell in the criteria range and adds the corresponding value from the sum range when it matches.
Add amounts only when every selected condition is satisfied.
SUMIFS evaluates multiple criteria ranges and adds only rows where all conditions match.
Count records only when every condition matches.
COUNTIFS checks the conditions in parallel and counts rows where all criteria are true.
Perform a flexible exact lookup without requiring the lookup column to be first.
MATCH finds the row position of the lookup value and INDEX returns the value at that position.
Convert a date or number into readable formatted text.
TEXT applies the selected display format and returns the result as text.
Return a chosen number of characters from the beginning of a text value.
LEFT starts at the first character and returns the requested number of characters.
Create a dynamic list containing only records that match your criteria.
FILTER evaluates the include condition and spills matching rows into the result area.
Return distinct values from a range without permanently deleting rows.
UNIQUE scans the source range and spills each distinct value once.
Calculate margin percentage from selling price and total cost.
Subtract cost from selling price to get profit, divide by selling price, then multiply by 100.
Add GST or tax percentage to a taxable value.
The formula multiplies the base amount by one plus the tax rate.
Reverse-calculate taxable value from a price that already includes GST.
The inclusive amount is divided by one plus the GST rate to remove tax.
Calculate completed age in years from a date of birth.
DATEDIF counts the number of full years between the birth date and today.
Add sales amounts where order dates fall inside a selected month.
SUMIFS checks dates from the month start up to the day before the next month.
Count how many rows match a status such as Paid, Pending, Shipped, or Cancelled.
COUNTIF checks each status cell and counts exact matches.
Flag invoice numbers that appear more than once.
COUNTIF counts how many times the current invoice number appears in the full invoice column.
Create invoice numbers with a prefix, year, and padded sequence number.
The formula joins fixed text, the year, and a four-digit sequence number.
Calculate commission from sales value and commission rate.
Multiply the sales amount by the percentage commission rate.
Calculate attendance percentage from present days and working days.
The formula divides present days by total working days.
Calculate overtime payment from extra hours and hourly rate.
MAX prevents negative overtime, then multiplies extra hours by the rate.
Look up a SKU and return the matching price.
XLOOKUP searches the SKU column and returns the matching price from the price column.
Use VLOOKUP to return a price from a product table.
VLOOKUP searches the first column of the table and returns a value from the selected column number.
Return financial year label for dates when the year starts in April.
The formula checks the month and creates the correct financial year range.
Try a broader task such as lookup, date, sales, text, or accounting.
Use the library when you know the spreadsheet problem but cannot remember the formula. Search for business words such as GST, profit margin, invoice, attendance, age, monthly sales, commission, XLOOKUP, or duplicate invoices. Then copy the formula and replace the sample cell references with your own workbook columns.
It is a searchable reference of formulas with syntax, examples, explanations, use cases, and common mistakes.
The library includes lookup, logical, date, accounting, HR, ecommerce, sales, and text-related formulas.
Yes. Each formula card includes copy-ready examples you can adapt to your workbook.