Modern Excel is a term first coined to distinguish between Excel 2007, and Excel 2010 that saw the introduction of PowerPivot and PowerQuery, Excel has become a first class enterprise Business intelligence solution, Millions of row can be loaded, using PowerQuery , data can be linked using PowerPivot interactive, reports are easy to generate using PivotCharts and Cube Formula, and your audience will have more confidence in your reports.
Basically excel become a personal datawarehouse that store and analyze millions of records from multiple data sources.
A sample dashboard In Modern Excel
A “traditional” excel dashboard has no real distinction between the data source and the report itself, everything is mixed together with the results generated by a mixtures of vlookup and sumproduct, and maybe some VBA code, if the boss ask for a different report, you have to start from scratch, but most importantly, it is error prone, if you move one cell, you may change the results without noticing, and there is no obvious way to audit the results.
The Dashboard was first built in Excel using PowerPivot and Powerquery then imported into PowerBI desktop then published to the web.
I could have simply distribute the excel file with the reports but I wanted to emphasis it is not simply another Excel spreadsheet with zillion of cryptic formulas that no one can understand except the author but a proper database solution based on columns not cells.
How it works
Anyone who used MS Access will find modern Excel very familiar, so let see how the steps needed to generate the sample dashboard, it is only a very high level description, not a detailed tutorial.
Data Source: most of SQL Servers, excel, text files, web, ms access, basically any data source you may think of
Data Preparation: PowerQuery is a Powerful ETL Tool for cleansing data and prepare it to be loaded in the data Model, for example if your export the spread of hours from P6, the data will be spread horizontally, in a couple of clicks you can unpivot the data and make it as a table
Data Model: is where the relationship between the table are defined, here is a screenshot from the sample dashboard
Dax: the language to query the data model and define your measures, it is not that different from sql queries
Report and Charts: Pivot table, Pivot chart and cube formula that read from the data model
Here is an example of a pivot table that query from the data model, notice how are all the tables are visible, a classical pivot table will show only one table.
Powerpivot vs PowerBI
It can be a little confusing when you hear all those terms but basically The core technology in Microsoft Business intelligence stack is in an in-memory analytic engine called Vertipaq, the same technology is present in different products intendent to service different market segment
- PowerBI : the cloud offer from Microsoft to provide business intelligence for enterprise, it mainly compete with the like of tableau and Qlik, PowerBI come in two editions free and professional.
- SQL Server Analysis Services : On premise Enterprise offer come with MS SQL Server
- PowerPivot : in Excel 2010 and up for self-service Business Intelligence
Self service business intelligence is changing how to deal with data, microsoft has manage to bring a powerful technology to the end users, previously a word like datawarehouse was exclusively a heavy IT investment, now with PowerPivot and PowerQuery you can do it by yourself.