Creating Dynamic Lists by Using PivotTables in Microsoft Office Excel 2007

Creating PivotTables from External Data

Although most of the time you will create PivotTables from data stored in Excel 2007 worksheets, you can also bring data from outside sources into Excel 2007. For example, you might need to work with data created in another spreadsheet program with a file format that Excel 2007 can’t read directly. Fortunately, you can transfer worksheets from one program to another by exporting the data from the original program into a text file, which Excel 2007 then translates into a worksheet.

Spreadsheet programs store data in cells, so the goal of representing spreadsheet data in a text file is to indicate where the contents of one cell end and those of the next cell begin. The character that marks the end of a cell is a delimiter, in that it marks the end (or “limit”) of a cell. The most common cell delimiter is the comma, so the delimited sequence 15, 18, 24, 28 represents data in four cells. The problem with using commas to delimit financial data is that larger values—such as 52,802—can be written by using commas as thousands markers. To avoid confusion when importing a text file, the most commonly used delimiter for financial data is the Tab character.

To import data from a text file, on the Data tab, in the Get External Data group, click From Text to display the Import Text File dialog box.

From within the Import Text File dialog box, you browse to the directory that contains the text file you want to import. Double-clicking the file launches the Text Import Wizard.

The first page of the Text Import Wizard enables you to indicate whether the data file you are importing is Delimited or Fixed Width; Fixed Width means that each cell value will fall within a specific position in the file. Clicking Next to accept the default choice, Delimited (which Excel 2007 assigns after examining the data source you selected), advances you to the next wizard screen.

This screen enables you to choose the delimiter for the file (in this case, Excel 2007 detected tabs in the file and selected the Tab check box for you) and gives you a preview of what the text file will look like when imported. Clicking Next advances you to the final wizard screen.

This screen enables you to change the data type and formatting of the columns in your data list. Because you’ll assign number styles and PivotTable Quick Styles after you create the PivotTable, you can click Finish to import the data into your worksheet. After the data is in Excel 2007, you can work with it normally.

In this exercise, you’ll import a data list into Excel 2007 from a text file and then create a PivotTable based on that list.

1. On the Data tab, in the Get External Data group, click From Text.

   The Import Text File dialog box opens.

2. Navigate to the Documents\Microsoft Press\Excel2007SBS\PivotTables folder, and then double-click Creating.txt.

   The Text Import Wizard starts.

3. Verify that the Delimited option is selected, and then click Next.

   The next Text Import Wizard page appears.

4. In the Delimiters section, verify that the Tab check box is selected and also verify that the data displayed in the Data preview area reflects the structure you expect.

5. Click Finish.

   The Import Data dialog box opens.

   

6. Verify that the Existing worksheet option button is selected, and then click OK.

   Excel 2007 imports the data into your workbook.

7. On the Insert tab, in the Tables group, click PivotTable.

   The Create PivotTable dialog box opens.

8. Verify that the Select a table or range option button is selected, that the range Sheet1$A$1:$H$6571 appears in the Table/Range field, and that the New Worksheet option button is selected.

9. Click OK.

   Excel 2007 creates a new worksheet.

10. In the PivotTable Field List task pane, drag the Volume field header to the Values area.

11. Drag the Weekday field header to the Column Labels area.

12. Drag the Center field header to the Row Labels data area.

    

13. On the Quick Access Toolbar, click the Save button.

    The Save As dialog box opens.

14. Browse to the Documents\Microsoft Press\Excel2007SBS\PivotTables folder.

15. In the File name field, type Imported Data.

16. Click OK.

    Excel 2007 saves your file.