Excel Multiplication Guide

Multiplying data in Excel is crucial for any kind of analysis or calculations you’re doing. Here’s how you can do it effectively using different methods.

Method 1: Using the PRODUCT function

The PRODUCT function is super handy for multiplying several numbers, cells, or ranges of cells together. It saves you from writing out a long multiplication formula and it’s the best way to multiply data when dealing with large sets of numbers.

  1. Select the cell where you want the multiplication result to appear.
  2. Type =PRODUCT( in the formula bar.
  3. Now, select the cells or the range of cells you need to multiply. For example, if you want to multiply the values from cell B1 to B5, select that whole range.
  4. Close the parentheses and hit Enter. Your formula will look like this: =PRODUCT(B1:B5). You’ll see the result in the cell.

formula bar

Method 2: Multiplication using the * operator

You can also multiply numbers in Excel using the multiplication operator, which is the asterisk symbol *. This method works well for simple calculations.

Multiplying numbers directly

  1. Click on the cell where you want the result to show up.
  2. Type = followed by the numbers you want to multiply, with * between them. For instance, to multiply 5 by 10, type =5*10.
  3. Press Enter to display the result.

multiplying numbers directly

Multiplying cells

If you want to multiply values in different cells, reference the cells in your formula instead of typing the numbers directly.

  1. Select the cell where the result should go.
  2. Start with =, and then type the cell references separated by *. To multiply cell A1 by B1, write =A1*B1.
  3. Press Enter to see the result.

multiplying cells

Multiplying columns of numbers

To multiply two columns of data, you can set up a formula in the first row, and then copy it down for the other rows.

  1. In the first cell of the results column, enter the multiplication formula, referencing the cells in that row. For example, enter =A1*B1.
  2. Hit Enter to get the product for the first row.
  3. Click the result cell, and look for a tiny green square at the bottom right corner of the cell. This is the fill handle.
  4. Click and drag the fill handle downwards to apply the formula to all the other rows.

multiplying columns of numbers
fill handle

Multiplying multiple cells individually

If you need to multiply a bunch of specific cells that are not adjacent, you can include all the references in the formula.

  1. Choose the cell where you want the product to be.
  2. Input your formula referencing each cell with a * between them. For example, =A3*B2*A4*B5.
  3. Press Enter and you will see the product.

multiplying multiple cells individually

Multiplying a column by a constant number

When you need to multiply a column by a fixed number, you can use an absolute cell reference for the constant.

  1. Enter your constant in a cell, let’s say in cell B7.
  2. In your results column, write the formula using an absolute reference for the constant. For instance, use =B1*$B$7, with $B$7 locking the cell reference to B7.
  3. Press Enter to calculate the product for the first row.
  4. Use the fill handle to apply this formula to the rest of the cells in the column.

Multiplying a column by a fixed number
formula with absolute reference

Method 3: Multiplying using Paste Special

If you find absolute cell references confusing, you can multiply a column by a constant using Paste Special without any complex formulas.

  1. Enter the constant number into a cell, like cell B7. Copy it by right-clicking and selecting copy or press Ctrl + C.

copy constant

  1. Select all the cells you want to multiply and then right-click on selected cells and choose Paste Special.

select range and paste special

  1. In the paste special dialog box, under the operation section, select multiply and hit OK.

paste special dialog box

Excel multiplies all the values by your constant, replacing the originals.

multiplication result by paste special