Multiplying Matrix Rows by Vector Columns in Excel

Performing matrix-vector multiplication in Excel often frustrates analysts due to tedious manual cell alignment and formula wrapping errors. While standard calculation workarounds like manual product summing can bridge this gap, they degrade model scalability.

Leveraging dynamic array formulas grants users immediate computational speed and eliminates manual errors. However, as a key educational stipulation, your matrix column count must strictly match your vector row count for the math to resolve.

By utilizing concrete functions like MMULT and TRANSPOSE, you can master this workflow. Below, we will outline the exact steps and formulas required to execute this calculation flawlessly.

In data analysis, financial modeling, and engineering, you often need to perform linear algebra operations directly within a spreadsheet. One of the most common mathematical operations is multiplying a matrix (a grid of numbers arranged in rows and columns) by a vector (a single row or column of numbers). Specifically, multiplying matrix rows with vector columns is a foundational step in calculating weighted averages, portfolio valuations, neural network node activations, and multi-criteria decision matrices.

Excel provides several powerful tools to execute this operation seamlessly. Depending on your version of Excel and your specific analytical goal, you can use traditional matrix functions like MMULT, modern dynamic array formulas like SUMPRODUCT, or advanced Lambda helper functions like BYROW. This comprehensive guide walks you through the exact formulas, visual setups, and step-by-step instructions to master these calculations.

Understanding the Mathematics: Matrix Row times Vector Column

Before writing the Excel formulas, it is important to understand the mathematical rules governing matrix-vector multiplication. To multiply a matrix by a column vector, the number of columns in the matrix must equal the number of rows in the vector.

If you have a matrix $A$ of dimensions $M \times N$ (where $M$ is the number of rows and $N$ is the number of columns), you can only multiply it by a column vector $B$ of dimension $N \times 1$. The resulting output will be a new column vector $C$ of dimension $M \times 1$.

Mathematically, for each row $i$ in the matrix:

Row Result = (Row Element 1 × Vector Element 1) + (Row Element 2 × Vector Element 2) + ... + (Row Element N × Vector Element N)

The Setup: Our Example Data

To make this practical, let's establish a sample dataset. Imagine we are analyzing product sales across three different regions (our Matrix) and want to calculate total revenue using fixed unit prices for each product (our Vector).

Matrix: Product Sales (Qty) in Range B2:D4

Column Vector: Unit Prices in Range F2:F4

Method 1: The Classic MMULT Formula (Recommended)

The cleanest and most mathematically direct way to multiply matrix rows with a vector column is using the native Excel MMULT (Matrix Multiplication) function. This function is specifically designed to handle matrix math operations.

The Formula:

=MMULT(B2:D4, F2:F4)

How to Enter the Formula:

• In Modern Excel (Microsoft 365, Excel 2021, and Excel for the Web): Simply click on an empty cell (e.g., H2), type the formula above, and press Enter. Excel will automatically spill the results downward into a $3 \times 1$ range (H2:H4).
• In Legacy Excel (Excel 2019 and older): Because older versions of Excel do not support dynamic arrays, you must enter this as a legacy array formula:
  1. Select the destination range of the exact output size (three vertical cells, e.g., H2:H4).
  2. Type the formula: =MMULT(B2:D4, F2:F4). Do not press Enter yet.
  3. Press Ctrl + Shift + Enter simultaneously. Excel will wrap your formula in curly braces {=MMULT(B2:D4, F2:F4)} and populate the entire range.

The Output Result:

Method 2: Element-Wise Row Scaling (Broadcasting Method)

Sometimes you do not want the matrix dot-product (which sums up the rows). Instead, you might want to scale every element in each row of the matrix by its corresponding item in the column vector, keeping the matrix structure intact. This is called broadcasting or element-wise scaling.

To do this, we multiply the matrix range directly by the transposed column vector. Because we want each product's price to apply to its respective product column, we can use Excel's dynamic array calculation engine:

The Formula:

=B2:D4 * TRANSPOSE(F2:F4)

How it Works:

The TRANSPOSE function converts our vertical column vector (size $3 \times 1$) into a horizontal row vector (size $1 \times 3$). When Excel multiplies a $3 \times 3$ matrix by a $1 \times 3$ row vector, it automatically multiplies every column of the matrix by the corresponding column index of the vector. The output will dynamically spill into a new $3 \times 3$ matrix containing the individual scaled calculations.

Method 3: The BYROW and SUMPRODUCT Lambda Approach (Excel 365)

If you are using Microsoft 365, you have access to Lambda helper functions. These allow you to apply a standard row-level function down a matrix dynamically without relying on traditional matrix algebra limitations. This is exceptionally helpful if your vector is oriented horizontally and you want to bypass transposing, or if you want to perform logical checks inside the calculation.

The Formula:

=BYROW(B2:D4, LAMBDA(row, SUMPRODUCT(row, TRANSPOSE(F2:F4))))

How it Works:

1. The BYROW function takes the matrix B2:D4 and processes it row-by-row.
2. For each row, it assigns the temporary variable name row.
3. The LAMBDA function then executes SUMPRODUCT, multiplying that individual row by the transposed column vector.
4. The engine loops through all rows and spits out a clean, vertical dynamic array of results.

Troubleshooting Common Errors

When working with matrix formulas in Excel, it is easy to run into calculation errors. Here are the most common pitfalls and how to quickly resolve them:

• #VALUE! Error: This almost always happens due to mismatched dimensions. Verify that the number of columns in your matrix exactly matches the number of rows in your vector. For example, multiplying a $3 \times 4$ matrix by a $3 \times 1$ vector will fail; the vector must have exactly 4 rows. Additionally, check that none of the cells in either range contain non-numeric text.
• #SPILL! Error: This is a modern Excel error. It occurs when you write a dynamic formula (like MMULT or BYROW) but there is already data, text, or formatting blocking the cells directly below or to the right of where the formula needs to populate. Clear the cells in the spill path to resolve it.
• #NAME? Error: If you see this error when trying to use BYROW or LAMBDA, your version of Excel is outdated. These functions are only available to Microsoft 365 users and Excel 2021+. Stick to the MMULT or traditional row-by-row SUMPRODUCT formulas if using older software.

Conclusion

Multiplying matrix rows with vector columns in Excel is a fundamental technique that streamlines workflows, reduces sheet bloat, and optimizes analytical modeling. For traditional dot-product matrix multiplication, the MMULT function remains the gold standard across all Excel versions. If you are aiming for element-wise scaling, leveraging the dynamic array engine with TRANSPOSE provides an elegant, interactive output. Lastly, if you are looking to build robust, scalable spreadsheets using modern Excel 365, combining BYROW with SUMPRODUCT yields unmatched flexibility.