Microsoft released the Workbook Size Optimizer for Excel, the first version of an Excel add-in for Excel 2013 that inspects the data model and suggest possible optimizations. Fundamentally, it tries to apply the best practices descripted in a white paper I mentioned a few weeks ago, removing useless columns and changing granularity to those that could reduce the overall memory cost of a table.
There are different setup available in the download page, depending on operating system (Windows 7 or Windows 8) and on Office version (32 or 64 bit). Once installed, you have a new tab in the Excel ribbon, called Workbook Size Optimizer, showing a single button that starts a wizard.
I tried to run the optimizer with a workbook where I imported several tables from Adventure Works Data Warehouse sample database. The first page shows a few information about the workbook size and the option of automatic detection or manual choice of rules. The latter is an option you can request also later, so I started with the default.
After a short analysis, I received three smart suggestions (considered the model I have). We might wonder that removing UnitCost is a smart thing, because it could be required in order to perform calculations and rounding the value might be not correct for our analysis.
Since I requested to apply some changes, I have the option of changing which rules to apply. This corresponds to the choice you have if you choose “Let me choose the rules myself” in the first screen of the wizard.
I kept all the rules and after I click Next I had to wait several seconds in order to complete the optimization process. The result shows a few information about the result of the job.
This is a good starting point. Don’t blindly trust any suggestion and try to consider carefully the rules to apply in order to avoid losing important data for your analysis. Moreover, you might have a better knowledge of your data model than a wizard and consider the deletion of many useless columns (for your analysis) that are not identified by the wizard. My article Checklist for Memory Optimizations in PowerPivot and Tabular Models contains several best practices that you can apply to your data model.