Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a 60mb Excel file containing around 2.1 million rows of information split across monthly worksheets (i.e. October contains 150,000 etc).

I can't amalgamate these in to a single worksheet because of Excel's maximum number of rows.

The PivotTable I'm trying to produce is something along these lines - this is taken from a single worksheet.

Single worksheet manual PivotTable

I want to be able to see how many times my students, parents or staff have accessed certain pages - hence the setup of...

  • Values: Count of date
  • Row Labels: filename
  • Report Filter: profile

If I use the Wizard, I can consolidate all of the worksheets' data into a single PT, but the labels don't seem to work the way I want them to.

All worksheets with PivotTable wizard

Is there any way of EITHER adding additional ranges to the manual PT (possibly using 3D references? I'm not sure how they work - currently the reference I'm using for an individual worksheet is 'September 11'!$A$1:$K$137145), OR setting up the PT Wizard so that my fields become usable in the way I want them?

Thanks in advance,

share|improve this question
You are really bumping up against the useful limits of excel's capacity here. Ms Access might make your life easier. Do you have 12 month tabs in a workbook? – ako Oct 12 '12 at 4:26
9, as it's a school year. Can I import data directly from worksheets to Access? – dunc Oct 12 '12 at 7:59
There is pretty good interoperability between Access and Excel. You can import using a wizard, or link to the data and just use Access as a front. The benefit of an SQL environment is you can set up queries to run periodically when data changes. – ako Oct 12 '12 at 17:42
the labels don't seem to work the way I want them to seems a bit less clear than it might be. – pnuts Apr 7 at 0:38

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.