This tutorial will show you how to apply and use an Excel formula for an entire column or row.
In reality, we use similar formulas in the columns of a spreadsheet all the time. For example, say you need to use the same formula for an entire column or row in Excel, such as C1=A1, C2=A2,…, Cn=An. Putting the formula in each cell one at a time will be very time-consuming. Also, you’ll waste a lot of time doing it. Excel, on the other hand, has several ways to quickly apply column formulas to a range of cells.
Here are some of the best and most valuable ways to apply the Excel formula to the entire column.
Method 1: Drag the fill handle to apply the formula to the entire column in Excel
With the fill handle, you can quickly copy formulas into cells next to each other. When you drag the fill handle, you can choose which cells to fill. So, you have more control over which cells your formula is used in.
Follow these steps to add more options to a formula and decide which ones to use:
1- First, choose the cell that has the Excel formula you want to use for a whole column. In our example, we choose cell C2, which has the formula A2+B2 written in it.
2-Click on the fill handle and move it over the cells you want to fill. In reality, the fill handle is just a small square in the lower right corner of the cell. See the picture below:
You can also use this method to spread the formula across an entire row. See the picture below:
Note that when you let go of the fill handle, you can change how the selection is filled. To do this, click the small Autofill Options icon that shows up, then choose the option you want.
Thank you for encouraging our YouTube channel
Method 2: Double-click the fill handle to apply an Excel formula to the entire column
The first method, which we just looked at, is very useful but has some problems. For example, dragging the handle to the end of a long list of data (more than 100 rows) is a lot of work. Still, you can use the Excel fill handle in another way to apply a formula to an entire column. The double-click is this.
When you double-click on the fill handle, your formula will be quickly used in many cells. But you can’t change how many cells are filled.
Take the dataset below as an example. In column D, we want to find out how much money was made in June and July.
Here are the steps you need to take to use this formula on all of column D:
1- First, leave cell D2 blank and type =B2+C2. See the picture below.
2-Then, with cell D2 selected, move the mouse cursor to the small green square at the bottom right of the cell (this is called the autofill handle). Notice how the cursor turns into a « + » sign. See the picture below:
3-To use the formula, double-click on this handle.
With the steps above, data would automatically fill up column D until the last row. In our example, the formula would be used for cells A1 through D10.
Method 3: Use the Excel Ribbon to fill a formula down
You can use a button on the Excel Ribbon to move a formula into a range of cells next to it. Just do what’s written below:
1- First, choose the cell with the formula and the cells next to it that you want to fill. In our case, we choose cells D2 through D10.
2-Then, click the « Fill » command in the « Editing » group on the « Home » tab.
3-Then, based on what you want to do, choose Down, Right, Up, or Left. In this case, we want to copy the formula down, so we should choose « Down« .
Method 4: Use copy and paste to make an Excel formula work for the whole column
The fourth way is also quick and lets you apply an Excel formula to the whole column (or to a certain cell in the column). This method basically involves copying the formula from the cell that has it and pasting it into the other cells in the column. It’s a very simple process. Here are the steps to follow:
1-Copy the cell with the formula to copy first. Right-click on it and choose « Copy » to do this. We copy cell D2 in our case.
2-Next, choose the range of cells where you want to use the same formula (D3:D10 in our example).
3-Then, choose « Formulas (U) » under « Paste options » and right-click on the selected cells.
4-Cell D2 will always be copied, but you can stop it by pressing the « esc » key on your keyboard. See the picture below:
Method 5: Use keyboard shortcuts to extend an Excel formula
Microsoft Excel also makes things easier by giving each option its own keyboard shortcut. So, use these shortcuts in Excel to apply your formula to entire column:
- Use the shortcut Ctrl+D to fill in the formula in a column. To do this, first select the cell with the formula to be copied and the cells below it, then press Ctrl + D.
- Use the keyboard shortcut Ctrl + R to copy the formula on the right into a row. First, choose the cell with the formula. Then, choose the cells to its right. Finally, press Ctrl+R.