Format Cells in a Live Workbook with xlwings

openpyxl formats a file: you load it, change it, save it, and someone opens it later. xlwings formats a workbook that is open in Excel right now — the person watching sees the header turn bold and the totals turn green as the script runs. That makes xlwings the tool for interactive tools, buttons in a workbook, and jobs that must work through Excel itself (refreshing data connections, using Excel's own number formats and table styles). Its formatting API is small and readable for the common cases, and for everything else it hands you Excel's own object model through .api. This page formats a report sheet end to end and covers the speed habits that matter, because every property you set is a round trip to Excel. It belongs to Automating Excel With xlwings: The Basics.

Prerequisites

pip install xlwings

xlwings drives a real Excel installation, so it runs on Windows or macOS with Excel installed; the code on this page is not executed by our build, which runs on Linux, but every xlwings property used was checked against the xlwings 0.37 API. The examples assume a workbook sales_report.xlsx with a sheet Report holding a header row in A1:E1 and 500 rows of data below, as an exported report usually looks before formatting.

Step 1 — Connect and look at the range

import xlwings as xw

wb = xw.Book("sales_report.xlsx")          # opens it, or attaches if already open
sheet = wb.sheets["Report"]
data = sheet.range("A1").expand()          # the contiguous block starting at A1
header = data.rows[0]
body = data.offset(1).resize(data.rows.count - 1)
print(data.address, header.address, body.address)

expand() grows from A1 down and right until it hits empty cells, so the code adapts when next month's report has more rows. offset and resize carve out the body below the header. Connecting to running workbooks and reading values is covered in Read and Write a Live Excel Workbook with xlwings.

Step 2 — Fills, fonts and number formats

header.color = "#4338CA"                   # fill; also accepts (67, 56, 202)
header.font.bold = True
header.font.color = "#FFFFFF"
header.font.size = 11

sheet.range("D2").expand("down").number_format = "#,##0.00"
sheet.range("B2").expand("down").number_format = "dd mmm yyyy"
sheet.range("E2").expand("down").number_format = "0.0%"
data.columns.autofit()

Range.color sets the fill and accepts a hex string, an RGB tuple or None to clear it; reading it back returns a tuple. font exposes bold, italic, color, size and name. number_format takes the same codes as Excel's Format Cells dialog, so anything that works there works here — see Write Custom Number Format Codes in Excel with Python. autofit asks Excel itself to size the columns, which is more accurate than any estimate a file-based library can make, because Excel measures the rendered text.

Step 3 — Borders and anything else through .api

xlwings deliberately keeps its own API small. For borders, conditional formats and the rest of Excel's formatting, use .api, which returns the underlying Excel object — the same object VBA uses:

from xlwings.constants import BordersIndex, LineStyle, BorderWeight

bottom = header.api.Borders(BordersIndex.xlEdgeBottom)      # Windows (COM)
bottom.LineStyle = LineStyle.xlContinuous
bottom.Weight = BorderWeight.xlMedium

total_row = data.rows.count + 1
sheet.range(f"A{total_row}").value = "Total"
sheet.range(f"D{total_row}").formula = f"=SUM(D2:D{total_row - 1})"
sheet.range(f"A{total_row}:E{total_row}").font.bold = True

xlwings.constants provides Excel's named constants (xlEdgeBottom is 9, xlContinuous 1), so the code reads like the VBA it mirrors — handy when translating a recorded macro, as in Translate Common VBA Macros to Python. The catch is portability: on Windows .api is Excel's COM object, on macOS it is an AppleScript bridge with different syntax, so .api code written on one platform usually needs adjusting for the other. Keep to xlwings' own properties where they suffice.

Step 4 — Conditional formats and an Excel table

Conditional formatting through .api uses Excel's FormatConditions collection directly (Windows):

margin = sheet.range("E2").expand("down")
scale = margin.api.FormatConditions.AddColorScale(3)       # three-colour scale
negatives = sheet.range("D2").expand("down").api.FormatConditions.Add(Type=1, Operator=6, Formula1="=0")
negatives.Font.Color = 0x00009C                             # BGR: dark red text for values < 0

Type=1 is a cell-value rule and Operator=6 means "less than"; Excel's colour values in COM are BGR integers, so 0x00009C is dark red. When in doubt, record a macro in Excel while applying the format and translate it.

Turning the range into a proper Excel table gives banding, filter buttons and a style in one call, with no .api needed:

table = sheet.tables.add(source=data, name="SalesReport", table_style_name="TableStyleMedium2")
print(table.name, table.range.address)

tables.add takes the source range, a table name and any built-in style name from Excel's gallery. Tables also make formulas more robust — see Create an Excel Table with Python.

Step 5 — Keep it fast

Every property assignment crosses from Python into Excel and back. Formatting 500 rows cell by cell means thousands of round trips and visible flicker; formatting the whole range at once is one call:

import time

app = wb.app
app.screen_updating = False
try:
    start = time.perf_counter()
    body.font.size = 10                               # one call for the whole body
    for i in range(1, body.rows.count, 2):            # banding: still one call per row
        body.rows[i].color = "#F0F4FF"
    print(f"formatted in {time.perf_counter() - start:.1f} s")
finally:
    app.screen_updating = True

Turning off screen_updating stops Excel redrawing after every change, which is often the biggest single speed-up; the try/finally guarantees it is turned back on even if the script fails, or the user is left with a frozen-looking window. Prefer whole-range operations, a table style or a conditional format over loops — banding via tables.add above costs one call instead of 250. The same principle applies to reading and writing values, as measured in Speed Up xlwings with Bulk Reads and Writes.

Step 6 — Save, and decide what the workbook should look like next time

sheet.activate()
excel = wb.app.api                                         # Windows: the Excel Application COM object
excel.ActiveWindow.FreezePanes = False                     # clear any existing freeze
sheet.range("A2").select()
excel.ActiveWindow.FreezePanes = True                      # freeze everything above row 2
wb.save()

Freezing panes is a window setting rather than a cell format, which is why it goes through the window object. Save when done; if the formatting is the same every month, consider putting it in a template workbook instead and letting the script only fill values — formatting that lives in the template does not need to be re-applied, and runs faster. Template filling is covered in Fill an Excel Template with Python and openpyxl.

Step 7 — Make the formatting repeatable

Scripts that format a live workbook tend to run more than once — after the data is refreshed, or when someone presses the button again. Clearing the previous formatting first makes every run produce the same result instead of piling conditional formats on top of each other:

data.clear_formats()                                       # fills, fonts, borders, number formats
data.api.FormatConditions.Delete()                         # Windows: remove earlier conditional formats
for existing in list(sheet.tables):
    if existing.name == "SalesReport":
        existing.api.Unlist()                              # turn the old table back into a range

clear_formats resets the visual formatting while keeping the values; deleting the conditional formats matters most, because FormatConditions.Add adds a rule every time and a sheet that has been formatted twenty times carries twenty identical rules. Removing the old table before tables.add avoids an error about overlapping tables. Run the clean-up, then Steps 2–5, and the sheet looks the same no matter how often the script runs.

Common pitfalls

• Formatting cell by cell. Each assignment is a round trip; format whole ranges.
• Leaving screen updating off. Always restore it in a finally block.
• .api code across platforms. Windows COM and macOS AppleScript differ; test on the platform users have.
• BGR colours in COM. Excel's COM colour integers are blue-green-red; xlwings' own color property takes RGB.
• Formatting before writing values. expand() stops at empty cells; write the data first, then format the range it occupies.

Conclusion

xlwings formats a workbook live in Excel. Use its own properties — color, font.bold, font.color, number_format, autofit, tables.add — for the common cases, and .api with xlwings.constants for borders, conditional formats and anything else in Excel's object model, remembering that .api differs between Windows and macOS. Format whole ranges rather than cells, switch screen_updating off inside try/finally, and move formatting that never changes into a template.

Frequently asked questions

How do I change a cell's background colour with xlwings? Assign to the range's color property: sheet.range("A1").color = "#FFF2CC" or an RGB tuple such as (255, 242, 204). Set it to None to remove the fill.

How do I make text bold or change the font colour in xlwings? Use the range's font object: rng.font.bold = True, rng.font.color = "#9C0006", rng.font.size = 11, rng.font.name = "Calibri".

How do I add borders with xlwings? xlwings has no border property of its own, so use the Excel object model through the range's api attribute: on Windows, take the Borders item for the edge you want and set its LineStyle and Weight, using the named constants in xlwings.constants.

Why is formatting with xlwings slow? Every property assignment is a call from Python into Excel. Format whole ranges instead of looping over cells, prefer table styles and conditional formats, and set app.screen_updating = False while the script runs.

Related

• Up one level: Automating Excel With xlwings: The Basics — controlling Excel from Python.
• Styling Excel Cells with openpyxl — the same formatting on files.
• When to Use xlwings Instead of openpyxl — choosing between the two.
• Insert a matplotlib Plot into a Live Workbook with xlwings — adding pictures the same way.