+ Reply to Thread
Results 1 to 5 of 5

Request for tips working with 900,000 row spreadsheets

  1. #1
    Registered User
    Join Date
    01-26-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    3

    Request for tips working with 900,000 row spreadsheets

    Hi, again.
    For many reasons that I would rather not go into here, I am limited to working with a spreadsheet (Excel 2010) instead of using a database application for analyzing really, really large spreadsheets. One that I am currently working with has over 911,000 rows. I am using pivot tables somewhat successfully, but working with worksheets this large takes some significant processing time and requires a deliberate approach to extracting usable tables to minimize recalc time and frequency. The spreadsheet also has 20+ columns.

    Does anyone have any tips that you would like to share for more efficient data analysis?

    Thanks, to all

    Randy

  2. #2
    Registered User
    Join Date
    01-26-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: Request for tips working with 900,000 row spreadsheets

    Hmm, you might have to narrow that question down a bit lol. I'm in the same boat though--I can't use a database at work although I'd prefer to, and stuck using multiple spreadsheets.

  3. #3
    Registered User
    Join Date
    01-26-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Request for tips working with 900,000 row spreadsheets

    Thanks Nellie,
    Currently I build a pivot table report for the data that I need and the copy it to another workbook for further analysis. But the original data is nearly 100MB and reports are in the range of 15-20 MB before any filters, or other manipulations are performed. I know the data is what it is and really can't be diminished or the output would be compromised. But the method of cutting and slicing the data has to be easier ... right? Is there any other method that uses less PC resources than pivot tables? Even with caching the files, performing minimal operations on the main data, and only formatting the data as text it still can take 5-6 minutes for saves, copies and formatting of columns. Any ideas?

    Thx
    Randy

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,077

    Re: Request for tips working with 900,000 row spreadsheets

    When I have had to use large tables before (not as big as yours though), what I did was to have 1 file as my data base, and then I used another file to reference the 1st file.

    In some cases, I had some minimal calculating in the big file, but most of the calcs were done in the 2nd file
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    01-06-2013
    Location
    Orange Park, Florida, USA
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: Request for tips working with 900,000 row spreadsheets

    Mikery1985.

    I have found the list at Chandoo to be pretty useful. 75 tips from readers on how to speed up excel.

    http://chandoo.org/wp/2012/03/27/75-...eding-up-tips/

    good luck
    Rick

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1