Excel to Python: A Transition Guide | Finance Train

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.

1import pandas as pd
2
3# Read Excel file
4df = pd.read_excel('sales_data.xlsx')
5
6# Or read CSV
7df = pd.read_csv('sales_data.csv')
8

python

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:

1pivot = df.pivot_table(
2    values='Sales',
3    index='Region',
4    columns='Product',
5

Group By (Subtotals)

Excel: Data > Subtotal

Python:

1df.groupby('Region')['Sales'].sum()
2
3# Multiple aggregations
4df.groupby('Region'

Complete Example

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

1import pandas as pd
2
3# Load data
4df = pd.read_excel('sales.xlsx')
5
6

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