How to multiply values across different sheets in Excel?
Multiplying values in Excel is a common task, and while it's simple to multiply values within a single worksheet, doing so across multiple sheets can be a bit tricky. Have you ever needed to multiply data from different sheets in Excel?
For example, imagine you have one sheet (Sheet1) that lists employees' daily salaries and another sheet (Sheet2) that contains their working hours. You might need to calculate each employee’s total salary based on both their hourly rate and the hours worked. This tutorial will show you how to multiply values across multiple sheets using basic formulas, making this task much easier. Understanding how to connect data across sheets not only saves manual effort but also helps maintain accuracy and efficiency - essential when working with large datasets or dynamic information that updates frequently.
Use the multiplication symbol to multiply values from different worksheets
Use the PRODUCT function to multiply values from different worksheets
Use VBA to multiply corresponding cells from different worksheets
Use the multiplication symbol to multiply values from different worksheets
If you want to multiply values located on different sheets, the quickest approach is to use the regular multiplication symbol in your formula. Follow these steps for a straightforward calculation:
1. Choose a cell in which you want to display the result, then type the following formula:
=Sheet2!B2*Sheet1!B2
2. After entering the formula, press the "Enter" key. To calculate results for additional rows, use the fill handle by dragging it down; Excel will automatically update the calculation for corresponding rows, as shown below:
=Sheet1!B2*Sheet2!B2*Sheet3!B2*Sheet4!B2…Practical advice: Double-check your sheet and cell references before confirming the formula to avoid common errors such as referencing blank cells or mismatched rows.
Error reminder: If a referenced cell contains text or is empty, the formula will return an error or zero.
Use the PRODUCT function to multiply values from different worksheets
An alternative to using the multiplication symbol is the PRODUCT function, which can also be used to multiply values from multiple sheets. This method is beneficial for longer multiplication chains, as it simplifies your formula syntax:
1. Select the cell where you want to display the multiplication result, then enter the following formula:
=PRODUCT(Sheet1!B2, Sheet2!B2)
2. Press "Enter" to get the first result. To repeat the calculation for other rows, use the fill handle to copy the formula down, and each result will reflect the corresponding data. See the screenshot below:
=PRODUCT(Sheet1!B2,Sheet2!B2,Sheet3!B2,Sheet4!B2...)Advantages: PRODUCT is easy to expand for multiple values, and is ideal when a dataset involves three or more factors.
Caution: Ensure each referenced cell contains a numeric value, or the formula may result in an error.
Use VBA to multiply corresponding cells from different worksheets
When you need to multiply values across multiple sheets for large datasets, or if the ranges of data change frequently, a VBA macro can automate and greatly simplify the task. VBA is particularly helpful when your data spans many rows and columns and manual formula application would be time-consuming.
Advantages: VBA can process hundreds or even thousands of rows in just a few seconds, making it ideal for repetitive or large-scale calculations. However, it requires macro-enabled workbooks and some familiarity with VBA basics.
Note: Verify the worksheet names and the output range before running the macro to prevent overwriting existing data. If needed, back up your workbook or run the code on a copy first.
How to set up and use a VBA macro for this task:
1. Go to Developer Tools in Excel (if not visible, enable it via File > Options > Customize Ribbon). Click Visual Basic to open the VBA editor. In the editor window, select Insert > Module, and paste the following code into the Module:
Sub CrossSheetMultiply()
Dim ws1 As Worksheet, ws2 As Worksheet, wsResult As Worksheet
Dim rng1 As Range, rng2 As Range
Dim i As Long, j As Long
Dim lastRow As Long, lastCol As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws1 = Worksheets(Application.InputBox("First sheet name", xTitleId, "Sheet1", Type:=2))
Set ws2 = Worksheets(Application.InputBox("Second sheet name", xTitleId, "Sheet2", Type:=2))
Set wsResult = Worksheets.Add
wsResult.Name = "Multiply_Result"
lastRow = Application.InputBox("Number of rows to process", xTitleId, "10", Type:=1)
lastCol = Application.InputBox("Number of columns to process", xTitleId, "5", Type:=1)
For i = 1 To lastRow
For j = 1 To lastCol
wsResult.Cells(i, j).Value = ws1.Cells(i, j).Value * ws2.Cells(i, j).Value
Next j
Next i
MsgBox "Multiplication complete! Results placed in 'Multiply_Result'.", vbInformation
End Sub2. Click the 
button to run the macro. You’ll be prompted to enter the names of the sheets and the data size - simply follow the prompts. Once finished, a new sheet called "Multiply_Result" will display the results of multiplying corresponding cells from the two specified sheets.
Practical tips: This VBA macro takes two worksheets and multiplies their values cell by cell, beginning from row 1 and column A, based on the number of rows and columns you specify. Before running the macro, ensure both source sheets contain numerical data in the same range size to avoid errors or blank outputs.
Troubleshooting: If the macro cannot find a sheet, double-check the spelling and existence of sheet names. If you see #VALUE! or incorrect results, confirm there are no non-numeric data entries. Always save your work before executing VBA code as changes cannot be undone directly.
Summary suggestions: Multiplying values across multiple sheets is best managed with simple formulas for smaller data but use VBA for more extensive tasks, repeated actions, or when data structure changes often. Choose the method that matches your skills and workflow needs.
More related articles:
- Multiply Two Columns And Then Sum In Excel
- In Excel, most of us may suffer to multiply two columns and then add them up, of course, we can multiply each items and then sum them, but this will be troublesome if there are hundreds or thousands rows need to calculate. In Excel, there is a powerful function – SUMPRODUCT, with it, we can quickly multiply two columns and then sum them.
- Multiply Hours And Minutes By An Hourly Rate In Excel
- Supposing, you have a list of time cells that indicate the worked hours and minutes in Column A, and Column B contains the hourly rate, now, you want to calculate the payment based on hours and rate as following screenshot shown. This article, I will talk about how to calculate the payment with worked hour and rate in Excel.
- Add/Sum Two Numbers Then Multiply In One Formula In Excel
- Have you ever tried to sum two numbers and then multiply by a specified percentage in one formula in Excel? If you have no idea for getting through it, the method in this article will do you a favor.
- VLOOKUP Then Multiply In Excel Tables
- Here, I have two tables in an Excel sheet, one is the table about the rule between profit rate and sales of each product, the other contains the real sales of products. In the rule table, the more sales are, the much higher the profit rate you will get. Now I want to calculate the profit rate and profit of each product in the table list as below screenshot shown in Excel.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in