Excel Data Model is a database that is built-in to Excel. It has been around since 2010. Using it increases the capacity of Excel to handle millions of rows, it reduces file size significantly and eliminates VLOOKUP for code to description mapping. These new options in Excel 2016 and above simplify the usage of Excel Data Model and improve performance for large data operations. Go to File – Options – Data tab.
Options in section 1 and section 2 are already covered in my previous blog articles. Section 3 helps us incorporate Data Model more easily into day-to-day Excel data management activities.
Contents
Prefer Excel Data Model …
This option helps us use the data model, even if you are creating a Pivot Table from data in an Excel sheet. If this option is selected, the raw data from Excel sheet is first added to the Excel Data Model and then a data model based Pivot Table is created.
How can you tell the difference? A regular pivot table does not show table names. Pivot table based upon Excel Data Model shows the table name (even if there is a single table).
Excel Data Model based pivot tables support practically unlimited number of rows. In addition, you can create measures (calculated values) using the very powerful (and also fairly complex) DAX language. A pivot table created from the Excel Data Model is called Power Pivot.
Disadvantages of Excel Data Model based Pivot tables
The most practical deterrent is the inability to use Pivot based grouping for Text, Numbers of Dates. Right click – Group option just does not work with Data model based pivots.
Refer to other related articles on Data grouping
Disable Undo ( 2 options)
These options eliminate the overhead of UNDO in order to speed up Excel Data Model based calculations as well as refresh. Change the 8 MB limit according to your needs and PC configuration.
Enable Data Analysis Add-ins
This option enables the Power Pivot tab as well as Power View and Power Map buttons in the Insert Menu. Power View is used for creating integrated, visual interactive dashboards. Power Map (3D map) is a powerful tool for plotting your data on an interactive 3D map of the world.
Disable automatic grouping …
Since 2016, Excel automatically groups Dates into Years, Months and Quarters. While this is a good feature, for large amounts of raw data, this grouping may slow down the analytical activities. Use this checkbox to prevent automatic grouping.
What next?
Notice these options and use these to your advantage when needed. Of course, there are more ways in which we can increase Excel analytical performance. Here are some important boosters to Excel performance (with or without use of Data Model).
-
Use 64 bit version of Excel
-
Provide maximum possible memory (RAM) and fastest available processor
-
Even if you cannot upgrade the hardware for all users, consider using a shared, powerful PC for performing complex and intensive calculations. Once computed, the files can be used on less powerful PCs
-
Close all other applications. Also close unwanted Excel files
-
Disable Undo for Pivot and / or data models as discussed above
-
Import only the required amount of data
-
If calculated columns are needed, consider if these could be calculated in Power Query – before importing
-
Make sure that your PC is configured to use the High Performance option in Power Options
-
Reduce usage of volatile functions (NOW, RAND, INFO, CELL, OFFSET, INDIRECT, TODAY
-
Eliminate unnecessary VLOOKUPS using relationships in data model
***
