Excel Experts, Help me out

[Nebor]

I have an excel spreadsheet that's being used as a long range calendar. It's basically just dates across the top rows with events in colored boxes underneath each date. I've been using it for several years at this point. It doesn't have formulas (other than maybe to number the weeks) and it doesn't have conditional formatting. It has worked flawlessly for years, but after handing it over to my replacement, it has become so slow and processor\memory intensive as to become unuseable. He of course claims to have done nothing to it, and I can't figure out what's wrong with it, but I'd really like to recover it without having to recreate it from scratch (or from a .csv file, which is near scratch.)

So, I need ideas about what could be slowing it down would be appreciated. Alternately I would be willing to pay a small reward ($30) for someone that can fix it for me. PM me for details.

 

[AreaCode707]

Did he add formulae? Are you using conditional formatting?

 

[spacejamz]

No backup version prior to giving it to your coworker that you could just paste new updates from the current version into?

 

[Nebor]

No backup version prior to giving it to your coworker that you could just paste new updates from the current version into?

No current backups. It's normally accessed by a variety of people for updating and kept on a network drive. It's been passed from person to person for years without issue. Unfortunately it finally made it's way to someone that "doesn't know computers."

 

[Nebor]

Did he add formulae? Are you using conditional formatting?

No, he doesn't know how formulas work. And there is no conditional formatting. Just to be sure, I did a "find and select" for condition formatting and found nothing.

 

[zzuupp]

Some other thoughts:
--Was calculation automatic or manual?
--Is the Auto Save/Recover now set at something like 1 minute?
--Has circular references iteration been turned on, and to a high level?

 

[Nebor]

Some other thoughts:
--Was calculation automatic or manual?
--Is the Auto Save/Recover now set at something like 1 minute?
--Has circular references iteration been turned on, and to a high level?

I tried a variety of solutions, like writing a VB script to clear our conditional formatting and formulas, changing calculation to manual, deleting historical data... None of it had any effect. This thing literally went from fluid to unuseable nearly overnight. I just can't figure out what caused it.

 

[LurkerPrime]

If he's added any type of data access (from access, website, SQL, etc...) that shit can really put a damper on doing anything. Those querries are usually run when you first start up the file and it wont let you do shit until they are done running. Whats the size of the file?

 

[AreaCode707]

Is he storing it on a share drive or other location that may be really slow to save to? Did he change the file to be "shared" (in Review options in Excel 2007)

 

[Nebor]

The file is only 1.8mb in size, but takes approximately 500MB of memory once it's started, and consumes 100% of processing power on my E8600. There shouldn't be any data access, unless someone added it without me being able to find it. It is stored on a share drive, but I've been trying to fix it on my local machine and it's no better.

 

[spacejamz]

No current backups. It's normally accessed by a variety of people for updating and kept on a network drive. It's been passed from person to person for years without issue. Unfortunately it finally made it's way to someone that "doesn't know computers."

When you do you first notice the performance issue? You can't get a backup from the network prior to that date?

Once you have this back up version, just paste in data from the current spreadsheet (the messed up one) into the back up version to bring it current. You might want to paste in portions at a time to see if the section might have had the cause of the performance issue...

 

[AreaCode707]

If you take a copy and delete all the data out of it, completely, just leaving the formatting, do you still have the issue?

 

[LurkerPrime]

One thing to try is to just ctril-a then copy all the data from your spreadsheet. Then open a new one and paste it in. Save the new one and close and open it to see if the problem continues. The old file could be corrupted in some way causing the issues.

 

[kranky]

I had one like that and this is how I fixed it. Never did figure out what caused it, though, but it had something to do with the rows and columns we weren't even using.

Copy worksheet to new workbook.
Select then delete every unused column.
Select then delete every unused row.
Save as.

At this point things worked normally.

 

[DeviousTrap]

One thing to try is to just ctril-a then copy all the data from your spreadsheet. Then open a new one and paste it in. Save the new one and close and open it to see if the problem continues. The old file could be corrupted in some way causing the issues.

The better way would be to copy and paste special just the values and the formulas. But if this is a calendar, there will probably be merged cells which makes this a little bit more difficult.

I asked the OP to send me the file, but my work computer only has Excel 2003 and it's an xlsx. The converter tried to open it in Excel 03, but used 500mb ram and then crashed

 

[JulesMaximus]

I have a file I work in daily that holds our entire item master which is over 260,000 rows and 27 columns of data. It is 57MB and takes me about 20 seconds to open and save and a couple minutes for it to recalculate something like a row deletion in the working tab. The working tab has over 3000 rows and uses vlookups across several columns to return data. I use it for cost/list price analysis.

This is on a 3GHz Pentium D w/Windows 7, with 4GB of RAM running Excel 2010.

Your sheet doesn't sound anywhere near that complicated so I'm unsure what could cause you the slow down. Is it possible his computer has a virus or malware that has been spread through your network?

 

[LurkerPrime]

Another thing to try is to go to excel options -> trust center -> macro settings and then disable all macros. At that point try to open the file and see if it gives you any problems. Seems alot like a good old infinite loop macro running at start up.

 

[AreaCode707]

I had one like that and this is how I fixed it. Never did figure out what caused it, though, but it had something to do with the rows and columns we weren't even using.

Copy worksheet to new workbook.
Select then delete every unused column.
Select then delete every unused row.
Save as.

At this point things worked normally.

That was my next question - Excel likes to guess at which columns and rows you are using, defining your "list". If it guesses wrong and thinks that you are using way more rows than you actually are, it can eat up resources.

The easiest way to fix this, in my mind, is copy JUST the content you have on the sheet, put it on a new spreadsheet, and start with that.

 

[Nebor]

If you take a copy and delete all the data out of it, completely, just leaving the formatting, do you still have the issue?

I tried this, and it is still slow, so presumably the problem is the formatting. Similarly, I tried saving the data as .csv and that works fine. But since, as a calendar, much of the work is in the formatting (merged cells, colors, borders) the formatting is important.

 

[LurkerPrime]

I would like to know if any of my suggestions work so I can collect on that reward. Also feel free to send me the file.

 

[Nebor]

One thing to try is to just ctril-a then copy all the data from your spreadsheet. Then open a new one and paste it in. Save the new one and close and open it to see if the problem continues. The old file could be corrupted in some way causing the issues.

Tried this. It takes about 30 minutes to complete, and it doesn't work.

 

[AreaCode707]

I tried this, and it is still slow, so presumably the problem is the formatting. Similarly, I tried saving the data as .csv and that works fine. But since, as a calendar, much of the work is in the formatting (merged cells, colors, borders) the formatting is important.

Go to the last row at the bottom of column A. Hold down Ctrl and hit the down arrow. In which cell does the cursor stop? Also, how far down does your formatting go?

 

[darkewaffle]

Check formula view, ctrl + ` (tilde key), and see if there's anything out of the ordinary floating around behind the scenes?

 

[sdifox]

err how many columns?

 

[Nebor]

Go to the last row at the bottom of column A. Hold down Ctrl and hit the down arrow. In which cell does the cursor stop? Also, how far down does your formatting go?

I think you may be on to something. It goes to row 1048576. I only have ~220 rows of used space. But when I try to delete the rows between the last used row and row 1048576, it doesn't do anything. They don't go away. Unless I scroll all the way to the top of the spreadsheet, in which case those lower rows become "hidden" and the scroll bar returns to a normal size.