7 Ways Pivot Tables Help You Analyze Data

A Pivot Table is an effective tool for the purpose of capturing, summarizing and analyzing data. These are often referred to as spreadsheets. The most common applications include Microsoft Excel, Lotus 1-2-3, and SpotFire. Most software systems have an export option which allows data to easily be downloaded into a spreadsheet application.

Pivot Tables provide users with the functionality to sort, count, and total the data they have collected. Pivot Tables are easy to use with drag and drop fields, new column and row insertion, and automatic calculations for quantity and currency calculations. Data can easily be sorted and filtered for multiple views to provide easy analysis. Most applications also include graphing functionality for a visual presentation of data summary.

Example of the Data Captured:

The following sample spreadsheet displays the data collected by a business owner after advertising her products on the Internet starting July 1, 2010.

Date Name Sex Age State Action Order Referral
07/10/10 Bill M 33 NJ Sale $100.00 Internet
08/03/10 Sam M 25 GA Inquiry Internet
08/12/10 Lena F 27 FL Inquiry Internet
08/24/10 David M 40 GA Inquiry Telephone
08/29/10 Mary F 42 FL Inquiry Telephone
09/01/10 Sam M 25 GA Sale $100.00 Internet
09/11/10 Lena F 27 FL Sale $100.00 Internet
09/13/10 Chris M 55 CT Inquiry Internet
09/17/10 Sue F 36 AL Inquiry Internet
09/18/10 Sue F 36 AL Sale $200.00 Internet

Pivot Table Summary Information:

Following are 7 examples of how this Pivot Table provided this business owner with good data analysis critical to the success of her business.


  • Visitors – 10
  • Inquiries Only – 6 (60%)
  • Sales – 4 (40%)

#1 – After posting her business online, this owner received 10 visitors to her website. 60% of those visitors inquired about her products. More importantly, 40% of those prospects became clients and purchased her merchandise. A 40% sales ratio shows an exceptionally high rate of success for this short amount of time. This business owner now knows her marketing efforts, while fairly new, are already producing great results!


  • Visitors in July 2010 – 1 (10%)
  • Visitors in August 2010 – 4 (40%)
  • Visitors in September 2010 -5 (50%)

#2 – This online marketing strategy was initiated in July 2010 and only in place for 2.5 months. Although initial activity for the first month was low, activity increased each month thereafter. With 12 days left for additional activity in the month of September, this business owner can be confident with the significant percent of growth provided by these early results.


  • Activity from NE Region – 2 (20%)
  • Activity from SE Region – 8 (80%)

#3 – Demographics collected are important for effective marketing. These early results show this business owner that her products are more popular for residents in the SE than the NE. This is great information that she can use for specific target marketing efforts in the southern region of the United States in order to increase her revenues at a faster rate.


  • Internet – 8 (80%)
  • Telephone – 2 (20%)

#4 – These statistics confirm where the majority of the business is coming from. During the 2.5 months of her online marketing launch, 80% of her business came from online shoppers. This tells her the online marketing strategy is working better than cold calling prospects. It also confirms the overall effectiveness of her website.


  • Total Inquiries to Date – 6 (60%)
  • Inquiries by Men – 3 (50%)
  • Inquiries by Women – 3 (50%)

#5 – This analysis confirms that her prospects and clients are both men and women. This is important information so she continues to develop her marketing strategies in a manner that is appealing to both sexes.


  • Average No. of Inquiries Per Month – 3.33
  • Average No. of Inquiries Prior to Sale – 0.75 (75%)

#6 – So far this business owner has had exceptional results experiencing an average of 3.33 inquiries per month that turned into her 40% sales ratio. While 3 of the 4 sales included a separate inquiry prior to purchase, this data shows that at least 1 sale occurred during a first site visit. That means some of her clients will buy her product at first glimpse virtually eliminating a lengthy sales cycle altogether.


  • Total Sales Generated – 4 (40%)
  • Male Consumers – 3 (50%)
  • Women Consumers – 3 (50%)
  • Average Sale Amount – $125
  • Largest Sale Amount – $200

Total Revenue Generated $500

#7 – Understanding your sales is critical to increasing them! The Pivot Table analysis shows this business owner that out of the 40% sales ratio she experienced, her product was sold equally to men and women.

Her lowest sales amount was $100 and largest at $200, giving her an average sale of $125. During the 2.5 months from the start of her marketing launch she has already generated $500 in revenue. That’s an average income of $200 per month to date. By using a Pivot Table to analyze her data, she is able to quickly compare her results against her projections to determine how well her plan is working.

The Pivot Table provides a summary of data providing business owners and managers with valuable information about the effectiveness of their marketing efforts.

This is a great tool to determine the demographics of a target market, the most effective ways to find prospects and clients, and the overall effectiveness and success of the marketing strategy. Pivot Tables provide the detailed information needed for real time measurement of results against the business plan, goals and budget for any business. It is one of the most effective measurement tools for growing a successful business.

About the author


I am Vishal Gaikar, Engineer, Web Addicted, Living in Maharastra, India. Email Me @

Leave a Comment

CommentLuv badge