We use numbers in Excel in different cases. In those cases, we may get numbers with **long decimals**. But in most real cases, those long decimals are not that significant. We often curtail those numbers to make those numbers round to the nearest number. To become more accessible and easier to understand. In this article, we will discuss how you can round decimal numbers to the nearest multiple of 5 in Excel.

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

## 5 Suitable Ways to Round Numbers to the Nearest Multiple of 5 in Excel

Let us have a data set like this. We have the record of the average marks of the students of a school named **Sunflower Kindergarten**. Now the **Principal **of the school wants to round each mark to its **nearest multiple of 5**. To do that, we will apply the **ROUND**, **MROUND**, **ROUNDUP**, **CEILING**, **ROUNDDOWN**, **FLOOR** functions, and the **VBA **code as well.

### 1. Apply ROUND Function to Round Numbers to the Nearest Multiple of 5

This section will apply **the ROUND function** to round to the nearest 5 in Excel. We want to round the average marks (column **C**) to the nearest multiple of 5. Let’s follow the instructions below to learn!

**Steps:**

- First of all, select cell
**D5**and write down the below**ROUND**function in that cell. The functions are,

`=ROUND(C5/5,0)*5`

- Hence, simply press
**Enter**on your keyboard. As a result, you will get the nearest multiples of 5 which is the return of**the****ROUND****function**. The return is**80.**

- Further,
**AutoFill**the**ROUND**function to the rest of the cells in column**D.**

**Read More: How to Round Numbers in Excel Without Formula (3 Quick Ways)**

### 2. Use MROUND Function to Round Numbers to the Nearest 5

You can use **the MROUND function** to round the numbers to their nearest multiples of 5. This is the simplest method to achieve round-offs to certain multiples of any number. Let’s follow the instructions below to learn!

**Steps:**

- Firstly, select cell
**D5**and write down the below**MROUND**function in that cell. The functions are,

`=MROUND(C5,5)`

- Hence, simply press
**Enter**on your keyboard. As a result, you will get the nearest multiples of 5 which is the return of**the MROUND****function**. The return is**80.**

- Further,
**AutoFill**the**MROUND**function to the rest of the cells in column**D.**

**Read More:** **How to Round to Nearest 100 in Excel (6 Quickest Ways)**

### 3. Round Numbers to the Nearest Upper Multiple of 5

Now imagine a different scenario. The **Principal **wants to round each average mark to the nearest multiple of 5, but the upper multiple. For instance, if the mark is **91.75**, he wants it to round to **95**, not **90**. To do that, we will use the **ROUNDUP **and **CEILING **functions.

#### 3.1 Insert ROUNDUP Function

Now, we will apply **the ROUNDUP function** to round the nearest upper multiple. Let’s follow the instructions below to learn!

**Steps:**

- First of all, select cell
**D5**and write down the below**ROUNDUP**function in that cell. The functions are,

`=ROUNDUP(C5/5,0)*5`

- Hence, simply press
**Enter**on your keyboard. As a result, you will get the nearest multiples of 5 which is the return of**the ROUNDUP****function**. The return is**80.**

- Further,
**AutoFill**the**ROUNDUP**function to the rest of the cells in column**D.**

#### 3.2 Use CEILING Function

In this sub-method, we will apply **the CEILING function** to round the nearest upper multiple. Let’s follow the instructions below to learn!

**Steps:**

- Firstly, select cell
**D5**and write down the below**CEILING**function in that cell. The functions are,

`=CEILING(C5,5)`

- Hence, simply press
**Enter**on your keyboard. As a result, you will get the nearest multiples of 5 which is the return of**the CEILING function**. The return is**80.**

- Further,
**AutoFill**the**CEILING**function to the rest of the cells in column**D.**

**Read More:** **How to Round Numbers to Nearest 10000 in Excel (5 Easy Ways)**

**Similar Readings**

**How to Use Number Format Code in Excel (13 Ways)****[Solved] Excel Number Stored As Text****How to Convert Number to Percentage in Excel (3 Quick Ways)****Custom Number Format: Millions with One Decimal in Excel (6 Ways)****How to Apply Engineering Number Format in Excel (2 Easy Ways)**

### 4. Round Numbers to the Nearest Lower Multiple of 5

Now imagine yet another different scenario. The **Principal **wants to round each average mark to the nearest multiple of 5, but the lower multiple. For instance, if the mark is **84.75**, he wants it to round to **80**, not **85**. To do that, we will use the **ROUNDDOWN **and **FLOOR **functions.

#### 4.1 Utilize ROUNDDOWN Function

Now, we will apply **the ROUNDDOWN function** to round the nearest multiple. Let’s follow the instructions below to learn!

**Steps:**

- Firstly, select cell
**D5**and write down the below**ROUNDDOWN**function in that cell. The functions are,

`=ROUNDDOWN(C5/5,0)*5`

- Hence, simply press
**Enter**on your keyboard. As a result, you will get the nearest multiples of 5 which is the return of**the ROUNDDOWN****function**. The return is**75.**

- Further,
**AutoFill**the**ROUNDDOWN**function to the rest of the cells in column**D.**

** **

#### 4.2 Apply FLOOR Function

In this sub-method, we will apply **the FLOOR function** to round the nearest upper multiple. Let’s follow the instructions below to learn!

**Steps:**

- Firstly, select cell
**D5**and write down the below**FLOOR**function in that cell. The functions are,

`=FLOOR(C5,5)`

- Hence, simply press
**Enter**on your keyboard. As a result, you will get the nearest multiples of 5 which is the return of**the FLOOR function**. The return is**75.**

- Further,
**AutoFill**the**FLOOR**function to the rest of the cells in column**D.**

**Read More:** **How to Round to Nearest 1000 in Excel (7 Easy Methods)**

### 5. Run VBA Code to Round Numbers to the Nearest 5

Now I’ll show you how to round to the nearest 5 in** Excel **by using a simple **VBA **code. It’s very helpful for some particular moments. From our dataset, we will get round to the nearest 5 in** Excel.** Let’s follow the instructions below to learn!

**Step 1:**

- First of all, open a Module, to do that, firstly, from your
**Developer**tab, go to,

**Developer → Visual Basic**

- After clicking on the
**Visual Basic**ribbon, a window named**Microsoft Visual Basic for Applications – Round to Nearest 5**will instantly appear in front of you. From that window, we will insert a module for applying our**VBA code**. To do that, go to,

**Insert → Module**

**Step 2:**

- Hence, the
**Round to Nearest 5**module pops up. In the**Round to Nearest 5**module, write down the below**VBA**

```
Sub Round_to_Upper_Nearest_5()
SheetName = "VBA"
DataSet = "C5:C13"
Output = "D5:D13"
Set Input_Range = Worksheets(SheetName).Range(DataSet)
Set Output_Range = Worksheets(SheetName).Range(Output)
For i = 1 To Input_Range.Rows.Count
For j = 1 To Input_Range.Columns.Count
Number = Input_Range.Cells(i, j)
If Int(Number / 5) = (Number / 5) Then
Nearest_5 = Number
Else
k = 0
While k < Number
k = k + 5
Wend
Nearest_5 = k
End If
Output_Range.Cells(i, j) = Nearest_5
Next j
Next i
End Sub
```

- Hence, run the
**VBA**To do that, go to,

**Run → Run Sub/UserForm**

- After running the
**VBA Code**, go back to your worksheet and you will be able to**Round to Nearest 5**which has been given in the below screenshot.

**Read More: How to Format Number with VBA in Excel (3 Methods)**

## Round Numbers to the Nearest Whole Number in Excel

**The ROUND function** is an effective function to **round numbers** down to the nearest integer. In this function, we need to enter the number of digits to which our number argument will be rounded. The number will be rounded to the nearest integer if the value is 0. Have a look at the below screenshot to understand the nearest whole number in Excel.

**Read More: How to Custom Number Format in Excel with Multiple Conditions**

## Bottom Line

👉 You can pop up **Microsoft Visual Basic for Applications **window by pressing **Alt + F11** **simultaneously**.

👉 If a **Developer **tab is not visible in your ribbon, you can make it visible. To do that, go to,

**File → Option → Customize Ribbon**

👉 **#N/A!** the error arises when the formula or a function in the formula fails to find the referenced data.

👉 **#DIV/0!** the error happens when a value is divided by **zero(0)** or the cell reference is blank.

## Conclusion

Using the above methods, we can round any number to a multiple of 5 quite sophistically. Do you know any other methods? Or are you facing any problems applying our methods? Let us know in the comment section.

## Related Articles

**Apply Accounting Number Format in Excel (4 Useful Methods)****Keep 0 Before a Phone Number in Excel (6 Methods)****Custom Cell Format Number with Text in Excel (4 Ways)****How to Format a Number in Thousands K and Millions M in Excel (4 Ways)****Put Parentheses for Negative Numbers in Excel****How to Use Short Date Number Format in Excel**