Arrays in Microsoft Excel are a very powerful, yet generally unknown tool. Using array formulas allows you to perform tasks that may currently take more than one step and consolidate them into one step, improving efficiency.

Array formulas make it easier to perform tasks such as comparing and evaluating lists, mathematical operations, quantifying errors and identifying duplicates in data.  Excel also has built in functions that may be used to work with arrays.  These functions include SUMPRODUCT, the combination of the INDEX and MATCH functions and the AGGREGATE function. These functions will also be discussed.

Learning Objectives
  • Discover how to use array formulas in mathematical operations
  • Discover how to create and use defined range names
  • Recognize the MATCH function and its uses in comparing lists 
  • Explore how to use of ISNA and ISERROR to ignore and quantify errors in data
  • Recognize the use of COUNTIF as an array function to identify duplicates in data
  • Recognize the SUMPRODUCT function and its uses when multiplying arrays
  • Recognize the combined INDEX and MATCH function as a tool to insert information or match two lists
  • Identify the uses of the AGGREGATE function
Last updated/reviewed: January 23, 2023
7 Reviews (21 ratings)

Reviews

5
Anonymous Author
Really great introduction to arrays with multiple examples and explanations to guide you through the formulas. Gave multiple approaches to solving problems with reasons to use one or the other.
4
Anonymous Author
This training gives a good overview of arrays and some of the more common functions to use with them. It was presented in an organized manner and the support data was great.
5
Member's Profile
I loved the fact there was no instructor and I was able to work thru the material at my own pace with the excellent instructions and tools provided.
5
Anonymous Author
Fantastic review course - I had always struggled with arrays, but felt this was explained in an easy to digest manner.
5
Member's Profile
Really handy overview of array tools and formulas. Definitely need to keep this in my back pocket!
5
Anonymous Author
This course contained some excellent Excel formulas and solution ideas.
5
Anonymous Author
The instructor makes the material easy to understand and remember.
Prerequisites
Course Complexity: Intermediate

No advanced preparation or prerequisites are required for this course.

Education Provider Information
Company: Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact: For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Instructor for this course
  Course Overview CPE
Course Syllabus
COURSE MATERIALS
  ARRAYS: A Powerful Excel ToolPDF
  Workbook: ARRAYS: A Powerful Excel ToolXLSX
REVIEW AND TEST
  REVIEW QUESTIONS quiz
 FINAL EXAMexam