Pandas to Excel conditional formatting whole column

Question

I want to write a Pandas dataframe into Excel with formatting. For this I'm using xlsxwriter. My question is twofold:

• First, how can I apply conditional formatting to a whole column? In the examples they use specific cell-range formatting (eg. worksheet1.conditional_format('B3:K12', ...)), is there a way I can reference a whole column?

• Second, I want to mark the column B with red if it differs from column C by more than 15%. What would be the correct formula for this?

I'm currently trying the below code, which doesn't work (I don't know how to reference columns).

writer = pd.ExcelWriter('pacing.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Last year vs. current state')
#worksheet.set_column('B:B', 18, format1)
workbook  = writer.book
worksheet = writer.sheets['Last year vs. current state']
format_missed = workbook.add_format({'bg_color': '#ff0000'})
format_ok = workbook.add_format({'bg_color': '#008000'})

worksheet.conditional_format('C:C', {'type':     'formula',
                                        'criteria': '=ABS((B-C)/C) > 15',
                                        'format':   format_missed})
writer.save() 

Also, I'm running Excel on a Mac, not sure if it makes any difference, but I've noticed where the Windows version uses ,, the Mac version needs ; for formulas. Don't know how this affects xlsxwriter.

Sugar

Do it in Excel and than copy the formula in python. B:B is the usual method to reference entire column (also in this case: test first in excel). Conditional formating is excel stuff. Maybe you want just simply check in python and format cells, instead of giving Excel the condition, range and format parameters). – Boyceboycey

Answer 1

First, how can I apply conditional formatting to a whole column

You can specify the cell range from the first to the last row in the column like C1:C1048576. This is how it is stored internally, it is just displayed as C:C.

This applies to all cases where a column range can be used in XlsxWriter.

Second, I want to mark the column B with red if it differs from column C by more than 15%. What would be the correct formula for this?

That is something you should figure out in Excel and then apply the conditional format to XlsxWriter. Something like this will probably work:

=ABS(B1-C1)/C1 > 0.15

Also, I'm running Excel on a Mac, not sure if it makes any difference, but I've noticed where the Windows version uses ,, the Mac version needs ; for formulas. Don't know how this affects xlsxwriter

See the Working with Formulas section of the XlsxWriter docs for an explanation of how to work with this.

Xray

Thank you! As for the ABS(B1-C1)/C1 > 0.15 part, this again is only specified to one row, but I'd like it to apply to all the rows. Any ideas on how I can do that? I'm currently using =(ABS(($D:$D-$E:$E)/$E:$E)) > 0.15 but it doesn't do anything. Even from within Excel this leaves the backgrounds white. – Sugar

The formula ABS(B1-C1)/C1 > 0.15 will work for the entire range. You can try it. If you apply a conditional formula to a range you need only write it in terms of the first cell(s) in the range. Make sure to omit the $ absolute reference as well. – Xray

The column referencing format 'C : C' is also stated in the docs, but in this context, with conditional formatting its not working..for some reason. If you find the solution, please let know! – Var

@kritcoder. The C:C notation is only used for methods that deal with columns only. The conditional_format() method works on a range so you need to specify the column as C1:C1048576, as shown in the answer. – Xray