This post is going to show you all the ways you can multiply values in Google Sheets.

Basic arithmetic operations like addition, subtractions, and multiplications are frequently used in calculations when working on a project.

It’s important you get familiar with the various methods you can use to carry out these operations in Google Sheets.

Various occasions will require multiplication, such as generating a new column based on the contents of other columns.

For instance, you might have financial data that has a price and quantity. To get a revenue column, you have to multiply the price and quantity columns. Operations like this are very common.

The following methods to multiply values will be discussed in this post.

• Multiplication with an asterisk or multiplication operator.
• Multiplication with the MULTIPLY formula.
• Multiplication with the PRODUCT formula.
• Multiplication with an Apps Script.

Multiply using the Multiply Operator

The spreadsheet uses special characters as operators for arithmetic tasks. The asterisk * is used for multiplying numbers.

There are different ways you can use the multiplication operator.

= 22 * 9

You can use the asterisk to multiply individual values just like you would with a calculator, although there’s not a real situation where you should use it this way in a project.

It’s not dynamic and will only make your work harder down the line.

You might find it useful if want to run a quick multiplication that you don’t intend to save.

= A2 * B2

A better way to use the asterisk operator will be to use cell references.

Both methods will return the same result but using cell references will cause the results to recalculate when the values in the referenced cells are changed.

Multiply using the MULTIPLY Function

= MULTIPLY ( factor1, factor2 )

The MULTIPLY function returns the product of two numbers.

This does the same thing as the asterisk operator, and the only difference is it’s a function.

=MULTIPLY(A2, B2)

This formula will multiply the value in cell A2 by the value in cell B2.

You can then copy and paste down the formula to provide results for each row.

=ARRAYFORMULA(MULTIPLY(A2:A10, B2:B10)

You could use the ARRAYFORMULA function like in the above formula to return an array of results.

Ensure the ranges are of the same length. This will prevent your syntax from returning an error.

Multiply using the PRODUCT Function

= PRODUCT ( factor1, [factor2, …] )

The PRODUCT function is another way you can use to multiply cells in Google Sheets.

The PRODUCT function takes an arbitrary number of arguments. This means you can multiply as many values as you want to with the PRODUCT function.

Whereas the MULTIPLY function allows the multiplication of only two values.

= PRODUCT ( A2, B2 )

The above formula will multiply the value in cell A2 by the value in cell B2.

= PRODUCT ( A2:B2 )

But the PRODUCT function only needs one argument to carry out the same multiplication as you can reference a range of cells and it will multiply each cell together.

The MULTIPLY function however has a few advantages over the PRODUCT function. MULTIPLY works better when multiplying across two columns.

= PRODUCT ( A2:A10, B2:B10 )

The PRODUCT function doesn’t handle multiplication across columns in this manner quite as well. The above formula will multiply each cell and return a single value.

It will be better to reference the values in the cells separately to return the correct result, as in the first PRODUCT function syntax above.

As a result, you must be careful when using the PRODUCT function as it doesn’t work inside an ARRAYFORMULA function.

Multiply using a Pivot Table Custom Calculation

A pivot table helps you easily summarize and view your data from various perspectives.

It’s an awesome tool in Google Sheets because of its functionalities that allow you to create new measures and calculated columns using predefined or custom formulas.

Follow these steps if you want to get the revenue for this sample dataset using the pivot tables custom calculation.

1. Select the range containing the data you want to use the pivot table to summarize
2. Go to the Insert menu and click on the Pivot table.

When the Create pivot table dialogue box opens, the Data range option would automatically pick up the range you just selected.

3. Click on the Existing sheet radio button in the Insert to section.

You can choose to insert the pivot table to a new sheet by clicking on the New sheet radio button. Whatever option you choose, select a cell that will serve as the origin for inserting the pivot table.

4. Click on the OK button.

The Pivot table editor should appear to the right of your spreadsheet.

5. Drag the Products column to the Rows field, and drag the Price and Quantity columns to the Value as field. You can also do this using the Add button to place the columns in each field.
6. Click on Add and select Calculated Field in the Values as field.

= Product * Price

7. A text box will appear. Below the text box, you can add the above formula to generate the values for the calculated column.

= MULTIPLY ( Product, Price )

You can also use the MULTIPLY function as above.

To change the column headers, go to the pivot table and double-click on the column headers.

Change the default name for the calculated field to Revenue.

Now you have the Revenue values, and you can also see the revenue generated per product.

You will notice that the Grand total for the calculated column doesn’t quite add up.  The total sum of the values in the Revenue column is clearly overstated.

This has happened because all the values in the calculated field are derived using the formula entered in the text box including the value in the Grand Total row.

You should take note of this when using custom calculations in a pivot table. It is easier to spot the error in this case because the dataset is quite small.

With a large dataset, the error becomes almost impossible to spot, and it will have a huge impact on the accuracy of your analysis.

To rectify this error, simply uncheck the Show totals option in the Rows field to remove the Grand totals row.

Another thing to keep in mind when creating custom calculations is the correctness of your spellings when typing the syntax for your formula.

The calculated field input in Google Sheets doesn’t have autofill or predictive text capabilities yet, so any misspelled word will either trigger an error or return the wrong result.

Multiply using an Apps Script

You can create an Apps Script to carry out a multiplication operation.

Let’s say you want to increase the Price column by 10 %. That means you have to multiply each row by 1.1 using either of the methods discussed earlier.

An Apps script can help you carry out this task faster and more efficiently without creating any redundant data.

Go to the Extensions menu and select Apps Script to open the app script editor.

function multiply() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var selectedRange = ss.getActiveRange()
  var selectedValues = selectedRange.getValues()
  var selectedColumns = selectedRange.getWidth()
  var selectedRows = selectedRange.getHeight()

  var ui = SpreadsheetApp.getUi()
  var input = ui.prompt("Enter number to multiply with range")

  for(i = 0; i<selectedRows; i++) {
    for(j = 0; j<selectedColumns; j++) {      
      if (input.getSelectedButton() == ui.Button.OK) {
        selectedRange.getCell(i + 1, j + 1).setValue(selectedValues[i][j] * input.getResponseText());
      }
    }
  }
}

function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('Multiplication')
      .addItem('Multiply Range with Single Value', 'multiply')
      .addToUi();
}

Copy and paste this script into your script editor and press the Save button, then proceed to refresh your spreadsheet page.

The script creates and adds the Multiplication tab to the menu. This new menu item will appear in your spreadsheet after you refresh it.

To use the script, select the cells you want to increase their values, click on the Multiplication menu, and select the Multiply Range with Single Value sub-menu.

This action triggers a prompt. Enter 1.1 in the text box that appears and click on OK.

The script will increase each value in the range by the value entered in the prompt and return the result in the same cell, just like the paste special multiply operation in Excel.

Conclusion

These methods for multiplying values in your spreadsheet will be sufficient for many of the multiplication tasks you will carry out in your spreadsheet.

You only need to remember which one is more efficient for the task at hand.

The MULTIPLY function or asterisk operator is best for multiplying two values.

When you want to multiply two or more values, use the PRODUCT function.

If you want to multiply the values of a range by a constant number, then using an Apps script will be more efficient.

Do you know of any other multiplication methods? Let me know in the comments section!