尽管有几个电子表格工具和其他类似的应用程序，我们仍然更喜欢与Microsoft Excel本身一起工作。这是因为如果您知道使用它的方法，这个电子表格程序会让您的工作变得非常方便。无论您是小商人还是在公司办公室工作，Excel工作表现在已成为日常用户非常有用且必不可少的资源。
该应用程序主要用于记录和操作用于各种目的的数值数据。由于其简单的功能和易于使用的特性，它一直是每个企业用户的主要手段。在本指南中，您必须了解 10 个主要有用的 Excel 提示和技巧。
Microsoft Excel 提示和技巧
在这篇文章中，我们将介绍以下我们认为最有用的Excel 提示(Excel Tips)和技巧：(Tricks)
- 一次选择所有单元格
- 自动调整列宽
- 转置列和行
- 使用过滤器命令(Use Filter Command)隐藏不需要的数据(Hide Unwanted Data)
- 删除重复条目
- 删除空白记录
- 将空白行插入记录
- 自动将数据复制(Copy Data)到另一个工作表(Sheet Automatically)
- 使用 Excel 相机截屏
- 将单元格链接到您的电子邮件地址
让我们开始并详细了解每一个。
1]一次选择所有单元格
有时您可能需要对Excel工作表进行一些重大更改。在这种情况下，您可以使用电子表格最左上角的方框选择工作表中的所有单元格。
当您选择方框时，它会突出显示工作表中的所有单元格，甚至是空单元格。作为替代方案，您还可以使用Ctrl+A键盘快捷键来选择工作表上的所有单元格。
2]自动调整列宽
在处理具有大量数据记录的MS Excel时，根据其文本调整每列单元格的大小通常会变得很复杂。(MS Excel)为了以简单的方式调整列的大小，您可以使用Excel中内置的(Excel)AutoFit列宽功能。
您只需将鼠标指针移动到要自动调整的列的边界，然后双击它。如果您必须调整整个列，则将它们全部选中并双击任何列的边界。
或者，您也可以使用自动调整列宽(Autofit Column width)选项来调整列宽。为此，请选择要调整的列，然后导航到菜单(Menu)栏 >主页(Home)选项卡。在单元格(Cells)部分中，单击格式(Format)选项，然后从下拉菜单中选择自动调整列宽。(Autofit Columns Width)
3]转置列和行
有些人创建Excel电子表格，其中主要字段连续水平排列。其他人将数据垂直放置。有时，这些首选项会导致您想要转置Excel数据的情况。Transpose是一个Excel函数，它允许您将Excel列切换为行，而无需重新键入数据。
转(Transpose)置功能是您将在MS Excel电子表格中获得的一项很棒的功能。当您需要在不重新键入数据的情况下将数据列从水平切换到垂直时，它会变得非常有用。
在上图中，数据以表格形式排列，受试者的数字垂直显示。如果我们需要以手动方式水平重新排列数据，那将是相当困难的。但是借助转置功能，您可以在几秒钟内完成。
因此，您首先需要从表中选择数据并复制它们。现在，将鼠标指针放在必须转置数据的位置，单击鼠标右键并从上下文菜单中选择“选择性粘贴”选项。(Paste Special)打开粘贴特殊(Paste Special)窗口时，请选中转(Transpose)置功能，然后单击“确定”按钮。
4]使用过滤器命令(Use Filter Command)隐藏不需要的数据(Hide Unwanted Data)
Excel过滤器功能允许您隐藏不需要的数据并在电子表格中仅显示某些重要数据。使用此功能，您可以轻松地从庞大的数据集或表中过滤名称、日期和数字。但是，它不会删除或修改原始数据。当您只需要关注某些特殊类型的信息时，它变得非常有用。
在上面的数据集中，你可以看到有一个分数不同的学生列表，他们的地址不同。它可以更大。因此，如果您需要过滤任何指定的数据，那么这可能会非常困难。
为了以简单的方式进行操作，您可以使用 filter 命令。因此，您只需要选择表格的标题，然后导航到“编辑(Editing)”部分。在相应的部分中，选择排序和筛选(Sort & Filter)，然后选择筛选(Filter)工具。
单击(Click)出现在每列标题附近的向下箭头选项。它允许您按字母顺序对数据进行排序或按从小到大排列数字。此外，您可以使用文本过滤器根据您的要求过滤数据。
5]删除重复条目
对于具有少量数据的电子表格，查找和删除重复条目是一项非常容易的工作。但是，如果必须使用列和行数以百计的大型数据集来完成，那么这确实是一项具有挑战性的任务。
在上表中，有十条记录，其中两个条目完全相似，并且在三行中使用了一个共同的名称。因此，为了找出重复的条目，您需要选择表。之后，导航到Data菜单，选择Data Tools类别，然后单击Remove Duplicates。
在对话框中，标记 My data has headers 旁边的复选框。并且，在列部分下，删除序列号的标记，因为每个条目都不同。最后，单击“确定(OK)”按钮，您将看到重复条目已从表中删除。
6]删除空白记录
有时您可能已经注意到电子表格中大部分不受欢迎的空白单元格或行。用户通常在指定的行上单击鼠标右键，然后将其一一删除。但是如果他们的人数是数百人，你会怎么做？在那种情况下，一个一个地删除它们确实是一件乏味的工作。
因此，要一次性删除它们，请按Ctrl+G快捷键，当出现“转到”(Go To)对话框时，选择“特殊(Special)”按钮。在下一页上，标记空白(Blanks)单选按钮，然后单击确定按钮。您会注意到所有空白单元格都被选中。
现在，按Ctrl+minus按钮。出现弹出菜单时，选择向上移动单元格，(Shift cells up)然后单击确定按钮。你会看到，所有的空白单元格都被删除了，数据集也得到了适当的排列。
7]将空白行插入记录
正如我之前提到的，空白行大多是不可取的，但有时它也可以。因此，如果您需要插入它们，您可以右键单击指定的行并按照我所说的在删除空白行的同时将它们一一添加。
因此，要在每个单独的记录中插入空白行，请制作一系列提到的序列号。之后，选择所有数据集，然后单击“数据”选项卡上可用的“(Data)排序(Sort)”工具。
在下面的对话框中，单击排序方式下拉菜单，并按序列号排列它们。
单击“确定”按钮，它将在每个单独的行之间插入一个空白行。
8]自动(Sheet Automatically)将数据复制(Copy Data)到另一张表
有时，您需要使用您在第一张表中输入的另一张表中的数据。为此，您通常使用传统的复制和粘贴方法。但是，当您需要更改一张纸中的某些数据时，它也必须再次更改另一张纸上的数据。当您必须一张一张地更改两张纸上的相同数据时，它变得有点复杂。
在 excel 中，有一个选项可以实时将第一张工作表的数据自动复制到其他工作表中。但是，要将数据自动复制到其他工作表，您需要先在它们之间建立连接。
为此，请转到第一张工作表的状态栏。之后，按住Ctrl键并单击必须自动复制数据的工作表。就是这样。
现在，无论您在第一张纸上输入什么数据，它都会自动复制到其他纸上。
9]使用Excel相机截图(Screenshot)
一直以来都说“一张图抵千言”。因此，有时您可能需要在Excel工作表中添加屏幕截图，以便更好地解释这些内容。
但是，您不需要使用任何第三方应用程序来捕获屏幕截图。Excel有一个内置的相机命令，可帮助您单击屏幕截图。
要使用此功能，您首先必须启用相机命令。因此，您只需导航到快速访问工具栏并单击自定义快速访问工具栏(Customize Quick Access Toolbar)。之后，单击从>All commands > Camera. 选择命令(Choose commands from)的下拉菜单。现在，选择添加(Add)按钮并点击确定。
完成上述步骤后，您会注意到一个相机(Camera)图标现已添加到快速访问工具栏中。现在，选择需要图像的单元格或数据。之后，单击相机(Camera)图标，然后单击工作表上的任意位置。您还可以拖动并调整图像大小以根据您的要求进行调整。
10]将单元格链接(Link)到您的电子邮件地址
在 Excel 工作表中，您可以轻松地创建指向您的Gmail地址的超链接。
为此，您首先需要选择要插入链接的单元格。之后，按Ctrl+K快捷键。当屏幕上出现对话框提示时，从左侧窗格中选择电子邮件地址。(E-mail Address)添加您的电子邮件地址，然后单击“确定”按钮。
就是这样。我希望这些提示和技巧在您的计算机上使用Excel工作表时可能会有所帮助。
Microsoft Excel Tips and Tricks for beginners
In spite of having sеveral spreadsheet tools and other ѕimilar appliсations, we stіll prefеr to work along with the Microsoft Excel itself. This is because this spreadsheet program makes your job pretty handy if you know the way to use it. Whether you are a small businessman or working in a corporate office, Excel worksheet has now become a quite helpful and essential resource for daily users.
This application is mainly used for recording and manipulating the numerical data for various purposes. Because of its straightforward functions and easy-to-use features, it has been always the primary means of every corporate user. In this guide, there are the 10 major helpful excel tips and tricks that one must need to know.
Microsoft Excel Tips and Tricks
In this post we will cover the following Excel Tips and Tricks, which we think are the most useful ones:
- Select all cells at once
- Autofit Column width
- Transpose columns and rows
- Use Filter Command to Hide Unwanted Data
- Remove Duplicate Entry
- Remove the Blank Records
- Insert Blank Rows into Record
- Copy Data to Another Sheet Automatically
- Take Screenshot using Excel Camera
- Link the cells to your email address
Let us start and know each one in detail.
1] Select all cells at once
Sometimes you might need to make some massive changes to your Excel worksheet. In such a case, you can select all the cells in your worksheet using the square box that is available in the extreme top-left corner of the spreadsheet.
When you select the square box, it highlights all the cells in the worksheet even the empty cells as well. As an alternative, you can also use the Ctrl+A keyboard shortcut to select all the cells on the worksheet.
2] Autofit Column width
While working on the MS Excel with a large amount of data record, often it becomes complicated to resize each column cells according to its text. In order to resize the columns in an easy way, you can use the AutoFit column width feature that is an inbuilt feature in Excel.
All you need to move your mouse pointer to the boundary of the column you want to autofit and then double click on it. And if you have to adjust the entire columns then select them all and double click on the boundary of any column.
Alternatively, you can also resize the column width by using the Autofit Column width option. To do this, select the column you want to adjust and then navigate to the Menu bar > Home tab. In the Cells section, click on the Format option and then select Autofit Columns Width from the drop-down menu.
3] Transpose columns and rows
Some people create Excel spreadsheets where the main fields go across horizontally in a row. Others put the data vertically. Sometimes these preferences lead to a scenario where you want to transpose Excel data. Transpose is an Excel function that allows you to switch Excel columns to rows without retyping the data.
Transpose function is a great feature you will get in the MS Excel spreadsheets. It becomes very helpful when you need to switch the data columns from horizontal to vertical without retyping the data.
In the above picture, the data are arranged in a tabular form where the figures of the subjects are showing vertically. If we need to rearrange the data horizontally in a manual way then it would be quite difficult. But with the help of transpose function, you can do it in a couple of seconds.
So, you first need to select the data from the table and copy them. Now, place your mouse pointer where you have to transpose the data, do a right-click and select Paste Special option from the context menu. When the Paste Special window opens, check the Transpose function and click on the OK button.
4] Use Filter Command to Hide Unwanted Data
Excel filter function allows you to hide the unwanted data and to display only certain important data in the spreadsheet. Using this feature, you can easily filter the names, dates, and numbers from the huge dataset or table. However, it doesn’t remove or modify the original data. It becomes very useful when you need to focus only on some special kind of information.
In the above dataset, you can see there is a list of students whose marks are different, their address is different. It can be large even more. So, if you need to filter any specified data then it can be quite difficult.
To make it in an easy way, you can use the filter command. So, all you need to select the header of the table and then navigate to the Editing section. In the corresponding section, select the Sort & Filter and then choose the Filter tool.
Click on the down-arrow option that comes to appear near the header of each column. It allows you to sort your data in alphabetical order or to arrange the numbers by smallest to largest. Furthermore, you can use the text filter to filter the data as per your requirement.
5] Remove Duplicate Entry
It is quite an easy job to locate and remove duplicates entries for a spreadsheet having a small set of data. But if it has to be done with a large dataset where the columns and rows are in hundreds then it would be indeed a challenging task.
In the above table, there are ten records in which two entries are totally similar and a common name has been used in three rows. So, in order to find out the duplicate entries, you need to select the table. After that, navigate to the Data menu, select the Data Tools category, and then click Remove Duplicates.
In the dialog box, mark the checkbox near the My data has headers. And, under the columns section, remove the mark of the serial number as it is different for each entry. Finally, click on the OK button and you will see the duplicate entry gets removed from the table.
6] Remove the Blank Records
Sometimes you might have noticed the blank cells or rows in your spreadsheet that are mostly undesirable. Users commonly do a right-click on the specified row and delete them one by one. But what will you do if their numbers are in hundreds? In that situation, deleting them one by one would really be a tedious job.
Therefore, in order to delete them in one go, press Ctrl+G shortcut key and when the Go To dialog box comes to appear, select the Special button. On the following page, mark the Blanks radio button and click on the OK button. You will notice that all the blank cells get selected.
Now, press the Ctrl+minus button. When a pop-up menu appears, select Shift cells up and click on the OK button. You will see, all the blanks cells get removed and the dataset also arranged suitably.
7] Insert Blank Rows into Record
As I aforementioned that blank rows are mostly undesirable but sometimes it works as well. So, if you need to insert them, you can do a right-click on the specified row and add them one by one as I stated while removing blank rows.
So, to insert the blank rows in each separate record, make a series of the mentioned serial number. After that, select all the data set and then click on the Sort tool available on the Data tab.
In the following dialog box, click on the Sort by drop-down menu, and arrange them by serial number.
Clicking on the OK button, it will insert a blank row between each separate row.
8] Copy Data to Another Sheet Automatically
Sometimes it happens that you need to use the data in another sheet which you enter in the first sheet. To do so, you commonly use the traditional copy and paste method. But, when you need to change some data in one sheet then it again has to change it on the other sheet as well. It becomes a bit complicated when you have to change the same data on both sheets one by one.
In excel, there is an option that automatically copies the data of the first sheet into other sheets in a real-time process. However, for copying the data automatically to other sheets, you need to first make a connection between them.
To do so, go to the status bar of the first sheet. After that, keep pressing the Ctrl key and click those sheets on which you have to copy the data automatically. That’s it.
Now whatever data you enter on the first sheet, it automatically gets copied on the other sheets.
9] Take Screenshot using Excel Camera
It has always been said that “A picture is worth a thousand words”. Accordingly, sometimes you may need to add a screenshot within your Excel worksheet so that you could explain the things in a better way.
However, you don’t need to use any third-party application to capture a screenshot. Excel has an inbuilt camera command that helps you to take the screenshot in a single click.
To use this feature, you first have to enable the camera command. So, all you need to navigate to the quick access toolbar and click on the Customize Quick Access Toolbar. After that, click on the drop-down menu of Choose commands from > All commands > Camera. Now, select the Add button and hit OK.
After following the above procedure, you will notice a Camera icon is now added to the quick access toolbar. Now, select the cells or data for which you need an image. After that, click on the Camera icon and then click anywhere on the worksheet. You can also drag and resize the image to adjust according to your requirements.
10] Link the cells to your email address
In the excel worksheet, you can easily create a hyperlink to your Gmail address.
To do so, you first need to select the cell where you want to insert the link. After that, press Ctrl+K shortcut. When the dialog box prompts on the screen, select the E-mail Address from the left pane. Add your email address and then hit the OK button.
That’s it. I hope these tips and tricks may be helpful while using Excel worksheets on your computer.
