Excel to Python: A Transition Guide

If you are comfortable with Excel, you are already halfway to learning Python for data analysis. This guide shows you how your existing skills translate.

Why Make the Switch?

Excel is great, but Python offers:

Handle millions of rows (Excel struggles past 100K)
Automate repetitive tasks
Reproducible analysis
Advanced ML and visualization
Free and runs everywhere

The Pandas Library

Pandas is Python’s answer to Excel. A DataFrame is essentially a spreadsheet.

import pandas as pd

# Read Excel file
df = pd.read_excel('sales_data.xlsx')

# Or read CSV
df = pd.read_csv('sales_data.csv')

Common Operations: Excel vs Python

Viewing Data

Excel	Python
Scroll through sheet	`df.head()` - first 5 rows
Ctrl+End	`df.shape` - (rows, columns)
Look at columns	`df.columns`

Selecting Data

Excel	Python
Click column A	`df['A']` or `df.A`
Select A and B	`df[['A', 'B']]`
Row 5	`df.iloc[4]` (0-indexed)
A1:B10	`df.loc[0:9, ['A', 'B']]`

Filtering

Excel	Python
Filter > Greater than 100	`df[df['Sales'] > 100]`
Filter > Text contains “NY”	`df[df['City'].str.contains('NY')]`
Multiple conditions	`df[(df['Sales'] > 100) & (df['Region'] == 'East')]`

Formulas to Functions

Excel	Python
=SUM(A:A)	`df['A'].sum()`
=AVERAGE(A:A)	`df['A'].mean()`
=MAX(A:A)	`df['A'].max()`
=COUNT(A:A)	`df['A'].count()`
=COUNTIF(A:A, “>100”)	`(df['A'] > 100).sum()`

Creating New Columns

Excel: New column with formula =A1*B1

Python:

df['Total'] = df['Price'] * df['Quantity']

VLOOKUP → merge()

Excel: =VLOOKUP(A1, Sheet2!A:B, 2, FALSE)

Python:

result = pd.merge(df1, df2, on='ID', how='left')

Pivot Tables

Excel: Insert > PivotTable

Python:

pivot = df.pivot_table(
    values='Sales',
    index='Region',
    columns='Product',
    aggfunc='sum'
)

Group By (Subtotals)

Excel: Data > Subtotal

Python:

df.groupby('Region')['Sales'].sum()

# Multiple aggregations
df.groupby('Region').agg({
    'Sales': 'sum',
    'Quantity': 'mean',
    'Customer': 'count'
})

Complete Example

Let’s analyze sales data - something you would do in Excel:

import pandas as pd

# Load data
df = pd.read_excel('sales.xlsx')

# Quick overview
print(df.head())
print(df.describe())

# Filter to 2024
df_2024 = df[df['Year'] == 2024]

# Sales by region
region_sales = df_2024.groupby('Region')['Revenue'].sum()
print(region_sales)

# Top 10 customers
top_customers = df_2024.groupby('Customer')['Revenue'].sum().nlargest(10)
print(top_customers)

# Add profit margin column
df_2024['Margin'] = (df_2024['Revenue'] - df_2024['Cost']) / df_2024['Revenue']

# Save results
df_2024.to_excel('analysis_results.xlsx', index=False)

Tips for Transitioning

Start with familiar tasks: Recreate an Excel analysis in Python
Keep Excel as reference: Compare results to verify
Use Jupyter notebooks: Interactive, like working cell by cell
Google is your friend: “pandas equivalent of VLOOKUP”
Do not memorize: Bookmark the Pandas cheat sheet

Next Steps

Getting Started with Python - Learn Python basics
Data Manipulation with Pandas - Deep dive into Pandas