Pivot tables are interactive tables that allow users to group and summarize large amounts of data in a compact, tabular format that makes reporting and analysis easier. These tables can sort, calculate, and add data and are available in various spreadsheet programs. Excel allows you to easily create Pivot Tables by dragging and dropping relevant information into the appropriate boxes. You can then filter and sort your data to find patterns and trends.
Step
Part 1 of 3: Creating a Pivot Table
Step 1. Load the worksheet that will be the basis for creating the Pivot Table
Pivot tables allow you to create visual reports of data from a worksheet. You can perform calculations without having to enter any formulas or copy any cells. To create a Pivot Table all you need is a worksheet with several entries.
You can also create a Pivot Table in Excel by using an external data source, such as Access. You can insert a Pivot Table in a new Excel worksheet
Step 2. Make sure that your data matches the required Pivot Table
Pivot tables may not always be the answer you are looking for. In order to take advantage of the Pivot Table features, your worksheet must meet several basic criteria:
- Your worksheet must contain at least one column with duplicate values. Basically it just means that at least one column has repeating data. In the example discussed in the next section, the “Product Type” column has two entries: “Table” or “Chair”.
- The worksheet must contain numeric information. This information will be compared and summed in the table. In the example in the next section, the “Sales” column has numeric data.
Step 3. Run 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…”
Step 4. Select the data you will use
By default (default), Excel selects all data on the active worksheet. You can click and drag to select a specific part of the worksheet or type in a range of cells manually.
If you are using an external source for your data, click the “Use an external data source” option and then click Choose Connection…. Look for the connection database stored on your computer
Step 5. Choose a location for your Pivot Table
After specifying your range, select your location option from the same window. By default, Excel will place the table on a new worksheet that allows 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 so you can choose which cells to place.
When you are satisfied with your choices, click OK. Your Pivot Table will be placed and the interface will change
Part 2 of 3: Configuring Pivot Tables
Step 1. Add row fields
When you create a Pivot Table, you are basically sorting the data by rows and columns. What you add in a particular place will determine the structure of the table. Drag a field from the Field List on the right into the Row Fields section of the Pivot Table to insert its information.
- Let's say your company sells two products: tables and chairs. You have a worksheet with numbers (Sales) of each product (Product Type) sold in your five stores (Store). You want to see how much each product sold in each store.
- Drag the Store field from the Field List to the Row Fields section of the Pivot Table. A list of your stores will appear, each as its own row.
Step 2. Add column fields
Like rows, columns allow you to sort and display data. In the example above, the Store field is added to the Row Fields section. To see how much each product type sold, drag the Product Type field to the Column Fields section.
Step 3. Add a value field
Now that you're done laying out the order, you can add data to display in the table. Click and drag the Sales field to the Value Fields section of the Pivot Table. You'll see your table showing sales information for both products in each of your stores, with the Total column on the right.
For all of the above steps, you can drag the fields to the corresponding box under the Field List on the right side of the window instead of to the table
Step 4. Add multiple fields to a section
Pivot tables allow you to add multiple fields to each section at once so you can control in more detail how the data is displayed. Using the example above, let's say you create several types of tables and chairs. Your worksheet records whether an item is a table or chair (Product Type), as well as exactly what model of table or chair was sold (Model).
Drag the Model field to the Column Fields section. The columns will now show a breakdown of sales per model as well as overall type. You can change the order in which these labels are displayed by clicking the arrow buttons next to the respective fields in the boxes in the lower-right corner of the window. Select “Move Up” or “Move Down” to change the order
Step 5. Change the way data is displayed
You can change how values are displayed by clicking the arrow icon next to a value in the Values box. Select “Value Field Settings” to change how values are calculated. For example, you can display these values as a percentage instead of a total, or average them instead of adding them up.
You can take advantage of this opportunity by adding the same field to the Value box multiple times. In the example above, the total sales for each store are displayed. By adding the Sales field again, you can change the setting of that value to show the second Sales as a percentage of total sales
Step 6. Learn several ways to manipulate values
When changing the way values are calculated, you have several options, depending on your needs.
- Sum (sum) – This is the default for the value field. Excel will add up all the values in the selected field.
- Count – This will count the number of cells containing data in the selected field.
- Average – This will calculate the average of all values in the selected field.
Step 7. Add filters
The “Report Filter” area contains fields that allow you to view an overview of the data shown in the Pivot Table by filtering the data set. These fields act as filters for the report. For example, by setting the Store field-not Row Label-as a filter, you can select individual stores to view total sales per store, or view multiple stores at the same time.
Part 3 of 3: Using Pivot Tables
Step 1. Sort and filter the results
One of the key features of Pivot Tables is its ability to sort results and view 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 and filter it to show only specific entries.
Step 2. Update your worksheet
The Pivot Table will automatically update once you change the base worksheet. This capability is important for monitoring your worksheets and tracking changes.
Step 3. Fiddle with the Pivot Table
Pivot tables make it very easy for you to change the location and order of fields. Try dragging different fields to different locations to get a Pivot Table that fits your exact needs.
This is where the name Pivot Table comes from. Moving data to different locations is known as “pivoting.” It is when you change direction that the data is displayed
Step 4. Create a Pivot Chart
You can use Pivot Charts to show dynamic visual reports. Your Pivot Chart The finished Pivot, so the process of creating the chart is just a snap.
Tips
- If you use the Import Data command from the Data menu, you have more options in how to import a range of data from Office Database connections, Excel files, Access databases, Text files, ODBC DSN, web pages, OLAP and XML/XSL. You can then use your data as you would an Excel list.
- If you are using AutoFilter (under “Data”, “Filters”), disable this feature when creating the Pivot Table. You can re-enable it after creating the Pivot Table.
