I have a large Excel file consisting of multiple data sheets with plain data and a couple of dashboard sheets with various graphs and kpi's based on these data. I am looking to make the file smaller and faster to work with. Should I convert the unformatted data to tables or not. I can't really find anything to support this.
Anyone got any ideas?
Tables have a lot of benefits but they are generally slower than plain data, (although the latest version of Excel 2016 has significant Table speed improvements).
You're actually asking the wrong question. The questions you should be asking is "Why is Excel taking so long to recalculate, why is my filesize so large, and what can I do about it?"
And you don't give us much info about your symptoms. How large is your file in MB? How many rows of data in it? Lots of lookups on big ranges? Lots of volatile functions like OFFSET, INDIRECT at the head of long dependency chains? How slow is it? What version/SKU of Excel do you have?
If Excel runs slowly, it's generally because people have inadvertently programmed it to run slowly, due to poor formula choice and suboptimal layout. Converting to Tables or not isn't going to make a hell of a lot of difference, by the sound of things.
Common culprits that result in slow files include the following (note the last one re Tables) :
Inefficient lookups on multiple columns (such as using multiple VLOOKUPS to bring through data for the same record rather than using one MATCH and multiple INDEX functions)
Lookups on very, very long arrays. See my post at http://dailydoseofexcel.com/archives/2015/04/23/how-much-faster-is-the-double-vlookup-trick/ to learn how sorting your lookup tables and using the binary match parameter can speed up lookups thousands fold.
Overuse of resource-intensive formulas such as SUMPRODUCT, when simpler alternatives exist (including SUMIF and it's variants, or even better, PivotTables)
Using IF and other functions to change the formatting of thousands of cells, instead of using custom number formatting
Using Data Tables. (These can really hog resources, and sometimes better alternatives exist)
Using many thousands of extra formulas to reference data input cells that might not be used, rather than using Excel Tables (aka ListObjects) that expand dynamically, automatically. I always use Tables to host my data and settings. They radically simplify referencing (including from VBA) and file maintainability.
You need to address the root cause, not the symptoms. A good place to start is this article by recalculation guru Charles Williams (who I see has dropped by):
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/excel-tips-for-optimizing-performance-obstructions
In terms of file size, as Charles Williams puts it: To save memory and reduce file size, Excel tries to store information about the area only on a worksheet that was used. This is called the used range. Sometimes various editing and formatting operations extend the used range significantly beyond the range that you would currently consider used. This can cause performance obstructions and file-size obstructions.
You can check what Excel thinks is the used range by pushing Ctrl + End. If you find yourself miles below or to the right of where your data ends, then delete all the rows/columns between that point and the edge of your data:
(Note that you’ve got to use the Right-Click DELETE option, and not just push the Delete key on the keyboard.)
When you’ve done this, then push Ctrl + End again and see where you end up – hopefully close to the bottom right corner of your data. Sometimes it doesn’t work, in which case you need to push Alt + F11 (which opens the VBA editor) and type Application.ActiveSheet.UsedRange in the Immediate Window and then pushing ENTER (and if you can’t see a window with the caption “Immediate” then push Ctrl G).
Lastly, depending on what version and SKU of Excel you have, you may be able to use PowerPivot and PowerQuery to radically simplify things and drastically cut down on the amount of formulas in your workbook.
