How To Handle Large Data Sets In Excel

Working with Huge Datasets

Norman'due south calculator has a fast processor and 32 GB of RAM. For statistical purposes, he often needs to employ meg-line worksheets. He's still using Excel 2007, and it doesn't piece of work well after it'south run awhile with these huge workbooks. (In fact, it frequently crashes.) Other times, later startup, it works OK. Norman wonders what he can do to get more than reliable operation when working with such large datasets. He wonders if Excel 365 work better, or perhaps more RAM.

Allow's address the hardware outcome showtime. Your arrangement is probably sufficient for working with large data sets in Excel. Nevertheless, if you are planning on updating your hardware, it is always a good thought to get more than RAM. This allows Excel (and other programs) to practice more data manipulation in memory rather than needing to swap the data between retention and the deejay enshroud on the difficult bulldoze. Bottom line: Don't upgrade your organization but for this 1 reason, but if you are upgrading for other reasons, then get a system with lots of RAM.

Now, on to Excel. The version of Excel you are using (Excel 2007) has a maximum retention allocation of 2 GB per instance, and do not appear to fully release all memory used exist a workbook that was previously opened. This could be the cause of your "works at first, just not afterward" trouble, and the only way to work around it is to periodically shut and restart Excel.

In add-on, Excel 2007 is only available in a 32-scrap version. If you update to a subsequently version of Excel, you'll be able to install a 64-bit version of the program. This is important because information technology allows you lot to accost more than memory, which means easier manipulation of larger data sets.

Once you upgrade your version of Excel, yous'll want to follow these steps to brand sure that Excel is utilizing the ability of your CPU every bit well as information technology can:

Brandish the Excel Options dialog box. (In Excel 2010 and later versions display the File tab of the ribbon so click Options.)
At the left side of the dialog box click Advanced.
Scroll downward until yous come across the Formulas section of the options. (See Figure 1.)

Figure 1. The Formulas section of Excel'due south avant-garde options.

Click the check box next to the Enable Multi-Threaded Calculation pick.
Make sure the radio button next to Utilize All Processors On This Calculator is selected. (This choice is simply accessible after yous complete pace 4.)
Click the OK button.

Subsequently taking these steps you may all the same run into problems with your data if the worksheets contain a lot of formulas. It makes sense that the more than formulas you take—particularly if those formulas involve volatile functions that must be recalculated constantly—the college "load" you place on Excel. Anything you can do to reduce the number of formulas (and volatile functions), the more responsive you'll find Excel to exist.

Now allow's await at the advisability of using Excel itself. The underlying question is whether you really demand to have such large worksheets in Excel in the get-go place. Depending on the nature of the data with which you are working, y'all may discover it improve to load your data into a database (such as Admission, which is provided with many versions of Microsoft Office) so utilise the database plan to practise your information summaries. You could, at that point, copy a much smaller subset of records into Excel to exercise your terminal analysis or formatting for presentation purposes.

Another approach once the data is in the database is to employ the capabilities of Excel to access the data. For example, since you (hopefully) updated to a later on version of Excel, yous could use Ability Query (chosen Get & Transform in Excel 2022) or Ability Pivot to establish a connexion to the database to process the data, which decreases the overall processing load on Excel.

ExcelTips is your source for cost-constructive Microsoft Excel training. This tip (4809) applies to Microsoft Excel 2007, 2010, 2022, and 2022.

Writer Bio

With more than than 50 not-fiction books and numerous magazine manufactures to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Assembly, a figurer and publishing services company. Learn more nigh Allen...

More than FROM ALLEN

Multiple Pages Per Sheet

You can save on newspaper on your printouts by instructing Word to print multiple pages on a unmarried sail of newspaper. In fact, ...

Discover More

Aligning a Paragraph in a Macro

If y'all are applying formatting from within a macro, you may want to modify the alignment of various paragraphs. Here's ...

Discover More

Opening a Workbook to a Specific Worksheet

When y'all open up a workbook, Excel displays the worksheet that was visible when the workbook was last saved. You may desire, ...

Discover More