To Pivot or Not To Pivot

General interest question... How many people out there are using pivot tables on a regular basis? Who thinks they are indispensable and who can't understand what all of the fuss is about. I personally could not live without them. I am constantly retrieving large data sets out of our data warehouse and analysing them with pivot tables. I of course am a financial analyst in a relatively large organization with large amounts of data to wade through. Somehow I assume that everyone must be doing the same as I am.

I am putting together some training material for others here at the office (who work in a variety of different roles). This is intended for regular users of XL who are at an intermediate level. I was intending to put material on pivot tables high on the list of things to cover but I wanted to know if pivoting was a universal must have skill or if it applied more of a small niche. 

Jim,

I would say that everyone who do some kind of 'number' analyzing should know the Pivot table as well as the Autofilter /Advanced Filter + built-in db functions and the Subtotal function.

Pivot tables may be useful for some forms of exploratory data analysis drawing data from large datasets, but it's substantially less useful for other forms, such as sampling. They also don't recalculate automatically, so they're of much less use when the source data varies frequently.

Most of my work in Excel involves forecasting, discounted cashflow analysis and risk profiling. Pivot tables are of little or no use. There's one model I use that's maintained by a different department and that's similar do what you describe - use pivot tables to fetch data from data warehouses. But I use it maybe twice a quarter. For my own work, I never use pivot tables.

Which leads to a rule-of-thumb: if you frequently need data already stored in databases, pivot tables may be useful. If you use Excel mostly for calculations on varying, nontabular data, pivot tables are close to useless.

I was putting SumProduct ahead of the built in DB and functions... The db functions can be a bit more efficient but sumproduct is so flexible and easy. The DB's are a nice to have but IMO SumProduct is indispensable.

I was going to demo filtering at the same time as pivots... 

With most of my data stored in Databases, my first job when wanting to look at that data is how do I get it into a pivot.

I run a mail order company, so everything is looked at in 500 different ways to spot that small trend that could make a huge difference to catalogue response.

When training (I have to multi-task, should've been a woman), as soon as I get people past SUM, COUNTIF, etc. I get them on to pivot tables where they can do huge amounts of 'what-if I looked at it this way'. This was always the problem when database admins held the keys to paper reports, you would specify a report and think, "wow", that number looks good/bad, wouldn't it be nice to see the data this way... and the next three weeks went by and the head got thrown back with, "why didn't you ask that in the first place?", answer, "well I couldn't see the issue until I got the first report!".

I seldom, if ever use data off the worksheet it is too restricting, so it's either Access, SQL Server or iSeries.

Just my slant

I can agree tha the Sumproduct is more flexible to use then the db functions. But it may be more difficult to explain/learn how the Sumproduct works. Except for that too many complex Sumproducts tend to have a major negative impact on the performance. BTW, I believe Bob Philips has a good and detailed explanation about Sumproduct on his site.

Pivot, Pivot, Pivot. These are indispensible to data extraction related to project management, also for hierarchical data analysis. Not so easy to automate. I've even started working with consolidation pivot tables.

I was a big proponent of the array formula before I learned how to do the sumproduct trick.  In fact, I've never once used the sumproduct function for its intended purpose, nor can I think of a situation where I would need to.

With 2007, though, I've become a big fan of COUNTIFS and SUMIFS.  Between the two of them, they handle everything sumproduct could do, but are easier to understand and faster to execute.  Also, AVERAGEIFS is a nice touch.  As an example, I had a formula somewhat like this:

 =SUMPRODUCT((A1:A1000="MEDICARE)*(B1:B1000)="SURG")*(C1:C1000<>"EXCLUDE)*(D1:D1000)) / SUMPRODUCT((A1:A1000="MEDICARE)*(B1:B1000)="SURG")*(C1:C1000<>"EXCLUDE)*1)

I know that final *1 is gratuitous, but it helps in legibility. 

 The replacement formula is:

=AVERAGEIFS(D1:D1000,A1:A1000,"MEDICARE",B1:B1000","SURG",C1:C1000,"<>EXCLUDE") 

As for the original question, I use pivots all the time.  There's no other way to analyze a large chunk of data as quickly.  Sometimes it is far easier to pull a huge chunk off a SQL server and pivot it rather than try to figure out the proper syntax to get what you need right from the server.  I see myself doing that a lot more with the 65,536 barrier broken.

The DB functions may APPEAR to be easier to explain, but their use is likely to lead to errors. For example, given the data range (named Data)

 

Name	Age
Bob	10
Kate	20
Bobo	30
Katerina	40

and the criteria range  (named Criteria)

 

Name

Bob

What does the formula =DMAX(Data,"Age",Criteria) return? 30, of course, because Excel [mis|MAL]treats the criteria Bob as if it were Bob*. In order to match records for 'Bob' and not 'Bobo', you'd need to use ="=Bob" (or '=Bob) as the criterion. Then there's formula criteria which require blank field names in the criteria range and references to cells in the second row of the data table. And multiple rows for OR criteria. Excel inherrited this functionality from Lotus 1-2-3 Release 2 over 20 years ago. Lotus improved upon this syntax by 1989. Microsoft hasn't.

With SUMPRODUCT, all a new user has to understand is that it ONLY works with numbers, and Excel requires them to convert boolean values to numbers using idempotent arithmetic operations or chain multiple criteria together using binary arithmetic operations the same as they'd need to in pairwise boolean operations on arrays.

"Excel requires them to convert boolean values to numbers using idempotent arithmetic operations or chain multiple criteria together using binary arithmetic operations the same as they'd need to in pairwise boolean operations on arrays."

Wow...that's a mouthful.  I gotta tell you, if I'm ever in a spot where I'm using idempotent arithmetic or binary arithmetic operations to make a SUMPRODUCT function work, I'm quitting.

 

I use Pivot Tables fairly extensively to summarize a whole heap of transactional data.
I may then use GetPivot Data to extract values to a report layout that suits the client, but for sheer number crunching without having to write any formulae, it takes a lot of beating..

Using the 2003 List function or 2007 Table function or using dynamic rages fro earlier versions, easily copes with continually expanding data ranges.
Whilst I agree it would be nicer if PT's did automatically refresh (I had hoped that would come with 2007, but regrettably not), a simple one line piece of code as a sheet Activate event ensures that the PT is refreshed whenever it is viewed.

Whenever in code I extract data using GetPivotData, it is aways preceded by a PivotCache.Refresh, so there is never a problem.

I think that Pivot Tables are a hidden gem in the Excel arsenal, and they save a lot of un-necessary writing of formulae.

Just my 2 cents.

 

 

I wonder if that "hidden gem" comment is based on industry-specific experiece.  I say that because I've worked with a few financial institutions where pivot tables were very commonly used, even by users who were otherwise pretty inexperienced in Excel.  I've also worked with many hospitals where pivots were unheard of by intermediate users.  I don't know why that is.  In both cases there were situations where a nice pivot would have saved countless hours.  In the finance case, there were situations where pivots were used where a good sumif formula would've been more efficient.  I did not really start using pivots until about 5 years ago, but I've been heavily into Excel for 10.  Looking back, there were a lot of instances in those first 5 years where I could've done a much better job had I have known about them.

One thing I really wish would've been improved for Excel 2007 is the ability to use external data sources in a pivot.  With the pivot's calculation power, the overhauled charting interface, and VBA programmability, I really think a good external data interface would provide Excel the capability to replace something like Crystal Reports / Business Objects for building live reports and dashboards.  But until there's a way to build in queries across joined tables that can be changed on the fly, it's almost totally useless.  All you can do with it is perform a data dump from one table, and you have to build a data connection for each and every table you want to do that with.  Pointless.  Sure you can reference ADO and bring such data to a worksheet and pivot from that, but the whole point is that the data never even needs to be part of the workbook.

zfraile:

build in queries across joined tables that can be changed on the fly

Through MS Query you can join tables and modify queries to return very specific data sets, so I am a little unclear just exactly what you mean?

What I mean is, the external data connection is very specifically linked to one database that is defined when you create the pivottable and can't easily be changed.  I don't want to create a data connection for each different query I need to run.  I'm thinking of something more along the lines of returning a pivottable from the data returned from a connection string defined in one cell and command string defined in another.  There's ways to do this via VBA, but all require returning data to the workbook first.

That said, your comment got me to exploring the MS Query a little bit more.  I found that if I do create a pivottable from MS Query, I can modify the connection string and commandtext in VBA, but it doesn't seem to work with Windows authentication.

For example, I'd like to put together a worksheet that shows a pivottable that reaches into a hospital service database for the month of service and a service intensity factor.  I want to show the average service intensity factor on a monthly basis, and I want to update this weekly.  I might want to do this side-by-side for ten different hospitals.  I might also want to break break this out by insurance company.  To do so using data connections is just cumbersome, and also messy as all of those connections get stored outside of the workbook as well.

If the query you wrote is reusable with some minor tweaks then save the query and you can hook now pivot tables to it. if it is not reusable then if you hit the Exit Door icon in MS Query you will exit directly back to XL embedding the query directly in the pivot table. If you want to change the connection without all of the hassel of VBA you can save the file as XML and open it with notepad which will allow you to modify your connection strings...