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

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

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.


Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

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.


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.

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.

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.

Kutools for Excel's Operation Tools can perform common math operations in multiple cells together, such as addition, subtraction, multiplication and division, etc. Click to know more…


Related articles:


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Comments  

+4#Sarvesh2013-11-09 10:03
:-) Wow its very Usefull!!!
Reply | Reply with quote | Quote
-2#Roshan Bhandari2013-12-19 10:36
This formula is very useful for my daily office work.
Reply | Reply with quote | Quote
-2#paolo2014-02-04 12:02
thanks a lot man....!!!!!!!!!!!


.
Reply | Reply with quote | Quote
+5#K2014-03-17 21:57
Dragging down doesn't work. It just repeats the values, not the formula. I have to manually type in the formula in each cell. Please tell me the correct way of repeating a formula down a column.
Reply | Reply with quote | Quote
+6#Bubbalo2014-03-27 21:32
Go into Formulas>Calculation Options and change the option to automatic if its in manual. Then highlight the cells where you want the formula to go and click Fill>Down.
Reply | Reply with quote | Quote
-2#Bubbalo2014-03-27 21:15
I have the same issue as K above. What is the solution? It did not appear in the string of comments. Thanks
Reply | Reply with quote | Quote
0#Ericfinaly2014-04-26 15:51
very helpful thank you!
Reply | Reply with quote | Quote
0#Ramachandran2014-05-02 12:17
if 5 column and 5 Row given with some sales and Clents and Fixed Percentage So HOw to put it in excel cal
Reply | Reply with quote | Quote
0#Doru2014-06-19 07:55
"=IF(B24=N5:N18,N4,IF(B24=O5:O19,O4,IF(B24=P5:P13,P4,L4)))"

I have this formula for comparing text cells. But it works only for row 5 if i introduce values for above it shows me L4 value.
Can you help me with this?
Reply | Reply with quote | Quote
0#kipling2014-07-02 20:16
How does one apply an equation for a different row? For example,I used =COUNTIF(C8:C19,"
Reply | Reply with quote | Quote
0#waddah2014-07-07 09:48
thanks a lot, really very helpful
Reply | Reply with quote | Quote
0#J2014-07-08 14:09
I want to add column c to column D for each row and put the result in column E...How do I do that?
Reply | Reply with quote | Quote

Add comment


Security code
Refresh