How to Use AutoFill in Excel to Save Time on Data Entry
If you want to save some time and energy on your data entry tasks, we’ll show you how to use AutoFill in Excel for text, dates, formulas, and more.
When it comes to entering the data into your Microsoft Excel spreadsheets, there are bound to be times when it’s simply a tedious task. You may want to enter a series of text, numbers, or dates, which can be time-consuming for large spreadsheets. Luckily, Excel provides a wonderful feature to save you time called AutoFill.
Using AutoFill, you can automatically fill cells without manually typing the data into each one. Here, we’ll show you exactly how to use AutoFill and save yourself some time and energy with your data entry.
What is AutoFill in Excel?
In a nutshell, AutoFill in Excel helps you fill cells in rows or columns with data based on the other cells. You can fill cells down, up, or across and the most common types of data include series of numbers, days, months, years, dates, times, and lists.
For instance, you may want to list out the months of the year in the first column. You can type the first month you want to start with and simply drag through the cells below to add the upcoming months in order.
In addition, you can use AutoFill to automatically format cells the same as others. For example, maybe you use a fill color and bold text for some of the cells in a row. Using AutoFill, you can drag through all cells to apply just the formatting, without the data.
AutoFill a Text Series or List
Starting off with our basic example, here’s how to fill a series of months in a column.
- Type the first item in the series in the cell. Here, we’ll enter January.
- With the cell selected, move the cursor to the bottom right corner until you see the Fill Handle (black plus sign) appear.
- Hold your left mouse button and drag through the remaining cells in the column to automatically fill them. You’ll notice as you drag, a small box displays with the data to be placed into each cell.
- Release your mouse when you finish.
Filling this type of text series is basically using a list in Excel; whether a list of months in the year or days in the week. But in Excel, you can create and AutoFill custom lists too. This is helpful if you enter the same items over and over in your sheets. It could be a list of products, colors, or even people.
If you have custom lists set up in Excel, use the same AutoFill steps above and your list should be recognized and populate correctly as you drag.
AutoFill a Date Series
Many spreadsheets contain dates and if you are listing out dates for daily tasks, for instance, Excel can help get the job done.
- Enter the first date in the cell.
- Select the cell and move the cursor to the corner until the Fill Handle.
- Hold your left mouse button, drag through the remaining cells, and release when you finish.
You’ll notice that Excel knows to increase the middle number for the date, not the last number for the year. And you can use the same steps to go backward in time by dragging upward or to the left, depending on how your sheet is laid out. Excel knows how to work with dates and complies.
AutoFill a Patterned Series
You can already see that Excel is a smart tool. So when it comes to using AutoFill for a series, it can recognize patterns in a series. Let’s say you have dollar amounts that increase by $50 for savings tracking.
- Add the first two amounts in the series so that Excel can detect the pattern.
- With both cells selected, move the cursor to the corner until the Fill Handle.
- Hold your left mouse button and drag through the remaining cells, releasing when you finish.
Another example of a pattered series takes us back to dates. Say you want to fill dates in a column that are the first of each month.
- Add the first two dates in the series for Excel to detect as a pattern.
- With both cells selected, move the cursor to the corner until the Fill Handle.
- Hold your left mouse button and drag through the remaining cells, releasing when you finish.
AutoFill Formulas
If you use simple formulas in Excel like sum, average, maximum, or minimum, AutoFill can come in handy for those too. Maybe you have a spreadsheet with columns of numbers or currency and you want to total at the bottom of each. This is an ideal time to use AutoFill.
- Select the first cell containing the formula.
- Move your cursor to display the Fill Handle.
- Hold your left mouse button, drag through the adjacent cells, and release.
As you can see in the screenshot below, we copied the formula in the cells to the right in that same row and it copied correctly!
AutoFill Formatting
Another convenient feature of AutoFill in Excel is the ability to fill formatting. Maybe the cells in your spreadsheet are formatted a particular way and you want to carry that formatting to the adjacent cells.
- Select the cell with the formatting you want to copy.
- Move your cursor to the corner until you see the Fill Handle.
- This time hold your right mouse button and drag through the cells where you want to apply the formatting.
- Release the button and you’ll see a shortcut menu appear.
- Choose Fill Formatting Only.
You should see those cells immediately transform into the copied formatting.
You might notice in that shortcut menu another formatting option: Fill Without Formatting. This is a convenient option if you want to AutoFill the data from cells but do so without copying the formatting. So, be sure to keep this one in mind and check out our tutorial for conditional formatting in Excel for even more ways to apply formatting.
AutoFill in Excel is a Simple Time-Saver
When it comes to entering data into an Excel spreadsheet, any way that you can save yourself from that tedious task is priceless. That’s why AutoFill in Excel is such a great feature!
Let us know if it works for you! You can hit us up on Twitter or Facebook with your thoughts.
