Afrina
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.
Here is the overview of this article. You will learn more once you go through the total article.
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.
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.
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.
- Then, select range C5:D5 and get the profit for the last two months.
=SUM(C5:D5)
- After that, place your cursor on the fill handle and double-click the left side of the mouse. Lastly, the final output is below.
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.
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.
Similar Readings
- How to Insert Superscript in Google Sheets (2 Simple Ways)
- Insert Button in Google Sheets (5 Quick Steps)
- How to Add Parentheses in Google Sheets (5 Ideal Scenarios)
- Insert Error Bars in Google Sheets (3 Practical Examples)
- How to Insert Yes or No Box in Google Sheets (2 Easy Ways)
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.
- 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.
- Here is the final output.
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.
- 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)
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.
- 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 a Header in Google Sheets (2 Simple Scenarios)
- Insert Page Break in Google Sheets (An Easy Guide)
- How to Insert a Textbox in Google Sheets (An Easy Guide)
- Insert Rows Between Other Rows in Google Sheets (4 Easy Ways)
- How to Insert Multiple Rows in Google Sheets (4 Ways)
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.
📌 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.
- 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.
📌 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.
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
- How to Insert a Drop-Down List in Google Sheets (2 Easy Ways)
- Insert Sparklines in Google Sheets (4 Useful Examples)
- How to Insert Video in Google Sheets (2 Easy Ways)
- Insert Serial Numbers in Google Sheets (7 Easy Ways)
- How to Insert Multiple Columns in Google Sheets (2 Quick Ways)
- Make a Tournament Bracket in Google Sheets (Easy Steps)
- How to Insert PDF in Google Sheets (2 Suitable Methods)
