在本文中,我将介绍 10 个Excel
技巧,您可以使用它们来提高工作效率并让您的工作生活更轻松。请务必通读整个列表,以便找到对您最有用的提示。
我将直接跳到下面的动作中。确保(Make)您将此页面添加为书签,以便在您忘记我将分享的任何有用提示时返回此处。
使用状态栏
每当您突出显示一系列数字时,Excel底部的状态栏都会为您提供一些有用的信息。您可以找到有关数字的总和、平均值和总数的信息。
很多人都知道状态栏,但你知道你可以右键添加更多功能吗?例如,您可以在一个范围内添加最小值和最大值。
使用快捷方式进行快速导航
如果您需要快速浏览工作表,可以使用以下键盘快捷键。
- Ctrl+End –导航到您输入数据的最右边的单元格。
- Ctrl+home – 导航到开头。
- Ctrl+right箭头 - 导航到Excel工作表的最右侧。
- Ctrl+left箭头 - 导航到Excel工作表的最左侧。
- Ctrl+up箭头 - 导航到Excel工作表的顶部。
- Ctrl+down箭头 - 导航到Excel工作表的底部。
粘性行
如果您希望某些行在滚动浏览Excel工作表时保留在其顶部,请通过单击左侧的字母来( clicking on its letter)选择该行,然后单击顶部的查看。(View )接下来,单击冻结窗格(Freeze Panes)以提供以下选项:
- 冻结窗格 -(Freeze Panes – Sticky)全部粘贴,基于当前视图。
- 冻结顶行 - 仅粘(Freeze Top Row – Sticky)在顶行。
- 冻结第一列 – 仅粘贴(First Column – Stick)第一列。
快速选择公式
如果您不断输入公式,此提示可以节省大量时间。当您开始输入公式时,您可以使用向上/向下箭头键浏览建议的公式,并使用 Tab 键自动选择该公式。这种方法通常比每次都输入完整的公式要快得多。
自动填写信息
如果您正在填写数据范围,您可以突出显示该范围,然后向下拖动以使用以下信息自动填充更多单元格。有很多方法可以用来提高工作效率。例如,它可用于自动输入连续日期。
Excel很擅长猜测您要自动填写哪些信息。例如,如果您有一列每隔三天显示一次,它可以使用自动填充功能遵循该模式。
创建宏以自动执行任务
如果您在Excel中重复执行任何任务,您可以创建一个宏来自动执行该过程。为此,请按照以下步骤操作。
- 单击文件。(File.)
- 单击选项。(Options.)
- 单击自定义功能区。(Customize Ribbon.)
- 在Main选项卡下,选择以激活Developer box 。
- 单击确定。(OK.)
- 现在,单击Excel功能区顶部的新开发人员(Developer )选项卡。
- 之后,单击录制宏(Record Macro)按钮。
- 现在,为宏命名(name the macro)并为其选择一个快捷方式。(choose a shortcut for it.)
- 您还可以添加说明,以便将来更轻松地进行操作。
- 接下来,只需执行您通常在Excel上执行的任何任务。
- 完成后,单击停止录制。(Stop Recording.)
- 您现在可以使用您创建的快捷方式来立即执行您录制的任务。
在几秒钟内创建一个漂亮的桌子
您可以按照以下步骤在几秒钟内创建具有视觉吸引力的表格。
- 首先,在表格中突出显示您想要的数据。
- 转到插入( Insert )选项卡。
- 单击表。(Table.)
- 突出显示的区域现在将在其周围有一个边框。
- 单击确定(OK)以创建表。
您可以使用右上角的表格图标来更改颜色,并使用设计选项卡下的复选框来更改布局。
一旦光标悬停在表格上,您也可以单击表格下方的小图标。这将打开一个小菜单,用于创建图表、表格、格式规则等。
使用 Go To 查找特定单元格
如果您的Excel工作表包含大量数据,则很难追踪所有内容。您可以使用Go To(e Go To)工具更轻松地查找特定单元格。它的工作原理类似于Microsoft Word中的(Microsoft Word)查找(Find)工具。
- 首先,选择(select the range of data)您要在其中查找信息的数据范围。
- 或者,只需按 Ctrl+A即可选择所有内容。
- 在右上角的主页(Home)选项卡上查找编辑(Edit )部分。
- 单击转到(Go To)。
- 然后,您可以输入一个值、单词或使用“特殊”选项卡(enter a value, word, or use the ‘special’ tab)来查找更具体的元素。
快速提示:(Quick tip: )您可以在以后轻松查找的任何单元格上创建评论,然后在使用“转到”时使用“特殊(Special )”选项卡中的“评论”选项。(Comment)
更改“输入”的工作方式
默认情况下,按Enter会将您向下移动一个单元格,但是有一种方法可以更改Enter在Excel 中(Excel)的工作方式。要更改此设置,请转到File,然后Options,然后单击Advanced选项卡。
从这里,您可以选择Enter
键是向上、向下、向右还是向左移动。或者,您可以完全关闭该功能。如果您希望将输入作为确认数据的简单方式并希望手动导航单元格,这将非常有用。
一次(Once)将相同的数据(Same Data)复制到多个单元格(Multiple Cells)中
您可以快速将一个单元格的数据复制到任意多个单元格中。为此,首先在要复制的单元格上按Ctrl+C接下来(Next),突出显示要复制到的单元格(highlight the cells you’d like to copy to),然后按Ctrl+V。
概括
(Did)这些Excel技巧被证明有用吗?但愿如此。如果您对本文中包含的提示有任何疑问,请在Twitter 上(Twitter)与我联系,我会尽快回复您。享受!
10 Excel Tips and Tricks for 2019
In this article, I’ll be covering 10 Excel
tips that you can use to boost produсtivity and make your work lіfe easier.
Makе sure you read through the entire list so that you can find the tips that
will be most useful to you.
I’ll be jumping straight into the action
below. Make sure you bookmark this page so that you can come back here in case
you forget any of the useful tips I’ll be sharing.
Make Use of the Status Bar
Whenever you highlight a range of numbers, the
status bar at the bottom of Excel will feed you some useful information. You
can find information about the sum, the average, and the total count of
numbers.
Many people know about the status bar, but did
you know that you can right click it to add more features? For example, you can
add the minimum and maximum values in a range.
Use Shortcuts for Quick Navigation
If you need to navigate through a sheet
quickly, you can use the following keyboard shortcuts.
- Ctrl+End – navigate to the
furthest right cell you’ve entered data in.
- Ctrl+home – navigate to the start.
- Ctrl+right arrow – navigate to the
furthest right of the Excel sheet.
- Ctrl+left arrow – navigate to the
furthest left of the Excel sheet.
- Ctrl+up arrow – navigate to the
top of the Excel sheet.
- Ctrl+down arrow – navigate to the
bottom of the Excel sheet.
Sticky Rows
If you’d like some rows to stay at the top of the Excel sheet as you scroll through it, select the row by clicking on its letter on the left side, then click View at the top. Next, click Freeze Panes to be given the following options:
- Freeze Panes – Sticky all, based
on current view.
- Freeze Top Row – Sticky only the
top row.
- Freeze First Column – Stick only
the first column.
Quick Select Formulas
This tip can save a lot of time if you are
constantly entering formulas. As you begin to type a formula, you can use the
up/down arrow keys to go through suggested formulas and the tab key to
automatically select that formula. This method is often much faster than typing
out a formula in full each time.
Auto Fill Information
If you are filling out a range of data, you
can highlight that range, then drag downward to autofill more cells with the
following information. There are many ways you can use this to be more
productive. For example, it can be used to automatically enter consecutive
dates.
Excel is smart at guessing what information
you’d like to auto fill. For example, if you have a column that shows every
third day, it can follow that pattern with the auto fill feature.
Create a Macro to Automate a Task
If there are any tasks you repeatedly perform
in Excel, you can create a macro to automate the process. To do this, follow
the steps below.
- Click File.
- Click Options.
- Click Customize Ribbon.
- Under the Main tab, select to activate the Developer box.
- Click OK.
- Now, click the new Developer tab at the top of the Excel ribbon.
- After that, click the Record Macro button.
- Now, name the macro and choose a shortcut for it.
- You can also add a description to make things easier for you in the future.
- Next, simply perform any task just like you’d normally do on Excel.
- Once you’ve done, click Stop Recording.
- You can now use the shortcut you’ve created to instantly perform the task you recorded.
Create a Beautiful Table in Seconds
You can create visually appealing tables in
seconds by following the steps below.
- First, highlight the data you’d like in the table.
- Go to the Insert tab.
- Click Table.
- The highlighted area will now have a border around it.
- Click OK to create the table.
You can use the table icons at the top right
to change the colors, and use the checkboxes under the design tab to change up
the layout.
You can also click the small icon underneath
the table once your cursor is hovering over the table. This will open a small
menu for creating charts, tables, formatting rules, and more.
Use Go To to Find Specific Cells
If your Excel sheet has an overwhelming amount of data, it becomes difficult to track everything down. You can use the Go To tool to find specific cells easier. It works similarly to the Find tool in Microsoft Word.
- First, select the range of data you’d like to find information in.
- Alternatively, just press Ctrl+A to select everything.
- Look for the Edit section on the Home tab at the top right.
- Click Go To.
- You can then enter a value, word, or use the ‘special’ tab to find more specific elements.
Quick tip: You can create a comment on any cells you’d like to find easily later on and then use the Comment selection in the Special tab when using Go To.
Change How ‘Enter’ Works
By default, pressing Enter will move you a cell down, but there is a method to change how Enter works in Excel. To change this, go to File, then Options, then click on the Advanced tab.
From here, you can choose whether the Enter
key will move you up, down, right, or left. Alternatively, you can just turn
off the functionality completely. This is useful if you’d like to keep enter as
a simple way to confirm data and would prefer to navigate cells manually.
Copy the Same Data into Multiple Cells at Once
You can quickly copy the data of one cell into as many cells as you’d like. To do this, first press Ctrl+C on the cell you’d like to copy. Next, highlight the cells you’d like to copy to, then press Ctrl+V.
Summary
Did these Excel tips prove to be useful? I hope so. If you have any questions about the tips I’ve included in this article, please connect with me on Twitter and I’ll get back to you as soon as I can. Enjoy!