title: Pandas to Excel conditional formatting whole column
body: <p>I want to write a Pandas dataframe into Excel with formatting. For this I'm using <code>xlsxwriter</code>. My question is twofold:</p>
  
  <ul>
  <li><p>First, how can I apply conditional formatting to a whole column? In the <a href="http://xlsxwriter.readthedocs.io/example_conditional_format.html" rel="noreferrer">examples</a> they use specific cell-range formatting (eg. <code>worksheet1.conditional_format('B3:K12', ...)</code>), is there a way I can reference a whole column?</p></li>
  <li><p>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? </p></li>
  </ul>
  
  <p>I'm currently trying the below code, which doesn't work (I don't know how to reference columns).  </p>
  
  <pre><code>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) &gt; 15',
                                          'format':   format_missed})
  writer.save() 
  </code></pre>
  
  <p>Also, I'm running Excel on a Mac, not sure if it makes any difference, but I've noticed where the Windows version uses <code>,</code>, the Mac version needs <code>;</code> for formulas. Don't know how this affects <code>xlsxwriter</code>.</p>