Excel help/question

[MattR]

I have a sheet that is somehow 3.98mb which seems excessive for what is on it (it has two sheets of about 800 row but mostly blank cells at this stage, and across to Y on columns) I don't have any hyperlinks on there (or if I do then it has been inadvertently - is there a way to check if the sheet has hyperlinks - certainly nothing evident, underlined etc). If it's not that what else could be causing it to be such a size? Or this size about right? It's office 2007. It's also taking an age to paste anything on the sheet and undoing a paste has caused it to hang a couple of times.

[MattR]

Just tried something that may have been the reason - though not sure why it should be that different. I had it saved in compatibility mode as it was originally also used on office 2003. I just saved it as 2007 worksheet and the size is now 218kb. Would the extra info I assume office saves to make it compatible account for that much difference??

[mathare]

Would the extra info I assume office saves to make it compatible account for that much difference??I doubt it. But saving it in a different format may have caused Excel to remeasure the size of the data on the sheet. Sometimes, especially if a lot of data has been deleted from a sheet. Excel gets a bit 'confused' as to how much of the sheet is still being used and saves the file as larger than expected. Opening and resaving without making any changes can often get Excel to work out the right file size.

Note also that if a formula is contained within a cell Excel counts that cells as used and containing data so if a s/sheet does get large check which cells contain formulae that don't need to at present.

Ctrl-End and Ctrl-Home will move to the last/first used cells so you could try and Ctrl-End to see if there is any data in any rows lower or columns to the right of where you think your data ends.

[MattR]

Thanks Mat.

[wb]

Matt, I'm probably being a bit basic here, but when you cut and paste, are you doing a 'paste special' (to 'text' for example) in order that no hyperlinks are getting through? I used to paste as normal, but discovered that the hyperlinks would still be added unless I did a paste special.

[MattR]

No Wayne, it was just pasting from another spreadsheet that had been typed in so wasn't pasted from a website. Good thought though!

[GlosRFC]

First things first...the obvious causes for a large filesize. Do you have any hidden worksheets that might've got copied across when you pasted? Check by going to Format, Sheet, and seeing if the Unhide option is available. If so, unhide any sheets and see if you still want them. Then delete any worksheets that you have included in your Workbook - Excel automatically defaults to three sheets whether you need them or not.

Using the Save As option then saving your spreadsheet as 'Microsoft Excel Workbook (*.xls)' can reduce the filesize by 50% or more. You can check the reduction by first clicking on File, Properties then looking at the Filesize entry under the General tab. Do the Save As and look at the File, Properties, General tab again.

Another cause of bloated filesize is that Excel can add false blank cells. First click on any cell then press F5. The Go To window should appear so click on Special, select the Blanks option, and click OK. Excel will now highlight all of the cells that it considers to be blank, i.e. that don't contain data/text/formulas etc. Once the blank cells have been highlighted, click Edit, Clear, All and it will delete any false blank content in the highlighted cells. Save As and check the File Properties again.

Excel also gets confused about the ranges within a spreadsheet. It's easy enough to force Excel to reset the ranges by using the CTRL-End key combination that Matt mentions. Once you get to the last used cell, click the NEXT ROW number beneath that cell in the margin. Once the entire row is highlighted, hold down the CTRL+SHIFT keys then press the Down Arrow and Excel should highlight all of the remaining unused rows. Use the Edit, Clear, All option again. Repeat the CTRL+End key combination but this time select the NEXT COLUMN letter from the top margin. CTRL+SHIFT+Right Arrow will select all the unused columns and you should use Edit, Clear, All once more. Then it's time for another Save As, and File Properties check.

Finally, you might also have copied over the formatting from the previous worksheet, i.e. cell colours, number formats, etc. Making sure that you remove all of this formatting isn't easy because there are so many options. However there are programs that can strip all of this out if you're still having problems.

[MattR]

Thanks Glos. I've found out what was causing it now and it was the formatting you mentioned. The sheet is for baseball and has a list of the teams with pr/lo for bets. I have been pasting the days bets across to a seperate spreadsheet for ease so I can play around with them and sort them etc. When I checked manage rules on the sheet in excel instead of the $g$1:$g$30 that was originally there I now had about 80 addditional conditional formatting rules! As I pasted the bet results back it must have been bringing all that with it causing the pasting to take an age and the sheet size to expand to over 3mb. I removed all conditioning on both sheets and they now come in at 258kb!

It was only a condition to highlight the cell if the value was above zero so I can live without it anyway if all the sorting is going to confuse things. Originally the main sheet was done in excel 2003 before i got a new computer with office 2007. I can't recall if the other sheet was 2003 or 2007 originally so I don't know whether this was part of the reason it all got confused. Both are now in 2007 format so perhaps if I put in new conditional formatting it won't be a problem now.