Many of my customers are evaluating business intelligence (BI) solutions. There are often many business drivers behind this trend including the need for analysis, reporting, planning and forecasting.
Prior to the release of Excel 2007 many customers would purchase a variety of different BI tools each with their own user interface, licensing costs and set of functionality. With Excel 2007 we have sought to bring BI to the masses while eliminating the need for a number of different tools and lowering the overall total cost of ownership for BI solutions.
Here is a summary of my top 10 favorite aspects of Excel 2007 for BI solutions:
- Excel has become the rich BI client front end for PerformancePoint, SharePoint, SQL Server and any external data source or system. This allows users to easily aggregate, slice and dice many disparate data sources and get a consolidated view of their business.
- By supporting 1 million rows and 16,000 columns of data in the same workbook, Excel 2007 allows you to work with more data. This addresses a critical business need expressed by many Excel users and overcomes previous product limitations of 64,000 rows which often resulted in the creation of multiple workbooks and extensive macros and programming to link the data together.
- Excel Services enables you to easily publish an Excel workbook to SharePoint and then allow other users to access that data with just a web browser. This is of tremendous value when you want to make sure that everyone is looking at the latest version of data (aka a “single version of the truth”) and cases where you want to suppress hidden formulas, data and secure other sensitive data.
- Conditional formatting adds powerful data visualization capabilities to Excel. You can easily add data bars, color scales, key performance indicators (KPI), and other icon sets to spot trends.
- Pivot tables can now be created and used by anyone to organize and report on data. You do not need to be the “Excel guru” to be able to work with and change pivot tables. The redesigned pivot table capability and wizards streamlines the process.
- The graphics engine for Office has been significantly improved with Office 2007. Charting and SmartArt produce professional looking graphics in very little time. These graphics can be shared and reused across Excel, PowerPoint and Word and eliminate the need for many 3rd party services and tools.
- Tables in Excel automatically add common tools for working with related sets of data. For example, the filter and sort features have been improved and are automatically enabled now for Excel tables. You can apply multiple selections and even filter and sort by color (e.g. rows highlighted in green) and conditional format (e.g. by KPI).
- Advanced built in formulas automate the creation of even the most complex calculations. Using Excel tables you can now even refer to cells based on user defined column and row names (e.g. Northeast, Southwest, etc.) rather than having to figure out the cryptic cell/column numbering (e.g. A1).
- Data tools for identifying and removing duplicates, consolidating data and performing what-if analysis improves how you work with large sets of data and obtain meaningful insight from it.
- Custom Office Business Application by partners and customers for building solutions on top of Excel. These could range from advanced Visual Studio .NET applications to basic macros. Since Excel uses the Open XML industry standard file format, Excel documents can be programmatically built and accessed from various tools.