Create Pivot Tables in Excel

Pivot tables are interactive tables that allow the user to group and summarize large amounts of data in a concise, tabular format for easier reporting and analysis. They can sort, count, and total the data, and are available in a variety of spreadsheet programs. Excel allows you to easily create pivot tables by dragging and dropping your relevant information into the appropriate boxes. You can then filter and sort your data to find patterns and trends.

Steps

Building the Pivot Table

  1. Load the spreadsheet you want to create the Pivot Table from. A Pivot Table allows you to create visual reports of the data from a spreadsheet. You can perform calculations without having to input any formulas or copy any cells. You will need a spreadsheet with several entries in order to create a Pivot Table.
    • You can also create a Pivot Table in Excel using an outside data source, such as Access. You can insert the Pivot Table in a new Excel spreadsheet.
  2. Ensure that your data meets the needs of a pivot table. A pivot table is not always the answer you are looking for. In order to take advantage of the pivot table features, your spreadsheet should meet some basic criteria:[1]
    • Your spreadsheet should include at least one column with duplicate values. This basically just means that at least one column should have repeating data. In the example discussed in the next section, the "Product Type" column has two entries: "Table" or "Chair".
    • It should include numerical information. This is what will be compared and totaled in the table. In the example in the next section, the "Sales" column has numerical data.
  3. Start the Pivot Table wizard. Click the "Insert" tab at the top of the Excel window. Click the "PivotTable" button on the left side of the Insert ribbon.
    • If you are using Excel 2003 or earlier, click the Data menu and select PivotTable and PivotChart Report...
  4. Select the data you want to use. By default, Excel will select all of the data on your active spreadsheet. You can click and drag to choose a specific part of the spreadsheet, or you can type the cell range in manually.
    • If you are using an external source for your data, click the "Use an external data source" option and click Choose Connection.... Browse for the database connection saved on your computer.
  5. Select the location for your Pivot Table. After selecting your range, choose your location option from the same window. By default, Excel will place the table on a new worksheet, allowing you to switch back and forth by clicking the tabs at the bottom of the window. You can also choose to place the Pivot Table on the same sheet as the data, which allows you to pick the cell you want it to be placed.[2]
    • When you are satisfied with your choices, click OK. Your Pivot Table will be placed and the interface will change.

Configuring the Pivot Table

  1. Add a row field. When creating a Pivot Table, you are essentially sorting your data by rows and columns. What you add where determines the structure of the table. Drag a field from the Field List on the right onto the Row Fields section of the Pivot Table to insert the information.
    • For example, your company sells two products: tables and chairs. You have a spreadsheet with the number (Sales) of each product (Product Type) sold in your five stores (Store). You want to see how much of each product is sold in each store.
    • Drag the Store field from the field list into the Row Fields section of the Pivot Table. Your list of stores will appear, each as its own row.
  2. Add a column field. Like the rows, the columns allow you to sort and display your data. In the above example, the Store field was added to the Row Fields section. To see how much of each type of product was sold, drag the Product Type field to the Column Fields section.
  3. Add a value field. Now that you have the organization laid out, you can add the data to be displayed in the table. Click and drag the Sales field into Value Fields section of the Pivot Table. You will see your table display the sales information for both of your products in each of your stores, with a Total column on the right.[3]
    • For all of the above steps, you can drag the fields into the corresponding boxes below the Fields list on the right side of the window instead of dragging them onto the table.
  4. Add multiple fields to a section. Pivot tables allow you to add multiple fields to each section, allowing for more minute control over how the data is displayed. Using the above example, say you make several types of tables and several types of chairs. Your spreadsheet is records whether the item is a table or chair (Product Type), but also the exact model of the table or chair sold (Model).
    • Drag the Model field onto the Column Fields section. The columns will now display the breakdown of sales per model and overall type. You can change the order that these labels are displayed by clicking the arrow button next to the field in the boxes in the lower-right corner of the window. Select "Move Up" or "Move Down" to change the order.
  5. Change the way data is displayed. You can change the way values are displayed by clicking the arrow icon next to a value in the Values box. Select "Value Field Settings" to change the way the values are calculated. For example, you could display the value in terms of a percentage instead of a total, or average the values instead of summing them.
    • You can add the same field to the Value box multiple times to take advantage of this. In the above example, the sales total for each store is displayed. By adding the Sales field again, you can change the value settings to show the second Sales as percentage of total sales.
  6. Learn some of the ways that values can be manipulated. When changing the ways values are calculated, you have several options to choose from depending on your needs.
    • Sum - This is the default for value fields. Excel will total all of the values in the selected field.
    • Count - This will count the number of cells that contain data in the selected field.
    • Average - This will take the average of all of the values in the selected field.
  7. Add a filter. The "Report filter" area contains the fields that enable you to page through the data summaries shown in the pivot table by filtering out sets of data. They act as the filters for the report. For example, setting your Store field as the filter instead of a Row Label will allow you to select each store to see individual sales totals, or see multiple stores at the same time.

Using the Pivot Table

  1. Sort and filter your results. One of the key features of the Pivot Table is the ability to sort results and see dynamic reports. Each label can be sorted and filtered by clicking the down arrow button next to the label header. You can then sort the list or filter it to only show specific entries.[4]
  2. Update your spreadsheet. Your pivot table will automatically update as you modify the base spreadsheet. This can be great for monitoring your spreadsheets and tracking changes. .
  3. Change your pivot table around. Pivot tables make it extremely easy to change the location and order of fields. Try dragging different fields to different locations to come up with a pivot table that meets your exact needs.
    • This is where the pivot table gets its name. Moving the data to different locations is known as "pivoting" as you are changing the direction that the data is displayed.
  4. Create a Pivot Chart. You can use a Pivot Chart to show dynamic visual reports. Your Pivot Chart can be Create-a-Chart-from-a-Pivot-Table, making the chart creation process a snap.



Tips

  • If you use the Import Data command from the Data menu, you have more options on how to import data ranging from Office Database connections, Excel files, Access databases, Text files, ODBC DSN's, webpages, OLAP and XML/XSL. You can then use your data as you would an Excel list.
  • If you are using an AutoFilter (Under "Data", "Filter"), disable this when creating the pivot table. It is okay to re-enable it after you have created the pivot table.

Warnings

  • If you are using data in an existing spreadsheet, make sure that the range that you select has a unique column name at the top of each column of data.

Related Articles

Sources and Citations

  1. ↑ http://oreilly.com/pub/a/windows/archive/whatisapivottable.html?page=3
  2. ↑ http://fiveminutelessons.com/learn-microsoft-excel/how-create-pivot-table-excel
  3. ↑ http://www.excel-easy.com/data-analysis/pivot-tables.html
  4. ↑ http://office.microsoft.com/en-us/excel-help/create-a-pivottable-to-analyze-worksheet-data-HA102840046.aspx
Retrieved from "https://kipkis.com/index.php?title=Create_Pivot_Tables_in_Excel&oldid=352542"