Better Sheets

Learn ↓

Free Resources ↓

Member LoginStart 7 Day Free Trial

How To Use ARRAYFORMULA()

Description

Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.

Common questions about the ARRAYFORMULA formula include:
How do I use the ARRAYFORMULA formula?  carefully

What are the benefits of using the ARRAYFORMULA formula? Add formulas to your top row without having to type them out for every row.
The ARRAYFORMULA formula can be used appropriately to apply a single formula to an entire range of cells. It can also be used to create a dynamic array of values from a single formula.

What is the purpose of the ARRAYFORMULA formula? to use non-array functions with arrays.

What data types can be used in ARRAYFORMULA formulas?  Any range

How can ARRAYFORMULA be commonly mistyped?
The ARRAYFORMULA formula can be commonly mistyped as ARRAYFORMULAS, or ARRAYFORMULAE, or ARRAY, or ARRAY FORMULA, or ARRAY FORM, or ARYFORMULA, or ARRYFORMULA.

How can the ARRAYFORMULA formula is used inappropriately?
Some common ways the ARRAYFORMULA formula is used inappropriately include using it to apply a formula to a single cell, using it to apply a formula to a range of cells that is not the same size as the range of cells the formula is being applied to, and using it to apply a formula to a range of cells that is not the same size as the range of cells the formula is being applied to.
If you're looking to use ARRAYFORMULA to get an array from another place and put it into a column/row, then you can instead use curly brackets around an array. use: { } around a range is far easier and less to type.

What are some common pitfalls when using ARRAYFORMULA?
Common pitfalls when using the ARRAYFORMULA formula include forgetting to include the array when entering the formula, forgetting to include the range of cells the formula is being applied to, and forgetting to include the range of cells the formula is being applied to. An ARRAYFORMULA won't magically know what you're trying to do. If you misuse it, it sometimes will come up with an error.

What are some common mistakes when using ARRAYFORMULA?
Common mistakes when using the ARRAYFORMULA formula include forgetting to include the array when entering the formula, forgetting to include the range of cells the formula is being applied to, and forgetting to include the range of cells the formula is being applied to.

Common misconceptions people might have with the ARRAYFORMULA Formula include:
Thinking that the ARRAYFORMULA will always provide a quicker outcome. Using Curly brackets is far easier if you want something quick to reference an array.
That the ARRAYFORMULA is a universal formula that can be used for any calculation. That the ARRAYFORMULA can be used to apply a single formula to multiple sheets in a workbook at the same time.

Learn Every Google Sheets Formula
Better than any other Google Sheets course, At Better Sheets and specifically:  https://bettersheets.co/formulas, I have everything you need to take your Google Sheets skills to the next level. 

From ABS() to YIELD(), Better Sheets has it all. 

There’s no shortcuts to becoming a Google Sheets pro! Learn more here at Better Sheets

Watch the magic for yourself. Video tutorials at every turn here at Better Sheets.

Your spreadsheets will never be the same again.

How To Actually Use ARRAYFORMULA() in Sheets

ARRAYFORMULA(array_formula)
Google Docs Help

21Better Sheets Tutorials

Replace 1,000 Formulas Instantly
Learn a cool trick you can do with the ArrayFormula. If you don't know what the ArrayFormula does, I show you how to use it in the first few minutes of this video.  Then show you how to use it better.
Beginner
Create Instant QR Codes
Learn how to use a free Google API to create QR codes in sheet to any URL. And we'll learn how to make them magically appear as you enter domains.
Turn Titles Into Lower Case Hyphened Slugs
Create a slug maker inside your sheet. We'll take any title with any case, even if it's wrong. Our sheet template will lower case the whole line, and replace any spaces with hyphens. This way we can enter any kind of title we want and get a hyperlink slug out for use in blogs or cataloging.
Beginner
Deep Inside Dark Habits Google Script
Go inside this apps script and sheet. See how it's designed and created.
Kristy Asks How to Get Percentages from Scores
How to figure out a percentage score when our reporting numbers are a formatted in a particular way. In this case we have a score and then a slash and then the total possible score in a single cell. We go through each option of solution and try to derive a solution that is both useful in the exact situation and possibly useful for other problems too.

We find that the Query formula and other solutions are inflexible or time intensive. We want a solution that does actually solve the problem at hand, in a quick way, and also is easy to edit and flexible to solve other problems.
Beginner
How to Make a Marketplace in a Google Sheet
Make a marketplace where sellers and buyers do not have to have edit access to a sheet.
Intermediate
Creating A Dark Mode Theme | Announcing Dark Habits
Build a habit tracker in dark mode. Learn how to use emojis and checkboxes effectively.
Most PopularIntermediate
Find Hundreds of Available Domains From Keywords with Concat and Array Formula
Build a list of domains you can buy right now. Easily check if the domain you want is still available.
Most PopularIntermediate
Secure Your Sheets by BetterSheets.co
tactics on how to keep your sheets secure.
Beginner
How to Iterate Counting Blank Cells in Google Sheets
Count different types of blank cells. Cells with nothing in them.
Intermediate
Wallace Asks: How To Find New Names
Got a list of names, need to add new ones to main list.
Discover Misspelled Duplicates
Discover effective methods to handle misspelled duplicates in Google Sheets using formulas without destroying your data. Search and find duplicates that are spelled differently.

Unveiling the Art of Taming Misspelled Duplicates: A Harmonious Symphony of Google Sheets Tricks and Tips

Embark on a captivating journey as we unravel enchanting secrets in this video guide.

Discover an ethereal dance of formulas that transcends the limitations of Google Sheets' native features, gracefully addressing the conundrum of misspelled duplicates. 

Delve into the profound intricacies of the issue, where statuses and names intertwine, inviting chaos into your CRM or meeting records. 

Witness the mesmerizing revelation of unorthodox yet elegant techniques that preserve your data's essence without sacrificing its integrity. Unveil the hidden world of the duplicate detector, where rows are harmoniously synchronized, leaving no spelling errors unnoticed. 

Prepare to be enchanted by the sublime artistry of equals unique, a symphony of transformation that transforms hundreds or thousands of rows into a carefully curated masterpiece. Immerse yourself in this poetic quest and unlock the mystique of finding truth amidst a sea of missteps.
Beginner
Send a Daily Email with Inspiring Quote - A Little Pick Me Up
Created a daily email with a motivational quote in the subject.
Intermediate
Learn to Love Your Sheets
Quick intro to a better experience of using Google Sheets
Beginner
Benoit Asks: How to Convert Case
How can I use this function, lower, upper, or proper as a conditional formatting? And then the other question is, how can I use this to apply on a bunch of cells without having to double the cells?
Member Asks: How do I calculate from a set of Form entries?
Use arrayformula to deal with Google form entries on a sheet. Even as they keep getting added.
Combine 2 Columns into 1: First Names and Last Names Together
Add together two columns data.
Beginner
Build a Domain Grader
Create a way to grade the quality of a domain based on many different votes and inputs and qualities. Like measuring the length and ranking the results.
Sheet Improvement: Better Business Idea Selection Framework
Got this template directly from AppSumo and improved upon it.
Count How Many Subscribers In a Company by BetterSheets.co
I download it from MailChimp and then I try to figure out which companies are subscribed to me most. And I do, I have to figure out the domain and count the domain. And we're gonna do that all in Google Sheets.
Intermediate
Create a PR Agency From Scratch
Go from nothing to a working PR agency. Capture sites, and Send email via script
Intermediate

Learn more about the ARRAYFORMULA() formula:

Google Sheets ARRAYFORMULA, Introductions to Arrays, ARRAY_CONSTRAIN, SORT Functions Tutorial

earn how to use ARRAYFORMULA function in Google Sheets as well as arrays in Google Sheets. This tutorial will cover array syntax, ARRAYFORMULA, SORT & ARRAY_CONSTRAIN functions and other tips and tricks.

Five Frustrating Formulas and How to Conquer Them

Google Sheets formulas can be frustrating for a few reasons. First, you’re trying to remember what to use on the fly. Then you can't remember the name of a formula. If you do figure out the name, you can't remember the syntax. Then you don't know the proper way to write the data. FRUSTRATING!

Better Sheets

Something for everyone to Learn Google Sheets. Includes Google Sheets for Beginners and Advanced Google Sheets Tutorials alike.

Start 7 Day Free Trial

Google Sheets Help

Google Sheets Courses

Google Sheet Templates and Tools

Google Sheet Add Ons