• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

EXCEL storage

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
GSD4ME
Asked:
GSD4ME
1 Solution
 
Kyle SchroederEndpoint EngineerCommented:
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
 
Kyle SchroederEndpoint EngineerCommented:
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
 
Kyle SchroederEndpoint EngineerCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
GSD4MEAuthor Commented:
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
 
CrazyOneCommented:
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
 
Kyle SchroederEndpoint EngineerCommented:
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
 
GSD4MEAuthor Commented:
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
 
Kyle SchroederEndpoint EngineerCommented:
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
 
CyberStretchCommented:
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
 
GSD4MEAuthor Commented:

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
 
Kyle SchroederEndpoint EngineerCommented:
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
 
GSD4MEAuthor Commented:
Just closing this one down as there has not been a definitive answer and it was really just a 'what if...' type question
0
 
Kyle SchroederEndpoint EngineerCommented:
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now