Discover How To Create A CSE Formula To Build A Super Formula With This Excel Tutorial

Strategy: The typical strategy is to add a new column with price times quantity and add that up. However, this is not necessary if you use a type of super formula that I call the CSE formula.

=SUM(E2:E564*F2:F564)

If you’ve been using Excel for a while, you will think that this looks like it will not work. In fact, if you enter the formula, you will get a #VALUE! error, confirming that it does not work, as shown in Fig. 588.

However, if you know the secret you can still make the formula work. Edit the formula by hitting F2. Instead of using Enter to finish the formula, hold down Ctrl+Shift and then hit Enter.

CSE=Ctrl+Shift+Enter

As shown in Fig. 589, miraculously, Excel does 563 multiplications and then adds them up to give you a result.

In the formula bar above, you will note that there are curly braces around the formula. You do not enter these braces. Excel adds them when you use Ctrl+Shift+Enter.

Additional Details: Most people will only have an occasion to use this formula once a month. I found that I could never remember the keystroke combination, so I renamed these formulas “CSE” to help me to remember Ctrl+Shift+Enter. If you need to search Microsoft Help on the subject, check under the name “Array Formulas”.

Summary: One single CSE formula can replace hundreds or even thousands of intermediate formulas.

Commands Discussed: Control+Shift+Enter

See all Microsoft Excel tips

Images

References and Additional Resources

If you’re looking for more tips and tutorials, check out 91 Tips for Calculating With Microsoft Excel . This collection of easy-to-follow guides shows how to customize charts and graphs, different ways to make complex spreadsheets easier to update, and even how to play games like Craps in Excel.

Other Resources:

Microsoft Excel Official Site, https://office.microsoft.com/en-us/excel/

Bill Jelen, Microsoft Excel 2010 In Depth, Available from Amazon.com .