Python & Excel – Number Format

Sharing is caring!

Last Updated on July 14, 2022 by Jay

This is a guide on Excel number format using the Python openpyxl library. The Excel program provides dozens of different number formats to fit different needs, and we can use Python to set all of them.

We are going to continue to “beautify” our previous monthly budget Excel template.

python-openpyxl-excel
python-openpyxl-excel

Library

To install the required library, simply type the following inside a command prompt window:

pip install openpyxl

Adjust Excel Number Format using Python

Since we are making a budget template, let’s add a dollar sign in front of all the numbers. For the net difference number, we are going to introduce a red color for negative numbers. Then for the exp/income ratio, we’ll format it as a percentage.

It’s simple to change the format, just set the cell.number_format attribute to whatever format we need. That’s it!

for r in range(8,20):
    ws[f'C{r}'].number_format ='"$"#,##0_);("$"#,##0)'

for r in range(7,13,2):
    ws[f'F{r}'].number_format= '"$"#,##0_);[Red]("$"#,##0)'

ws['F13'].number_format = '0.00%'

Common Excel Number Formats

The below is for your reference. You can get the same list from the Excel program’s Format Cells menu as well.

Excel format cell menu
Excel format cell menu
ParameterNumber Example
‘General’1234.56
‘0’1235
‘0.00’1234.56
‘#,##0’1,235
‘#,##0.00’1,234.56
‘#,##0_);(#,##0)’1,235
‘”$”#,##0_);(“$”#,##0)’$1,235
‘#,##0_);[Red](#,##0)’1,235 or -1,235
‘”$”#,##0_);[Red](“$”#,##0)’$1,235 or -$1,235
‘”$”#,##0.00_);(“$”#,##0.00)’$1,234.56
‘#,##0.00_);[Red](#,##0.00)’1,234.56 or -1,234.56
‘”$”#,##0.00_);[Red](“$”#,##0.00)’$1,234.56 or -$1,234.56
‘0%’123456%
‘0.00%123456.00%
‘0.00E+00’1.23E+03

Of course, there are also formats for dates. However, note this works only for the “number” data when converted into dates. In Excel, 0 means 1900-01-00, 1 means 1900-01-01, and so on. Today is 2022-03-17, the equivalent integer number is 44637.

ParameterDate Format
‘General’44637
‘mm-dd-yy’03-17-22
‘d-mmm-yy’17-Mar-22
‘d-mmm’17-Mar
‘mmm-yy’Mar-22

Additional Resources

How to Use Python to Read Excel Formula

How to Work with Excel Named Range in Python

Work with Excel Named Range in Python

Write Data to Excel using Python

Leave a Reply

Your email address will not be published. Required fields are marked *