Excel to Python: ROUND Function - A Complete Guide | Mito
Home Icon






How to Use Excel's ROUND Function in Pandas

Excel's ROUND function adjusts a number to a specified number of digits. Often used in finance, for example, for format dollar values with two decimal places.

This page explains how to implement Excel's ROUND function in Python using pandas.

Mito is an open source library that lets you write Excel formulas in Python. Either write the formula directly in Python or use the ROUND formula in the Mito Spreadsheet and generate the equivalent Python code automatically.

Mito's ROUND function works exactly like it does in Excel. That means you don't need worry about managing data types, handling errors, or the edge case differences between Excel and Python formulas.

Install Mito to start using Excel formulas in Python.

# Import the mitosheet Excel functions
from mitosheet.public.v3 import *;

# Use Mito's ROUND function
# Note: The Mito ROUND formula is implemented to replicate
# Excel's behavior, so we don't need to create edge cases for
# handling .5 rounding differently.
df['A'] = ROUND(df['A'], 2)

Here are some common ways of using the round function in Python.

To round to the nearest whole number, you can use the the build in `round` function in Python.

# Round number to nearest whole number

Similar to how =ROUND(A1, 2) rounds the value in cell A1 to 2 decimal places in Excel, in pandas, you use the `round` function.

For instance, to round all values in column 'A' to 2 decimal places, you can use the following code snippet.

However, its important to note that pandas' `round` function rounds to the nearest even number when the last significant digit is 5. This is often referred to as 'bankers' rounding'. This is different from Excel! To round using the same technique as Excel, see the next section.

# Round column 'A' to 2 decimal places
df['A'] = df['A'].round(2)

Excel and Python have different default rounding behavior. In Excel, 2.5 and 3.5 both round to 3. In Python, 2.5 rounds to 2, and 3.5 rounds to 4.

This is because Excel uses what most consider as normal rounding (.5 always rounds up), whereas Python uses 'bankers' rounding' (round to the nearest even number).

To implement Excel's rounding behavior in Python, you can use the following code:

import decimal

# Define a fucntion excel_round that rounds like Excel
# by setting the rounding method to ROUND_HALF_UP
def excel_round(number, digits):
   # Similar to Excel, if the number is missing return 0
   if number is None or pd.isna(number):
        return 0

   context = decimal.getcontext()
   context.rounding = decimal.ROUND_HALF_UP
   number = decimal.Decimal(number)
   rounded_number = round(number, digits)
   return float(rounded_number)

# Round column 'A' to 0 decimal places
df['A'] = df['A'].apply(lambda x: excel_round(x, 0))

In Excel, you might use the =ROUNDUP(A1, 0) to round a value up. In pandas, for rounding up, you can use the `numpy.ceil` function.

# Round column A up to the nearest integer
df['A'] = df['A'].apply(np.ceil)

To round values down in Excel, you might use =ROUNDDOWN(A1, 0). In pandas, you can achieve rounding down with the `numpy.floor` function.

# Round column A down to the nearest integer
df['A'] = df['A'].apply(np.floor)

When dealing with financial data in Excel, it is easy to format numbers as currency and still apply rounding. For example, you can use =ROUND(A1, 2) to round a number to 2 decimal places, and then apply the currency format to the cell.

In Pandas, however, you might have values that look like $10.99, that are actually stored as strings. In this case, you can use the `astype` method to convert the column to a numeric type, and then use the `round` method to round the values.

# Remove the $ sign from the values in column 'A'
df['A'] = df['A'].str.replace('$', '')

# Convert column 'A' to a numeric dtype
df['A'] = df['A'].astype(float)

# Round column 'A' to 0 decimal places
df['A'] = df['A'].round(0)

# Add the $ sign back to the values in column 'A'
df['A'] = '$' + df['A'].astype(str)

Rounding numbers in pandas might seem straightforward, but it has certain nuances that can trip up those new to Python and pandas, especially if you're expecting pandas rounding to behave exactly like Excel. Here are some pitfalls to watch out for:

In pandas, when using `.round(0)` on a DataFrame or series, the result remains as float, unlike in some other environments where rounding to 0 decimals might convert the result to an integer.

To ensure the result is an integer type in pandas, you need to use the `astype` function after rounding.

# Round column A to 0 decimal places and convert to int
df['A'] = df['A'].round(0).astype(int)

By default, pandas uses the 'round half to even' method, often referred to as 'bankers' rounding'. This might differ from the expected behavior in Excel which rounds 0.5 always up.

For instance, 2.5 and 3.5 both round to 3 in pandas by default. To mirror Excel's behavior in pandas, you need a custom rounding function.

The ROUND function in Excel adjusts a number to the specified number of digits, rounding up when the last significant digit is 5 or greater, and rounding down when the last significant digit is less than 5.

=ROUND(number, num_digits)

ROUND Excel Syntax

ParameterDescriptionData Type
numberThe number you want to round.number
num_digitsThe number of digits to which you want to round the number.number


=ROUND(2.689, 1)Rounds 2.689 to one decimal place.2.7
=ROUND(2.689, 0)Rounds 2.689 to the nearest whole number.3
=ROUND(3.5, 0)Rounds 3.5 to the nearest whole number.4

Don't re-invent the wheel. Use Excel formulas in Python.

Install Mito

Don't want to re-implement Excel's functionality in Python?

Automate analysis with Mito