Styling Excel Cells with Python openpyxl: A Comprehensive Guide

In everyday work, Excel can become a vivid storytelling canvas, and Python's openpyxl library makes it easy to enhance cell appearance with various styles.

First, ensure the openpyxl library is installed via pip install openpyxl .

Example 1: Font Styles – The code creates bold, italic, underline, and red fonts and applies them to cells A1‑D1.

from openpyxl import Workbook
from openpyxl.styles import Font
wb = Workbook()
ws = wb.active
bold_font = Font(bold=True)
italic_font = Font(italic=True)
underline_font = Font(underline='single')
color_font = Font(color="FF0000")
ws['A1'] = 'Bold Text'
ws['A1'].font = bold_font
ws['B1'] = 'Italic Text'
ws['B1'].font = italic_font
ws['C1'] = 'Underline Text'
ws['C1'].font = underline_font
ws['D1'] = 'Red Text'
ws['D1'].font = color_font
wb.save('styled_excel.xlsx')

Example 2: Borders – A thin border style is defined and applied to cell E1.

from openpyxl.styles import Border, Side
thin_border = Border(left=Side(style='thin'),
                     right=Side(style='thin'),
                     top=Side(style='thin'),
                     bottom=Side(style='thin'))
ws['E1'] = 'Cell with Thin Border'
ws['E1'].border = thin_border

Example 3: Fill Background – A solid yellow fill is created and set for cell F1.

from openpyxl.styles import PatternFill
yellow_fill = PatternFill(start_color='FFFF00',
                          end_color='FFFF00',
                          fill_type='solid')
ws['F1'] = 'Yellow Background'
ws['F1'].fill = yellow_fill

Example 4: Alignment – Center alignment is defined and applied to cell G1.

from openpyxl.styles import Alignment
center_alignment = Alignment(horizontal='center',
                             vertical='center')
ws['G1'] = 'Centered Text'
ws['G1'].alignment = center_alignment

Example 5: Number Format – A numeric value is placed in H1 with a custom number format.

ws['H1'] = 123456.789
ws['H1'].number_format = '#,##0.00'

Example 6: Conditional Formatting – A three‑color scale is defined and applied to the range I1:I10.

from openpyxl.styles import ColorScale
cs = ColorScale(num_colors=3,
                color=['000000', 'FFFF00', '00FF00'],
                type='num',
                val=[0, 50, 100])
ws.conditional_formatting.add('I1:I10', cs)

Example 7: Merged Cells – Cells J1 and K1 are merged and centered.

ws.merge_cells('J1:K1')
ws['J1'] = 'Merged Cells'
ws['J1'].alignment = center_alignment

Example 8: Column Width and Row Height – Column L width and row 1 height are set.

ws.column_dimensions['L'].width = 20
ws.row_dimensions[1].height = 40

Example 9: Hyperlink – A hyperlink to Google is added in cell M1.

from openpyxl.styles import Hyperlink
ws['M1'] = Hyperlink(display='Visit Google', ref='http://www.google.com')

Example 10: Combined Styles – Multiple styles are applied together to cell N1.

ws['N1'] = 'Combined Styles'
ws['N1'].font = bold_font
ws['N1'].fill = yellow_fill
ws['N1'].border = thin_border
ws['N1'].alignment = center_alignment

Full Code Showcase – The complete script combines all the above techniques, creating a workbook with styled cells and saving it as styled_excel.xlsx .

from openpyxl import Workbook
from openpyxl.styles import Font, Border, Side, PatternFill, Alignment, ColorScale, Hyperlink
wb = Workbook()
ws = wb.active
bold_font = Font(bold=True)
italic_font = Font(italic=True)
underline_font = Font(underline='single')
color_font = Font(color="FF0000")
thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))
yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
center_alignment = Alignment(horizontal='center', vertical='center')
cs = ColorScale(num_colors=3, color=['000000', 'FFFF00', '00FF00'], type='num', val=[0, 50, 100])
ws['A1'] = 'Bold Text'
ws['A1'].font = bold_font
ws['B1'] = 'Italic Text'
ws['B1'].font = italic_font
ws['C1'] = 'Underline Text'
ws['C1'].font = underline_font
ws['D1'] = 'Red Text'
ws['D1'].font = color_font
ws['E1'] = 'Cell with Thin Border'
ws['E1'].border = thin_border
ws['F1'] = 'Yellow Background'
ws['F1'].fill = yellow_fill
ws['G1'] = 'Centered Text'
ws['G1'].alignment = center_alignment
ws['H1'] = 123456.789
ws['H1'].number_format = '#,##0.00'
ws.conditional_formatting.add('I1:I10', cs)
ws['J1'] = 'Merged Cells'
ws.merge_cells('J1:K1')
ws['J1'].alignment = center_alignment
ws.column_dimensions['L'].width = 20
ws.row_dimensions[1].height = 40
ws['M1'] = Hyperlink(display='Visit Google', ref='http://www.google.com')
ws['N1'] = 'Combined Styles'
ws['N1'].font = bold_font
ws['N1'].fill = yellow_fill
ws['N1'].border = thin_border
ws['N1'].alignment = center_alignment
wb.save('styled_excel.xlsx')

With just a few lines of Python, you can transform ordinary Excel sheets into visually engaging reports that stand out to colleagues and stakeholders.