N Function in Excel: Turn a Value Into a Number
N returns numbers unchanged, TRUE as 1 and text as 0. Its oddest use is hiding a note inside a formula.
Read article →Excel
Excel formulas, unusual formula combinations, VBA, Power Query and dashboards — from basics to clever tricks.
DVAR Function in Excel: Sample Variance of Matching Records
DVAR Function in Excel: Sample Variance of Matching Records
DVAR returns the sample variance for matching records. Like all D-functions it uses a database range and a criteria range you can edit on…
Read article →IMABS Function in Excel: Size (Modulus) of a Complex Number
IMABS Function in Excel: Size (Modulus) of a Complex Number
IMABS returns |z|, the length of the vector: SQRT(a²+b²). For impedance it's the magnitude in ohms. Complex numbers are text like 3+4i; build them…
Read article →Combine Data From Multiple Sheets Into One in Excel (VSTACK, FILTER)
Combine Data From Multiple Sheets Into One in Excel (VSTACK, FILTER)
Stack monthly or branch sheets into one live list with VSTACK and 3D references, drop blank rows with FILTER, tag each row with its…
Read article →Power Query Intermediate Lesson 8: Project – ERP Register to HSN-wise GST Summary
Power Query Intermediate Lesson 8: Project – ERP Register to HSN-wise GST Summary
Final project: keep only the latest revision of each invoice, compute GST and build an HSN-wise summary for GSTR-1 style reporting, refreshable every month.
Read article →Power Query Intermediate Lesson 7: Two Header Rows – Unpivot a Region-over-Month Report
Power Query Intermediate Lesson 7: Two Header Rows – Unpivot a Region-over-Month Report
Turn a report with merged Region headers over Month headers into a clean table: transpose, fill down, combine header rows, unpivot and split.
Read article →Power Query Intermediate Lesson 6: Custom Functions – Clean Every File the Same Way
Power Query Intermediate Lesson 6: Custom Functions – Clean Every File the Same Way
Write a custom M function that skips junk header lines, promotes headers and sets types, then apply it to every file in a folder…
Read article →Power Query Intermediate Lesson 5: Custom Columns in M – Clean Text, Digits and try … otherwise
Power Query Intermediate Lesson 5: Custom Columns in M – Clean Text, Digits and try … otherwise
Write custom columns in M: proper-case names, 10-digit Indian mobile numbers, PIN codes and amounts from text like "Rs. 2,250", with try ... otherwise…
Read article →Power Query Intermediate Lesson 4: Group By With All Rows – Latest Record per Customer
Power Query Intermediate Lesson 4: Group By With All Rows – Latest Record per Customer
Keep the latest credit limit per customer with Group By, All Rows and Table.Max, count changes, and restore column types after expanding records.
Read article →Power Query Intermediate Lesson 3: Fuzzy Merge for Misspelt Names
Power Query Intermediate Lesson 3: Fuzzy Merge for Misspelt Names
Match "Sharma Trader", "Mehta and Sons" and "sharma traders" to the right master record with fuzzy merge, and tune the similarity threshold.
Read article →Bank Reconciliation in Excel with XLOOKUP and FILTER
Bank Reconciliation in Excel with XLOOKUP and FILTER
Match your books with the bank statement: build a matching key, find entries missing on either side with XLOOKUP, handle duplicates with COUNTIFS and…
Read article →Power Query Intermediate Lesson 2: Merge Kinds – Unpaid Invoices and Orphan Payments
Power Query Intermediate Lesson 2: Merge Kinds – Unpaid Invoices and Orphan Payments
Use Left Anti and Right Anti merges to find invoices with no payment and payments with no invoice, and know when to use each…
Read article →