Skip to main content
AmigoFish

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.

Watch on YouTube

Not rated yet

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

Back to Leila Gharani · Report a problem