A Beginner’s Guide to Styling Pandas DataFrame (Including Styler.To_Excel)

Python provides umpteen features to customise the visualisation of the data that is being analysed. In this article, we shall explore in great depth one such function that aids in the styling of the data in the MS Excel spreadsheet. A styler object is a high-potential tool that could be deployed for formatting the DataFrame, rendering a compelling facade that might not seem like a spreadsheet anymore. So let us get started with knowing how to bend MS Excel to our desire using the styler from Python.

Also Read: Python Data Types – Easily Explained!

Introduction to Styling

An accessor needs to be put into use for utilising the styling properties for the pandas DataFrame to its fullest potential. It also supports modifying the styler object which is primarily used to control the different ways in which a DataFrame is displayed.

Given below are the various types of styling that can be incorporated into a spreadsheet using the styler option in Python programming:

  • Highlight Minimum Values
  • Highlight Maximum Values
  • Highlight Null Values
  • Colour Table Borders & Data
  • Truncating the Decimals Displayed
  • Hide the Index Column
  • Style Writing a Spreadsheet

Highlight Minimum Values

In order to highlight the minimum values in the given spreadsheet data, the below code is deployed.


Given below is an example in which the minimum values of each column are highlighted.

Minimum Values Highlighted
Highlighting Minimum Values

Highlighting Maximum Values

Now that we have highlighted the minimum values, let us now replace the min with max in the code in order to get the maximum values of the given dataset highlighted.


Given below is an example in which the maximum values of each column are highlighted.

Maximum Values Highlighted
Highlighting Maximum Values

Highlight Null Values

With the maximum and minimum values being taken care of, one might wonder whether there is a way to visualise the locations in which null values are present in the given dataset, should there be any. This very thought leads us to the following code which highlights the null values as shown below.

Null Values Highlighted
Highlighting Null Values

Colour Table Borders & Data

The DataFrames displayed as an output using Python compilers such as the Jupyter Notebook, for instance, are generally rendered by using HTML and CSS. Therefore it seems a no-brainer to tweak the data visualisation using the set_properties function along with the styler object as demonstrated in the code given below.

df.style.set_properties(**{'border': '1.3px solid red', 'color': 'green'})
Table Border Textual Data Coloured
Colouring Table Border & Data

Truncating the Decimals Displayed

At times one might be dealing with decimals in the dataset with each entry having its own count of decimal digits. To set things in order, one can deploy the styler object along with the set_precision function to standardise the decimal places in all the entries as shown below.

All Columns With 3 Digit Decimals
Decimals Truncated in all Applicable Columns

Hide the Index Column

The very first column in a DataFrame shall be its index which works very well as a reference but not in all cases. Thus, during times of necessity, the below code is put to use to make the index column disappear.

DataFrame With Index Column Hidden
Hiding the Index Column

Style Writing a Spreadsheet

Enough of tweaking the output in compilers like the Jupyter Notebook, to make the real thing happen, choose any of the above modifications or even better, all of it and use the to.excel( ) function to embed all the selected modifications into the data in an MS Excel spreadsheet as shown below.

df.style.highlight_min().set_properties(**{'border': '1.3px solid red','color': 'green'}). set_precision(3).to_excel('File.xlsx', engine = 'openpyxl')


Now that we have reached the end of this article, hope it has elaborated on the techniques to use the styler function combined with the to_excel( ) function for editing the style of the contents within an Excel spreadsheet. Here is another article that details how to use numpy positive in Python. There are numerous other enjoyable and equally informative articles in CodeforGeek that might be of great help to those who are looking to level up in Python. 



Arulius Savio
Arulius Savio
Articles: 26