I have a spreadsheet which is really badly designed.. its takes an age to open.. but once open it is generally ok. It is 30mb and takes approx 5 mins to open on any machine.. regardless of power.
This contains a lot of data which needs to be used till next april when a new system is implemented.
The first column of the spreadsheet is a simple, general format column which contains codes.. simple ones such as B11.
However we have one laptop, brand new, very powerful which when you do a CTRL + F on this first column (only), Excel locks up for about 3 mins, and then you can do your search.
This does not happen on any other machine.
I have
1) De-fragmented
2) Disk Cleanup
3) Reinstalled / repaired office
4) Tried chainging the format of the cells to plain text etc
There is no formatting, or formula in this column, nor does any other part of the spreadsheet reference it.
This has only started happening recently since this column was recreated as it was accidently deleted, and ONLY on the one machine.
Can anyone advice / assist before I end up rebuilinding this one machine that has the issue as it is the primary machine that uses this file.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Laptop has 3gb, and a dual core processor.
Have cleaned up a sample spreadsheet using all your other techniques, and got it to b 5mb smaller than it was, its still take a while to open... however the ultimate test will be tonight when I try it on the main laptop that needs the file.
Long opening time = acceptable
Freezing when pressing CTRL + F not
Will get back to you in a few hours with the results of the test.
Thanks for the great tip, very concise
Created a new spreadsheet, and impotred the data. This stripped out all the formatting. Tidied up the spread sheet and now it opens in less that 5 seconds, and searches instantly.
The laptop was not the problem in this instance. It just more of a leisure machien as opposed to a business machine.
Still a very good guide for spreadsheets hence why it was the accpeted answer. thanks
Business Accounts
Answer for Membership
by: patrickabPosted on 2009-09-10 at 02:10:59ID: 25298491
mvwmail,
Before you rebuild the offending laptop I suggest that the problem is with both the Excel workbook and perhaps with some settings/configuration of the laptop. Have a look at:
1. The size of the installed RAM. Laptops are notorious for being under-resourced with RAM. Install a minimum of 3Gb RAM.
2. Check the size of the PageFile - Start/ControlPanel/System/
3. Work through this check list:
Minimising Excel file size fanpages) list
http://www.experts-exchang
To reduce the overall size of an Excel workbook, you may like to try one/more of these suggestions:
* Don't save as dual format file (Excel 95/97); use the most recent version of Excel you (and your intended) audience will have access to - for instance, "Excel 2002", or "Excel 2000"? However, save in the oldest copy of MS-Excel you can, then re-save in the latest revision you can. Doing this may reduce overall size.
* If you notice that the vertical or horizontal scroll-bar(s) go(es) past the end of your data, you can clear the blank rows & columns beyond the extent of your data:
a) Go to the bottom row of data & then select the entire next row by clicking on the row number (in the "margin"). Use [Ctrl]+[Shift]+[Down Arrow] to select all the remaining rows in the worksheet, then click the "Edit" / "Clear" menu item to clear the rows of values & formats.
b) Go to the far right column of data & select the entire next column by clicking on the column letter. Use [Ctrl]+[Shift]+[Right Arrow] to select all the remaining columns in the worksheet. As before, use the "Edit" / "Clear" menu item to clear the columns of values & formats.
Repeat the above steps for each worksheet in your workbook where you can scroll past the end of your data. Finally, save the workbook, and close it. Upon re-opening the scroll-bars should now extend only as far as the data.
* Format your cells in continuous ranges (blocks) rather than individually setting the formats of distinct cells. For example, if all of row 1 is "size 12, arial, bold" do not set A1, B1, C1, D1, E1 (etc) separately, just select row 1 & apply the same style to each cell at once. Formatting in this manner can also be used with colo[u]rs (cell foreground & background), fonts, font sizes, font styles. Try to apply the same formatting to single or multiple columns, and/or rows, and make individual cells the exceptions, rather than apply formatting to individual cells one by one.
* Refresh any queries or pivot tables with (select critieria that references) the least amount of data possible.
* Research (via Help) & use inbuilt bespoke functions rather than writing your own code.
* Reduce the length of Range names so that they are still meaningful, but not as verbose.
* If you have any (auto) filtering enabled in your workbook, across one, or more, worksheets, remove this, or reset it back to "(All)" prior to saving.
* Remove any unused worksheets, or any unused VBA code (or pre-recorded macros).
* You may like to try exporting (right-click tab and select "Copy To" 'New Book') all your worksheets into a new workbook & then saving this newly created book as a different filename. When comparing the two files you may notice a vast difference.
* If you regularly save data across a network (i.e. is the workbook stored on a network), or is it 'Shared'? If 'Shared', try unsharing & saving locally, re-share & then save back to the network.
4. Eliminate the use of volatile functions. They are:
RAND(), NOW(), TODAY(), OFFSET(), CELL(), INDIRECT(), INFO().
Despite claims, these are not volatile: INDEX(), ROWS(), COLUMNS(), AREAS().
That's it for the moment.
Patrick