Home

Automatically Filling Cells
 
  

Introduction
 

Microsoft Excel shares a lot of characteristics with window applications: the ability to search through the document for a particular word or phrase, the ability to find a word or group of words and replace it with another word or expression.
 

A feature available in Microsoft Excel as a spreadsheet application is the ability to fill out some cells with values that belong to a common series.

Overview of AutoFill

Microsoft Excel recognizes series of items so far as they can be clearly identified, either by the common language or by defining them explicitly in a worksheet. Common series include time, dates, weekdays, or months, etc. Whenever Microsoft Excel can identify the content of a cell as being a series, you can use the Fill Handle to copy adjacent values of the series to neighbor cells. If a series is not obvious, you need to clearly define it so the application can recognize the allure you want to use for the series.

To use the AutoFill, first select a cell or the cells that define the series. Position your mouse on the lower right corner of the selected cell or cells, and then drag in the appropriate direction.

Practical Learning: Exploring the AutoFill
  1. In Microsoft Excel, press Ctrl + N to start a new empty workbook.
  2. In cell B2, type 1st Qtr
  3. Position your mouse on the lower right corner of the selected cell, on the squared point. This point is called the Fill Handle. Your mouse will turn into a +
     
    The Fill Handle Mouse Pointer
  4. Click on the Fill Handle, hold the mouse down and drag in the right direction until you get to cell E2
     
    Filling the cells
  5. Release the mouse. Now you get 1st Qtr, 2nd Qtr, 3rd Qtr, and 4th Qtr
  6. In cell B4, type 1st Grade
  7. Drag the Fill Handle in the right direction until you get to cell G4. Then release the mouse to see the subsequent grades
  8. In cell B6, type 1:00
  9. Drag the Fill Handle in the right direction until you get to cell H6. Then release the mouse to see the hours displayed
  10. In cell B8, type 9:00 and drag its Fill Handle to cell H8. Notice how Microsoft Excel displays time values in subsequent cells
  11. Click Cell B8 and notice the value displayed in the Formula Bar: 9:00:00 AM
  12. In cell B10, type 13:00 and drag its Fill Handle to H10. Notice how times get displayed in the other cells.
  13. In cell B12, type 9:00 AM and drag its Fill Handle to cell H12. Notice how this time, unlike row 8, Microsoft Excel changed the AM to PM after 11:00 AM:
     
    Fill Handle
  14. In cell B14, type Jan and drag its Fill Handle to cell H14. Notice how the application displays months in subsequent cells
  15. In cell B16, type Monday and drag its Fill Handle to cell H16. The computer displays all weekdays
  16. In cell B18, type 02/24/2008 and drag its Fill Handle to cell H18. Dates incrementing each day by 1 are displaying in the other cells
  17. Click the Sheet2 tab
  18. Sometimes Microsoft Excel will not recognize a series with just one value, this happens when the series is not obvious. For example, if you type 1970 in a cell and drag the Fill Handle, the computer would not know whether you want to increment the number by 1, 2, 4, or 10. So you have to specify how the series will be applied. To define a series, type the beginning value, then in the subsequent cell type the incremental values or the value that will be used to define the series.
    In cell B2, type 1970, press Tab
  19. In cell C2, type 1974
  20. Select cells B2:C2
  21. Position the mouse on the Fill handle, which is on the lower right border of cell C2 (because both cells are selected). When the mouse becomes a +, drag the Fill Handle in the right direction up to cell H2. The cell values have been incremented by 1 starting on cell B2
  22. In cell B4, type 1
  23. In cell C4, type 2
  24. In cell B5, type 0
  25. In cell C5, type 2
  26. In cell B6, type 0
  27. In cell C6, type 3
  28. In cell B7, type 100
  29. In cell C7, type 101
  30. In cell B8, type 100
  31. In cell C8, type 125
  32. Select cells B4:C8
  33. Position your mouse on the Fill Handle in the lower right corner of the selected cells (in cell C8). Then drag the Fill Handle to column H and release the mouse:
     
    Fill Handle
  34. Notice that Microsoft Excel could figure out how to handle each series. The first was to increment numbers by 1 starting at 1. The second was asked to list even numbers starting at 0. The third gave a list of odd numbers starting at 0. The third had to count by 1 starting at 100. The fourth was going to find quarter values in a 100 number basis starting at 100
  35. In cell B10, type Jan
  36. In cell C10, type Apr
  37. Select cells B10:C10. Drag the Fill Handle of the selected cells to cell E10. You get the first month of every quarter
  38. In cell B12, type 1st Qtr
  39. In cell A13, type Jan and drag the Fill Handle of cell A13 to cell C13. That will display the months of the first quarter
  40. Select cells A12:C13. Drag the Fill Handle of the selected cells to cell L13

Fill Handle
 
 
 

AutoFill Application

As the AutoFill features provides a quick means of completing cells that can host series data, this feature can be applied in various scenarios.

When creating time sheets, use the weekdays and time periods to complete adjacent cells. In a yearly sales report, Microsoft Excel can recognize series such as months, quarters, and years. In a school’s spreadsheet used to collect students’ grades, a series can be created from 1st Grade, and then dragging the Fill Handle, Microsoft Excel will complete other cells with subsequent class grades.

Practical Learning: Applying the AutoFill
  1. Open the Grier Summer Camp4 workbook
  2. Click the Employment Application3 sheet tab
  3. Click Cell B19
  4. Position the mouse on the lower right corner of the selected cell, on the point, until the mouse pointer becomes a + sign. Click and hold your mouse down, then drag in the right direction to cell H19. That will update the weekdays
  5. Save the workbook
  6. Access the CPAR1.xlsx workbook and click the Transaction analysis tab
  7. Click Cell B6 and position the mouse on the lower-right corner of the cell
  8. Click and drag down to Cell B17
  9. Save and close the workbook
 
   
 
Previous Copyright © 2007-2009 FunctionX, Inc. Home