Is there a reason why memory of an Excel file shows 6 times larger in Task Manager

Hi,
I am trying to reduce the stored EXCEL memory that is shown in the task manager.
Only one file is loaded and memory for Excel is shown as 1.35 GB yet in file explorer the file size is only 230 MB ?
How can that be ?
I opened a second instance of Excel and copied the entire sheet to it to see if there had been any stored settings
and that made no difference.
I am hugely disappointed in Microsoft limiting Excel to only 2GB and wonder if this might be addressed in new version this year.
Any assistance much appreciated
Many Thanks
Ian
Ian BellretiredAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KimputerCommented:
If it's so big, is a database approach maybe better suited for you?
Excel files are now essentially compressed (if you use the xlsx format). When opened, it will need the full DECOMPRESSED data to work efficiently.
Ian BellretiredAuthor Commented:
Thanks for your quick response Kim, I am looking at the various database options
I am surprised that when decompressed it is 6-7 times larger.
When I deleted all the data and formulas in the file the memory still showed 1.3 GB why would that be ?
HuaMin ChenProblem resolverCommented:
Background operating system should have relevant amount of memory for supporting to process list of data/row , within Excel file, something like temporary list of records are being used. Thus extra memory is needed to Excel application.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

John TsioumprisSoftware & Systems EngineerCommented:
When things start to grow and your Excel doesn't open in couple of seconds( SSD) then its time for a database solution.. (Access for start)
Ian BellretiredAuthor Commented:
Thanks Hua, I was just surprised at the escalation in size to 7 times.
Ian BellretiredAuthor Commented:
John, are you suggestion I use an SSD ? I had been thinking about it for some time.
Any advice on size etc ? I use a Dell T7500 Workstation Quad core with 24 GB Ram and utilise 8 processors
I bought it in 2009 and wondered if an SSD will fit without structural changes.
KimputerCommented:
If you deleted all the data and formulas, and you think it's still too big, some file "corruption" may have occurred, and you're better off starting a new file, and copying all relevant data to this new Excel file. Don't copy whole sheets, as you'll probably copy the "errors" with the sheet, but really only copy the ranges per sheet do the new file.
John TsioumprisSoftware & Systems EngineerCommented:
Normally you should add it with no problems...but please do buy an SSD...after you install you will probably feel like you bought a new computer...
KimputerCommented:
As for the SSD question, it will always fit. That's because they're only a few grams, and can be put somewhere safe in your PC using double sided tape (if there's no special slot or screw holes to be found)
Fabrice LambertConsultingCommented:
I am trying to reduce the stored EXCEL memory that is shown in the task manager.
Only one file is loaded and memory for Excel is shown as 1.35 GB yet in file explorer the file size is only 230 MB ?
How can that be ?
First, there is the base memory used by Ms Excel.
Second, add memory to load data from the file.
Third, add memory to handle formulas, charts, graphs, VBA and whatever dynamic content in your workbook.
This explain the memory usage.
Ian BellretiredAuthor Commented:
Hi Fabrice, I am only interested in formulas, is there a way to disable the memory for the others you mentioned ?
Lee W, MVPTechnology and Business Process AdvisorCommented:
Are you using the 64 bit version of Excel?  The 32 bit version has memory limitations due to the limits of a 32 bit address space.  Microsoft didn't just say "lets stop there" - it a technology issue.  Upgrade to 64 bit.
https://support.microsoft.com/en-us/help/3066990/memory-usage-in-the-32-bit-edition-of-excel-2013-and-2016
Lee W, MVPTechnology and Business Process AdvisorCommented:
Quoting my above link:
Excel expert users who work with complex Excel worksheets can benefit from using the 64-bit edition of Office 2013/2016. This is because the 64-bit edition of Office does not impose hard limits on file size. Instead, workbook size is limited only by available memory and system resources. On the other hand, the 32-bit edition of Office is limited to 2 GB of virtual address space, and this space is shared by Excel, the workbook, and add-ins that run in the same process. (Worksheets smaller than 2 GB on disk might still contain enough data to occupy 2 GB or more of addressable memory.)
Ian BellretiredAuthor Commented:
Hi Lee, I do have the 64 Bit 2016 version but is does go into 'not responding' mode when memory
gets near the 1.3GB mark.  My PC runs 8 processors with 27GB RAM
Lee W, MVPTechnology and Business Process AdvisorCommented:
Out of curiousity, are you using an .xls file... or an .xlsx file?
Lee W, MVPTechnology and Business Process AdvisorCommented:
And does the problem occur on other computers?
Ian BellretiredAuthor Commented:
.xlsx
Ian BellretiredAuthor Commented:
I seem to recall same problem on other computers but will double check
Lee W, MVPTechnology and Business Process AdvisorCommented:
Also, how old is the excel file?  To be that big, I can see it being initially created in a MUCH earlier version of Excel and used in successively newer versions... there could be just the "right" data in there from old versions that's causing an issue... perhaps a rebuilt file would work better?  Can you create a new file with junk data (without copying any formulas, names, etc) to test and see?
Ian BellretiredAuthor Commented:
I have downloaded Excel data csv format from a data service.
The records in the files date from 2011 to current.
The formulas were created about a year ago using 2016 64bit
John TsioumprisSoftware & Systems EngineerCommented:
Well to add the 7500 Workstation does has some issues with SSDs mainly due to limitations from the chipset...cheap SATA3 pcie controller can get the best performance..well the complete procedure is described in this youtube video

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ian BellretiredAuthor Commented:
Hi Lee, I have checked the 2 things you mentioned.
1) It is equally slow on another desktop quad core with 8GB ram
2) The files were created on the Excel version 2016 64 BIT 2016

Both computers are 10 years old but even as high spec as they are the hard drives are
outdated and the motherboard is probably a bit too ancient to expect new devices to be added.
A new computer seems the answer. Thanks you have stirred me into action.
Ian BellretiredAuthor Commented:
Hi John, Thanks for the You Tube video. I may take the step of installing that
SSD as I can always add it to a new machine if I do go ahead and have one built.
Many thanks
Ian BellretiredAuthor Commented:
Thanks guys, seems I should try an SSD for a start and then look at building a new PC
Cheers
Ian
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Memo

From novice to tech pro — start learning today.