How to make Workbooks smaller / Excel performance better

I have a workbook that is accessed by a lot of users using different
spec machines. The workbook is periodically updated with data added
from different sources in an automated fashion using macros.

Of late the file has grown from 23megs to a whopping 93megs, although
as far as I can tell the new worksheets and charts added should
*logically* constitute no more than a 20% increase in size from the
previous incarnation, and anyway most of the data is stored as values
(i.e. formulas removed after processing).

This growth in file size has resulted in increased loading times and
system slowdowns whenever the workbook is accessed.

Note that one thing that I have done recently is move a handful of
charts so that they are located inside worksheets rather than in their
own tabs. Could this have had such a marked effect on increasing the
size?

My questions are:
(1) Is there anything I can do to make the file smaller
(2) Why has the file grown so big all of a sudden
(3) What can I do to improve Excel's performance? I have 2 gigs of ram
on my machine and a really fast processor and still suffer from
annoying slowdowns when working with big files (and am pretty sure I do
not have viruses/spyware on my machine)

Any thoughts would be extremely appreciated!

0
12/21/2005 6:32:00 PM
excel 39879 articles. 2 followers. Follow

4 Replies
637 Views

Similar Articles

[PageSpeed] 41

Excel has a problem with determining the range, call it used range, in a 
sheet.  For instance, do this:
Open a new blank file.
Type something in cell E10.
Select A1.
Now do Ctrl - End.  This should take you to the last cell in the used range.
Note that Excel selects E10.  Good.
Now clear E10.
Select C5 and type something in that cell.
Select A1.
Do Ctrl - End again.
Notice that Excel selects E10.
You know the last cell in the used range is C5.  Excel thinks it's E10.
The size of the file is due, in part, to what Excel thinks is the used 
range.
The above example is over a small range.  If the above were to happen over 
thousands of rows and hundreds of columns, the difference in the size of the 
file would be significant.  Even huge.
Go to each of the sheets in this file you have and do Ctrl-End and see how 
Excel's idea of the used range and your idea of it differ.
With the many users of the file and many sources of data coming into the 
file that you describe, this may be your problem.
What you have to do, if this is a problem, is delete all the columns after 
the real last column and the same with the rows.  Then save the file, close 
the file, reopen the file, and see what you have in size.
There are ways to automate this process of resetting the used range if your 
file has many sheets.  Post back with what you find after you follow the 
above suggestion and if you need more.   HTH   Otto
<samer.kurdi@gmail.com> wrote in message 
news:1135189920.727278.307120@g14g2000cwa.googlegroups.com...
>I have a workbook that is accessed by a lot of users using different
> spec machines. The workbook is periodically updated with data added
> from different sources in an automated fashion using macros.
>
> Of late the file has grown from 23megs to a whopping 93megs, although
> as far as I can tell the new worksheets and charts added should
> *logically* constitute no more than a 20% increase in size from the
> previous incarnation, and anyway most of the data is stored as values
> (i.e. formulas removed after processing).
>
> This growth in file size has resulted in increased loading times and
> system slowdowns whenever the workbook is accessed.
>
> Note that one thing that I have done recently is move a handful of
> charts so that they are located inside worksheets rather than in their
> own tabs. Could this have had such a marked effect on increasing the
> size?
>
> My questions are:
> (1) Is there anything I can do to make the file smaller
> (2) Why has the file grown so big all of a sudden
> (3) What can I do to improve Excel's performance? I have 2 gigs of ram
> on my machine and a really fast processor and still suffer from
> annoying slowdowns when working with big files (and am pretty sure I do
> not have viruses/spyware on my machine)
>
> Any thoughts would be extremely appreciated!
> 


0
ottokmnop (389)
12/22/2005 2:32:36 AM
Thanks Otto, for the tip...

You're quite right... Excel thinks many of my sheets end at column IV,
and thinks most of them end about 100 columns to the right of where
they actually do end. It also adds a few thousand rows on some
worksheets for good measure.

Moreover, even after I go in and select then delete the rows and
columns it still doesn't redefine its idea of where the sheet ends, and
pressing ctrl+end sends me back to the exact same ending cell every
time no matter how many times I try to delete rows/columns... Please
Help!!

0
12/22/2005 5:22:41 PM
samer

After the deletion of rows and columns you must save the workbook for changes
to become apparent.

In some versions of Excel a save/close/re-open is necessary.


Gord Dibben Excel MVP

On 22 Dec 2005 09:22:41 -0800, samer.kurdi@gmail.com wrote:

>Thanks Otto, for the tip...
>
>You're quite right... Excel thinks many of my sheets end at column IV,
>and thinks most of them end about 100 columns to the right of where
>they actually do end. It also adds a few thousand rows on some
>worksheets for good measure.
>
>Moreover, even after I go in and select then delete the rows and
>columns it still doesn't redefine its idea of where the sheet ends, and
>pressing ctrl+end sends me back to the exact same ending cell every
>time no matter how many times I try to delete rows/columns... Please
>Help!!
0
Gord
12/22/2005 7:03:41 PM
Wow.... I was able to cut down the file size by way more than half,
back to a sensible 29 megs. Thanks a whole bunch!!

0
12/22/2005 11:40:58 PM
Reply:

Similar Artilces:

How to synchronize data edited from excel to sql server
I am using excel 2003, my project asks me to do the data analysis on Excel and update the change to sql server. How would i update those info's/ synchronize those info's? I appreciate all helps :D ...

I cannot load the Analysis Toolpak in Excel 2003 like Microsoft H.
I need the Data Analysis command which is supposed to be in the tools pull-down menu. It wasn't there. I then tried to load the Analysis Toolpak multiple times with no success. Does anyone know how to get this done? Try Tools | Add-Ins | Analysis Toolpak This will put Data Analysis in the Tools Menu. tj "Frustrated student" wrote: > I need the Data Analysis command which is supposed to be in the tools > pull-down menu. It wasn't there. I then tried to load the Analysis Toolpak > multiple times with no success. Does anyone know how to get this done? If...

workbook duplication #2
how do I duplicate a workbook with formulas so that it can be used for a different application, with the same formulas? i have saved and reentered data, however it keeps linking back to the original and data gets changed automatically. i have clicked on do not update. thanks, bruce Look at Edit/Links. Change the source for the new workbook as appropriate. On Tue, 12 Oct 2004 07:07:04 -0700, becamp <becamp@discussions.microsoft.com> wrote: >how do I duplicate a workbook with formulas so that it can be used for a >different application, with the same formulas? >i have saved ...

Does Excel Support the VB List Class?
I have one quick question. Does Excel support vb Lists? I wanted to add some data to a vb list via the list.add method, but excel does not support the format dim Mylist as list(of double) Does this mean I have to use the array class? Best, Ben What sort of List do you have in mind Regards, Peter T "Benjamin Fortunato" <BenjaminFortunato@discussions.microsoft.com> wrote in message news:8CBF362B-4723-46ED-A0FA-2FD145EE9AF7@microsoft.com... >I have one quick question. Does Excel support vb Lists? I wanted to add >some > data to a vb l...

Need help!
Hi all: I am trying to extract some data from an Access query using MS Query while in Excel. The access query will extract the data from the data source without any problems. (I can even cut and past the data from the access query to the excel spreadsheet.) The problem arises when I go to refresh the data in the excel spreadsheet using the MS query. I get the following error message: "[Microsoft][ODBC Microsoft Access Driver] Invalid character value for cast specification on column number 4 (SumofSumofTransValue)" Can anyone shed some light on this problem? Everything wo...

Jump from Excel to another application
Sorry that my privous issue was in danish !! I often want to jump from an excel (2007) cell to an form (screen) in the erp-system Dynamics Ax 4.0. E.g. You have a pivottable in excel with all itemnumbers. If you rightclick at one of the numbers my idea was that you was offered "Go to Ax" and was send to an Ax screen showing the itemtable. I don't know how it schould be possible, but I am sure that it would be appriciated. Best regards Peter ...

Excel.exe application error
When I try to run VBA code I often get this message: Excel.Exe Application Error The instruction at "0x3005fc4b" referenced memory at "0x00000000". The memory could not be "read". Click on OK to terminate the progam Click on CANCEL to debug the program If I quit Excel and restart and try to reload the workbook I get the message that the workbook is in use. Sometimes deleting temporary internet files helps. Sometimes I have to reboot. What's causing this and what can I do to solve the problem. It happens quite a lot. ...

Excel and VBA
Hi there, I'm just a starter with VBA, and couldn't find any 'better' Newsgroup for my question. I have an excel workbook with just one sheet. in Row 1 there are the titles.: Date, Sum, Explanation. I'm trying to do an Userform with what to feed this information. Can't figure out what code it is which transfers for instance 1000 ? into B2, then next time to B3 and so on.... I know it's a lot to ask, but is there anyone there who could help me to start? Sorry my english -jippo- Range("B65536").end(xlup).Offset(1).Value = Userform1.Textbox1.Text Or, ...

Help with Excel Inventory Database
Is there a good example of a parts inventory database and reports that is publicly available? I have an idea of what I need to do with the columns for receiving and releasing parts, but I am not certain how to display the current inventory for each part. Any help will be greatly appreciated. Hi, Take a look at my Excel database tutorial at http://edferrero.m6.net/DataTutor1.html Ed Ferrero "ridgerunner" <iluttrell@msn.com> wrote in message news:%23t0uUdoIFHA.576@TK2MSFTNGP15.phx.gbl... > Is there a good example of a parts inventory database and reports that is ...

Excel 2008 startup
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel How do I prevent a new workbook being created when I launch Excel? <br><br>TIA, <br> Jim Other than launching by double-clicking an existing workbook file, you can't. That's the way the program is designed to work because there is absolutely nothing that can be done - including changing Preferences settings - unless a file is open. Even if you can't see it, much of what the program needs in order to operate is actually stored in a workbook file. There is no problem because...

A Publisher viewer would make it easier to read other's pub files
I'm finding it rather frustating not being able to open other people's pub files. Why isn't there a publisher viewer?? This can't be doing great things for the amount of usage of publisher... considering anyone who uses it can't show their work to others... whereas PDFs etc... its an open-source format. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the ...

How to return short cuts to tool tips in Excel 2003 sp3?
Hello! How to return short cuts to tool tips in Excel 2003 sp3? Regards, Dima Hi, That is not an option in 2003. If you are interested in a list of all Excel shortcut keys send me an email. -- If this helps, please click the Yes button. Cheers, Shane Devenshire shanedevenshire@sbcglobal.net "Dima" wrote: > Hello! > How to return short cuts to tool tips in Excel 2003 sp3? > Regards, > Dima > Thanks for your reply! Yes, I'm interested in a list of all Excel shortcut keys. My email is kopn at bk dot ru "Shane Devenshire" wrote: > Hi, > ...

Analyze it with Excel from Access
A user that is not the owner of the database trys to export an access query to excel using the Analyze it with Excel option only gets a blank spreadsheet. The owner of the database can export the same query using the Anaylyze it with Excel option and sees the data in the spreadsheet from the query. Why does the non-user's spreadsheet come up blank (empty)? ...

how do you pass the prove it test for Microsoft Excel?
I applied for a job in a clerical position and they made me take a prove it test for MS Excel and Office? Is there a link on how I can practice this test and retake it so I can pass the prove it test? I would imagine the test was designed in-house by the company to which you applied. There is no standard "prove it test" for Office and Excel other than the MOUS exams. MOUS = Microsoft Office User Specialist Search the 'net for MOUS exam or certification. Gord Dibben MS Excel MVP On Wed, 17 Jun 2009 13:06:01 -0700, jerry <jerry@discussions.microsoft.com> wrote: >...

Any way to make all references in a cell absolute?
Is there any way to make all references in a cell (or better yet a rang of cells) absolute -- Message posted from http://www.ExcelForum.com Hi ModelerGirl! This was posted earlier today Sub Absolute() 'Converts all relative refs in formula to absolute 'Posted by Gord Dibben 27-Feb-2004 Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula _ (cell.Formula, xlA1, xlA1, xlAbsolute) End If Next 'line above in ( ) can be altered for varying references as below '(cell.Formula, xlA1, xlA1, xlAbsRowRelColumn) '(cell....

Macro commands to assign colors to each line in an Excel chart.
Is there a macro command that will assign a user specifide color to each individual line of a scatter graph? Hi, First, scatter charts do not usually display as lines, so when you say line what do you mean? You chosen as style with connecting lines? or you are refering to drop lines, or error bar lines, or...? You can tell Excel to vary the color by point but if you want to assign specific colors to specific points you will need to write a macro. What version of Excel are you using? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "JohnnyC" wr...

use only PART of a # in excel?
OK I know this might sound like a strange question. But I am trying to write a formula for a machine that I have in my factory. It is a multi step formula, and in one of the steps it will give a result, for example, of 127.6543. Is there ANY possible way for me to ask the next part of the formula to ONLY use the .6543 without taking the 127 into account, or without having to tell it to subtract the 127 manually (and ALSO vice versa meaning I need it to then use ONLY the 127 and NOT the .6543) ?? I hope I am making myself clear, because Im not even sure that I understand what I am asking ...

Making A Particular Wooksheet Active
How do I from a UserForm bring up a particular wookboob sheet to enter data in dependant upon the results of testing weather another wooksheets cell True or False condition, or Do I HAVE TO make these entries from a UserForm? That's confusing. Post back and tell us what you have and what you want to do. HTH Otto "Wmeyer2" <Wmeyer2@discussions.microsoft.com> wrote in message news:C2AE216F-787B-46CE-BA27-E15C343EE9E4@microsoft.com... > How do I from a UserForm bring up a particular wookboob sheet to enter > data > in dependant upon the results of ...

finding days in Excel
Is there a format in Excel which returns the day for the date you put in? or does it have to be done manually using lookup tables etc? Thanks Andy -- Andy B. ------------------------------------------------------------------------ Andy B.'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14764 View this thread: http://www.excelforum.com/showthread.php?threadid=264547 i hope you meant a worksheet function which would return the weekday fo the date entered: =TEXT(WEEKDAY(TODAY()),"dddd" -- icestationzbr -------------------------------------------...

2005 Calendar for Excel
I went to http://office.microsoft.com/templates/category.aspx? CategoryID=CT061993171033&CTT=6&Origin=EC010963431033 and have already used the 2004 Excel template but when I clicked on 2005 there were no Excel templates. Does anyone have a place where I can get next years or is there a way to manipulate the 2004 to create 2005 month tabs? Thanks in advance. You may get more help if you say which excel template for 2004 you want to try to use for 2005. There seem to quite a few at: http://office.microsoft.com/en-us/templates/CT062100861033.aspx Mike King wrote: > > I wen...

Excel webquery questions
I have about 500 URL's, which I want to use in webqueries. I have two problems. PROBLEM 1 ^^^^^^^^^ Every URL returns a page with tables, and I know which table I need (for every page the same table) As an example, this is one of the URLS's. http://www.realestate.com.au/cgi-bin/rsearch?a=s&cu=&s=wa&ag=&t=res&snf=ras&pm=&tb=&u=FURNISSDALE&px=&cat=&minlandsize=&minbed=4&maxbed=&parking=&p=10&o=p I need the cel, displaying the first offer. This webquery works, and returns: ---------- GREENFIELDSThe Real Mccoy $195,000...

Non-Automatic Link Updating in Excel 2003
I wrote a large system of about 30 Excel workbooks that are all linked together in various ways. I've been using this sytem for several years under Excel 2000. I just got Excel 3000 (XP), and to my disbelief, every time I open a workbook, I have to digress and answer questions about the link updating. If I go into Edit->Links I see the usual (with some new features) links window. I checked the box that says to do the updating automatically. I selected the option that says to always update the links and DO NOT present me with a popup message asking me if I want to. These two ite...

Testing with Excel
I'm writing a software program using .NET framework 2.0 with Excel 2007 installed on my dell laptop machine. I'm developing with Visual Studio 2005 using C#. I have a method the first creates an instance of the Excel application: Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); i then test the excelApp object to see if it is null and if it's null then that means that Excel doesn't exist on the machine that is hosting the application i'm developing, right? or will the application throw an exception because excel doe...

Exporting to Excel #2
Some computers have an the option "EXPORT TO MICR. EXCEL" in their toolbar after right clicking on a webpage? I don't have that option but really need it. Can anyone help? Try the following in "no particular order" Copy the selection and paste it into excel Do a save as CSV (Comma Seperated Values) Do a save as Tab deliminated. If you try to import and everything goes into column A only. Selec column A and then in the "Font" selection Select "COURIER" and then yo might see where there are "possible" column breaks. If there are - the - ...

webpage to upload excel files
I need to give some people a way to upload their files (excel spreadsheets) for further analysis. Actually, it a survey files. For uploading I need to give them a simple webpage with a custom appearance with a window to locate a file for uploading and a button to submit/upload the file. Those people who are uploading files shouldn’t have any access to anything else except that webpage. Please, advise if it's possible with Puclisher. If not please advise how I could implement it. Thanks Not natively with Publisher. Ask your host what they support for uploads (some hosts don&#...