Microsoft Excel has always been a savior when it comes to storing large amounts of data. While Excel can be used to maintain the data, it also has many other features to ease the mathematical burden that comes along with the data. You can easily add, subtract, divide and multiply numbers in Excel.
Using Excel is quite easy if you have figured out all of its functions and formulae. However, for a total novice, it can be a daunting task to maintain large sets of data involving a lot of mathematical operations.
In this tutorial, I will help you out with one of the mathematical operations, that is, multiplication. Let us learn the different methods to multiply numbers in Microsoft Excel.
Multiplication in Excel Using Asterisk Sign
One of the easiest ways to multiply in Excel is to use the Asterisk Sign (*). It can be used in multiple situations. Using the asterisk sign you can multiply numbers in a single cell, multiple cells, you can also multiply columns and rows.
How to Multiply Numbers in a Single Cell
To multiply numbers in a single cell, you just have to follow the formula of =A*B
Here A is the first number you want to multiply and B is the second number you want to multiply.
Say I want to multiply the number 15 with 3, I will follow the following steps to do it:
- The first step would be to use the equals sign (=).
- Then write the number you want to multiply.
- Then write the asterisk sign and the other number you want to multiply. (Here I have used 15 and 3 for example)
- Press Enter and you will have your product in the cell.
Note: Do not forget to add the equals sign (=) to the cell. Without it, the formula would not work.
Also, read How to Copy Conditional Formatting in Microsoft Excel
How to Multiply Multiple Cells
Just like the multiplication of two numbers in a single cell, you can also multiply multiple cells. For this, you have to use a similar formula used in single-cell multiplication. However, remember to use cell references instead of numbers.
Here is what you have to do for multiplying multiple cells:
- Use the equals sign (=)
- Then add the cell number you want to multiply.
- After that use the asterisk sign and the other cell reference you want to multiply.
- Press Enter and you will have your result
Similarly, you can multiple multiple cells by adding the cell reference. Say if you want to multiply A1, B1, and C1 from the above screenshot you just have to use the formula of =A1*B1*C1 and you will have your result.
How to Multiply Columns
This formula becomes relatively easy when you know how to multiply two cells (I’ve explained that in the above step).
The following steps will help you multiple columns in Microsoft Excel:
- Write multiplication formula for the topmost cell only.
- You will see a small green square at the lower right-hand corner of the cell.
- All you have to do is drag the square to copy the formula down the column.
This was about multiplying one column with another having different numbers in each cell. If you want to multiply one column with another column, the second one having a constant number, you will have to use a different formula. We will discuss that a little later. Before that let us learn how to multiply two rows.
How to Multiply Rows
This one is similar to the multiplication of columns. All you have to do is instead of dragging the square vertically, you drag it horizontally to apply the formula to the rest of the row.
The following steps will help you to multiply rows in Microsoft Excel:
- Apply the multiplication formula in the first cell of the row.
- You will see a green square on the lower right side of the cell.
- Now drag the square horizontally till the end of the row and you will have your result.
How to Multiply a Column by the Same Number
I have already told you about the multiplication of different columns. However, it focused on different numbers in every cell. Now if you want to multiply a column with a constant number you have to change the formula a bit.
The following steps can be used to multiply a column with a constant number:
- Enter the constant number in a cell, say B1
- Now, write the multiplication formula for the topmost cell in a column
Let us assume we have to multiply the constant cell B1 with the numbers in column A, starting from cell A1.
So we will be using the formula =A1*$B$1
Be sure to add the $ symbol before the column letter and row number ($B$1) to make an absolute reference.
- Now drag the little square and copy the formula down the column.
Note: It is important to lock the column and row coordinates of the cell with the constant number to prevent the reference from changing when you copy the formula to other cells.
How to Multiply Using the PRODUCT Function
You can use the Product Function in Excel to multiply multiple numbers, cells, and ranges of cells.
Also, read How to Divide Numbers in Excel: A Detailed Guide
Using Product Function to Multiply Numbers in Excel
To easily multiply different numbers in Excel, you can use the Product Function. All you have to do is use the formula =PRODUCT (A, B, C, D). Here A, B, C, and D are to be replaced by the numbers you want to multiply.
Say you want to multiply numbers 10,50 and 20
You will apply the formula as =PRODUCT(10,50,20)
Using Product Function to Multiply Cells in Excel
Another way to multiply multiple cells is to use the Product Function. But instead of adding the numbers to the formula, you have to add the Cell Reference.
Say you want to multiply cells A1, A2, and A3, you can refer to the cell in the formula instead of the number.
So the formula becomes =PRODUCT(A1, A2, A3)
You will still get the same result as what you did before when using the numbers instead of a cell reference. This is just a different way to reach the same conclusion.
However, this function helps you when you want to multiply different cells with each other and also some other number.
Say you wish to multiply A1, A2, and A3. And after that, you wish to multiply their product with a different number, say 25.
You can use the formula =PRODUCT(A1, A2, A3, 25)
Using Product Function to Multiply a Range of Cells
Now, while working on Excel, you tend to use a lot of data. Unlike the examples given above, you would not have just two to three cells to multiply. So to manage the multiplication of a large number of cells you need a more convenient formula.
To multiply a range of cells, say from A1 to A10. You need to use the formula =PRODUCT(A1:A10)
Here the colon sign (:) signifies the range of cells to be multiplied.
Now, if you want to multiply a range of cells from one column with a range of cells from another column, say you want to multiply cell A1 to A10 with Cells B1 to B5, you can use the formula
You’re All Set to Multiply in Excel!
In this article, you have learned all the ways and methods to multiply numbers in Excel. You are now all set to use this feature while using Excel.
Grab your laptop and practice all these methods. Next time you have to manage data in Excel, trust me it will save you a lot of time!