Master Excel Number Formats with Python: Apply Number Formats and Retrieve the Results

6 min readApr 29, 2024

Number formats in Excel determine how numerical data is displayed within cells. They control aspects such as decimal places, currency symbols, percentage signs, date and time representations, and more. By applying different number formats, you can tailor the appearance of your data to suit specific requirements.

In this article, we will explore how to apply a wide range of number formats to Excel cells using Python. Additionally, we will also delve into the process of hiding values in cells by setting number formats and retrieving the results or displayed values of cells that have applied number formats using Python.

Apply Number Formats to Excel Cells with Python
Apply Number Formats to Hide Values in Excel Cells with Python
Get the Displayed Value of Excel Cells that Have Applied Number Formats with Python

Python Library to Apply Number Formats in Excel

To apply number formats to Excel cells and retrieve the results with Python, we can use the Spire.XLS for Python library.

Spire.XLS for Python is an easy-to-use and feature-rich library for creating, reading, editing, and converting Excel files within Python applications. With this library, you can work with a lot of spreadsheet formats, such as XLS, XLSX, XLSB, XLSM, and ODS. Moreover, you are also able to render Excel files to other types of file formats, such as PDF, HTML, CSV, Text, Image, XML, SVG, ODS, PostScript, and XPS.

You can install Spire.XLS for Python from PyPI by running the following commands in your terminal:

pip install Spire.Xls

For more detailed information about the installation, you can check this official documentation: How to Install Spire.XLS for Python in VS Code.

Apply Number Formats to Excel Cells with Python

Spire.XLS for Python uses number format codes to display cells in different formats such as number, currency, accounting, date, percentage, and fraction.

The list below shows some common elements used in Excel number format codes:

“@”: Text placeholder. Displays text exactly as entered.
“0”: Digit placeholder. Displays a digit if available; otherwise, displays a zero.
“#”: Digit placeholder. Displays a digit if available, but does not display leading or trailing zeros.
“.”: Decimal point separator. Indicates the position of the decimal in a number.
“,”: Thousands separator. Separates groups of three digits.
“$”: Currency symbol. Represents the currency used in the number.
“%”: Percentage symbol. Multiplies the number by 100 and displays it as a percentage.
“E+” or “e+”: Exponential notation. Represents numbers in scientific notation.
“-”: Minus sign. Displays a negative number or indicates a negative value.
“[Color]”: Conditional formatting. Applies formatting based on specified conditions.
“/”: Fraction separator. Formats the number as a fraction.
“;”: Section separator. Separates different sections for positive, negative, and zero values in the number format.

These elements can be combined to create various number format codes to display numbers. For example:

“0.00”: Displays numbers with two decimal places.
“#,##0”: Displays numbers with thousands separators.
“0.00%”: Displays numbers as percentages with two decimal places.
“$#,##0.00”: Displays numbers as currency values with a dollar sign and two decimal places.
“[Green]0.00;[Red]-0.00;[Blue]0.00”: Displays positive values in green, negative values in red, and zero values in blue.

Spire.XLS for Python provides the CellRange.NumberFormat property that enables you to apply various types of number formats to individual cells or a range of cells through format codes. Here is a simple example that explains how to apply number formats to Excel cells using Python and Spire.XLS for Python:

from spire.xls import *
from spire.xls.common import *

# Instantiate a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Example.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

sheet.Range["A1"].Text = "Number Format"
sheet.Range["B1"].Text = "Example Input"
sheet.Range["C1"].Text = "Example Output"
sheet.Range["A1:C1"].Style.Font.IsBold = True
sheet.Range["A1:C1"].Style.HorizontalAlignment = HorizontalAlignType.Center

# Apply different types of number formats
sheet.Range["A2"].Text = "Number"
sheet.Range["B2"].Text = "1234.5678"
sheet.Range["C2"].NumberValue = 1234.5678
sheet.Range["C2"].NumberFormat = "0"

sheet.Range["A3"].Text = "Number"
sheet.Range["B3"].Text = "1234.5678"
sheet.Range["C3"].NumberValue = 1234.5678
sheet.Range["C3"].NumberFormat = "0.00"

sheet.Range["A4"].Text = "Number"
sheet.Range["B4"].Text = "1234.5678"
sheet.Range["C4"].NumberValue = 1234.5678
sheet.Range["C4"].NumberFormat = "#,##0.00"

# Apply currency format
sheet.Range["A5"].Text = "Currency"
sheet.Range["B5"].Text = "1234.5678"
sheet.Range["C5"].NumberValue = 1234.5678
sheet.Range["C5"].NumberFormat = "\"$\"#,##0.00"

# Apply date format
sheet.Range["A6"].Text = "Date"
sheet.Range["B6"].Text = DateTime.get_Today().ToShortDateString()
sheet.Range["C6"].DateTimeValue = DateTime.get_Today()
sheet.Range["C6"].NumberFormat = "mm/dd/yyyy"

# Apply scientific format
sheet.Range["A7"].Text = "Scientific"
sheet.Range["B7"].Text = "1234567.0005"
sheet.Range["C7"].NumberValue = 1234.5678
sheet.Range["C7"].NumberFormat = "0.00E+00"

# Apply accounting format
sheet.Range["A8"].Text = "Accounting"
# Positive value
sheet.Range["B8"].Text = "5"
sheet.Range["C8"].NumberValue = 5 
sheet.Range["A9"].Text = "Accounting"
# Negative value
sheet.Range["B9"].Text = "-5"
sheet.Range["C9"].NumberValue = -5
sheet.Range["A10"].Text = "Accounting"
# Zero
sheet.Range["B10"].Text = "0"
sheet.Range["C10"].NumberValue = 0
sheet.Range["C8:C10"].NumberFormat = "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)"

# Apply custom format
sheet.Range["A11"].Text = "Custom"
# Positive value
sheet.Range["B11"].Text = "5"
sheet.Range["C11"].NumberValue = 5 
sheet.Range["A12"].Text = "Custom"
# Negative value
sheet.Range["B12"].Text = "-5"
sheet.Range["C12"].NumberValue = -5
sheet.Range["A13"].Text = "Custom"
# Zero
sheet.Range["B13"].Text = "0"
sheet.Range["C13"].NumberValue = 0
sheet.Range["C11:C13"].NumberFormat = "[Green]0.00;[Red]-0.00;[Blue]0.00"

# Apply percentage format
sheet.Range["A14"].Text = "Percentage"
sheet.Range["B14"].Text = "0.58"
sheet.Range["C14"].NumberValue = 0.58
sheet.Range["C14"].NumberFormat = "0.00%"

# Apply fraction format
sheet.Range["A15"].Text = "Fraction"
sheet.Range["B15"].Text = "0.25"
sheet.Range["C15"].NumberValue = 0.25
sheet.Range["C15"].NumberFormat = "# ?/?"

# Apply fraction format
sheet.Range["A16"].Text = "Text"
sheet.Range["B16"].Text = "001"
sheet.Range["C16"].Value = "001"
sheet.Range["C16"].NumberFormat = "@"

# Set column widths
sheet.SetColumnWidth(1, 23)
sheet.SetColumnWidth(2, 44)
sheet.SetColumnWidth(3, 23)

# Save the result file
workbook.SaveToFile("ApplyNumberFormats.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Apply Number Formats in Excel with Python

Apply Number Formats to Hide Values in Excel Cells with Python

You can hide the zero values or the entire values in Excel cells by setting number formats. To hide zero values in cells, use the “0;-0;;@” format code. To hide all values in cells, use the “;;;” format code.

Here is a simple example that shows how to hide the zero values or the entire values in specific Excel cells using Python and Spire.XLS for Python:

from spire.xls import *
from spire.xls.common import *

# Instantiate a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample Data.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Hide the zero values in the used cell range
sheet.Range.NumberFormat = "0;-0;;@"

# Hide the entire value in cell C3
sheet.Range["C3"].NumberFormat = ";;;"

# Save the result file
workbook.SaveToFile("HideCellValues.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Hide Cell Values by Setting Number Formats in Excel with Python

Get the Displayed Value of Excel Cells that Have Applied Number Formats with Python

When integrating Excel data with other systems or applications, it may be necessary to read the displayed values as they are presented to users. By using the CellRange.DisplayedText property, you can get the displayed value of Excel cells effortlessly.

Here is a simple example that shows how to get the displayed value of an Excel cell that has applied number format using Python and Spire.XLS for Python:

from spire.xls import *
from spire.xls.common import *

# Instantiate a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("ApplyNumberFormats.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Get the displayed text of cell C2
displayedText = sheet.Range["C2"].DisplayedText
print(displayedText)

workbook.Dispose()

Get the displayed text of cells that have applied number formats in Excel with Python

Conclusion

This article explained how to apply various types of number formats to Excel cells, hide cell values by applying number formats, as well as get the results or displayed text of cells that have applied number formats using Python. We hope you find it helpful.