SE3 Super Excel Modeling & Array Formulas Course
This course is for people who want to be the best of the best.
In all the years prior to 2021, I found myself telling majority of my SE2 course attendees that they might not want to take the SE3 course. Not because it's not useful, but because it's so much harder to understand for an average Excel formula user.
Now, in the post-2021 era, with the release of Dynamic Array Formula feature in Excel 2021 and MS 365 version, I'm happy and confident to recommend the SE3 course to majority of SE2 course students. Go for it! You can do it! Be the best you can be!
Wait! Array Formulas? Dynamic Array Formulas? What array formulas?!
"For most Excel users, they never heard about the concept of array formulas."
"For those people who are working as analysts, I dare to say more than half of them are not even aware of array formulas. If one claims 10% of the working analysts know how to use array formulas, I think that is an overestimate."
Above were what I had always been saying prior to year 2021. Now I believe it's going to change. Microsoft has made array formula so much easier to use. I predict array formulas will not stay as reserved for a small group of very logical thinkers. It will come down from the ivory tower, join the everyday lives of average Excel formula users.
Array formulas are not very well known, not because it's not useful, but because it's very difficult to understand, learn, and teach. With the Dynamic Array Formula feature, we can now complete some of the common and difficult tasks with shorter and simpler formulas. In all those years prior, we had to create those long and insanely hard to conceive array formulas to accomplish the same things.
In the past, if you looked at most Excel training organizations, they did not even teach array formulas. Some of them, when they did, they were teaching the official and book-smart version of array formulas, which are rarely used in real life for most people. Those are the people who studied Excel array formulas so they can teach array formulas. That is quite different from somebody, who has the need to use array formulas in real life, and learned array formulas, became good at array formulas, and then teach array formulas. This is what I consider who I am.
Now, there will be more training organizations offering Excel array formulas courses. I bet most of them will focus on the few dynamic array functions released in Excel 2021/MS 365 versions, and stay in the shallow water of array formulas field.
With the on-demand self-paced online course version of the SE3 Super Excel Modeling & Array formula course, you will gain deep array formula knowledge and learn various real life uses of array formulas, and use the new dynamic array functions in a much deeper and versatile manner.
How are array formulas different from the regular formulas?
In essence, array formulas are not about any new functions to learn. It is a way of using functions, including those functions you learned in the SE2 course.
The key difference between regular formula and array formula is that, regular formula calculates against single cell or array to return one single value, while array formula always calculates against array and return array. Array formulas are much longer. They do require more logical and abstract thinking.
Array formulas can do things that's impossible to accomplish with regular formulas.
Some of the things that can be done with regular formulas, but require creating supporting columns. With array formulas they can be done in one single cell formula, as those intermediate steps are done inside the array formula.
These capabilities are important to Excel models. Once the input raw data is dumped in, the model will automatically return output, without the need of creating supporting columns with formulas around the input range.
Imagine an ambitious young man, he travelled mountains and oceans, learned martial arts from all kinds of masters all over the world. He has developed certain moves with his fists, palms, elbows, feet, legs, knees, and they are all powerful. Finally, he learned another way of martial art to raise his internal power. He is still using his fists, palms, elbows, feet, legs, knees (functions), but his punches crush wood, his kicks break rocks, everything becomes more powerful.
In his first stage (regular formula), his punches and kicks output single value and may need to use swords or spears (supporting columns) to fight; in the second stage (array formula), his punches and kicks output powerful array, without needing external weapons (supporting columns), as his fists and legs already carry the power of a sword or spear internally, and more.
In the pre-Excel-2021 era, most functions are designed to return single values. The way to create regular formulas and array formulas are quite different.
In the post-Excel-2021 era, Microsoft has added few dynamic array functions that are designed to return arrays. With the Dynamic Array Formula feature, there is no distinguishable difference in creating regular formulas and array formulas. However, their powers are dramatically different.
Who is this course for?
For a small percentage of SE2 course students who find SE2 course very difficult, the SE3 course may be too hard for you. Do not take this course.
For the rest, if you are working as an analyst, or analyst is one step on your career path, you have very high expectation of yourself, and you do have a logical mind, I would encourage you to take the SE3 course.
You could be a manager, senior manager, director, project manager, specialist, or having any other job title, as long as your work involves lots of data crunching, the array formula skills will benefit your career a big deal.
I will guide you from very simple array formula concepts and exercises, step by step, one built upon another, in a short time, when you look back, you will be surprised how far you have gone.
You have to have the knowledge and skills from the SE2 Super Excel data Processing & Analysis course though, before you embark on the journey of array formulas.
If you do not have the knowledge and skills covered within the SE1-A and SE1-B courses, there is no point to learn SE3 yet. You will have no stage to use the array formulas.
Completion of the SE3 course will make you the best of the best among Excel formula users. To most people who do not understand array formulas, you will be a God like being to them. With your array formula skills from the SE3 course, you are making impossible to possible.
SE3 Super Excel Modeling & Array Formulas Course Outline
24-hour Hands-On Course
Overview
Business planning has become more complex while the turnaround time is getting shorter. Your business planning has to be more automated to meet the expectations. Excel could help you to automate many of the planning processes if you know the more advanced functions, array formulas, and the right structures to link the inputs, variables, formulas, and outputs together as integrated models.
This hands-on course will teach you the more advanced functions and array formulas that are necessary for complex modeling, the general structure of effective models, and common techniques used in modeling process. Combining with real case practices, you will gain the skills and ability to build Excel models and automate your business planning processes.
Who Should Attend
This course is designed for people who have advanced level Excel skills, wish to be able to build effective Excel models with more advanced functions and array formulas to automate business planning.
Attendees should be able to use VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, IF, IFERROR, AND, OR, LEFT, RIGHT, MID, SEARCH, and SUBSTITUTE functions individually as well as nest them together in multiple layers comfortably before taking this course. These pre-requisite knowledge and skills are covered in the SE2 Super Excel Data Processing & Analysis course.
Topics Outline
Module 1 – Build the foundation for all array formulas; use Boolean logic to build array formula with multiple criteria
- Array formula in its simplest form
- MAX function against an array
- Combining IF array formula with MAX function
- Combining multiple layers of IF array formulas with SUM function
- Understanding Boolean logic
- SUM with AND and OR criteria in one dimension
- Understanding two dimensional array calculations
- SUM with AND and OR criteria in two dimensions
Module 2 – Summary reporting model with Boolean calculations nested inside SUMPRODUCT; essential LARGE, SMALL, IF array formulas combination for multiple values extraction purpose
- Change formulas in batch
- Understanding SUMPRODUCT function
- Use SUMPRODUCT instead of SUM array formula
- Summary reporting model
- Array formula to deal with dates
- Look up multiple items without supporting column
- Combining IF array formula with LARGE and SMALL functions
- Create dynamic ranges with INDIRECT function
Module 3 – Functions to create dynamic ranges and dynamic charts; structuralize unstructured text; change model variables and inputs with control buttons
- Summarize multiple sheets data with 3-dimensional formula
- Create dynamic charts with OFFSET function and range names
- Structuralize unstructured text with text manipulation model
- Change model variables with spin button, option button, and scroll bar
- Change model inputs with cascading dropdown lists
Module 4 – Powerful array formulas to extract all records meeting criteria; exception reporting model
- Array formula to look up and return multiple records
- Array formula to count unique records from transactional data
- Array formula to return unique records from transactional data
- Exception reporting model
Bonus Module A – New functions in Excel 2021 onwards versions and Microsoft 365 version
- UNIQUE, SORT, SORTBY function
- SEQUENCE, FILTER, TEXTJOIN function
- Retrieve Number Characters with Nested Formula
Bonus Module B – Dynamic Array Formula feature impact to Excel functions
- SUMIFS spilled array formula
- COUNTIFS spilled array formula
- VLOOKUP spilled array formula
- XLOOKUP spilled array formula
* Please note in the on-demand self-paced online version of this course, it also includes a bonus module covering XLOOKUP and XMATCH function. If you took the SE2 Super Excel Data Processing & Analysis course in the years prior to Excel 2021 version release, you are covered and have all you need to proceed with SE3 Super Excel Modeling & Array Formulas course.
I attended Steve’s live online Super Excel Modelling & Array Formulas course in the summer of 2020. I also took an in-person course with Steve approximately 8 years ago, which allowed me to successfully pass an Excel test for a senior financial analyst position and then be able to handle large amounts of data processing and analysis. I would say the online Zoom class was just as effective as taking in person – Steve thoroughly explained the concepts we were going to learn and walked us through the practice files before we tested our knowledge on the files individually while Steve took our questions as they came up. I would also say that the live class with Steve was worth the price for a few reasons – other online but non-live classes do not provide the accountability and direct interaction to ask questions and would take me way more time to complete. As a working professional the relatively short amount of time to complete the course was worth it. The course advanced my data analysis using Excel to provide data summaries that pivot tables and lookup formulas can’t always accomplish.
Bryan M.
Toronto Canada
Thank you very much Steve it was a pleasure meeting you and learning about an aspect of Excel [Array Formulas] that I would deem game changing that I was not aware of. I found your teaching methodology to be very effective as well as having the benefit of asking questions and getting instant feedback to create a very custom experience that I find lacking in online courses in general. Truly a pleasure and thank you.
David H.
The Super Excel Modeling & Array Formula course was really helpful to me as I could implement it at work on my reports. I am spending half the time to do my sales report.
S. Mir, CPA, CMA
I would say the Super Excel Modeling & Array Formula course raised my knowledge to a different level. I recently found time to go over these again and I do believe it was worthy of attending this course. Without these new concepts I am still working at a lower level of Excel! Actually I have tried to use some concepts like INDIRECT to fulfil some automation in my sales report: I had to change data scope of last 52 weeks manually in the past, now I just change one cell then it pops out new forecast in a second! I think more important for me is that I learned so many new concepts which will open a window for future tasks – I might think in a different way of doing my work.
Michael X.
As a business owner I found Super Excel Modeling & Array Formulas is a really useful course, saved me lots of time to analyze company data, and enabled me to better understand company performance by using those Excel skill. Also Steve is very nice instructor, he will answer all your Excel questions from your daily work. Love this course!
Edison H.
When You Sign Up SE3 On-Demand Self-Paced Online Course, You Get:
Let Me Breakdown What You Are Getting
and Show You How They Help You to Master the Right Excel Skills
Excel Training
Training Videos
The recorded videos are just like how this course is taught in a live in-person setting, often with additional explanations to address various potential questions or issues.
This is an Excel course taught in person in classroom setting for many years. It is tested effective and fine-tuned in many years.
The common and typical questions that attendees would ask are already incorporated in the recorded teaching videos. Also some of the common formula errors are also demonstrated in the teaching videos.
Training Excel Files
Practice sheets and answer sheets
All files have answer sheets corresponding to each of the practice sheets.
Bonus materials and associated answers
12 out of the 22 Excel files have bonus materials. All bonus questions have their own answer sheets provided in the files.
They either help you to apply what you learned to a different scenario, or to springboard you to a higher and broader application by adding a little bit twist. They could be new materials but very related to the topic you just learned.
All of the bonus materials are to reinforce your understanding, expand your application to new scenarios, broaden your knowledge base, intrigue your curiosity to creative ways of using the functions you learned.
Course eBook
This course has very detail course eBook to guide you working through the exercises. There are lots of screenshots and illustration to explain the concepts. It is written in conversational style. When you read them, you feel just like sitting across from me, listening to the teaching. You can see clearly the logic in the thought process of developing the solutions.
Instructor Support
Most online Excel courses are just one-way communication from instructor to students. Students are left alone with no place to get help.
With this online course, you could ask questions at the comment section of each lesson, and I will answer each and every single one of your questions. Your fellow students can also see your questions and may jump in to share their perspectives too.
This online course format provides not only two-way communication between you and me, but also discussions and interactions among fellow students.
Excel Coaching
Bonus: 90-Day Access to Super Excel Coaching Community
In this online community, you will get Excel coaching support.
With what you learned in the course, you have the knowledge and skills to solve more problems than you realize. This course provides many examples of ways using those functions and their combinations, but they won't cover all the scenarios you face in workplace.
When you face Excel challenges at work, you can ask me in the online forum to get help. Each and everyone of your questions will be answered.
If the problem can be handled using what you learned from the course, you will be coached to apply that to solve the problem.
If the problem cannot be handled using what you learned from the course, you will receive guidance on what to learn to solve the problem.
This is also a good opportunity to learn from each others. Often times, a question asked by one student, is the same question many other students wanted to ask, or will ask later on.
Based on the questions asked by students, I may hold live Zoom sessions in the community to answer questions that are hard to discuss via writing.
Bonus: Tapping Into the Power of Your Higher Self
This is a series of short videos where I share with you some techniques I have used to improve the quality of my life.
Our emotional state determines the qualities of our lives. Getting richer does not necessarily relive or reduce the stress we feel in our everyday lives.
There is a part of us that has deeper wisdom. I call it the inner self, the true self, or the higher self.
In our everyday life, we are busy "doing" all kinds of things, and often disconnect with the inner self or higher self.
There are lots of ways to connect with the higher self and transform us from a negative emotional state to a more positive emotional state.
I have been pursuing a deeper understanding about life since 2014. Along the way, I have learned and benefited a lot.
Through these sharing, I hope to make a contribution to your joyful and fulfilling lives.
