MMULT function is incredibly powerful and yet mysterious to most. Let’s explore this complex function (this post inspired by a formula challenge from contextures.com )

# Post Category → Efficiency

# Sum Cells in Overlapping Ranges

**This is a cruel Excel challenge** (I’ve used it!). Why? Because there is a brilliantly simple solution to summing cells that overlap two ranges. Continue reading

# Dynamic Ranges Using Index Function

Robert H. Gascon’s comment from my **last post** convinced me that I should be using the INDEX function for dynamic ranges **not** the volatile OFFSET function. Continue reading

# False Friends in Excel

The term False Friends is used in various fields including linguistics and chess. In this post I’ll explain how it applies to Microsoft Excel! Continue reading

# Concatenate Values to Create a Key

Excel users who don’t know this concept waste hours manually looking through their data. A filter helps but creating a valid lookup key is what Mr Carlson needs! Continue reading

# Paste Formula Into Excel’s Name Box

Do you ever hold down the Ctrl key to select multiple non adjacent cells or ranges? I’ve found a way to select multiple non adjacent ranges (any sheet) by pasting a formula into Excel’s Name Box! Continue reading

# The Einstellung Effect in Excel?

How did a Scientific American article help me create a much simpler solution in Excel? And what connection does it have to the Einstellung Effect? Read on and I’ll tell you 🙂 Continue reading

# Chandoo’s Sequence Number Puzzle

I saw this sequencing challenge from **Chandoo** “**Generate a new sequence number when value changes**“. His formula solution is great but I kept thinking “**What if we have 20000 rows?**“ Continue reading

# An off switch! What an excellent idea!

Some Excel models have so many complex formulas that they just can’t calculate any more. These models often need to be re-created. Some models can be saved by making formulas more efficient. One technique is to **add an OFF SWITCH to heavy formulas**. Continue reading

# What is the easiest way to calculate grades in Excel?

Although the **VLOOKUP** versus **INDEX/MATCH** debate gets most of the attention there are MANY more topics to debate in Excel. When calculating student grades what method is best: **NESTED IF** or **LOOKUP/BINNING TABLE**? Continue reading