+ Reply to Thread
Results 1 to 4 of 4

Excel File - Slow to Open/Save (2.5MB)

  1. #1
    Registered User
    Join Date
    12-10-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    2

    Excel File - Slow to Open/Save (2.5MB)

    Hi,

    I have an excel file that takes long to open/save.

    The sheet is designed to take test data and analyse and produce graphs/ test report. Even with no data entered it can take ~2 minutes to open or close the file.

    The files contains conditional formatting, named ranges, a number of macros (if these make any difference?).

    I've tried looking through existing threads for solutions i.e. searching for external links, disabling 'save external link values), but haven't solved the problem.

    I have also tried saving locally & on the network, both give the same issue.

    Is there anything you guys can suggest?

    Thanks in advance.
    Attached Files Attached Files
    Last edited by asrarrazaq; 06-28-2019 at 03:59 AM.

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Excel File - Slow to Open/Save (2.5MB)

    The dynamic named ranges (using volatile function OFFSET) are very expensive to performance. You have a large number of these. There are better ways of segmenting your data, than this.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    12-10-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    2

    Re: Excel File - Slow to Open/Save (2.5MB)

    Hi Olly,

    Thanks for your response. What alternatives would you suggest? the ranges have to be dynamic as the amount of rows in the test data can vary form test to test. Also the test is often done in 3 cycles, the sheet needs to be able to differentiate between the cycles and operate on the data pertaining to each cycle individually.

    Once again thanks for your help.

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Excel File - Slow to Open/Save (2.5MB)

    I'd suggest a completely different approach, where you normalise and categorise your data first, then report against it by parameter. I'd use Power Query to transform and restucture the source data, and Power Pivot to report on it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excel file open too slow
    By younan in forum Excel General
    Replies: 1
    Last Post: 02-24-2019, 04:47 PM
  2. very slow open, save, close and file bloating
    By boonwd in forum Excel General
    Replies: 5
    Last Post: 05-30-2018, 05:36 PM
  3. [SOLVED] Slow to open excel file
    By d7882 in forum Excel General
    Replies: 8
    Last Post: 11-06-2017, 04:40 PM
  4. [SOLVED] Excel slow open/save i think because of hyperlinks??
    By hermarjan in forum Excel General
    Replies: 24
    Last Post: 07-14-2013, 08:36 AM
  5. my excel project is slow to open and save ,,,help plz
    By meonly123 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-02-2013, 09:00 PM
  6. my excel project is slow to open and save ,help plz
    By meonly123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-11-2013, 01:31 AM
  7. excel 2010 very slow to open/save workbook
    By bebe123 in forum Excel General
    Replies: 10
    Last Post: 01-06-2011, 11:10 AM

Tags for this Thread

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