April 28, 2015 // By Steve Hughes
In July of 2012, I wrote about the impact of Excel 2013 for BI users. I wanted to follow that up with more information about what you will find when you open up Excel 2013. In my original post, I highlighted the “big release” features Microsoft brought to Excel. Before we dig into some of the other cool features, let’s look at what Microsoft’s strategy is around BI for end users and visualizations.
What Appears to Be Microsoft’s Strategy?
First, I have to disclose I neither work for Microsoft nor am I privy to the internal strategic workings there. I am simply drawing conclusions based on their activity and public comments. For instance, “From data mash-up and exploration to interactive visualization and sharing – Excel 2013 is now the only self-service BI tool business users need to build their BI solutions.” (http://www.microsoft.com/en-us/bi/Products/OfficePreview.aspx). As you can see they seem to be making a strategic play to make Excel the complete BI tool for everyone to use. I bring this up as I see the landscape shifting as Microsoft moves from BI server tools into legitimate BI end-user tools. Excel is now positioned to be a huge player in the BI world, if for no other reasons than the fact that it is on almost every desktop and is now cloud-enabled.
Additionally, Microsoft is further integrating Excel into the overall BI stack. Working from Excel into the other tools, Excel Services in SharePoint 2013 and Excel on Office365 provide an additional level of built-in support for Excel. Both of these toolsets enable a large amount of collaboration among user bases both large and small. Excel also has the capability to interact with multiple data sources using the power of xVelocity In-memory Analytics or PowerPivot. Microsoft is adding more data access to tools such as SQL Server, SQL Server Analysis Services, Hadoop, and Windows Azure Marketplace to name a few. Some of these tools are enabled by add-ins from the SQL Server team, including extended PowerPivot functionality and data mining.
Power in the Product
Microsoft did something amazing by building PowerPivot and Power View into the product. Power View is definitely the “eye candy” and allows you to produce awesome, dynamic reporting into Excel. Power View needs to be added from Options in Excel 2013. This is not an install but simply making the option visible. Much of the PowerPivot capability is built right into Excel. Once you have activated PowerPivot, you get the menu option and the full plate of new capabilities such as data modeling and hierarchy support.
One feature that still exists is the interactivity between Excel and SQL Server Analysis Services. Excel has always been a great tool to work with multidimensional data. What I discovered recently is a set of additional OLAP tools that allow creation of calculated members and calculated measures to the sheet which expand on the content from the cube without affecting the database design.
More impressive is the inclusion of the drilling capability that was added via the Quick Explore option. This brings some of the PerformancePoint operations into Excel that have been missing. This is truly an awesome feature addition, as shown below:
Quick Analysis shortcut is very cool and allows a user to try visualizations on a selected section of data. In the figure below, you can see the icon that is revealed when an area of data is selected. In the following two figures, one shows adding data bars and the second shows a chart option.
As you can see, Quick Analysis is very cool. It can also create pivot tables, add aggregations, and other visualizations.
Office and SharePoint Apps
If you can’t find what you need, start looking at the Office and SharePoint Apps that are already available or that you can build yourself. For instance, there is currently a gauge and a Bing Map app that can be used in Excel. I really expect that this will be the feature that ultimately enables Excel to overtake competitors and further consolidate the BI experience on the Microsoft platform. If you are interested in trying your hand at Office or SharePoint Apps, check out the following sites:
And There Is Still More…
The fun does not stop here! As you take time to learn more about the tool, pivot tables now can have slicers and the new timeline filter. If you want to take it further, you can add data mining to your Excel solution. There are still so many features to explore, including Inquire and Flash Fill. I encourage you to take Excel 2013 for a serious test drive. You may find the most complete BI tool is only an upgrade away.