Chapter 4: Excel Tutorial (Creating and Using Pivot Tables)

After completing the tutorial, you will be able to create your own pivot tables in Excel. Pivot tables are used in Excel to summarize and evaluate data. Pivot tables allow the user to select a data set that the individual would like to alter. This feature allows the user to look at the data in different ways without altering the actual data set. In this tutorial, you will use the information provided below that is related to the self-study problem presented on page 103.

Creating and Using Pivot Tables—the following steps can be used to create a pivot table that in turn, can be used to evaluate various data sets with ease. By exploring the pivot tables within Excel, you can use this feature to perform many different summations and calculations. This tutorial will only show you a simple version of a pivot table.

Usually information is given to you to analyze from a database. This data has not been sorted or manipulated in any sense. This kind of information is referred to as “raw data”. This is because the data, seen in the tutorial spreadsheet, is very raw and is not easy to see key figures such as totals.

  1. Open the chapter 4 spreadsheet.
  2. Go to the worksheet labeled “Data”
  3. Go to the Insert tab on the ribbon, and select the PivotTable button.
  1. Once you click on the PivotTable button a new screen will pop-up. Select your data on the “Data” sheet, and check that you would like the pivot table to be created in a new sheet as seen below:

  1. Now you can click OK.
  2. As you can see, your pivot table is shown within a new sheet, and now a new box has opened on the left side of your screen. This box allows you to modify the data within Excel. The lists shown in the Fields box are the headers of your data columns in the “Data” sheet. You should now select the field names, and drag them into the boxes below. For a detailed example see the screen captures below:
  1. When you look at the pivot table you can see that it has summed up left-to-right, and top-to-bottom. This data is the same as the information given in part a and b of the self-study problem.

  1. The drop-down boxes within the pivot table allow you to modify what data will be shown within the pivot table. Select the drop-down box for Purchased Products and select only “Material X.” As you can see below, this changed your pivot table to only show data relevant to “Material X.”
  1. To change the format of the data within the pivot table is the same as changing the data of a normal cell. You can select from the formatting shortcut box, or you can right click on each, or all by selecting all the cells, and selecting Format cells… Format your numbers into this format 2,222.
  2. Your final result should look like the pivot table below:

Try to create your own data, and use a pivot table to modify the appearance of the data. Pivot tables are very helpful in the business world. This feature allows you to easily filter, change, and sum data in a format that is easy for a manager to read and understand.