How to quickly apply formula to an entire column or row in Excel?

Sometimes you may need to apply one same formula to an entire column or row in Excel, such as C1=A1*2, C2=A2*2, …, Cn=An*2. It will be quite tedious if you enter the formula in each cell one by one. There are some tricky ways to apply one same formula to an enter column or row quickly.

Apply formula to an entire column or row with dragging Fill handle

Apply formula to an entire column or row with shortcut keyboards

Apply formula to an entire column or row with Fill command

Apply formula to an entire column or row with Kutools for Excel

Supposing you need to apply the formula of =(A1*3+8)/5 in the Column C, and see the following tutorials to apply same formula to entire Column C.


Hint


arrow blue right bubble Apply formula to an entire column or row with dragging Fill handle

Dragging the Fill handle is the most common way to apply formula to an entire column or row in Excel.

  • Firstly type the formula of =(A1*3+8)/5 in Cell C1;
  • Then click the Cell C1, and drag the Fill Handle down to the bottom in Column C.

Then the formula of =(A1*3+8)/5 is applied in the whole Column C. If you need to apply it to the entire row, you can drag the Fill Handle to the far right.


arrow blue right bubble Apply formula to an entire column or row with shortcut keyboards

Sometimes, the column you want to apply the formula may covers hundreds of rows, and dragging Fill handle may be not convenient. Actually you and use shortcut keyboards.

  • Firstly, select the whole Column C.
  • Enter the formula of =(A1*3+8)/5.
  • Press the Ctrl key and Enter key together.

If you want apply the formula to an entire row, you can select the entire row first.

Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, save two hours every day!
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.

arrow blue right bubble Apply formula to an entire column or row with Fill command

Actually there is a Fill command on the Microsoft Excel' Ribbon to help you apply formula to an entire column or row quickly.

  • Enter the formula of =(A1*3+8)/5 in Cell C.
  • Select the Column C.
  • Click the Home > Fill > Down.

If you want to apply the formula to entire row, just select a row, and click Fill > Right.


arrow blue right bubbleApply formula to an entire column or row with Kutools for Excel

All of three methods introduced above are to apply formulas to blank column or row. Sometime you may need to apply formula to an entire column or row with data. How to process? You can try the Kutools for Excel's Operation Tools.

Recommend: Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 60 days. Read More...

Step 1: Select the column or row that you will work with, in this case select the Column A.

Step 2: Click the Kutools > More > Operation Tools.

doc-apply-formula3

Step 3: In the Operation Tools dialog box, select the Custom in Operation list box, enter the (?*3+8)/5 in the blank box in the Addition section.

doc-apply-formula4

Step 4: Click OK or Apply. Then the whole column are filled with the formula of =(?*3+8)/5, and the ? refers to the value in corresponding cell.

Notes: 1. If you check the Create formulas option, the results will be in the form of formulas for each cell.

2. If Skip formula cells option is checked, the operation will skip and automatically ignore the formula cells in the range.

The Operation Tools can perform common math operations in multiple cells together, such as addition, subtraction, multiplication and division, etc. Read more…


Related articles:


Is your problem solved?

Recommended Productivity Tools

The following tools will greatly save your time and effort, which one do you prefer?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 More than 120 powerful advanced functions which designed for Excel:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more     btn download     btn purchase