Check out some similar questions!

Mohan SC · Nov 7, 2007, 10:19 PM

Hi,

I have a excel file which is shared and used by around 10 people who update in respective rows and columns assigned to them or in individual spreadsheets.

The file size is has increased to more than 50 MB and its take time to save and update.

Is there a way to reduce the same?

Please advice.

Regards,
Mohan

ScottGem · Nov 8, 2007, 07:14 AM

It would probably be better to use a database. But the only way to reduce the spreadsheet size is to delete data.

benn11 · Nov 8, 2007, 07:35 AM

I agree with Scott, It will be better to use a database because the Excel file is also limited to a number of rows, so give a suggestion to import all the data in the excel to a database

retsoksirhc · Nov 8, 2007, 08:19 AM

It sounds like you've already got Microsoft Office. If you use Access instead of Excel, you can create a database where everyone has their own table to update. Just share the database from a server, and erveryone can access it at the same time. It will update fast as well, since it only has to save NEW data, not everything that's been written there before.

It probably won't decrease the file size, but it will speed things up.

ScottGem · Nov 8, 2007, 09:24 AM

You do not need to give people their own tables. That would actually be a denormalized structure. Instead you would use filtering to show people only those records that pertain to them.

If you would like some help setting this up just let us know.

retsoksirhc · Nov 8, 2007, 12:06 PM

Originally Posted by ScottGem

you do not need to give people their own tables. That would actually be a denormalized structure. Instead you would use filtering to show people only those records that pertain to them.

If you would like some help setting this up just let us know.

It really depends on the setup of the spreadsheet. I was just assuming that since some people had their own spreadsheet in the file, that it would be easier for them to set up a table with the same fields as the columns in the spreadsheet.

What do you mean by denormalized structure? I'm not really too up on database terminology.

ScottGem · Nov 8, 2007, 12:17 PM

Originally Posted by retsoksirhc

What do you mean by denormalized structure? I'm not really too up on database terminology.

That's the key. Normalization is an essential process of designing data in a relational database. First proposed by Dr EF Codd in the early 70s, the rules of normalization have proven themselves over time.

If one is converting a spreadsheet (flat file) to a relational database (like Access), one should normalize the structure rather than just copiying the spreadsheet structure. Otherwise you lose the efficiencies of relational databases. So it doesn't depend on the setup of the spreadsheet, spreadsheets and real databases work differently, and one should NOT copy the structure of a spreadsheet into a relational database, they should normalize the data first.

If you want to learn more about normalization, I can offer some links. While my new book (Amazon.com: Microsoft Office Access 2007 VBA (Business Solutions): Books: Scott B. Diamond,Brent Spaulding) touches on normalization there are better volumes that go into it in more detail.