Excel – Dynamic Arrays
Registration Fee – $69
July 6, 2023 – 9:00 am – 12:00 Noon PST – (3.0 CPE)
Level: Basic – Basic/Intermediate
Prerequisites: A good understanding of Excel basics
Advance Preparation: None
Field of Study: Technical
Presented by: Jim Colville, CPA-CFE, Retired
Handout Materials: All files and notes available for download. See below
Description:
Excel and other spreadsheets began back in the 1980’s and were and still are incredibly easy to learn and use. Their primary features were columns and rows, formulas and functions. Even powerful pivot tables are an extension of these features.Very generally, think of Excel as one formula, one cell. The formula must be in the cell where you want the result to show. It’s easy to copy the formula, once entered, to other cells. With Dynamic Arrays, you select the source data and enter the formula. Excel spills the results to the appropriate cells upon pressing Enter. Think of Dynamic Arrays as the “new and improved” way to work with Excel.
Dynamic Arrays are built into Excel 365 and other new versions and are not available the traditional Excel versions, such as Excel 2016 and 2019. Since they are built-in all that’s needed by the user is to use them. This session will cover this in detail.
The new Dynamic Array functions to be covered:
- Traditional Excel, enter a formula and copy it.
- Excel 365 Dynamic Array-Enter the formula, it copies upon pressing enter
- Dynamic arrays are built in to all formulas and functions. You’ll learn how to use them
- New dynamic array functions have been added:
- Filter
- RandArray
- Sequence
- Sort
- SortBy
- Unique
- XLookup
- XMatch
- Massive simplification-so much is drastically simplified. Ten times the power at one-tenth the time.
- Embedding or chaining the formulas or functions unleashed even more power.
Dynamic Arrays are now built into all of Excel, the above is just the new Dynamic Array Functions added. Working with Dynamic Arrays in the legacy functions will also be covered.
Today’s presentation is a follow-on of Jim’s presentation at the IMA 2022 Annual National Conference & Expo (Live) in Austin, TX on June 14, 2022.
All the files used in presenting these topics will be available for download. The files will contain instructions on how to use the concept which includes the presenter’s notes. The files will remain available online for about two weeks after the webinar.
Jim Colville, Bio
Jim began working with spreadsheets with Lotus 1-2-3, v1a and then with Excel v3, and soon thereafter v4. He learned very early the power of Excel and how to be super productive in solving problems, creating business solutions and enhancing reporting in his CPA practice.
Seeing the business opportunity in educating others in using Excel he began his own PC training company teaching over 20,000 users over a period about four years. The training evolved into a consulting company offering specific solutions to companies and corporations by combining training combined with project challenges which resulted in powerful solutions. A by-product of this is Jim’s Zero Time Reporting, once the data is entered (or imported) the report is done.
Jim has authored well over Excel 500 courses, both as training materials and solution based Excel products for companies, corporations and nonprofit organizations. Many of these later became the foundation of web based articles and solutions under the the name of Info-Stat.com . With the saturation of Internet based Excel websites and Jim’s commitment to his CPA business the Info-Stat website was allowed to sunset. With his retirement from his CPA practice in 2019, Jim has revitalized his web-based activities in by writing articles, creating Excel files that offer solutions, revitalizing Info-Stat.com concept with the new website, www.Excel365Solutions.com and as well as educational webinars through AEI. Excel has changed dramatically over the years and the new course and webinar offerings significantly modernize the Excel materials including, but not limited to, business and artificial intelligence.
Of major significance is the evolution of Jim’s education activities from a local San Diego PC training company to a national and world wide based company with Internet based products, both website and webinar offerings.