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.
Library
To install the required library, simply type the following inside a command prompt window:
pip install openpyxlAdjust 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.
| Parameter | Number 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.
| Parameter | Date 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