Note: The other languages of the website are Google-translated. Back to English

How to quickly apply formula to an entire column or row with/without dragging 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.

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


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

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

Firstly type the formula of =(A1*3+8)/5 in Cell C1, and then drag the AutoFill 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 AutoFill Handle to the far right.
doc apply formula 02

Note: This dragging AutoFill handle method requires formula calculation automatically. You can enable it with clicking Formulas > Calculation Options > Automatic. See below screenshot:


Apply formula to an entire column or row without dragging by 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 to archive it easily in Excel.

Firstly, select the whole Column C, secondly enter the formula =(A1*3+8)/5, and then press the Ctrl + Enter keys together.

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

Copy formulas exactly/ statically from one column to another without changing cell references in Excel

Kutools for Excel Exact Copy utility can help you easily copy multiple formulas exactly without changing cell references in Excel, preventing relative cell references updating automatically. Full Feature Free Trial 30-day!
ad exact copy formulas 3

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

Apply formula to an entire column or row without dragging by Fill feature

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

Firstly enter the formula =(A1*3+8)/5 into the Cell C1 (the first cell of column where you will enter the same formula), secondly select the entire Column C, and then click Home > Fill > Down.

If you want to apply the formula to entire row, just enter the formula into the first cell of your entire row, next select the entire row, and then click Home > Fill > Right.


Apply formula to an entire column or row without dragging by Kutools for Excel

All of above methods are to apply formulas to blank column or row. Sometimes you may need to apply the same 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 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now!

1. Select the column or row that you will work with (in this case select the Column A), and click Kutools > More > Operation.
doc apply formula kte 01

2. In the Operation Tools dialog box, select the Custom in Operation box, enter (?*3+8)/5 in the blank box in the Custom section, and click the OK button. See screenshot:

And then the whole column are filled with the formula of =(?*3+8)/5, and the ? refers to the value in corresponding cell. See below screenshots:
doc apply formula 11

Notes:
(1) If you check the Create formulas option, the results will be in the form of formulas for each cell.
(2) If the Skip formula cells option is checked, the operation will skip and automatically ignore the formula cells in the selected range.

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

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


Demo: Apply formula to an entire column or row without dragging by Kutools for Excel


Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

Easily add prefix or suffix to all cells in an entire column or row in Excel

It’s easy to fill all cells with same content in a column with the AutoFill feature. But, how to add same prefix or suffix to all cells in a column? Comparing to type the prefix or suffix to each cell separately, Kutools for Excel’s Add Text utility provides an easy workaround to get it done with several clicks only. Full Feature Free Trial 30-day!
ad add text prefix suffix

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


Related articles:


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (63)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
:-) Wow its very Usefull!!!
This comment was minimized by the moderator on the site
This formula is very useful for my daily office work.
This comment was minimized by the moderator on the site
thanks a lot man....!!!!!!!!!!! .
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Thanks ... u saved my time :eek:
This comment was minimized by the moderator on the site
I have the same issue as K above. What is the solution? It did not appear in the string of comments. Thanks
This comment was minimized by the moderator on the site
it helped but ohhh noooo
This comment was minimized by the moderator on the site
very helpful thank you!
This comment was minimized by the moderator on the site
if 5 column and 5 Row given with some sales and Clents and Fixed Percentage So HOw to put it in excel cal
This comment was minimized by the moderator on the site
"=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?
This comment was minimized by the moderator on the site
How does one apply an equation for a different row? For example,I used =COUNTIF(C8:C19,"
This comment was minimized by the moderator on the site
thanks a lot, really very helpful
This comment was minimized by the moderator on the site
I want to add column c to column D for each row and put the result in column E...How do I do that?
This comment was minimized by the moderator on the site
Here is the formula I wrote to accomplish that task: =CONCATENATE(INDIRECT("D"&ROW())," ",INDIRECT("E"&ROW())) I wanted the system to take the text from columns D and E in the current row and put them in the column with the formula. I was working with text. You may need to incorporate the SUM function if you want to combine numbers. Row() tells it to use the current row number. Good luck!
This comment was minimized by the moderator on the site
Thank for this, But what will do when row D have not continuously series i.e. if any blank cell available there and i want print above cell value in blank cell and then concatenate the both cell
This comment was minimized by the moderator on the site
hii I am not be able to drag below mentioned formula =E15*E11/E63 I want E11 and E63 to remain same only E15 to be change as cell wise pls help regards Harish Balam
This comment was minimized by the moderator on the site
To copy the formula and change it automatically, do the following: Select cell D2 Click on the Copy button in the Home ribbon (or press Control+C on the keyboard) to copy the cell formula Mark cells D3 through D11 (put the mouse on cell D3, click the left mouse button and leave it pressed, then move the mouse to cell D11 and release the mouse button) Click on the Paste button in the Home ribbon (or press Control+V on the keyboard) to paste the formula to the cells you selected And the formula is automatically applied to all the selected cells. http://www.excel-formulas.com/mathematical-excel-formulas/apply-formula-to-row.php<br />Worked for me, it should for you too.
This comment was minimized by the moderator on the site
This may be a simple question for you guys/gals, but I'm clueless. after I've applied my formulas how do I get rid of the #DIV/0???
This comment was minimized by the moderator on the site
Your cell is to small? Widen the cell by dragging to the right.
This comment was minimized by the moderator on the site
[quote]This may be a simple question for you guys/gals, but I'm clueless. after I've applied my formulas how do I get rid of the #DIV/0???By Janine[/quote] Select the whole area that includes all the #DIV/0 Go to "Find & Select" - Go to SPECIAL. Click the radio button in front of Formulas. Unclick Numbers, Text, Logicals - which leaves only "Errors" Checked. Click OK Now all the Div/0 will be highlighted and nothing else. Use you delete key and they will disappear.
This comment was minimized by the moderator on the site
I have the same problem as Harris above. How do I go about it?
This comment was minimized by the moderator on the site
very helpful and easy to follow!
This comment was minimized by the moderator on the site
This was easy and so helpful ~Thanks~
This comment was minimized by the moderator on the site
Hi, Please can you tell me how to do a forumula to work out the number of days using dates eg 09/06/12 - 08/05/12 - when i put in the forumulation it brings up a date instead of number of days.
This comment was minimized by the moderator on the site
Thanks a lot!! :D :D :lol: rEALLY helped
This comment was minimized by the moderator on the site
Dear Sir/Madam I want to add formula in a xls sheet as i have dat like =Sheet1!A1,=Sheet1!A2,=Sheet1!A3........ =Sheet2!A1,=Sheet2!A2,=Sheet2!A3........ =Sheet3!A1,=Sheet3!A2,=Sheet3!A3........ . . . i want to devide complete data by 1000 ex =Sheet2!A1/1000,=Sheet2!A2/1000,=Sheet2!A3/1000........ . . . . . .
This comment was minimized by the moderator on the site
I have a totally off topic question... How did you get the cool 16-bit looking color depth in the office graphic? Is that just a byproduct of the png rendering? It looks really cool, and I kind of wish just running office in 16 bit color depth would do that, but I feel like it would just break..
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations