Working with Huge Datasets

2007 is no longer supported, but the question is still valid. Here are a couple of links about RAM support in newer versions of Excel.

@ RAM Support increased 32-bit Excel 2013-2016
https://www.excelguru.ca/blog/2016/05/10/32-bit-excel-memory-limit-increase/
Excel 2016 build 16.0.6868.2060
Excel 2013 build 15.0.4833.1000
Have been updated to increase RAM supported. It has been 2GB in 32-bit Win and Excel.
Now 32-bit Excel in 32-bit Win is up to 3GB and in 64-bit Win up to 4GB
Excel 2010 and earlier have not been updated.


@@ LLA - Large Address Aware capability change for Excel
https://support.microsoft.com/en-us/help/3160741/large-address-aware-capability-change-for-excel
32-bit versions of Microsoft Excel 2013 and Excel 2016 can take advantage of Large Address Aware (LAA) functionality after installation of the latest updates. (see the "Resolution" section) This change lets 32-bit installations of Excel 2016 consume double the memory when users work on a 64-bit Windows OS. The system provides this capability by increasing the user mode virtual memory from 2 gigabytes (GB) to 4 GB. This change provides 50 percent more memory (for example, from 2 GB to 3 GB) when users work on a 32-bit system.


The suggestion has already been made, so I'll second it. For large datasets like that, PowerQuery and PowerPivot are the way to go.
PowerPivot and Power View Add-Ins for Excel 2013
https://www.dummies.com/software/microsoft-office/excel/the-powerpivot-and-power-view-add-ins-for-excel-2013/
In Excel 2013, the PowerPivot add-in, introduced in Excel 2010, that enables you to efficiently work with and analyze large datasets (such as those with hundreds of thousands or even millions of records) has been made a much more integral part the program. You can easily manage massive amounts of data from many related data tables is now part and parcel of Excel 2013 in the form of its Data Model feature.
Instead of having to download the add-in from the Microsoft Office website, you can start using PowerPivot simply by activating the add-in

Use Power Query to Create Dynamic Lookup Tables from a large Data Table
https://powerpivotpro.com/2014/02/two-quick-updates-university-re-launches-monday-and-power-query-is-amazing-amazing/
The data I have is one big fat CSV exported from the LMS software. Web “data sources” are just like that. They say, “here you go, here’s your big wide CSV file. Now we can wash our hands of the whole analysis and reporting business and dump it on you.”
Making Lookup tables manually… sucks. Who wants to go through that process over and over and over? Not me.
Power Query to the rescue!

Perspectives in PowerPivot (“views”)
https://support.office.com/en-us/article/perspectives-in-power-pivot-43b4e178-3d89-486c-8acd-ebc63802fead
One of the advantages of using the Power Pivot add-in to refine a Data Model is the ability to add perspectives. Perspectives provide custom views that you define for a particular user group or business scenario, making it easier to navigate large data sets. You can include any combination of tables, columns, and measures (including KPIs) in a perspective, and you can create multiple perspectives for various reporting constituencies in your organization. Perspectives can be used as a data source for other PivotTables and reports, including Power View reports. When you connect to a workbook that includes perspectives, you can choose a particular perspective on the Select Tables and Views page of the Data Connection Wizard.



Here are some free downloads about working with large data:
9. Excel 2016 Large Data Final Report Advanced
https://www.computer-pdf.com/office/excel/589-tutorial-excel-2016-large-data-final-report.html
Download free tutorial Microsoft Office Excel 2016 Large Data Final Report, PDF book by Pandora Rose Cowart University Of Florida.

10. Excel 2016 Large Data vLookups Advanced
https://www.computer-pdf.com/office/excel/588-tutorial-excel-2016-large-data-vlookups.html
Download free tutorial Microsoft Office Excel 2016 Large Data vLookups, PDF book by Pandora Rose Cowart University Of Florida.

11. Excel 2016 Large Data PivotTables Advanced
https://www.computer-pdf.com/office/excel/587-tutorial-excel-2016-large-data-pivottables.html
Download free tutorial Microsoft Office Excel 2016 Large Data PivotTables, PDF book by Pandora Rose Cowart University Of Florida.

12. Excel 2016 Large Data Sorting and Filtering Intermediate
https://www.computer-pdf.com/office/excel/586-tutorial-excel-2016-large-data-sorting-and-filtering.html
Download free tutorial Microsoft Office Excel 2016 Large Data Sorting and Filtering, PDF book by Pandora Rose Cowart University Of Florida.



Cond- Toggle Excel Conditional Formatting On and Off
http://www.myonlinetraininghub.com/toggle-excel-conditional-formatting-on-and-off
One of the nice features of Excel Tables is the banded row formatting, which makes it easier to read and scan your data. Unfortunately Excel Tables aren’t efficient with large data sets (over 100k rows), but we can replicate the banded rows with Conditional Formatting, and we can toggle it on and off at the click of a button like this (graphic on web page is active).

My 2012 Macbook Pro bogs down to uselessness at about 100,000 rows, but that's because I must often use lookup formulas.
The one feature that has helped at all is to turn off automatic processing as I work with the table and then select a block of those lookup formulas for updating rather than do the whole column.
I use index(... match( for the lookups. Where I have had lookups in more than one column, I use use match() alone to get a row number and then index() in both the data columns I want. It saves ... a little ... time.
Eventually there is no option but to update the entire page, but I don't have to do it often.

Two things I've noticed when manipulating large datasets in Excel 2010:
1. Using Find/Replace and Copy/Paste can take a long time if I'm not careful to sort the column where the operation is taking place first.
2. The screen freezes. The fix to this is to Unfreeze the worksheet (View Ribbon / Freeze Panes) and then to Freeze Panes again.

Before upgrading to a 64 bit version of Excel, make sure that he is using a 64 bit version of windows. In order to determine this, click on the start button on the task bar and then right click on the Computer text and choose Properties. A windows will come up and the bit version will be under the System section. This will display the process type and speed, how much ram and the System Type (32 or 64 bit).

Good article and good tips from Graham too.

Updating to Office 365 would give Norman access to the latest version of Excel, I download the 64bit version although the 32bit is the default. I know PowerPivot is not available in all versions of Excel but Norman really needs PowerPivot if he is going to continue using Excel for these monster datasets so the extra spend is worthwhile.

Here is a Microsoft Office blog that discusses PowerPivot and PowerQuery.

https://blogs.office.com/en-us/2015/09/18/new-ways-to-get-the-excel-business-analytics-features-you-need/?eu=true

And of course PowerPivot and PowerQuery (and a couple other Power Tools) underpin PowerBI.

All of the suggestions above are good. However a short-term answer to ease the situation may be to stop automatic calculations during data entry, filtering, running macros, etc.
The simple macros below would help to turn calculations on and off :-

Sub CalcManual()
' Switches to Manual calculation of formulae
Application.Calculation = xlManual
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub

Sub CalcAutomatic()
' Switches to Automatic calculation of formulae
Application.Calculation = xlAutomatic
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub

Also any macros that are used could have the Application.Calculation lines inserted at the start and end.

Sub ExampleMacro()
Application.Calculation = xlManual

Macro commands

Application.Calculation = xlAutomatic
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub