Solved

EXCEL storage

Posted on 2002-03-13
13
157 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
 

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

Do email signature updates give you a headache?

Do you feel like you are constantly making changes to email signatures? Are the images not formatting how you want them to? Want high-quality HTML signatures on all devices, including on mobiles and Macs? Then, let Exclaimer solve all your email signature problems today.

Join & Write a Comment

Create your own, high-performance VM backup appliance by installing NAKIVO Backup & Replication directly onto a Synology NAS!
If you get continual lockouts after changing your Active Directory password, there are several possible reasons.  Two of the most common are using other devices to access your email and stored passwords in the credential manager of windows.
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…

706 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now