Table Of ContentCtrl+Shift+Enter
Mike Girvin
Holy Macro! Books
PO Box 82 Uniontown, OH 44685
Ctrl+Shift+Enter: Mastering Excel Array Formulas
© 2013 Holy Macro! Books and Mike Girvin
All rights reserved. No part of this book may be reproduced or transmitted in any form or by
any means, electronic or mechanical, including photocopying, recording, or by any
information or storage retrieval system without permission from the publisher. Every effort
has been made to make this book as complete and accurate as possible, but no warranty or
fitness is implied. The information is provided on an "as is" basis. The authors and the
publisher shall have neither liability nor responsibility to any person or entity with respect
to any loss or damages arising from the information contained in this book.
Author: Mike Excelisfun Girvin
Editor: Kitty Wilson
Layout: Tyler Nash
Published by: Holy Macro! Holy Macro! Books, PO Box 82 Uniontown, OH 44685, USA
Printed in USA
First printing: July 2013
Tech Editor: Bob Umlas
Cover Design: Shannon Mattiza 6’4 Design
Indexing: Nellie J. Liwam
Distributed by: Independent Publishers Group, Chicago, IL
ISBN: 978-1-61547-007-5 (print)
ISBN: 978-1-61547-109-6 (mobi)
ISBN: 978-1-61547-209-3 (pdf)
ISBN: 978-1-61547-329-8 (epub)
Library of Congress Control Number: 2013938519
About the Author
In 2011, Mike Girvin published his first book and DVD, titled Slaying Excel Dragons.
Since 2008, he has video blogged at the excelisfun channel at YouTube, which contains more
than 2,000 Excel how-to videos and logs about 30,000 views per day. Since 2002, he has
taught quantitative business classes at Highline Community College, using 100% Excel in all
classes so that students get a working-world-ready business education. In the 1990s, he ran a
boomerang manufacturing company called Gel Boomerangs.
Mike still remembers the awe he felt the first time he changed a formula input for an Excel
income statement, and the whole thing updated. Since that inspiring, life-changing moment,
Mike has tried to create the same feeling of Excel awe in every class, video, and book he
has created.
Mike has won a number of awards for Excel video blogging, including the 2011 Highline
Faculty of the Year Award, the 2012 Central Washington University Achieving the Dream
Award, and the 2013 Microsoft Excel MVP Award.
Acknowledgements
Thanks to Steve Kavanaugh, the first person to show me Excel in the 1990s. Thanks to Bill
"MrExcel" Jelen for inspiring me to learn Excel well with his books and podcasts, and
thanks also to Mr Excel for noticing the videos I had posted on YouTube and inviting me to
make videos with him and write books for his company. Thanks to Dusty Wilson for helping
me with my matrix algebra. Thanks to the editors, Bob Umlas and Kitty Wilson, for helping
me to make this a good book.
But wait…
The REAL thanks that I must give is to all the amazing Excel masters at the MrExcel
Message Board. The MrExcel Message Board is where I really learned about advanced
formulas and array formulas. This amazing source for information on how formulas really
work is incalculably valuable. This book is simply my attempt to put the ideas that I have
learned from the MrExcel Message Board masters together in a logical order. I’d like to
thank the people from the MrExcel Message Board and YouTube comments section who
have helped me over the years and who are the source of the ideas in this book.
Specifically from the MrExcel Message Board, I must say a few words about the masters I
have learned from:
• Thanks to Aladin Akyurek for all the amazing formula knowledge and always helping
me and many others to "robustify" our formulas. As so many people write, it all starts
with Aladin!
• Thanks to barry houdini for some amazing date formulas and other cool formula stuff.
• Thanks to Charles "Fast" Williams for his amazing articles on how to speed up
calculation time.
• Thanks to Domenic for always answering my detailed array formula questions.
• Thanks to DonkeyOte for amazing formulas, including two awesome reverse lookup
formulas!
• Thanks to pgc01 for amazing statistical formulas and VBA!
And thanks all the people who have answered my posts at the MrExcel Message Board:
Aladin Akyurek, Andrew Poulsom, barry houdini, Bill "MrExcel" Jelen, Charles "Fast"
Williams, circledchicken, Colin Legg, Dave Patton, Domenic, DonkeyOte, HOTPEPPER,
jeffreybrown, Jon von der Heyden, Marcelo Branco, Norie, Peter_SSs , pgc01, Richard
Schollar , Ron Coderre, Smitty, T. Valko, Tom Urtis, VoG, xenou, Yogi Anand, Teethless
mama, BigC, Boller, Beate Schmitz, bosco_yip, c_m_s_jr, crimson_b1ade, Domski,
edokhotnik, Erdinç E. Karaçam, erik.van.geit, Fazza, gavinkelly, GlennUK, HalfAce, Haseeb
Avarakkan, hiker95, hkaplan2, iknowu99, iliace, James006, jasonb75, jbeaucaire, jindon,
jonmo1, krn6264, Latchmaker, lenze, Lweiy, Makrini, Mark O’Brien, Matty, MickG,
mikerickson, MrKowz, mvptomlinson, NateO, nbrcrunch, Oaktree, PaddyD, PCL, pto160,
RalfA, ravishankar, robind21283, RomulusMilea, RoryA, sanrv1f, schielrn, scottylad2,
shemayisroel, shg, sous2817, stanleydgromjr, Starkman, steimel386, texasalynn, timorrill,
Travis, tusharm, UniMord, Weaver, wigi, and wsjackman.
Thanks also to a few amazing YouTube Excelers who have helped me tremendously:
circledchicken, krn14242, hamy72, AThehos, SchultzesBlues, and dmr450.
Go, Excel online team!!!
Dedications
To Amy Girvin, my wife, who puts up with all this "Excel stuff."
To Dennis Ho, my 17-year-old son, who likes to hang with his friends and play tennis.
To Isaac Girvin, my 7-year-old son, who likes to race BMX, play baseball, and go on
adventures.
Introduction
Why in the world would you read a book about array
formulas?
Seriously, array formulas are rarely needed and can be insanely difficult. Here are some
potential reasons you might want to read a book about them:
• 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 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.
Why are array formulas important tools to have in your
Excel toolkit?
Here are some reasons array formulas may be useful to you:
• Sometimes using array formulas is the only way to accomplish a given task. See
Figure IN.1
• Array formulas can replace intermediate steps and provide a single-cell solution. See
Figure IN.2.
• In general, the advantage of using formulas over using Excel features such as sorting,
filtering, and PivotTables is that when you change a formula input, the formulas update
instantly. By learning about array formulas, you increase your range of formula skills
and are less limited when it comes to building Excel solutions. See Figure IN.3.
• If you understand how array formulas work, you understand the true beauty and power
of Excel formulas! See Figure IN.4.
Figure IN.1 If you can’t use a PivotTable, you don’t have the AGGREGATE Excel 2010 function, and you need to
copy the formula down a column (you can’t use DMIN function), using the array formula in cell E3 is an efficient
solution. See how to create this formula in Chapter 4.
Figure IN.2 If you want to avoid using the lookup formula in column D before calculating the total costs, you can
use the array formula in cell G7 as an efficient single-cell solution. See how to create this formula in Chapter 6.
Figure IN.3 When the criteria for record extraction changes often, it can be beneficial to use formulas rather than
the Filter feature. The array formula in cell G8 is an efficient solution for extracting with three criteria. See how to
create this formula in Chapter 15.
Figure IN.4 As shown in cell B4, this array formula does the seemingly impossible, counting how many "Friday the
13ths" there are between a start date and an end date. With the power and beauty of array formulas under your belt,
almost anything is possible with formulas! See how to create this formula in Chapter 10.
Why would Mike excelisfun Girvin write a book about array
formulas?
Here are some potential difficulties with writing a book about the topic of Excel array
formulas:
• It’s a niche topic.
• It’s a very difficult topic.
• Microsoft doesn’t provide much documentation about array formulas.
• Excel users disagree about what an array formula is.
• There is no systematic listing of all the aspects of array formulas, and there is not a
good set of rules or guidelines for array formulas.
Why would I, Mike excelisfun Girvin, write a book about Excel array formulas—especially
when there are hundreds of Excel masters who are much smarter than I about Excel and
Excel array formulas? As I always say at YouTube, "I am just a guy having fun with Excel!"
I’ve written this book because I have long wished for a book that systematically lists the
aspects, elements and guidelines for array formulas. What I have done in this book is just
gather up what I have learned over the years from places like the MrExcel Message Board
(mrexcel.com/forum) and other Excel sites, and I’ve tried to present the details I have
gleaned in a systematic way. I do not promise that this book tells the complete story of Excel
array formulas or even that it is 100% correct, but I do promise that I have done my best to
present what I have learned about Excel array formulas.
About the Book
This book is similar to the first book I wrote, Slaying Excel Dragons, in that it is a story
from beginning to end about efficiencies in Excel.
Whereas in Slaying I wrote about how to build a foundation of basic skills across all of
Excel, this book concentrates on a set of guidelines for how to create efficient array
formulas. That said, this book actually covers three topics: formulas, advanced formulas,
and array formulas. Because an array formula is just a more narrow type of advanced
formula or formula, much of what this book says can be applied across all three categories
of formulas. This is good news because it means that this book is not just about building
efficient array formulas, but it is about building efficient solutions using formulas. Further,
throughout the book I compare and contrast a given potential array formula solution against
other Excel features, such as filtering, PivotTables, and non-array formulas. This will help
you understand the context or situation in which array formulas may be the most efficient
solution.
Finally, this book is not just a listing of cool array formulas or of readymade solutions.
Instead, it is a story that starts at the point of "no knowledge of array formulas" and builds,
piece by piece, toward an endpoint of "now we have a set of guidelines and rules we can
use to build our own array formulas." To this end, this book gradually builds a set of "Array
Formula Efficiency Rules," one by one, which Chapter 14 lists all in one place. Then, in
later chapters, you’ll be able to create truly mega Excel formulas. In the early parts of this
book, I give a lot of detail and move slowly through each topic, especially about how each
individual part of a formula is working. As the book moves along, I give fewer details,
move more quickly and show more examples.
A final note about this book: I think that I have done a less than stellar job with the writing in
this book. I believe that sometimes as I was writing I got lost in the complexities of the topic
and wrote in a somewhat less than optimal manner. However, if you can wade through the
less than stellar writing, there are many valuable formula lessons in this book that can
improve your formula creation abilities.
Files That Accompany the Book
As you read this book, you’ll want to follow along with the examples, in many cases trying
them out yourself. To make this easier for you, I’ve posted the Excel files that contain the
finished examples from this book online. You can get them from the "Ctrl+Shift+Enter:
Mastering Excel Array Formulas" section of this website:
http://people.highline.edu/mgirvin/excelisfun.htm
At this site you can download a zipped folder named FilesForCtrlShiftEnterBook. After you
download the zipped folder, you will have to unzip the folder to get access to the files inside
the zipped folder.
Inside the downloaded folder is a file named CtrlShiftEnterBookFinishedFile.xlsm. This file
has all the finished examples from this book. To find the worksheet in this workbook that
relates to the example you are interested in, simply navigate to the worksheet that has the
same name as the figure number in the book. For example, if the figure number is IN.1, the
sheet name you should look for is IN.1.
In addition to the workbook with the finished examples from the book, I’ve made available a
number of other files with large data sets that have timing results for different formulas. In
each section of the book that discusses timing results, I’ve provided the name of the
corresponding file or files you should use.
Note: If you are used to watching excelisfun videos at YouTube or if you have read the
book Slaying Excel Dragons, please note that there is no Start file for this book. This
is because most of the examples in the book are already completed for you. If you want
to try the formulas as shown in the book, a good strategy would be to copy the file with
the finished examples and then delete the formulas from the cells before you try the
formulas on your own.
Note: I have published both a book and a DVD titled Ctrl+Shift+Enter: Mastering
Excel Array Formulas. The files that go along with this book and that DVD are not the
same files. If you are reading the book, download the files that go along with the book.
The Ctrl+Shift+Enter: Mastering Excel Array Formulas DVD
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
