Irritating Issue with Excel.

Hi All,

Whenever we have to format cells(color/font) or duplicate formulas across cells, we tend to select the whole sheet or full columns/rows…Now this increases the size of the excel heavily…even if we remove the formatting from the unwanted cells, it still doesn’t help…say if you are going to use only 10,000 rows of Worksheet then there is no point in formatting rest of the rows…same goes for the column…so what can be done in such a scenario is create a new worksheet and copy the formulae/formatting in the first row/column only and then drag it to a comfortable number of cells…this definitely reduces the size of the file…though is a tiresome process.

To talk ‘statistics’, the size of worksheet was around 5MB…we did this exercise and it reduced to some 100 KB…and then it was fast…

Can anyone enlighten me why Excel behaves like this and better, any solution for the same [apart from copying the data in new worksheet and formatting carefully.]?

JD
http://jdk.phpkid.org

JD
Tuesday, January 20, 2004

If you want to set formatting (color/font etc) or copy a formula across multiple cells, then the best way is to select the specified cells before performing any operation. If you apply formatting to un-necessary cells, the size of the xls will bloat because of the extra information that needs to be stored (don't want to get into discussion about how much % is the file size bloated or how excel can optimize the data storage)

Some tips to select multiple cells (rows/columns/both) easily:

1. If all the cells are filled, then go to the first cell (top,left), press the "End" key, and then press the "->" or down arrow key to select all the columns,rows respectively.

2. If there are empty cells between the rows/columns, go to the last cell (right,bottom) and use the Ctrl+Home key to select all cells till top.

In general, experiment with the "Home" and "End" keys (with/without "Ctrl" key) to move around large tables/data.

T-90
Tuesday, January 20, 2004

Tools are easier to use if you work with them, not against them. From your description, it's clear that Excel isn't very good at handling lots of (unneeded) formatting, so the easiest solution is probably to format only the sections of the spreadsheet that you need formatted, instead of formatting whole row/columns/sheets. It may involve a change in how you think about the formatting, but in the long run it'll pay off.

Adrian Gilby
Tuesday, January 20, 2004

Hmmm, T-90 posted while I was composing my posting. He's right, you'll need to learn how to quickly select blocks of data by using the control, arrows, home and end keys. Once you can do this, it becomes easier to apply formatting to only the bits of the spreadsheet you're interested in.

Adrian Gilby
Tuesday, January 20, 2004

Yeah, I think I realized this problem.

But I have quite a few Excels which I need to clean up. :( So is there any tool/script you are aware which will handle this for me??

JD

JD
Tuesday, January 20, 2004

Sometimes "Format/Conditional Formatting" can help you.
It lets you apply formatting to cells in a selection based on criteria (can be both on cell value and on cell formula). E.g.  apply formatting only to non-empty cells.

Just me (Sir to you)
Tuesday, January 20, 2004

any tools?

the macro editor!

mb
Tuesday, January 20, 2004

Nice to see somebody else who has the same problem.

All MS has done with Excel for years is to change the buttons. Even the sample templates they put on the official Office site would disgrace a high school project.

Excel users generally occupy a position in their company that numbs any putative idea of complaining, and MS developers don't see solving bugs like this as cool.

Stephen Jones
Wednesday, January 21, 2004

>Even the sample templates they put on the official Office site would disgrace a high school project.

They're not universally bad. For example, These ones are ok:
Calendar:
http://office.microsoft.com/templates/preview.aspx?AssetID=TC060850611033&CTT=4&Origin=TC060850361033&CategoryID=CT062100861033
Business Card:
http://office.microsoft.com/templates/preview.aspx?AssetID=TC062071681033&CTT=4&Origin=TC010001411033&CategoryID=CT061994801033

SC
Wednesday, January 21, 2004

  Recent Topics

  Fog Creek Home