如何在Excel中有/无拖动方式将公式快速应用于整个列或行？
有时您可能需要将一个相同的公式应用于Excel中的整个列或行，例如 C1 = A1 * 2, C2 = A2 * 2，... Cn = An * 2。 如果您在每个单元格中一个一个地输入公式，将非常繁琐。 有一些技巧可以将一个相同的公式快速应用于输入列或行。
 通过拖动自动填充手柄将公式应用于整个列或行
 将公式应用于整个列或行，而无需通过快捷键盘拖动
 将公式应用于整个列或行，而无需按“填充”功能拖动
 将公式应用于整个列或行而无需Kutools for Excel拖动
假设您需要在C列中应用=（A1 * 3 + 8）/ 5的公式，请参阅以下教程，将相同的公式应用于整个C列。
通过拖动自动填充手柄将公式应用于整个列或行
拖动自动填充手柄是将相同公式应用于Excel中整个列或行的最常见方法。
首先输入公式 =（A1 * 3 + 8）/ 5 在单元格C1中，然后拖动“自动填充”手柄 向下到C列的底部，然后是 =（A1 * 3 + 8）/ 5 应用于整个C列。如果您需要将其应用于整行，则可以拖动自动填充手柄 在最右边。
备注：此拖动的“自动填充”手柄方法需要自动进行公式计算。 您可以通过单击启用它 公式s> 计算选项 > 自动。 见下面的截图：
将公式应用于整个列或行，而无需通过快捷键盘拖动
有时，您要应用公式的列可能覆盖数百行，并且拖动“填充”手柄可能不方便。 实际上，您可以使用快捷键盘将其轻松存档到Excel中。
首先，选择整个C列，然后输入公式 =（A1 * 3 + 8）/ 5，然后按 按Ctrl + 输入 钥匙在一起。
如果要将公式应用于整个行，则可以先选择整个行。
将公式精确/静态地从一列复制到另一列，而无需在Excel中更改单元格引用
Kutools for Excel 确切的副本 实用程序可以帮助您轻松准确地精确复制多个公式，而无需在Excel中更改单元格引用，从而防止相对单元格引用自动更新。
全功能30天免费试用！
Kutools for Excel 包括300多个用于Excel的便捷工具。 全功能30天免费试用，无需信用卡！ 立即行动吧!
将公式应用于整个列或行，而无需按“填充”功能拖动
其实有一个 填 Excel功能区上的命令可帮助您快速将公式应用于整个列或行。
首先输入公式 =（A1 * 3 + 8）/ 5 进入单元格C1（您将在其中输入相同公式的列的第一个单元格）中，然后选择整个列C，然后单击 主页 > 填 > Down.
如果要将公式应用于整个行，只需将公式输入到整个行的第一个单元格，然后选择整个行，然后单击 主页>填充 > 右.
将公式应用于整个列或行而无需Kutools for Excel拖动
以上所有方法都是将公式应用于空白列或行。 有时，您可能需要将相同的公式应用于具有数据的整个列或行。 如何处理？ 您可以尝试Kutools for Excel的 操作工具.
Kutools for Excel 包括300多个用于Excel的便捷工具。 全功能30天免费试用，无需信用卡！ 立即免费试用！
1.选择要使用的列或行（在这种情况下，选择列A），然后单击 库工具 > 更多 > 手术.
2。 在里面 操作工具 对话框中，选择 私人订制 in 手术 框中输入 （？* 3 + 8）/ 5 在空白框中 私人订制 部分，然后单击 OK 按钮。 看截图：
然后，整个列都用=（？* 3 + 8）/ 5公式和？ 指相应单元格中的值。 请参阅以下屏幕截图：
备注：
（1）如果您检查 创建公式 选项，结果将以每个单元格的公式形式出现。
（2）如果 跳过公式单元格 如果选中此选项，则该操作将跳过并自动忽略所选范围内的公式单元格。
 操作工具 可以在多个单元格中共同执行常见的数学运算，例如加法，减法，乘法和除法等。
更多信息
Kutools for Excel 包括300多个用于Excel的便捷工具。 全功能30天免费试用，无需信用卡！ 立即行动吧!
演示：将公式应用于整个列或行而无需Kutools for Excel拖动
在Excel的整个列或行中的所有单元格中轻松添加前缀或后缀
使用“自动填充”功能可以很容易地在列中填充具有相同内容的所有单元格。 但是，如何为列中的所有单元格添加相同的前缀或后缀？ 比较分别在每个单元格中键入前缀或后缀，Kutools for Excel的 添加文本 实用程序提供了一种简单的解决方法，只需单击几次即可完成。
全功能30天免费试用！
Kutools for Excel 包括300多个用于Excel的便捷工具。 全功能30天免费试用，无需信用卡！ 立即行动吧!
相关文章：
最佳办公效率工具
Kutools for Excel解决了您的大多数问题，并使您的生产率提高了80％
 重用: 快速插入 复杂的公式，图表 以及您以前使用过的任何东西； 加密单元 带密码 创建邮件列表 并发送电子邮件...
 超级公式栏 （轻松编辑多行文本和公式）； 阅读版式 （轻松读取和编辑大量单元格）； 粘贴到过滤范围...
 合并单元格/行/列 不会丢失数据； 拆分单元格内容； 合并重复的行/列...防止细胞重复； 比较范围...
 选择重复或唯一 行; 选择空白行 （所有单元格都是空的）； 超级查找和模糊查找 在许多工作簿中； 随机选择...
 确切的副本 多个单元格，无需更改公式参考； 自动创建参考 到多张纸； 插入项目符号，复选框等...
 提取文字，添加文本，按位置删除， 删除空间; 创建和打印分页小计； 在单元格内容和注释之间转换...
 超级滤镜 （将过滤方案保存并应用于其他工作表）； 高级排序 按月/周/日，频率及更多； 特殊过滤器 用粗体，斜体...
 结合工作簿和工作表; 根据关键列合并表； 将数据分割成多个工作表; 批量转换xls，xlsx和PDF...
 超过300种强大功能。 支持Office / Excel 20072019和365。支持所有语言。 在您的企业或组织中轻松部署。 完整功能30天免费试用。 60天退款保证。
Office选项卡为Office带来了选项卡式界面，使您的工作更加轻松
 在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
 每天将您的工作效率提高50％，并减少数百次鼠标单击！
You are guest
or post as a guest, but your post won't be published automatically.

To post as a guest, your comment is unpublished.· 11 months agoWhen dragging function down a column I cant get formula to go past line 30. 31 and down shows #VALUE! any suggestions?

To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.Hi, i wanted to ask something
I want to count the total Shifts within the Shift column and put it in either Shift 1,Shift 2, or Shift 3 in column Q,R & S.
As you can see in the picture, i already copy the formula from M2 (Circle 2) to below it.
The thing is, i wanted to count the Shift per Each Day.
But as you can see i had a little bit of problem. I had 2 problems :
1. I want to copy the formula to the cell below BUT in a different day, NOT the same day, i searched on the internet and i still don't have a clue on how to do that.
2.If there's no way to do that, is there any way that i can count the shift (i'm using COUNTIF here in Q2,R2 & S2 (Circle 3) ) from 23rd July  30th July, without including the shift from the same day ? The point is i wanted to retrieve one shift per day, and as you can see if i retrieve the data, it counts as three/four shift per day (Circle 1).
If there's any question about my problem, do feel free to ask !!
Thank you.
To post as a guest, your comment is unpublished.I forgot to attach the picture i think, here it is.


To post as a guest, your comment is unpublished.hi, if the cell are not the same size in a column, then how can i copy the calculation of the top cell of column to the end (there are 200 rows in that column), as example if the first cell contains three rows merged but the second one is of two rows merged then dragging option does not work, what to do then ? pls advise.

To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.How i can use them in VBA?

To post as a guest, your comment is unpublished.Hi Rakesh,
All of methods introduced in the article are very easy. Is the VBA necessary?


To post as a guest, your comment is unpublished.How do i divide the one entire coloumn filled with number by 1000 or any number witout creating nother coloumn?

To post as a guest, your comment is unpublished.Hi Sunny,
Kutools for Excel’s Operation feature can help solve this problem.
(1) Select the column you need to divided by a certain number;
(2) Click Kutools > More > Operation;
(3) In the Operation Tools dialog, specify Division, type in the divisor, and click OK.


To post as a guest, your comment is unpublished.Hi,
How can I do sum of two different column with formula?
Like I want to sum of A1 and D1 and getting answer on F1 then which formula apply?
To post as a guest, your comment is unpublished.you can use this formula,F1=A1+D1


To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.Hi, I am trying to do a similar thing, but with letters. I am researching and trying to complete the civil aircraft register for the UK. I therefore need to have column A as GAAAA to GAZZZ on one work sheet and then GBAAA to GBZZZ on the next worksheet, etc. all the way to GZAAA to GZZZZ on the 25th worksheet. Is there an easy way of drag filling letters rather than numbers? As I have to manually fill each cell at the moment, which is very laborious. Thanks in advance, Lawrence

To post as a guest, your comment is unpublished.I want to show a certain row with a specific word in it, in a specific sheet by the help of a drop down. like i creat a drop down with some specific category in it and all category have a sheet for it own data, I want to seprate all the data with that specfice word, in different sheets always as i update the sheet every time.Please let me know if any one knows the answer ASAP!!!!

To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.Please i need help. How do i hide my formulas in my workbook. I don't want the formulas or the result to show in my workbook until i entered the required values in the linked column/cells. Presently, the cells either show the formula, zero or #NA. I don't want any of those to show until i have values for linked cells or columns.
Thanks 
To post as a guest, your comment is unpublished.Working OK, Thanks...... : )

To post as a guest, your comment is unpublished.For the questions on getting rid of #Div/0. I'm guessing you have divided by 0 or something and want those cells to be blank.
Select the whole column with the #Div/0 you don't want.
Go to Home > Find & Select > Go to Special
Toggle off Comments and Toggle On Formulas; Remove all checkmarks except the one in front of Errors.
Click OK.
You are now back to your column. You have "Found" all the "Div/0" cells.
Click the delete key and they are now blank. 
To post as a guest, your comment is unpublished.i am write in a one colum month and pur in one colum but don`t write.sir plz help me.

To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.In your above direction as how to 'Apply formula to an entire column or row without dragging by Fill feature', you state:
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.
[b]this appears to work only only if the formula is in the first cell; is there any way to use the Fill feature (for rows) starting in a cell other than the first one?[/b] 
To post as a guest, your comment is unpublished.excel all formulas short cut key please toking

To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.This is helpful, HOWEVER, when you copy down formulas through the whole column, it makes the spreadsheet have thousands and thousands of rows.

To post as a guest, your comment is unpublished.Delete all the rows below your active range, e.g., if you expanded column A, there are 1048756 filled rows there but you're only using 100 rows, then in the Name box at the left side of the Formula Bar, type
A101:A1048756
Then press Enter and then Delete
This worked for me, and I got it from at:
http://answers.microsoft.com/enus/mac/forum/macoffice2011macexcel/howdoideleteextrarowsinexcel/5a4e26b239fb47dda8c8b0a5c4d1b9b4


To post as a guest, your comment is unpublished.hi,
I want to copy formula, if rate 152,458,496 and Quantity 1,2,3 in first line then second line quntity 4,5,3 then third line 8,7,9 then forth line 7,9,6 so i want to calculate rate * every quntity. for example 152*1+152*2+152*3=2280.i want this formula past in every column so how do i do ? 
To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.Hey,
I am trying to create a spreadsheet, I want the numbers to go kind of like back to front? so when I press control N the first number for example will go on the line below, so basically the numbers start from small to big not big to small.
I have got another spreadsheet that does do this however I cant remember how it was done and cant find out how to do it anywhere.
I know I didn't explain that very well but does anyone know what I mean? 
To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.I have a totally off topic question... How did you get the cool 16bit 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..

To post as a guest, your comment is unpublished.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........
.
.
.
.
.
. 
To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.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???

To post as a guest, your comment is unpublished.[quote name="Janine"]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???[/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. 
To post as a guest, your comment is unpublished.


To post as a guest, your comment is unpublished.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
To post as a guest, your comment is unpublished.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.excelformulas.com/mathematicalexcelformulas/applyformulatorow.php
Worked for me, it should for you too.


To post as a guest, your comment is unpublished.I want to add column c to column D for each row and put the result in column E...How do I do that?

To post as a guest, your comment is unpublished.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!


To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.if 5 column and 5 Row given with some sales and Clents and Fixed Percentage So HOw to put it in excel cal

To post as a guest, your comment is unpublished.very helpful thank you!

To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.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.

To post as a guest, your comment is unpublished.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.


To post as a guest, your comment is unpublished.

To post as a guest, your comment is unpublished.This formula is very useful for my daily office work.

To post as a guest, your comment is unpublished.