5 Ways to Find the Last Row in Excel VBA (and When Each One Fails)
Five ways to find the last used row in Excel VBA — End(xlUp), UsedRange, Find, CurrentRegion and ListObject — with the traps each one…
Read article →Category Archives: Excel
Excel formulas, unusual formula combinations, VBA, Power Query and dashboards — from basics to clever tricks.
Automate SAP from Excel: Run a Report and Export It with SAP GUI Scripting
Automate SAP from Excel: Run a Report and Export It with SAP GUI Scripting
Step-by-step: enable SAP GUI Scripting, record a transaction, and run it from Excel VBA to export a report automatically.
Read article →LET and LAMBDA: Write Your Own Excel Functions (No VBA)
LET and LAMBDA: Write Your Own Excel Functions (No VBA)
Use LET to name parts of long formulas and LAMBDA to create reusable custom functions like CLEANTEXT or FYEAR — saved in Name Manager,…
Read article →The Complete Guide to Excel Lookups: VLOOKUP, XLOOKUP, INDEX-MATCH and Beyond
The Complete Guide to Excel Lookups: VLOOKUP, XLOOKUP, INDEX-MATCH and Beyond
Everything about looking up data in Excel in one place: exact and approximate matches, left lookups, two-way lookups, multiple criteria, last match, returning all…
Read article →Dependent Dropdown Lists in Excel (Two Methods That Actually Work)
Dependent Dropdown Lists in Excel (Two Methods That Actually Work)
Create a second dropdown that changes with the first — Category then Product, Country then City — using INDIRECT with named ranges or a…
Read article →Excel Pivot Tables: The Complete Guide from First Click to Dashboard
Excel Pivot Tables: The Complete Guide from First Click to Dashboard
Build pivot tables step by step: layout, value settings, Show Values As, grouping dates, slicers and timelines, calculated fields, distinct counts, GETPIVOTDATA, refresh tricks…
Read article →Remove Duplicates in Excel: 3 Quick Ways (and Which to Use)
Remove Duplicates in Excel: 3 Quick Ways (and Which to Use)
Three ways to remove duplicates in Excel — the Remove Duplicates button, the UNIQUE function and Advanced Filter — with when each is the…
Read article →Why 0.1 + 0.2 Is Not Exactly 0.3 (and Why Excel Sometimes Agrees)
Why 0.1 + 0.2 Is Not Exactly 0.3 (and Why Excel Sometimes Agrees)
Computers store numbers in binary, so 0.1 cannot be stored exactly. Why that makes 0.1+0.2 ≠ 0.3, how it shows up in Excel, Python…
Read article →Send Emails from Excel with VBA and Outlook (With Attachments)
Send Emails from Excel with VBA and Outlook (With Attachments)
A tested Excel VBA macro that sends personalised Outlook emails from a list — with attachments, CC, a preview mode and a log column.
Read article →Excel Tables (Ctrl+T): 10 Reasons to Use Them for Every List
Excel Tables (Ctrl+T): 10 Reasons to Use Them for Every List
Formatting a range as an Excel Table gives you auto-expanding formulas, readable references, slicers, totals and pivot sources that never miss new rows. Ten…
Read article →Excel Date Formulas Cheat Sheet: 25 Formulas You Will Actually Use
Excel Date Formulas Cheat Sheet: 25 Formulas You Will Actually Use
A cheat sheet of Excel date formulas: first and last day of the month, quarters, Indian financial year, working days, age, week numbers, adding…
Read article →In-Cell Bar Charts in Excel: REPT, Data Bars and Sparklines
In-Cell Bar Charts in Excel: REPT, Data Bars and Sparklines
Three ways to show charts inside cells — the REPT formula trick, conditional-formatting Data Bars and Sparklines — for compact dashboards that update automatically.
Read article →