?
Solved

Microsoft Excel Not Responding

Posted on 2011-04-28
23
Medium Priority
?
701 Views
Last Modified: 2012-05-11
I created a spreadsheet approximately 10 years ago using excel 97 to keep track of employee 401(k) contributions and employer match.  The format has changed somewhat over the years but the general layout is the same.  There are 13 sheets or tabs, one for each month, and a summary sheet that accumulates YTD contributions, match, and total remittance.   In the beginning we remitted payments to the Trustee once a month so entries were made for the employees as remittances were required.  We also used the totals generated to develop entries to our accounting records.   As the plan grew, requirements for remitting payments to the trustee increased from monthly to weekly  so additional functionality was built into the spreadsheet to accommodate separation of weekly amounts on each monthly sheet while documenting the weekly accounting entries and still allowing YTD amounts to accumulate.  2 macros were added to allow this last bit of functionality.   The spreadsheet worked very well for several years until we upgraded to Office 2007.  The first time that I accessed the file after the upgrade I noticed that it took an excessive amount of time to load.  Data entry in a given cell could take anywhere from immediate up to several seconds depending on which monthly sheet was being worked on.  The months of April, July, September, and November are especially troublesome.   Attempting to run a macro on any of the trouble month sheets could make excel stop working completely.   Copying and pasting data can sometimes take 3-4 minutes to accomplish, that is when it doesn’t make Excel stop responding all together.  When these problems started I would borrow another workstation that was running an earlier version of excel and this worked for a while.  Eventually however, the same problems were encountered with the earlier version of excel.  

I am currently running Microsoft Windows 7 Professional and Office 2010 on an HP Elite 7000 PC with 4GB of Ram.  The processor is an Intel Core i7 860.

I’ve been using Excel for approximately 15 years and Lotus 123 for another 15 years prior, so I consider myself quite experienced with spreadsheets.  I am an accountant not a programmer and I’m certain that there are more efficient and competent users than I.   But I’ve never experienced this kind of performance degradation.  This has become a very frustrating and time consuming issue during certain months (trouble months above) and I get angrier with Microsoft every time I have to deal with it.  I have attempted many things to try to alleviate the problem but to no avail.  I’ve set recalculation to manual, I’ve deleted certain “If” formulas thinking that these were slowing down performance, and I’ve converted the file to an excel macro file format.   Nothing that I’ve tried seems to help.

Can someone offer some help?  I can submit a version of the spreadsheet that has been edited to remove SS#’s, names, etc.  for review.  
0
Comment
Question by:tncraig
  • 9
  • 8
  • 6
23 Comments
 
LVL 5

Expert Comment

by:jason987
ID: 35486161
Definitely need more information.  A good start would be to export the macros and attach them.  

0
 

Author Comment

by:tncraig
ID: 35486674
Jason987 - thanks for your suggestion.  I have tried this previously with limited success.  It does improve performance somewhat but not dramatically.  Open the attached and select the "Apr" sheet to get an idea of how much time it takes to actually open up.  Then try selecting "Aug" and watch it get worse. 401K-sample.xlsm
0
 
LVL 5

Expert Comment

by:jason987
ID: 35486864
Wow, you weren't kidding.  3 minutes in and still loading.  :)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 5

Expert Comment

by:jason987
ID: 35486920
First things first:

Sub calcoff()
Application.Calculation = xlCalculationManual
End Sub

Sub calcon()
Application.Calculation = xlCalculationAutomatic
End Sub

Add those to your modules and run calcoff if you want to work with the file without pulling out your hair. That will turn off the calculations but remember to turn them back on for current data.

0
 

Author Comment

by:tncraig
ID: 35487086
Jason987 - thanks for another good suggestion.  After adding the modules and running "calcoff" I attempted to copy information from a group of cells and paste to another part of the worksheet (on "Apr" tab).  After waiting for 4 minutes for excel to complete the operation I gave up and tried to close the file.  As has happened so many times excel locks up and will not close normally.  It will only close through Task manager - end task.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35487098
It's not the calculations. Something else is wrong. Not sure what but I'm looking...

Kevin
0
 
LVL 5

Assisted Solution

by:jason987
jason987 earned 664 total points
ID: 35487124
Yea, it's using 1 gig of memory so I am thinking one/some/all sheets have that issue where *all* of the possible cells were formatted so it is using the millions of cells.  

Hard to nail down because it's grinding everything to a halt  :)
0
 

Author Comment

by:tncraig
ID: 35487186
I certainly didn't format all possible cells but I agree it makes you think that calculations are going on in every cell.  And since problems didn't start until upgrading to Excel 2007 which dramatically increased the # of cells available maybe this is a clue.  But jason987 you're right, it is hard to nail down because the file either is so slow responding or it just locks up.

Thanks for taking a look zorvek.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35487208
Dang! This is one messed up workbook! Still messing with the damn thing...
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35487218
Oh oh...my laptop just caught on fire!

kidding...

The fan is spinning pretty good though.
0
 

Author Comment

by:tncraig
ID: 35487237
Kevin - believe me, I have been dealing with this craziness for a couple of years.  I can believe a fire!!
0
 
LVL 5

Expert Comment

by:jason987
ID: 35487246
Lol, I had to reboot because ti was making everything swap.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35487247
I'm moving over to my Cray...I wonder if Watson can do this...
0
 

Author Comment

by:tncraig
ID: 35487265
You guys are making me feel so much better!  I have to go home now but much appreciate everyone who is trying to help.  
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35487277
You can't go home! I've put my life on hold for you!

No worries...this is now a challenge...
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35487291
I saved as an Excel 2003 file - 63MB. God help me.
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 1336 total points
ID: 35487451
OK, here's the deal. The format tables were pretty whacked which cause the excessive bloat and performance issues. There is really no way to salvage the formatting so I had Excel repair the file and remove ALL formatting. The values and formulas remain intact and the workbook size is now about 400K.

This is a reasonably sized workbook and you should be able to manually apply the formatting in an hour or two.

I have attached the repaired workbook.

Kevin
401K-sample-V2.xls
0
 

Author Comment

by:tncraig
ID: 35488187
Kevin - what a sense of humor!  I've gone absolutely insane over this thing and you are making me lol.  Plus looks like you have solved it in about a nanosecond relative to the time I have spent on it.  I'll have to wait till tomorrow however to actually try it out.  Thank you!!

Chuck
0
 
LVL 5

Expert Comment

by:jason987
ID: 35488523
Just in case you were wondering I tried conversions, cut&paste, and a ton of everything else and zorvek has your solution.  There's something epically wrong with that file.  It's actually pretty impressive!  Put it on a thumb drive and hang it on your wall.  :)
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35488546
Oh Gawd no! Burn it! Bury it! Put it in concrete and toss it in the Hudson. Send it into space on the next (last) space shuttle and jettison it toward the sun.

My laptop is all crispy and covered in that fire extinguisher foam shit. The fan busted out of it's seat, limped out on to the balcony, and jumped.

Oh, wait, I know:

   EdTheExcelPM@microsoft.com
0
 

Author Comment

by:tncraig
ID: 35491933
Jason and Kevin - I would like to take all of your suggestions, ie: burn it, bury it, nail it to the wall because I'm completely fed up with it.  I see that the sample file that Kevin saved as a .xls file does solve the issue, however unfortunately I now need to recreate what Kevin did to my actual file.  I've spent the last 2 hours (with interruptions) trying to do just that.  I finally got the reformatted (whoops - the unformatted) version to save as a.xls file and low and behold it is not 403KB but over 89mb.  When I look at the file, although it says it is a .xls file it still has over 16k cols and 1mm rows.  Is there a magic trick to get Excel 2010 to actually save a file in a previous file format other than taking "save as" and selecting excel 97-2003 format?  

I'm completely whipped!!
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 1336 total points
ID: 35495792
Probably the easiest way to remove all formatting is to copy all 13 sheets to a new workbook. To do a specific sheet, add a new sheet to the new workbook (do not copy the sheet by right-clicking on the tab). Select the source data and press CTRL+C. Switch to the new sheet, select cell A1, and, from the Home tab, click the Paste drop down, and select Formulas. DO NOT select anything with "Formatting" in the name. Repeat for the rest of the sheets and you will have a new workbook without any of the bad formatting tables but all of the data and formulas.

Kevin
0
 

Author Closing Comment

by:tncraig
ID: 35504614
Kevin and Jason - Thank you for working on this.  I wish I could give you both a few million points just for looking.  Kevin your solution was right on! Jason thanks for getting us going in the right direction.  

Chuck
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question