While performing some calculations in Microsoft Excel, we need to divide a group of cells by a number. We can divide a cell or the group of cells easily in Excel. Today’s task is to divide a group of cells by a number. In this article, we’ll learn three quick and suitable ways to divide a group of cells by a number in Excel effectively with appropriate illustrations.
How to Divide a Group of Cells by a Number in Excel: 3 Suitable Ways
Let’s say, we have a dataset that contains information about several sales representatives of the Armani group. The type of products and the revenue earned by the sales representatives are given in columns C, and D respectively. We will divide revenue earned by a number 12. We can easily do that by using the paste special command, the SUM function, and VBA macros also. Here’s an overview of the dataset for today’s task.
1. Use the Paste Special Command to Divide a Group of Cells by a Number in Excel
We will use the Paste Special command to divide a group of cells by a number in Excel. This is the easiest and time-saving way. Here, we’ll divide the revenue earned by the sales representatives by a number 12. Let’s follow the steps below to learn!
- First of all, select the number 12 and copy it by using the keyboard shortcuts Ctrl + C. Then select the cells array which is divided by 12.
- After that, from your Home tab, go to,
Home → Clipboard → Paste → Paste Special
- Hence, a Paste Special dialog box will appear in front of you. From that window, firstly, select the Divide option from the Operation At last, press OK.
- After completing the above process, you will be able to divide the group of cells titled revenue earned by a number 12 that has been given in the below screenshot.
2. Apply the SUM Function to Divide a Group of Cells by a Number in Excel
Now, we will apply the SUM function to divide a group of cells by a number. This is the easiest way to divide a group of cells by a number. Firstly, we will sup up the group of cells D5 to D14 by using the SUM function. Then we divide the output of the SUM function by 25. Let’s follow the instructions below to learn!
- First, select cell D16 to write down the SUM function. The SUM function is,
- Hence, press Enter on your keyboard and you will get $6,440,830 as the return of the SUM function.
- Again, select cell D18 to divide the value of cell D16. Now type the below formula in that cell.
- Further, again, simply press Enter on your keyboard and you will get $257,633.20 as the return of the formula.
- How to Delete a Cell in Excel (4 Easy Ways)
- Excel if One Cell Equals another then Return another Cell
- Selecting Non-Adjacent or Non-Contiguous Cells in Excel (5 Simple Methods)
- Data clean-up techniques in Excel: Filling blank cells
- What is cell in Excel definition
3. Run a VBA Code to Divide a Group of Cells by a Number in Excel
In this method, we will divide a group of cells by a number by applying the VBA Code. Applying the VBA code to divide a group of cells by a number is the easiest and the time-saving also. Let’s follow the instructions below to learn!
- First of all, from your Developer tab, go to,
Developer → Visual Basic
- After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Divide a Group of cells by a Number 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
- Hence, the Divide a Group of cells by a Number module pops up. In the Divide a Group of cells by a Number module, write down the below VBA
Sub Divide_Group_of_Cells() 'UpdatebyRasel_0043 Dim R As Range Dim WR As Range Dim i As Integer On Error Resume Next xTitleId = "Divide a Group of Cells" Set WR = Application.Selection Set WR = Application.InputBox("Range", xTitleId, WR.Address, Type:=8) i = Application.InputBox("Division num", xTitleId, Type:=1) For Each R In WR R.Value = R.Value / i Next End Sub
- After that, run the VBA To do that, go to,
Run → Run Sub/UserForm
- After running the code, a dialog box named Divide a Group of cells will appear instantly in front of you. From the Divide a Group of cells dialog box, type $D$5:$D$14 in the Range Then press OK.
- By pressing OK, a new dialog box pops up and in that dialog box, write down 20 in the Division num. At last, press OK.
- After completing the above process, you will be able to divide a group of cells by a number 20 which has been given below screenshot.
Things to Remember
👉 #DIV/0 error occurs when the denominator is 0 or the reference of the cell is not valid.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
I hope all of the suitable methods mentioned above to divide a group of cells by a number will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
- How to Move All Cells Down in Excel (5 Methods)
- How to Click One Cell and Highlight Another in Excel (2 Methods)
- [Fix]: Arrow Keys Not Moving Cells in Excel (2 Methods)
- How to Lock Cells in Excel When Scrolling (2 Easy Ways)
- How to Select Cells in Excel Using Keyboard (9 Ways)
- How to Shift Cells Down in Excel without Changing Formula (4 Methods)