I have an Excel 2007 workbook (.XLSX).
It has about 6 tabs (sheets).
One of the tabs (only one) has begun responding VERY slowly (like 60 seconds) to simple actions. For example, copying and pasting a row takes forever!!!
All of the other sheets are behaving normally (fast).
The sheet has only about 40 rows and maybe 12 columns.
For example, after I Copy (CTRL-C) a row, and press Paste (CTRL-V) it takes like 60 seconds! All during the wait, the Disk Drive light is ON.
It also takes forever to delete a row. The other sheets in this workbook are normal.
--------------------------
How can I either fix the problem OR figure out WHY it is taking so long. Is there a "what is it doing" tool?
This has become a serious annoyance.
Thank you.
BTW: I am running XP-Pro and Office 2007.
What I think is an important clue is that it doesn't go SLOW on the other sheets in this same workbook. And because this sheet is not so big, debugging could be easier.
I wonder if there isn't some sort of (unknown) link in one of the cells of the sheet.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
One more thought. You asked if there is a "What is it doing tool?" I use ProcMon (process monitor) to do this and it's been very helpful in the past. One of the many great tools made by Sysinternals.
http://technet.microsoft.c
Let me know if you want tips on how to use ProcMon.
Here's a list prepared by fanpages to minimise file size and speed up a workbook. Some of the actions he recommends should help.
http://www.experts-ex
To reduce the overall size of an Excel workbook, you may like to try one/more of these suggestions:
* Don't save as dual format file (Excel 95/97); use the most recent version of Excel you (and your intended) audience will have access to - for instance, "Excel 2002", or "Excel 2000"? However, save in the oldest copy of MS-Excel you can, then re-save in the latest revision you can. Doing this may reduce overall size.
* If you notice that the vertical or horizontal scroll-bar(s) go(es) past the end of your data, you can clear the blank rows & columns beyond the extent of your data:
a) Go to the bottom row of data & then select the entire next row by clicking on the row number (in the "margin"). Use [Ctrl]+[Shift]+[Down Arrow] to select all the remaining rows in the worksheet, then click the "Edit" / "Clear" menu item to clear the rows of values & formats.
b) Go to the far right column of data & select the entire next column by clicking on the column letter. Use [Ctrl]+[Shift]+[Right Arrow] to select all the remaining columns in the worksheet. As before, use the "Edit" / "Clear" menu item to clear the columns of values & formats.
Repeat the above steps for each worksheet in your workbook where you can scroll past the end of your data. Finally, save the workbook, and close it. Upon re-opening the scroll-bars should now extend only as far as the data.
* Format your cells in continuous ranges (blocks) rather than individually setting the formats of distinct cells. For example, if all of row 1 is "size 12, arial, bold" do not set A1, B1, C1, D1, E1 (etc) separately, just select row 1 & apply the same style to each cell at once. Formatting in this manner can also be used with colo[u]rs (cell foreground & background), fonts, font sizes, font styles. Try to apply the same formatting to single or multiple columns, and/or rows, and make individual cells the exceptions, rather than apply formatting to individual cells one by one.
* Refresh any queries or pivot tables with (select critieria that references) the least amount of data possible.
* Research (via Help) & use inbuilt bespoke functions rather than writing your own code.
* Reduce the length of Range names so that they are still meaningful, but not as verbose.
* If you have any (auto) filtering enabled in your workbook, across one, or more, worksheets, remove this, or reset it back to "(All)" prior to saving.
* Remove any unused worksheets, or any unused VBA code (or pre-recorded macros).
* You may like to try exporting (right-click tab and select "Copy To" 'New Book') all your worksheets into a new workbook & then saving this newly created book as a different filename. When comparing the two files you may notice a vast difference.
* If you regularly save data across a network (i.e. is the workbook stored on a network), or is it 'Shared'? If 'Shared', try unsharing & saving locally, re-share & then save back to the network.
Update:
As you may recall, my complaint is that one sheet in my Excel-2007 workbook is DOG SLOW. I mean SLOW!
So, I tried this: I saved each sheet into it's own workbook. So I now have 5 workbooks with ONE sheet each. I tried the bad sheet in it's new workbook and it appeared to be fixed. But today, I tried it again and it is S-L-O-W, I mean horrendous.
I have a few theories / ideas. What do you think?
1. I should try saving it in Excel-2003 format, not Excel-2007.
2. Somehow there is an (not-yet-understood-by-me but BAD) interaction between Excel and Internet Explorer.
3. Somehow it is referencing some cell way down or way-right in the sheet. This is a sheet with only 57 rows and about 12 columns. Is there away to tell Excel to WIPE / IGNORE everything beyond my actual 57x10 data?
4. There are references to other sheets in my bad sheet. How can I locate them? Is there a way to tell Excel "show me my external references?" I should note that there was a button at top-left that said it was blocking access to something (for my protection). I clicked it said Allow. I think it was blocking access to my disk drive - not sure.
5. The problem probably began when I created the new tabs (worksheets) in the original database. There are various ways to create a new tab. I may have highlighted my data grid in the old sheet and pasted those cells into the new sheet. If I recall correctly, the paste took FOREVER. So, that's any area of suspiciion.
Truth be told, I have not yet tried the suggestions in PatrickAB's last post. I didn't have a chance. I'm going to try them ASAP. thanks.
DFlaschen,
> Is there away to tell Excel to WIPE / IGNORE everything beyond my actual 57x10 data?
Yes, I have covered that in my previous comments - see above
>4. There are references to other sheets in my bad sheet. How can I locate them? Is there a way to tell Excel "show me my external references?"
You can switch between results (normal view) and the formula in a cell. To do that use CTRL+¬ or CTRL+~ The first combination works on my machine.
Patrick
This may be hard to believe, but trying a and b above seems to have made is worse! And, because I am using Excel 2007, there is no "Edit / Clear". Instead I right-click'ed and did Clear Contents.
I should note that I use colors a lot in my spreadsheets. Although I cleared the contents, the colors remained. Perhaps I need use the Format Painter to make the non-data cells white. How can I do that? Thanks.
Try these modified instructions instead:
a) Go to row after the bottom row of data & then select the entire next row by clicking on the row number (in the "margin"). Use [Ctrl]+[Shift]+[Down Arrow] to select all the remaining rows in the worksheet, then press CTRL+- (that's hold down CTRL and press the minus sign once)
b) Go to the column after the last column of data & select the entire next column by clicking on the column letter. Use [Ctrl]+[Shift]+[Right Arrow] to select all the remaining columns in the worksheet, then press CTRL+- (that's hold down CTRL and press the minus sign once)
NEW INFORMATION
I've spent a lot of time testing. Here's what I found:
I deleted ALL of the data - all rows and all columns - and it still is VERY, VERY slow.
After deleting everything, I entered one cell. I then copied that row and it STILL took forever.
WEIRD FINDING:
I discovered there is no A and B columns. I did UNHIDE, I scrolled left. Nothing. The leftmost column is column C. I can't find any A or B columns!!
There are also NO H, I, K or L columns. How can I get these columns back. I am thinking this is related to my slowness The unhide button is disabled.
REMEMBER please: This is is Excel 2007 Thanks.
Summary: All the (unhidden) data is cleared. It is still horribly slow.
THere are (apparently) hidden columns, which I haven't been able to unhide.
thanks
I guess I'm not the only person who can't unhide columns in Excel 2007:
http://www.google.com/sear
Patrick, your response is funny, but I think you mean it. Do you mean it? Are you saying what you mean, that the sheet is just "somehow screwed up" and there's "no fixing it." I'd bet Bill Gates would be surprised to learn that Excel 2007 could do that.
By the way, what is the proper (best) way to copy a sheet? There are several possibilities: Right click on the tab and choose Move or Copy. Highlight the whole sheet by clicking on the little empty box above the column numbers and to the left of the row numbers and highlighting active cells and copy those using Cut and Paste.
I am guessing that these REALLY AGGRAVATING problems began because I used the wrong copy mechanism. The source of copy worked fine.
Thank you.
One option to download OpenOffice and open the spreadsheet and resave it in OpenOffice. Then open it back in Excel. That may strip out some of the sicknesses (aka Ebola).
http://www.openoffice.org/
DFlaschen,
>Are you saying what you mean, that the sheet is just "somehow screwed up" and there's "no fixing it."
I don't think Bill Gates would be at all surprised...
Alternatively you could try opening and reparing. When you get to the dialogue box in which you select the file to open, you will see at the righthand end of the 'Open' button a dropdown arrow, select that and then select 'Open and Repair'. You never know it might help. Certainly worth a try. It's also worth trying tenaj-207's suggestion as Open Office is free and it sometimes helps.
Patrick
Patrick wrote:
I've had a look at the 'sick' spreadsheet and have come to the conclusion that it is terminally ill with Ebola virus. There no hope for it. Give it up and start all over again.
Indeed. A simple spreadsheet from a $500 program just won't work and it can't be fixed. I guess it's easier to complain than do better, but this was a very disturbing incident. What did I learn? "You can destroy a MS Office 2007 without doing anything wrong!"
SOLUTION: I copied the sheet cell-by-cell to an OpenOffice spreadsheet. It works fine there.
At one time I knew how to read the byte-by-byte format of an Excel spreadsheet - very complicated. If I had loads of money and time, I'd like to analyze this sheet (you can do it - the sheet is attached). And find out what's wrong. The attached sheet has like 2 rows and 10 cells. It still takes 120 seconds to copy a row.
Thanks so much to PatrickAB and Tanaj-207.
Pray for a 2009 where people respect each other and don't kill each other all the time. Peace bros.
Business Accounts
Answer for Membership
by: tenaj-207Posted on 2008-11-10 at 21:12:23ID: 22927886
I think you're on the right track. I recently had a similar issue and it ended up there was data on the last field of the row, which caused some wired issues.
I would create a new tab then copy all of your information to that new tab, don't use ctrl+A, click in one corner then go to the other and hold shift and click corner. That should select just the data you are using.
If that doesn't work then post back what you're doing with this page and if it links to any other tabs or documents.