Skip to main content

How to multiply in Excel? – a complete methods guide

Author: Siluvia Last Modified: 2024-01-17

In Excel, there is no single "universal multiplication formula" that covers all possible multiplication scenarios. Instead, Excel offers multiple ways to perform multiplication, each suitable for different needs and situations. Users can choose from basic operations using the asterisk (*) symbol, functions like PRODUCT and SUMPRODUCT, or features like Paste Special, depending on the complexity and nature of their data. In this guide, we'll dive into the diverse ways you can perform multiplication in Excel, tailoring each method to your specific needs, whether it's handling basic number calculations or dealing with more complex data sets.


Multiply with the Multiplication Symbol (*)

The most basic operation for performing multiplication is to use the asterisk * symbol. In this section, I will show you how to use this symbol for multiplication in different situations.


Multiplying Individual Numbers

For example, to multiply the number 5 by the number 3, you can do as follows.

  1. Select a cell (A2 in this case) to output the result.
  2. Start by entering an equal sign “=”, and then enter the numbers with the multiplication symbol. Here I enter the following formula into cell A2.
    =5*3
  3. Press the Enter key to get the result.
Notes:
  • The multiplication symbol can be used to multiply several numbers together; for example, the formula =5*3*2*1 yields a result of 30.
  • In Excel, the asterisk (*) symbol for multiplication can be combined with other arithmetic operators like addition (+), subtraction (-), division (/), and exponentiation (^). When doing so, it's important to remember that Excel follows the PEMDAS order of operations: Parentheses first, then Exponents, followed by Multiplication and Division (from left to right), and finally, Addition and Subtraction (from left to right).
    For example, in this formula: =(3+2)*4-3^2, Excel will first calculate the expression within the parentheses (3+2), then multiply the result 5 by 4 to get 20, and finally subtract the square of 3 (3^2=9), and get the final result as 11 (20-9=11)

Multiplying Cells

If you have numbers in different cells, you can apply the * symbol to multiply these cells.

  1. Select a cell to output the result.
  2. Start by entering an equal sign “=”, select the first number cell, type in the * symbol, and then select the second number cell. Here the formula will display as:
    =A2*B2
  3. Press the Enter key to get the result.
Note: If you have numbers in A2, B2 and C2, =A1*B1*C2 will multiply these numbers.

Multiplying Two Rows or Columns

In this example, I will show how to calculate the sales for each product by multiplying the Unit price column and the Quantity column.

  1. Click on cell D2, enter the multiplication formula as follows and press Enter to get the total sale for product A.
    =B2*C2
  2. Click on cell D2 again, and then hover your mouse over the small square at the bottom right corner of the cell until the cursor turns into a plus sign. Click and drag this down to cell D6.
    Now you have multiplied the two columns.
Note: To multiply two rows, use the * symbol with this formula: =B2*B3, and then drag the Fill Handle to the right over the cells to obtain the results for the remaining cells.

Multiplying a Column by a Constant

To multiply a column by a constant number, say 5, you can use the following formula and drag the fill handle down across the column.

=B2*5

Notes:
  • If the constant value is in a cell and you need to reference it in your multiplication formula across multiple cells, you should use an absolute cell reference, such as $E$2. The reason for using absolute reference is to prevent the cell address from changing when you copy or drag the formula to other cells.
    =B2*$E$2
  • For those seeking a formula-free solution, Kutools for Excel offers a powerful Operation tool that allows you to multiply an entire column by a constant number with just a few clicks, eliminating the need for manual calculations in Excel. Explore Kutools for Excel right now!

Multiplying a Cell by Percentage

In Excel, performing multiplication that involves percentages is a common operation and is particularly useful when applying a discount, calculating a commission, or adjusting values based on percentage increases or decreases.

Let's say you have a product that costs $200, and you want to apply a 15% discount to this price. To get the discount amount, here’s how you would do it:

  • Select a cell where you want the discount amount to be displayed.
  • Enter one of the following formulas into the cell. Ensure that 15% is formatted as a percentage, or alternatively, you can enter it as 0.15.
    =200*15%
    =200*0.15
  • Press Enter, and the cell will display the discount amount, which is $30 in this case.

Multiply Multiple Cells or Ranges with PRODUCT

The multiplication symbol (*) is a quick and direct method for multiplying a small number of figures. The PRODUCT function, on the other hand, is a more efficient and clean choice for larger sets of numbers, especially when dealing with ranges of cells.

Easily multiply all numbers in a range

If you want to multiply all numbers from A2 to A6, apply this formula.

=PRODUCT(A2:A6)

The formula =PRODUCT(A2:A6) is equivalent to =A2*A3*A4*A5*A6.

Easily multiply numbers in different ranges

For example, you have two ranges A2:A6 and B2:B6, and you want to multiply all the numbers in these ranges together, apply this formula.

=PRODUCT(A2:A6,B2:B6)

This formula takes all the numbers in the range from A2 to A6 and all the numbers in the range from B2 to B6, and multiplies them together, which is equivalent to =A2*A3*A4*A5*A6*B2*B3*B4*B5*B6.

Tip: To know more about the PRODUCT function, visit this page.

Multiply with Paste Special Feature

This Paste Special feature is useful for applying a multiplication operation across a range without using a formula.

For example, you want to multiply a range of cells (A2:A6) by a number in cell C2. You can do as follows.

  1. Select the cell with the number you want to multiply with, and then press the Ctrl + C keys to copy it. Here I copy cell C2.
  2. Select the range A2:A6 and right click on it. In the right-clicking menu, choose Paste Special > Paste Special.
  3. In the Paste Special dialog box, select the Multiply option and click OK.
  4. Then all numbers in the selected cells are multiplied by the number 5. See screenshot:

Multiply and Sum with SUMPRODUCT

The SUMPRODUCT function in Excel is a versatile tool, especially powerful when you need to multiply corresponding elements in arrays (or ranges) and then sum up those products.

Let’s say you have a set of products, their respective prices, and sold quantities, and now, you want to calculate the total revenue.

To calculate the total revenue generated from all products, please do as follows:

  1. Select a cell where you want the total revenue (say, C8), enter the SUMPRODUCT formula and press Enter.
    =SUMPRODUCT(B2:B6,C2:C6)
    The cell will display the total revenue generated from all products.
Tip: To know more about the SUMPRODUCT function, visit this page.

Multiplying in Excel is a fundamental skill that can be applied in various scenarios. From basic multiplication to more advanced techniques like SUMPRODUCT, this tutorial offers multiple ways to approach multiplication. Armed with these skills, you are now well-equipped to tackle a wide range of multiplication scenarios in Excel, enhancing both your productivity and analytical capabilities. For those eager to delve deeper into Excel's capabilities, our website boasts a wealth of tutorials. Discover more Excel tips and tricks here.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations