Microsoft Excel是任何行业中使用最广泛的工具之一。作为一个典型的电子表格程序,它允许用户在工作簿中导入和存储大量数据。MS Excel针对数据分析和计算进行了优化。这个灵活的程序还可用于构建分析数据的模型、编写简单和复杂的公式以对该数据执行计算、以任何可能的方式从数据中提取数据透视表,并使用具有专业外观的图表呈现数据。当用户在Excel上使用数据时,他们经常发现需要在Microsoft Excel中删除多行。
当用户处理大型数据集时,有很多方法可以删除单个行甚至数千行。在本指南中,您将了解一次性删除(delete multiple rows)Microsoft Excel 中多行的不同技巧。
如何删除Excel中的多行(Multiple Rows)
您可以尝试以下方法删除Microsoft Excel中不需要的多行:
- 通过上下文菜单删除Microsoft Excel中的多行
- 使用快捷方式删除(Shortcut)Microsoft Excel中的多行
- 按单元格颜色删除Excel中的多行
- (Delete)通过运行宏删除多行(Rows)
进一步阅读以了解这些技巧是如何工作的。
1.通过上下文菜单删除(Delete)Microsoft Excel中的多行
当您打算删除多个连续行时,此技巧很有效,请按照下列步骤操作:
1]打开包含您要操作的数据的Microsoft Excel工作表。(Open Microsoft Excel)
2]从数据中,选择要一次性删除的所有行。
3]现在,右键单击选择以打开上下文菜单。
4]点击“删除(Delete)”。
5]然后从删除选项列表中选择“整行”并点击“(Entire row’)确定”。(Ok’.)
或者,您也可以点击MS Excel工作表上的“(MS Excel)主页(Home)”选项卡并导航到“单元格(Cells)”组。单击“删除(Delete)”选项旁边的箭头按钮展开选项。现在从下拉菜单中选择“删除工作表行(Delete Sheet Rows)”以删除不需要的行。
2.使用快捷方式(Shortcut)删除(Delete)Excel中的多行
在这里,我们将使用最常用的快捷键之一,即“ CTRL+ Minus (-) ‘。让我们了解如何使用它:
- 一次尝试选择多行(Select multiple rows in a single attempt)
1]在您的工作表上一次尝试选择(Select)不需要的行以删除Microsoft Excel中的多行。
2]现在,按' Ctrl + – '删除所做的选择。
请注意(Please note):现在,如果列或行不连续,您必须按住键盘上的CTRL按钮并单独单击不需要的行来选择单独的行。
选择完成后,按提到的快捷方式。(Once the selection is completed hit the shortcut as mentioned.)
- 行后批量删除数据(Mass Delete Data after row)
例如,如果您有一个数据集,您需要在其中删除所有超过行的数据,例如 30。这可以通过使用快捷键' CTRL + Shift + ? ' 选择从起点到最后的所有行。选择行后,从上下文菜单(contextual menu)或“主页”选项卡中的(Home)单元格组(cell group)中点击删除,或者只需点击“ CTRL + -.'
3.按单元格颜色删除(Delete)Excel中的多行
Excel 提供了这个惊人的过滤器选项,允许用户以不同的方式对数据进行排序——按单元格颜色排序就是其中之一。这允许您删除包含特定背景颜色的所有列。
1]打开包含您要操作的数据的Microsoft Excel工作表。(Open Microsoft Excel)
2] 要将文件管理器应用于您的表,请转到“数据”(Data’)选项卡,然后点击“过滤器”(Filter’)图标。
3]现在单击目标列名称旁边出现的小箭头。
4] 转到“按颜色过滤(Filter by color)”并选择要删除的正确单元格颜色。
5]单击确定(Click OK),您将在顶部看到所有选定的单元格。
6]现在选择过滤后的彩色单元格,右键单击它们,然后从选项列表中选择“删除行”。(Delete Row)
具有相同颜色的线条将立即被删除。
4.通过运行宏删除(Delete)多个行(Rows)
对于那些在Microsoft Excel中有需要重复执行的任务的人,可以通过录制宏轻松实现自动化。宏是一个动作或一组动作,用户可以按照他/她的意图多次运行。因此,您也可以创建一个宏来删除(Hence)Microsoft excel中的多行;以下是步骤:
1]选择(Select)要删除的目标行。
2] 按键盘上的“ ALT+F11 ”键打开VBA 编辑器(VBA editor)。
3]单击(Click)左侧窗格中的第一个项目。
4]]导航(Navigate)到菜单栏并选择'Insert > Module'。
5]在新窗口中粘贴(Paste)以下代码以选择多个单元格进行删除:
更改(Alter)行号引用(“C4:C6”)以选择不需要的特定行。
6 ] 最后(] Finally),运行宏脚本。您可以单击“运行”(Run’)按钮或按快捷键F5删除 Excel 中的多行。
完毕!现在,只要需要操作类似的数据,就执行相同的宏。(Macro)
Microsoft Excel是存储和存储不时更改的数据的一站式目的地。但是更新数据可能是一项乏味的任务。即使是更小的事情,比如删除行,也会变得很耗时,并且可能需要付出很多努力。我们希望本指南对您有所帮助并使事情变得容易一些。
相关阅读:(Related Reads:)
- 对初学者最有用的 10 个 Excel 提示和技巧(10 most useful Excel Tips and Tricks for beginners)
- 高级 Microsoft Excel 提示和技巧。
How to delete Multiple Rows in Microsoft Excel in one go
Microsoft Excel is one of the most widely used tools in any industry. As a typical spreadsheet program, it allows the users to import and store large amounts of data in workbooks. MS Excel is optimized for data analysis and calculation. This flexible program can also be used to build models for analyzing data, write simple and complex formulas to execute calculations on that data, pull pivot tables from the data in any possible way, and present data with professional-looking charts. While users play around with data on Excel, often they find a need to delete multiple rows in Microsoft Excel.
When users deal with large data sets, there are many ways in which they can delete individual rows to even thousands of rows. In this guide, you will know the different tricks to delete multiple rows in Microsoft Excel in one go.
How to delete Multiple Rows in Excel
You can try the following methods to delete multiple rows in Microsoft Excel, which are unwanted:
- Deleting multiple rows in Microsoft Excel through the contextual menu
- Deleting multiple rows in Microsoft Excel using Shortcut
- Deleting multiple rows in Excel by cell color
- Delete multiple Rows by running a Macro
Read further to know how these tricks work.
1. Delete multiple rows in Microsoft Excel through the contextual menu
This trick works well when you intend to delete multiple consecutive rows, follow these steps:
1] Open Microsoft Excel sheet which has the data you wish to manipulate.
2] From the data, select all the rows you want to delete in one stretch.
3] Now, right-click on the selection to open the contextual menu.
4] Hit ‘Delete’.
5] Then select ‘Entire row’ from the delete options list and hit ‘Ok’.
Alternatively, you can also hit the ‘Home’ tab on the MS Excel worksheet and navigate to the ‘Cells’ group. Expand the options by clicking on the arrow button appearing next to the ‘Delete’ option. Now from the drop-down menu select ‘Delete Sheet Rows’ to remove the unwanted rows.
2. Delete multiple rows in Excel using Shortcut
Here we are going to use one of the most used shortcut keys i.e. ‘CTRL+ Minus (-) ‘. Let us understand how to use it:
- Select multiple rows in a single attempt
1] Select the unwanted rows in a single attempt on your worksheet to delete multiple rows in Microsoft Excel.
2] Now, press ‘Ctrl + – ‘to delete the selection made.
Please note: Now if the columns or rows are non-consecutive, you will have to select individual rows by pressing and holding the CTRL button on your keyboard and clicking individually on the unwanted rows.
Once the selection is completed hit the shortcut as mentioned.
- Mass Delete Data after row
In case you have a data set in which you need to delete all the data past row is 30 for example. This can easily be done by using the shortcut ‘CTRL + Shift + ?’ to select all rows from your starting point to last. Once the rows are selected hit delete either from the contextual menu or cell group from the Home tab or simply hit ‘CTRL + -. ‘
3. Delete multiple rows in Excel by cell color
Excel offers this amazing filter option that allows the users to sort their data in different ways – and sorting by cell color is one of them. This allows you to delete all the columns containing a specific background color.
1] Open Microsoft Excel sheet which has the data you wish to manipulate.
2] To apply filers to your table, go to the ‘Data’ tab, and hit the ‘Filter’ icon.
3] Now click on the small arrow appearing right next to the name of the target column.
4] Go to ‘Filter by color’ and select the correct cell color you wish to delete.
5] Click OK and you will see all the selected cells at the top.
6] Now select the filtered colored cells, right-click on them, and select ‘Delete Row’ from the options list.
The lines with the same color will be removed in no time.
4. Delete multiple Rows by running a Macro
For those who have tasks in Microsoft Excel that they need to do repeatedly, it can be easily be automated by recording a macro. A macro is an action or a set of actions that can be run by a user as many times he/she intends to. Hence you can create a macro to delete multiple rows in Microsoft excel too; here are the steps:
1] Select the targeted rows that you want to delete.
2] Press ‘ALT+F11’ keys on your keyboard to open the VBA editor.
3] Click the first project from the left-hand pane.
4]] Navigate to the menu bar and select ‘Insert > Module’.
5] Paste the following code in the new window to select multiple cells for deletion:
Alter the row number references (“C4:C6”) to select the specific rows which are unwanted.
6] Finally, run the macro script. You can either click the ‘Run’ button or press the shortcut F5 to delete multiple rows in Excel.
Done! Now execute the same Macro whenever there is a need to manipulate similar data.
Microsoft Excel is a one-stop destination for storing and storing data that changes from time to time. But updating the data can be one tedious task; even smaller things like deleting rows can become time-consuming and may demand a lot of effort. We hope this guide does help you and make things a bit easy.
Related Reads:
- 10 most useful Excel Tips and Tricks for beginners
- Advanced Microsoft Excel Tips and Tricks.