Do you want to learn how to fill down a formula in Google Sheets?

In this blog post, weâ€™re going to show you all the different ways to do it. Weâ€™ll also explain the benefits of each method and when you might want to use them.

Google Sheets is a great productivity tool that provides various ways to work around the data you have. This includes creating new calculations with formulas.

One thing that you will do most often is to copy or fill a formula down the rows in Google Sheets. This is because of the tabular structure of your data where each column contains various rows, and you need to apply a formula to each row.

Copying the formula down across the rows or filling the formula down is a task you can do in seconds, provided that you know the proper technique.

By the end of this post, youâ€™ll be able to fill down formulas with ease. So what are you waiting for? Get your copy of the example workbook and get started!

## Fill Down a Formula with Copy and Paste

The crudest method of filling down the formula is copying and pasting it across the cells.

You don’t need to be a master of keyboard shortcuts or anything like that while working with this method. It’s just a simple copy and paste.

Suppose we have data as shown in the screenshot above, where column A consists of **Sales Amount** in USD.

In column B, you wish to apply a formula that captures the **Profit**, which is a simple 5% calculation of the **Sales Amount**.

In cell **B2**, type the formula `=A2*0.05`

or `=A2*5%`

.

Both of these formulas produce the same result and generate 5% of the **Sales Amount** in cell **B2**.

Copy cell **B2** using `Ctrl` + `C`. This will copy the formula in the cell.

Select the entire range from cell **B3** to **B11** and through your keyboard, hit `Ctrl` + `V` to paste it across the rows.

The formula will do its work and capture the 5% of revenue for each sales value present in column A.

Piece of Cake? It is indeed!

You can use this method to fill the formula down across the rows and generate the results.

**Fill Down a Formula with Click and Drag**

If you are working on a relatively smaller dataset such as a few hundred rows or less, then using the Click and Drag method is the best way to populate the formula down the rows.

This allows you to save time as you don’t need to copy and paste the values down across the rows.

While using this feature in Google Sheets, it recognizes the pattern across the rows. It then implements this over the remaining rows to generate a list with the same formulas.

To implement this method, click select cell **B2** which contains the formula. Pay attention to the blue rectangle that appears as soon as you select the cell. This is the fill handle!

Hover your mouse cursor over the solid blue rectangle placed at the extreme right of the selected cell, and you will see the plus icon appearing with the fill handle.

Once you see the plus icon, click and hold on the left key of your mouse or touchpad and drag it down until cell **B11**.

Google Sheets identifies that you are trying to fill the cells with a formula and populates it across the cells.

You will see that the formula is filled down across the rows and captures the profit associated with each sales value.

## Fill Down a Formula with Double Click

Another way that is faster than dragging the formula cell down across the cells is using the left mouse or touchpad key with a double click on the fill handle of the selected formula cell.

The method is more convenient as it allows you to copy the formula down even for a sheet with thousands of rows. You don’t need to worry about where the last row is for the formula.

Google Sheets is smart enough to check for the last working row and populate the formula until that point.

The formula is in cell B2 that captures the profit as 5% of the sales value from cell A2.

Select the cell and the fill handle will appear.

Hover your mouse over the fill handle and then **double-click** the left mouse key.

You will see that the formula is populated across the rows in column B for each row in column A until the last working row next to column A.

This utility saves you time as it automatically detects the last working row.

## Fill Down a Formula with AutoFill

In 2020, Google Sheets added the **AutoFill** tool that allows you to either auto-complete a list or fills down a formula. This is by far one of the best features.

You can use this to complete a list by detecting patterns. It also automatically populates a formula down the rows and provides you with intelligent formula suggestions.

If you want to explore **AutoFill** more then you can read this post about all the ways you can use Smart Fill.

Type the formula `=A2*0.05`

in cell **B2**.

As soon as you hit the `Enter` button to execute the formula, the **AutoFill** window will pop up.

Click on the **checkmark** button to accept the suggestion. You can also use the keyboard shortcut `Ctrl` + `Enter` to **AutoFill** the formula down all the rows.

## Fill Down a Formula with ARRAYFORMULA Function

There are a lot of very useful spreadsheet functions which you will only find in Google Sheets and **ARRAYFORMULA** should be at the top of this list because adds utility to many other functions.

Whenever you come up with a situation where a formula needs to be populated across the rows (as an array), the **ARRAYFORMULA** function is the way to achieve this.

**ARRAYFORMULA** allows you to convert formulae that result in a single cell value to an array of values.

`= ARRAYFORMULA ( A2:A11 * 0.05 )`

Select cell **B2** and insert the above formula. This will output the calculation for each row in your data.

ðŸ’¡ **Tip**: There is a keyboard shortcut that will automatically wrap your formula with an **ARRAYFORMULA** function while in edit mode. Press `Ctrl` + `Shift` + `Enter` on the keyboard to convert the formula to an **ARRAYFORMULA**.

## Fill Down a Formula with the QUERY Function

The **QUERY** function is very powerful due to its ability to work with arrays as SQL-Like databases.

It allows you to manipulate and transform data in almost any way imaginable. Moreover, it can return array values for row by row calculations.

If you wish to know more about this function, then check out this detailed guide to the **QUERY** function.

You can utilize the powerful QUERY function to fill a calculation down the rows in Google Sheets.

`= QUERY ( A1:A11, "SELECT A*0.05" )`

Insert the above **QUERY** function with the range **A1:A11** as the **data** argument. There is no need to fix the range reference while working with the **QUERY** function since the single formula will return all the results.

Within the **query** argument, you can write a `SELECT`

query. Since you want to capture 5% of column A, you must write the **query** argument as `"SELECT A * 0.05"`

including the double quotations so it’s entered as text.

The formula performs a calculation for each row and returns the results in an array.

## Apps Script to Automatically Fill Down a Formula

Apps Scripts allow you to write JavaScript code to automate your day-to-day activities in Google Sheets.

Interestingly, you can use the Apps Scripts to automatically fill the formula down across the rows in Google Sheets.

If you want to know more then check out this full guide to get started with Apps Scripts in Google Sheets.

Go to the **Extensions** menu and select the **Apps Script** option to open the Apps Script editor in Google Sheets.

```
function fillDown() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Example 7");
ss.getRange("B2").setFormula("=A2*0.05");
var lastRow = ss.getLastRow();
var fillDownFormulaRange = ss.getRange(2, 2, lastRow-1);
ss.getRange("B2"). copyTo(fillDownFormulaRange);
}
```

Copy and paste the above script code into the interface.

### Explanation

This code will insert a formula into cell **B2** and then copy it down to **B11**.

`fillDown()`

is the name given to this code using the`function`

keyword. You can name it whatever you want, but having a descriptive name is always best.- The
`SpreadsheetApp.getActiveSpreadsheet().getSheetByName()`

method allows you to access the sheet named**Example 7**. This is stored in a variable named`ss`

. - The
`getRange()`

method calls on`ss`

and allows you to access cell**B2**. Then the`setFormula()`

clause applies the formula`=A2*0.05`

in that cell. - The
`getLastRow()`

method calls upon`ss`

and gives you the last working row from your sheet. The results are stored in a variable named`lastRow`

. - The
`getRange()`

method is used to capture the range**B2:B11**in a variable with the name`fillDownFormulaRange`

. - The
`copyTo()`

clause will then copy the formula stored in cell**B2**down the range**B2:B11**.

Once you copy and paste the code, use the **Run** button from the ribbon inside **Apps Script** to run this script.

The **Execution log** will tell you the progress of this code running. Once it is completed, you will see the formula being populated across the rows in column B.

ðŸ’¡ **Tip**: You can use Apps Scripts to build your own custom functions such as this running total function that automatically fill down results.

## Conclusions

There are many ways to fill down your formula calculations in Google Sheets.

A simple copy and paste is the easiest way and will get the job done.

Click and drag or double click methods can also be used to copy and paste formulas down the rows more quickly.

**AutoFill** can recognize the formula and last working row to populate the formula across those rows automatically. The method is versatile and quick. It also shows you the preview and allows you to either accept or reject the suggestions.

The **ARRAYFORMULA** function allows you to convert single value formulae into arrays to execute calculations on a row-by-row basis. You can reference all the rows at once on which the formula should be applied, and will automatically fill the results in each row.

The **QUERY** function is also helpful in filling down the calculation results across the rows by using an SQL query to achieve this result.

Finally, you can write a code of your own that works dynamically to fill the formula down the rows at a click of a button.

Which method do you use to fill down your formulas? Do you know any other tricks to achieve this? Let me know in the comments below!

## 0 Comments