How to Insert Formula in Google Sheets for Entire Column

Suppose you are working on a dataset where you need to apply formulas but this work will be difficult if you work on a complicated dataset or a large dataset. Say, you are working on a dataset where you need to sum up the yearly profit of your company but this is a large number and the dataset will be also large at the same time if you insert the formula into every cell then this process will be very time-consuming. But if you can insert the formula at a time for the entire column then this process will be very easy. In this article, we will learn how to insert formula in google sheets for an entire column.

how to insert formula in google sheets for entire column

Here is the overview of this article. You will learn more once you go through the total article.


A Sample of Practice Spreadsheet

You may copy the spreadsheet below and practice by yourself.


4 Simple Ways to Insert Formula for Entire Column in Google Sheets

The dataset below contains Comnapy Name, Profit in Million (June), and Profit in Million (July). ย This dataset represents the monthly profit for June and July for the top companies. Now we will sum up the profit of these two months using the formula. But inserting a formula into every cell of a column is quite difficult. So, these processes below shows how to insert formula for entire column in google sheets. So letโ€™s start.

Dataset of how to insert formula in google sheets for entire column


1. Using Fill Handle

When you need to insert a single formula into every cell of a column then fill handle is one of the easiest options. If you place your cursor on the bottom right side of a cell then you get a fill handle.

showing fill handle.

You can use the fill handle in two different ways. In this article, we will show you these two ways. So follow the below steps to execute this process.


1.1. Double-Clicking

Here, we will execute this formula by double-clicking the fill handle.

๐Ÿ“Œ Steps:

  • First, select cell E5 and enter the SUM function to get the profit of the last two months.

sum function

  • Then, select range C5:D5 and get the profit for the last two months.
=SUM(C5:D5)

completing formula

  • After that, place your cursor on the fill handle and double-click the left side of the mouse. Lastly, the final output is below.

double click


1.2. Dragging Down

Here, we will insert the formula dragging down the fill handle maintaining the below steps.

๐Ÿ“Œ Steps:

  • Initially, select cell E5 and enter the SUM function as before.
  • Consequently, place the cursor on the bottom right side of the cell and drag down the fill handle.

output of how to insert formula in google sheets for entire column

Read More: How to Insert Equation in Google Sheets (4 Tricky Ways)


2. Applying Keyboard Shortcut

We can also execute this process using the keyboard shortcut. Say you are working on a laptop and you donโ€™t have a mouse with you. Then this process is the most convenient way to complete this method. So, follow the steps below.

๐Ÿ“Œ Steps:

  • In the beginning, select cell E5 and enter the SUM function.
  • After that, select range C5:D5 and complete the SUM.
  • Now we need to enter this formula into every cell of this column to execute this process.
  • Select every cell of column E in the dataset including cell E5.
  • As all the cells are selected press CTRL + D to insert the dataset in the entire column.

another output of how to insert formula in google sheets for entire column


Similar Readings


3. Using Autofill Option

When you working on a dataset and entering a formula in one cell then google sheets suggest you to enter the same formula into the other blank cell of the column. This feature is called auto-fill. Follow the steps below to execute this process.

๐Ÿ“Œ Steps:

  • First, select cell E5 and enter the SUM function.

entering sum function

  • Enter the range C5:D5 and press enter to execute this formula.
  • Once, you press Enter google sheets will suggest you AUTO FILL to copy the same formula into the blank cells, Now click the Tick mark to accept the suggestion.

The other output of how to insert formula in google sheets for entire column

  • Here is the final output.

after autofill

Read More: How to Insert Blank Column Using QUERY in Google Sheets


4. Employing ARRAYFORMULA Function

You can also apply The ARRAYFORMULA Function to insert the same formula for the entire column. As ARRAYFORMULA function combines all the cells and returns the value as a group.

๐Ÿ“Œ Steps:

  • Initially, select cell E5 and enter the ARRAYFORMULA function.

adding ARRAYFORMULA function

  • Afterward, select ranges C5:C10 and D5:D10, to sum up, the two ranges.
  • Finally, press Enter, and the formula will be copied into all the blank cells of column E in the dataset.
=ARRAYFORMULA(C5:C10+D5:D10)

the other output of how to insert formula in google sheets for entire column


How to Lock Cell References of a Formula in Google Sheets

What if you are working on a dataset where you need to insert a formula using a specific cell reference but whenever you are dragging down the fill handle the cell reference changes. You need to lock the cell reference if you want to fix the cell reference.

๐Ÿ“Œ Steps:

  • First select cell E5 and enter the SUMย function.
  • Then select the cell range C5:C10 and press F4 to lock the cell reference.
  • Lastly, press ENTER to execute this formula.

lock cell reference

  • As the cell is already locked, if you drag down the formula the reference will remain the same and the formula will be constant.
  • You can lock the cell reference by pressing F4 or the $ sign. If you want to lock only the column then enter $ at the beginning of the reference only.
  • If you want to lock the row only and enter $ only before the row number.

Similar Readings


How to Insert Formula for an Entire Row in Google Sheets

You can insert the same formula for an entire row using the same method we already used while inserting the formula for an entire column. Follow the steps below to complete this process using the dataset below.

dataset of inserting formula for an entire row

๐Ÿ“Œ Steps:

  • Initially, select cell C7 and enter the SUM function.
  • Afterward, select range C5:C6 and press Enter to get the sum of the profit for last two months.
  • Now, drag the fill handle rightward to get the formula copied for the entire row as below.

the output

  • You can execute this process by pressing CTRL + D using the range C7:F7. (Process showed earlier in Keyboard shortcut)

Read More: How to Paste and Insert Rows in Google Sheets (3 Easy Ways)


How to Insert Formula for Entire Table in Google Sheets

Suppose you are working on a table and the data has two different parameters. Now you need to insert a formula for an entire table. The dataset below contains The share of the investors, Profit in million (June), Profit in Million (July), and Profit in Million (Aug). In this dataset, we need to calculate the share profit for each of the investors. In the last three months. Please follow the steps below to execute this process.

dataset

๐Ÿ“Œ Steps:

  • In the beginning, select cell D5 and enter the function.
=$C5*D$4
  • Here we want to get the profit percentage of the investors so lock the column when you are dragging down to get the profit for one investor in the last 3 months.
  • And lock the row when you are dragging the fill handle right to get the profit of a specific month for the 3 investors.

output


Things to Remember

  • If you want to use a keyboard shortcut in google sheets while inserting the formula in enter column then you need to select the entire column. Otherwise, this process wonโ€™t work.
  • You can also autofill the entire column by pressing CTRL + ENTER.

Conclusion

In this article, we explained how to insert formula in google sheets for an entire column using different methods. Hopefully, these processes will help you apply this method to your dataset. Please let us know in the comment section if you have further queries or suggestions. You may also visit our OfficeWheel blog to explore more Google Sheets-related articles.


Related Articles

Afrina

Afrina

This is Afrina Nafisa. Currently working as a Technical Writer at OFFICEWHEEL.COM. I have done my graduation from Ahsanullah University of Science and Technology. As I am eager to learn more and more currently my motive is to make myself better every day with my work so that I can make better content for all the readers.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo