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.
Continue reading Excel Data Model : Simplifying usage
Now it is time to spell out exactly what to look for when creating good raw data (or repairing bad input data). Use this as a checklist to quickly classify data into GOOD or BAD (requires clean-up).
Continue reading Excel Tables 5 – Good Data Checklist
Excel Table seems like a trivial feature. But if used correctly, it transforms and improves the way you use Excel dramatically. Here is a set of articles (the entire knowledge pack) which explain the concept and importance of Excel tables. Many practical usage scenarios are also explained in detail.
Articles about Excel Tables
All you wanted to know about Tables. In a concise series of articles. Below is the Knowledge Pack.
- What are Excel Tables and Why should you use them?
- Converting regular data to Excel Tables
- Handling manual formatting while creating tables
- Merged Cells and Cross Tab – The Data Villains
- Good Data Checklist – Prerequisite to creating Excel Tables
- Miraculous Formula Auto-Copy with Excel Tables
- Excel Tables Formula Auto-copy not working
- Freeze “Pain”killer
- Automatic copying of everything!
- Unbelievable simplicity – Calculated Columns (Readable Formulas)
- Everything is in a name
- Auto Update Dependent Formulas and References
- Standard Operating Procedure for using tables across the organization
It is a powerful but often ignored feature of Power Pivot.
It allows you to describe the meaning of the data column – independent of its field name.
Estimated reading time 8 min
Continue reading Understanding Data Categories in Power Pivot
||If you use text month in a Power Pivot Table, it is sorted in alphabetical order.
Here is the solution to the problem.
Quick Solution time 2 min
Detailed reading time 15 min
Continue reading Repairing Month Sorting Order in Power Pivot – Part 1