HOW TO APPLY FORMULA TO ENTIRE COLUMN IN EXCEL?

Table of Contents

INTRODUCTION

The possibility to automate our tasks is one of the major reasons for the use of Excel in our repetitive tasks.

Excel consists of a large number of cells with columns and rows. But generally, we don’t that many formulas in our reports.

Most of the formulas are same but applied to hundreds or thousands of the cells in a similar pattern.

What if we need to apply the same formula to different cells? Do we need to apply them separately to every cell?

Well! we have many options to do the same in Excel.

In this article, we’ll find out the ways to apply the formula to the entire column without bothering about applying it to each cell individually.

WHAT WE MEAN BY APPLYING FORMULA TO ENTIRE COLUMN?

Most of the time we have the same calculation for every cell in the column.

For example, if we have created a report and the last column is Total, we need to apply the formula for summing up the required fields[ column a+column b+column c….] to each and every row of TOTAL COLUMN.

But , just imagine the time it’d take if we have 1000 rows and it’ll be a complete wastage of time.

So, we have got ways to apply the formula to a column at once and within seconds.

Let us start.

WAYS TO APPLY FORMULA TO ENTIRE COLUMN IN EXCEL

Primarily we can make use of the following two ways and their subways to apply the formula instantly to the entire column even if have thousands of rows.

  1. USING FORMULAS – Making use of the functions or formulas
  2. USING DRAG HANDLE– Making use of the Fill or Drag Handle.

APPLY FORMULA TO ENTIRE COLUMN IN EXCEL USING FORMULAS

We can apply a formula to the entire column or multiple cells in any column using the formula also.

For easy understanding, let us take an example of a simple summation.

We have two columns containing some numbers and and 200 rows.

We are calculating a simple total of NUMBER1 AND NUMBER2 and getting the result. [Refer to picture below]

FOLLOW THE STEPS TO APPLY THE FORMULA TO THE ENTIRE COLUMN

  1. In this example, we’ll use copying to apply the formula to the entire column.
  2. Apply the formula to any one of the cells, for example, the first cell as shown in the picture below.

APPLY FORMULA TO THE FIRST CELL

  1. Select the cell where we applied the formula.
  2. Right Click> Copy or press CTRL+C. [ We are copying the formula. Simply copying will copy the values if no formula is there but formula if it is there on any cell.]
  3. If your column doesn’t have a heading, simply select the column by clicking on the COLUMN NAME and click RIGHT CLICK>PASTE or CTRL+V.
  4. The formula will be applied to the entire column at once. [ It’ll be applied to the complete column even beyond our 200 entries. ]
  5. The following picture shows the scenario.
  6. We need to again type the COLUMN HEADING as TOTAL because that is lost due to the application of the formula to the entire column. If you don’t want it, follow the next solution.

APPLIED FORMULA TO THE ENTIRE COLUMN

APPLY THE FORMULA TO THE ENTIRE COLUMN BUT SELECTED CELLS ONLY

  1. If you want to apply this formula to the entire column limited to the Entered values only, copy the formula cell as discussed above.
  2. Simply select all the cells of the TOTAL COLUMN where you want the result.
  3. Press CTRL+V or RIGHT CLICK>PASTE and it’ll be applied neatly to the entire column as required.
  4. This method will apply the formula only to the selected cells of the column.

PASTING THE FORMULA TO THE SELECTED CELLS ONLY

In addition to these ways, we can use the FILL HANDLE for this purpose too.

APPLY FORMULA TO ENTIRE COLUMN IN EXCEL USING FILL OR DRAG HANDLE

Just like the previous method, we can again use the other method to apply the formula to the entire column using the fill or drag handle.

WHAT IS A FILL HANDLE?
Drag handle is also known as Fill Handle, which is a small plus + sign or a solid Square ▪ at the right bottom of the selected cell or cell cursor.
This handle is used to drag or auto fill the remaining cells on the same pattern adapted from the selected cell from where the dragging was started.
CLICK HERE IF YOU CAN'T FIND THE FILL HANDLE.

For the similar example as above, let us use the drag or fill handle to apply the formula to the whole column at once.

FOLLOW THE STEPS TO APPLY THE FORMULA TO THE ENTIRE COLUMN USING THE DRAG OR FILL HANDLE

  1. Apply the formula to the first row which you want to replicate to the entire column.
  2. For our example, we applied the SUM formula as shown in the picture below.
  3. For our example, we applied the formula as =D2+E2.
ENTER THE FORMULA FOR THE FIRST CELL
  1. We can make use of two different styles of using FILL or DRAG HANDLE.
  2. Double click the FILL HANDLE and it’ll replicate the formula to all the cells in the continuation till the 200 rows i.e. the entire useful column.
  3. The process is shown in the picture below.

DOUBLE CLICK THE FILL HANDLE TO INSTANTLY APPLY THE FORMULA TO THE ENTIRE COLUMN

There is one more usage of the fill or drag handle.

We can hold and drag the fill handle till the cell we require the replication of the formula.

In our case, we’ll drag it till the end i.e. 200th row.

The formula will be applied to the portion of the column we want.

DRAG THE FILL HANDLE TO APPLY THE FORMULA TILL THE ROW YOU WANT IT



These were the ways to apply any formula instantly to the entire column.

FAQs

HOW TO APPLY THE FORMULA TO THE MULTIPLE COLUMNS?

All the methods above can be used to apply the formulas to the entire multiple columns.

  1. USING FORMULAS
  • Simply copy the formulas of the multiple columns together.
  • Select all the columns where you want to apply the formula, RIGHT CLICK>PASTE or press CTRL+V.
  • You are done.
  1. USING FILL HANDLE
  • Simply select all the cells where the formula is already applied together.
  • Double Click or drag the fill handle as per the requirement as discussed above.
  • You’re done.