3 Ways to Divide a Group of Cells by a Number in Excel

You can conduct several calculations with Excel, including multiplication, division, addition, and subtraction. It is simpler to carry out these computations for a single cell. However, when we need to perform these calculations for a group of cells, we must consider taking a different approach. As a result, this article will describe 4 straightforward methods to divide a group of cells by a number in Excel.

how to divide a group of cells by a number in excel


📁 Download Excel File

You can download the Excel file used for the demonstration from the link below.


Learn to Divide a Group of Cells by a Number in Excel Using These 3 Methods

First, let us become acquainted with our dataset, which will be used as an example throughout the article. We have the product name and total cost of the product. We also have the number of units produced. Now, we want to calculate the unit cost of each product. So, we need to divide all the products’ total cost by the units produced. Hence, we will use this dataset to demonstrate 3 simple approaches to dividing a group of cells by a number in Excel.

Dataset for calculating unit cost


Approach 1

1. Using Basic Division Formula

This method will use the basic division formula to divide a group of cells by a number in Excel. Since we must divide the group of cells by a specified number, we applied it as an absolute cell reference in the formula. This is the most straightforward way of dividing a group of cells by a number in Excel.

Let us go through the steps to apply this function.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select the cell where you want the desired result. Here, we are selecting cell D6.
  • Next, insert the formula.

=C6/$C$12

Using basic division formula to divide a group of cells by a number in excel

Here, cell C6 contains the value we want to divide, and we divide it by 10, which is in cell C12.

  • Lastly, drag the Fill handle down, and you will have the group of cells divided by a number in Excel.

Dragging down the fill handle

📕 Read More: How to Select Multiple Cells in Excel (10 Quick Methods)

Approach 2

2. Applying Paste Special

This method will apply the Paste Special feature to divide a group of cells by a number in Excel. The Paste Special feature allows users to choose different paste options, such as all, formula, value, format, etc., and operation options, such as add, subtraction, multiplication, and division. You can add, subtract, multiply, and divide using this feature while pasting the copied value.

Let us go through the steps to apply this feature.

⬇️⬇️ STEPS ⬇️⬇️

  • First, copy the range C6:C10 by pressing Ctrl+C and paste them by pressing Ctrl+V in cell D6.

Copy and pasting to divide a group of cells by a number in excel

  • Next, copy cell D12, select range D6:D10, and go to Home tab from the Clipboard group of commands, click on Paste Special.

Applying paste special feature to divide a group of cells by a number in excel

  • Then, a box named Paste Special will appear. Click on Divide from the Operation options and press OK.

Selecting Divide option from Paste special box

  • Lastly, you will have the group of cells divided by the number in Excel.

Outcome of applying paste special feature

📕 Read More: 10 Ways to Select Multiple Cells Without Mouse in Excel

Approach 3

3. Embedding VBA Code

This method will demonstrate how to divide a group of cells by number in Excel using Microsoft Visual Basic Application (VBA).

So, ensure you have the Developer tab in the ribbon before applying this code. If you do not have the Developer tab, click here to see how to enable the Developer tab on your ribbon.

Now, let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • First, go to the Developer tab and select Visual Basic from the code group.

Choosing Visual Basic from developer tab

  • Then, Microsoft Visual Basic for Application named box will appear. Go to the Insert tab and select the Module option.

Inserting module to enter the code

  • Enter the code and press F5.
Sub Divide_Group_of_Cells_By_Number()
 
Dim A As Range
Dim WA As Range
Dim i As Integer

On Error Resume Next
xTitleId = "Divide a Group of Cells by a Number"
Set WA = Application.Selection
Set WA = Application.InputBox("Range", xTitleId, WA.Address, Type:=8)
i = Application.InputBox("Divide by", xTitleId, Type:=1)

For Each A In WA
    A.Value = A.Value / i
Next

End Sub
  • Next, copy the range C6:C10 by pressing Ctrl+C and paste them by pressing Ctrl+V in cell D6.

Copying range and pasting them in unit cost column

  • Now, press Alt+F8 and select Divide_Group_of_Cells_By_Number and then Run.

Using Macro to divide a group of cells by a number in excel

  • After that, a box named Divide Group of Cells By Number will appear. Type $D$6:$D$10 as range there and press OK.

Entering range in input box

  • Then, type 10 in Divide by option and press OK.

Entering divisor in input box

  • Lastly, we will have our outcome.

Embedding VBA to divide a group of cells by a number in excel


📄 Important Notes

🖊️  While dividing always check the cell references and the values in cells to avoid #DIV/0! error.

🖊️  To apply VBA codes, ensure you have the Developer tab in the ribbon before the application.


📝 Takeaways from This Article

📌  This article demonstrated 3 approaches to divide a group of cells by a number in Excel.

📌  In the first method, we used Basic Division Formula for division in excel.

📌  After that, we applied Paste Special feature in Excel.

📌  Finally, we embedded a VBA code to divide a group of cells by a number in Excel.


Conclusion

This article has demonstrated 3 approaches to divide a group of cells by a number in Excel. All these approaches are incredibly practical and straightforward to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit Excelden.com.


Related Articles

(Visited 66 times, 1 visits today)

Tags:

Priti

Priti

Hello! Welcome to my Profile. I am Priti. I'm now working and researching Microsoft Excel, and I'll share articles on it here. I am passionate about problem-solving, and I hope these articles will help you. Please leave a remark if you have any queries, and I will do my best to find an appropriate answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo