Top Three Things Not To Do in Excel

Let’s face it: We all have seen a crazy Microsoft Excel spreadsheet or encountered one of its dreaded “Not Responding” messages. Unfortunately, the flexibility and ease of Excel makes it the ideal candidate for inappropriate use and widespread abuse.

As the most widely used analytical tool in the world, Excel has indeed come a long way since the days of Visicalc and MultiPlan. Modern Excel 2013 and the latest Power BI add-ins do sizzle in demonstrations, but there are analyses that simply do not make sense to use Excel for today. What are these, and when does Excel become a liability by limited critical insights? In this article, we explore three scenarios where Excel is clearly NOT the right tool for the job and share how other data discovery tools are being successfully used in its place.

1. Big Data Analysis

Originally introduced in Excel 2010, Power Pivot opened up a new world of personal, “mash-up,” OLAP analytics for Excel users. Don’t confuse Power Pivot with pivot tables. Pivot tables are summarized data displays in Excel worksheet cells whereas Power Pivot is a personal, in-memory, Analysis Services OLAP cube application. Power Pivot allows for analysis of data sets far greater than the historical Excel worksheet’s 1,048,576 row maximum. Although the frustrating worksheet row limit still remains in Excel 2013, the amount of data that you can analyze in Excel with Power Pivot is only limited by your operating system and the amount of available memory on your computer for in-memory data copies.

While Excel Power Pivot is a significant improvement for personal analytics, the personal Analysis Services OLAP cubes are often not. Due to misunderstandings of the technology, and a steep dimensional modeling learning curve, they can be terribly misused. With the enhanced Power Pivot capability to combine and investigate much more data within the comfort-zone of Excel, data analysts are now inappropriately trying to copy entire data warehouses, unintentionally taking spreadsheet messes to a whole new paradigm and running into all sorts of unexpected issues. Some examples:

• Memory Limitations

If you try to use Excel Power Pivot with a 32-bit operating system, you will be disappointed by the small amount of data that can be analyzed—and quite possibly crash Excel and lock your system. In 32-bit operating systems, Excel process memory space is limited to two gigabytes. That space gets concurrently shared by Excel workbooks, Power Pivot data models, and any Excel add-ins, further reducing the amount of space for Power Pivot data. With the operating system and needed default computer background tasks running, little memory is freely available for Excel processes and Power Pivot data copies. More often than not, the dreaded “Not Responding” message appears when trying to use Excel for analytics on a 32-bit operating system.

If you have a 64-bit operating system, 16 or 32 free gigabytes of RAM, a lot of patience, and a few hours of free time to load data into the latest version of Excel’s in-memory Power Pivot model, you could theoretically put over 100 million records into it. Without a doubt, that is a game-changing Excel capability. However, most organizations today do not provide premium memory computers for running Excel 2013. A few groups have upgraded to 64-bit operating systems, but having all prerequisites for large data set analytics is highly unlikely. And just because you can does not at all mean that you should! Excel Power Pivot is not a replacement for data warehouses or even mainstream data discovery tools.

• Connectivity Limitations

One of the significant differences between modern Excel 2013 and Power BI, and the other in-memory, data discovery analytics offerings is the lack of Power Pivot direct connectivity and a restrictive requirement to copy data to Excel. In this day and age, copying millions of records doesn’t make sense, and it’s not secure. Contemporary data discovery tools provide optional use of local, desktop in-memory data engines or direct data source connectivity to databases and data warehouses. This one aspect alone separates the toys from the tools because we live in a world with billions of records and petabytes of data that continue to grow every single day.

Figure 1: Excel Power Pivot in-memory model local storage location

• Visualization Limitations

Other common problems with using Excel 2013 Power BI include inflexible data visualizations, inadequate chart selections, and limited data point rendering capability. Visualizing data to look for trends, patterns, and exceptions in large data sets is not an uncommon necessity. If you can only view a small sampling of data in an Excel chart, you could be overlooking or missing critical decision-making information. Native Excel 2013 charts are incapable of plotting data sets of a few hundred records. The latest Excel Power View release can only render 1,000 data points before randomly sampling your data. Although the brand name Power BI infers a strong analytic solution, Power BI is not powerful when compared to leading data discovery offerings available on the analytics market today.

•  
  

  Figure 2: Excel 2013 Power View example large data set rendering results

  Analytics with Large Data Sets

Data discovery solutions are exceptionally popular since they can efficiently and effectively deliver the true power of information to the masses. When using a data discovery tool that is capable of rendering large data sets, minor market shifts and outlier situations—which otherwise could go undetected until it is too late—can be quickly seen and acted upon. Present-day analytic needs far surpass what can actually be done with Excel Power BI. For competitive analytic depth and breadth, data savvy organizations increasingly rely on dedicated solutions. This video provides a glimpse into a few key differences, which are particularly important because, to uninformed buyers, the end-result charts often look similar and vital functionalities can be overlooked or misunderstood.

2. Enterprise Dashboards

Another typical mistake is trying to use Excel and Power BI to replace enterprise dashboard applications. Although low cost from a licensing perspective, Excel dashboards are expensive with regards to development inefficiency, security, reliability, and capability. What might literally take a few seconds with two or three clicks in a data discovery tool can take hours or longer to generate with Excel and VBA.

Although it is easy to create simple charts and tables in Excel and Power View, you usually end up with multiple versions of the truth floating around. Why? Excel dashboard tweaks and variations are usually developed by users to overcome Excel’s visualization and interactivity limitations. Unlike data discovery tools that are designed specifically for visual analytic activities, Excel and Power View lack key features for flexible axes control, combination charts, business logic, drill-downs to view details, robust filtering, parameters, alerting, actions, and mobile delivery.

3. Visualized Map Data

If you have not seen it yet, Excel 2013 Power Map is nothing less than impressive! If you always have reliable Internet connectivity, need only basic data mapping, and don’t want to share your maps with others, Power Map may be a perfect option. Power View Bing Maps are also nice for simple, online mapping scenarios.

But if you require offline maps, advanced map displays with WMS, custom shapes, indicators, and layering, or if you want to distribute your maps, then data discovery tools are a much better fit for the task. Most data discovery solutions have exceptional, fully interactive, geospatial mapping like automatic geocoding, custom shapes, spatial searching, clustering, geofencing, distance and route calculations, and choropleth mapping.

Better Decisions with Analytics

Tom Davenport, an analytical thought leader and author of “Competing on Analytics,” said: “Decision making techniques and technologies will be the next competitive battleground.” We are already seeing organizations value data more than ever in this fast-paced, globally competitive, uncertain world. To thrive in today’s fickle economy, advanced analytics have become a necessity. Since Excel 2013 and Power BI lack advanced analytics capabilities, reliance on Excel for analytics has become an organizational liability.

There is an old saying: “Friends don’t let friends use Excel for statistics and advanced analytics.” That still remains true in 2014. When Excel is compared side-by-side with data discovery tools for advanced analytics use cases, it quickly pales, fails, and clearly shows the crucial informational differences.

Figure 3: Visual predictive analytics

In this article, we walked through a few common, inappropriate uses of Excel and Power BI for true analytics. Although Excel and the new Power BI add-ins are reasonable solutions for basic needs, they were not designed to replace data warehouses, nor are they the right tools for serious analytic, data visualization, and dashboard use cases. Data discovery tools like have become mainstream analytical assets that provide a substantial competitive advantage. Don’t be left behind or continue using an outdated Excel approach because you already own it or have always used it in the past. The best way to fully understand the potential power of applying modern analytic technologies is to evaluate these tools with your own data sets. You won’t know what you are missing until you personally experience the difference.

About the author: Steve Leung is Director of Product Management for TIBCO Spotfire. He has over 15 years of experience in the enterprise technology with strong experience in financial services. Prior to joining TIBCO Software, Steve has been with companies such as Oracle, Autonomy, BEA and webMethods helping global organizations design and build complex enterprise architectures. Steve is a business technologist that has held multiple roles as a consultant to business development helping customers achieve business value from technology. Steve holds a Bachelor’s degree in Computer Science from Binghamton University.