Why Switch to Python?

Excel is powerful, familiar, and fast for one-off analysis. But at scale, it becomes a liability: formulas break, changes ripple unpredictably, files bloat, and collaborative version control is a nightmare. Python (via pandas) wins when your data grows, when you need reproducibility, and when your analysis is no longer a one-time glance.

When Excel is still the right tool

  • Small datasets (under 50k rows): Excel's UI is faster to explore by eye than writing code.
  • Ad-hoc one-off reports: You need an answer in 5 minutes, not in a reproducible script.
  • Stakeholder deliverables: Non-technical users expect a .xlsx with formatting, not a Python script.
  • Financial formulas: NPV, IRR, XIRR are simpler in Excel's financial suite (though pandas-financial exists).
  • Conditional formatting visuals: Heat maps and icon sets are faster to set up in Excel UI.

When pandas wins

  • 100k+ rows: Excel slows to a crawl; pandas handles millions with ease.
  • Repeatability: Run the same analysis on new data next month—just re-run your script.
  • Complex joins: Multi-table merges, many-to-many relationships, and rolling window calculations are cleaner in pandas.
  • Real charting: Matplotlib/Seaborn produce publication-ready visuals in one script; Excel chart UI is brittle.
  • Version control: Git tracks code changes line-by-line; Excel binary format is opaque and produces merge conflicts.
  • Automation: Chain 10 transformations in one pipeline; Excel requires manual step-by-step clicking.
  • Data quality: Detect nulls, duplicates, and type mismatches programmatically; Excel's data validation is fragile.

Honest take: If you're an Excel power-user, pandas will feel slower at first (more typing, less clicking). By week 3, you'll realize you're shipping cleaner, more reliable analysis faster. The payoff is patience.

Setup Bridge: Read Excel, Work in Pandas

You don't have to abandon Excel files. This is the workflow: stake gets .xlsx → you read it into pandas → transform → write back .xlsx with formatting.

import pandas as pd import openpyxl # Read a single sheet df = pd.read_excel('data.xlsx', sheet_name='Sales') # Read multiple sheets into a dict sheets = pd.read_excel('data.xlsx', sheet_name=None) # all sheets df_2024 = sheets['2024'] df_2025 = sheets['2025'] # Specify dtype to avoid type inference surprises df = pd.read_excel('data.xlsx', dtype={'product_id': str, 'date': 'datetime64'}) # Write back with openpyxl formatting (see section 9) with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer: df.to_excel(writer, sheet_name='Results', index=False) Copy

Key setup commands

pip install pandas openpyxl python -m pip install --upgrade pandas # stay current Copy

The 40 Most-Used Excel Formulas → Pandas

This is the heart of the guide. Each row is a formula you know, its pandas equivalent, and a one-line comment on why pandas wins (or doesn't).

🔍 Filter table:
Excel Formula Pandas Equivalent Why Pandas Wins
=SUM(A:A) df['A'].sum() Vectorized; handles nulls safely (ignores NaN).
=AVERAGE(A:A) df['A'].mean() Same; works on any numeric column instantly.
=COUNT(A:A) df['A'].count() Counts non-null values; df.shape[0] for row count.
=COUNTIF(A:A, ">100") (df['A'] > 100).sum() Boolean mask + sum is cleaner and faster than nested IF loops.
=SUMIF(A:A, ">100", B:B) df.loc[df['A']>100, 'B'].sum() Same logic, more readable; scales to multi-condition .loc[].
=VLOOKUP(key, range, 3, 0) df.merge(lookup_df, on='key')[...].iloc[:,2] pandas.merge is the honest answer; VLOOKUP is fragile and slow at scale.
=INDEX(MATCH(...)) df.merge(lookup_df, on='key') Same; merge handles many-to-many correctly; INDEX/MATCH doesn't scale.
=IF(A>10, "big", "small") np.where(df['A']>10, 'big', 'small') Vectorized; one operation on 1M rows, not a loop.
=IFS(...multiple conditions) np.select([cond1, cond2], [val1, val2], default) More readable than nested IF; pandas naturally handles multi-way splits.
=CONCATENATE(A, "-", B) df['A'].str.cat(df['B'], sep='-') str.cat is cleaner; or use f-strings: df.apply(lambda r: f"{r.A}-{r.B}", axis=1).
=LEFT(A, 3) df['A'].str[:3] or df['A'].str.slice(0, 3) String slicing is idiomatic Python; shorter code.
=RIGHT(A, 3) df['A'].str[-3:] Negative indexing is intuitive for "last N characters."
=MID(A, 2, 4) df['A'].str[1:5] Same logic; pandas uses Python's slice notation.
=TRIM(A) df['A'].str.strip() Removes leading/trailing whitespace; df['A'].str.strip().str.lower() chains easily.
=LOWER(A) df['A'].str.lower() Same; .str.upper(), .str.title(), .str.capitalize() also available.
=PROPER(A) df['A'].str.title() Title-cases each word; or .str.capitalize() for first char only.
=DATE(2025, 3, 15) pd.Timestamp('2025-03-15') pandas timestamps are native; no format string needed in most cases.
=YEAR(date_col) df['date'].dt.year dt accessor unlocks .month, .day, .weekday, .dayofyear, etc. in one method.
=MONTH(date_col) df['date'].dt.month Same; df['date'].dt.quarter also works.
=DAY(date_col) df['date'].dt.day Same.
=WEEKDAY(date_col) df['date'].dt.dayofweek (0=Mon, 6=Sun) Same logic; pandas 0-indexes from Monday (Excel does Sunday).
=DATEDIF(start, end, "D") (df['end'] - df['start']).dt.days Subtract dates, use .dt.days; simpler than DATEDIF quirks.
=NETWORKDAYS(start, end) np.busday_count(df['start'], df['end']) Counts weekdays; very fast on large ranges.
=UNIQUE(A:A) df['A'].unique() Returns array; use .value_counts() to see counts per unique value.
=SORT(A:A, 1, 1) df.sort_values(by='A', ascending=False) Multi-column sort: .sort_values(by=['A', 'B']); axis is always column-aware.
=FILTER(range, cond) df[df['A'] > 100] or df.query("A > 100") Boolean indexing is pandas core; query() is more SQL-like if you prefer.
=ROUND(A, 2) df['A'].round(2) Same; df['A'].round() is idiomatic (no decimals arg needed for display).
=CEILING(A, 1) np.ceil(df['A']) Rounds up; import numpy for this one.
=FLOOR(A, 1) np.floor(df['A']) Rounds down.
=RANK(A, range, 1) df['A'].rank(method='min') Ties get min rank; method='average' breaks ties. Excel's RANK is method='min'.
=PERCENTILE(A:A, 0.95) df['A'].quantile(0.95) Same; quantile is pandas standard. df['A'].quantile([0.25, 0.5, 0.75]) for multiple at once.
=STDEV(A:A) df['A'].std() Sample std dev; use .std(ddof=0) for population std dev (Excel default is ddof=1).
=VAR(A:A) df['A'].var() Same; .var(ddof=0) for population variance.
=SUMPRODUCT(A:A, B:B) (df['A'] * df['B']).sum() Element-wise multiply, then sum; cleaner than SUMPRODUCT syntax.
=RAND() np.random.uniform(0, 1) Random float 0–1; seed with np.random.seed(42) for reproducibility.
=RANDBETWEEN(1, 100) np.random.randint(1, 101) Upper bound is exclusive in numpy; so 101 gives 1–100.
=IFERROR(formula, value) .fillna(value) or try/except or pd.to_numeric(..., errors='coerce') Use fillna for null handling; try/except for formula errors; coerce for type conversion.
=SUBSTITUTE(A, old, new) df['A'].str.replace(old, new) Same; use regex=True for pattern matching.
=LEN(A) df['A'].str.len() Character count per string; null values return NaN.
=FIND(substr, A) df['A'].str.find(substr) Returns position of first match (0-indexed); -1 if not found.
=SEARCH(pattern, A) [case-insensitive] df['A'].str.contains(pattern, case=False) Returns True/False; use regex=True for complex patterns.
Remove duplicates df.drop_duplicates() By default keeps first occurrence; use subset=['col'] to dedupe on specific columns.

Notes: All examples assume you've imported pandas as pd and numpy as np. Null values in pandas are NaN (float) or pd.NA (nullable types); most operations skip them automatically. Use df.fillna() to replace nulls proactively.

PivotTables → pivot_table() + groupby()

Pivot tables are the most-missed Excel feature. Pandas gives you two paths: .pivot_table() for the classic approach, or .groupby() for more control.

Classic Example: Revenue by Region & Month

In Excel: Create a PivotTable, drag Region to rows, Month to columns, Revenue to values (sum).

Pandas: pivot_table()

df.pivot_table( index='region', columns='month', values='revenue', aggfunc='sum' ) Copy

Pros: One-liner, exactly like Excel UI. Cons: Less flexible for complex operations.

Pandas: groupby() + unstack()

df.groupby(['region', 'month'])['revenue'].sum().unstack(fill_value=0) Copy

Pros: Chain transformations before unstacking. Cons: Slightly more verbose.

Multiple Aggregations

Count rows AND sum revenue in the same pivot:

df.pivot_table( index='region', columns='month', values=['revenue', 'order_id'], aggfunc={'revenue': 'sum', 'order_id': 'count'} ) Copy

When to use which

  • Use .pivot_table() for simple, one-off pivots (feels like Excel).
  • Use .groupby() when you need to chain other transformations (filter, join, transform) before or after.
  • Use .pivot_table(margins=True) to add subtotals (like Excel's grand total).

Power Query → Pandas Pipelines

Power Query's "Applied Steps" list is exactly a pandas pipeline. Each step (filter, add column, group) is a chained method.

Power Query (M language)

  • 1. Table.SelectRows(cond)
  • 2. Table.AddColumn(...)
  • 3. Table.Group(...)
  • 4. Table.SortByCount(...)

Pandas Pipeline

result = ( df[df['status'] == 'active'] .assign(profit=df['revenue'] - df['cost']) .groupby('region')['profit'].sum() .sort_values(ascending=False) ) Copy

Key Power Query → Pandas translation

  • Table.SelectRows(cond) → df[cond] (boolean indexing)
  • Table.AddColumn(name, formula) → df.assign(new_col=...) or df['new_col'] = ...
  • Table.Group(..., aggfunc) → df.groupby().agg()
  • Table.SortBy(...) → df.sort_values(...)
  • Table.Merge(other, key) → df.merge(other, on='key')
  • Table.TransformColumnType(...) → df['col'].astype(...)

Pro tip: Use .pipe() to inject custom functions mid-pipeline, or use .assign() for computed columns to keep chains immutable (no side effects).

Charts: Excel → matplotlib/seaborn

Excel's charting UI is fast for one-off exploration, but pandas + matplotlib/seaborn are faster for repeatability and polish.

When to drop Excel charts

  • Consistency: Use the same chart style across all reports (set matplotlib rcParams once).
  • Reproducibility: Re-run the chart on new data without clicking; Excel requires re-selecting ranges.
  • High-DPI export: Matplotlib exports to PDF/SVG at any resolution; Excel charts are screen-optimized.
  • Complex layouts: Subplot grids, multi-axis charts, and annotations are easier in code than Excel UI.

Quick examples

import matplotlib.pyplot as plt import seaborn as sns # Bar chart df.groupby('region')['revenue'].sum().plot(kind='bar') # Seaborn (opinionated, publication-ready) sns.barplot(data=df, x='region', y='revenue', estimator=sum) plt.savefig('chart.png', dpi=300, bbox_inches='tight') # Line chart df.groupby('month')['daily_active_users'].mean().plot() # Scatter with regression line sns.regplot(data=df, x='ad_spend', y='conversions') Copy

When Excel still wins

  • Stakeholder dashboards: They expect click-and-drag interactivity (use Plotly, not static matplotlib).
  • One-off visuals: 5-minute exploratory chart; code setup overhead not worth it.
  • Conditional formatting heatmaps: Excel's color scales are faster to set up than matplotlib custom colormaps.

Things Excel Still Does Better

  • Ad-hoc glance: Open file, skim data, spot an outlier in 20 seconds; pandas requires a script.
  • Sharing with non-technical users: They expect .xlsx, not a Jupyter notebook.
  • Financial formulas: NPV, IRR, XIRR are one-liners in Excel; pandas-financial works but is less polished.
  • Conditional formatting: Color scales, icon sets, and data bars are faster in Excel UI.
  • Pivot table UI exploration: Drag-and-drop regions/months in seconds; pandas is code-first.
  • Small data QA: A human eye scanning 100 rows in Excel is often faster than writing filters.

Strategy: Use Excel for input and output (stakeholder delivery, quick checks), pandas for the pipeline (transform, validate, analyze).

The Bridge Workflow: Excel Input → Pandas → Excel Output

Don't abandon Excel entirely. Use it for what it's good at (stakeholder interfaces), and pandas for the heavy lifting.

1
Stakeholder sends .xlsx
File contains raw data: sales, regions, dates, product IDs (usually formatted inconsistently).
↓
2
Read into pandas
df = pd.read_excel('input.xlsx', sheet_name='Raw Data')
↓
3
Transform: dedupe, clean, join, pivot
Filter nulls, standardize dates, merge lookups, aggregate.
↓
4
Write back .xlsx with openpyxl formatting
Freeze panes, bold headers, number formats, column widths (see section 9).
↓
5
Deliver to stakeholder
They open .xlsx in Excel, see formatted output, add their own notes/filters if needed.
# Full bridge example import pandas as pd from openpyxl.styles import Font # Read df = pd.read_excel('input.xlsx') # Transform df['date'] = pd.to_datetime(df['date']) df = df[df['status'] == 'active'].drop_duplicates(subset=['id']) result = df.groupby('region')['revenue'].sum().reset_index() # Write with formatting with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer: result.to_excel(writer, sheet_name='Summary', index=False) ws = writer.sheets['Summary'] for cell in ws[1]: # Bold header row cell.font = Font(bold=True) Copy

Openpyxl: Write Pandas DataFrames with Formatting

Your stakeholders expect polished .xlsx files. Use openpyxl to add formatting after pandas writes the data.

Formatting cheatsheet

Freeze panes (lock top row & left column)

from openpyxl import load_workbook wb = load_workbook('output.xlsx') ws = wb.active ws.freeze_panes = 'B2' # Freeze row 1 and column A wb.save('output.xlsx') Copy

Bold headers

from openpyxl.styles import Font wb = load_workbook('output.xlsx') ws = wb.active for cell in ws[1]: # Row 1 cell.font = Font(bold=True, size=12) wb.save('output.xlsx') Copy

Number formats

from openpyxl.styles import numbers wb = load_workbook('output.xlsx') ws = wb.active for row in ws.iter_rows(min_row=2, max_col=3, max_row=ws.max_row): # Column 3 = currency row[2].number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE # Or: '$#,##0.00' wb.save('output.xlsx') Copy

Column widths

wb = load_workbook('output.xlsx') ws = wb.active ws.column_dimensions['A'].width = 20 ws.column_dimensions['B'].width = 15 # Auto-fit: ws.column_dimensions['A'].width = None (requires openpyxl 3.0+) wb.save('output.xlsx') Copy

Conditional formatting (color scale)

from openpyxl.formatting.rule import ColorScaleRule wb = load_workbook('output.xlsx') ws = wb.active color_scale = ColorScaleRule( start_type='min', start_color='63BE7B', # Green end_type='max', end_color='F8696B' # Red ) ws.conditional_formatting.add(f'C2:C{ws.max_row}', color_scale) wb.save('output.xlsx') Copy

Tip: Do pandas transformations first, then use openpyxl on the written file. Don't try to format during df.to_excel(); it's limiting.

Common Excel-User Mistakes in Pandas

1. Off-by-one ranges

Excel: A1:A10 includes both A1 and A10 (10 rows).

Pandas: df.iloc[0:10] includes rows 0–9 (10 rows); row 10 is excluded.

# RIGHT df.iloc[0:10] # Rows 0–9 (10 rows) df.loc['A':'J'] # Both A and J included (inclusive!) # WRONG (off by one) df.iloc[0:11] # 11 rows, not 10 Copy

2. Text vs. numeric dtype surprises

The problem: pd.read_excel sometimes reads "123" as string, not int. Filters break.

# WRONG df = pd.read_excel('data.xlsx') df[df['product_id'] == 123] # No match if product_id is string "123" # RIGHT df = pd.read_excel('data.xlsx', dtype={'product_id': str}) # Or convert after reading: df['product_id'] = df['product_id'].astype(str) Copy

3. Treating the index like a row label

The problem: Excel has no "index"; pandas does. Forgetting to reset_index() means your index disappears on export.

# WRONG result = df.groupby('region')['revenue'].sum() result.to_excel('output.xlsx') # Index (region names) is column 1, but unnamed # RIGHT result = df.groupby('region')['revenue'].sum().reset_index() result.to_excel('output.xlsx') # Column headers: 'region', 'revenue' Copy

4. Comparing floats with ==

The problem: Floating-point rounding errors break equality checks.

# WRONG df[df['price'] == 9.99] # May miss 9.989999999... due to float precision # RIGHT df[(df['price'] - 9.99).abs() < 0.01] # Allow tolerance # Or use pandas isin for exact matches df[df['price'].isin([9.99, 10.49])] Copy

5. Using loops instead of vectorization

The problem: Row-by-row loops are 100x slower than vectorized operations.

# WRONG (slow!) for idx, row in df.iterrows(): df.at[idx, 'profit'] = row['revenue'] - row['cost'] # RIGHT (vectorized) df['profit'] = df['revenue'] - df['cost'] # Also avoid: df.apply(lambda x: custom_func(x), axis=1) # Still slower than groupby # Use groupby + transform for grouped operations: df['region_avg'] = df.groupby('region')['revenue'].transform('mean') Copy

Quick Wins: Your First Week in Pandas

Don't memorize everything. Just pick one task per day and ship it. By Friday, you'll have 5 wins under your belt.

Day 1: Read a CSV and sum a column

import pandas as pd df = pd.read_csv('sales.csv') total = df['amount'].sum() print(f"Total sales: ${total:,.2f}") Copy

Goal: Get comfortable with read_csv, column selection, and .sum().

Day 2: Filter and groupby

# Filter rows where status == 'completed' completed = df[df['status'] == 'completed'] # Sum amount by region by_region = completed.groupby('region')['amount'].sum() print(by_region) Copy

Goal: Master boolean filtering and groupby aggregation.

Day 3: Pivot table

# Revenue by region and month pivot = df.pivot_table( index='region', columns='month', values='amount', aggfunc='sum' ) print(pivot) Copy

Goal: Create your first pivot table; see how it compares to Excel.

Day 4: Merge (join) two tables

# Merge sales with customer info sales = pd.read_csv('sales.csv') customers = pd.read_csv('customers.csv') merged = sales.merge(customers, on='customer_id', how='left') print(merged.head()) Copy

Goal: Replace VLOOKUP with merge; feel the power.

Day 5: Ship a chart

import matplotlib.pyplot as plt # Line chart: revenue over time monthly_revenue = df.groupby('month')['amount'].sum() plt.figure(figsize=(10, 6)) monthly_revenue.plot(kind='line', marker='o') plt.title('Monthly Revenue') plt.xlabel('Month') plt.ylabel('Revenue ($)') plt.savefig('chart.png', dpi=300, bbox_inches='tight') plt.show() Copy

Goal: Export a chart; see how easy reproducibility is.

Africa & India's Best AI Academy
🤖 AI-first 🎓 Placement Assistance

Ready to make the switch stick?

Join the Best AI Program: The AI-Era Analyst — 5 months, fully online, with placement assistance. Week 1 is free.

Explore The AI-Era Analyst →