In our daily lives, we often find ourselves repeating the same formatting tasks in Excel, such as changing font styles, aligning cells, or applying colors. These tasks can be time-consuming and very tedious, especially when dealing with large datasets. However, with the magic of Python automation, we can simplify and speed up these repetitive formatting tasks in Excel.

By writing small Python scripts, we can automate the process of formatting Excel files, saving us valuable time and effort. In this article, we’ll explore how Python can be used to automate these formatting tasks, making our workflow more efficient and productive. Whether you’re a data analyst, business professional, or student, learning how to automate Excel formatting with Python can greatly enhance your productivity and streamline your workflow.

I ll showcase 5 pointers for How you can automate repetitive formatting tasks in Excel using Python along with code:

1. Standardize Cell Formatting: Automatically apply consistent formatting (such as font style, size, color, and alignment) to specific cells or ranges across multiple Excel files.
2. Conditional Formatting: Implement conditional formatting rules programmatically to highlight cells based on specified criteria, such as value thresholds or text patterns.
3. Data Validation: Automate the setup of data validation rules to ensure data integrity and consistency, such as restricting input to a predefined list or range of values.
4. Merge and Center: Automatically merge and center cells in specific columns or rows based on predefined conditions, improving readability and presentation of data.
5. Custom Number Formatting: Apply custom number formatting to cells to display data in desired formats, such as currency, percentages, dates, or scientific notation, across multiple worksheets or workbooks.

Standardize Cell Formatting

import openpyxl
from openpyxl.styles import Font, Alignment, PatternFill

# Load the Excel workbook
workbook = openpyxl.load_workbook("employee_names_with_passwords.xlsx")

# Select the active sheet
sheet = workbook.active

# Iterate through each row in the sheet
for row in sheet.iter_rows(min_row=2, min_col=1, max_col=4):
    # Apply formatting to each cell in the row
    for cell in row:
        # Apply desired formatting
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal="center")
        cell.fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")

# Save the modified workbook
workbook.save("formatted_example.xlsx")

in the above code the excel sheet name consist of employee name with there passwords

Output:

Conditional Formatting

import openpyxl
from openpyxl.formatting.rule import ColorScaleRule

def apply_conditional_formatting(file_path, sheet_name, condition):
    # Load the Excel workbook
    workbook = openpyxl.load_workbook(file_path)
    # Select the sheet by name (using the sheet_name variable)
    sheet = workbook[sheet_name]
    # Define the conditional formatting rule
    rule = ColorScaleRule(start_type='num', start_value=0, start_color='FF0000', end_type='num', end_value=1, end_color='00FF00')
    # Apply the rule to the specified range
    sheet.conditional_formatting.add('A1:C10', rule)
    # Save the modified workbook
    workbook.save(file_path)

# Usage: Pass the file path, sheet name, and condition as arguments
apply_conditional_formatting('employee_names_with_passwords.xlsx', 'Sheet', 0.05)

To verify that the conditional formatting rule has been applied correctly, you can open the Excel file and inspect the formatting settings. Here’s how you can do it:

1. Open the Excel file ’employee_names_with_passwords.xlsx’ using Microsoft Excel or any compatible spreadsheet application.
2. Select the range of cells where you applied the conditional formatting rule. In this case, it’s cells A1 to C10.
3. Go to the ‘Home’ tab on the Excel ribbon.
4. In the ‘Styles’ group, click on ‘Conditional Formatting’.
5. From the dropdown menu, select ‘Manage Rules’.
6. You should see a list of conditional formatting rules applied to the selected range. Find the rule that you defined in the Python code, which should be a color scale rule with a red-to-green gradient.
7. You can further inspect or modify the rule settings by selecting it and clicking on ‘Edit Rule’.

Output for the above

Data Validation

import openpyxl

def apply_data_validation(file_path, sheet_name, cell_range, values):
    workbook = openpyxl.load_workbook(file_path)
    sheet = workbook[sheet_name]
    dv = openpyxl.worksheet.datavalidation.DataValidation(type="list", formula1=f'"{",".join(values)}"', showDropDown=True)
    sheet.add_data_validation(dv)
    dv.add(cell_range)
    workbook.save(file_path)

# Usage:
apply_data_validation('employee_names_with_passwords.xlsx', 'Sheet', 'A1:A10', ['Option1', 'Option2', 'Option3'])

How to Check the above Code in Output:

Note: Relace excel with your file name

1. Open the Excel file ’employee_names_with_passwords.xlsx’ using Microsoft Excel or any compatible spreadsheet application.
2. Navigate to the sheet named ‘Sheet1’, or the sheet name you specified in the code.
3. Check the cell range specified in the code, which is ‘A1:A10’. This range should contain dropdown lists with the values ‘Option1’, ‘Option2’, and ‘Option3’.
4. Click on any cell within the specified range to see if a dropdown arrow appears next to the cell. If it does, click on the arrow to verify that the dropdown list contains the expected values.
5. Repeat this process for other cells within the specified range to ensure that the data validation has been applied correctly to all cells.

Merge and Center

import openpyxl
from openpyxl.utils import get_column_letter

def merge_and_center(file_path, sheet_name, column, start_row, end_row):
    workbook = openpyxl.load_workbook(file_path)
    sheet = workbook[sheet_name]
    sheet.merge_cells(f'{get_column_letter(column)}{start_row}:{get_column_letter(column)}{end_row}')
    workbook.save(file_path)

# Usage:
merge_and_center('employee_names_with_passwords.xlsx', 'Sheet', 1, 1, 5)

Custom Number Formatting

import openpyxl

def apply_custom_number_formatting(file_path, sheet_name, cell_range, number_format):
    workbook = openpyxl.load_workbook(file_path)
    sheet = workbook[sheet_name]
    for row in sheet[cell_range]:
        for cell in row:
            cell.number_format = number_format
    workbook.save(file_path)

# Usage:
apply_custom_number_formatting('employee_names_with_passwords.xlsx', 'Sheet', 'A1:A10', '0.00%')

The output of the above code would be to apply the custom number formatting ‘0.00%’ to the cells in the range ‘A1:A10′ of the specified Excel file (’employee_names_with_passwords.xlsx’) and sheet (‘Sheet’). This formatting would display the numbers in percentage format with two decimal places.

However, since the code does not produce any visible output other than saving the modified Excel file, you wouldn’t see any direct visual confirmation of the formatting changes unless you open the Excel file and inspect the specified range ‘A1:A10’.

In conclusion, leveraging Python for automating repetitive formatting tasks in Excel provides significant efficiency gains and streamlines workflows for users handling large datasets or complex spreadsheets. By harnessing Python’s libraries such as Openpyxl, Pandas, and XlsxWriter, users can programmatically apply formatting rules, manipulate data, and generate reports with ease. This automation not only reduces manual effort and errors but also enhances productivity and enables users to focus on higher-value tasks. With the versatility and flexibility of Python, the possibilities for automating Excel formatting tasks are virtually limitless, offering a powerful solution for optimizing data management processes.