Maximize Your Social Security Benefits

Schedule a Free Consultation with an RSSA Social Security Expert

Tools and Software

Dynamic Arrays When Using Excel

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

Read the rest of Jeff's post here

Comments (0)

There are no comments yet, Be the first to comment.
Log In
Your message is required.
Markdown cheatsheet.

Share This Article

Looking For More?

View all Tools and Software Articles

View More Articles

What's Trending?

Trending topics & tools for the CPA community

Start Planning for Tax Season with Offshore Staffing

Entigrity is the number 1 offshore staffing firm for CPA & accounting firms. Supporting 500+ clients nationwide. Hire experienced professionals starting at $10/hour. Start planning your staffing requirements for the coming tax season.

Schedule a Consultation

Everything You Need to Understand About Credit Card Processing Rates

Content originally appeared on the CPACharge blog.If there’s one universally agreed-upon sentiment about accepting credit cards in your business, it’s this: credit card processing rates......


3 Paperless Solutions for CPAs

Content originally appeared on the CPACharge blog.As our world becomes increasingly digital, CPA and accounting firms are eliminating their dependence on paper in favor of......