Do you regularly find yourself entering repetitive data or formulas in your spreadsheets? You’re not alone. The following tips tell how to use autofill in Microsoft Excel to quickly enter lists and repetitive formulas like a pro.
Excel can automatically continue a series (list) of numbers, dates or time periods based on a pattern you establish. You can also set up custom lists and use autofill to automatically replicate them in new worksheets.
Additionally, autofill can be used to enter repetitive formulas across rows or columns.
The Fill Handle
Invoke autofill by selecting the appropriate cells and dragging the fill handle. The fill handle is located in the bottom right-hand corner of the selected cells. It appears as a small square in the outline around the selected cells.
You can drag the fill handle in any direction. Drag it down a column to populate the series downward, or up a column to populate it upward. Drag it across a row to populate the series to the right (or to the left).
Built-in AutoFill Examples
Excel is set up to autofill patterns based on numbers, dates, times or combinations of numbers and text.
Excel can extend various numerical patterns. The most common numerical pattern is to count (1, 2, 3, 4, 5, etc.). However, if you enter a single number (such as a 1), select it, and drag and then release the fill handle, the cells will all populate with the number 1. To fix the issue, you need to give Excel a pattern to replicate. Instead, type “1” in the first cell and “2” in the second cell. Select both cells; then use the fill handle.
Excel can also autofill numbers based on other patterns, such as odd or even numbers. Start by entering two or more numbers in their own cells (such as “1” and “3”), selecting them, and using the fill handle.
Excel can extend a series of dates. The most common date-based pattern is to increment dates (1/1/2020, 1/2/2020, 1/3/2020, etc.) Unlike numerical patterns, if you enter a date and use the fill handle, Excel will automatically increment the date by one day for each cell you autofill, as show in the figure below. Unlike the Numbers example above, you do not need to enter multiple dates to begin.
If you’d like to increment by a different number of days, by months or by years, start by entering the first two dates in their own cells. Then select those cells and use the fill handle. Full dates are not required to autofill. Excel can also extend a series of months, days of the week, quarters or years.
Like dates, Excel can also extend series of times. To extend times by one hour, you only need to enter the initial hour. Select the cell and autofill from there. Anything more complicated than that will require entering the pattern in multiple cells first.
Custom AutoFill Lists
You can also create your own autofill lists. For example, if you’re always creating worksheets that contain a list of attorneys in your department and you’re tired of typing them in every time, you can let autofill do the work for you. Follow these steps to make that list a permanent option and then drop it into a new workbook.
First, create the custom list:
- Type the list of names, each name in its own cell across a row or down a column. Either a row or column will work.
- Select the list (drag with your mouse).
- In the File menu, click on the Options button.
- In the Options dialog, click on the Advanced settings on the left-hand side, and scroll down to the General section on the right-hand side.
- Click on the Edit Custom Lists button.
- Click on the Import button.
- Click OK.
To use the custom list in any workbook:
- Type a name in the custom list you previously created.
- Use the autofill handle (as described above) to populate the list across a row or up/down a column.
Sometimes, you need to repeat a formula, such as the total of a column. In the first row, enter the formula to sum that row. Then, repeat that formula in the other rows.
You can use the autofill handle to copy the formula from one cell to the next, just as if you were creating a list. The formula will automatically adjust the cell references as it autofills.
In this new tips series, experts from Affinity Consulting Group offer straightforward answers to common questions about popular software programs used in law offices. These tips are from their book “Microsoft Excel for Legal Professionals.” Written specifically for lawyers and legal professionals, it is is an easy read, full of numbered steps and screen illustrations. Download your copy of the book in the Attorney at Work bookstore, or opt for a license for all your attorneys.