Adjust Excel Fonts using Python openpyxl

Last Updated on July 14, 2022 by Jay

This tutorial will show you how to adjust Excel fonts including size, color, bold, etc. using the Python openpyxl library. We’ll continue with the previous example to make our monthly budget spreadsheet look prettier. Check out the first tutorial to learn how to create the following spreadsheet using Python.

Library

To install the openpyxl library, type the following line in a command prompt window:

pip install openpyxl

The Excel Fonts and Styles Objects in Python

We can use openpyxl‘s Font object to customize fonts for our Excel files. Let’s import that first. As the import line hints, the Font object is under a parent class called styles.

Styles are used to basically change the look of the spreadsheet, including fonts, color, border, alignment, etc. We are going to focus on just fonts in this tutorial, and we’ll cover other styles (i.e. formatting) in later tutorials.

from openpyxl.styles import Font

Changing Fonts

Let’s make the spreadsheet title bigger and change its font color. We can customize the font using the Font object. The arguments inside the Font() in the below code should show you all the elements we can change for a font in Excel.

Also, ws.column_dimentions.width is used to change the cell width.

ws['B2'].font = Font(name= 'Calibri',
                     size=40,
                     bold=True,
                     italic=False,
                     strike=False,
                     underline='none',
                     color='4472C4'
                     )

for c in 'BCDEF':
    ws.column_dimensions[c].width = 15

Once a cell’s font is set, we can’t change individual elements. Let’s try changing the text color to red (“FF0000”) following the above ws[‘B2’].font = Font(…) assignment. It will give an AttributeError:

ws['B2'].font.color = 'FF0000'

AttributeError: Style objects are immutable and cannot be changed.Reassign the style with a copy

If we want to change any element of a pre-defined Font object, we need to re-construct a new object and re-assign it to the cell we want to change. The below would work fine:

ws['B2'].font = Font(name= 'Calibri',
                     size=40,
                     bold=True,
                     italic=False,
                     strike=False,
                     underline='none',
                     color='FF0000'
                     )

Copying Fonts

Copying fonts work like the “Formatting brush” in Excel. We can copy the font and other styles of a given cell, then apply those to other cells. Note we need to import the copy library to copy fonts.

from copy import copy

ws['B7'].font = Font(bold=True, size = 14)

ws['B14'].font = copy(ws['B7'].font)
ws['B11'].font = copy(ws['B7'].font)
ws['B19'].font = copy(ws['B7'].font)

for r in range(7,14,2):
    ws[f'E{r}'].font = copy(ws['B7'].font)