How do I create a custom autofill list from values in a range in Excel?
share TWEET PIN IT share share 0 Tech TipsHow to Master Autofill in Microsoft ExcelLet Excel do the data entry work for you!By Danielle DavisRoeDo you regularly find yourself entering repetitive data or formulas in your spreadsheets? Youre not alone. The following tips tell how to use autofill in Microsoft Excel to quickly enter lists and repetitive formulas like a pro. Show 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 HandleInvoke 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 ExamplesExcel is set up to autofill patterns based on numbers, dates, times or combinations of numbers and text. NumbersExcel 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. DatesExcel 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 youd 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. TimesLike 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 ListsYou can also create your own autofill lists. For example, if youre always creating worksheets that contain a list of attorneys in your department and youre 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:
To use the custom list in any workbook:
AutoFilling FormulasSometimes, 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 publishedAugust 14, 2020 Last updated September 17, 2020 share TWEET PIN IT share share
|