If you use Excel VBA for data processing, Python can do the same tasks faster, with less code, and handle datasets 100x larger. This guide is specifically written for Excel/VBA users transitioning to Python — every concept is explained with an Excel equivalent so you can learn by analogy.
In this article
- Python vs Excel: Concept Mapping
- Basic Level: Reading and Writing Excel Files
- SUMIF / COUNTIF Equivalents
- Intermediate Level: VLOOKUP, Pivot Tables, and Cleaning
- VLOOKUP Replacement with merge()
- Pivot Table Equivalent
- Data Cleaning (The Real Power of Python)
- Expert Level: Automating Multiple Files and Reporting
- Continue learning
Python vs Excel: Concept Mapping
| Excel Concept | Python Equivalent | Python Library |
|---|---|---|
| Worksheet range (cells) | DataFrame | pandas |
| VLOOKUP / XLOOKUP | merge() or map() | pandas |
| SUMIF / COUNTIF | groupby().agg() | pandas |
| Pivot Table | pivot_table() | pandas |
| Charts | plot() | matplotlib / plotly |
| VBA For Loop | for item in list: | built-in Python |
| VBA If/Then/Else | if / elif / else: | built-in Python |
| Open workbook | pd.read_excel() | pandas + openpyxl |
| Save workbook | df.to_excel() | pandas + openpyxl |
Basic Level: Reading and Writing Excel Files
import pandas as pd
# READ Excel file (like opening a workbook in VBA)
df = pd.read_excel("sales_data.xlsx", sheet_name="Sheet1")
# See the data (like pressing Ctrl+End in Excel)
print(df.shape) # (rows, columns) e.g. (1000, 8)
print(df.head(5)) # First 5 rows
print(df.columns.tolist()) # Column names
print(df.dtypes) # Data types of each column
# Filter rows (like AutoFilter in Excel)
# VBA: If cells(i,3).Value > 50000 Then ...
high_sales = df[df['Sales'] > 50000]
print(f"High sales rows: {len(high_sales)}")
# Select specific columns
subset = df[['Name', 'Sales', 'Region']]
# WRITE to Excel (like Save As)
df.to_excel("output.xlsx", index=False, sheet_name="Results")
print("Saved output.xlsx")SUMIF / COUNTIF Equivalents
# Excel: =SUMIF(A:A,"North",C:C)
# Python:
north_total = df[df['Region'] == 'North']['Sales'].sum()
print(f"North region total: ₹{north_total:,.0f}")
# Excel: =COUNTIF(D:D,">50000")
count_high = (df['Sales'] > 50000).sum()
print(f"Records with sales > 50000: {count_high}")
# Excel: =AVERAGEIF(A:A,"Q1",C:C)
q1_avg = df[df['Quarter'] == 'Q1']['Sales'].mean()
print(f"Q1 average sales: ₹{q1_avg:,.0f}")
# Multi-condition (SUMIFS equivalent)
# =SUMIFS(C:C, A:A, "North", B:B, "Q1")
north_q1 = df[(df['Region']=='North') & (df['Quarter']=='Q1')]['Sales'].sum()
print(f"North + Q1: ₹{north_q1:,.0f}")Intermediate Level: VLOOKUP, Pivot Tables, and Cleaning
VLOOKUP Replacement with merge()
# Excel: =VLOOKUP(A2, Products!A:C, 3, 0)
# Python equivalent using merge (like a database JOIN)
# Main data
orders = pd.read_excel("orders.xlsx")
# Lookup table (like the array in VLOOKUP)
products = pd.read_excel("products.xlsx")
# Merge = VLOOKUP
# Like: for each product_id in orders, find the name and price from products
result = orders.merge(
products[['product_id', 'product_name', 'unit_price']],
on='product_id',
how='left' # 'left' = keep all orders even if product not found (like VLOOKUP returning #N/A vs 0)
)
# Calculate line total (new column)
result['line_total'] = result['qty'] * result['unit_price']
print(result.head())Pivot Table Equivalent
# Excel Pivot Table → Python pivot_table()
pivot = pd.pivot_table(
df,
values='Sales', # Values field
index='Region', # Row field
columns='Quarter', # Column field
aggfunc='sum', # Summary type (sum/count/mean/max)
fill_value=0, # Replace NaN with 0
margins=True, # Add Grand Total row/column
margins_name='Total'
)
# Format numbers like Excel (₹ with commas)
print(pivot.applymap(lambda x: f"₹{x:,.0f}"))
# Save pivot to Excel with formatting
pivot.to_excel("pivot_output.xlsx")Data Cleaning (The Real Power of Python)
# Clean messy data — things that take hours in Excel
df = pd.read_excel("raw_data.xlsx")
# 1. Remove duplicates (like Remove Duplicates in Excel)
df.drop_duplicates(inplace=True)
print(f"After dedup: {len(df)} rows")
# 2. Fill missing values (NaN)
df['Region'].fillna('Unknown', inplace=True)
df['Sales'].fillna(0, inplace=True)
# 3. Clean text columns (strip spaces, fix case)
df['Name'] = df['Name'].str.strip().str.title() # " ravi kumar " → "Ravi Kumar"
df['Email'] = df['Email'].str.lower().str.strip()
# 4. Fix date formats
df['Order Date'] = pd.to_datetime(df['Order Date'], dayfirst=True) # Handles DD/MM/YYYY
df['Year'] = df['Order Date'].dt.year
df['Month'] = df['Order Date'].dt.month_name()
# 5. Remove invalid rows (negative sales, empty names)
df = df[df['Sales'] >= 0]
df = df[df['Name'].notna() & (df['Name'] != '')]
print(f"Clean rows: {len(df)}")
df.to_excel("clean_data.xlsx", index=False)Expert Level: Automating Multiple Files and Reporting
import pandas as pd
import glob
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font
from openpyxl.utils import get_column_letter
# TASK: Combine 12 monthly Excel files into one annual report with formatting
# 1. Read all monthly files using wildcard (like Dir() in VBA)
monthly_files = glob.glob("Sales_Reports/Monthly_*.xlsx")
print(f"Found {len(monthly_files)} monthly files")
all_data = []
for file in sorted(monthly_files):
df = pd.read_excel(file)
df['Source File'] = file.split('/')[-1] # Track source
all_data.append(df)
# 2. Combine all into one DataFrame
combined = pd.concat(all_data, ignore_index=True)
print(f"Total rows combined: {len(combined)}")
# 3. Create annual summary pivot
annual_summary = combined.groupby(['Region', 'Product']).agg(
Total_Sales=('Sales', 'sum'),
Total_Orders=('OrderID', 'count'),
Avg_Order=('Sales', 'mean')
).reset_index()
# 4. Write to Excel with formatting via openpyxl
output_file = "Annual_Report_2024.xlsx"
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
combined.to_excel(writer, sheet_name='Raw Data', index=False)
annual_summary.to_excel(writer, sheet_name='Summary', index=False)
# Apply formatting to Summary sheet
wb = writer.book
ws = wb['Summary']
# Bold header row (like VBA: Range("1:1").Font.Bold = True)
header_fill = PatternFill(start_color="2B6CB0", fill_type="solid")
for cell in ws[1]:
cell.font = Font(bold=True, color="FFFFFF")
cell.fill = header_fill
# Auto-fit column widths
for col in ws.columns:
max_length = max(len(str(cell.value or '')) for cell in col)
ws.column_dimensions[get_column_letter(col[0].column)].width = min(max_length + 2, 40)
print(f"Report saved: {output_file}")
print(f"Sheets: Raw Data ({len(combined)} rows), Summary ({len(annual_summary)} rows)")Setup: Install Python from python.org, then run pip install pandas openpyxl xlrd matplotlib in Command Prompt. Use VS Code or Jupyter Notebook for development. The pandas documentation at pandas.pydata.org is excellent.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong