Last Updated on July 14, 2022 by Jay
The Python xlsxwriter library is capable of creating all sorts of conditional formatting for our Excel file. Let’s take a look at how to do that. One of our friends on Youtube requested this, and here you go!
This is part of the automating Excel series. If you need an introduction to the xlsxwriter library that we are using, head to part 1 of the series.
Excel Conditional Formatting
The name says it all – formatting based on conditions. It’s a neat feature in Excel that allows us to highlight (in most cases) cells according to certain conditions. And of course, we, the users get to define those conditions.
Also, what makes conditional formatting great is that the formatting is dynamic – when the values change, the format will change too.
In Excel, click on Conditional Formatting, we see a bunch of choices, let’s go down to “New Rules”. This will open up a window that basically summarizes all the options. The ways we can define the conditions are super flexible, so there’s no way to cover every single case. I’ll cover a few common scenarios here, but if you need help with any specific example, leave a comment down below and I will get back to you ????
Getting The Python Environment Ready
Let’s head into Python, generate some values, and a xlsxwriter workbook. Time for Christmas decoration!
The three formats we have created are format_r (red), format_y (yellow), and format_g (green).
import xlsxwriter
wb = xlsxwriter.Workbook(r'C:\Users\jay\Desktop\PythonInOffice\excel-file-formula\conditional_formatting.xlsx')
ws = wb.add_worksheet('formatting')
list_1 = list(range(10))
list_2 = [5,4,3,2,1,9,8,7,6,0]
list_3 = [i for i in range(-10,10)]
list_text = ['python','excel','python xlsxwriter','python automate excel','excel automate python','python']
format_r = wb.add_format({'bg_color': '#FFC7CE',
'font_color': '#9C0006'})
format_y = wb.add_format({'bg_color': '#FFEB9C',
'font_color': '#9C6500'})
format_g = wb.add_format({'bg_color': '#C6EFCE',
'font_color': '#006100'})Review Time
If you need a refresher on how to add formats using xlsxwriter library, head to part 3 of the series.
- We need a workbook object (wb) and a worksheet object (ws).
- Either the “A1” or (row, col) style notation can be used to reference cells and ranges.
- Create Excel format by using the
workbook.add_format()method. - Format a cell by writing data and formatting simultaneously into cell/range.
New Concept
- To create conditional formatting , use
worksheet.conditional_format('A1', {parameters}) - A conditional formatting is superimposed over the existing cell format. Not all cell format properties can be modified, such as font name, size, alignment, etc.
- Related to #2 above, most of the time we use conditional formatting just to highlight cells. i.e. change cell color.
Xlsxwriter Conditional Formatting parameters
The parameters inside the conditional_format() method must be a dictionary containing the properties that describe the type and style for the format. Some of the main properties are:
type– are we formatting cells, numbers, text, ranking, average, duplicate or formula? See the picture below, the “type” refers to the “Rule Type”.criteria– do we want to find the “greater/less than”, “contain” certain text, top items, etcvalue– usually in conjuction with the criteria, “great than 7”, “between 5 and 7”, “above” average, etcformat– the formatting, usually it’s just changing the cell/font color
Now we got the theory out of the way, let’s see how to apply them. Because we are going through several examples, so we’ll group our Python code into functions to make it more organized.
Following the order of the above screen “Rule Type” offered by Excel, we’ll take a look at each scenario below.
Conditional Formatting All Cells Color Scale
I hope you like rainbow ???? We can specify three colors (min, mid and max) and Excel will make a pretty rainbow for us. If you prefer only dual colors, then change 'type' to be '2_color_scale', and then simply remove the 'mid_color'.
def color_scale():
ws.write('N1','3 color scale')
ws.write_column('N2',list_1)
ws.conditional_format('N2:N11',
{'type': '3_color_scale',
'min_color':'red',
'mid_color':'yellow',
'max_color':'green'
})
Conditional Formatting All Cells Data Bar
You can make a lot of different cool stuff with the data bar, lots of flexibility there.
def data_bar():
ws.write('P1','data bar')
ws.write_column('P2',list_3)
ws.conditional_format('P2:P22',
{'type': 'data_bar',
'bar_color':'green',
'bar_only': True, #True - no data, False - show data
'bar_solid': False, # True - solid color fill, False - gradient color
'bar_negative_color':'red',
'bar_direction': 'left', # or 'right'
'bar_axis_position':'middle', # or 'none'
})
Conditional Formatting Based On Numbers
Pay attention to the dictionary inside the ws.conditional_format method, especially what values are passed into those properties. The 'criteria' can be any of the following (either column will work):
| ‘between’ | |
| ‘not between’ | |
| ‘equal to’ | ‘==’ |
| ‘not equal to’ | ‘!=’ |
| ‘greater than’ | ‘>’ |
| ‘less than’ | ‘<‘ |
| ‘greater than or equal to’ | ‘>=’ |
| ‘less than or equal to’ | ‘<=’ |
def based_on_number():
ws.merge_range('B1:C1','>7 is green, <5 is red, in-between is yellow')
ws.write_column('B2', list_1)
ws.write_column('C2', list_2)
## greater than 7, green color
ws.conditional_format('B2:C11',
{'type': 'cell',
'criteria': '>',
'value': 7,
'format':format_g
})
## less than 5, red color
ws.conditional_format('B2:C11',
{'type': 'cell',
'criteria': '<',
'value': 5,
'format':format_r
})
## between 5 and 7, yellow color
ws.conditional_format('B2:C11',
{'type': 'cell',
'criteria': 'between',
'minimum':5,
'maximum':7,
'format':format_y
})The above code generates the following conditional formatting:
Conditional Formatting Based On Numbers From Cell Input
This is similar to the previous example, except that we are not hardcoding the threshold values 5 and 7. We will let the formatting depends on cell values – even more dynamic!
Pay close attention to the ‘value' property in the code below – we need to use absolute references, or it won’t work. In general, for any 'value' property we need to use an absolute reference.
Once we generate this in Excel, you’ll see that the formatting will change as we modify the values inside cells B19 and C19.
def based_on_number_input():
ws.merge_range('B18:C18','with number input')
ws.write('B19',7) ## threshold value
ws.write('C19',5) ## threshold value
ws.write_column('B20', list_1)
ws.write_column('C20', list_2)
ws.conditional_format('B20:C29',
{'type': 'cell',
'criteria': '>',
'value': '$B$19',
'format':format_g
})
ws.conditional_format('B20:C29',
{'type': 'cell',
'criteria': '<',
'value': '$C$19',
'format':format_r
})
Conditional Formatting Based On Text
We can check whether the cells contain certain text. The criteria property includes the following:
'containing''not containing''begins with''ends with'
def based_on_text():
ws.write('E1','contain "python"')
ws.write('F1','begins with "python"')
ws.write_column('E2', list_text)
ws.write_column('F2', list_text)
ws.conditional_format('E2:E11',
{'type': 'text',
'criteria': 'containing',
'value': 'python',
'format':format_g
})
ws.conditional_format('F2:F11',
{'type': 'text',
'criteria': 'begins with',
'value': 'python',
'format':format_g
})Conditional Formatting Top/Bottom Ranked Values
We can highlight the items by 'top' or 'bottom' values, i.e. first 5 largest, or by percent, i.e. bottom 10% of the selected values. Omitting the 'criteria' means by count, while setting 'criteria' : '%' means by percentage.
def top_n():
ws.write('H1','top 5')
ws.write_column('H2',list_2)
ws.conditional_format('H2:H11',
{'type': 'top',
'value': 5,
#'criteria': '%',
'format':format_g
})Conditional Formatting Above/Below Average
For this one, Excel will calculate an average for the selected range, then compare each number in the range with the average, and format accordingly.
def average():
ws.write('J1','average')
ws.write_column('J2',list_2)
ws.conditional_format('J2:J11',
{'type': 'average',
'criteria': 'above',
'format':format_g
})Conditional Formatting Unique/Duplicate Values
We can also highlight duplicated (or unique) values in a selected range.
def duplicate():
ws.write('L1','duplicate')
ws.write_column('L2',list_text)
ws.conditional_format('L2:L11',
{'type': 'duplicate', #or 'unique'
'format':format_g
})Conditional Formatting Based On Formula
We can also do conditional formatting based on a formula – and make our Excel even much more dynamic!!
However, formula-based formatting can be a little bit tricky to work with because some situations require absolute references while others require a non-absolute reference. My go-to strategy is: try the formula in Excel, with or without $ in your cell references. If it works in your Excel, taking the same formula over to Python will also work.
The below code compares numbers in column R and column S, then highlights (in green) the larger number between the two columns.
Note that 'type' is set to 'formula', in the 'criteria' we type the formula as if for the first item (of the selected range) only. Inside the conditional_format method, we are formatting cells R2:R11, the first element is R2, thus the formula is '=R2>S2'. If we want to apply the format to R3:R11, then the formula needs to be ‘=R3>S3’, etc.
Also, note that in this case, we are comparing two columns so we don’t use absolute references in the formula. In other cases, you might need to use absolute references to make this formula-based formatting work. See my go-to strategy above.
def based_on_formula():
ws.merge_range('R1:S1','compare R and S, highlight the greater #')
ws.write_column('R2', list_1)
ws.write_column('S2', list_2)
ws.conditional_format('R2:R11',
{'type': 'formula',
'criteria': '=R2>S2',
'format':format_g
})
ws.conditional_format('S2:S11',
{'type': 'formula',
'criteria': '=S2>R2',
'format':format_g
})That’s it! Leave a comment below if you need help with any specific scenario.
The examples are very helpful. Thanks for putting together such a useful page.