Multiplication in Excel and All Its Formulas & Functions - Compute Expert

Multiplication in Excel and All Its Formulas & Functions


Home >> Excel Tutorials from Compute Expert >> Excel Calculations >> Multiplication in Excel and All Its Formulas & Functions





In this tutorial, we will discuss various ways to do multiplication in excel. Besides how to write the formula manually, we will also talk about the use of PRODUCT and paste special to multiply.

If you haven’t understood how to do this calculation in excel, then you have come to the right place! After finish learning all the materials here, you should be able to do excel multiplication easily.

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more







How to Multiply Numbers in Excel

If you want to learn multiplication in excel, then the first thing you should learn is the way to multiply numbers.

The process to do numbers multiplication in excel itself is pretty easy. You just need to type a standard multiplication formula in your cell with star signs (*) as the multiplication symbol. Of course, you should type an equal sign ( = ) first as the symbol of formula writing in excel.

In general, here is the standard writing form of a multiplication formula in excel for numbers.

=number_1 * number_2


If there are more than two numbers you need to multiply, then add more star signs and numbers in your typing. The numbers in the formula can be typed directly or inputted through cell coordinates where the numbers are.

To make it clearer about numbers multiplication formula writing in excel, see the implementation example below.

Multiplication in Excel and All Its Formulas & Functions - Screenshot of the Example for Numbers Multiplication in Excel

As seen in the dollar multiplication example above, we can get a multiplication result using the writing form explained previously. We can see in the example’s formula bar, how to write a multiplication formula for all the numbers in the example.

Type an equal sign and input all the numbers you want to multiply. Don’t forget to add * signs between the numbers. After finish writing the formula, press enter. You will then get the multiplication result you need!



How to Multiply Percentages in Excel

The way to multiply percentages in excel is similar to how to multiply numbers. It is just, you need to make sure your percentages are in percentages, or at least in its decimal equivalent form. This is so you can get the correct result from your calculation.

Generally, here is the writing form of percentages multiplication formula in excel.

=number * percentage


Almost the same as numbers multiplication, right? If your percentage number already has a % sign behind, then just write the formula like the one above. Or if using its decimal equivalent, then it should have the same value as the percentage number value divided by 100 (for example, 80% is equivalent to the 0.8 decimal, the result of 80 divided by 100).



The implementation example of the percentages multiplication formula in excel and its result is as follows.

Multiplication in Excel and All Its Formulas & Functions - Screenshot of the Example for Percentages Multiplication in Excel

Look at the example’s formula bar. The writing pattern is the same as numbers multiplication, right? Input the number to multiply, the * sign, and the percentage in one writing and enter. You will get the percentage multiplication result after that.



How to Multiply Columns in Excel

What if the numbers you want to multiply are spread per column and you want to multiply them fast?

Don’t worry because that means you just need to copy your multiplication formula after you write one. This is useful if there are many numbers in your columns and you need to multiply the numbers per row.

The way to write a formula in your column is the same as writing a normal numbers multiplication formula in excel.

=number_1 * number_2


You just need to add more numbers and star signs if you want to multiply more than two numbers.

As an example of the columns multiplication, see the following data.

Multiplication in Excel and All Its Formulas & Functions - Screenshot of the Data for the Example of Columns Multiplication in Excel

In the example, we want to multiply per row all the numbers in column 1 to 3. How to do it? First, we type our multiplication formula in the first row of the column like this.

Multiplication in Excel and All Its Formulas & Functions - Screenshot of the Formula for the Example of Columns Multiplication in Excel

Then, we move our pointer to the bottom right of the cell cursor until it changes into a + sign like this.

Multiplication in Excel and All Its Formulas & Functions - Screenshot of the + Sign Example to Copy the Columns Multiplication Formula

Next, we just click and drag our pointer until the bottom of the row we want to multiply. Let go of the drag and it is done!

Multiplication in Excel and All Its Formulas & Functions - Screenshot of the Result for the Example of Columns Multiplication in Excel

You have got the multiplication result of each row for all the columns’ numbers.



Multiplication in Excel With Constants

What if you have constants for all the numbers you want to multiply in excel? That means there is one particular number you want to multiply with all your other numbers. Do you need to write separate multiplication formulas for each of the multiplications?

You don’t have to. You can make one multiplication formula and copy it by making your constant cell as an absolute reference first. Or if not, you can also type directly the constant number in your multiplication formula so it doesn’t change when copied (not using a cell coordinate).

If you want to use an absolute reference for your constant cell, then you need to add $ signs. Add them in front of your constant cell coordinate’s column letter and row number. To add them, you can type them directly or press F4 (Command + T in Mac) once when your cursor is in the cell coordinate.

The general form of the constant multiplication writing itself is almost identical to a normal numbers multiplication.

=constant_number * number


To make you understand better, here is an example of constant multiplication formula writing. The example given is a constant multiplication writing with direct typing and an absolute cell coordinate reference.



Direct typing:

Multiplication in Excel and All Its Formulas & Functions - Screenshot of the Example for the Direct Typing Method for Excel Constant Multiplication

Absolute cell coordinate reference:

Multiplication in Excel and All Its Formulas & Functions - Screenshot of the Example for the Absolute Cell Coordinate Reference Method for Excel Constant Multiplication

Easy, isn’t it? By typing directly or using an absolute cell coordinate reference, you just need to type your formula once. After done typing, copy the formula to other cells to get the other number and constant multiplication results!



How to Multiply Matrices in Excel

Sometimes, you also need to multiply the number matrices you have in excel. To do this kind of multiplication easily, you can utilize the excel MMULT function.

Generally, this is how we write MMULT in excel.

{=MMULT(matrix_cell_range1,matrix_cell_range2)}


Why do we have curly brackets around the MMULT formula writing? Those are the symbol of an array formula form in excel!

We usually have our number matrices in the form of cell ranges in excel. To multiply those cell ranges and get our result in full, we should use the array formula form.

In the MMULT writing form itself, we input the cell ranges where we put our number matrices. To get the array formula form, we press Ctrl + Shift + Enter (Control + Shift + Enter in Mac) buttons after we finish writing the formula (we cannot type the curly brackets ourselves). By doing that, we can get our matrices multiplication result correctly!

To do the matrixes multiplication itself, first, we need to highlight the cell range where we want to put the result. To make the understanding easier, look at the example below when we highlight the cell range for our matrixes multiplication result.

Multiplication in Excel and All Its Formulas & Functions - Screenshot of the Highlight Example for the Matrixes Multiplication's Result Cell Range

After highlighting the cell range, we type MMULT and input the matrix cell ranges we want to multiply. Press Ctrl + Shift + Enter after we finish with the MMULT formula writing and your matrixes multiplication result will be there!

Multiplication in Excel and All Its Formulas & Functions - Screenshot of the Example for Matrixes Multiplication in Excel Using MMULT



How to Multiply and Sum (Or Other Relevant Operations) in Excel

What if we want to multiply and then sum or do other numbers processing at once? Can we do that or we must multiply our numbers first before processing them further?

By using array forms, we can multiply and do another process on all of our numbers at once. This can be done in one writing. Special for sum, we can also use SUMPRODUCT as an alternative method.

If you use SUMPRODUCT, then the writing form is like this.

=SUMPRODUCT(cell_range_1 * cell_range_2)


If there are more than 2 cell ranges to multiply, you just need to add the cell ranges and * signs.

For a clearer explanation for this SUMPRODUCT, here is its implementation example.

Multiplication in Excel and All Its Formulas & Functions - Screenshot of the SUMPRODUCT Example to Multiply and Sum at the Same Time

In the example, we input cell ranges we want to multiply (quantity and price columns) to then sum the multiplication results.

We can do it in phases by knowing the multiplication results per row first before summing them. However, we can also do it directly using SUMPRODUCT.

In our formula, the numbers in the quantity and price cell ranges are multiplied first in SUMPRODUCT. Because our multiplication is a cell ranges multiplication, the result of the example becomes an array like this.

{60000, 60000, 75000}


The numbers there are the multiplication result of the numbers that have the same position in the quantity and price columns (3x20000, 2x30000, 3x25000). After we get the array, SUMPRODUCT will sum all the numbers in it as per its function. From there, we get 195000, which becomes the number we put in the cell.

What if we want to use another way to multiply and sum? Or we want to multiply our numbers then do another operation like using MAX or AVERAGE formula?



The general array formula writing form to do that can be illustrated as follows.

{=FORMULA((cell_range1) * (cell_range2))}


Like SUMPRODUCT, if there are more than 2 cell ranges, then you just need to add the cell ranges and * signs. Make sure the formula you use accepts a number cell range as its input so this won’t produce an error.

As an example of this array formula implementation, take a look at the screenshot below.

Multiplication in Excel and All Its Formulas & Functions - Screenshot of the Example for the Array Formula to Do Multiplication and Another Number Processing

As shown in the example, we can use SUM, AVERAGE, MAX, MIN, and COUNT among the formulas combined with the multiplication. Multiply the cell ranges containing the numbers in the formula we use and press Ctrl + Shift + Enter. We will get the multiplication and the formula processing directly!

The array form becomes important here because the multiplication result in the formula we use is an array. Don’t forget to use that form because your formula will produce an error if you forget.



Alternative Multiplication Method 1: PRODUCT

What if we want to multiply many numbers in a particular cell range fast? It is troublesome if we need to write the multiplication formula with * signs because we need to input all the numbers. Is there another way we can use in that situation?

The answer to that question is there is another way. We can use the PRODUCT formula to make our formula writing much quicker to get the multiplication result we need.

How to use this formula? If you have used SUM before, then the usage principle of PRODUCT is similar. You just need to input the cell range which contains the numbers you want to multiply as its input.

Generally, here is the writing form of PRODUCT in excel.

=PRODUCT(number_1, number_2, …)


Although officially, we can input many numbers/cell coordinates, we often only use one cell range input. However, if needed, you can also input more than one cell range/numbers/cell coordinates.

Here is an example of PRODUCT usage and result in excel.

Multiplication in Excel and All Its Formulas & Functions - Screenshot of the Example for PRODUCT Implementation in Excel

The example of the PRODUCT writing can be seen in the formula bar. As shown there, we just need to input the cell range that contains our numbers in PRODUCT. This formula will then give the cell range’s numbers multiplication result directly.

If you want to learn further about PRODUCT, you can read this tutorial which discusses it specifically.



Alternative Multiplication Method 2: Paste Special

There is one more method to do multiplication in excel, which is by utilizing the paste special feature. By using this method, you can multiply your numbers without any need to write a formula.

To make it clearer, let’s see its implementation example. For paste special, first, you need to get the cell containing a number you want to multiply into a copy mode.

This mode can be activated by pressing Ctrl + C (Command + C in Mac) on the cell. It is already active if your cell has dotted lines move around it. You can see the example in this screenshot.

Multiplication in Excel and All Its Formulas & Functions - Screenshot of the Example for the Copy Mode on a Number Cell

After those lines are there, move your cursor to another cell where you place the other number you want to multiply. Click the dropdown in the Paste menu on the Home tab and choose Paste Special…

Multiplication in Excel and All Its Formulas & Functions - Screenshot of the Paste Special Location in the Paste Menu Dropdown

In the dialog box shown, choose Multiply in the Operation part. After that, click OK.

Multiplication in Excel and All Its Formulas & Functions - Screenshot of the Multiply and OK Location in the Paste Special Dialog Box

Your number has been multiplied in the cell where you do the paste special. You don’t need to write any formula to do it!

Multiplication in Excel and All Its Formulas & Functions - Screenshot of the Multiplication Result Using Paste Special Feature



Exercise

After learning how to multiply in excel, now let’s do an exercise. We provide the exercise so you can deepen your understanding on what you have learned just now.

Download the exercise file and answer all the questions. Download the answer key file when you have answered all the questions. Or probably when you are confused about how to finish one or all the questions!

Link to the exercise file:
Download here

Questions

  1. How much total payment is needed for hot dogs, hamburgers, and french fries? Use the manual method with the star sign ( * ) to answer!
  2. How much total payment is needed for tacos and sandwiches? Use PRODUCT to answer it!
  3. How much total payment is needed for all the food? Use an array formula form to answer it!

Link to the answer key file:
Download here



Additional Note


PRODUCT is seldomly used because you usually only need to multiply a few numbers. However, when you need to multiply many numbers, your understanding of PRODUCT will be very helpful.



Related tutorials you should learn:



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!





Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!




Follow our tutorial content also on


Hand-Picked CE Tutorials

Excel Calculation

How to Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division Excel Calculation

Average Excel Calculation



Excel Formula

VLOOKUP Excel Formula

Excel IF Function

SUM Formula in Excel

COUNTIF Formula in Excel

COUNT Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text Excel

Excel Worksheet Definition

Excel Range Definition

How to Add Columns in Excel



Excel Consultation

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2021 Compute Expert