How to Copy a Formula on Entire Column in Google Sheets

Written by Sarmad Gardezi August 21, 2017

People now a days only use Google Drive to work fast. If your a Beginner  then this tutorial helps you to perform the function easily and it makes your work more easy. There are several ways to solve this problem.

The easiest approach to copy down formulas is to use the  fill handle in Google Sheets. Write your formula in the first row of your spreadsheet, and then point your mouse to the lower right corner of the formula cell.

But i have found some useful methods which can make your work more easier. Just follow the steps below and enjoy the work just like a cup of cake.

The easiest approach to copy down formulas is to use the fill handle in Google Sheets. Write your formula in the first row of your spreadsheet, and then point your mouse to the lower right corner of the formula cell.

The pointer changes into a fill handle (black plus symbol) that you can drag to the last row of the sheet. The fill handle will not just copy down the formulas to all the adjacent cells but also copies the visual formatting.

If you need to copy the formulas across cells but sans any formatting, select the cell that contains the formatting and press Ctrl+C to copy it to the clipboard. Next select the range where that formula needs to applied, right-click, choose Paste Special and Paste Formula only.


Sometimes we have 100 of rows or more then that so, it may be a very hard process to apply formula on all columns one by one. Im sharing an easy way which lead you to this process very easily.

Highlight the first cell in the column and type the formula as earlier. Instead of specifying a single cell as a parameter, we’ll specify the entire column using the B2:B notation (start from cell B2 and go all the way down to the last row of column B).

After that press Ctrl+Shift+Enter, or Cmd+Shift+Enter on Mac, and Google Sheets will automatically surround your formula with ARRAYFORMULA function.

Thus, we are able to apply the formula to the entire column of the spreadsheet with only a single cell. Array Formulas are more efficient as they process a batch of rows in one go. They are also easier to maintain as you only need to modify a single cell for edit the formula.

One issue that you may have noticed with the above formulae is that it applies to every row in the column where you have only want to add formulas to rows that contain data and skip the blank rows.

This can be done by adding an IF contain to our ARRAYFORMULA so that it doesn’t apply the formula the any of the blank rows.

Google Spreadsheet offers two functions to help test whether a cell is empty or now.

  • ISBLANK(A1) – Returns TRUE if the referenced cell is empty.
  • LEN(A1) <> 0 – Returns TRUE if the referenced cell not empty, FALSE otherwise

Our modified Array Formulas would therefore read:

Using ISBLANK(Cell Reference):

There are several other ways to test if a cell is blank or not:

[code_block_line_numbers]=ArrayFormula(IF(ISBLANK(B2:B), “”, ROUND(B2:B*18%, 2)))
=ArrayFormula(IF(LEN(B2:B)&lt;&gt;0, ROUND(B2:B*18%, 2), “”))
=ArrayFormula(IF(B2:B=””, “”, ROUND(B2:B*18%, 2)))[/code_block_line_numbers]


In our previous examples, the text of the column titles (like TaxTotal Amount) was pre-populated and the formulas were only added to the first row of the dataset.

We can further improve our formula so that they can be applied to the column header itself. If the index of the current row is 1, calculated using the ROW() function, the formula outputs the column title else it performs the calculation using the formula.

[code_line]=ArrayFormula(IF(ROW(B:B)=1,”Tax”,IF(ISBLANK(B:B),””,ROUND(B:B*18%, 2))))[/code_line]


ARRAYFORMULA functions are particularly useful for Google Formswhen the form responses are getting saved inside a Google Sheet. You cannot do live calculations inside Google Forms but they can be performed inside the spreadsheet that is collecting the responses.

You can create new columns inside the Google Spreadsheet and apply the ARRAYFORMULA to the first row of the added columns.

As soon as a new form submission is received, a new row would be added to the Google Sheet and the formulas would be cloned and automatically applied to the new rows without you have to copy-paste stuff.


You can combine ARRAYFORMULA with VLOOKUP to quickly perform a lookup across an entire column.

Say you have a “Fruits” sheet that lists the fruit names in column A and the corresponding prices in column B. The second sheet “Orders” has fruit names in column A, the quantity in column B and you are supposed to calculate the order amount in column C.

[code_line]=ArrayFormula( IF(ROW(A:A)=1, “Total”, IF(NOT(ISBLANK(A:A)), VLOOKUP(A:A, Fruits!A2:B6, 2, FALSE) * B:B, “”)))[/code_line]

In simple English, if the row of the current cell is 1, ouput the column title in plain text. If the row is greater than 1 and the column A of the current row is not empty, perform a VLOOKUP to fetch the price of the item from the Fruits sheet. Then multiply that price with the quantity in cell B and output the value in cell C.

If your VLOOKUP range is in another Google Spreadsheet, use the IMPORTRANGE() function with the ID of the other Google Sheeet.


Free Online Image Hosting

Host your Images Online for free & use them on Website Blogs and Social Media.

Host Image Now Featured
Online Dictator

Convert your Speech into Text. You can either copy, Download or Share it Online for free.

Try it Now Popular
My First Tweet

Find first tweet on Twitter and retweet it to amaze people.

Lets Go! Trending

What is YouTube Unlisted Video & How I can Create it?

Learn the Easy & Best Way to Make Money in Pakistan

Insert Date in Google Docs Using Google Apps Scripts

How to Embed Twitter DM / Message Button in Website

get in touch

I build bespoke solutions that use the capabilities and the features of Google Apps for automating business processes and driving business productivity.

or send us a message via Facebook Messenger