Important Database Concepts - Microsoft Business Intelligence Tools for Excel Analysts - page 4

Traditional Limits of Excel
and How Databases Help
Managers, accountants, and analysts have had to accept one simple fact over the years: Their analyti-
cal needs had outgrown Excel. They all met with fundamental issues that stemmed from one or more
of Excel's three problem areas: scalability, transparency of analytical processes, and separation of data
and presentation.
Scalability
Scalability is the ability for an application to develop flexibly to meet growth and complexity require-
ments. In the context of Excel, scalability refers to Excel's ability to handle ever-increasing volumes of
data. Most Excel aficionados are quick to point out that as of Excel 2007, you can place 1,048,576
rows of data into a single Excel worksheet. This is an overwhelming increase from the limitation of
65,536 rows imposed by previous versions of Excel. However, this increase in capacity does not solve
all of the scalability issues that inundate Excel.
Imagine that you're working in a small company and using Excel to analyze your daily transactions. As
time goes on, you build a robust process complete with all the formulas, PivotTables, and macros you
need to analyze the data that is stored in your neatly maintained worksheet.
As your data grows, you start to notice performance issues. Your spreadsheet becomes slow to load
and then slow to calculate. Why does this happen? It has to do with the way Excel handles memory.
When an Excel file is loaded, the entire file is loaded into RAM. Excel does this to allow for quick data
processing and access. The drawback to this behavior is that each time something changes in your
spreadsheet, Excel has to reload the entire spreadsheet into RAM. A large spreadsheet takes a great
deal of RAM to process even the smallest change. Eventually, each action you take in your gigantic
worksheet will result in an excruciating wait.
Your PivotTables will require bigger pivot caches (memory containers), almost doubling your Excel
workbook's file size. Eventually, your workbook will become too big to distribute easily. You may even
consider breaking down the workbook into smaller workbooks (possibly one for each region). This
causes you to duplicate your work.
In time, you may eventually reach the 1,048,576-row limit of your worksheet. What happens then? Do
you start a new worksheet? How do you analyze two datasets on two different worksheets as one
entity? Are your formulas still good? Will you have to write new macros?
These are all issues that need to be dealt with.
You can find various clever ways to work around these limitations. In the end, though, they are just
workarounds. Eventually you will begin to think less about the most effective way to perform and
present analysis of your data and more about how to make something “fit” into Excel without break-
ing your formulas and functions. Excel is flexible enough that you can make most things “fit” into
Excel just fine. However, when you think only in terms of Excel, you're limiting yourself, albeit in an
incredibly functional way.