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.
📁 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.
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.
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.
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.
- Next, copy cell D12, select range D6:D10, and go to Home tab from the Clipboard group of commands, click on Paste Special.
- Then, a box named Paste Special will appear. Click on Divide from the Operation options and press OK.
- Lastly, you will have the group of cells divided by the number in Excel.
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.
- Then, Microsoft Visual Basic for Application named box will appear. Go to the Insert tab and select the Module option.
- Enter the code and press F5.
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, copy the range C6:C10 by pressing Ctrl+C and paste them by pressing Ctrl+V in cell D6.
- Now, press Alt+F8 and select Divide_Group_of_Cells_By_Number and then Run.
- After that, a box named Divide Group of Cells By Number will appear. Type $D$6:$D$10 as range there and press OK.
- Then, type 10 in Divide by option and press OK.
- Lastly, we will have our outcome.
📄 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.
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.
- 7 Ways to Check If Multiple Cells Are Equal in Excel
- 3 Ways to Clear Contents Without Deleting Formulas in Excel
- 7 Ways to Assign Value If a Cell Contains Word in Excel