Pivot tables are interactive tables that allow the user to group and summarize large amounts of data in a concise format for easier analysis and reporting. They can sort, count and sum data and are available in many programs that have spreadsheets. An advantage of this Excel feature is that it allows you to reorder, hide and show different categories within the pivot table to get alternative views of the data. Read on to find out how to create your own pivot table in Excel.
Steps
Part 1 of 3: Create a Pivot Table
Step 1. Load the spreadsheet you want to create the pivot table from, which allows you to graph spreadsheet data
You can perform calculations without having to enter any functions or copy cells. To create a pivot table you will need a spreadsheet with several entries.
You can also create a pivot table in Excel using an external data source, such as Access. You will be able to insert it in a new Excel sheet
Step 2. Make sure your data meets the requirements for a pivot table, which isn't always a solution to the problem you encounter
To be able to create it, the spreadsheet must have the following basic characteristics:
- It must include at least one Column with duplicate values, i.e. repeated data. In the example we will discuss later, the "Product Type" column has two values "Table" or "Chair".
- It should contain numeric values to compare and add in the table. In the next example, the "Sales" column actually contains numeric values.
Step 3. Start auto-dial PivotTable
Click on the "Insert" tab at the top of the Excel window. Click on the "Pivot Table" button on the left.
With version of Excel 2003 or earlier, click on the menu Data and select Pivot Table and Chart Report….
Step 4. Select the data you want to use
By default, Excel will select all data on the active worksheet. You can click and drag to choose a specific part of the spreadsheet or you can type the range of cells manually.
If you are using an external data source, you need to click on the "Use an external data source" option and press Choose Connection…. Browse your computer to find the database
Step 5. Select the location of the pivot table
After selecting your range, choose where to put it from the window itself. By default, Excel will insert the table into a new worksheet and allow you to move back and forth by clicking on the tabs at the bottom of the window. You can also choose to place the PivotTable on the same datasheet, allowing you to choose the cell you want them to be inserted into.
When you are satisfied with your choices, click OK. Your pivot table will be inserted and the interface will change
Part 2 of 3: Configure the Pivot Table
Step 1. Add a row field
When you create a PivotTable, you essentially sort the data by rows and columns. The structure of the table is determined by what you add and where. To enter information, drag a field from the Field List to the right of the Row Fields section of the pivot table.
- For example, your company sells your products: tables and chairs. You have a spreadsheet with numbers (Sales) referring to each product (Product Type) sold in your five points of sale (Store). Let's say you want to see how much of each product is sold in each store.
- Drag the Store field from the field list to the Row Fields section of the pivot table. The list of stores will appear, each in its own row.
Step 2. Add a column field
Like rows, columns also allow you to sort and view data. In the example above, the Shop field has been added to the Row Fields section. To see how much of each product type has been sold, drag the Product Type field to the Column Fields section.
Step 3. Add a value field
Now that you've established the overall organization, you can add the data to display in the table. Click and drag the Sales field into the Value Fields section of the pivot table. In your table you will see the sales information for both products in each of your stores displayed, with a Total Column on the right.
For all of the above steps, you can drag fields to the corresponding boxes below the Fields list on the right side of the window instead of dragging them to the table
Step 4. Add multiple fields to a section
Pivot tables allow you to add multiple fields for each section, allowing you more control over how the data is displayed. Using the previous example, let's assume you make different types of tables and chairs. Your spreadsheet can store whether the item is a table or chair (Product Type), but also the exact model that was sold (Model).
Drag the Model field to the Column Fields section. The columns will now display the sales breakdown for each model and type. You can change the order in which these labels appear by clicking the arrow 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
Step 5. Change the way the data is displayed by clicking the arrow icon next to a value in the Values box
Select "Value Field Settings" to change the way values are calculated. For example, you might see the value as a percentage instead of a total or an average instead of a sum.
You can add the same field to the Value box multiple times to make everything easier. In the example above, the total Sales for each store is displayed. By adding the Sales field again, you can change the value settings to show sales ranked second as a percentage of total sales
Step 6. Learn some ways that values can be manipulated
When changing the ways in which values are calculated, you have several options to choose from depending on your needs.
- Sum - this is the default for Value fields. Excel will sum all the values in the selected field.
- Count - will count the number of cells that contain the data in the selected field.
- Average - will average all values of the selected field.
Step 7. Add a filter
The "Report Filter" area contains the fields that allow you to move between the summaries of the values shown in the pivot table by filtering the data set. They act as filters for the relationship. For example, by setting the Shop field as the filter instead of a Row Label, you will be able to select each shop to see individual sales totals or multiple shops at the same time.
Part 3 of 3: Using the Pivot Table
Step 1. Sort and filter your results
One of the key features of the PivotTable 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 show only specific items.
Step 2. Update your spreadsheet
The PivotTable updates automatically when you change the base spreadsheet. This can be very useful for worksheets that track and detect changes.
Step 3. Edit the PivotTable, where it is extremely easy to change the position and order of the fields
Try dragging the different fields to different locations to find a pivot table that meets your exact needs.
This is where the pivot table gets its name from. Moving data to different places is called "pivoting" to change the direction in which the data is displayed
Step 4. Create a Pivot Chart
You can use a pivot chart to show dynamic views. The PivotChart can be created directly from the PivotTable once completed, speeding up the creation of the chart as much as possible.
Advice
- If you use the Import Data command from the Data menu, you will have more import options such as connections with Database Office, Excel file, Access database, text file, ODBC DSN, websites, OLAP and XML / XSL. You can then use the data as you would an Excel list.
- If you are using an Autofilter (under "Data" -> "Filter"), turn it off when creating the pivot table. You will be able to reactivate it after creating it.
