Microsoft Excel 2007 for Business Intelligence

Business Intelligence technologies allow organizations to collect, organize, and analyze vital company data in order to make better informed decisions.  It may come as a surprise that the current leader in BI technologies is Microsoft Excel, and there are more than 150 million Excel users worldwide.  By using only Excel, you can have Ad-hoc Business Adhoc_BI-300x194Intelligence, also known as Personal BI.


  • Excel 2007 (Excel 12)
  • ERP/CRM database such as Microsoft Dynamics GP or Dynamics CRM
  • Some basic knowledge of SQL Views (or access to someone who can help)
  • A hypothesis or theory you would like to prove or disprove

Here are 5 reasons why people don’t use Excel for BI and how they can be debunked.

  1. Lack of confidence in the data.This is true if you connect to a SQL view that isn’t validated.  Use these best practices:
    • Isolate a known result and test output from Excel
    • Use naming conventions such as ExcelView FlatSales
    • Use an Excel data connection library
    • Use data governance- appoint Excel connections manager
  2. Can’t trust worksheets as they move around.  This is true if you don’t refresh the data connection.  Use these best practices:
    • Set all connections to refresh when opened
    • When using a data connections library, the connection is stored on the server
    • When a worksheet is e-mailed/shared, it will automatically seek the server connection file and refresh the data
    • Limit users who can modify connection files
  3. Takes too long to refresh the data set.  This is true if you don’t use parameter queries.  Use these best practices:
    • Use parameters to limit the data as it comes in.  For example, use a month parameter for a monthly report or limit sales people so they can only see their customers
  4. Excel is just a spreadsheet.  This is true if you don’t know the true purpose of Excel 2007.  Excel 2007 was designed as an ad-hoc BI tool.  Use these best practices:
    • Start by providing pre-built pivot tables
    • Offer basic training in Excel data connections
    • Provide advanced Excel training for power users
  5. Excel requires many complicated calculations.  This is true if you don’t use SQL for the calculations.  Use these best practices:
    • Limit the need for users to perform their own calculations (avoid rework)
    • Assign a SQL developer the task of implementing common calculation in SQL View (i.e. Gross Profit=Sales Amt-Cost Amt)
    • Don’t overwhelm basic users with too many calculations
    • Advanced Excel users can add calculated fields to the pivot table

Excel offers even more powerful tools and techniques such as Predictive Analytics to predict a certain outcome and Data Mining to gain insight into relationships.  To learn more about using Excel 2007 for business intelligence,  view the entire “Accessing Company Data Using Microsoft Excel 2007” presentation, presented by Socius’ BI consultant, Brad Blackmore.

Learn More about Socius’ Business Intelligence Solutions