This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?

Microsoft Excel 2007

Using the Fill Command

Excel provides many ways to repeat information in many cells throughout a worksheet. The most convenient way to repeat information in contiguous cells is by using the Fill command. If the first cell contains a formula, the formula will be repeated in the additional cells. If the first cell contains text, the text will be repeated in the additional cells. You can allow Excel to automatically fill in the information for you, or you may choose to create custom patterns of information.

NOTE: If Excel recognizes a pattern in the information you entered, the additional cells will contain the next item in the pattern. For example, if the first cell contains the day Sunday, Excel will fill the following cells with Monday, Tuesday, etc. Other examples include filling months of the year and hours of the day.

return to topFilling Cells

Filling Cells: Ribbon Option

This option copies (repeats) the information from one cell to another. To extend the series, refer to the mouse option.

  1. Type the information (cell contents or formula) in the first cell of the group

  2. Starting with the cell containing the contents to be copied, select the group of cells to be filled

  3. From the Ribbon, select the Home command tab

  4. In the Editing group, click FILLFill button» select the appropriate option:

Down
repeats the contents of the first cell throughout the selected cells below it.

Right
repeats the contents of the first cell throughout the selected cells to the right of it.

Up
repeats the contents of the first cell throughout the selected cells above it.

Left
repeats the contents of the first cell throughout the selected cells to the left of it.

Across Worksheets
repeats the contents of the selected cells in another selected worksheet.

Series...
fills in data that is part of a pattern, rather than simply repeating the data across selected cells. This option brings up the Series dialog box, which allows you to select the type of pattern you would like to use.
EXAMPLE: If the first cell contains the day Sunday, Excel will fill the following cells with Monday, Tuesday, etc.

Justify
distributes text in the first selected cell throughout several selected rows.
NOTE: This option changes the contents of these cells by moving the data to fit within the new rows.

  1. OPTIONAL: If you selected the Series option
    1. In the Series dialog box, select the desired options:

Series in
determines whether the additional information will appear in a vertical line (rows) or in a horizontal line (columns).

Type
determines whether the additional information will differ from the previous entry by a specific interval (Linear), by multiplying the previous entry by a specific value (Growth), by creating a series of dates according to the selected date format (Date), or by automatically detecting what type of fill you would like to apply (AutoFill).

Date Unit
when Date is selected as the type, determines whether numbers are listed for each day (Day) for each day in the weekday (Weekday), for that day of every month (Month), or for that day of every year (Year).

Trend
when Linear or Growth is selected as the type, calculates the average difference between values existing in selected cells and uses that difference to calculate the fill values.

Step Value
when Linear or Growth is selected as the type, determines the amount that the previous value is added to or multiplied by to come up with the results for the next cell. When Date is selected as the type, it determines how many dates should be skipped between each recorded value.

Stop Value
determines a value at which the series should no longer be continued.

  1. Click OK
    The fill is applied.

Filling Cells: Mouse Option

Using this option will extend the data in the series to the selected cells.

  1. Type the information (cell contents or formula) in the first cell of the group

  2. In this cell, move your pointer over the fill corner so your pointer changes into crosshairsCrosshairs pointer
    NOTE: For this option to work, you must ensure that the pointer changes into a crosshairs before filling.
     Fill Corner 

  3. Click and hold the crosshairs

  4. Drag the mouse in the direction you want the information to be copied
    NOTES:
    You can drag the corner in any one direction; left, right, up, or down.

  5. Release the mouse button
    The fill is applied.

return to topWorking with Custom Fills

Custom fills allow you to select what information you want repeated. For example, if you typed the UW-Eau Claire terms (Fall, Winterim, Spring, Interim and Summer) several times, custom fills allow you to automate the process. Using the regular Fill function, Excel would repeat "Fall" in all selected cells. With a custom fill, when you type Fall and use the Fill command, Winterim, Spring, Interim, and Summer will be entered in the adjacent cells. This feature is also handy when using Excel to create a grade book. For example, you could create a custom fill for letter grades (e.g., A, A-, B+, B, B-, ..., F).

Custom fills can be created from scratch or from an existing section of your worksheet. After creation, Excel will automatically apply your custom fills when using the AutoFill function.

NOTES:
Custom fills will work consistently only with word lists.
Lists of values do not work consistently. 

Creating a Custom Fill

  1. From the Office Button menuFile button, click EXCEL OPTIONSExcel Options button
    The Excel Options dialog box appears.

  2. From the Categories list, select Popular
    The dialog box refreshes to show the Popular options.

  3. In the Top options for working with Excel section, click EDIT CUSTOM LISTS...
    The Custom Lists dialog box appears.
    Custom List dialog box

  4. In the Custom lists scroll box, select NEW LIST

  5. In the List entries text box, type the information you want to be added in the custom fill, pressing [Enter] after each entry
    EXAMPLE: A, A-, B+, B, B-, ..., F
    NOTE: Be sure to type every unit in the order you want it to appear.
    Options dialog box - Custom Lists tab

  6. Click ADD
    Your new entries appear in the Custom Lists scroll list.

  7. Click OK
    The Custom Lists dialog box closes.

  8. Click OK
    The Excel Options dialog box closes.

Importing Custom Fills

If you have a group of cells that contains information you would like to repeat elsewhere, you can create a custom fill from them.

  1. Select the group of cells with the information you want to make into a custom fill

  2. From the Office Button menuFile button, click EXCEL OPTIONSExcel Options button
    The Excel Options dialog box appears.

  3. From the Categories list, select Popular
    The dialog box refreshes to show the Popular options.

  4. In the Top options for working with Excel section, click EDIT CUSTOM LISTS...
    The Custom Lists dialog box appears.
    Custom List dialog box

  5. Click IMPORT
    Your selection appears in the List entries text box.
    Custom List dialog box: Imported List options

  6. Click OK
    The Custom Lists dialog box closes.

  7. Click OK
    The Excel Options dialog box closes.

Using a Custom Fill

  1. To use your custom fill, place the cell pointer in any cell and type the first entry
    EXAMPLE: Type A
    HINT: If you have created several custom fills that begin with the same entry, you may need to type the first two entries in the first two cells in order for Excel to know which fill you want to use.

  2. In this cell, move your pointer over the fill corner so your pointer changes into crosshairsCrosshairs pointer
    NOTE: For this option to work, you must ensure that the pointer changes into a crosshairs before filling.
     Fill Corner 

  3. Click and hold the crosshairs

  4. Drag the corner in the direction you want the information to be copied
    NOTE: For more information about filling cells, refer to Filling Cells: Mouse Option.

  5. Release the mouse button
    The cells will be filled with the information you provided.

Excellence. Our Measure. Our Motto. Our Goal.