Using the Fill Command in Excel (Filling Cells, Filling Cells: Ribbon Option, Filling Cells: Mouse Option, Working with Custom Fills, Creating a Custom Fill, Importing Custom Fills, Using a Custom Fill)
Filling 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
- In the Editing group, click FILL » select the appropriate option:
Down
repeats the contents of the first cell throughout the selected cells below it.
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.
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.
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.
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.
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.
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.
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.
- OPTIONAL: If you selected the Series option
- 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).
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).
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).
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.
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.
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.
determines a value at which the series should no longer be continued.
- 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 crosshairs
NOTE: For this option to work, you must ensure that the pointer changes into a crosshairs before filling.
NOTE: For this option to work, you must ensure that the pointer changes into a crosshairs before filling.
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.
NOTES:
You can drag the corner in any one direction; left, right, up, or down.
5. Release the mouse button
The fill is applied.
The fill is applied.
Working 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 menu, click EXCEL OPTIONS
The Excel Options dialog box appears.
2. From the Categories list, select Popular
The dialog box refreshes to show the Popular options.
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.
The Custom Lists dialog box appears.
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.
EXAMPLE: A, A-, B+, B, B-, ..., F
NOTE: Be sure to type every unit in the order you want it to appear.
6. Click ADD
Your new entries appear in the Custom Lists scroll list.
Your new entries appear in the Custom Lists scroll list.
7. Click OK
The Custom Lists dialog box closes.
The Custom Lists dialog box closes.
8. Click OK
The Excel Options dialog box closes.
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 menu, click EXCEL OPTIONS
The Excel Options dialog box appears.
The Excel Options dialog box appears.
3. From the Categories list, select Popular
The dialog box refreshes to show the Popular options.
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.
The Custom Lists dialog box appears.
5. Click IMPORT
Your selection appears in the List entries text box.
Your selection appears in the List entries text box.
6. Click OK
The Custom Lists dialog box closes.
The Custom Lists dialog box closes.
7. Click OK
The Excel Options dialog box closes.
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.
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 crosshairs
NOTE: For this option to work, you must ensure that the pointer changes into a crosshairs before filling.
NOTE: For this option to work, you must ensure that the pointer changes into a crosshairs before filling.
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.
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.
The cells will be filled with the information you provided.
0 comments:
Post a Comment