Highlight an Entire Row in Excel With a Formula (Conditional Formatting)
Colour the whole row when status is Overdue, an amount crosses a limit or a date has passed: conditional formatting formulas with the $…
Read article →Formula Combos
Unusual formula combinations that turn hours of manual work into one cell.
GST Calculation in Excel: Add GST, Remove GST, Split CGST/SGST/IGST
GST Calculation in Excel: Add GST, Remove GST, Split CGST/SGST/IGST
Formulas to go from GST-exclusive to inclusive, back-calculate the base from an inclusive price, split CGST/SGST vs IGST by state, look up rates by…
Read article →Running Total and Running Count in Excel (Classic, Table and SCAN Methods)
Running Total and Running Count in Excel (Classic, Table and SCAN Methods)
Cumulative totals, running counts, running totals by category and month-to-date sums — with expanding ranges, SUMIFS, Excel Table formulas and the new SCAN function…
Read article →Calculate Age and Tenure in Excel: Years, Months and Days (DATEDIF and YEARFRAC)
Calculate Age and Tenure in Excel: Years, Months and Days (DATEDIF and YEARFRAC)
Exact age or service tenure as "5 years 3 months 12 days", completed years for gratuity, age on a specific date, decimal years with…
Read article →Look Up the 2nd, 3rd or Nth Match in Excel
Look Up the 2nd, 3rd or Nth Match in Excel
VLOOKUP only finds the first match. Return a customer’s Nth order, the first N rows, the last match or the Nth largest per group…
Read article →SUMIFS Between Two Dates and With OR Conditions in Excel
SUMIFS Between Two Dates and With OR Conditions in Excel
Total sales between two dates, for this month, or for “North OR East”: SUMIFS with date operators, array constants, SUMPRODUCT and FILTER, with the…
Read article →Get the Latest Record for Each Customer in Excel (Last Price, Last Order, Last Status)
Get the Latest Record for Each Customer in Excel (Last Price, Last Order, Last Status)
Find each customer’s most recent order date, last price paid or latest status with MAXIFS, XLOOKUP search-from-last, SORTBY+UNIQUE and LOOKUP(2,1/…) — so duplicates show…
Read article →Working Days and Due Dates in Excel: Skip Weekends and Holidays
Working Days and Due Dates in Excel: Skip Weekends and Holidays
Calculate due dates N working days ahead, count working days per month, handle Sunday-only or Friday–Saturday weekends, and keep a holiday list — with…
Read article →Sum, Average or Max While Ignoring #N/A and Errors in Excel (AGGREGATE)
Sum, Average or Max While Ignoring #N/A and Errors in Excel (AGGREGATE)
One #N/A ruins a SUM. Ignore errors with AGGREGATE, SUMIF and IFERROR inside arrays, count the errors so they stay visible, and know when…
Read article →Receivables Ageing in Excel: 0–30, 31–60, 61–90, 90+ Day Buckets
Receivables Ageing in Excel: 0–30, 31–60, 61–90, 90+ Day Buckets
Build an accounts receivable ageing report: days overdue from the due date, bucket labels with IFS or a lookup table, totals per bucket and…
Read article →EMI and Loan Amortisation Schedule in Excel (PMT, IPMT, PPMT)
EMI and Loan Amortisation Schedule in Excel (PMT, IPMT, PPMT)
Calculate a home or car loan EMI with PMT, split each EMI into interest and principal, build the full month-by-month schedule and see the…
Read article →Count Unique Values With Criteria in Excel (365 and Older Versions)
Count Unique Values With Criteria in Excel (365 and Older Versions)
How many different customers bought in North? How many distinct products did Asha sell? Count unique values with UNIQUE+FILTER, SUMPRODUCT+COUNTIFS for older Excel, and…
Read article →