Skip to main content
Practice

How to Modify Cell Values Using Python Code

In this lesson, we will learn how to use openpyxl to modify cell values in various ways and change cell formatting.


Modifying a Specific Cell in a Sheet​

To modify a cell's value, simply assign a new value to it.

For example, to change the value of cell B1 to 100, you can do the following:

Modify the value of cell B1
ws['B1'] = 100

Alternatively, you can use the cell method introduced in previous lessons, using ws.cell(row=row_number, column=column_number).


Modifying Multiple Cells Based on a Pattern​

When you need to enter data following a specific pattern into multiple cells, you can use a loop, as shown below.

Modify Multiple Cells Based on a Pattern
# Enter values from 1 to 10 in column B
for i in range(1, 11):
    ws[f'B{i}'] = str(i * 100) + 'km'

In this example, cells from B1 to B10 are filled with values from 100 to 1000 followed by 'km'.


You can also enter values from 1 to 10 in row 1, columns 1 through 10, as follows:

Modify Multiple Cells Based on a Pattern
# Enter values from 1 to 10 in row 1
for i in range(1, 11):
    ws.cell(row=1, column=i, value=str(i) + "th")

By applying this concept, you can automatically enter patterned data into both rows and columns.

Modify Multiple Cells Based on a Pattern
# Nested for loop to enter values from 1 to 100 (5 columns x 20 rows)
for i in range(1, 21):
    for j in range(1, 6):
        ws.cell(row=i, column=j, value=(i - 1) * 5 + j)

Modifying Cell Data Based on Conditions​

Using conditional statements, you can modify cell data based on specific conditions.

The code below changes the value of cells with values greater than 5 to 'Large', and changes others to 'Small'.

Modify Cell Data Based on Conditions
# Change the value of cells with values greater than 5 to 'Large'
for i in range(1, 11):
    if ws[f'B{i}'].value > 5:
        ws[f'B{i}'] = 'Large'
    else:
        ws[f'B{i}'] = 'Small'

# Save the modified content
wb.save('output_file.xlsx')

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.