Excel Tutorial: How To Pivot Un-Pivotable Data

If you want to use one of Microsft Excel's most powerful features to summarise, analyse and present your data- you need to know how to create, use and manipulate a Pivot Table.
To successfully use a Pivot Table in Excel we usually need what is called transactional data or a flat data table. These are usually identifiable as a set of data containing rows and columns. A data set that is not summarised already. So just how do we know if the data is summarised already?.

One common way to spot already summarised data is that of a work book that has the months spread across columns. The months area already summarised. This is always a giveaway!
If the data is already summarised then we really cannot manipulate it more especially not by using a Pivot Table.

So what can we do about it, can we make this data 'pivotable'?
Can we change it in some way so we can carry out some analysis on it?
Of course we can. There is a a neat trick is available. It involves taking already formatted summary data and transform it right back into transactional data. Here is how-

• Firstly go to create a Pivot Table as normal
• Hit ALT+D then P to bring up the Pivot Table Wizard (Excel 2010-)
• In Step 1 choose select Option 3 -Multiple Consolidation Ranges
• Hit Next
• Allow Excel to create a single page in Step 2
• Hit Next
• Click Add then Next
• Click Finish on step 3 of the Pivot Table Wizard
• One Pivot Table is created that does not look great

This is the clever part though, just hit the total cell at the very bottom right, (the Grand Total for Rows and Grand Total for Columns) and as you know by double clicking on any cell in a Pivot Table you get a new excel work sheet with all of the data rows and columns that makes up that cell.
So all we do now is double click and a whole new page of transactional data appears. Just like magic!.

We can now see a list of transactional data which shows a new record for every intersection of a column and row in our original data set.
We can now go and anlayse our data set with a Pivot Table.

• Highlight your data set
• Click the Insert Tab
• Select the PivotTable button from the Tables group
• Select PivotTable from the list
• The Create PivotTable dialog appears

Go right ahead and explore your data.

BJ Johnston has been an advanced Excel user for 15 years and is the creator of http://www.howtoexcelatexcel.com/?page_id=2 a site that shares Excel tips and tricks with it's enthusiastic members. To join in the discussion, where users are working smarter and faster with Excel sign up for the FREE newsletter and as a bonus receive a FREE EBook- 50 Top Tips and Tricks. http://www.howtoexcelatexcel.com/

Article Source: http://EzineArticles.com/?expert=BJ_Johnston

Article Source: http://EzineArticles.com/8486127