Manipulating Arrays and MMULT in Excel

This installment of the Excel Boot Camp teaches the basics of matrix multiplication, and how to utilize these mathematical operations in Excel through the SUMPRODUCT(), TRANSPOSE(), and MMULT() formulas.

This video teaches

  1. Arrays of numbers
  2. Array manipulation
  3. Array multiplication
  4. Matrix multiplication


1) Arrays of numbers

Any row or column or block of numbers is an array

2) Array manipulation

Transpose([■(a&b&c)]) = [■(a@b@c)]

Transpose([■(a@b@c)]) =[■(a&b&c)]

Transpose([■(a&d@b&e@c&f)]) =[■(a&b&c@d&e&f)]

(rotation on the diagonal)

3) Array multiplication


In Excel =SUMPRODUCT([a b c],[1 2 3])

Row × column = single number.

Let’s practice with molar masses and combustion enthalpies

4) Matrix multiplication

Expanded form of SUMPRODUCT()



Matrix Multiplication Rule

M1 × M2 = M3

The columns of M1 must equal rows of M2.

The result matrix M3 will have the number of rows of M1 and the number of columns of M2.

[row1 by column1]× [row2 by column2]

M3 = [row1 by column2]

[2 by 3]×[3 by 2] = [2 by 2]


You need to understand the above to do matrix multiplication (by hand AND in Excel).

See the video for examples of computing BOTH molar mass and combustion enthalpy combined in one shot.

Also enjoy the integration of wave functions to show orthonormality.

225 Integrals with “Ctrl+Shift+Enter”!

THAT is why we like array formulas.

Happy Excelling!


Posted in EXCEL BOOT CAMP, Science Education

Charting Pro Tips in Excel

This installment of the Excel Boot Camp covers three topics.

  1. Cell block navigation – No, this is not related to Huntsville, Texas. 😉
  2. Efficient ways to select data.
  3. Creating XY Scatter charts

Cell Block Navigation

For small blocks of data – tables that fit on one screen – the mouse is fine. But for LARGE sets of data like an FTIR spectrum that has over 2000 data points, navigating around the data set with the mouse is stupid. (Yes, I said it.)

You can navigate within a block of cells containing data in the following manner:

  • Arrow keys move the cell selection one row or column at a time.
  • “Ctrl + arrow” moves the cell selection to the far right, far left, top, or bottom of the block of cells according to the arrow that was used.
  • “Ctrl + Home” moves the cell selection to cell “A1” – the topmost corner of the sheet. This is very handy.
  • Ctrl + PgUp and PgDn change the sheets. (That’s ripe for a meme.)
  • “Ctrl + End” moves the cell selection to the bottom-right corner of the data on a sheet.

Selecting Data

Adding the Shift key to the above sequences allows you to select large chunks of data. This is necessary for EFFICIENTLY charting what you want to chart.

Say I had a data set with an x-values column and 15 y-values columns. And  furthermore, I only want to plot the first three y-columns against the same x column.

  1. I left-click the x-value column header.
  2. I press and hold Shift.
  3. I tap the right arrow 3 times.
  4. I add the Ctrl key so that Ctrl and Shift are BOTH pressed.
  5. I tap the down arrow, and voila! A block of the x-values and three columns of y-values are selected and ready to chart.

Charting Pro Tips

Since this is so visual, I’m going to punt and say “Watch the video…


I welcome your comments, likes and subscribes. You know the drill.

Happy pchemming!


Posted in EXCEL BOOT CAMP, Science Education

Anchoring Cells in Excel

The first Excel Boot Camp topic is on anchoring cells. Newcomers to Excel must master this skill early on to be successful.

To enter formulas into Excel, simply click in a cell with the mouse, type “=” and enter the formula. Notice that you can use references to other cells in your formulas, and THAT is why spreadsheets are so useful.

Spreadsheets are NOT just tables of numerical values. They are complex interrelated formulas that change output when the input changes.

Spreadsheets are also incredibly visual which is why I favor them when teaching the applied mathematics related to physical chemistry.

So back to the main topic.

The dollar sign symbol “$” is the anchor. If you create a cell formula in cell B5 for a linear equation such as “y = mx + b” using cell references, it might look like this. “=B1*A5+B2”

B1 is the slope value at the top of the page, B2 is the intercept value at the top of the page, and A5 contains the x values for the equation. If you copy this formula down for several x values, the formula in B6 will have a slope of B2 and an intercept of B3. This is bad! The “constants” have been pulled down by when the formula is copied down the column.

The relative cell references act like boats that can be pulled along wherever the formula is pasted. But if the formula contains a constant cell reference you need to anchor that boat on a specific cell.

Use the dollar sign symbol to anchor the cell references so that the formula in B5 is “=$B$1*A5+$B$2”. Now, the formula will use the slope in B1 and the intercept in B2 no matter where it is pasted.

And that’s how you anchor cells.


If you are typing your formulas into Excel, you are prone to make mistakes! Use the mouse to select the cells for your formulas. When you click on a cell that should be anchored, hit the function key “F4” at the top of the keyboard. This automatically adds the dollar signs to the cell reference. Sometimes you only want to anchor the column, or the row, so hit “F4” multiple times, and the anchoring will cycle through all the options.

We learn by doing, so watch the video and try it yourself!


Posted in D L Williams, Education, EXCEL BOOT CAMP, Science Education

How to EXCEL in PCHEM?

I have begun a new tutorial series on Excel I’m calling my EXCEL Boot Camp.

Why host an EXCEL Boot Camp for PCHEM?

I’m glad you asked


Many of my students have never used EXCEL. Years ago, I added videos to the lab materials showing them how to complete the many data analysis tasks in Excel, but these were very cook-book in nature. The students were successful in following the recipe, but if they made any small mistakes along the way the process dragged on, consuming the whole FOUR-HOUR lab period (or longer).

In this situation, when it came time for the student to answer some simple and basic questions about the SCIENCE behind the Excel data analysis, they were unable to do so.

Therefore, I am adding some Excel tutorials that are skills-based. This will train the students in the useful spreadsheet skills so that they can bring them to the data analysis exercises and fully participate in the SCIENCE.

The Lessons will be added to this blog as they become available on YouTube. You can subscribe here and on YouTube to be notified when new content is posted.

Happy pchemming!