Googlesheets
No Responses June 3, 2025

How to fill formula down in Google Sheets without dragging or Copy Paste!!

We’ve all been there. You write a formula in one cell—maybe a VLOOKUP, an IF, or a simple calculation—then copy it down for 200 rows. It works, until…

You insert a new row and the formula doesn’t apply.

You forget to copy it far enough.

Or worse—someone else deletes a formula row by accident.

This repetitive, error-prone approach isn’t scalable. That’s where ARRAYFORMULA comes in—a powerful way to apply a formula across rows without ever copying it down manually.

Let’s explore how to convert traditional row-level formulas into dynamic, full-column solutions using ARRAYFORMULA.

1. Converting VLOOKUP to Work Across a Column

Original formula:

=VLOOKUP(D2, ‘Master’!A2:B, 2, FALSE)

You usually drag this down, but instead, use:

=ARRAYFORMULA(IF(D2:D=””, “”, VLOOKUP(D2:D, ‘Master’!A2:B, 2, FALSE)))

This version:

  • Checks if D is blank—if so, it leaves the row empty.
  • Otherwise, it runs VLOOKUP across the entire column.
  • No need to drag ever again.

2. Using ARRAYFORMULA with Conditional IF Logic

Old way:

=IF(A2<>””, U2 + 3, “”)

New way:

=ARRAYFORMULA(IF(A2:A<>””, U2:U + 3, “”))

And if you want to conditionally use U if filled, or fallback to W:

=ARRAYFORMULA( IF(U2:U<>””, U2:U + 3, W2:W + 3) )

To skip rows where both U and W are blank:

=ARRAYFORMULA( IF((U2:U=””) * (W2:W=””), “”, IF(U2:U<>””, U2:U + 3, W2:W + 3)) )

3. Extracting Text Without Copying Down

Let’s say you have a column of values like:

9706096015/9101337992

And you want to extract the number after the slash:

=ARRAYFORMULA(IF(A2:A=””, “”, REGEXEXTRACT(A2:A, “[^/]+$”)))

This gives you the second number in every row—without writing a new formula each time.

The Result: Zero Manual Dragging

With ARRAYFORMULA, your formulas live in a single cell—often row 1 or 2—and apply themselves intelligently to every row that has data.

No dragging. No breakage. No forgetting.

If you’re still dragging formulas in Google Sheets, it’s time to stop. Wrap your logic in ARRAYFORMULA and make your sheets clean, powerful, and self-maintaining.