Ms Business Intelligence Consulting: Excel Pivot Tables

Pivot tables are most often created directly using Microsoft Excel. Using Excel’s user-friendly Pivot table wizard, it is easy to Pivot, and re-pivot columns so that the columns can be organized any way the user deems useful for collecting critical business intelligence. Pivot tables are a simple introduction to the power of business intelligence, and form the foundation for the design of powerful analytics cube solutions.

Pivot tables summarize data into rows and columns of summed up (aggregated) data. Excel spreadsheets are most often designed as flat, one-dimensional tables, consisting of data elements organized under column headers. This often makes it difficult to get summarized business intelligence, particularly when data needs to be grouped and sub-totaled into any number of categories or dimensions. Our Business Intelligence Consultants overcome this obstacle by quickly aggregating and summarizing large amounts of flat data, giving it depth and the desired values.

A simple form of a Pivot table is Cross Tabulation (Cross Tab), which is a two-dimensional view of data. For example, a “Monthly Sales by Region” Pivot table might show the different sales regions as columns across the top, and the time dimension e.g. Months, Quarters, Years along the left column. The aggregate sales unit figures for each region for each time period display in the individual cells.

The data could also be represented as three columns in a traditional spreadsheet view e.g. Month, Region, Amount, but the table would be tall and narrow, difficult to read, and thus potentially obscure important business intelligence.

The name Pivot derives from the fact that if you Pivot, or rotate one of the fields (in this case the region), up to make them column headers, you get a much more readable output. By adding another column to the Pivot table, one could easily view the units sold each quarter through various points of sale.

New columns can also be dragged onto (and off of) the Pivot table from a list of available fields. Pivot tables allow several different types of aggregations for business intelligence including: sum, average, standard deviation, count, etc. In the example above, sum aggregation is used to show the total number of units shipped by region and channel over Q1 and Q2.

Pivot Charts

Pivot Charts provide a visual representation of a Pivot Table’s aggregated data, and a wide range of charting and graphing controls are available including, Microsoft Excel’s built-in charting functions. Pivot charts are frequently used to view historical data trends and to measure key performance indicators, providing critical business intelligence. Any number of Pivot charts and Pivot tables can be combined to provide an executive dashboard.