Opening an Existing Document on Windows
First we will open the Excel workbook that we will be using in today's workshop.
Step1. Launch Excel.
Step2. To begin to open a workbook, in the left pane,
Click Open Other Workbooks
Step3. To browse for the file,
Setting the Location for Opening Your File
When the dialog box opens, it lists a default location from where the file will be opened. All of our exercise files are contained in the epclass folder, located on the desktop. We'll want to change our location to this folder.
We will start at the desktop, since our exercise file folder, epclass, is located there.
Step1. To move to the desktop,
Step2. To open the epclass folder,
Step3. To open the correct folder,
Double-Click the PivotTable_Analysis folder
Step4. To open the file,
Step5. To enable editing, if necessary,
If you are working with Excel 2016 for Mac, you will want to follow these steps for opening StudentRecords.xlsx.
Step1. Launch Excel.
Step2. To begin opening a file,
Step3. To begin navigating to the correct folder,
Step4. To move to the Desktop,
Step5. To move to the epclass folder,
Step6. To open the correct folder,
Step7. To open the file,
A pivot table is a dynamic table generated from a database that quickly summarizes, organizes, and compares large amounts of data in a worksheet. Excel provides the PivotTable feature to help analyze sophisticated data sets. Often we will build PivotTables from data which resides in an Excel worksheet. However, data in an external database file, such as Microsoft Access, can also be utilized. Thus allowing PivotTables to be created without importing the underlying data into Excel.
Here is an example of a simple PivotTable:
In the above example, we see the source data or sales figures summarized for various regions. The filter is optional, but in this example, the Month field is used as a filter, which would allow us to view this sales data for a certain month.
Rows and columns may be pivoted (rotated) to allow different views of the same data. For example, we could easily place the Region field in the rows area and the Product ID field in the columns area. The ability to pivot the dimensions of the table—for example, to transpose columns and rows—gives the PivotTable its name.
Here is an example of the same data as above, pivoted:
Organizing the Source Data
Before creating a PivotTable, the data needs to be organized in such a way that Excel can manage it correctly. Today we will be working with data stored in Excel spreadsheets.
Some considerations are:
- All related data should be in the same column to make the analysis easier. For example, keep all of the related dollar amounts in the same column, regions in the same column, dates in the same column, etc.
- A unique identifier, such as an ID number, is helpful for each row, especially when counting records.
- Do not include any blank rows or columns within the source data. Separate the source data from any other data on the worksheet with at least one blank row and one blank column between it and the other data. It is best to have only the source data in one worksheet.
- Separate source data into multiple columns, if necessary. For example, you may want the month separated from the year, or the address separated into street, city, and state. Then you will be able to analyze the data by using these individual fields.
Putting forth a little extra time up front will save time later in helping to get the results you want from PivotTables. The Excel data must be organized into rows and columns with each row containing information about one record, such as a student record or a sales order. Each column in the source data must contain a unique heading, which can be one word or multiple words.
Not all data can be used effectively to create PivotTables. PivotTables lend themselves well to calculating or summarizing one or more fields. Generally speaking, the data source will consist of two types of fields: Data (values to be summarized) and Categories (labels that describe the data).
Calculations can be performed on the data in a PivotTable. Therefore, when creating a PivotTable, it is not necessary to include calculated summaries from the source data. Doing so can cause problems because Excel will see the calculations (sums, averages, etc.) as data and include it in the PivotTable analysis. Remember to allow the PivotTable to do the work!
Suppose someone wanted to track grades for several students who take several classes. Here is an example of poorly structured data:
In the previous data, notice that the address field contains three parts of the Address (street, city, state) and the Class/Grade field contains two different types of data in one field. Also, with this setup, we would have to continue to add columns for each class the student took.
Here is an example of how a "good" data source might be structured:
The data remains the same as in the first example; however, each row captures grade data for a particular class. By separating the Address and Class/Grade fields into separate columns, the PivotTable will be more flexible and much more meaningful since Excel can summarize data for an individual student by city, state, class, and/or grade. You will see later how easy it is to manipulate data to get desired results.