The New Way to Work with Data, Formulas, and Functions

In this section, you will learn how to effectively work with Excel 2010 and explore the new possibilities.

Creating the Month Data Series

To quickly create a list of all months of the year, perform the following steps:

1. Select File/New and click Create to open an empty Excel workbook.

2. Enter the following text in columns A2 through E2 (see Figure 1-1):

   • Month (A2)

   • Purchase (B2)

   • Sales (C2)

   • Profit margin (D2)

   

   Figure 1-1. Naming the table columns.

3. To enter the names of the months, you will use the fill feature. Start by entering January in cell A3.

4. Select cell A3 and point to the small square in the lower-right corner of the selected cell, the fill handle (see Figure 1-2). The mouse pointer changes to a black crosshair pointer.

   

   Figure 1-2. The fill handle for extending the data series.

5. Drag the crosshairs into cell A14. While you drag the crosshairs down, you will see the names of the months in the preview (see Figure 1-3).

   

   Figure 1-3. The months are added by using the fill handle.

6. Release the mouse button in cell A14 to display all months from January through December (see Figure 1-4).

   

   Figure 1-4. The names of the months are automatically filled in.

Creating an Individual Data Series

This feature can be used to fill rows or columns; it works for weekdays, months, and dates. To fill a number series, fill in the first two numbers in the sequence manually, select the two completed cells, and then drag the fill handle. The function can also be useful if you want to create your own data or AutoFill series. For example, if you don’t want to enter a list of sales managers over and over again, you can define your own AutoFill list:

1. Click the last sheet tab at the bottom of the workbook, which is the one with the new sheet symbol, to open a new sheet. Alternatively, you can press the Ctrl+F11 key combination.

2. Enter Sales manager in a free cell.

3. Enter the names of the sales managers in the cells below the Sales manager title (see Figure 1-5).

   

   Figure 1-5. Creating a custom data series.

4. Select the range containing the names.

5. Click the File tab and select Options (see Figure 1-6).

   

   Figure 1-6. Selecting Excel options.

6. In the Excel Options dialog box, select the Advanced category and click Edit Custom Lists in the General section (see Figure 1-7).

   

   Figure 1-7. Opening frequently used lists.

   Tip

   In Excel 2003, the Excel options are located in the Custom menu. In Excel 2007, you can access the options from the Microsoft Office button, and in Excel 2010 they are on the File tab. Click Excel Options or Options, respectively.

7. Make sure that the cell reference for the selected list is displayed in the Import List From Cells field, and click Import (see Figure 1-8).

   

   Figure 1-8. Specifying the cell range to be imported.

   The elements in the selected list are added to the List Entries field (see Figure 1-9).

   

   Figure 1-9. The list entries are added.

8. Click OK twice.

Now you have to enter only the name of one sales manager in a cell and drag the fill handle in the desired direction to generate the list of the sales managers on any spreadsheet. The series is created automatically.

This strategy gives you almost unlimited possibilities for creating a data series.