3

I am collaborating with a data analyst using two linked files stored in a shared folder in my Dropbox. The first file contains the raw data (several worksheets; total file size ~110MB); the second file contains the outputs (tables; pivots; slicers; graphs; 10MB total file size) and is linked to the raw data file (the raw data file is the source file).

We are both Mac users. He is using a MacBook Pro which I think is running a dual core i5 and SSD. My primary computer is my iMac (late 2012 build; quad core i7 3.1ghz; 1TB Fusion Drive; 16GB RAM; Catalina). I have used stress test software and the Fusion Drive appears to be fine : ~230 MB/s write; ~450 MB/s read.

Although the raw data file is large, the data analyst can open the file and edit it without significant performance issues.

My iMac will open the raw data file using Excel for Mac (64 Bit version), however, Activity Monitor shows Excel running the CPU at ~200% even as 'rest', i.e. no calculations being performed.

As soon as I edit the file in any way, even something simple such as inserting a row in a worksheet, the CPU usage increases to ~775% and the data read rate drops to <1MB/s.

Excel then hangs and is unusable.

I've tried Excel for Mac, Excel for Windows in Parallels using Windows Virtual Machine, and Excel for Windows in Boot Camp. I've also tried working with the Excel file using my MacBook Pro (mid-2013; 2.5Ghz Core i5; 8GB RAM; 500GB SSD; Mojave) using Excel for Mac and Excel for Windows in Boot Camp.

The problem persists in each of these environments.

I've spent around 6 hours on tech support calls with Apple, Microsoft and Parallels but no progress has been made.

I've repaired Home Permissions (no improvement) completely uninstalled Office suite and installed 64 Bit versions of the Office suite (no improvement).

If the file was corrupt I'd expect the data analyst to be experiencing significant issues with the file, but he isn't. This seems to point towards this being a local problem, i.e. my system.

I'm stumped, especially regarding the very high CPU usage by Excel and the almost zero data transfer rate.

We've run Excel files approaching 300MB in the past, using client Windows computers (nothing particularly advanced in terms of specs) and even though the files took (say) a minute to load they were usable.

Any help would be hugely appreciated.

EDIT/ UPDATE 2020-08-12 :

Apologies to all that took the time to add comments/suggestions - several weeks have passed without an update from me.

In short, there are two issues : collaborating using linked Excel files in Dropbox, and slow performance in an Excel For Mac environment.

It became clear that Excel for Mac simply cannot handle linked files which are not saved on a local drive. The data analyst and I have had to revert to a single integrated file, which is working but of course increases the size of the file considerably.

The speed issues are a different matter and we still haven't resolved those. Despite the data analyst having a 'lesser' spec MacBook Pro (2019; 1.4Ghz quad core i5; 8GB RAM) than my iMac (2012; 3.1Ghz quad core i7; 16GB RAM) his machine is able to operate the data file far more quickly.

  • 1
    Thanks, and the short answer is "no". I've copied and opened the file from my desktop and also from a USB2 connected SSD. Unfortunately neither makes a difference. – frustr8d Apr 12 '20 at 19:07
  • 1
    Perhaps it's possible to use LibreOffice. Depends on whether you have macros, mostly. – Peter - Reinstate Monica Apr 13 '20 at 14:35
  • 1
    @frustr8d Firstly, welcome to Ask Different! :) I'm just wondering, is there anything about this particular scenario that is different to the other times you've worked with Excel files approaching 300MB in size? For example, were the other situations with single Excel files, or were they multiple Excel files linked together as in this case? Or are you using Visual Basic (Macros etc) in this instance but not in the others? Or perhaps this time you're using Dropbox but not on the other occasions? – Monomeeth Apr 14 '20 at 8:57
  • 1
    Also, when you copied the file to your desktop for testing, did you copy both files or just one? And did you check to make sure the file wasn't still linking to the file in Dropbox? As an aside, I spend a lot of time using Excel and often (not always) these problems end up being caused by something simple. I'd be more than happy to download the files and test for you, but of course appreciate this may not be possible for confidentiality etc reasons. But, if that isn't an issue, the offer is there. – Monomeeth Apr 14 '20 at 8:57
  • 1
    @Gilby that's an excellent point, and getting clarity would be useful. However, as per the OP's question, they seem to be having the same problem using Excel for Windows (both in a VM and using Boot Camp, so there seems to be an issue relating to the setup of the files themselves (although not necessarily a problem with the files, but perhaps with how they are being accessed). – Monomeeth Apr 15 '20 at 1:15
5

At that level of data, it’s often easy to kill Excel whether you’re on Windows 10 or macOS.

  • Plain data should be fine for hundreds of thousands of rows and less than 30 columns for most data that’s not UUID length.
  • What seems to be most finicky is calculations, graphs, conditional formatting and all the processing that happens to the data and not simply the file of 100 or even 500 MB on disk.

The code base for 2020 Excel is the same on Mac and Windows with just some features selectively enabled for each platform. This was unified in the past 16 months, so you would need to simplify your calculations by troubleshooting your spreadsheets or perhaps move to an engine that’s less likely to get caught up like python or R. The main difference on Mac vs Windows is major feature and UI code for each platform, not the core data handling.

Depending on your linking, you might also just need a database which can handle indexing and optimization better than Excel can.

These are not at all trivial suggestions, so I know you’d prefer to not fix Excel, but it has so many layers of features, that you can stray away from performance and end up with a very fragile workbook in practice. There’s only so much stripping of charts and data you can do in some cases when you overload Excel with code and graphs and conditional calculations or worse, looping conditions that it cannot self detect or alert you.

  • The only issue I've experienced with reduced run-time occurs when the number of rows is greater than 65,000. The excel will not load them. You should try using Numbers to check how it works with your data file. Numbers is an apple spreadsheet program and is similar to excel except written specifically for mac. Maybe this will give you better performance, Numbers is free. – jmh Apr 12 '20 at 18:08
  • Thanks for the comments so far. I have tried Numbers - the same problem persists. The file will, eventually, load but Numbers also hangs. I've previously used Excel files ~250MB in size which contained ~330K lines of data plus pivots, umpteen formulae, graphs, etc. The 65K lines threshold is not a limitation that I've experienced. Alternative software (coding) would be beyond my capabilities. – frustr8d Apr 12 '20 at 19:01
  • @jmh although Numbers is free, native and opens Excel files, it is not a drop-in replacement for Excel as yet. For example, although the OP doesn’t state his use case, Numbers does not yet support array functions. bmike is correct in stating the Excel is easy to overload with completely. A proper database may be the solution in this case. – bjbk Apr 12 '20 at 19:03
  • I hear you @frustr8d - we have some data sets larger than yours that seem to chug along, others much smaller that just become unworkable. Just wanted you to know it’s easy to get mired in data with excel and that the problem is on both windows and Mac versions in my experience. I’ve not made the jump to Microsoft BI license, but that would be my next avenue if Python or another tool was too much friction to start. – bmike Apr 12 '20 at 23:54
  • @bjbk A database, or simply a text file (if the data is relatively large but simply structured, as it appears to be). It is not a problem to read 110 MB of text into memory and then do whatever one likes with it, programmatically. Of course the doing is much easier with Excel's backing. – Peter - Reinstate Monica Apr 13 '20 at 14:46
0

Upon looking over your question again, I'm wondering whether at some point your Calculation preferences within Excel were changed? Unfortunately, these settings remain across all workbooks and are not set on a 'per workbook' basis, so if they were changed at some point then it'd apply to all workbooks.

The Calculation options I refer to can be accessed from Excel > Preferences > Calculations (see screenshot below of the Calculation Option screen).

In the above screenshot you'll see that the Automatic radio button is selected. This is great when your workbook doesn't contain a huge amount of data, but will definitely slow things down if it does. Why? Because it means that every time you do anything that requires a recalculation Excel is having to recalculate the entire worksheet (or workbook depending on how you've set things up). So experimenting with the other two options may be worth a shot.

In relation to enabling multi-threaded calculations, this may or may not be preferable depending on your workbook. I know this sounds counter-intuitive, but some functionality in Excel doesn't support the use of multi-threading, and if you're using enough of those functions (especially in such a large file), then Excel will run slower if multi-threading is enabled than it would if it wasn't.

Things that Excel will only handle single-threadedly include:

  • the INDIRECT function
  • the GETPIVOTDATA function
  • VBA Macros
  • User Defined Functions (aka UDFs)
  • Iterative Data Tables
  • Circular References
  • Cube Functions

So it could be worth disabling this if it's enabled to see what impact, if any, it has.

Of course, if it's currently disabled, it may be worth enabling it and seeing what effect that has as well. It really depends on your workbook design.

  • Thanks for the additional suggestions - I'll try combinations of those settings and see whether they make any difference. I appreciate you having taken another look at this. – frustr8d Aug 12 '20 at 16:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .