Category: PIVOT TABLES

Compute “running total in” across years in a Pivot Table

{2 Comments}

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 Total in, resets the quantity sold to 0 when the year changes.

This issue can be overcome by writing DAX formulas in a Power Pivot.  You may refer to my solution in this workbook.

Perform a Variance Analysis within a Pivot Table

{16 Comments}

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 one attempts to write a calculated item formula within a Pivot Table, then variance rows will appear even for cases where Budget figures are equal to Actual figures i.e. where variance is equal to 0.

You may view the question, actual dataset, the failed Pivot Table attempt and final Power Query attempt in this workbook.

Ensure that “Show Value as” feature of the Pivot Table works even when some Pivot Table columns are unfiltered/hidden

{9 Comments}

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 the same for every month for two year (2012 and 2013)."

While this is easy to accomplish using the "Show Value As" feature of a Pivot Table, the problem arises when a particular year is filtered out from the Pivot Table.  This activity of filtering out a year from the Pivot Table causes the other computed Sales Delta figures to vanish.

This problem can be overcome by creating a Pivot Table from the PowerPivot tool of Excel 2010 and higher versions.

You may refer to the question, Pivot Table attempt and PowerPivot solution in this workbook.

Create a Pivot Table from multiple individual ranges without using ancillary columns

{16 Comments}

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 a Pivot Table to generate the following:

1. Region wise and visitor wise:
a.  Fee per visit
b.  Frequency of visit
c.  Revenue
2. Revenue collected by month wise and by visitor

In this workbook, I have shared two solutions:

1. PowerPivot solution - This solution answers both questions above without using any calculated/ancillary columns in the base data.  By establishing simple relationships in the PowerPivot window and by writing two calculated Field formulas, both questions above have been answered.  To use this PowerPivot solution, you need to be using the PowerPivot add-in for MS Excel.  This add-in is only available for Excel 2010 and higher versions.

2. Pivot Table solution - This solution answers both questions above by using calculated/ancillary columns in the base data.

Here's another example.  Assume a four column table showing Date of session, Client, Location and Participant Name.  Assume another four column table showing Client, Date of session, Date of invoice and Amount Billed.  The task is to determine the Amount billed per location.  You may refer to my PowerPivot and Pivot Table solution in this workbook.

Computing penalties by Employee, Group and Labour type using a PowerPivot

{0 Comments}

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. Actual salary
2. Maximum permissible salary
3. OverUnderMax - This is calculated as the difference between Actual Salary and Maximum permissible salary
4. Penalty - Maximum of 0 and OverUnderMax

As can be seen in the Pivot Table worksheets of this workbook, there is a problem with the result of the calculated field formula result in the Grand Total cell.

You may refer to my solution in the PowerPivot worksheets.  Please note that to see my result in the PowerPivot worksheets, you will have to use the PowerPivot add-in for MS Excel 2010/2013.

Show granular as well as total figures on the Summary sheet

{0 Comments}

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 and resolved.

The task is to create a Summary sheet which:

1. Show the consolidated figure of complaints received and complaints resolved for both years
2. Show the individual regions which make up the consolidated figure in 1 above.

In this workbook, you may see the data layout and four different ways of resolving the two tasks mentioned above.

You may also refer to the following related posts:

1. Summarise data from multiple worksheets with one condition
2. Summarise data from multiple worksheets with one condition - Part II
3. Summarise data from multiple worksheets
4. Summarise data from multiple worksheets with multiple conditions
5. Summarise data from multiple worksheets with multiple conditions - Part II
6. Summarise data from different cells of multiple worksheets

Count unique values with conditions on large databases

{8 Comments}

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 Excel 2010 and higher versions

2. Using a  normal Pivot Table and SUMPRODUCT() function - Will work for all versions but is not as efficient as the PowerPivot solution.

To count unique values with conditions on small databases, you may refer to the following link

Calculate a unique count with conditions in a Pivot Table

{12 Comments}

Assume a three column table arranged as follows: Circle, Date of Fault and ID.  Dates in the date range span one week - November 26, 2012 to December 2, 2012.  A particular equipment can be only one specific Region and the same equipment an go faulty multiple times within one week.  Data for one week is about 8,400 rows.

There are three questions to be answered from this data:

1. The Circle wise, count of ID's which went faulty more than twice between November 26, 2012 and December 2, 2012; and
2. The Circle wise, count of faulty instances more than twice between November 26, 2012 and December 2, 2012; and
3. Determine individual sites for 1 and 2 above

The difference between 1 and 2 above is "If a certain ID goes down 4 times, then for question1, the answer should be 1.  For question2, the answer should be 4."

The first question basically boils down to "Count of unique ID's by Circle which went faulty more than twice."

There are two ways one can go about answering the questions above:

Solution A - For Excel 2010 and higher versions - This solution is for those using the PowerPivot MS Excel add-in for Excel 2010 and higher versions.

Solution B - For all versions of MS Excel - This solution will work in all versions of MS Excel but for those using Excel 2010 and higher versions, the PowerPivot solution would be far more efficient.

The steps for creating a pivot table under Solution B for answering both questions above are:

1.  Count of downtime sites.xlsx is saved in a folder on the desktop;
2. Open the workbook, select the data on the Base_Data sheet (including the first row as the header row – it will be range A1:C8741.  Ensure that the header row has some distinctive formatting such as Bold or some colour) and press Ctrl+F3 > New.  In the Name box, type Dummy and click on OK > Close.
3. To cross check that the name assigned above has indeed been assigned correctly, select the data range once again and in the Name box (left of the formula bar), Dummy should appear.
4. Select range A1:C8741 of the Base_Data sheet again and press Ctrl+T to convert this range into a Table.  Ensure that the “My Table has headers” box is checked.  Save the workbook.
5. Open a new worksheet and go to Data > From Other Sources > From Microsoft Query
6. Under Databases, select Excel files > OK
7. In the Directories dialog box, navigate to the folder on the desktop where the workbook file is saved.  So for me, it is saved under C:\Users\Ashish\Desktop\ and double click on the folder where the workbook is saved.
8. In the left hand side window, select the Count of downtime sites.xlsx file and click on OK
9. With Dummy selected, click on the > symbol to bring over all columns of this named range to the right hand side box 10. Click on Next three times
11. Select the option of View Data or Edit Query in Microsoft Query
12. Click on the SQL button, delete the contents in the white space there and paste the following SQL Query

SELECT ucase(dummy.Circle) AS 'Circle', ucase(dummy.Indus_Site_ID) AS 'Indus_site_ID', Count(dummy.Indus_Site_ID) AS 'fault_frequency'
FROM `C:\Users\Ashish\Desktop\Count of downtime sites.xlsx`.dummy dummy
GROUP BY ucase(dummy.Circle), ucase(dummy.Indus_Site_ID)
HAVING (Count(dummy.Indus_Site_ID)>2)

13. Click on OK and on the message box which appears, click on OK
14. Under File, select the last option – Return Data to Microsoft Excel
15. At this stage, if you wish to get data in a tabular form, then select Table.  If you directly want a pivot table, select the second option button – Pivot Table.  For this example, select Pivot Table and in the cell reference box, select any cell where you would like to the result to appear, say cell A1.  Click on OK
16. A counter will run at the bottom left hand side with the title of Reading Data
17. Drag Circle and ID to the to the Row Labels
18. Drag Fault Frequency to the Value Area twice
19. Right click on any one number in the fault frequency column and under Summarise Value by, select Count
20. Right click on any value in the ID column and under Expand/Collapse, select Collapse Entire Field.

Compute Pro rata growth rate within a Pivot Table

{0 Comments}

Assume a four column input data range (refer sheet named "Input" of this workbook) arranged as follows:

1. First tow columns are Group and Type which have text values
2. Third column is a month column with entries such as Dec_11 and Sep_12.  These denote 12 months ended December 2011 and 9 months ended 2012
3. Fourth column is Revenue which has numbers

The objective is to compute pro rata growth rate of Sep_12 revenue over Dec_11 revenue within a Pivot Table.  In the workbook (see link above), I tried to use a calculated item formula within a Pivot Table to compute this but encountered three difficulties in doing so (refer Notes section of sheet named "Result of Calc item formula ").

There are two ways one can go about answering the questions above:

Solution A – For Excel 2010 and higher versions – This solution is for those using the PowerPivot MS Excel add-in for Excel 2010 and higher versions.

Solution B – For all versions of MS Excel – This solution will work in all versions of MS Excel but for those using Excel 2010 and higher versions, the PowerPivot solution would be far more efficient.

The steps for creating a pivot table under Solution B are:

The Objective is to change the orientation of the base data such that a calculated field formula can be written within a Pivot table instead of a calculated item formula.  The process to change the orientation of the base data is described below:

1. Select range A2:D14 of Input sheet and press Ctrl+F3 > New.  In the name box, type Dummy
2. Select range A2:D14 of Input sheet and press Ctrl+T to convert to a Table
3. Open a third worksheet in the workbook (downloaded from the link above) and save the workbook, say on any folder on the desktop
4. While the active cell is any cell of the new worksheet, go to Data > From Other Sources > From Microsoft Query
5. Under Databases, select Excel files > OK
6. In the Directories dialog box, navigate to the folder on the desktop where the workbook is saved.  So for me, it is saved under C:\Users\Ashish\Desktop\ and click on the folder where the workbook is saved
7. In the left hand side window, select the workbook and click on OK
8. With Dummy selected, click on the > symbol to bring over all columns of this named range to the right hand side box
9. Click on Next three times
10. Select the option of View Data or Edit Query in Microsoft Query
11. Click on the SQL button and delete whatever you see in the box there
12. Enter the following SQL statement in the white box

Transform sum(rev)
SELECT Dummy.`GROUP`, Dummy.TYPE
FROM Dummy
Group by Dummy.`GROUP`, Dummy.TYPE
Pivot Mth

13. When you click on OK, you will see a four column database (refer sheet named "Result of MS Query")
14. Under File, select the last option – Return Data to Microsoft Excel
15. At this stage, if you wish to get data as you saw in MS Query then select Table.  If you directly want a pivot table, select the second option button – Pivot Table.  Select any cell where you would like to the result to appear, say cell A1. 16. Click on OK.  A counter will run at the bottom left hand side with the title of Reading Data
17. If you had selected pivot table in step 15 above, then the pivot table grid/layout will appear
18. You may now drag fields to create a pivot table
19. You may now write a calculated field formula within the Pivot Table (refer sheet named "Output")

As can be observed in the "Output" sheet, all the shortcomings mentioned in the Notes section of the "Result of Calc item formula" sheet have been overcome.

Create a Pivot Table from multiple worksheets in different workbooks

{65 Comments}

Assume two workbooks named Book1.xls and Book2.xls.  In Book1.xls, there is a sheet named Jan and in the Book2.xls, there is a worksheet named Feb.  In both the worksheets - Jan and Feb, the following exist

1. Same number of columns on both worksheets; and
2. Same order of columns on both worksheets; and
3. Same spellings of headings on both worksheets

As you can observe in zipped file, all three aspects mentioned above are in place.  One may want to perform any of the following tasks:

1. Create one pivot table from both worksheets (which are in different workbooks); and/or
2. Consolidate data from both worksheets (which are in different workbooks) one below the other

To curtail the workbook size, I have deleted rows of data.  In each workbook, there were 65,000 rows of data on each Jan worksheet and Feb worksheet.

While one simple way would be to copy and paste data from Jan and Feb worksheet in a third worksheet and then create a pivot table, the following shortcomings exist with this method

1. Copying and pasting data from multiple workbooks into one is a manual process; and
2. Since the pivot table will be based on the manually created sheet (by copying and pasting), changes in the two original workbooks will not update the pivot table on refreshing unless the same changes are carried out in the consolidated sheet (created by copying and pasting above) as well.  So this leads to duplication of effort.

Both problems outlined above can be resolved by using MS Query.  The technique mentioned below will work as long as the three conditions mentioned above are satisfied.  Furthermore, since MS Query will only recognize named ranged with rows up to 65,536, the number of rows of data in each of the worksheets (Jan and Feb) should not exceed 65,536.  The combined rows in both worksheets could be any number.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).  In order for the Power Query solution to work, all files will have to be saved as CSV files in the same folder (Since a CSV files can have only one worksheet, the Jan and Feb worksheets in the other workbook will have to become two workbooks - Jan.csv and Feb.csv).  In Power Query, there is a feature to append data from multiple CSV files lying in a specific folder into one running range.  Once this is done, the running range can either be transferred to an MS Excel worksheet (if the number or rows are up to 1 million) from where a Pivot Table can be created OR the running range can be loaded to the Data Model (Excel 2013) [the Data Model will be able to accommodate more than 1 million rows depending upon the hardware and software configuration of the machine] from where a Pivot Table can be created.

You may watch a short video here:

Solution for all versions of MS Excel

The steps for creating a pivot table from multiple worksheets (both in the same file) are:

1. Both workbooks - Book1.xls and Book2.xls are saved on the desktop.
2. Open Book1.xls, select the data on the Jan sheet (including the first row as the header row - on the Jan sheet, it will be A1:S4.  Ensure that the header row has some distinctive formatting such as Bold or some colour) and press Ctrl+F3 > New.  In the Name box, type Dummy and click on OK > Close.
3. To cross check that the name assigned above has indeed been assigned correctly, select the data range once again and in the Name box (left of the formula bar), Dummy should appear.
4. Select A1:S4 of the Jan sheet again and press Ctrl+T to convert this range into a Table.  Ensure that the "My Table has headers" box is checked.  Save and close Book1.xks
5. Repeat steps 2 - 4 for the Feb sheet on Book2.xls as well.  In step 3, just change the name to Dummy1.  Save and close Book2.xls
6. Open a new workbook and go to Data > From Other Sources > From Microsoft Query
7. Under Databases, select Excel files > OK
8. In the Directories dialog box, navigate to the folder on the desktop where the Book1.xls file is saved.  So for me, it is saved under C:\Users\Ashish\Desktop\ and double click on the folder where Book1.xls file is saved
9. In the left hand side window, select the Book1.xls file and click on OK
10. With Dummy selected, click on the > symbol to bring over all columns of this named range to the right hand side box
11. Click on Next three times
12. Select the option of Return Data to Microsoft Excel
13. At this stage, if you wish to get data in a Table form then select Table.  If you wish to create a pivot table, select the second option button - Pivot Table.  Select any cell where you would like to the result to appear, say cell A1.
14. Click on OK.  A counter will run at the bottom left hand side with the title of Reading Data
15. If you had selected pivot table in step 13 above, then the pivot table grid/layout will appear
16. You may now drag fields to create a pivot table

So the Table or pivot table which you have created so far is only from Jan sheet of Book1.xls.  We have to also include data from Feb sheet of Book2.xls.

17. If you had created a Table in step 13 above, then click on any cell in the Table and under Table Tools (yellow button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.  If you had created a Pivot Table in step 13 above, then select any cell in the pivot table and under Pivot Table Tools (red button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.
18. In the Command text box, you see something like this

SELECT dummy.Invoice_number, dummy.Date, dummy.`Article Number`, dummy.`Article Name`, dummy.`Tax Type`, dummy.Brand, dummy.Category, dummy.`Sports Code`, dummy.`Product Type`, dummy.MRP, dummy.`Customer #`, dummy.`Customer Name`, dummy.Region, dummy.`Area Code`, dummy.Quantity, dummy.`Line Discount`, dummy.`Sales Value`, dummy.`Before Tax`, dummy.`VAT Amount` FROM `C:\Users\Ashish\Desktop\Book1.xls`.dummy dummy

Replace this lengthy statement with the following shorter one

SELECT * from `C:\Users\Ashish\Desktop\Book1.xls`.dummy
Union all
SELECT * from `C:\Users\Ashish\Desktop\Book2.xls`.dummy1

19. Click on OK and the Table/Pivot Table should update
20. Save the file as Consolidated.xls and close it.

Update Table/Pivot Table for addition/editing in existing named ranges

To see the effect of a dynamic Table/pivot table, edit data in Jan and/or Feb sheets of Book1.xls and/or Book2.xls.  Save and close the file(s).  Open Consolidated.xls and refresh the Table/Pivot Table (Right click and Refresh).  At the bottom right had side the counter will run again and (step 14 above) and once it has read all data, the Table/pivot table will update.

Furthermore, since the two named ranges (Dummy and Dummy1) have been converted into Tables, even if you add data by rows (with no row being left entirely blank), when you right click to Refresh the pivot Table, data of new rows will appear.

Update Table/Pivot Table for addition of new worksheets

Assume you now want to add data from a worksheet titled March in Book3.xls.  To include this sheet in the pivot Table, follow the under mentioned steps:

1. Open Book3.xls

2. Follow steps 2 - 4 mentioned above for the March sheet.  In step 3, just change the name to Dummy3

3. Save and close the workbook

4. Open Consolidated.xls

5. Select any cell in the pivot table and under Pivot Table Tools (red button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.  If you selected Table (instead of Pivot Table) in step 13 above, click on any cell in the Table and under Table Tools (yellow button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.

6. Click on the Definition tab and under Command Text, add the following at the end of the SQL query:

Union all
SELECT * from `C:\Users\Ashish\Desktop\Book3.xls`.dummy3

7. When you click on OK, the counter will run again and the pivot should reflect data from March sheet.