Are you faced with having to analyze large amounts of data? Excel’s Pivot tables may be just the tool to give you the answers you need. Pivot tables provides a flexible way to summarize your data in many different table views and charts. Plus you can drill down to see the underlying detail behind your summary.
Once you have created your first pivot table, you will be amazed at how easy it is to manipulate your data by rotating the rows and columns. You can literally “pivot” the information in a variety of configurations to best suit your analysis.
What is a Pivot Table? A Pivot table summarizes a list of information in a three dimensional table. Your raw data is summarized and cross-tabulated in a meaningful way that you define.
Now that your Pivot Table has been created, you can easily “pivot” the rows and columns to create different interpretive views. You can filter the data, display or hide details, group items, create formulas, produce totals and create visual representations with a chart. Plus, like most Excel features, you can refresh the results when your underlying data changes.
Basic Terminology
• Fields – Categories of data (Column labels in your database)
• Record – individual line entries in the data list
• Items – unique values in each field (i.e., Beauchene is an item in the Sales Rep. field)
• Data Field – contains numeric data or a count of text entries, summarized by column/row/page fields. (i.e. find the number of Customers by Industry; of get a total of Sales by State)
• Column/Row/Page field – contain limited sets of unique values that can be summarized (i.e., years, months, product codes etc.)
Pivot tables are created from an Excel list or database. External data from an Access, DbaseIII or other database can also be used. When working with an external source that is not dependent on periodic updates, I prefer to import the external data into an Excel Workbook prior to creating the Pivot Table. However, you can link to a dynamic external data source and select settings that will refresh your pivot table with any updates. You can also use Multiple Consolidation Ranges as your source data or base a new Pivot Table on an existing one.
An Excel list must be in conformance before you can create a pivot table. Meaning, the column headings in your worksheet must be a different format than the data contained in the list. Furthermore, the list cannot contain mixed data in a column and blank rows and columns are prohibited. Adhering to these precautions, you will be successful in creating your pivot tables. A good reference on organizing your Excel data in a list format can be found by examining “Guidelines for Creating a List on a Worksheet” in the MS Help files. You can also reference List Management.
Once you have a conforming Excel list, you are ready to manipulate your data using Pivot tables as an analytical tool. Once you discover how easy it is, you will be hooked on their power.
