Why Excel Users Should Learn pandas (And Why Most Don't)
The honest truth: most Excel users do not need pandas to do their day jobs. Excel and Power Query can handle 90% of cleaning tasks on files under 100,000 rows. So why bother? Three reasons, in my experience. First, pandas is reproducible — you save a script and re-run it on next month's file, no clicks to repeat. Second, pandas handles files that Excel chokes on (millions of rows, repeated operations across many files). Third, pandas is a prerequisite for almost every data science and analytics job posting in 2026, so the time you invest pays back in salary and options.
Here is the mental bridge that helped me most when I was learning: think of pandas DataFrames as Excel workbooks, and individual pandas Series as Excel columns. Most Excel operations you already know — sort, filter, pivot, vlookup, drop duplicates — have direct pandas equivalents. You are not learning a new way of thinking, you are learning a new syntax for thinking you already do.
15 rows of customer data with intentional issues: missing values, whitespace, inconsistent casing, duplicate rows, and one outlier. Clean it step by step in this walkthrough.
Setup: Install pandas and Open the File
Before any cleaning, you need pandas installed and the file loaded. If you have not used Python before, the standard install path is the Anaconda distribution, which bundles Python, pandas, and Jupyter Notebook together so you can start writing code immediately. If you already have Python, install pandas with one command.
In a terminal, run: pip install pandas. If you have Anaconda, it is already installed. Verify by running python -c 'import pandas; print(pandas.__version__)' — any 2.x version is fine. I recommend 2.x because some old 1.x tutorials use deprecated syntax that no longer works.
In the same terminal, run: jupyter notebook. Your browser opens a file browser. Create a new Python 3 notebook. Each cell in the notebook runs Python code, and the output (tables, print statements, charts) appears below the cell. Jupyter is the standard pandas workflow — it lets you build the cleaning step by step and see the result after each step.
In the first cell, write: import pandas as pd. In the second cell, write: df = pd.read_csv('/path/to/messy_customers.csv'). Print the data with: df.head() and df.info(). You should see 15 rows and the columns CustomerID, Name, Email, SignupDate, Country, AmountSpent, Status. That is your working DataFrame for the rest of this guide.
Always run df.info() first. It tells you exactly how many non-null values are in each column, so you see the missing values immediately. I have caught every kind of dirty data issue from df.info() — it is the single most useful first command in pandas.
Step 1: Remove Duplicate Rows
The first thing I clean in any dataset is duplicates, because they silently inflate every later total. The practice file has two duplicates of Charlie Brown and two of Bob Johnson — if you do not remove them, your customer count is wrong, every aggregate is wrong, and your chart is wrong. In Excel, you would use Data > Remove Duplicates. In pandas, the same idea is one line.
Run: df.duplicated().sum(). That tells you how many duplicate rows there are. For the practice file, the answer is 2 — exactly the two repeated rows. Now check which ones: df[df.duplicated(keep=False)].sort_values('CustomerID'). You can now see both copies of each duplicate pair.
Run: df = df.drop_duplicates(). Check the row count again: print(len(df)) — it should now be 13 instead of 15. The two duplicate rows are gone, and every later aggregate will use the de-duplicated data.
If the unique identifier is just CustomerID (not the whole row), use df.drop_duplicates(subset='CustomerID') instead. That removes any row with a duplicate ID, even if other columns differ. Use this when the row is the entity, not the whole row.
Always inspect duplicates before removing them. I learned this the hard way — I dropped 'duplicates' that turned out to be legitimate repeated transactions where a customer bought twice on the same day. Run df.duplicated(keep=False) first so you see all copies and decide whether dropping is correct.
Step 2: Trim Whitespace and Fix Casing
The practice file has invisible whitespace at the start and end of several Name cells (look at ' Alice Smith ' and ' Charlie Brown '). In Excel you can spot them; in code they survive any exact-match comparison. So if you try to count how many customers are named 'Alice Smith' exactly, you get 0 because the value is ' Alice Smith '. The fix is two operations: trim whitespace and normalize casing.
Run: df['Name'] = df['Name'].str.strip(). The .str.strip() method removes leading and trailing whitespace from every value in the column. Print df['Name'].head() and you can see 'Alice Smith' instead of ' Alice Smith '. The change is permanent — pandas overwrites the column.
The practice file has 'CANADA' in some rows and 'Canada' in others, plus 'Active' and 'active' and ' active' in Status. Run: df['Country'] = df['Country'].str.strip().str.title() and df['Status'] = df['Status'].str.strip().str.title(). Now Country is consistently 'Canada' (or 'Usa', 'Uk', 'Australia') and Status is consistently 'Active' or 'Inactive'. Every grouping and aggregation will now work correctly.
The practice file has 'BOB@example.com' and 'alice@example.com'. Emails are case-insensitive, so normalize them: df['Email'] = df['Email'].str.strip().str.lower(). Now duplicates on email can be detected by string match, and any email-based join to another table will work.
The order matters. Trim first, then change casing. If you title-case first, ' CANADA ' becomes ' Canada ' and the trailing space survives. Strip before any other string transformation — it is the cheapest operation and prevents a whole class of bugs.
Step 3: Handle Missing Values
The practice file has missing Email (Charlie Brown), missing AmountSpent (Charlie Brown, Jack Taylor, Mia Wilson), and a missing Email. In Excel, blank cells are common; in pandas, missing values are NaN by default, and they silently break any math. The right way to handle them depends on the column.
Run: df.isna().sum(). You should see Email: 1, AmountSpent: 3. Knowing exactly where the NaNs are is the first step — you cannot fix what you cannot see.
For AmountSpent, the right move depends on the analysis. If you are doing a sum across all customers, fill with 0: df['AmountSpent'] = df['AmountSpent'].fillna(0). If you are computing the average among active customers, you want to exclude missing values from the mean, so leave them as NaN and use df['AmountSpent'].mean(skipna=True) (which is the default). For Email, missing is fine — do not fill it with 'unknown@unknown.com' because that is a fake email and will pollute any downstream join.
If the missing value is on the join key (CustomerID, Email), the row is unusable for joins and you can drop it: df = df.dropna(subset=['CustomerID']). For analytical columns like AmountSpent, do not drop — that removes data the analyst may want to keep, just with a null in one cell.
Never use a global df.dropna() on real data — it drops any row with even one missing value and silently removes data you wanted to keep. Always drop on a specific subset (the join keys or the columns that matter for this analysis). I have seen analysts drop 40% of a real dataset because they used dropna() without a subset, and it took two days to figure out where the data went.
Step 4: Fix Data Types
The practice file has SignupDate in two formats: '2025-01-12' (ISO) and '2025/02/03' (slash). And AmountSpent is text-like in some rows. In Excel, dates sort correctly regardless of format; in pandas, you must convert types explicitly or every date-based analysis breaks. Same for numbers — you cannot sum a column that is text.
Run: df['SignupDate'] = pd.to_datetime(df['SignupDate'], errors='coerce', format='mixed'). The format='mixed' tells pandas to accept multiple date formats. errors='coerce' turns unparseable values into NaT (the pandas missing-date marker) instead of raising an error. Print df['SignupDate'].head() — every row is now a real datetime you can filter and sort on.
Run: df['AmountSpent'] = pd.to_numeric(df['AmountSpent'], errors='coerce'). This handles any string values that snuck into the column and turns them into NaN. If you ever have currency symbols like '$1,250.50', you would first strip those: df['AmountSpent'] = df['AmountSpent'].str.replace('$', '', regex=False).str.replace(',', '', regex=False) and then convert.
Run: df.dtypes. You should see CustomerID as int64, Email as object, SignupDate as datetime64[ns], AmountSpent as float64. Every column should match the kind of data it contains. If CustomerID is float64 (because pandas filled missing IDs with NaN), that is a flag to investigate why IDs are missing.
The pair pd.to_datetime(..., errors='coerce') and pd.to_numeric(..., errors='coerce') are your two best friends in data cleaning. They convert messy columns to clean types and turn anything they cannot parse into NaN instead of crashing. If your conversion returns many NaNs, you found a data quality issue — investigate, do not paper over it.
Step 5: Catch and Handle Outliers
The practice file has one outlier: Frank Miller at 9999.99 in AmountSpent. Everyone else is between 75 and 2450. In Excel you would sort and eyeball it; in pandas, you can detect outliers with one summary statistic and decide whether to keep, flag, or remove.
Run: df['AmountSpent'].describe(). You see count, mean, std, min, 25%, 50%, 75%, max. The max is 9999.99, which is roughly 4x the next highest (2450). That is your outlier flag. The mean is also pulled up by the outlier — useful detail.
For Frank's 9999.99, the right move depends on the business question. If you are reporting average spend per customer, the outlier pulls the average up — you may want to report the median instead, or note 'excluding one outlier of $9,999'. If Frank is a legitimate VIP customer, keep the value and the high average is the truth. If Frank's 9999.99 is a data-entry error (should have been 999.99), correct it or remove the row.
Run: df.to_csv('cleaned_customers.csv', index=False). The index=False prevents pandas from writing a useless 0,1,2,... column. Your cleaned file is now ready for analysis, charting, or loading into Power BI, Tableau, or a SQL database. The full cleaning script is roughly 15 lines and runs in under a second on this file.
The cleaned CSV is now small enough to load anywhere. For files in the millions of rows, you would skip the to_csv and instead load directly into a database or Power BI. The cleaning steps (dedupe, trim, fix types, handle outliers) scale to any size — pandas handles 10 million rows on a laptop without breaking a sweat.
Excel vs pandas: Side-by-Side Workflow
Now that you have seen the pandas version, here is the side-by-side comparison with the Excel version of the same workflow. The patterns are nearly identical; the difference is whether you click them or write them. Pick the one that fits your current problem.
Data > Get Data > From File > CSV → Power Query Editor → Remove Duplicates → Transform > Format > Trim and Clean → Replace Values for missing → Change Type on columns → Close & Apply. About 6 clicks and 30 seconds for this file. The same workflow is repeatable next month: just refresh the data source. Power Query is genuinely good.
import pandas as pd → df = pd.read_csv(...) → df.drop_duplicates() → df['Name'].str.strip() → df.fillna(0) → pd.to_datetime / pd.to_numeric → df.to_csv(...). About 8 lines of code and 30 seconds. The same script runs on next month's file unchanged — just change the path. The advantage of pandas is reproducibility: the cleaning is in the script, not in your head.
Use Excel/Power Query when the file is small (< 100K rows), you are doing a one-off clean, and your stakeholders want a .xlsx deliverable. Use pandas when the file is large, you are doing the same cleaning every week/month on new files, or you need to integrate with other Python tooling (matplotlib for charts, scikit-learn for analysis). If the data already lives in a database, cleaning with SQL is often the fastest route. Both are real skills — knowing both is the strongest combination.
Start with Power Query in Excel, then graduate to pandas for files that Power Query chokes on or tasks you repeat every month. You do not have to abandon Excel to learn pandas — they are complementary. The skill that transfers 100% is the data-cleaning mindset: dedupe, trim, fix types, handle missing, catch outliers. The tool just changes.
5 Mistakes Excel Users Make in pandas (And the Fix)
You will hit most of these in your first week of pandas. Knowing them upfront saves you from assuming pandas is broken when the issue is a syntax or habit difference from Excel. These are the exact issues I see in almost every Excel-first analyst's first pandas script.
Excel operations feel like they happen in place. pandas operations return a new Series/DataFrame and you must assign it: df['Name'] = df['Name'].str.strip(). Just running df['Name'].str.strip() without the left side shows the result but does not change df. This is the #1 pandas gotcha for Excel users.
.sum() in pandas skips NaN by default, but .mean() and .std() can also behave differently than Excel's AVERAGE and STDEV. If your result looks wrong, the issue is usually a missing-value default. Use df['AmountSpent'].sum(skipna=False) and see if NaN is the culprit.
In Jupyter, do not write the entire cleaning in one cell. Break it into small cells, one operation each, and run df.head() after each. The reason: debugging pandas is 10x faster when you see the result of each step, not just the final output. A 10-cell Jupyter notebook is better than a 1-cell notebook of 50 lines.
.iloc is positional (like Excel row number, starting at 0). .loc is label-based (like Excel's named ranges). Beginners mix them up. Use .loc with column names and condition: df.loc[df['Country'] == 'USA', 'Status']. Use .iloc when you want a row by position: df.iloc[0].
pandas sometimes warns you with SettingWithCopyWarning. The fix is .copy() before you modify: subset = df[df['Country'] == 'USA'].copy(). Without copy, modifications can silently fail or affect the original. The warning is annoying but it is the language saving you from a real bug.
What to Do Next (Your Week-One pandas Plan)
You now have a working pandas cleaning script. Here is the plan I give every Excel user for the first week of pandas. The goal is one real cleaned file by end of week one, not a polished system.
Take any real CSV or Excel file you clean regularly. Run the same six steps on it (load, dedupe, trim, types, missing, outliers). Even if it takes 10x longer than Power Query, you have learned the workflow. The speed comes back over time.
Time yourself. Excel/Power Query will be faster today. Pandas will be faster in week 3 when you have the cleaning steps as a reusable script. The ROI is on the second file you clean, not the first.
Save your cleaning script as a Python file with template paths. Next month, change the file path and re-run. This template becomes the seed of your data team's pandas toolkit. Most analysts have a private repo of cleaning scripts within 6 months.
After cleaning, the next pandas skills to add are groupby (for aggregation by category) and merge (for joining two tables, like Excel's VLOOKUP). These two functions cover 80% of the post-cleaning work you will do in pandas. Plan 2-3 weeks of practice on each.
Treat pandas as a complement to Excel, not a replacement. The Excel users who succeed with pandas are the ones who use pandas for the parts that benefit (reproducible cleaning, large files, automation) and keep Excel for the parts Excel is great at (quick visual review, stakeholder-facing reports, one-off analysis). The combination is more than the sum of its parts. To see where pandas sits in the wider analyst skill set, our data analysis learning path maps the full sequence.
