Advanced Number Formatting Python openpyxl Guide

Excel number formatting makes data readable. Python openpyxl provides powerful tools. You can format numbers professionally.

This guide covers advanced techniques. Learn currency, percentages, dates, and custom formats. Create professional Excel reports automatically.

Understanding Number Format Basics

Number formats control how values appear. They don't change the actual value. Only the display changes.

Use the number_format attribute. Apply it to cells or ranges. Format numbers as needed.

from openpyxl import Workbook
from openpyxl.styles import numbers

# Create workbook and select active sheet
wb = Workbook()
ws = wb.active

# Basic number formatting examples
ws['A1'] = 1234.567
ws['A1'].number_format = '#,##0.00'

ws['A2'] = 0.456
ws['A2'].number_format = '0.00%'

ws['A3'] = 42
ws['A3'].number_format = '$#,##0.00'

# Save the workbook
wb.save('basic_formatting.xlsx')

This code creates different number formats. It shows decimal places, percentages, and currency.

Currency and Accounting Formats

Currency formats display monetary values. Accounting formats align currency symbols. Both are essential for financial reports.

Use standard currency symbols. Or use custom currency formats. Control decimal places and symbols.

# Currency formatting examples
ws['B1'] = 1234.56
ws['B1'].number_format = '$#,##0.00'

ws['B2'] = -789.12
ws['B2'].number_format = '$#,##0.00_);[Red]($#,##0.00)'

# Accounting format
ws['B3'] = 4567.89
ws['B3'].number_format = '_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)'

# European format with euro symbol
ws['B4'] = 1234.56
ws['B4'].number_format = '#,##0.00 €'

The accounting format aligns currency symbols. Negative numbers show in red. Formats vary by region.

Percentage and Fraction Formats

Percentage formats display decimal values as percentages. Fractions show values as fractions. Both are common in reports.

Choose percentage precision. Select fraction types. Match your data needs.

# Percentage formatting
ws['C1'] = 0.456
ws['C1'].number_format = '0%'  # Shows as 46%

ws['C2'] = 0.1234
ws['C2'].number_format = '0.00%'  # Shows as 12.34%

# Fraction formatting
ws['C3'] = 0.5
ws['C3'].number_format = '# ?/?'

ws['C4'] = 0.75
ws['C4'].number_format = '# ??/??'

Percentage formats multiply by 100. They add the % symbol. Fractions show exact ratios.

Date and Time Formatting

Date formats display serial numbers as dates. Time formats show time values. Combine both for datetime.

Use standard date codes. Create custom date formats. Handle different locales.

from datetime import datetime

# Date formatting examples
ws['D1'] = datetime(2024, 12, 25)
ws['D1'].number_format = 'yyyy-mm-dd'

ws['D2'] = datetime(2024, 12, 25)
ws['D2'].number_format = 'dddd, mmmm dd, yyyy'

# Time formatting
ws['D3'] = datetime(2024, 12, 25, 14, 30, 45)
ws['D3'].number_format = 'hh:mm:ss AM/PM'

# Combined date and time
ws['D4'] = datetime(2024, 12, 25, 14, 30, 45)
ws['D4'].number_format = 'mm/dd/yyyy hh:mm:ss'

Excel stores dates as serial numbers. Formatting makes them readable. Choose appropriate date styles.

Scientific and Special Formats

Scientific notation handles very large or small numbers. Special formats include phone numbers and zip codes. They make data entry consistent.

Use scientific notation for precision. Apply special formats for standardization. Improve data quality.

# Scientific notation
ws['E1'] = 123456789
ws['E1'].number_format = '0.00E+00'

ws['E2'] = 0.000012345
ws['E2'].number_format = '0.00E-00'

# Special formats
ws['E3'] = 1234567890
ws['E3'].number_format = '(###) ###-####'  # Phone number

ws['E4'] = 12345
ws['E4'].number_format = '00000'  # Zip code with leading zeros

Scientific notation maintains precision. Special formats ensure consistency. Both improve data presentation.

Custom Number Formats

Custom formats provide maximum flexibility. Create exactly what you need. Use format codes creatively.

Learn format code syntax. Combine different sections. Handle positive, negative, zero, and text.

# Custom format with multiple sections
ws['F1'] = 1234.56
ws['F1'].number_format = '[Blue]$#,##0.00;[Red]-$#,##0.00;[Green]"Zero";[Yellow]@'

ws['F2'] = -789.12
ws['F2'].number_format = '[Blue]$#,##0.00;[Red]-$#,##0.00;[Green]"Zero";[Yellow]@'

ws['F3'] = 0
ws['F3'].number_format = '[Blue]$#,##0.00;[Red]-$#,##0.00;[Green]"Zero";[Yellow]@'

ws['F4'] = "Text Value"
ws['F4'].number_format = '[Blue]$#,##0.00;[Red]-$#,##0.00;[Green]"Zero";[Yellow]@'

# Conditional formatting with colors
ws['F5'] = 1500
ws['F5'].number_format = '[Green]$#,##0.00;[Red]-$#,##0.00'

Custom formats have four sections. They handle positive, negative, zero, and text values. Colors enhance readability.

Applying Formats to Ranges

Apply formats to cell ranges efficiently. Use loops or batch operations. Save time and ensure consistency.

Format entire columns or rows. Apply to specific ranges. Use Python loops for bulk formatting.

# Format a range of cells
for row in ws['G1:G10']:
    for cell in row:
        cell.number_format = '$#,##0.00'

# Format entire column
for cell in ws['H']:
    cell.number_format = '0.00%'

# Format based on conditions
for row in range(1, 11):
    value = ws[f'I{row}'].value
    if isinstance(value, (int, float)):
        if value > 1000:
            ws[f'I{row}'].number_format = '[Green]$#,##0'
        else:
            ws[f'I{row}'].number_format = '[Red]$#,##0'

Batch formatting saves time. Conditional formatting adds intelligence. Both improve efficiency.

Working with Large Datasets

Large datasets need efficient formatting. Use appropriate openpyxl modes. Consider performance implications.

For reading large files, check our Python openpyxl Read Only vs Normal Mode Guide. For writing, see Python openpyxl Streaming Write Mode Guide.

# Efficient formatting for large datasets
def format_large_range(worksheet, start_row, end_row, format_string):
    """Apply format to large range efficiently"""
    for row in range(start_row, end_row + 1):
        for col in range(1, 6):  # Columns A to E
            cell = worksheet.cell(row=row, column=col)
            if cell.value is not None:
                cell.number_format = format_string

# Apply currency format to first 1000 rows
format_large_range(ws, 1, 1000, '$#,##0.00')

Efficient formatting prevents memory issues. Batch operations improve performance. Plan your formatting strategy.

Best Practices and Common Issues

Follow best practices for reliable results. Avoid common formatting pitfalls. Test your formats thoroughly.

Use consistent formatting across similar data. Document custom format codes. Test with various data types.

# Best practices example
def apply_consistent_formatting(worksheet):
    """Apply consistent number formatting"""
    
    # Currency for financial data
    financial_columns = ['B', 'C', 'D']
    for col in financial_columns:
        for cell in worksheet[col]:
            if isinstance(cell.value, (int, float)):
                cell.number_format = '$#,##0.00'
    
    # Percentages for ratios
    for cell in worksheet['E']:
        if isinstance(cell.value, (int, float)):
            cell.number_format = '0.00%'
    
    # Dates for timestamp columns
    for cell in worksheet['F']:
        if isinstance(cell.value, datetime):
            cell.number_format = 'yyyy-mm-dd'

apply_consistent_formatting(ws)

Consistency improves readability. Documentation helps maintenance. Testing ensures reliability.

Integration with Other openpyxl Features

Number formatting works with other openpyxl features. Combine with cell styling and data validation. Create comprehensive Excel solutions.

For cell protection features, see Protect Sheets Lock Cells Python openpyxl Guide. Combine formatting with protection for secure reports.

from openpyxl.styles import Font, PatternFill, Alignment

# Combined formatting with styling
def format_financial_cell(cell, value):
    """Apply comprehensive formatting to financial cell"""
    cell.value = value
    cell.number_format = '$#,##0.00'
    
    if value < 0:
        cell.font = Font(color='FF0000', bold=True)  # Red for negative
    elif value > 1000:
        cell.font = Font(color='00FF00', bold=True)  # Green for large positive
    
    cell.alignment = Alignment(horizontal='right')
    return cell

# Apply comprehensive formatting
financial_cell = format_financial_cell(ws['J1'], -500.75)

Combined features create professional reports. Styling enhances formatting. Protection secures data.

Conclusion

Advanced number formatting is essential for professional Excel reports. Python openpyxl provides comprehensive formatting capabilities.

Master currency, percentages, dates, and custom formats. Apply formats efficiently to ranges. Combine with other openpyxl features.

Remember to use consistent formatting practices. Test your formats thoroughly. Create readable, professional Excel reports automatically.

Number formatting transforms raw data into meaningful information. It makes your Excel reports clear and professional. Start implementing these techniques today.