+ Reply to Thread
Results 1 to 10 of 10

Excel file make laptops and computer to much slow and didn't let work on any thing

  1. #1
    Registered User
    Join Date
    01-08-2019
    Location
    england
    MS-Off Ver
    2007,2010
    Posts
    5

    Excel file make laptops and computer to much slow and didn't let work on any thing

    Hello,
    I am facing a problem very much. the excel file is 17.8 MB and whenever anybody open this file and try to copy anything from one sheet to another its start iteration and take to much time some time its hag the laptop and computer and sometimes its take 10 20 minutes
    if I close this file and work on excel the everything works fine
    i upgrade excel of my users to 2007 to 2010 but still the same problem

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,340

    Re: Excel file make laptops and computer to much slow and didn't let work on any thing

    Could be lots of things. Why is the workbook so large? Do you have a lot of data? How many sheets? How many columns and rows on each sheet? Do you have formulae that are copied all the way down the sheet(s)? Do you have lots of complex formulae or Array formula? Do your formulae use full column references? Do you have lots of formatting or conditional formatting? Is there any VBA code?

    If you press Ctrl-End (on each sheet), does Excel select a cell way beyond the real data?
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    01-08-2019
    Location
    england
    MS-Off Ver
    2007,2010
    Posts
    5

    Re: Excel file make laptops and computer to much slow and didn't let work on any thing

    its budget file from the finance department and file have 30 to 31 sheets
    One sheet has 1257 rows and like 20 to 30 columns and every sheet have like 50, 200, 500 rows and like 20, 30, ten columns
    and I don't know how much formulas and complex formulas are using,
    and I also think they use different formate like on some sheets use Arial and some use Calibri

    If you press Ctrl-End (on each sheet), does Excel select a cell way beyond the real data? ( No on some sheets its goes very down)

    Thanks in advance

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,340

    Re: Excel file make laptops and computer to much slow and didn't let work on any thing

    I would start by making a copy of the workbook so you can restore it if necessary.

    Then, sheet by sheet, locate the last "real" row of data and go to the first "blank" row. Then press Ctrl-Shift-End to select all the rows to the bottom of the worksheet. The use the Delete icon on the Ribbon to delete all the selected rows. Repeat for the columns; that is, go to the right of the data and select all the "blank" columns. Delete them using the Delete icon. Save the workbook. Repeat for each sheet.

    That, hopefully, will get rid of some of the bloat.

    I would also clear any Conditional Formatting on each sheet. Maybe reinstate it later if it is important.

    You will need to look at the formulae. Perhaps post some examples in the thread so we can assess and advise.

  5. #5
    Registered User
    Join Date
    01-08-2019
    Location
    england
    MS-Off Ver
    2007,2010
    Posts
    5

    Re: Excel file make laptops and computer to much slow and didn't let work on any thing

    I do one thing, and the sheets work fine now. Excel is running fast on this file now.
    Before iterative calculation maximum iterations are 100 and now I change this to 2. Now sheets open quickly than before, and excel was not hanging.

    Is there any disadvantage of this please let me know.
    Last edited by lokomoki; 01-09-2019 at 01:18 AM.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,302

    Re: Excel file make laptops and computer to much slow and didn't let work on any thing

    Are there circular references in this sheet and iteration is enabled? What kind of problem is the circular reference trying to solve? The main disadvantage to reducing max iterations is that 2 iterations may not be enough for an iterative algorithm to converge on a solution. Long calculation times due to Excel using all 100 iterations may indicate that an iterative algorithm is oscillating or diverging -- which would indicate that whatever the circular reference/iterative calculation is set up to solve may not be resolving correctly (which could mean all kinds of wrong numbers elsewhere in the file).

    If reducing the max iterations dramatically speeds up the file, it suggests to me that you need to look at whatever circular reference/iterative calculation is causing Excel to run iterative calculations and determine why that calculation is taking so long.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  7. #7
    Registered User
    Join Date
    01-08-2019
    Location
    england
    MS-Off Ver
    2007,2010
    Posts
    5

    Re: Excel file make laptops and computer to much slow and didn't let work on any thing

    when i open the file first popup i receive

    " This workbook contains link to one or more external sources that could be unsafe
    if u trust the links, update them to get the latest date, otherwise, you can keep working with the data you have "

    and when i press update or not update or close the popup the next popup i receive

    " Careful, we found one or more circular references in your workbook that might cause your forumulas to calculate incorrectly.
    FYI: A circular refrence can be a forumula that refers to its own cell value, or refers to a cell dependent on its own cell value. "

    and then next when i open any sheet of the file and click on error checking i found many errors in all sheets.

    and when i click on circular refrence i see this

    " HIGHLIGHTS!$D$9
    HIGHLIGHTS!$D$15
    HIGHLIGHTS!$D$5 "

    i am not much expert on excel but try to figure out what is going on what is the problem
    thanks for helping

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,302

    Re: Excel file make laptops and computer to much slow and didn't let work on any thing

    I'm getting mixed messages from your post. That is the kind of warning you will get if iteration is not enabled. If iteration is not enabled, changing the max iterations should not change anything. If iteration is enabled, then changing the max iterations could easily change the amount of time the file takes to calculate.

    I'm not sure how much we can help you at this point. Is the circular reference intentional or accidental? If it is intentional, then one would need to enable iteration and look carefully at the formulas and determine why they take so long to calculate. If it is accidental, then one needs to look at the formulas and determine what was intended and break the circular reference. In either case, it will involve looking at the formulas and understanding what those formulas should be doing. I don't think we will be able to help much without a detailed description of the spreadsheet (and maybe a copy of the spreadsheet). Are you in a position to post a copy of your file (with all confidential information removed/edited?

  9. #9
    Registered User
    Join Date
    01-08-2019
    Location
    england
    MS-Off Ver
    2007,2010
    Posts
    5

    Re: Excel file make laptops and computer to much slow and didn't let work on any thing

    ops sorry.
    Because i change the iteration on another pc thats why i recieve this error i enable iteration now on my pc
    Yes when i change iteration from 100 to 2 then file work fine and its load and calculate easily

    The sheet is from finance department and they tell me their is circular refrence and many formulas because they didn't work without this
    The problem for me is to how to check which formula is creating the problem if the iteration value is 100 and when they try to take value from any sheet to past on any other sheet of the file its take to much time and maximum time file hanged and excel stop working.
    but with minize the iteration value every thing work fine now

    its more then 30 sheets in this file and all are with confidential inforamtion so its not possible for me to post this publically

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,302

    Re: Excel file make laptops and computer to much slow and didn't let work on any thing

    A difficult position to be in. Here's what I might do:

    1) Go to someone in finance and ask them to take enough time to help you understand the circular reference, how it works in the spreadsheet, why it is necessary, etc. If you are going to make any improvements to this spreadsheet, it seems to me that you must be able to understand this circular reference, and they should hopefully be able to help you understand it.
    2) Disable iteration and, when the circular reference warning comes up, take careful note of which cell(s) Excel indicates is (are) involved in the iterative calculation. Spend some time with those cells tracking down exactly how the circular logic works. In a large and complex spreadsheet, this can be difficult -- it depends on how much is involved in the circular reference.
    3) Re enable iteration with max iterations set to 1 or 2. Repeatedly press F9 and see if you can see the cells involved in the iteration changing. Are they converging on a value, or do they change wildly.
    4) Somewhere in step 2, maybe you can see how to separate the circular reference from the large spreadsheet. Extract the calculation into a new file (or build a new sheet from scratch) that contains just the iterative calculation and enough inputs to see what the calculation is doing. This will allow you to see if the iterative calculation by itself is also slow, or if it is only slow in the context of the larger spreadsheet. (In the last post of this thread, I explored a little with how "outside" calculations can dramatically slow an iterative algorithm: https://www.excelforum.com/tips-and-...ml#post4688682 ). This might also be a good starting place for a file to upload to the forum, where you can show us the circular reference and just enough dummy data to represent the rest of the large file for us to see what is going on without violating confidentiality rules.
    5) Once you understand what the circular reference is doing, spend some time with the algebra of the problem. I have seen many posts on forums like this where a problem like this could be solved by simply rearranging the problem algebraically to eliminate the circular logic. I recognize that this is sometimes not possible, but it often is.

    I am not comfortable privately receiving a file that you cannot suitably anonymize for public posting. However, those are some strategies that I might use to try to figure out what this file is doing.

+ 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. Replies: 6
    Last Post: 10-30-2017, 05:55 AM
  2. [SOLVED] Slow Work Sheet within excel file
    By Statz in forum Excel General
    Replies: 8
    Last Post: 03-10-2016, 11:39 AM
  3. Code to make an excel cell work like F2 command in Computer
    By boddulus in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-13-2015, 09:40 AM
  4. [SOLVED] Macro "save file to folder" to work on co-workers laptops
    By thecdnmole in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-15-2014, 12:08 PM
  5. Macro does not work when file is sent to another computer
    By brdstyle in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-25-2013, 01:51 AM
  6. [SOLVED] slow computer (slow clipboard) breaks my macro
    By twilsonco in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2013, 09:16 PM
  7. Strange thing only on one Computer running this XLS
    By johnnywinter in forum Excel General
    Replies: 3
    Last Post: 02-08-2009, 11:31 PM

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