如果您的工作需要您实际使用Excel工作表,您可能正在寻找使重复性任务更简单的方法。例如,每当您处理需要定期更新的数据时,您经常需要添加多行。
在本指南中,我们将告诉您如何通过几个快速简单的步骤在Excel中插入多行。(Excel)选择最适合您的。
如何通过拖动边框(Border)选择插入多行(Insert Multiple Rows)
这是在Excel 工作(Excel)表中插入多个空白行的最快方法。这很简单,也不需要使用任何键盘快捷键(keyboard shortcuts)。
- 选择要在其下方或上方输入空白行的行。
- 将指针悬停(Hover)在边框选择上。
- 按Shift 键(Shift ),您会看到指针变为带有向上和向下箭头的点。向下拖动选择以获取要添加的行数。
如何使用 Excel 键盘快捷键插入多行(Insert Multiple Rows Using Excel Keyboard Shortcuts)
如果您在处理Excel(Excel)工作表时不喜欢多次右键单击(multiple right-clicks)的想法,可以使用两个键盘快捷键。
首先,选择与您要输入的空白行数相等的行数。使用Shift + Space选择所选行的所有列,或使用左侧的行号选择行。
然后使用两个快捷方式之一:
- Alt + I and Alt + R:按Alt + I,按住Alt键,然后按 R。
- Ctrl + Plus:您需要在数字键盘上使用加号。如果要使用主键盘上的加号,请使用Ctrl + Shift + Plus。
快捷方式也是可扩展的。在Excel工作表上按F4会重复上一个操作。例如,您可以使用上述快捷方式之一添加 10 行,然后按F4 10次以添加 100 个空白行。
如何使用键盘快捷键插入多个复制的行(Insert Multiple Copied Rows Using Keyboard Shortcuts)
如果要复制或剪切多行而不是添加空白行,请使用Alt + I + E快捷键。
- 首先复制或剪切行。
- 通过按左侧的行号选择要在其上方粘贴行的行。(above)
- 按Alt + I + E并选择Shift cells down。
您还可以使用以下快捷方式代替Alt + I + E(无论您使用哪种快捷方式,该过程都是相同的):
- Ctrl + Shift + =
- Control + Plus(加号必须来自数字键盘)
如何使用插入选项(Insert Option)插入多行(Insert Multiple Rows)
要使用此方法,您只需选择要添加的行数,右键单击所选内容,然后选择Insert。
如果数据中有很多列,请按Shift + Space选择所选行的所有列,或使用工作表左侧的行号选择行。
当您选择Insert时,Excel将在您选择的第一行上方插入行。
如果要输入相对较多的空白行,可以选择一个范围,然后使用“插入(Insert)”选项。例如,假设您要输入 500 行。
- 选择格式为“起始行:最后一行”的范围。假设您要在第 5 行之后输入 500 行,请通过在单元格名称框中输入此范围来选择范围 5:504。
- 右键单击并选择插入(Insert)。
如何使用Excel 宏(Excel Macro)插入多行(Insert Multiple Rows)
如果您在希望经常使用的工作表上处理大型数据集,则可以使用Macros自动插入行。您无需成为专家,尽管了解VBA 的基础知识(basics of VBA)可以在您使用Excel时为您提供强大的功能。
在开始该过程之前,请检查顶部的Excel功能区是否有一个名为(Excel)Developer的选项卡。如果没有,请转到Files > Options > Customize Ribbon并检查(Customize Ribbon)Developer旁边的单选按钮。
- 导航到Developer选项卡并选择Visual Basic。
- Visual Basic 编辑器(Visual Basic Editor)打开后查找Insert菜单并选择Module。
- 在编辑器中输入(Enter)以下代码:
Sub InsertRowsAtCursor()
Answer = InputBox (“要插入多少行?(最多 100(Rows)行(Rows))”)
NumLines = Int(Val(Answer))
If NumLines > 100 Then
NumLines = 100
End If
If NumLines = 0 Then
GoTo EndInsertLines
End If
Do
Selection.EntireRow.Insert
Count = Count + 1
Loop While Count < NumLines
EndInsertLines:
End Sub
- 退出代码编辑器。
- 从顶部功能区中,选择Developer > Macros或直接按Alt + F8。选择您刚刚创建的宏,然后选择选项(Options)。为宏设置快捷键,但确保它不是您经常使用的现有组合键,例如Ctrl + C。在本例中,我们使用了Ctrl + Shift + T。完成后,选择确定(OK)以保存更改并退出。
- 返回工作表并选择要在其下方输入行的单元格。
- 使用您为宏设置的快捷方式(在本例中为Ctrl + Shift + T)。您将在工作表上看到一个弹出窗口,要求您输入要添加的行数。例如,假设您要输入五行。输入 5(Enter 5)并按Enter。
- 这将在您选择的单元格下方(below)添加 5 行。
每次要在Excel中插入多行时,只需使用快捷方式,输入行数,即可完成。
在 Excel 中插入多行
如果您经常使用 Excel(frequently use Excel),那么值得了解如何在重复的机械任务(例如插入行)上节省时间。由于有多种方法可以在Excel中插入多行,这只是您觉得合适的方法的问题。如果您在数据集中添加了很多空白行,也有几种方法可以删除空白行(ways to delete blank rows)。
How to Quickly Insert Multiple Rows in Excel
If your work requіres you to practically live in Excel sheets, уou may be looking for ways to make reрetitive tasks simpler. For instance, whenever you work with data that needs to be updated regularly, you’ll frequently nеed to add multiple rows.
In this guide, we’ll tell you how to insert multiple rows in Excel in a few quick and easy steps. Choose one that works best for you.
How to Insert Multiple Rows by Dragging Border Selection
This is the quickest way to insert multiple blank rows into your Excel sheet. It’s easy and doesn’t require using any keyboard shortcuts either.
- Select the row below or above which you want to enter blank rows.
- Hover your pointer over the border selection.
- Press Shift and you’ll see the pointer change into a point with an upward and downward arrow. Drag the selection down for the number of rows you want to add.
How to Insert Multiple Rows Using Excel Keyboard Shortcuts
If you don’t like the idea of multiple right-clicks while you work on your Excel sheet, there are two keyboard shortcuts you can use.
First, select the number of rows equal to the number of blank rows you want to enter. Use Shift + Space to select all columns for the selected rows or select rows using row numbers on the left.
Then use either of the two shortcuts:
- Alt + I and Alt + R: Press Alt + I, keep holding the Alt key, and press R.
- Ctrl + Plus: You need to use the plus sign on the numeric pad. If you want to use the plus sign on the main keypad, use Ctrl + Shift + Plus.
The shortcut method is scalable too. Pressing F4 on an Excel sheet repeats the previous action. You could use one of the above shortcuts to add, for instance, 10 rows and subsequently press F4 10 times to add 100 blank rows.
How to Insert Multiple Copied Rows Using Keyboard Shortcuts
If you want to copy or cut multiple rows instead of adding blank rows, use the Alt + I + E shortcut.
- Start by copying or cutting the rows.
- Select the row above which you want to paste the rows by pressing the row number at the left.
- Press Alt + I + E and choose Shift cells down.
You could also use the following shortcuts instead of Alt + I + E (the process is the same regardless of which shortcut you use):
- Ctrl + Shift + =
- Control + Plus (plus sign must be from the numeric keypad)
How to Insert Multiple Rows Using the Insert Option
To use this method, all you need to do is select the number of rows you want to add, right-click on the selection, and select Insert.
If you have a lot of columns in your data, press Shift + Space to select all columns for the selected rows, or select the rows using the row numbers at the left of the worksheet.
When you select Insert, Excel will insert rows above the first row that you selected.
If you want to enter a relatively larger number of blank rows, you could select a range and then use the Insert option. For instance, say you want to enter 500 rows.
- Select a range in the format ‘starting row : final row’. Assuming you want to enter 500 rows after row 5, select the range 5:504 by entering this range in the cell name box.
- Right-click and select Insert.
How to Insert Multiple Rows Using an Excel Macro
If you’re working with a large dataset on a worksheet that you expect to use regularly, you could automate row insertion using Macros. You don’t need to be an expert, though knowing the basics of VBA can give you a lot of power while you use Excel.
Before you start the process, check if the Excel ribbon at the top has a tab called Developer. If it doesn’t, go to Files > Options > Customize Ribbon and check the radio button beside Developer.
- Navigate to the Developer tab and select Visual Basic.
- Look for the Insert menu once the Visual Basic Editor opens and select Module.
- Enter the following code in the editor:
Sub InsertRowsAtCursor()
Answer = InputBox(“How many Rows to insert? (100 Rows maximum)”)
NumLines = Int(Val(Answer))
If NumLines > 100 Then
NumLines = 100
End If
If NumLines = 0 Then
GoTo EndInsertLines
End If
Do
Selection.EntireRow.Insert
Count = Count + 1
Loop While Count < NumLines
EndInsertLines:
End Sub
- Exit the code editor.
- From the top ribbon, select Developer > Macros or simply press Alt + F8. Select the macro you just created and select Options. Set a shortcut key for the macro, but ensure it’s not an existing key combination that you use frequently, such as Ctrl + C. In this case, we’ve used Ctrl + Shift + T. When you’re done, select OK to save changes and exit.
- Return to the worksheet and select a cell below which you want to enter the rows.
- Use the shortcut you set for the macro (in this case, Ctrl + Shift + T). You’ll see a pop-up on the worksheet asking you to enter the number of rows you’d like to add. For instance, let’s say you want to enter five rows. Enter 5 and press Enter.
- This will add 5 rows below the cell you had selected.
Every time you want to insert multiple rows in Excel, just use the shortcut, enter the number of rows, and you’re done.
Inserting Multiple Rows in Excel
If you frequently use Excel, it’s worth learning about how you can save time on repetitive, mechanical tasks such as inserting rows. Since there are several ways to insert multiple rows in Excel, it’s just a matter of what method you feel comfortable with. If you’ve added a lot of blank lines within the data set, there are several ways to delete blank rows, too.