Excel pivot tables: YOUR OBJECTIVE
If you need to better understand large amounts of data in Excel tables, the excel Pivot Tables function allows you to efficiently analyze the data from various perspectives, without requiring that any changes be made to the table itself. Let’s say, for example, you have an Excel table in which all your company’s incoming orders from the last several months are recorded, and now you’d like to analyze this data to find out more about the value of those orders and the sales regions in which the orders were made.
This learning module “Excel pivot tables” will give you some basic information about pivot tables and show you how to plan for using them.
Excel pivot tables: HOW DOES IT WORK?
What are excel pivot tables?
Excel Pivot tables provide you with an interactive means to efficiently analyze large amounts of data. You can use them to filter, display and summarize data, and make certain calculations, as well. Pivot tables are quite versatile; with very little effort on your part, you can modify the data analysis and/or the data-presentation layout. Excel pivot tables give you the ability to condense and to understand large amounts of data from various points-of-view.
Use excel pivot tables to:
- Combine data into groups and analyze those groups.
- Develop subtotals and grand totals from the data summary.
- Analyze the data by making use of special calculations.
- Filter the data using your own criteria.
- Optically present the data in the form of a chart.
- Use a variety of layouts that allow you to view the data from various perspectives.
PivotTable is a term trademarked by Microsoft. In this and other learning modules, we will use PivotTable to refer to Excel’s buttons or functions but will generally use pivot table to refer to the table itself.
What data can be used to create excel pivot tables?
Generally speaking, any kind and any amount of data that can be recorded in an Excel table can be used to as the basis for a pivot table. But data from other sources, such as from a relational database (e.g., Microsoft Access) or from an OLAP database can also be used for this purpose. Nonetheless, in this learning module we only discuss creating pivot tables from Excel tables.
Planning excel pivot tables
The only prerequisite for using a pivot table to analyze your data is that your Excel table contains much data, i.e., many columns or rows of data. If you have not got a significant amout of data, you can form a pivot table if you like, but that would make little sense as the data in a small table can usually be well understood just by looking at it. But it is when you have large amounts of data to deal with that pivot tables prove their worth.
If you want to use a pivot table to to analyze your Excel table, it is a good idea to consider first what information you want to extract, examine or summarize, and how you want to display the results. Some people use paper and pencil to plan which data should be analyzed and how the excel pivot tabls should be laid out. Before generating a pivot table, you might want to consider the following questions:
What data shall be displayed in which pivot table columns?
Which data should be displayed in the rows?
What data do you want to summarize and analyze?
Using which criteria might you want to filter the data?
To make these considerations more concrete, let us take the following example:
Suppose you have a lengthy Excel table in which all orders that your company received within a certain timeframe were recorded. After sorting by customer name, you realize that a number of customers placed multiple orders. You are interested in finding out how much each customer spent with your company. You therefore want to add together the value of all orders placed by each customer. And since your table identifies your company’s sales regions – EAST, MIDWEST and NORTH – you also want to see the order values broken down by region as well. With very little effort on your part, a pivot table could be generated that would give you all the information you want. Below, at the left, you see an excerpt from the Excel table. At the right you see a pivot table that was generated from the data in the Excel table…
Given the excel pivot tables, you can now make clear and accurate statements about the revenue breakdown for all customers in each of the three sales regions.