CLEAN Function in Excel: Remove Non-Printing Characters
CLEAN removes invisible control characters (like line breaks from system exports) that break lookups and look like spaces.
Read article →Excel
Excel formulas, unusual formula combinations, VBA, Power Query and dashboards — from basics to clever tricks.
VLOOKUP Approximate Match: Tax Slabs, Commissions and Grades
VLOOKUP Approximate Match: Tax Slabs, Commissions and Grades
Use approximate-match lookups for slabs and bands: how TRUE/-1 matching works, building a bands table, commission and grade examples, progressive tax slabs with SUMPRODUCT,…
Read article →SUBSTITUTE Function in Excel: Replace Specific Text
SUBSTITUTE Function in Excel: Replace Specific Text
SUBSTITUTE replaces existing text with new text — all occurrences or just the n-th one.
Read article →ACCRINT Function in Excel: Interest Earned Since Last Coupon
ACCRINT Function in Excel: Interest Earned Since Last Coupon
ACCRINT calculates interest that has built up on a bond — the amount a buyer pays the seller on top of the quoted price.
Read article →PROPER Function in Excel: Capitalise Each Word
PROPER Function in Excel: Capitalise Each Word
PROPER capitalises the first letter of each word; UPPER and LOWER change the whole text.
Read article →CODE Function in Excel: Character Code of the First Letter
CODE Function in Excel: Character Code of the First Letter
CODE returns the number of the first character. It is the detective tool for “invisible” characters that break lookups: a non-breaking space shows up…
Read article →YIELD Function in Excel: Bond Yield From Its Price
YIELD Function in Excel: Bond Yield From Its Price
YIELD is the reverse of PRICE: what annual return you lock in if you buy at today’s price and hold to maturity.
Read article →TRIM Function in Excel: Remove Extra Spaces
TRIM Function in Excel: Remove Extra Spaces
TRIM removes leading and trailing spaces and reduces multiple spaces between words to one. Essential after copying data from websites and ERPs.
Read article →VDB Function in Excel: Depreciation for Any Span
VDB Function in Excel: Depreciation for Any Span
VDB returns declining-balance depreciation between any two periods and, by default, switches to straight-line when that gives more — as many tax rules expect.
Read article →PI Function in Excel: The Value of π
PI Function in Excel: The Value of π
PI returns 3.14159265358979, accurate to 15 digits. It takes no arguments but still needs the brackets.
Read article →SYD Function in Excel: Sum-of-Years’ Digits Depreciation
SYD Function in Excel: Sum-of-Years’ Digits Depreciation
SYD is another accelerated method, gentler than DDB. For a 5-year life the fractions are 5/15, 4/15, 3/15 and so on.
Read article →DDB Function in Excel: Double-Declining Depreciation
DDB Function in Excel: Double-Declining Depreciation
DDB front-loads depreciation: more in early years, less later. It suits laptops and vehicles that lose value fast.
Read article →