autofill in excel
share TWEET PIN IT share share 0

Tech Tips

How to Master Autofill in Microsoft Excel

Let Excel do the data entry work for you!

By Danielle DavisRoe

Book cover Microsoft Excel for Legal ProfessionalsDo 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.

Numbers

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.

Dates

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.

Times

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:

  1. 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.
  2. Select the list (drag with your mouse).
  3. In the File menu, click on the Options button.
  4. 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.
  5. Click on the Edit Custom Lists button.
  6. Click on the Import button.
  7. Click OK.

To use the custom list in any workbook:

  1. Type a name in the custom list you previously created.
  2. Use the autofill handle (as described above) to populate the list across a row or up/down a column.

AutoFilling Formulas

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.

©iStockphoto.com

More Microsoft Office Tips:

How to Master Page Numbers in Microsoft Word

Using Styles to Create an Automated Table of Contents in Microsoft Word (Video)

Categories: Legal Technology, Microsoft Office Tips for Lawyers, Office 365, Tech Tools
Originally published August 14, 2020
Last updated September 17, 2020
share TWEET PIN IT share share
Danielle Danielle DavisRoe

Danielle DavisRoe is a senior consultant with Affinity Consulting Group’s legal document automation team (@affinitylegal). Whether it’s teaching clients a new skill, writing a macro to automate a complicated task, fixing a document that doesn’t work properly, or automating a complex set of documents, Danielle is 100% focused on making the lives of her clients better. She has a Bachelor of Science in Business Administration from The Ohio State University Fisher College of Business and a Juris Doctorate from The Ohio State University Moritz College of Law.

More Posts By This Author
envelope

Welcome to Attorney at Work!

Sign up for our free newsletter.

x

All fields are required. By signing up, you are opting in to Attorney at Work's free practice tips newsletter and occasional emails with news and offers. By using this service, you indicate that you agree to our Terms and Conditions and have read and understand our Privacy Policy.