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. |
|
|
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 . |
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 . |
||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||
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.
|
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 |
Windows XP
Outlook 2003
Internet Explorer
Computer Concepts
Word 2003
Chapter One. Creating Documents with Microsoft Word 2003
Chapter Two. Formatting and Organizing Text
Chapter Three. Using Graphics and Tables
Chapter Four. Using Special Document Formats, Columns, and Mail Merge
Excel 2003
Chapter One. Creating a Worksheet and Charting Data
Chapter Two. Designing Effective Worksheets
Chapter Three. Using Functions and Data Tables
Access 2003
Chapter One. Getting Started with Access Databases and Tables
Chapter Two. Sort, Filter, and Query a Database
Chapter Three. Forms and Reports
Powerpoint 2003
Chapter One. Getting Started with PowerPoint 2003
Chapter Two. Creating a Presentation
Chapter Three. Formatting a Presentation
Integrated Projects
Chapter One. Using Access Data with Other Office Applications
Chapter Two. Using Tables in Word and Excel
Chapter Three. Using Excel as a Data Source in a Mail Merge
Chapter Four. Linking Data in Office Documents
Chapter Five. Creating Presentation Content from Office Documents