Buzz
A Pivot Table is an interactive tool that enables users to group and summarize large datasets into a concise table format, making it easier to report and analyze. Pivot Tables allow for sorting, counting, summing data, and are built into many spreadsheet programs. Excel allows you to create a Pivot Table quickly by dragging and dropping related information into corresponding boxes. You can filter and sort data to uncover patterns and trends.
Steps
Building a Pivot Table
Upload the spreadsheet you want to create a Pivot Table from. A Pivot Table allows you to generate visual reports of data from your spreadsheet. You can perform calculations without manually entering formulas or copying cells. You need a spreadsheet containing some data points to create a Pivot Table.
- You can create a Pivot Table in Excel from external data sources, such as Access, or insert it into a new Excel spreadsheet.
Ensure your data meets the requirements for a pivot table. A pivot table is not always necessary. To fully leverage the features of a pivot table, your spreadsheet needs to meet a few basic criteria:
- The spreadsheet must have at least one column with duplicate values, meaning a column that repeats data. For example, in the next section, the 'Product Type' column has two entries: 'Table' or 'Chair'.
- The table must contain numeric data. These are the values that will be compared and calculated in the table. For instance, in the next section, the 'Sales' column contains numerical data.
Start the Pivot Table wizard. Click the 'Insert' tab at the top of the Excel window. Select 'PivotTable' on the left side of the Insert tab.
- If you are using Excel 2003 or earlier, click on the Data menu and select PivotTable and PivotChart Report...
Select the data you want to use. By default, Excel will select all the data from the spreadsheet. You can click and drag to select specific parts of the sheet or manually input a cell range.
- If you're using external data, click 'Use an external data source' and Choose Connection... to access a saved database connection on your computer.
Choose the location for the Pivot Table. After selecting the range, choose the location within the same window. By default, Excel will create the table in a new worksheet, allowing you to switch between tabs at the bottom of the window. You can also place the Pivot Table within the same sheet as the data, selecting a specific cell.
- Once satisfied with the selection, click OK. The Pivot Table will be created and the interface will update accordingly.
Configuring the Pivot Table
Add row fields. Creating a basic Pivot Table involves organizing data by rows and columns. You need to add or define the structure of your table. Drag a field from the Field List on the right into the Row Fields section of the Pivot Table to populate the data.
- For example, your company sells two products: tables and chairs. You have a spreadsheet showing the quantity (Sales) of each product (Product Type) sold in two stores (Store). You want to see how many of each product were sold in each store.
- Drag the Store field into the Row Fields of the Pivot Table. A list of stores will appear as rows.
Add column fields. Similar to rows, columns allow you to sort and display data. In the example above, the Store field was added to the Row Fields. To see how many units of each product were sold, drag the Product Type field into the Column Fields section.
Add value fields. Now that your structure is organized, you can add the data to display in the table. Click and drag the Sales field into the Value field of the Pivot Table. You will see the table display sales information for both products and stores, with a total column on the right.
- Using the steps above, you can also drag the fields into the corresponding box below the Field List on the right side of the Excel window instead of dragging them directly into the table.
Add multiple fields to each section. Pivot Tables allow you to add multiple fields to each section, giving you more control over how the data is displayed. In the example above, you can create different types of tables and chairs. The spreadsheet will record the product as either a chair or a table (Product Type), but the exact model sold (Model) will be displayed.
- Drag the Model field into the Column Fields section. The column will display sales analysis for each model and the overall total. You can reorder the labels by clicking the arrow button in the box at the bottom-right corner of the window. Select 'Move Up' or 'Move Down' to change the order.
Change how data is displayed. You can change how values are displayed by clicking the arrow icon in the Value box. Select 'Value Field Settings' to modify the calculation method. For example, you can display data as a percentage rather than a total, or show the average instead of summing the values.
- You can add the same field multiple times to the Value box to take full advantage of this. In the example above, the total sales for each store are displayed. When you add another Sales field, you can adjust the settings to display the second Sales field as a percentage of the total sales.
Learn how to manipulate values. When changing how values are calculated, you have several options depending on your needs.
- Sum - This is the default for value fields. Excel will calculate the sum of the selected fields.
- Count - This counts the number of cells that contain data for the selected field.
- Average - This calculates the average value of the selected field.
Add filters. The 'Report filter' area contains fields that allow you to filter the data displayed in your pivot table. These work similarly to report filters. For example, setting the Store field as a filter rather than a row label lets you select individual stores to view their sales or check all stores at once.
Using Pivot Table
Sort and filter results. One of the main functions of a Pivot Table is the ability to sort results and display a clear report. Each label can be sorted or filtered by clicking the downward arrow next to the header. You can sort the list or filter it to show specific items.
Update the spreadsheet. Pivot tables automatically update when you change data in the spreadsheet. This is ideal for tracking changes and monitoring updates in real-time.
Change the pivot table. Pivot tables allow you to easily adjust the position and order of fields. Simply drag fields to different spots on the pivot table until you're satisfied.
- This is the time to understand the name of the pivot table. Moving data to different positions is called 'pivoting' because you are altering the direction in which the data is displayed.
Create a Pivot Chart. You can use a Pivot Chart to present your report in a lively, visual way. You can create a Pivot Chart directly from the Pivot Table, and the process is quick and easy.
Advice
- If you use the Import Data command from the Data menu, you have more options to import data from sources like Office Database Connections, Excel files, Access databases, Text files, ODBC DSNs, websites, OLAP, and XML/XSL. Afterward, you can use the data as usual.
- If you're using AutoFilter (go to "Data" and "Filter"), disable this feature while creating the pivot table. You can enable it again after the table is created.
Warning
- If you are using existing data from a spreadsheet, make sure that the columns within your selection range have distinct names.
