Solved

EXCEL storage

Posted on 2002-03-13
13
161 Views
Last Modified: 2013-12-29
A general question:

What is the most efficient way of storing data in EXCEL?
ie what takes up most (disk) space in an EXCEL spreadsheet? - colour of cells? strings? formulae?

So if I have 2 cells that will contain the same data is it better to have cell1 hold the (eg) string and have cell2 containing a formula saying that it is the same as cell1? Or is it better to hold the string twice, once in each cell?
Is it better to have coloured formats for easy reading of data or does that take up space so it is more efficient (ie smaller disk space) to hold the data without coloured strings, borders etc but make th data harder to pick out?

0
Comment
Question by:GSD4ME
13 Comments
 
LVL 16

Expert Comment

by:Kyle Schroeder
ID: 6860924
There is no difference at all as to what cell you put the formula in.  I just did a test and there is no difference in cell formatting (color, font, etc).  The file I made had 1 in A1 and =A1*5 in B1.  Saving this file in Excel 2000 format gave me a 14KB file, which is certainly more than the small amount of data.  The Excel files have alot of overhead.

-dog*
0
 
LVL 16

Expert Comment

by:Kyle Schroeder
ID: 6860929
However, Excel files do compress very well, I zipped the file and got a 1.32KB file, less than 10% of the original.

-dog*
0
 
LVL 16

Expert Comment

by:Kyle Schroeder
ID: 6860939
In any case....storage is cheap (~$2/gig) so why concern yourself unless you are going to be storing huge amounts of data.  Also, to the best of my knowledge graphs/charts don't make much difference either as long as they are stored in the file as charts and not converted to images.

-dog*
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:GSD4ME
ID: 6861062
Reason why I am asking is because I have a file holding data that is growing (up to 1.2Mb) now and its predecessor went to 8Mb when all that was stored was coloured formats and a lot of strings/numbers in cells. Guess it might be strings that take up the space.
So  - is it better to store numbers as strings or numbers? My guess would be the latter, making the storage space smaller.
I need to transport the files between my laptop and my desktop and it takes a while!
0
 
LVL 44

Expert Comment

by:CrazyOne
ID: 6861662
If you don't specifically set the size of the the string for the column or cell then it assumes either 255 or 64K I really can't remember for sure. Numbers generally speaking usually take less space if the cell is formatted as a number and not a string. Also using background colors will cause a file to balloon up in size. If background colors are needed then just use the very basic of colors like yellow, red , light blue, light green, gray, silver because these colors don't carry as many pixel layers and take up less memory and disk space.  


The Crazy One
0
 
LVL 16

Expert Comment

by:Kyle Schroeder
ID: 6861664
Hmm...well in that case it probably would be better to set as numbers, which of course makes it much easier to do calculations also!  The cell formatting shouldn't make much (if any) difference at all.  How are you transferring the file from laptop to desktop? Serial/parallel cable?  FTP? In any case, you'll save some time by installing WinZip (www.winzip.com) on both machines and "Zipping" the file before copying it, then copy, then open the Zip file, copy the Excel file out, do work on it, then when you're done working on it, overwrite the Excel file in the .Zip file with the one you've updated.

-dog*
0
 

Author Comment

by:GSD4ME
ID: 6863870
I did an experiment - set 300 cells equal to "string".
That took up 14K of disk space.
Did the same thing but in a different way - set cel(1,1) = "string" and then used formulae to set the other 299 cells equal to the value in cell(1,1). That took 25K!!!
surprising!

To transfer files I have found a better way - laplink cable and using Windows Commander. Takes about 2 minutes to transfer about 8Mb along the cable.
0
 
LVL 16

Expert Comment

by:Kyle Schroeder
ID: 6865227
If you zipped the file, it would probably take ~30 seconds or less.  The reason that the 300 cells with the formula may be because the formula is larger than the string.

The big problem here is Microsoft doesn't concern themselves with file size for the most part (though Office 2000 did improve it in general, especially for PowerPoint files, or any other file with embedded graphics).

Saving the file in an older Excel version (95, 4.0) may help also, assuming that you just have basic data and no complicated Excel functions being used.

-dog*
0
 
LVL 3

Expert Comment

by:CyberStretch
ID: 6865398
I tend to agree with dogztar. Also, there is a command line add-on for Winzip (http://winzip.com/wzcline.htm) that will allow you to automate the process using batch or script files, presuming that the filename remains constant or it is the only .xls file in the directory.

Another option would be to try Windows Briefcase. IIRC, the only thing it does is synchronize the data from one system to the other. Therefore, it will only copy the changes from one copy to the other vs having to copy the whole file.
0
 

Author Comment

by:GSD4ME
ID: 6876262

There must be something funny about the way the data is stored - linked lists or something.
I set a border colour for a column (all 65536 rows of them) and stored the file - lots of Kb.
I thne cleared the border from about 2000 rows (leaving the first 100) - and the file got bigger!
The more I deleted, the bigger the file got. It was only when I cleared everything to the end (leaving the first 100 rows' worth) that the file became very small again.

Transferring data is not really the problem as I am happy with Commander - it was a simple question about how Execl stores data to make files grow so big
0
 
LVL 16

Accepted Solution

by:
Kyle Schroeder earned 50 total points
ID: 6880572
I believe part of the problem is that Excel doesn't just save the current state of the file, it also puts in some previous file version information and other data.  I don't know that there is a way to remove this information, though as I suggested before you could try down-version saving to Excel 5.0 or something else.  You'll lose formatting information however.

-dog*
0
 

Author Comment

by:GSD4ME
ID: 6893482
Just closing this one down as there has not been a definitive answer and it was really just a 'what if...' type question
0
 
LVL 16

Expert Comment

by:Kyle Schroeder
ID: 6894126
Sorry we couldn't provide a more definitive answer...I think the only source for this information (with a definite reason) would be Microsoft.  In any case, it was an interesting question.

-dog*
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Microsoft will be releasing the Windows 10 Creators Update in just a matter of weeks. Are you prepared? Follow these steps to ensure everything goes smoothly and you don't lose valuable data on your PC.
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question