Reading Excel in Pandas

Mastering pd.read_excel(): Explained

Excel files are everywhere in data analysis — reports, dashboards, business data, and more. Pandas makes it easy to read them with pd.read_excel(), which has many of the same powerful parameters as pd.read_csv(), plus Excel-specific ones like sheet selection.

Default settings work for simple files, but real Excel files often have multiple sheets, skipped rows, custom missing values, or specific columns you care about. Mastering these parameters saves hours of manual cleaning.

Why read_excel() Parameters Matter

Excel files can be messy: hidden metadata rows, multiple sheets, merged cells (pandas ignores merging), formatted numbers, or custom "N/A" markers. Using the right parameters loads data cleanly from the start.

Basic Usage and Key Parameters

Parameter-by-Parameter Breakdown

sheet_name (default: 0)
Which sheet(s) to read.

    
sheet_name='Sheet1' or sheet_name=0 (by position)
    
sheet_name=None: Read all sheets → returns dict of DataFrames
    
sheet_name=['Sheet1', 'Sheet2']: Read multiple → dict
    
sheet_name=0: First sheet (most common)


header (default: 'infer')
Same as read_csv(): row to use as column names.

    
header=0: First row
    
header=None: No header → integer column names
    
header=[0,1]: MultiIndex columns


skiprows (default: None)
Skip rows at the top (common for titles, subtitles, or notes in Excel).

    
skiprows=2: Skip first two rows
    
skiprows=[0,1,5]: Skip specific rows


usecols (default: None)
Read only specific columns — great for large Excel files.

    
usecols='A:D': Excel column letters/range
    
usecols=[0,1,3]: By position
    
usecols=['ID', 'Name', 'Salary']: By column names
    
usecols=lambda x: '2023' in x: Flexible filtering


dtype (default: None)
Force data types (prevents wrong inference, saves memory).

    
dtype={'ID': 'str', 'Salary': 'float32'}
    
Use 'category' for repeated text columns


na_values (default: None)
Custom strings to treat as missing (NaN).

    
na_values=['N/A', '', 'Missing', '-']


parse_dates (default: False)
Convert columns to datetime (Excel dates are often numbers internally).

    
parse_dates=['Join_Date']
    
parse_dates=True: Try to infer


Other Useful Parameters

    
engine='openpyxl' (default for .xlsx) or 'xlrd' for old .xls
    
nrows: Read only first N rows (testing)
    
thousands, decimal: For formatted numbers


Reading Multiple Sheets Efficiently

Use pd.ExcelFile() for better performance when reading many sheets:


Pro Tips for Beginners


    
Install openpyxl: pip install openpyxl (required for .xlsx)
    
Always preview: pd.read_excel('file.xlsx', nrows=5)
    
Many read_csv() parameters work here too (skiprows, usecols, dtype, etc.)
    
For very large Excel files, consider reading in chunks or converting to CSV first


Putting It All Together: Full Multi-Sheet Excel Workflow with Your Data

Here is a real example using your exact three-sheet data. Save this as practice_data.xlsx with three sheets named Sales, Products, and Regions.

Sheet 1: Sales


    

        

            
Region
            
Product
            
Sales
        
    
    

        

            
North
            
A
            
1200
        
        

            
North
            
B
            
1500
        
        

            
South
            
A
            
900
        
        

            
South
            
C
            
1300
        
        

            
East
            
B
            
700
        
        

            
West
            
C
            
1600
        
        

            
East
            
A
            
1100
        
        

            
West
            
B
            
800
        
    


Sheet 2: Products


    

        

            
Product
            
Category
            
Price
        
    
    

        

            
A
            
Electronics
            
300
        
        

            
B
            
Furniture
            
450
        
        

            
C
            
Furniture
            
700
        
    


Sheet 3: Regions


    

        

            
Region
            
Manager
        
    
    

        

            
North
            
Ravi
        
        

            
South
            
Sneha
        
        

            
East
            
Amit
        
        

            
West
            
John
        
    


Complete Script: Load, Merge All Sheets, Analyze & Export

 1000 else 'Low Sales')

# Step 6: Create summaries
# Pivot: Total Sales by Region (rows) and Product (columns)
pivot_sales = sales_df.pivot_table(
    values='Sales',
    index='Region',
    columns='Product',
    aggfunc='sum',
    fill_value=0
)

# GroupBy: Total Sales by Product and Region
grouped_sales = sales_df.groupby(['Product', 'Region'])['Sales'].sum().reset_index()

# Step 7: Save everything to a new multi-sheet Excel file
output_file = 'processed_sales_data.xlsx'
with pd.ExcelWriter(output_file) as writer:
    merged_df.to_excel(writer, sheet_name='MergedData', index=False)
    pivot_sales.to_excel(writer, sheet_name='PivotSales')
    grouped_sales.to_excel(writer, sheet_name='GroupedSales', index=False)

print(f"\nAll done! Processed file saved as: {output_file}")


This pattern — load all sheets → merge related data → analyze → export — is exactly how professionals handle real business Excel files.


Happy Learning !! 🐼