Use a CSE Formula (Array Formula) to Perform Super-Calculations on Data

October 16, 2001 - by Bill Jelen

Use Ctrl + Shift + Enter (CSE formulas) to supercharge your formulas in Excel! Yes, it is true-there is a secret class of formulas in Excel. If you know the magic three keys, you can get a single Excel array formula to replace thousands of other formulas.

95% of Excel users do not know about CSE Formulas. When most people hear their real name, they think "That doesn't sound the least bit useful" and never bother to learn about them. If you think SumIf and CountIf are cool, you will soon discover that Ctrl+Shift+Enter (CSE) formulas will run circles around SumIf and CountIf. CSE formulas allow you to literally replace 1000's of cells of formulas with a single formula. Yes, my fellow Excel Guru's, there is something this powerful sitting right under our noses and we never use it.

Before there was SumIf, you could use a CSE formula to do the same thing as SumIf. Microsoft gave us SumIf and CountIf, but CSE formulas are not obsolete. Oh no, and they can do much more! What about if you want to do AverageIf? How about GrowthIf? AveDevIf? Even MultiplyByPiAndTakeTheSquareRootIf. Just about anything you can imagine can be done with one of these CSE formulas.

Here is why I think CSE formulas never caught on. First, their real name scares everybody away. Second, they require a foreign, counter-intuitive handling in order to make them work. After you type in a CSE formula, you cannot just hit Enter. You cannot just exit the cell with a click of an arrow key. Even if you get the formula right and hit the enter key, Excel gives you the totally non-user friendly "VALUE!" error. It doesn't say, "Wow - that is beautiful. You are 99.1% of the way there," which you probably were.

Here is the secret: After you type a CSE formula, you have to hold down the Ctrl and the Shift keys, and then hit Enter. Grab a sticky note and write that down: Ctrl Shift Enter. Power Excel users will have the opportunity to use these formulas about once a month. If you don't write Ctrl Shift Enter down right now, you will forget it by the time something comes up again.

Examine this example: Say you wanted to average just the values in column C where column A was in the East region.

The formula in cell A13 is an example of a CSE formula.

=AVERAGE(IF(A2:A10="East",C2:C10))

Plug this in and you will get 7452.667, the average of just the East values. Cool? You just created your own version of the nonexistent Excel function AverageIf. Remember: Hit Ctrl + Shift + Enter to enter the formula.

Check out the next example below.

In this example, we make the CSE formula more general. Rather than specifying that we are looking for "East," indicate you want whatever value is in A13. The formula is now =AVERAGE(IF($A$2:$A$10=A13,$C$2:$C$10)).

Oddly enough, Excel will let you copy and paste CSE formulas without any special keystrokes. I copied C13 to C14:C15 and I now have averages for all of the regions.

Our mainframe system stores Quantity and Unit Price, but not the extended price. Sure, it is easy enough to add a column C and fill it with =A2*B2 and then total column C, but you don't have to!

Here, our CSE formula is =SUM(A2:A10*B2:B10). It takes each cell in A2:A10, multiplies by the corresponding cell in B2:B10 and totals the result. Type the formula, Hold down Ctrl and Shift while you hit enter, and you have you answer in one formula instead of 10.

Do you see how powerful this is? Even if I had 10,000 rows of data, Excel will take this single formula, do the 10,000 multiplications and give me the result.

OK, here are the rules: You have to hit Ctrl + Shift + Enter anytime you enter or edit these formulas. Failure to do so results in the totally ambiguous #VALUE! error. If you have multiple ranges, they all have to have the same general shape. If you have a range mixed with single cells, the single cells will be "replicated" in memory to match the shape of your range.

After you successfully enter one of these and look at it in the formula bar, you should have curly braces around the formula. You never enter the curly braces yourself. Hitting Ctrl + Shift + Enter puts them there.

These formulas are tough to master. MrExcel gets a headache just thinking about them. If I have a tough one to enter, I go to Tools>Wizards>Conditional Sum wizard and walk through the dialog boxes. The output of the conditional sum wizard is a CSE formula, so this usually can give me enough hints on how to enter what I really need.

Did you have to take BASIC in 11th grade with Mr. Irwin? Or, worse yet, did you get a "D" in linear algebra with Mrs. Duchess in college? If so, you are in good company and will shudder whenever you hear the word "array." - I run screaming in the other direction whenever anyone says array. I understand them, but this is Excel, I don't need arrays here!. The evil secret is that Excel and Microsoft call these formulas "array formulas". Stop - don't run away. It is OK, really. MrExcel has renamed them CSE formulas because it sounds less scary, and because the name helps you remember how to enter them - Ctrl Shift Enter. I only mention the real name here in case you run into someone from Microsoft who never had Mrs. Duchess for linear algebra, or, in case you decide to peruse the Help files. If you go to help, search under the evil alias of "array formula," but between us friends, let's call them CSE - OK?

Read Use a Special Excel Array Formula to Simulate SUMIF with Two Conditions.