learn_openpyxl

openpyxl - 3. Cells and Cell Values

3.1 Accessing Cell Values

Cell values are accessed using several methods.

The most common method is indexing using the cell’s coordinates as a string (e.g., “A1”) or using the cell()1 method, which takes row and column numbers, see below sample:

from openpyxl import load_workbook

workbook = load_workbook("my_workbook.xlsx")
sheet = workbook.active

# Accessing using string index
cell_value = sheet["A2"].value
print(f"Value of A1 is: {cell_value}")

# Accessing using cell() method (row, column)
cell_value1 = sheet.cell(row=2, column=4).value
print(f"Value of D2 is: {cell_value1}")

# Checking for a None value (empty cell)
if sheet["F1"].value is None:
    print("Cell F1 is Empty")

if sheet["A200"].value is None:
    print("Cell A200 is Empty")
else:
    print(f"Value of A200 is: {sheet["A200"].value}")

Source code reference:

Note:

  1. the cell coordinates are 1-based (the top-left cell is A1).
  2. an empty cell will have a value of None.

3.2 Setting Cell Values

Setting a cell’s value is equally simple, as below sample:

from openpyxl import Workbook

workbook = Workbook()
sheet = workbook.active

sheet["A1"] = "Hello Excel"
sheet['B2'] = 42
sheet["C1"] = 3.1415
sheet["F2"] = "=SUM(B2,C1)"

sheet.cell(row = 4, column = 5).value = True

workbook.save("output.xlsx")

You can assign various data types to cells.

3.3 Data Types

openpyxl hands several data types as below:

Data Type Description
Numbers Integers, floats, etc. are stored as numbers.
Strings Text values are stored as strings
Booleans True and False are supported.
Dates and Times These are stored as Python datetime objects.
Formulas Formulas are stored as strings, but openpyxl can evalute some simple formulas
None Represents an empty cell

Source code reference:

3.4 Formulas and Calculations

openpyxl can handle formulas in cells, as below sample:

from openpyxl import Workbook

workbook = Workbook()
sheet = workbook.active

sheet["A1"], sheet["A2"], sheet["A3"] = 10, 20, 30
sheet["A10"] = "=SUM(A1:A2)" # suggest to keep full capital
sheet["A11"] = "=sum(A1:A2)"
sheet["A12"] = "=AVERAGE(A1:A3)"

sheet["B1"] = '=IF(A1>100, "High", "Low")'

# Using variable for ranges
start_row, end_row = 1, 3
sheet["B2"] = f"=SUM(A{start_row}:A{end_row})"

sheet1 = workbook.create_sheet('Sheet1')
sheet2 = workbook.create_sheet('Sheet2')
sheet1["A1"] = 25
sheet2["B1"] = "=Sheet1!A1 * Sheet1!A1"

workbook.save("formula_example.xlsx")

3.5 Number Formatting

To format numbers, use the number_format property of the cell:

from openpyxl import Workbook
from openpyxl.styles import numbers

workbook = Workbook()
sheet = workbook.active

sheet["A1"], sheet["A2"], sheet["A3"], sheet["A4"] = 1234.56, 1234.56, 1234.56, 1234.56

sheet["B1"], sheet["B2"], sheet["B3"], sheet["B4"] = 1234.56, 1234.56, 1234.56, 1234.56

sheet["B1"].number_format = numbers.FORMAT_PERCENTAGE
sheet["B2"].number_format = numbers.FORMAT_PERCENTAGE_00
sheet["B3"].number_format = '0.000'
sheet["B4"].number_format = '##,##0'

workbook.save("number_formats.xlsx")

Source Code Reference:

3.6 Dates and Times

Dates and times are represented using Python’s datetime objects:

from openpyxl import Workbook
from datetime import datetime
from openpyxl.styles import numbers

workbook = Workbook()
sheet = workbook.active

sheet["A1"] = datetime(2025, 12, 31, 13, 19, 00)
sheet["A2"] = "2025/11/05"
sheet["A2"].number_format = numbers.FORMAT_DATE_YYYYMMDD2
sheet["A3"] = datetime(2025, 12, 31, 13, 19, 00)
sheet["A3"].number_format = numbers.FORMAT_DATE_TIME6

workbook.save("date_time_sample.xlsx")

openpyxl handls the conversion between Python’s datetime objects and Excel’s date system automatically.

3.7 Working with Cell Ranges

You can efficiently work with ranges of cells using sheet.iter_rows() and sheet.iter_cols(). These methods provide iterators to traverse ranges efficiently:

from openpyxl import load_workbook

workbook = load_workbook("my_workbook.xlsx")
sheet = workbook.active

# Iterate through row 1 to 10 and column A to D
for row in sheet.iter_rows(
    min_row = 1,
    max_row = 5,
    min_col = 1,
    max_col = 5
):
    for cell in row:
        print(cell.value, end = "\t")
    print()

# Iterate through column 1 to 4
for col in sheet.iter_cols(
    # min_row = 1,
    max_row = 8,
    # min_col = 1,
    max_col = 2
):
    for cell in col:
        print(cell.value, end="\t")
    print()

Source code reference:

Last Updated at: 12/31/2025, 2:06:35 PM

This site is open source. Improve this page.