CP Lab – Experiment 9


Aim:  Creating and using PivotTables and PivotCharts, importing data from external sources to spreadsheet.

Objectives: 1) Understand and demonstrate working of PivotTables and use of PivotCharts

2) Importing and exporting data between text files and spreadsheet

Hardware requirements: Any CPU with Pentium Processor or similar, 256 MB RAM or more, 1 GB Hard Disk or more.

Software requirements:  Windows 64 bit Operating System, MS office 2010 or later

Theory: With Excel worksheets you can gather and present important data, but the standard worksheet can’t be changed from its original configuration easily. Such a neutral presentation of your data is versatile, but it has limitations. First, although you can use sorting and filtering to restrict the rows or columns shown, it’s difficult to change the worksheet’s organization. For example, in this worksheet, you can’t easily reorganize the contents of your worksheet so that the months are assigned to the rows and the distribution centers are assigned to the columns. The Excel tool to reorganize and redisplay your data dynamically is the PivotTable. You can create a PivotTable, or dynamic worksheet, that enables you to reorganize and filter your data on the fly.

  1. To create a PivotTable, you must have your data collected in a list.
  2. After you create an Excel table, you can click any cell in the table, display the Insert tab and then, in the Tables group, click PivotTable to open the Create PivotTable dialog box.
  3. In this dialog box, you verify the data source for your PivotTable and whether you want to create a PivotTable on a new worksheet or an existing worksheet.
  4. After you click OK, Excel displays a new or existing worksheet and displays the PivotTable Field List task pane.
  5. To assign a field, or column of data, to an area of the PivotTable, drag the field header from the Choose Fields To Add To Report area at the top of the PivotTable Field List task pane to the Drag Fields Between Areas Below area at the bottom of the task pane.
  6. It’s important to note that the order in which you enter the fields in the Row Labels and Column Labels areas affects how Excel organizes the data in your PivotTable.
  7. To pivot a PivotTable, you drag a field header to a new position in the PivotTable Field List task pane.
  8. As you drag a field within the task pane, Excel displays a blue line in the interior of the target area so you know where the field will appear when you release the left mouse button.
  9. If your data set is large or if you based your PivotTable on a data collection on another computer, it might take some time for Excel to reorganize the PivotTable after a pivot. You can have Excel delay redrawing the PivotTable by selecting the Defer Layout Update check box in the lower-left corner of the PivotTable Field List task pane.
  10. When you’re ready for Excel to display the reorganized PivotTable, click Update.

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.

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. 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.
  8. After the data is in Excel, you can work with it normally.

Creating Dynamic Charts by Using PivotCharts

  1. Creating a PivotChart is fairly straightforward. Just click any cell in a list or Excel table you would use to create a PivotTable, and then click the Insert tab.
  2. In the Tables group, in the PivotTable list, click PivotChart to create the chart.
  3. To create a PivotChart from an existing PivotTable, click a cell in the PivotTable, display the Insert tab and then, in the Charts group, click the type of chart you want to create.
  4. After you complete either of these procedures, Excel displays a new PivotChart in your workbook. Any changes to the PivotTable on which the PivotChart is based are reflected in the PivotChart.
  5. A PivotChart has tools with which you can filter the data in the PivotChart and PivotTable.
  6. If you ever want to change the chart type of an existing chart, you can do so by selecting the chart and then, on the Design tab, in the Type group, clicking Change Chart Type to display the Change Chart Type dialog box.
  7. When you select the type you want and click OK, Excel re-creates your chart.

Conclusion: In this experiment, we have demonstrated PivotTables, importing data from external source and PivotCharts.

R.No Name of Student Date of Performance Date of Evaluation Grade Sign of student Sign of Faculty

Reference Book: Microsoft Excel 2010 – Step by Step

Leave a Reply

Your email address will not be published. Required fields are marked *