Categories

How to Multiply in Excel

Learning to use a complex program like Excel is a bit like going back to school. You have to start with the basics, the most elementary things, before delving into more complex topics and gradually becoming familiar with all the advanced functions offered by the software.

In this regard, today I want to continue the introduction to Excel that we began with my post on how to use Excel and explain to you in detail how to multiply in Excel using the main options provided by the program. It’s a simple operation, I know, but if you don’t learn mechanisms like this well, it will be difficult for you to become an Excel expert.

I will be using Office 2024 for the tutorial, which is available for both Windows and macOS. In any case, the information contained in this guide can easily be applied to earlier versions of Excel and certainly also for future ones: the position of a button in the toolbar may change, but the formulas to use and the steps to take are always the same. Enjoy reading and have fun, or good work, depending on your perspective!

Table of Contents

Simple Multiplication

Let’s start this quick guide on how to multiply in Excel with simple multiplications, which will allow you to display the product of two numbers typed in the same cell.

To perform a simple multiplication in Excel, all you have to do is click on the cell in the spreadsheet where you want to display the product of the multiplication, type the equal sign (=), the two numbers to multiply separated by an asterisk (*), and hit the Enter key on your PC’s keyboard.

If you want to know how to multiply numbers in Excel, such as 12 and 98 (the cell will then display the number 1176), you only need to click on the cell in the spreadsheet where you want to show the multiplication product, type =12*98, and hit the Enter key on your PC’s keyboard.

Formula to Multiply in Excel

In addition to the method I mentioned in the previous chapter, you can also use a formula to multiply in Excel, without having to type in the corresponding mathematical operator. The function to use is =PRODUCT(val1,val2,...) where you must input the multiplication factors.

For example, if you want to multiply the values 12 and 6, in the cell where you want the result, type the formula =PRODUCT(12,6) and press the Enter key on the keyboard. If you want to multiply more factors, such as 12, 6, 3, 5, the formula should include all the factors, thus =PRODUCT(12,6,3,5).

How to Multiply Cells in Excel

To multiply the values contained in two or more different cells of the spreadsheet, you only need to create an expression using the coordinates of the cells to multiply. In this case, you can use the multiplication mathematical operator or the PRODUCT function. Let’s see how to proceed together.

For example, if you want to multiply two cells in Excel, like A1 and B1, click on the cell where you want to display the multiplication product, type =A1*B1 and hit the Enter key on your PC’s keyboard. To avoid errors, you can type the equal sign (=) in the cell where you want to display the result, then click on the first cell to multiply (e.g., A1), insert the mathematical operator asterisk (*), and then click on the other cell corresponding to the second factor (e.g., B1).

With the PRODUCT function, instead, you must indicate the cells to multiply as arguments of the function itself. Therefore, using the previous example, for the cells A1 and B1, the formula will be =PRODUCT(A1,B1).

If you want to multiply all the values in a column of the spreadsheet, use the formula =PRODUCT(A:A) where instead of A:A you must insert the letter of the column you want to multiply (e.g. B:B if you want to multiply the values of column B, C:C if you want to multiply the values of column C, and so on).

How to Multiply One Column by Another in Excel

If you want to multiply columns in Excel, so that all the values from one column are multiplied by those from another, you can do that too, and it’s very simple! However, you need to consider two cases: multiplying whole columns or limiting it to specific ranges.

To multiply two columns in Excel, click on a cell that is in the same row as the first cell to multiply (e.g., C2 if you need to multiply values starting from cells A2 and B2), type the formula =A:A*B:B, and press the Enter key on your PC’s keyboard. Of course, instead of A:A and B:B you must type the letters corresponding to the columns containing the values you intend to multiply. Once the operation is completed, in the cell where you’ve inserted the formula, you will see the product of the multiplication of the two columns in each adjacent cell.

If you want to consider only a range, like in the previous example, in cell C2 you should type the formula =A1:A9*B1:B9 if you wanted to multiply only the values present from cell A1 to A9, just as for the adjacent values in column B, from B1 to B9.

How to Multiply Excel with a Fixed Cell

All the procedures I have shown you up to now, where we deal with the product of cells, can be subject to calculation errors if the formula present in the cell is dragged horizontally or vertically to an adjacent cell.

In reality, dragging a cell is useful if you want to copy a formula from one cell to other adjacent ones, saving you significant time. Unfortunately, in the case of multiplications where one of the factors is fixed, that is, a constant, dragging will inevitably alter the cell reference, causing a calculation error.

Let me give you a practical example to better understand this. In your spreadsheet, you have a series of data in column A and you want all these values to be multiplied by a constant that is located in E1. If in cell C1 you write one of the formulas seen in previous chapters, like =A1*E1, and then drag the formula into adjacent cells below (C2, C3, etc.) using the fill handle in the bottom right corner of that cell, you will see that the formula has correctly calculated the value in cell C1, but incorrectly in the subsequent ones.

To overcome this problem, you need to lock the cell reference within the formula. In the formula mentioned earlier in the example =A1*E1, it is important that the cell serving as the constant is locked with the special dollar sign ($) both before the column reference and before the row reference. Therefore the formula will be =A1*$E$1. Now, dragging the cell down into the cells below will yield the correct calculation since only the references from column A will change, leaving the reference of cell E1 locked when the cell is dragged.

How to Multiply a Column by the Same Number

After all the knowledge acquired so far, you might be interested in how to multiply a column by a number in Excel. This is a simple operation that recalls exactly the same concepts and procedures I mentioned in the chapter on how to multiply a column by another.

To perform this simple operation, you just need to double-click on the cell adjacent to the one where the value you want to multiply is (for example, cell B2 for a value present in cell A2). After that, type the formula =A:A*[number]. Instead of [number], you should specify the number you want to use in the multiplication. For example, if you want to multiply all values in column A by the number 5, type the formula =A:A*5.

In this regard, I also want to point out another procedure whereby you can multiply several cells in a spreadsheet by the same number: type the number you wish to multiply the other figures by in a cell of the spreadsheet, select the cell with the mouse and click the Copy button in the Excel toolbar.

At this point, select with the mouse (holding down the left button) the cells with the figures you want to multiply by the number you previously copied and select the Paste Special option from the Paste menu (in the top left). In the window that opens, check the box next to the Multiply option and click the OK button to complete the operation. The numbers in the selected cells will be replaced by the results of the multiplications.

How to Multiply All Cells by a Constant in Excel

After what I indicated in the previous chapter, let’s see now how to replace a number with a true constant, in order to perform the multiplication.

What you need to do is first identify the constant in the Excel sheet. For example, let’s say all the values are in column A and the constant is indicated in cell G1. In this case, to proceed with the multiplication of all factors present in column A with the constant in G1, you must use the following formula =A:A*$G$1.

Specifically, you will use the dollar sign ($) both on the indication of the column of cell G1 and on the row, in order to lock it. This operation is necessary to prevent the cell reference from being modified when dragging the formula down into the cells below (using the fill handle).

How to Multiply by Percentage in Excel

The multiplication of a value by a percentage in Excel does not differ from what has been said so far. Therefore, if you want to use the multiplication mathematical operator, which is the asterisk (*), all you need to do is type in the cell where you want the result to be displayed the formula =[cell]*[percentage] if it’s a calculation of the percentage of a value already present in a cell or =[number]*[percentage], in the case you want to indicate a specific value. For example, if you have a value in cell A1 that you want to multiply by 5%, in an adjacent cell type the formula =A1*5%.

Conceptually you can perform the same operation using the PRODUCT function, which I talked about in the previous chapter. Even in this case, in the formula =PRODUCT(val1,val2,...) you must indicate an argument related to the first factor of multiplication (a value or the reference of a cell) and as a second argument the percentage. Referring to the previous example, you would write the formula =PRODUCT(A1,5%).

How to Multiply a Column by a Percentage

What if you wanted to multiply a column by a percentage? In this case, the operation to be performed is also very simple.

Double-click on a cell that is in the same row as the first cell of the column to be multiplied (e.g., B2 if you need to multiply the values located in column A starting from cell A2), type the formula =A:A*[percentage] and press the Enter key on your PC’s keyboard. Instead of A:A, you must type the letter corresponding to the column that contains the values to be multiplied, and instead of [percentage], you should enter the percentage by which to multiply the column.

At this point, the result of the multiplication between the value of the first cell (e.g., A2) and the percentage you indicated (e.g., 5%) will appear in the selected cell. If necessary, to show the results of the percentages relative to other cells in the column, drag the fill handle located in the bottom right of the cell where you entered the formula.

If you intend to multiply a column by a percentage already contained in the spreadsheet, you can use the formula =A:A*$B$1 where instead of A:A you must enter the letter corresponding to the column that contains the values to be multiplied and instead of $B$1, the coordinates of the cell that contains the percentage to be used for the multiplication (in this example is B1, the dollar signs serve to lock the value to multiply and prevent Excel from automatically multiplying the cells in the same row, such as A3 with B3, A4 with B4, and so on).

If you need more information on how to calculate percentages in Excel, feel free to check out the tutorial I wrote on the topic: inside you will find all the information and indications you need.

How to Multiply on Excel Sheet

Another situation you might find yourself in is wanting to perform the multiplication between cells or columns that are on different sheets.

In this case, there’s nothing different from what I have illustrated so far. The only thing you have to do is simply switch sheets when you need to specify in the multiplication the reference to the cell that is indeed on the other sheet. Let’s see everything with a practical example.

In the first Excel sheet, named Sheet1, in cell A1, you want to reference the product between a cell from Sheet2 and a constant. What you need to do is position yourself on cell A1 and type the equal sign (=) to indicate that it is a calculation.

After that, as the first factor of the multiplication, click on the label of Sheet2 and then click again on the cell that contains the value you want to include in the calculation. Then press the asterisk symbol (*) and finally type the value of the constant (in my example, 5). Press the Enter key and you’re done!

Keep in mind that you can perform the same operation whether it’s cells, columns, constants, etc. The important thing is always to indicate the cell reference during formula typing while reaching the sheet where the data is located.

How to Multiply Hours in Excel

Let’s now look at another example, regarding how to multiply hours in Excel. After reading what has been explained in the previous chapters, performing this operation may seem like child’s play, but in reality, it isn’t if you do not take some significant factors into consideration.

Applying what you’ve learned so far, let’s say you want to know how to multiply hours by euros in Excel: your spreadsheet consists of hour values located in column A which you want to multiply by the amounts in euros in column B.

In this regard, you might think that it would be enough to simply use the formula of the product between the two values to obtain the result in the adjacent cells (the ones in column C). In reality, performing this procedure would yield a blatantly incorrect result, which would not correspond to what you could easily do manually.

The basic problem behind the calculation error depends on a number of factors: first of all, you must set the hour format for the values in column A. Using the dropdown menu at the top General (or Custom), select the Time option. If a value in column A exceeds 24 hours, via the dropdown menu previously indicated, choose the Other number formats option, and in the window that appears, select the Time > 37:30:55 entries. For entering hours, follow the format [hours]:[minutes]:[seconds].

After that, set the Currency format for the values in column B through the dropdown menu at the top General (or Custom). You can also set the cells in column C with the Currency format since the result will be an amount in currency.

Finally, you must consider how hours are treated in Microsoft Excel: hours are automatically converted into days. For this reason, the multiplication must also include the factor 24 to get the correct result.

In light of what has been said, and considering the proposed example, the formula to be used in cell C2 is =A2*B2*24 or =A:A*B:B*24, thus obtaining the exact total amount based on the indicated hours. Then drag the formula using the fill handle to apply it to all the cells below.

How to Multiply in Excel Online

If you prefer to work from your PC but don’t have Microsoft Office at hand, you can create, view, and edit spreadsheets with Excel Online, the online version of Microsoft Excel that works directly from the browser and requires no payment: it can be used for free with simple access via Microsoft account.

Just like Excel for smartphones and tablets, Excel Online does not include all the advanced functions of the standard version of Excel (the one that installs with Office), but supports multiplications and allows you to use all the formulas mentioned earlier. Therefore, if you need to practice your multiplications in Excel and currently do not have Office installed on your PC, you can access Excel Online, log in with your Microsoft account, and work directly from the browser.

After working on a document in Excel Online, you can choose whether to keep the file on your personal OneDrive (thus online) or download it offline by going to the menu File > Create a Copy > Download a Copy of the service.

Finally, I also point out that other possible alternatives to Microsoft Excel where you can use the multiplication formulas described previously are LibreOffice, a free and open source productivity suite compatible with Windows, macOS, and Linux, and Google Sheets, Google’s online spreadsheet platform. Keep them in mind should you not have the chance (or the desire) to use Microsoft Excel.

How to Multiply in Excel for Smartphones and Tablets

As you probably already know, Excel is also available through apps for Android (from the Play Store or from alternative stores) and iOS/iPadOS. It can be used for free on all devices with sizes equal to or less than 10.1″ (else a subscription to Microsoft 365 is required).

If you have a smartphone or tablet that matches the aforementioned size criteria, you can download Excel simply by opening your device’s store, searching for Excel and tapping the Install/Get button next to the application icon. On iPhone and iPad, you might also be asked to verify your identity using Face ID, Touch ID, or Apple ID password.

Once the download is complete, you can create new spreadsheets, view existing ones, and edit them as you prefer. The documents can be saved (and uploaded) locally or from cloud storage services such as OneDrive, Google Drive, and iCloud Drive (only on Apple devices).

Aside from that, there’s not much to say; the mobile app of Excel has a user interface very similar to that of Excel on computers (on smartphones, to view the toolbar, you need to tap the pencil icon located at the top right), and the formulas to use for multiplications are the same as illustrated earlier for the desktop version of Excel.

More Reading

Post navigation