Apply Here Now – PPP Loan Program Ends May 31st
The Paycheck Protection Program provides small business loans with up to 100% forgiveness to help businesses impacted by COVID-19.
Funds are limited and will be disbursed on a first come, first served basis.
Dynamic Arrays When Using Excel

During the years we have been using Excel, we have come to understand that a formula calculates a value. Meaning, a single value. Right? Like … we write a formula, hit Enter, and the result is displayed in the cell.
Easy … that is how we have been using Excel for decades.
Multiple Values
But … formulas can return multiple values (an array of values).
Historically, these types of array formulas were somewhat complicated to create and required pressing special keys to enter. As a result, many Excel users did not take advantage of them.
However … times have changed!
In the most recent versions of Excel, writing and managing such formulas is greatly simplified and no special keys are required. This makes it much easier to take advantage of these types of formulas.
Spill Range
But … hang on Jeff!
If I write a formula that returns multiple results, what happens? Does just the first result appear in the cell? Or what? Here’s how that works.
When you write a formula that returns multiple values (an array), the results spill out of the formula cell into the adjacent cells (called the spill range). Wait … what? Crazy … I know! And super cool. And, it gets even better.
If the number of results returned by the formula changes over time, the number of cells used to display the results changes accordingly. That is, it is dynamic. Thus the term Dynamic Array.
Dynamic Array Series
This is the first post in the Dynamic Arrays series where we’ll explore this concept. Hang on tight, it’s gonna be a wild ride.
This post is written by Jeff Lenning, President of Excel University
Share This Article
What's Trending?
Trending topics & tools for the CPA community
Apply now: Paycheck Protection Program loans
First draw and second draw PPP loans now available. Funds are limited and will be disbursed on a first come, first served basis.
CPAdirectory and Funding Circle partner to help small business access Paycheck Protection Program loans.
How Business Owners Can Save on FICA Taxes
Many entrepreneurs think about taxes all year long. Most consider each quarter’s deadlines. There are due dates to meet for quarterly estimated tax payments, filing......
Tips for Managing your Business Taxes
Some of the easiest ways to get into hot water: If you forget one single step during your tax-paying year your, filing could be a......
Resources
Valuable information provided by our sponsors.
Buying and selling crypto, made simple
If you haven’t started building a crypto portfolio yet, now is a good time. We...
Frame Your CPA Certificate
It’s time to show off your credential. Shop now for a custom CPA certificate frame...
K-1 Analyzer
Thomson Reuters K-1 Analyzer software allows users to quickly extract and aggregate complex non-standard K-1...
Apply now: Paycheck Protection Program loans
First draw and second draw PPP loans now available. Funds are limited and will be...
U.S. Master 2021 Tax Guide® - Only $99
The nation's top federal tax resource has been updated to provide complete and reliable guidance...
Earn 15.5 CPE Credits While Becoming A Social Security Expert
Take our 5-module, online self-study course giving you Social Security and Medicare knowledge to support...
Comments (0)