I am happy to announce that my first detailed training course is now up and running at Udemy. It is about Power BI. But it starts with what you already know – Pivot Tables. That is why the course is called Pivot Table to Power BI.
Power Pivot Data Model adds database like features to Excel. Power Pivot allows you to handle millions of rows of data, from multiple data sources and lightening fast analysis. It also eliminates the need for using VLOOKUP function for decoding codes into descriptions.
Power Pivot does NOT provide Grouping, Calculated Columns and Calculated Fields features. These need to be managed using related tables and DAX formulas. This can be a limitation in some cases – especially when you are new to Power Pivot.
In some cases, you want best of both worlds. The database consolidation, relationships and performance of data model. But at the same time, you also want the features of Pivot Tables which you are used to for so many years.
The good news is that you can do it quite easily. Here is how (requires Excel 2010 & higher).
Create Pivot Table from Power Pivot Data Model
- Create the data model as required.
- Now go to the Excel workbook and choose Insert – Pivot Table.
- Please note that there is no data in the Excel sheet.
- Now, from the dialog, choose the checkbox Choose this workbook’s data model
- A regular pivot table is now created.
- All the expected features are now available.
- That’s It. Enjoy.
Here is a quick listing of the key differences from a usage point of view.
It will help you choose the right type based upon the analytical objectives.
Here is another short but power packed video which explains how analytics can drive growth. Read the related articles here.
Redundant or unwanted columns can confuse the users of Pivot Table. Here is how you can remove unwanted columns from appearing in the Pivot Table field list.
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).
Photo credit: Flyinace2000 / Foter / CC BY-SA
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
Presentation download with reference links
Download the Presentation (3 MB). I have added lots of links to related articles and external references including books written by stalwarts in Excel analytics field.
Thank you for the overwhelming response for Data Analytics webinar video. More than 800 attendees, 70+ questions.
Watch the video on YouTube (use 720P) or Download (MP4, 188 mb, 60 min) and view on your PC. A big thank you to the Microsoft and Economic Times team for making this possible.
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