How to Insert Formula in Excel for Entire Column (5 Smart Ways)

It`s often occurred to everyone that after applying a formula in a cell of Excel you needed to use that formula for the entire column. In this article, we will see different processes on how to insert formula in Excel for entire column.

We will see the process of dragging down or double-clicking the Fill Handle option, copying and pasting the formula for the entire column,  using Fill option from the ribbon and keyboard shortcut for this.

We will be working on an example where some heights and widths are given in two different columns of a table. We need to find the Areas for different heights and widths.

We will determine the area for one cell only and will see the process of inserting this formula for the entire column. The table containing the heights and widths is given below.

Dragging Down the Fill Handle

Whenever you select a cell, you will get to see a green box surrounds that specific cell. This green box is known as the Fill Handle. One of the easy processes of inserting formula for the entire column is dragging down the Fill Handle of the formulated cell. The process is given below

  • In C2, type =A2*B2 and press enter. By this, you will get the Area for the corresponding row.

  • Select the Cell C2 to get the Fill Handle. moving the cursor of the mouse you will see the (+) sign in the rightmost corner of cell C2. Pressing on the left button of your mouse drag this fill downwards.

  • Now, after you release the pressing in your mouse the result will appear in your worksheet.

  • To check the result that you are looking for, select any cell and you will see the formula appears on top of the worksheet.

Double-Clicking the Fill Handle

This is the easiest process for inserting the formula for the entire column. After you apply the formula in a cell double-click the plus sign in the fill handle with the left button of your mouse that appears in the corresponding cell. The most interesting thing about this double clicking is that it will automatically apply the formula to the cells where you actually need the formula to be applied. Like, in this example you will need to apply the formula from cell C2 to cell C10. The double clicking will automatically insert the formula from C2 to C10. As there is no value inserted in cell A11 or B11 there will be no insertion of the formula by double-clicking in cell C11.

=IF("", "")

Copying and Pasting the Formula for the Entire Column

One of the easy processes of inserting formula for the entire column is copying the formulated cell and pasting it into the entire column. The procedure is given below.

  • After applying the formula =A2*B2 copy the formulated cell.

  • After this select the rest of the cells of Column C and in the Paste Option, select Formulas.

  • By doing this you will get the below result.

Note: If you directly use Ctrl+V or choose normal paste option instead of this you will also get the same result.

Using the Fill option From the Ribbon

Excel has its own fill option to copy text, formulas and other things in a range of cells. To use the Fill option for an entire column. First, select the entire column along with the formulated cell.

Now, in the Home tab under the Editing option, select Fill and click on to the Down option.

After doing this, you will see the below result in your worksheet.

Keyboard Shortcut

You can use a keyboard shortcut to insert the formula for the entire column of your worksheet. To do that, first select the cells of the column where you want to insert the formula along with the formulated cell. After selecting the cells press on to Ctrl + D together. You will see the formula has been applied to the cells of that corresponding column.

Conclusion

Inserting Formula for the entire column or row in Excel saves a lot of time. While working on big data this small thing plays a significant role and performs your job smoothly. Although there are a lot of processes of copying the formula for the entire column, dragging down the plus sign or double-clicking it are the easier and popular ones.

Hope this article finds your interest.


Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and attitude to grow continuously. Continuous improvement and life-long learning is my motto.

5 Comments
  1. Reply
    Chandrasekhar N May 30, 2018 at 8:14 PM

    I thank you for the various tips and short cuts on various topics.

    • Reply
      Kawser May 30, 2018 at 8:30 PM

      You are welcome, Chandrasekhar! Glad to hear that our tips help you.
      Best regards

  2. Reply
    Amber May 31, 2018 at 12:14 AM

    Very useful! I knew there was a way to do this, but hadn’t taken the time to learn. Thank you!

    • Reply
      Kawser May 31, 2018 at 10:57 AM

      Glad to know that it was helpful.
      You’re welcome 🙂

  3. Reply
    Sohail Rizki November 8, 2018 at 8:54 PM

    Instead of Ctrl+D, just enter Ctrl+enter.

    Leave a reply