MS EXCEL AUTOFILL FEATURE

Customize and Use the AutoFill feature in Excel

Do you get annoyed by tedious, time-consuming tasks? ME TOO!! One task I automate in Excel is filling patterns by using the AutoFill feature. Use this feature to populate common terminology such as Dates, Months, Days of the Week, Numbers, Text with patterns you create. You can even create your own pattern that may make sense only to you and not Excel

Objectives:

  • Use the AutoFill Default Feature in Excel
  • Create a Pattern for Excel to Use in AutoFill
  • Create a Custom Pattern for Excel to Follow for AutoFill

What is AutoFill?

AutoFill is a super-cool feature in Excel that allows you to quickly fill in data across cells based on a specific pattern. Think of it as a mind reader that recognizes the patterns you wish to enter in the worksheet!

AutoFill works with values, dates, and text. You can create sequential lists of numbers, months and dates to avoid that repetitive typing stuff.

Using AutoFill is straightforward. Use the fill/copy mouse shape to hover over the small square at the bottom-right corner of a selection. Drag it in any direction, and fill adjacent cells based on a recognized pattern.

AutoFill Defaults

Fill a Number Sequence: Select the number to begin your sequence. Place the FILL/COPY mouse face on the square in the right-hand corner of the selection. Click and drag the mouse until you complete your fill series. You may need to let Excel know that it is a FILL NOT A COPY by clicking on the smart icon and choosing FILL SERIES instead of COPY.

Populate a Number Pattern: Select the number pattern to begin your sequence. Place the FILL/COPY mouse face on the square in the right-hand corner of the selection. Click and drag the mouse until you complete your fill series.

Fill Days of Weeks or Months: Type or select the day or Month with which you wish to begin your sequence. Place the FILL/COPY mouse face on the square in the right-hand corner of the selection. Click and drag the mouse until you complete your fill series.

Filling Dates: Type or select the with which you wish to begin your sequence. Place the FILL/COPY mouse face on the square in the right-hand corner of the selection. Click and drag the mouse until you complete your fill series.

Custom Lists

Create your own pattern list: If Excel doesn’t recognize a list pattern, such as a custom list of names, the alphabet or a list of departments, then you can edit the Custom List Dialog Box in Excel.

  1. File–Options
  2. Choose Advanced Category
  3. Under General, Choose Edit Custom Lists Button
  1. Enter your List entries by typing them and either place a comma between the entries or press ENTER between entries OR Import them from a list from cells in your worksheet.
  2. Choose Add
  3. Choose OK; OK
  1. Type the first entry in your custom list and use the fill handle to complete it.

Use a Formula or Function to Create a List

PUT (Power User Tip)
Use the fill handle to create default lists and save time. Create Custom lists if Excel does not recognize the sequence. Use the following functions for even more flexibility!

CHAR FUNCTION

The CHAR function in Excel is a command used to generate specific characters using a number code. CHAR uses the American Standard Code For Information Interchange, or ASCII, which is a collection of numbers between one and 255 that specify characters for computers. For example, the Capital letter A is 65 in the ASCI code, so to generate the letter A you would use the following.

=CHAR (65)

Uppercase and lowercase letters in ASCII character set reside between the numbers 65-90 and 97-122, respectively. 65 = A, 66 = B, 90 = Z, 97= a, etc. Using character conversion formulas like CHAR, we can convert numbers to corresponding letters.

The challenge for this approach is generating numbers for the CHAR function. Use the SEQUENCE function with the CHAR Function. The SEQUENCE function can generate an array of sequential numbers automatically by using your input of where to start in the ASCI table and how many numbers you need.

For Upper Case Alphabet Fill

=CHAR(SEQUENCE(26,1,65))

For Lower Case Alphabet Fill

=CHAR(SEQUENCE(26,1,97))

¡IMPORTANT!
These functions will return an ARRAY. You may not alter (delete, move copy, etc.) any part of an array, therefore if you want to manipulate the data simply copy it and use Paste Special to paste it as a value.

There you have it! Some great ways to save time and frustration by using Excel’s Autofill feature. How can you get even more creative after trying these techniques? Let me know, I’d love to hear from you. Until then, Have an EXCEL-LENT day!!–Stephanie

➤