Search code examples
pythonxlsxwriter

xlsxwriter conditional format formula with multiple criteria

I'm trying to apply conditional formatting to my excel file using xlsxwriter, but not sure how to code for two conditions. After perusing the documentation I only see examples for one.

Concretely I'm trying to say when the value (H13) is greater than (H5) AND > (H6) then color green.

Below is my attempt that does not work. I believe this is simply a syntax issue.

worksheet.conditional_format('H13:H13', {'type': 'formula',
                                         'criteria': '=H13 >= $H$5 and H13 > $H$6 ',
                                         'format': green_bg})
Solution

  • In all cases like this it is best to figure out the conditional format in Excel first and then transfer it across to xlsxwriter.

    Excel doesn't allow joined/union conditional format conditions like your example. Instead you would need to use something like AND(). Like this:

    import xlsxwriter

workbook = xlsxwriter.Workbook('conditional_format.xlsx')
worksheet = workbook.add_worksheet()

green_bg = workbook.add_format({'bg_color': '#C6EFCE',
                                 'font_color': '#006100'})


worksheet.write('H5', 2)
worksheet.write('H6', 5)
worksheet.write('H13', 9)

worksheet.conditional_format('H13:H13', {'type': 'formula',
                                        'criteria': '=AND($H$13 >= $H$5, $H$13 > $H$6)',
                                        'format': green_bg})

workbook.close()

    Output:

    enter image description here

    However, the logical statement here is a little suspect. It is actually just the same as $H$13 > $H$6. Maybe you meant to say ..., $H$13 < $H$6.