Creating Dynamic Worksheets by Using PivotTables in Microsoft Excel 2010

Creating PivotTables from External Data

Although most of the time you will create PivotTables from data stored in Excel worksheets, you can also bring data from outside sources into Excel. For example, you might need to work with data created in another spreadsheet program with a file format that Excel can’t read directly. Fortunately, you can export the data from the original program into a text file, which Excel 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, browse to the directory that contains the text file you want to import. Double-clicking the file launches the Text Import wizard.

On the first page of the Text Import wizard, you can 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 assigns after examining the data source you selected), advances you to the next wizard page.

On this page, you can choose the delimiter for the file (in this case, Excel 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 page.

On this page, you can change the data type and formatting of the columns in your data. 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, you can work with it normally.

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

Set Up

You need the Creating_start text file located in your Chapter09 practice file folder to complete this exercise.

1. Create a new Excel workbook. On the Data tab, click the Get External Data button, and then click From Text.

   

   The Import Text File dialog box opens.

2. Navigate to the Chapter09 practice file folder, and then double-click Creating_start.txt.

   The Text Import wizard starts.

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

   The next Text Import Wizard page opens.

4. In the Delimiters area, 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.

   Clicking Finish skips page 3 of the wizard, which has commands you can use to assign specific data types to each column. Excel assigns data types for you, so you don’t need to do so. After you click Finish, the Import Data dialog box opens.

   

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

   Excel imports the data into your workbook.

7. On the Home tab, in the Styles group, click Format as Table, and then click the first table style.

   

   The Format As Table dialog box opens.

8. Verify that the My table has headers check box is selected and that the range =$A$1:$H$6571 appears in the Where is the data for your table? box, and then click OK.

   A confirmation dialog box opens.

9. Click Yes to confirm you want to create the Excel table and break its link to the external data source.

   Excel creates an Excel table from your imported data.

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

    

    The Create PivotTable dialog box opens.

11. Verify that the Select a table or range option is selected, that Table1 appears in the Table/Range field, and that the New Worksheet option is selected.

12. Click OK.

    Excel creates the PivotTable on a new worksheet.

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

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

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

    

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

    

    The Save As dialog box opens.

17. Browse to the Chapter09 folder.

18. In the File name field, type ImportedData.

19. Click Save.

    Excel saves your file.

    Clean Up

    

    Close the Imported Data workbook. If you’re not continuing directly to the next chapter, exit Excel.