Excel LAMBDA Lesson 5: REDUCE and SCAN (Running Balances, Multi-Replace)
Use REDUCE to fold a list into one answer and SCAN to keep every step: running bank balances, compound growth and a MULTIREPLACE function.
Read article →Excel
Excel formulas, unusual formula combinations, VBA, Power Query and dashboards — from basics to clever tricks.
Rolling Totals in Excel: Last 7 Days, Last 30 Days and Moving Averages
Rolling Totals in Excel: Last 7 Days, Last 30 Days and Moving Averages
Sum or count the last 7 or 30 days with SUMIFS and TODAY(), build a 7-day moving average, a trailing-12-month total, and a rolling…
Read article →Excel LAMBDA Lesson 4: Financial-Year Date Functions
Excel LAMBDA Lesson 4: Financial-Year Date Functions
FYSTART, FYLABEL, FYQTR, DAYSLEFT and AGEYEARS as LAMBDA functions for the Indian April-March financial year.
Read article →Excel LAMBDA Lesson 3: Text Cleaning Functions
Excel LAMBDA Lesson 3: Text Cleaning Functions
CLEANNAME, INITIALS, DIGITSONLY, WORDCOUNT and MASK: custom LAMBDA functions for messy names, phone numbers and sensitive data.
Read article →Excel LAMBDA Lesson 2: GST, Lakhs and Money Functions
Excel LAMBDA Lesson 2: GST, Lakhs and Money Functions
Build GSTINCL, GSTEXCL, GSTONLY, CGST/SGST, LAKHS, CRORE and ROUNDTO5 as reusable LAMBDA functions for Indian invoices and reports.
Read article →Excel LAMBDA Lesson 1: Your First Custom Function
Excel LAMBDA Lesson 1: Your First Custom Function
What LAMBDA is, how to test it inline, save it in Name Manager and call it like a built-in function. With the difference between…
Read article →VBA Files and Folders + Final Project: Combine a Month of Reports Automatically
VBA Files and Folders + Final Project: Combine a Month of Reports Automatically
Excel VBA course lesson 15: loop through files with Dir, create folders, check whether files exist, read text/CSV files, then build the final project…
Read article →Bank Statement Lesson 8: Project – Monthly Expense Dashboard
Bank Statement Lesson 8: Project – Monthly Expense Dashboard
Final project: category totals, top category, most expensive month, food share and savings rate on a one-page expense dashboard with charts.
Read article →Bank Statement Lesson 7: Combine Monthly Statements With Power Query
Bank Statement Lesson 7: Combine Monthly Statements With Power Query
Drop each month's CSV into a folder and refresh: Power Query combines, cleans and appends them automatically, with India date locale and amount cleaning.
Read article →Bank Statement Lesson 6: Bank Reconciliation in Excel
Bank Statement Lesson 6: Bank Reconciliation in Excel
Match your books with the bank statement using COUNTIFS on amount and date, allow for posting delays, and list unmatched entries for the reconciliation…
Read article →Bank Statement Lesson 5: Monthly Summary and Cash Flow
Bank Statement Lesson 5: Monthly Summary and Cash Flow
Monthly income and spending, savings rate, lowest balance, average UPI spend and the biggest debits: a cash-flow summary from your statement with SUMIFS.
Read article →Bank Statement Lesson 4: Read UPI Narrations (Payee, UPI ID, Reference)
Bank Statement Lesson 4: Read UPI Narrations (Payee, UPI ID, Reference)
Split UPI narrations like UPI/DR/412345678901/SWIGGY/YESB/swiggy@ybl/Payment into reference number, payee name, bank and UPI ID with TEXTSPLIT and TEXTAFTER.
Read article →