(Autofill)Microsoft Excel中的自动填充是一个出色的工具，它可以减轻数据输入的大部分工作。通过使用“自动填充”(Autofill)按钮或只需拖动光标，您可以在几秒钟内自动填充单元格。然而，这个功能的内涵远不止表面上看到的那样。
您可以使用自动填充(Autofill)来执行简单的任务，例如填写一周中的几天。但您可以进一步使用此工具来填充日期、模式和格式。此外，您可以使用按一定量增加的值填充单元格，甚至可以合并不同行中的文本。
即使您以前使用过自动填充(Autofill)，也请查看一下使用您可能没有意识到的功能的各种方法。没错，是时候充分利用Excel中的(Excel)自动填充(Autofill)功能了。
自动填充列表
从使用Excel 自动填充(Excel Autofill)执行的最简单的任务开始，您可以轻松填充项目列表。这包括内置列表或您创建的自定义列表(lists you create)。
Excel提供完整单词或内置的三个字母缩写形式的月份和日期。让我们使用日期列表作为示例。
- 在单元格中输入(Enter)第一个列表项，然后选择该单元格。
- (Drag)沿要填充单元格的方向(Fill Handle)拖动单元格右下角的填充手柄（方形）。拖动时，您将在经过每个单元格时看到每个列表项。
- 当你到达终点时松开，你会看到填充的单元格。
如果您在Excel(Excel)中设置了自定义列表，应用程序也会在您使用自动填充(Autofill)时识别这一点。在这里，我们有一个自定义颜色列表，可以使用Autofill输入。
提示：要查看或创建列表，请转到File > Options。选择(Select Advanced)左侧的“高级”和右侧的
“编辑自定义列表”按钮。(Edit Custom Lists)
自动填充模式
使用自动填充的(Autofill)另一个好方法是当您的数据中有模式时。也许您的数据集中有一系列数字或字母。通过选择两个或多个单元格，您可以拖动以填充剩余的单元格。
作为一个基本示例，我们有字母 A、C 和 E。我们希望按该顺序使用相同的字母填充剩余的单元格。
- 至少选择两个包含要复制的模式的单元格。在这里，我们选择所有三个单元格，因为这是整个模式。
- 沿要填充的方向拖动(Drag)单元格右下角的
填充手柄(Fill Handle)。
- 当您填充所需数量的单元格时释放。
自动填充日期
当您在Excel(Excel)中输入日期时，应用程序足够智能，可以识别这一点，从而允许您使用自动填充(Autofill)来完成一系列连续的日期。此外，您可以通过调整月、日或年来填写日期，从而为您提供多个选项。
基本填充调整日期
默认情况下，当您对短日期(Short Date)格式 ( MM/DD/YYYY自动填充(Autofill)时，您会看到日期自动增加。
- 使用上述格式在单元格中输入日期并选择该单元格。
- (Drag)向要填充的方向
拖动填充手柄(Fill Handle)。
- 当您填充所需数量的单元格时释放。
如果您选择长日期(Long Date)格式（日(Day)、月(Month)、日(Date)和年(Year)），则其工作方式相同。您会看到日期增加一。
其他日期自动填充选项
除了上述两个简单的日期填充选项之外，您还可以选择增加工作日、月份或年份而不是日期(years rather than the dates)。
- 使用您喜欢的格式在单元格中输入日期，然后选择该单元格。
- 按住鼠标右键，向要填充的方向
拖动填充手柄。(Fill Handle)
- 当您达到要填充的单元格数量时，松开鼠标，您将看到一个弹出窗口。
- 从天、工作日、月或年中选择您想要使用的填充类型。
然后您将看到您的日期已填充您选择的选项。在这里，我们选择了月份。
(Autofill)带或不带格式的
自动填充
在Excel中使用自动填充的(Autofill)另一个超级方便的方法是格式化。您可以仅使用所选单元格的格式填充单元格，也可以使用不包含格式的数据填充单元格。让我们看几个例子。
填充格式
在这里，我们有一个使用红色斜体字体和黄色填充颜色格式化的单元格。(cell formatted)我们计划在不相关的单元格中输入其他数据，但希望复制该格式以节省一些时间。
- 选择包含要填充的格式的单元格。
- 按住鼠标右键，向要填充的方向
拖动填充手柄。(Fill Handle)
- 当您达到要填充的单元格数量时，松开鼠标，您将看到该弹出窗口。
- 选择仅填充格式。
然后，当您在填充的单元格之一中输入文本时，您会看到其格式与所选单元格相同。
不带格式填充
也许您想做与上述相反的事情。您可以填充数据但删除格式。在这里，我们将使用与上面相同格式的单元格；然而，我们只想填写日期。
- 选择包含要填充的数据的单元格。
- 按住鼠标右键，向要填充的方向
拖动填充手柄。(Fill Handle)
- 当您达到要填充的单元格数量时，松开鼠标，您将再次看到该弹出窗口。
- 选择填充但不设置格式。
然后，您将看到数据填充单元格，但没有格式化。
自动填充线性金额
如果您想在每个单元格中填充增加的数字，也可以使用自动填充。(Autofill)使用线性趋势(Linear Trend)选项，每个金额都会增加您选择的单元格中的金额。最好用一个例子来解释这一点。
这里，我们有数字 20 和 30。使用线性趋势自动填充(Linear Trend Autofill)，我们可以填充剩余的单元格，每个单元格增加 10，因为这是在 20 到 30 之间的增量。
- 选择(Select)包含数字的两个单元格。
- 按住鼠标右键，向要填充的方向
拖动填充手柄。(Fill Handle)
- 当您达到要填充的单元格数量时，松开鼠标，您将看到该弹出窗口，但有其他可用选项。
- 选择线性趋势。
然后您就会看到增加的数字。
精确的线性量
如果您希望选择增加的确切值以及停止值，则可以使用“自动填充”(Autofill)按钮和单个单元格来完成此操作。
- 选择包含该数字的单元格。
- 转到“主页”(Home)选项卡，然后打开“编辑”组中的(Editing)“自动填充”(Autofill)下拉菜单。选择系列(Choose Series)。
- 在“系列”(Series)对话框中，将要填充的相邻单元格的“行(Rows)”或“列”(Columns)选项标记为“类型”，并将“线性”(Linear)标记为“类型”(Type)。注意：使用“填充手柄”时， (Fill Handle)“趋势”复选框执行与上面的(Trend)“线性趋势”(Linear Trend)选项相同的操作。
- 输入(Enter)步长(Step)值，即您想要增加每个单元格的数字。（可选）输入“停止”(Stop)值，该值是您想要结束系列的数字。选择“确定”(Select OK)。
然后，您将看到单元格从您选择的数字开始填充，向每个单元格添加步长值，最后以停止值结束。
自动填充增长金额
与上面的线性趋势(Linear Trend)选项类似的是增长趋势(Growth Trend)。这里的区别在于数字乘以步长值而不是相加。
在这里，我们有数字 2 和 6。通过增长趋势自动填充(Growth Trend Autofill)，我们可以用 3 倍数增加的所有数字填充剩余的单元格，因为这是 2 到 6 之间的增长。
- 选择(Select)包含数字的两个单元格。
- 按住鼠标右键，向要填充的方向
拖动填充手柄。(Fill Handle)
- 当您达到要填充的单元格数量时，松开鼠标，您将看到弹出窗口。
- 选择增长趋势。
然后您将看到相乘的数字。
确切的增长量
如果您希望选择增长(Growth)增量的精确值以及停止值，您可以使用“自动填充”(Autofill)按钮来执行此操作，就像使用“线性自动填充”(Linear Autofill)一样。
- 选择包含该数字的单元格。
- 转到“主页”(Home)选项卡，打开“自动填充”(Autofill)下拉菜单，然后选择“系列”(Series)。
- 在框中，标记要填充位置的“行”(Rows)或“列”(Columns)选项，并将“增长”(Growth)标记为“类型”(Type)。注意：使用“填充手柄”时， (Fill Handle)“趋势”复选框执行与上面的(Trend)“增长趋势”(Growth Trend)选项相同的操作。
- 输入(Enter)步长(Step)值，该值是您要与每个单元格相乘的数字。（可选）(Optionally)输入“停止”(Stop)值，该值是您想要结束系列的数字。选择“确定”(Select OK)。
然后，您将看到单元格从您选择的数字开始填充，乘以步长值，最后以停止值结束。
自动填充以合并文本
使用自动填充(Autofill)可以执行的另一项节省时间的任务是将不同行中的文本合并到单个单元格中。这称为填充对齐(Fill Justify)，是一个非常酷的功能。
- 首先增加将包含组合文本的列的宽度。您只需选择列标题并拖动右侧出现的箭头即可增加宽度。如果跳过此步骤，结果将不准确。
- 选择包含文本的行中的单元格。
- 转到“主页”(Home)选项卡，打开“自动填充”(Autofill)下拉菜单，然后选择“对齐”(Justify)。
然后您将看到所有单元格中的文本合并到一个单元格中(cells combined into a single cell)。
充分发挥自动填充的潜力
(Autofill)Excel中的自动填充功能不仅仅可以用于填充一周中的几天。通过这些更高级的用途，您不仅可以节省时间，还可以同时降低工作表中因拼写错误或计算错误而出现错误的风险。
有关更多信息，请参阅如何在 Excel 中使用快速填充(how to use Flash Fill in Excel)。
How to Autofill in Microsoft Excel
Autofill in Microsoft Excel is a wondеrful tоol that takes much of the work out of data entrу. By using the Autofill button or simрly dragging your cursor, you can auto-populate сellѕ іn ѕeсonds. However, there’s more to this feature than meets the eye.
You can use Autofill for simple tasks like filling in the days of the week. But you can take this tool further and fill dates, patterns, and formatting. What’s more, you can fill cells with values that increase by a certain amount and even combine text from different rows.
Even if you’ve used Autofill before, take a look at these various ways to use the feature that you may not realize exist. That’s right, it’s time to make the most out of the Autofill feature in Excel.
Autofill a List
Starting with the simplest task you can perform with Excel Autofill, you can easily fill a list of items. This includes built-in lists or custom lists you create.
Excel offers months and days as full words or three-letter abbreviations built right in. Let’s use the list of days as an example.
- Enter the first list item in a cell and then select the cell.
- Drag the Fill Handle (square) in the bottom right corner of the cell in the direction you want to fill the cells. As you drag, you’ll see each list item as you pass through each cell.
- Release when you reach the end and you’ll see your filled cells.
If you have custom lists set up in Excel, the application recognizes this as you use Autofill too. Here, we have a custom list of colors that we can enter using Autofill.
Tip: To view or create lists, go to File > Options. Select Advanced on the left and the Edit Custom Lists button on the right.
Autofill a Pattern
Another great way to use Autofill is when you have a pattern in your data. Maybe you have ranges of numbers or letters in your dataset. By selecting two or more cells, you can drag to fill the remaining cells.
For a basic example, we have letters A, C, and E. We want to fill the remaining cells with the same letters in that order.
- Select at least two cells containing the pattern you want to replicate. Here, we select all three cells because that’s the entire pattern.
- Drag the Fill Handle in the lower right corner of the cell in the direction you want to fill.
- Release when you fill the number of cells you want.
Autofill Dates
When you enter dates in Excel, the application is smart enough to recognize this which allows you to use Autofill to complete a sequential series of dates. Additionally, you can fill in the dates by adjusting the month, day, or year, giving you more than one option.
Basic Fill Adjusting Dates
By default, when you use Autofill for the Short Date format (MM/DD/YYYY), you’ll see the date increase automatically.
- Enter a date in a cell using the above format and select the cell.
- Drag the Fill Handle in the direction you want to fill.
- Release when you fill the number of cells you want.
If you choose the Long Date format instead (Day, Month, Date, and Year) it works the same way. You’ll see the date increase by one.
Other Date Autofill Options
Along with the above two simple fill options for dates, you have additional options to increase the weekdays, months, or years rather than the dates.
- Enter a date in a cell using your preferred format and select the cell.
- Hold your right mouse button as you drag the Fill Handle in the direction you want to fill.
- When you reach the number of cells you want to fill, release and you’ll see a pop-up window.
- Choose the type of fill you want to use from days, weekdays, months, or years.
You’ll then see your dates filled with the option you selected. Here, we picked months.
Autofill With or Without Formatting
Another super handy way to use Autofill in Excel is for formatting. You can fill cells with only the formatting from your selected cell or with the data not including the formatting. Let’s look at a couple of examples.
Fill Formatting
Here, we have a cell formatted with red, italic font and a yellow fill color. We plan to enter other data in the cells unrelated but want to copy that formatting to save some time.
- Select the cell containing the formatting you want to fill.
- Hold your right mouse button as you drag the Fill Handle in the direction you want to fill.
- When you reach the number of cells you want to fill, release and you’ll see that pop-up window.
- Choose Fill Formatting Only.
Then, when you enter text into one of the cells you filled, you’ll see it’s formatted the same as your selected cell.
Fill Without Formatting
Maybe you want to do the opposite of the above. You can fill the data but remove the formatting. Here, we’ll use a cell with the same formatting as above; however, we only want to fill the dates.
- Select the cell containing the data you want to fill.
- Hold your right mouse button as you drag the Fill Handle in the direction you want to fill.
- When you reach the number of cells you want to fill, release and you’ll see that pop-up window again.
- Choose Fill Without Formatting.
Then, you’ll see the data fill the cells but without the formatting.
Autofill Linear Amounts
If you have numbers you want to fill that increase in each cell, you can use Autofill as well. With the Linear Trend option, each amount is increased by the amount in the cells you select. This is best explained with an example.
Here, we have numbers 20 and 30. With the Linear Trend Autofill, we can fill the remaining cells with each increasing by 10 because this is in the increase between 20 and 30.
- Select both cells containing the numbers.
- Hold your right mouse button as you drag the Fill Handle in the direction you want to fill.
- When you reach the number of cells you want to fill, release and you’ll see that pop-up window but with additional options available.
- Select Linear Trend.
You’ll then see your increased numbers.
Exact Linear Amounts
If you prefer to select the exact value for the increase as well as the stopping value, you can do this using the Autofill button and a single cell instead.
- Select the cell containing the number.
- Go to the Home tab and open the Autofill drop-down menu in the Editing group. Choose Series.
- In the Series dialog box, mark the options for Rows or Columns for the adjacent cells you want to fill and Linear as the Type. Note: The Trend checkbox performs the same action as the Linear Trend option above when using the Fill Handle.
- Enter the Step value which is the number you want to increase each cell. Optionally, enter a Stop value which is the number you want to end the series by. Select OK.
You’ll then see your cells filled starting with the number you selected, adding the step value to each, and ending by the stop value.
Autofill Growth Amounts
Similar to the Linear Trend option above is the Growth Trend. The difference here is that the numbers are multiplied by a step value rather than added.
Here, we have numbers 2 and 6. With the Growth Trend Autofill, we can fill the remaining cells with all numbers increasing by multiples of 3 because this is the increase between 2 and 6.
- Select both cells containing the numbers.
- Hold your right mouse button as you drag the Fill Handle in the direction you want to fill.
- When you reach the number of cells you want to fill, release and you’ll see the pop-up window.
- Select Growth Trend.
You’ll then see your multiplied numbers.
Exact Growth Amounts
If you prefer to select the exact value for the Growth increase as well as the stopping value, you can do this using the Autofill button just like with the Linear Autofill.
- Select the cell containing the number.
- Go to the Home tab, open the Autofill drop-down menu, and choose Series.
- In the box, mark the options for Rows or Columns for where you want to fill and Growth as the Type. Note: The Trend checkbox performs the same action as the Growth Trend option above when using the Fill Handle.
- Enter the Step value which is the number you want to multiply each cell by. Optionally, enter a Stop value which is the number you want to end the series by. Select OK.
You’ll then see your cells filled starting with the number you selected, multiplying the step value, and ending by the stop value.
Autofill to Combine Text
One more time-saving task you can perform with Autofill is to combine text in different rows into a single cell. This is called Fill Justify and is a super cool feature.
- Start by increasing the width of the column that will contain the combined text. You can simply select the column header and drag the arrow that appears on the right to increase the width. If you skip this step, the result won’t be accurate.
- Select the cells in the rows containing the text.
- Go to the Home tab, open the Autofill drop-down menu, and choose Justify.
You’ll then see the text from all cells combined into a single cell.
Use Autofill’s Full Potential
Autofill in Excel can be used for more than simply filling in days of the week. With these more advanced uses, you can not only save time, but reduce the risk of errors from typos or miscalculations in your worksheet at the same time.
For more, look at how to use Flash Fill in Excel too.
