Simplifying My Sheets
I'm going to show you a quick upgrade that I made to one of my Google Sheets this week that makes it more performant scalable and easy to maintain.
And it's by using one simple formula in the place of many formulas and a column.
FYI, this’ll be the last newsletter before the new year. Merry Christmas and see you in 2026!!
WALKTHROUGH
When and How to Use ARRAYFORMULA
I'm not really sure why I didn't do this sooner.
I've been using a custom Google Sheet for work to manage the processing of several invoices at one time into Quickbooks.
The details aren't super important for the sake of this tutorial, but the way that I was doing it is…
One of my columns took information from two of my other columns and applied an if statement to it so I could have it automatically enter a specific value based on the info already in those cells.
Here's what I'm talking about:
Column F automatically pulls the store name from another list of items because column D has a “Q” in it.
The reason for this is not important and it's very specific to this particular use case but the application to get a value automatically entered is important.
Here's the code that I had in column F before today:
=IFS(ISNUMBER(SEARCH("C",D2)),"Clinton", ISNUMBER(SEARCH("F",D2)),"Fondren", ISNUMBER(SEARCH("Q",D2)),"Quarter", ISBLANK(D2),"") This worked perfectly fine however it was not a way to do it because I had to copy it all the way down for the entire column F.
the alternative is to wrap this up in an ARRAYFORMULA.
Instead of copying it down however many rows an ARRAYFORMULA in one cell allows me to simply write one formula one time and use it for the entire column.
This is good for a few reasons:
Performance: One formula processing an array is significantly faster than 41 or 401 individual formulas. Google Sheets calculates the entire range in one operation instead of looping through each cell.
Maintenance: If I ever need to modify the logic all I have to do is change the one formula in the 1 cell at the very top and we're good to go.
Data Integrity: I don't usually run into errors when I've copied down formulas however it has happened and you always run the risk of it happening if you are copying down a ton of formulas to every cell in that column. One of them could get deleted or modified on accident.
Scaleability: If I add new data down to the bottom or if I extend my sheet and add new rows with ARRAYFORMULA I can just account for that and have built into the formula itself an extended range i.e.
D2:D.Cleaner Output: Now when I cursor over any of the cells except for that very first one in column F it shows simple text output instead of a formula.
CODE
The ARRAYFORMULA Code
Here’s what replaced 41 rows of formulas copied straight down.
This one formula is now in one cell and it works the same way for every row in the sheet.
=ARRAYFORMULA(IFS(ISNUMBER(SEARCH("C",D2:D41)),"Clinton", ISNUMBER(SEARCH("F",D2:D41)),"Fondren", ISNUMBER(SEARCH("Q",D2:D41)),"Quarter", ISBLANK(D2:D41),""))Why does this work?
ARRAYFORMULA processes entire ranges as arrays in a single calculation rather than evaluating each cell individually.
Instead of Google Sheets performing 41 separate "check D2, output result to F2; check D3, output result to F3..." operations, it loads the entire D2:D41 range into memory once, applies the logic to all values simultaneously in a vectorized operation, and outputs all results in one pass, similar to how batch processing is faster than handling items one-at-a-time.
TOOLS
Make life easier
Coefficient - Live spreadsheet connections to 60+ business systems
Lido - Automate your spreadsheets; accurately extract date from PDFs
TransactionPro - quickly import, export or delete data in Quickbooks
beehiiv - my choice for a newsletter operating system
Carrd - free one-page website builder
Transistor - my favorite podcast host
NEXT STEPS
Whenever you’re ready, here’s how I can help:
Work with me
I’m available for consulting projects. Reach out and let’s chat.Business tech stack
Some of my recommendations for software and tools.Personal budget tool
As a subscriber, you get a discount on my personal finance system.YouTube
If you aren’t subscribed yet, come on over to the YouTube channel where all the spreadsheet, automation and productivity tutorials live.
Happy Spreadsheeting!
Enjoyed this issue?
Subscribe below or Leave a testimonial
<iframe src="https://embeds.beehiiv.com/eab8502a-f9ab-4d3f-abc8-30b1abb520a9?slim=true" data-test-id="beehiiv-embed" height="52" frameborder="0" style="margin: 0; border-radius: 0px !important; background-color: transparent;"></iframe>
