“Excel running slow” is a common complaint for more and more users. Excel’s ubiquity and ease of use are its strengths, but Excel runs slow when tackling large data sets. We’ve said it before: the simple fact is that Excel’s framework for data processing wasn’t made for big data. It’s not just that Excel becomes error-prone with big data sets. When you add complicated functions to big data—think VLOOKUP or pivot—Excel can slow to an excruciatingly slow rate or even crash, making efficient business intelligence impossible.
With hundreds of millions of users, Excel will no doubt remain the most common reporting tool for those dealing with small data files. However, a recent survey of 6,000 financial professionals and accountants by the Institute of Management Accountants (IMA) and FinancialForce shows a growing trend of abandoning the product due to Excel running slow. According to the study, 60% of U.S. companies are still using spreadsheets, while 21% are planning to implement another software solution. The most common reasons for the departure were—you guessed it—Excel running slow during critical analyzes and error-filled spreadsheets.
Why is Excel so slow? Here are 4 reasons.
Petabytes of data have become the norm, not the exception. Excel wasn’t built to handle massive data sets and enormous data lakes, and that’s why Excel’s running slow. Here are just four examples:
Manual Calculation Mode:
Excel defaults to recalculating all formulas in real time as soon as you enter them, which makes it obvious when Excel running slow. Changing to a manual calculation—refreshed only when the F9 key is hit—can assert some control over when Excel recalculates.
In order to switch to Manual Calculation Mode, simply:
1 Under the Formulas tab, select “Calculation Options”
2 Select “Manual”
Though this is a great start, it’s important to note that enabling manual calculation will not speed up Excel’s ability to perform calculations.
Conversion of Formulas into Static Values:
On large datasets, you’ll notice Excel running slow any time you apply a formula. To accelerate time to insight, you can copy the column with the formula and paste it as values, and then delete the formula column.
To copy any column with a formula and paste it as values, simply:
1 Click the top cell of the column that you’d like to copy.
2 Press CTRL+SHIFT+ the down arrow key to select the column.
3 Click “Copy” from the Home Tab
4 When you’re ready to paste, select the “Paste Values” option, as shown below.
This is a great solution, but there are risks: you could lose the rules that created these newly-pasted values, determining how you got to your final numbers will be next to impossible for outside auditors, and all future maintenance will have to be completed manually.
The VLOOKUP or PIVOT Function:
These extremely time-consuming functions will always slow down your workbooks when dealing with large data sets. Is it possible to not to use them? Given that VLOOKUP and Pivot Function’s are the highest functioning techniques used to manipulate data in Excel, most businesses do not consider this an option.
However, there are a few workarounds. One of which is using a combination of the INDEX and MATCH functions. Using a combination of INDEX and MATCH can be a more efficient solution because instead of analyzing an entire table, the use of these functions will only search your selected columns.
For example, the below image shows a datasheet of member information. We’re trying to find the phone number of a specific member (member ID 1416290). Since we don’t know exactly where the member is on our datasheet, here’s how we’ll use the MATCH and INDEX function to find this phone number:
1 First, I’ve randomly chosen P2 as the cell where our phone number will be generated.
2 The general structure of nesting MATCH with INDEX looks like this: =INDEX(array, MATCH Function).
3 I’ll start by writing =INDEX(I2:I10941 which means that we’re looking in all of column I, the column that contains member phone numbers.
4 Next, I’ll include our MATCH function and specify that we’re looking for member ID 1416290, which can be found in column A.
5 The full formula reads: =INDEX(I2:I10941, MATCH(1416290, A2:A10941))
6 When we click enter, we can see that cell P2 now contains the exact phone number that we were looking for! I’ve highlighted the row that our formula extracted this number from below.
Cartesian Limits:
Finally, the last reason that Excel can run slow is because of its size limit. Excel maxes out at 1 million rows. That means that not only will Excel be running slow, but you’ll be running slow as you have to split your time between multiple spreadsheets. And you could miss the big picture as you try to splice it all together. There’s not much you can do about this limitation—it’s just a fact of working with Excel.
Frustrated by Excel Running Slow? Designer Cloud Can Help.
Designer Cloud has employed a better approach to data wrangling that leverages a best-in-class data processing engine, while also offering interactive feedback on all data transformations. Designer Cloud’s high-performance data wrangling engine, Photon, enables faster feedback on greater volumes of data, which leads to huge productivity gains for all of our users—which should be great news to anyone tired of Excel running slow.
Designer Cloud’s Intelligent Execution Architecture maintains support for a growing list of modern data processing engines, such as Spark, Google Data Flow and MapReduce. When you open a large dataset, the application automatically presents compelling visual representations of your data. When you brush over or click onto certain elements, Designer Cloud will suggest logical transforms that you can select, edit, or build from scratch with real time feedback. The process is seamless for users, who can also leverage Designer Cloud’s at-scale profiling to examine the validity of the resulting output data set and search for any remaining data inconsistencies.
Leveraging an intelligent visual interface and powerful compute framework, Designer Cloud has been able to provide the best user experience for analysts—keeping interactions agile and reactive—while delegating the heavy processing to the right engine. All of this means you get to stop waiting for Excel, running slowly, and get back to the fun part of your job: finding amazing insights.
To learn more about Designer Cloud, start working with a free version of our product today.