Excel 2016: Opening and Saving spreadsheets really slow (15+ minutes)

1
votes

For some time now I have been experiencing problems with the Excel files I work With. I am using MS Office 2016 version on Windows 10. Excel 2016 is fitted with a new functionality called usually Power Query. It's an interface for pulling the data directly from the database (SQL Server). The data from the DB are used to populate pivot tables. The pivot tables themselves take quite a lot of memory BUT it wasn't an issue in the previous versions of Excel - I have always used tones of them and never the file has been so heavy to open/close. Here is what I have tried:

  1. Control Panel - Change/Uninstall - Fix
  2. Close all the connections I could identify through the Excel Interface (Edit Links, Show Queries) - copied and pasted all the data sets pulled from SQL as text and removed the queries.
  3. Switch off auto-calculations and auto-recovery.
  4. Remove all the objects (icons, pictures, etc) from external sources.
  5. Disable synchronise (One Drive for Business, Evernote, Sharepoint, etc)
  6. Close all the other Office apps
  7. Identify the Excel's directory within the AppData and remove all the temporary files
  8. Kill excel and restart the PC (somehow opening takes a bit less time at first)
  9. Copy the part of the file I want to use for a presentation and replace all the pre-calculated values with plain text - the file is still incredibly slow when saving and opening.
  10. Open it on other workstations.

NOTE: working with the spreadsheet once it's been opened is fine. It responds well to scrolling, filling the cells in, etc. The only thing that takes ages is opening and saving. It simply shows a blank screen ("Excel Not Responding"). Excel does not take too much memory (206MB out of 8GB) or cpu when saving. I also noticed that for some reason some of the save operations increase the size of the file (like 5MB -> 6.5MB) which may or may not be related.

2
excelpowerquery
I forgot to add a detailed description of the issue I am dealing with. It has been addwd now. Is there any other reason for receiving points down? is stackoverflow not the right site to ask it? Should I move it elsewhere (superuser perhaps) – gopi
any formulas or macros in the files? try saving the file as .xlsb and disable all add-ins – Slai
@Slai - thanks but saving as .xlsb and disabling add-ins didn't help. – gopi

2 Answers

2
votes

I ended up unzipping the file and checking the contents manually for anything suspicious. I found two huge files in the xl/drawings directory (vmlDrawing1.vml and vmlDrawing2.vml - being the exact copy of vmlDrawing1.vml). The files contained an XML description of 65536 identical objects (IDs were different). I have no idea how so many copies of the object ended up in there as I only inserted it once. I removed 65535 of them from the xml file, zipped the entire thing back and renamed to .xlsx. To my delight, excel opened it easily and saving is now instant.

Note, that removing objects from within excel (find -> go to special -> objects/headers/footers -> remove) did not work, the huge files were still there, only the manual intervention helped.

0
votes

If you go to Data | New Query | Query Options, and then go to Current Workbook | Data Load, is there a section about Background Data? If so, uncheck the "Allow data preview to download in the background" checkbox, save the document, and see if Excel starts up more quickly.