Excel Waterfall Automation with Data Sanitizing

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.

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

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