# Category: PIVOT TABLES

Assume quantity sold date by date and City in a three column database.  The objective is to determine year wise, month wise and City wise running total of quantity sold in a Pivot Table. The issue which will arise with generating this result in a Pivot Table will be that the Show Values As > Running […]

Assume that a tabular database shows Year wise, month wise. Account and Activity wise expenditure under difference Expenditure heads.  While it is easy to drag columns inside a Pivot Table to analyse data, one may want to compute the Account wise and Activity wise Variance between May and August for all expense types except two. If […]

Let’s say the data sheet has Sales and Profit data by Product, Region, Salesman and Date.  One may want to analyse the following via a Pivot Table “The Sales Delta by year, month, salesman and product i.e. for May 2012, compute the [Sales in May 2012] less [Sales in May 2011] for every salesman and product.  Do […]

Assume that there are three separate tables showing the following information: 1. Date of visit data for visitors to a certain recreation facility.  The same visitor may visit the facility multiple times 2. Fee per visit in different cites 3. Region in which each city lies The question is to analyse the three tables above via […]

Assume a database of Maximum allowed pay and Actual pay for each employee.  The employees have been further categorized into Groups and labour categories. The task is to create three Pivot Tables (one each with Employee name, Group and Labour category in the row labels) with the following information in the Value area section: 1. […]

Assume there is data for complaints received and complaints resolved for two years – 2009 and 2010 for some regions regions.  Each Region has its own worksheet.  There are three headings on each sheet – Particulars, 2009 and 2010.  While the Particulars column has the text “Complaints received” and “Complaints resolved”, the year columns have number of complaints received […]

Given a database of 50,000 rows, counting unique values with conditions using formulas would either adversely effect workbook performance or would not work in the first place at all. In this workbook, I have shown the technique to count unique values with conditions on a large database 1. Using PowerPivot – Will only work in […]