Multiplication in Excel
Multiplication plays a vital role in Mathematics. Usually, the calculations are performed manually and automatically. Manual calculations take time and sometimes leads to error, and it is a challenging process for significant input. Automatic calculations performed on computers, laptops, calculators etc., help the work be done efficiently and quickly. Excel is widely used for calculations in sectors like banking, finance etc. The calculations are done quickly, where Excel provides default formulas for the respective work. For example, for multiplication purposes, Excel provides a PRODUCT function and a ‘*’ symbol for multiplying the values.
Let’s have a look what are the formulas and functions present in Excel for multiplication
How to perform Multiplication in Excel?
The formulas in Excel is always begins with ‘=’ sign.
Example 1: How to multiply the column of numbers by a constant given number?
Step 1: Enter the data in the cell from A1:A5
Step 2: Select a cell B1 and enter the desired constant number. Here the constant number is 5.
Step 3: Select cell C1 and enter the formula as = A1*$B$2. The result will be displayed in the cell C1.
Step 4: Drag the formula down towards the cell C5. The result will be displayed in the cell from C1 and C5. When the formula is dragged down ,the absolute reference ($B$1) remain the same ,while the relative reference called A1 changes according to the formula like A2,A3,A4,A5 etc..
Example 2: How to multiply the data in the cells using different numbers?
Sometimes, the data in the cells are multiplied using different numbers.
Step 1: Enter the data in the respective column.
Step 2: Select the cell B1 and enter the formula as =A1*A2
Step 3: The result will be displayed which is the multiplication of the data present in A1 and A2.
Step 4: Select the cell B2 and enter the formula as = PRODUCT(A1:A5)
Here in this method the PRODUCT function is used to multiply the whole columns of data within itself.
The result will be displayed as 375000 which is the product of (5*10*15*20*25)
Example 3: In this method, the data are entered directly in the cell along with the formula.
Step 1: Select the cell and type the formula along with the data like, =5*5*7.
Step 2: The result will be displayed in the respective cell as shown below.
From the above method, the result is displayed as 200 in cell A1.
Example 4: How to perform the multiplication using cell name for a set of column?
In this method the values present in the set of columns is multiplied using its cell name.
Step 1: Enter the data in the respective column namely A1:A5.
Step 2: Select the cell B1 and enter the formula as =A1*A2*A3*A4*A5.
Step 3: The result will be displayed in the cell B1 as 120.
Example 5: How to multiply more than one column using PRODUCT function?
In this method let’s see how to multiply the data which is present in more than one column using Excel PRODUCT function.
Step 1: Enter the data in the respective column namely A1:A5, B1:B3, C1.
Step 2: Select a new cell where the result wants to display and type the formula as =PRODUCT (A1:A5, B1:B3, C1, 3)
The result will be displayed as 34462668240 which is the product of A1*A2*A3*A4*A5*B1*B2*B3*C1*3.Here ‘3’ is the choice of number to multiplied with the data.
Example 6: How to multiply two columns together?
To multiply the data present in two columns following steps to be followed.
Step 1: Enter the data in two columns respectively A1:A5 and B1:B5.
Step 2: Select the new cell namely C1 where the result wants to display and type the formula A1*B1.
Step 3: The result will be displayed in the respective cell. Finally click on the lower right corner of the cell C1 and drag towards downwards C5. The result will be displayed in the remaining cells.
Example 7: How to multiply the numbers in the column without using formulas?
Step 1: Enter the data in the column namely A1:A5 and copy the data by pressing Ctrl+ C.
Step 2: Right click and chose the option “Paste Special” where a dialog box will appear in that choose Paste “All” option and choose the operation as “Multiply”.
Step 3: Click OK. It will display the multiplication of data present in the column as a result.
As shown in the above image the result will be displayed as 1,4,9,16,25 which is the multiplication of (1*1, 2*2, 3*3, 4*4, 5*5)
Example 8: How to multiply rows in Excel?
To multiply rows in Excel, following steps are followed.
Step 1: Enter the rows of data in Excel from A1:E2
Step 2: Select the cell A3 and enter the formula as = A1*A2.
Step 3: The result will be displayed in the cell A3. Drag the formula from A3 to E3. The result will be displayed in the remaining cell .While multiplying the columns the relative cell references in the formula change like A1, B1, C1, D1 and E1 based on rows and columns.
From the above spreadsheet, the values are displayed based on multiplying the rows A1:E2.
Example 9: How to multiply by Percentage in Excel?
To multiply the percentage data in Excel, steps to be followed are,
Step 1: Enter the data in the spreadsheet. Choose the desired percentage number and select a new cell to insert the formula.
Step 2: For example, here the data value is entered as 30 and the percentage value is 5%.
Step 3: Enter the formula as = 30*10%. The result will be display in the particular cell. The percentage and data value can be changed according to the user’s choice of data.
From the above spreadsheet the result in the column B is calculated by multiplying the value in A1 with certain percentage.
Example 10: How to multiply and sum the values in Excel?
Sometimes the calculation is based on multiplying two rows or columns and then adding the two resultant values. To implement this function SUMPRODUCT function is used. It is used to multiply and add the values.
For example, if the table contains the data of price of the product and quality of the product, the calculation is to multiply the price and quality of the product and sum the data. Automatic calculation is performed easily for this calculation. In Excel, the default function called SUMPRODUCT is used to calculate the values.
To use the SUMPRODUCT function steps to be followed are,
Step 1: Enter the data in the spreadsheet such as Name of the Product, quantity and cost.
Step 2: Select the cell where the result want to display and enter the formula as =SUMPRODUCT (B2:B5,C2:C5) which is the result of total sales.
From the above spreadsheet, the sum value of quantity and cost is multiplied which displays the result as 73115.
Summary
From the above tutorial, the various method and functions for multiplying two data is briefly explained.