Excel 2007 : Help me wrangle my Excel file into shape please! (Slow, big file, freezes)

**Kay129** · 12-14-2011, 01:58 PM

Hi,

I use Excel for a project managment purpose. I do this at the direction of my boss, so there's no chance I can change what I'm doing.

Right now I work with a 100+ page document, there are four tabs. I work in Excel 2007.

The columns include: date, three columns that I put checkmarks in (with wingdings), description/text, savings/spending.

When I go to update the worksheet, Excel takes forever. It freezes and I am forced to wait for it to unfreeze to do my task. (Changing date, adding text, adding row.) Saving takes a long time too.

I tried to fix the problem by (a) - saving as simply .xls . Instead of .xlsx. (I don't know if that was the right thing to do.)

(b) I broke out the first worksheet (100 pages) into its own workbook. I put the other three worksheets (about 40 pages total) into a separate workbook. It is the first workbook that I need to update frequently.

I think one thing that might help is if I could somehow turn off calculation in this spreadsheet. I do not need any formulas running, totaling, etc. Even though we track savings/spending, we don't track a total. If I can turn off calculation would this help? I can see formulas running in the bottom bar, it seems to slow everything down.

Any other suggestions? I have searched the web, but to be honest, I am a pretty inexperienced user and the tips and tricks I have found either don't apply or read like greek to me. I'm willing to learn though, if that's what will help.

Thanks so much for any assistance.

**TMS** · 12-14-2011, 02:08 PM

It would help if you posted a sample workbook ... obviously, not the "100 pages".

What exactly do you mean by "100 pages"? In tems of printing? How many rows? How many columns? How many formulae? What sort of formulae? Do you have lots of "blank" rows with formulae waiting for data to be input? Do you have lots of formats? Lots of conditional formatting?

You can switch off calculation in the Excel Options. But bear in mind that this will affect other workbooks that you may have open.

Regards

**Kay129** · 12-14-2011, 02:22 PM

Thanks for the quick reply.

Hmm - not sure how to upload but the document is available online at

http://www.health.ny.gov/health_care...t_workplan.xls

It is an old version but you get the idea. The sheet has 8 columns and just under 2100 rows.

There aren't any formulae that I have entered - I just figure that there is some background stuff going on since I'm in Excel. It sure seems like it. I don't want any formulae running - I just wanted everything I entered to be seen as text, and I don't want Excel to do anything but store the information I enter.

**TMS** · 12-14-2011, 04:58 PM

I can't see anything obvious. There are a number of rows and columns that are hidden but there don't appear to be any formulae in those cells.

If you do Ctrl-End on the MRT Proposals Project Workplan sheet, it goes to cell AP2116. However, the columns with data end at column M. That implies there are a lot of columns/cells with "nothing" in them ... maybe used in the past. I would select all those columns and delete them and then save the workbook

Similarly, the Completed sheet goes to AK397 with no data after column G.

The Merged sheet has 11 empty columns but not many rows.

Cancelled: data only goes to cell G16 but Ctrl-End takes you to T521.

There's no code and no hidden sheets ... therefore no formulae hiding in the background

Switching off calculation won't make any difference because, as far as I can see, there's nothing to calculate.

That said, there is some limited Conditional Formatting on most of the sheets so that *could* be a factor.

Personally, I'd switch off the "Print" view and just work with the "Normal" view but I don't know if that will make a difference.

The only recommendations are to delete the empty rows/columns and use the normal view.

Regards, TMS