Get the Python in Excel Workbook
Grab the ready-to-use Excel file from the video. Run pd.to_datetime, pd.melt, and sns.swarmplot on the exact same data used in the tutorial.
Trick 1: Fix Messy Dates in Seconds with pd.to_datetime()
To clean inconsistent dates, type =PY in an empty cell, enter pd.to_datetime(), point it at your messy date, and press Ctrl + Enter. It reads almost any date format and returns a clean, real date.
Here’s why this one matters to me. At my old job, messy dates were constant. People overwrote my data validation with their own formats, and SAP exports came in with dates Excel refused to recognize. I tried reformatting the cells: nothing. I tried the YEAR function, sure it was smart enough to cope: it wasn’t. So I fixed them by hand, cell by cell, across hundreds of rows.
The reason reformatting fails is simple. Those values aren’t dates, they’re text. Excel can’t convert them and DATEVALUE chokes the moment the format varies. pd.to_datetime() reads them all anyway, in one pass.
After you commit the formula, the cell shows a Python object, not a date. That’s expected. Switch the output from Python object to Excel Value, copy it down, and the whole column comes out clean, whether the original said “31st of ’25” or “3rd November.” pd.to_datetime() comes from the pandas library, which is already loaded, so there’s nothing to import.
Watch the day-vs-month order. On ambiguous dates like 03/04, pandas may guess wrong. Add dayfirst=True inside the brackets to force day-first reading.
Trick 2: Unpivot Data with pd.melt()
To unpivot data trapped in separate columns into a single column, use pd.melt(), set id_vars= to the column you want to keep, then spill the result with Ctrl + Alt + Shift + M.
This is the job formulas make painful and Power Query handles but not always quickly. Say you have quarterly sales spread across four columns and you need them stacked in one. Type =PY, enter pd.melt(), reference your data, and set id_vars= to the column that should stay put, like Product. Commit, then spill.
You’ll get three columns. The default headers are “variable” and “value,” which tells you nothing, so rename them: var_name="Quarter" and value_name="Sales".
To make it dynamic so it updates as you add rows, extend the referenced range past your current data, then clean up the overflow. Add .dropna() to drop the blank rows, and .reset_index(drop=True) to remove the leftover index gaps. Now a new product row flows straight into the output with no rework.
Free workbook. Instant download.
Trick 3: Spot Patterns Instantly with a Seaborn Swarm Plot
To reveal clusters and outliers in seconds, use sns.swarmplot() with x= for your category and y= for your values, then right-click and choose Display Plot over Cells.
Analysis comes down to finding patterns that drive better decisions, and you won’t find them scrolling a table. A swarm plot shows every data point grouped by category, so clusters and outliers are obvious at a glance.
Type =PY, enter sns.swarmplot(), and select your data. One snag worth knowing: if your data is an Excel table, click the table’s edge so the headers come along too, otherwise the chart can’t read your column names. Set x= to your category, like Shipping Method, and y= to your values, like Delivery Time. Commit, then right-click the image object and display it over the cells.
sns.swarmplot() plots every delivery time grouped by shipping method. Standard and Express carry the heavy clusters; Same Day and Overnight stay low and tight.To get more out of it, add hue="Package Type". The points split by color, and suddenly you can see that fragile packages take longer across overnight, same-day, and express shipping. That’s an insight that’s invisible in the raw numbers.
Python in Excel vs Power Query vs Formulas: When to Use Each
All three clean and reshape data; the right one depends on the job. Here’s when to reach for each.
| Tool | Best for | Skip it when |
|---|---|---|
| Python in Excel | Messy or large data, advanced visuals, and one-liners that replace monster formulas. | You need to save files, rename sheets, or run macros. It can’t automate. |
| Power Query | A repeatable, refreshable pipeline that pulls from external sources and updates with one click. | You need statistical charts or quick one-off analysis. |
| Formulas | Simple tasks where you want live recalculation with zero setup. | The logic gets long, nested, or hard to maintain. |
One limit to know: Python in Excel runs in a secure cloud container, so it can’t save files, rename sheets, or run macros. For automation like that, VBA or Office Scripts is the better fit.
Frequently Asked Questions
Why does =PY return a #CALC! error?
In a Python cell, #CALC! almost always points to the data, not your license. The most common causes:
- The referenced range exceeds the 100 MB Python in Excel calculation limit
- The range includes volatile cells (like RAND, NOW, or TODAY)
- It pulls in cells with errors or unsupported Power Query results
Try shrinking the range, swapping volatile values for static ones, or referencing the underlying data directly. If you’re seeing #BLOCKED! instead, that’s a license or feature-availability error.
Is Python in Excel free?
It’s included with qualifying Microsoft 365 plans at no extra cost. You get standard compute free, which handles everyday cleanup and charts like the ones in this tutorial. Heavy or fast workloads can use premium compute, which is a paid add-on, but you don’t need it for these three tricks.
Do I need to know how to code to use these tricks?
No. Each one is a single line you type into a cell. If you can write an IF function, you can do this. No Python background required.
Does Python in Excel work offline?
No. The code runs in a secure Microsoft Cloud container, so you need an internet connection. Nothing installs on your machine.
Get the Practice Workbook
All three examples ready to run: messy dates, the dynamic unpivot, and the Seaborn chart.
Take It Further
These three tricks are a small sample. Bullet charts, forecasting, outlier detection, and pulling in external data are all within reach, no programming background needed.
The full Python in Excel for the Real World course shows exactly where Python fits in your daily Excel workflow, with real cleanup, analysis, and visualization tasks you can apply the same day. Over 3,000 members already joined.
Popular Course
Python in Excel For the Real World
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
