Objective 1. Use AutoFill to Fill a Pattern of Column and Row Titles

Excel's AutoFill feature can generate a series of values into adjacent cells, based on the value of other cells. A series is a group of things that come one after another in succession. For example, January, February, March, April, and so on, is a series. The days of the week form a series. Quarter 1, Quarter 2, Quarter 3, and Quarter 4 form a series. The numbers 1, 2, 3, 4, 5 and 10, 15, 20, 25 are series. Excel's AutoFill feature completes a series so that you do not have to type every value.

Activity 2.1. Using AutoFill to Extend a Pattern of Days

Usually you begin a new worksheet by typing titles to identify the data that you plan to place in the rows and columns. When a series of values comprises your column or row titles, for example January, February, and March, you can enter the first value and then use the AutoFill feature to fill in the remaining titles.

1.	Start Excel and Close the Getting Started task pane. From the File menu, click Save As. In the Save As dialog box, click the Save in arrow, navigate to the location where you are storing your projects, click the Create New Folder button , and then create a folder named Excel Chapter 2 Press to make your new folder the active folder. In the File name box, type 2A_Staff_Schedule_Firstname_Lastname and then click Save or press .
2.	In cell A1, type Concession Stand Staff Schedule as the title of the worksheet, and then press . Select the range A1:P1, and then click the Merge and Center button .   Alert!: Selecting Beyond the Edge of the Screen May Cause Rapid Scrolling Selecting a range of cells that extends beyond the edge of the screen may cause rapid scrolling. This rapid scrolling might cause the selection to extend beyond your intended location. Continue holding down the mouse button and moving up or down or left or right until you have the selection you want. Or, type the range into the Name Box instead of selecting with the mouse.
3.	In cell A2, type Shift and then press . Select the range A2:B2, and then click the Merge and Center button . In cell C2, type This Week and then press . Select the range C2:I2, and then click the Merge and Center button .
4.	Scroll to view columns J through P on your screen. In cell J2, type Next Week and then press . Merge and Center the text across the range J2:P2. The staff schedule will span two weeks and it will be updated each week.
5.	Press to move to cell A1. In cell A3, type Starting and then press . In cell B3, type Ending and then press . In cell C3, type Monday and then press . Monday forms the title for this column, which will contain the staff schedule for Monday.
6.	Click cell C3 to make it the active cell, and then notice the small black square in the lower right corner of the cell as shown in Figure 2.2.   Figure 2.2. You can drag the fill handlethe small black square in the lower right corner of a selected cellto adjacent cells to fill the cells with values based on the first cell in the series.
7.	Point to the fill handle until your mouse pointer changes from the pointer to the pointer, hold down the left mouse button, drag to the right through cell P3, and as you do so, notice that a ScreenTip displays for each day of the week, as shown in Figure 2.3. Release the left mouse button. If you are not satisfied with your result, click Undo and begin again.   Figure 2.3. (This item is displayed on page 698 in the print version) After you release the mouse button, the days Monday through Sunday are filled in, and then repeat in the second week. A button displays at the end of the series.   Another Way: To Fill a Range of Cells You can type the range into the Name Box, and then from the menu click Edit, point to Fill, and then click Series. In the Series dialog box, under Type, click AutoFill and then click OK.
8.	Point to the Auto Fill Options button to display its ScreenTip. Then, click the Auto Fill Options button arrow to display a menu. Compare your screen with Figure 2.4.   Figure 2.4. (This item is displayed on page 699 in the print version) The Auto Fill Options button is a type of smart tag. When Excel recognizes certain types of data, Excel labels the data with a smart tag. Some smart tags, for example, the Auto Fill Options button, provide a menu of options related to the current task. Because the options are related to the current task, the tag or button is referred to as being context-sensitive. The menu lists commands that offer ways to fill the selected cells. On the displayed menu, Fill Series is selected, indicating the action that was taken. From the displayed menu, you could also select other actions. For example, if you click Fill Weekdays, the days Monday through Friday will fill the range without Saturday or Sunday. As you progress in your study of Excel, you will encounter additional smart tags that provide context-sensitive commands.
9.	Click any empty cell to close the Auto Fill Options menu without changing the selection. On the Standard toolbar, click the Save button .

More Knowledge: Smart Tags

Smart tags were first introduced in Microsoft Office XP. In Microsoft Office 2003, smart tags have been made even smarter. Using various types of software, organizations can develop their own custom smart tags, and smart tags can automatically search for information on the Web.

 

Activity 2.2. Using AutoFill to Extend a Pattern of Times

A series of dates or times can form row or column titles. If you place a time in a cell using a colonsuch as 6:00or with AM or PM, Excel will identify it as a time of day and apply a time format. You can fill a date or time into adjacent cells and the formatting is also filled unless you select a different option from the smart tag. The evening shifts start at 6 PM. The final shift begins at 11 PM and ends at midnight. Instead of typing each time period, you can type one time period and then have AutoFill complete the series of row titles.

1.

Hold down and press to move to cell A1recall that this is a convenient way to move to the upper left portion of the worksheet. Click cell A4, type 6 PM and then press .

Excel recognizes this as a time of day and applies the default time format6:00 PM.
 

---

2.

Click cell A4 to make it the active cell, point to the fill handle until your mouse pointer changes to the pointer, drag downward through cell A9, and as you do so, notice that a ScreenTip displays each hour. Release the left mouse button.
 

3.

Click the Auto Fill Options button arrow to display a menu, and then compare your screen with Figure 2.5.
 

Figure 2.5.

The menu offers commands regarding ways to fill the selected cells. On the displayed menu, Fill Series is selected, indicating the action that was taken. Notice that the smart tag is context sensitive and displays different commands for this time series, as compared to the options on the smart tag for the series of days. A smart tag remains on the screen until you take some other action or until you press .
 

4.

Click cell B4, type 7 PM and then press . Click cell B4, point to the fill handle and drag downward through cell B9.
 

5.

Save the changes you have made to your workbook, and then take a moment to study the various ways you can fill a series with AutoFill as described in the table in Figure 2.6.
 

Figure 2.6. AutoFill Series

(This item is displayed on page 701 in the print version)

Start with:	AutoFill generates this series:
Jan	Feb, Mar, Apr. . .
January	February, March, April. . .
Mon	Tue, Wed, Thu. . .
Monday	Tuesday, Wednesday, Thursday. . .
Qtr 1	Qtr 2, Qtr 3, Qtr 4. . .
Quarter 1	Quarter 2, Quarter 3, Quarter 4. . .
Oct-99	Nov-99, Dec-99, Jan-00. . .
15-Jan	16-Jan, 17-Jan, 18-Jan. . .
1st Period	2nd Period, 3rd Period, 4th Period. . .
Product 1	Product 2, Product 3, Product 4. . .
Text 1	Text 2, Text 3, Text 4. . .
10:00 AM	11:00 AM, 12:00 PM, 1:00 PM. . .

 

---

More Knowledge: Specifying Fill Intervals

The AutoFill feature assumes an interval of time such as one hour for times or one day for dates. If you want a different interval, you can specify the interval by providing an example. To provide an example, type the first two times or dates in the first two cells of the series. Then select both cells and drag the fill handle. Alternatively, begin to drag the fill handle using the right mouse button, release the mouse button, and a shortcut menu will display. From the menu, click Series. Then, in the displayed dialog box, increase the Step value, which is the interval between values in the series. Use whole numbers for intervals of days and decimal fractions for intervals of hours. For example an interval of 6 hours would be a step value of .25a quarter of a day.

[Page 701 (continued)]

Objective 2 Copy Text Using the Fill Handle