You can use the asterisk symbol (*) to multiply either formulas, cells or values in Excel. Multiplication can be applied many times until you get the result.

An alternative is to use the PRODUCT() function. An example is

=PRODUCT(A1:A4)

This will multiply all the numbers in cells A1, A2, A3 and A4.

img

Below we will focus on using the asterisk symbol (*). Just keep in mind that you can replace any examples below with the PRODUCT() function.

Multiplication Formula

img

Example — Multiply Two Cells

Suppose you want to get the product of cell A1 and cell B1, you can select a blank cell, and type an equal sign to ask Excel to evaluate what follows. As such:

= A1 * B1

It is a good idea to leave a blank space before and after the multiplication symbol. This makes the formula easier to read.

Example — Multiply Two Values

To multiply two numbers, say 2 and 4, do as follows:

= 2 * 4

You can multiply numbers, numbers stored as text, decimals and dates.

Example — Multiply a Value and a Cell

To multiply a value and a cell, say 2 and cell A1, do as follows:

= 2 * A1

The order of multiplication does not matter.

Example — Multiply Two Formulas

To multiply two formulas, it is a good practice to put each formula in round brackets.

For example:

Formula 1: B2 + C2

Formula 2: 4 – 2

Multiply Two Formulas:

= (B2 + C2) * (4 -2)

Automatically Multiply Each Cell Entry By A Number

You can add a column with formulas prewritten that will multiply the cells with a fixed number.

For example, in column A you have the numbers 1, 2 and 3.

You want to multiply each number by say, 5, as you type the numbers in column A

One way is to write the formula

=A2*5

in cell B2. Select the cell, then drag the dark cross handle down to more cells. This will fill the relevant formula in those cells. As shown:

auto multiply by a number

An alternative way is to convert the data range to a table.

Automatically Multiply Each Cell Entry By A Number

Select the data, press Ctrl + L or Ctrl + T to make it a table.

In a new column, type the equal sign (=), then select the cells and use the multiplication sign. Excel will put what is called a “structured formula” in the new column.

The formula may look alien at first, but you have done it using cell selection, so do not worry too much if some symbol, such as (@), does not make sense.

When you then enter a new value in the next row, Excel will fill out the new formula automatically.

The third way multiplies all numbers by the same multiple in one go.

paste special multiply

Suppose you want to multiply all numbers by 5. Put 5 in one of the blank cells, and press Ctrl+C to copy.

Select the data, press Alt+E+S+M to do paste special.

In the dialogue that shows up, select Multiply, click OK.

Alt E S M paste special multiply

How To Multiply Columns In Excel

multiply columns in excel

To multiply two columns and them sum in Excel in one cell, you can use the SUMPRODUCT() function.

The function has the syntax

=SUMPRODUCT(array1, array2, array3…)

where array 2 and further arrays are optional and can represent cell address.

For example, suppose you have two columns as shown below.

If you multiply the two columns, you should get 32 as the answer.

Similarly, when you type the formula

=SUMPRODUCT(A2:A4,B2:B4)

This will give you the same result.

Convert Text to Number

You can convert numbers stored as text to numbers by multiplying the number by 1. This is quite useful in a formula when you are not sure if the cell values are properly formatted. This will return the same number as actual numbers that you can do calculations on.

Multiply the Same Number Multiple Times

To multiply the same number multiple times, it is better to use an exponential function.

Suppose you want to multiply the number 2, 5 times.

You can use the formula:

=2^5

where the caret symbol (^), also called circumflex, allows you to do the equivalent of

= 2 *2 *2 *2 *2

The caret symbol can be found on top of the number 6 on the keyboard.

Summary

The multiplication symbol (*) allows you to get the product of numbers in succession.

Plenty of examples involving the use of numbers, cell address and formulas are given above.

The post also discussed several ways to automatically multiply cell entries by a number.

Just remember that if you are going to apply a multiplication formula that has cell addresses to other ranges, the result may not be what you expect. This is because Excel moves the cell reference in the formula according to where the new position is.

To address the issue, you can either enter each formula manually, or learn about absolute reference in formulas. Basically, the use of dollar sign ($) to fix either row or column position in cell reference.


Related Post

Pin It on Pinterest

Share This