Round multiple excel cell values in One-Click

Round multiple Excel cell values in one click

If you often find yourself manually rounding numbers in Excel (Excel 2007 and later), this add-in will save you time and effort. The “Round Multiple Cell Values in One Click” add-in allows you to round multiple excel cell values to a specified number of decimal places instantly. Whether you’re working with formulas or plain values, this tool works seamlessly to apply the rounding function across a range of cells. This add in is created with vba and excel round formula i.e. [=round(value,decimal)]

Main Highlights of this One Click ROUND add in:

Rounding Numbers in Formulas: If a cell contains a formula, the add-in will check if it already has the ROUND function. If not, it will automatically wrap the existing formula with the ROUND function to round it to your specified decimal places.
Handling Plain Values: For cells with numeric values, the add-in will check if the value is not a whole number and will replace the value with a rounded result, preserving the format as a ROUND formula.
Dynamic Input: The add-in prompts you to specify the number of decimal places you wish to round to. You can input any positive number, and the add-in will handle the rest.
User-Friendly: Simply select the range of cells you want to apply the rounding to, and click a button. The add-in will round all values, whether they’re the results of formulas or plain numbers, to your preferred decimal precision.

How It Works:

Step 1: Select the range of cells you want to round.
Step 2: Click on the “Round Multiple Cell Values in One Click” button in your toolbar.
Step 3: Enter the number of decimal places you wish to round to when prompted.
Step 4: The add-in will automatically apply the ROUND function [=round(value, decimal)] to all numbers and formulas in the selected range, rounding them as per your input.

Excel VBA code behind the One Click Round Add in:

To have full transparency and assist users in understanding the functionality of this free Excel add-in, we are making the VBA code that was used to develop it available. Get the code here:

Sub ApplyRoundFormula()
Dim cell As Range
Dim decimalPlaces As Integer
Dim userInput As String
Dim cellFormula As String
' Prompt user for decimal places
userInput = InputBox("Enter the number of decimal places to round to. E.g., enter 0 for rounding to the nearest whole number, 1 for rounding to one decimal place, etc.", "Round Formula", 2)
' Validate user input: Ensure it's a valid numeric value
If userInput = "" Or Not IsNumeric(userInput) Then
MsgBox "Invalid input. Please enter a valid number."
Exit Sub
End If
decimalPlaces = CInt(userInput)
' Disable screen updating and automatic calculation for performance
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error GoTo ErrorHandler ' Error handling

' Apply ROUND formula to selected cells
For Each cell In Selection
' Skip empty cells, non-numeric values, or error values
If Not IsEmpty(cell.value) And IsNumeric(cell.value) And Not IsError(cell.value) Then
' Skip array formulas
If cell.HasArray Then
' Skip array formulas and continue with the next cell
Else
' If the cell already contains a ROUND formula, do nothing
If InStr(1, cell.Formula, "ROUND") > 0 Then
' Do nothing if ROUND is already applied
ElseIf InStr(1, cell.Formula, "=") > 0 Then
' Check if the cell contains a formula (excluding ROUND)
cellFormula = cell.Formula
' Wrap the existing formula in ROUND, skip rounding for whole numbers
If Not IsWholeNumber(cell.value) Then
' Apply ROUND to the existing formula
cell.Formula = "=ROUND(" & Mid(cellFormula, 2) & "," & decimalPlaces & ")"
End If
Else
' If it's a plain value and not a whole number, apply ROUND directly to the value
If Not IsWholeNumber(cell.value) Then
cell.Formula = "=ROUND(" & cell.value & "," & decimalPlaces & ")"
End If
End If
End If
End If
Next cell
' Restore screen updating and automatic calculation
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Exit Sub
ErrorHandler:
MsgBox "An error occurred: " & Err.Description
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
' Function to check if the number is a whole number
Function IsWholeNumber(value As Variant) As Boolean
If IsNumeric(value) Then
IsWholeNumber = (value = Int(value))
Else
IsWholeNumber = False
End If
End Function

Benefits of One Click Round Add in:

Efficiency: Instead of manually editing each cell or formula, this add-in rounds all cells in a selected range in one go.
Accuracy: Ensure consistent rounding of numeric values across your sheet.
Flexibility: The add-in works with both numbers and formulas, ensuring a wide range of applications.

Useful for:

Financial reports that need consistent rounding across many cells.
Data analysis tasks where precision is key but a uniform format is required.
Anyone working with large datasets that need to be rounded quickly and accurately.

Example use cases of One Click Round Addin:

ROUND(number, num_digits)

number: The number you want to round.
num_digits: The number of digits to which you want to round the number.

1. ROUND(A1, 0)

This rounds the value in cell A1 to 0 decimal places (i.e., to the nearest whole number).

Example: If A1 contains 5.67, ROUND(A1, 0) will return 6.
Example: If A1 contains 3.2, ROUND(A1, 0) will return 3.

2. ROUND(A1, 1)

This rounds the value in cell A1 to 1 decimal place.

Example: If A1 contains 5.67, ROUND(A1, 1) will return 5.7.
Example: If A1 contains 3.14, ROUND(A1, 1) will return 3.1.

3. ROUND(A1, 2)

This rounds the value in cell A1 to 2 decimal places.

Example: If A1 contains 5.6789, ROUND(A1, 2) will return 5.68.
Example: If A1 contains 3.14159, ROUND(A1, 2) will return 3.14.

4. ROUND(A1, -1)

When you use a negative number for num_digits, it rounds to the left of the decimal point.

Example: If A1 contains 56, ROUND(A1, -1) will return 60.
Example: If A1 contains 53, ROUND(A1, -1) will return 50.

5. ROUND(A1, -2)

Example: If A1 contains 234, ROUND(A1, -2) will return 200.
Example: If A1 contains 276, ROUND(A1, -2) will return 300.

Key Points to remember:

Positive num_digits: Rounds to the right of the decimal point (i.e., to a specific number of decimal places).
Negative num_digits: Rounds to the left of the decimal point (i.e., to the nearest multiple of 10, 100, etc.).
Zero num_digits: Rounds to the nearest whole number.

In short:

ROUND(A1, 0) rounds to the nearest integer.
ROUND(A1, 1) rounds to 1 decimal place.
ROUND(A1, -1) rounds to the nearest multiple of 10.

Download the Round Multiple Cell Values in One Click excel add in here⏬:

Download Add-in