The 1-Formula Fix for Excel’s Messiest Tables
From Leila Gharani · · 1 min

The player loads from YouTube only when you click. It uses YouTube's privacy-enhanced mode.
If the video does not play, the owner may have turned off embedding. Watch it on YouTube instead.
Description
Learn Python in Excel: https://www.xelplus.com/course/python-in-excel/
Sales data split across monthly columns? Here's how to reshape it into one clean table, with month, sales, and units each in their own column, using Python inside Excel.
=PY pd.wide_to_long(sales_data, stubnames=["sales","units"], i="sales rep", j="month", sep="_", suffix=".+").reset_index()
- Type =PY and press Tab to activate Python mode - wide_to_long turns repeated column groups (like sales_Jan, units_Jan) into rows - stubnames lists the values you're tracking - i is your identifier column, like sales rep - j names the new column that holds the month - sep matches the separator in your source headers - suffix=".+" means the leftover text can be anything - reset_index() keeps everything aligned - Right-click the Python output and choose "Excel Value" to drop it into cells
Add a new month to your source data and the table updates on its own.
Uses the pandas wide_to_long function. Check the pandas docs for the full reference.
📨 Subscribe to "Between the Sheets" newsletter to stay on top of office tips and Excel hacks: https://link.xelplus.com/yt-d-newsletter
🔓 Join this channel to get access to perks: https://www.youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join
📲 Let’s connect on social: Instagram: https://www.instagram.com/lgharani LinkedIn: https://www.linkedin.com/in/leilagharani
#Excel