Ctrl + Shift + Enter

July 2013

Command The Power of the 'Array Formula' in Microsoft Excel!

You won't believe the formula power unleashed by pressing Ctrl+Shift+Enter!

Bill Jelen, MrExcel

category: Functions, Formulas
covers: Microsoft 365, Excel 2010 - 2016

Product Details
• 308 Pages
• Publisher: Holy Macro! Books
• ISBN: 978-1-61547-007-5
• PDF ISBN: 978-1-61547-342-7

Command The Power of the 'Array Formula' in Microsoft Excel!

Array Formulas are a powerful tool in Excel. An Array Formula works with a series of Data Values instead of a single Value. Excel Array Formulas are mysterious, even to people who use Excel 40 hours a week. Start with the secret keystroke that must be used to finish an Array Formula: Ctrl+Shift+Enter. Then, you have to know which functions will work with Arrays and which will not. Once you get a grasp on Array Formulas, you will find they solve problems that regular Excel Formulas can not solve, or would require hundreds of thousands of regular Formulas.

Although elusive, Array Formulas can be used to solve everyday Excel problems. If you need to figure out how many times your birthday fell on a Friday, an Array Formula can solve that. If you need to return a unique list of sales reps from a Database, an Array Formula can solve that.

How does this book approach Array Formulas?

This book systematically lists the aspects, elements and guidelines for array formulas. Array formulas can be insanely difficult. But there are times when an array formula is the only way to accomplish a task. Array formulas can beat filtering, sorting and pivot tables because array formulas will automatically update when the input cells change.

Why should you read this book:

• You love Excel magic and wizardry. Your idea of fun is making Excel do the impossible with a formula.
• You know how to create formulas and want to take the next leap in Excel, into the upper echelon of creating advanced formulas.
• You cringe when a co-worker sends a workbook with formulas wrapped in curly braces.
• You have heard about array formulas and know that in some situations, they are the most efficient solution, but you have no idea how to create them.
• You use array formulas and are aware of some of the drawbacks they present (such as slow formula calculation time), and you would like to learn how to create more efficient array formulas.

Note

The DVD 'Ctrl+Shift+Enter: Mastering Excel Array Formulas' is a separate product from this book. The two products cover the same broad topics related to creating efficient array formulas. However, the video has kinetic visuals of how array formulas work and less detail than the book. The book has a more complete story and systematic presentation of array formulas. If you really want to master array formulas, study both the book and the DVD.

• Introduction
• Chapter 1: Formula Basics
• Chapter 2: Introduction to Array Formulas
• Chapter 3: Math Array Operations
• Chapter 4: Comparative Array Operations and Aggregate Calculations with One or More Conditions
• Chapter 5: Join Array Operations
• Chapter 6: Function Argument Array Operations
• Chapter 7: Array Constants
• Chapter 8: Array Formulas That Deliver More Than One Value
• Chapter 9: A First Look at Array Functions: TRANSPOSE, MODE.MULT, and TREND
• Chapter 10: The Amazing SUMPRODUCT Function (and SUMIFS, Too)
• Chapter 11: Boolean Logic: AND Criteria and OR Criteria
• Chapter 12: When Is an Array Formula Really Needed?
• Chapter 13: Dynamic Ranges with the INDEX and OFFSET Functions
• Chapter 14: Array Formula Efficiency Rules
• Chapter 15: Extracting Data, Based on Criteria
• Chapter 16: The FREQUENCY Array Function
• Chapter 17: Unique Counting Formulas and the Power of the FREQUENCY Function
• Chapter 18: The MMULT Array Function
• Chapter 19: Extracting Unique Lists and Sorting Formulas
• Chapter 20: Conditional Formatting with Array Formulas
• Chapter 21: Data Tables
• Chapter 22: The LINEST Array Function
• Chapter 23: Can You Figure Out How the Huge Array Formula Works?
• Conclusion

MrExcel Bookstore

eBook from MrExcel.

MrExcel Bookstore

Print book from MrExcel.

Amazon

Print book from Amazon.

Related Products

April 2012

Think that you know everything that there is to know about Microsoft Excel? Guess again. If you're ready for the next level of Excel, then "Excel Outside the Box" is for you! 'Outside the Box' is an in-depth look at 54 amazing techniques from Excel MVP Bob Umlas.

September 2018

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.

June 2022

Excel Worksheet, Power Query, Power Pivot, Power BI. Calculations, Analytics, Modeling, Data Analysis and Dashboard Reporting for the New Era of Dynamic Data Driven Decision Making & Insight!