Learning how to use formulas in Google Sheets can help you calculate data more efficiently. This can also save a lot of time, especially when you need to multiply two columns. However, this formula can look complicated. But once you understand them, they will make your life much easier.
In this article, we will show you how to use a formula to multiply two columns in Google Sheets and other multiplier functions.
Basics of Multiplication Formula
For formulas on Google Sheets to work, they must have a few marks that you must remember. The first, which is the basis of each formula, is the sign of the equation (=). To have your formula valid and show numbers, write this sign at the beginning.
Next, to multiply numbers, use an asterisk (*) in between. Finally, to get the amount and complete your formula, press ‘Enter.’
Multiplying Two Columns
To multiply the two columns on Google Sheets, everything must enter data. The most efficient way is to use Array Formulas.
Suppose you want to have data values multiplied from columns C and D. Select the cell where you want to add them. Follow these steps to successfully apply the formula:
- First, write the equality sign (*) in the selected cell.
- Next, type ‘ARRAYFORMULA’.
- Or, you can press Ctrl + Shift + Enter, or Cmd + Shift + Enter for Mac users. Google Sheets automatically adds an array of formulas.
- Now, drag down the cell in the first column that you want to duplicate.
- Then, type ‘*’ to make sure you multiply.
- Drag down cells from other columns.
- Finally, tap ‘Enter’ to apply the formula.
- The column you choose will display the multiplied value.
After you create an Array Formula, you cannot delete or edit individual arrays. However, you can delete the array altogether. Just double-click on the cell where you typed the formula and delete content. It will automatically delete all amounts from the column.
Getting the Number of Values Multiplied
If you need to get the number of values multiplied for several reasons, there is also a simple way to do it. Make sure you go through these steps:
- First, complete the steps above to multiply the cell.
- Now, select the cell where you want to get the value multiplied.
- Type the equality sign (=) there.
- Next, write ‘SUMPRODUCT’.
- Then, select the cell that you want to duplicate. (This will be a cell with your Array Formula).
- Finally, click ‘Enter’ to get the amount.
Multiplying Cross Columns
When you have two separate columns with data, and you need to multiply them, follow these steps:
- First, select the cell where you want the number to appear.
- Type the equality sign (=).
- Then, click on the cell from the first column.
- Now type ‘*.’
- Next, select a cell from another column.
- Finally, tap ‘Enter’.
- The number will appear in the cell that you selected.
To have all the values appear in the column, click on the small box in the lower right corner of the multiplied value. You should be able to drag it to the bottom of the column. In this way, all amounts will be displayed in the cell.
Multiplying with the Same Number
If you have multiple cells with the same number, there is a special formula for that too. You have to use something called absolute reference. This is represented by the dollar symbol ($). Look at this Google Sheet. There is some data in column C, which we want to multiply three.
But we don’t want to do this manually for every cell. This takes time, especially if there are more cells in number than we have here. To multiply C5 with E5, you can simply type the following:
- In the cell you want to have the value multiplied, write an equality sign (=). We will type it in D5.
- Now, click on C5 or type next to ‘=’.
- Then write ‘*’.
- After that, click on E5 or type.
- Tap ‘Enter’.
- The number will appear where you want it.
Now, you can try dragging values to get multiplied values for all cells. Unfortunately, this will not work and you will only get zero in all cells.
In order for the number to be displayed in all cells, you must apply a different formula. That’s why you all have to use absolute references. Even though it sounds complicated, it really isn’t. Hang in with us.
- Select the cell where you want the value to appear.
- Now, write the equality sign (=).
- Click on the cell you want to duplicate.
- Type ‘*.’
- Next, click on the cell that you want to use to multiply all cells. For example, E5.
- Enter ‘$’ in front of the letter and the number you represent. It should look like this ‘$ E $ 5’.
- Tap ‘Enter’ to complete the formula.
- Click on the small box in the lower right corner of the formula.
- Drag down the column so that values appear in all cells.
When you write ‘$’ in front of the letter and the number that represents the cell, Google Sheets tells you it’s an absolute reference. So when you drag down the formula, all values represent the multiplier of that number and other numbers from the cell.
Use Google Sheets for Advanced Calculations
Google Sheets can be useful for advanced calculations. However, this can be tricky if you don’t know which formula to use. In this article, we have described how to multiply two columns and carry out other copy operations.
Do you use Google Sheets to multiply? Which of the methods in this article do you use most often? Let us know in the comments section below.