Excel Waterfall Automation with Data Sanitizing
Darren Pierson
Project Summary
A client needed monthly updates to an Excel waterfall. Their process was copy and paste from a monthly export into the Export sheet, then paste totals into YTD columns, which fed the Raw Data tab via vlookups and finally the charts. It worked but was slow, error prone, and lacked consistency in data handling.
What I Changed
Built a Working table fed by Power Query to standardize and sanitize the monthly export
Used VBA to load the new month, fill the month columns on Export, and refresh totals
Kept the existing workbook layout so charts and vlookups continued to work without redesign
Result
Update time reduced from manual pasting to a one-click run
Consistent mapping of Sales and Sales LY with fewer copy errors
Process is repeatable, sanitized, and ready for additional months without rework
Tech Used
Excel, VBA, Power Query, XLOOKUP, structured tables
Like this project
Posted Aug 11, 2025
Automated Excel waterfall updates with Power Query and VBA, including data sanitizing. Cut manual work to one click while ensuring accuracy and consistency.
Likes
0
Views
2
