Why is my system so slow when I have so much RAM?
Might some of these referenced spreadsheets be on a LAN? If so, what sort of connection do you have to the files on the LAN (100mbit, Gigabit, wireless, etc)?
Excel is very "chatty". Especially when you're dealing with reference spreadsheets and data spread out across various resources, it can take a lot of communication for the application to send and receive necessary information. If most of the files are on network shares and network drives, this may explain at least a chunk of the slowness you're experiencing.
Further, regardless of how much memory you're using, you'll probably find excel.exe usually doesn't take as much memory as you expect it to take. Are there other processes that are running on your computer that aren't necessary? Lots of taskbar icons (next to your clock) that you have no idea how they got there or what they're for?
Finally, lots of companies leverage Excel far beyond it's best functions, like it seems your company does (why, oh why don't they use a database when they need a database and leave Excel to the simple data recording and processing functions????), I've found that comparing the speed of Excel on our older/slower machines (4 year old IBM desktops 8215 and 9645) and newer/faster machines (6month old desktops, 3269, 9964), and there is no significant difference.
Depending on how the macros and code connecting the spreadsheets is written, there may be what are essentially hard limits to how fast that spreadsheet can collect and process the data it needs.
All you can do is identify and mitigate all the possible points of slowness you can, and hope for the best.
Windows XP doesn't do a very good job of using more than around 1GB of RAM. Some applications may use more, but no single application can use more than 2GB at a time, at least by default - that's the largest virtual address space an application can see. There's a mode that allows a 3GB address space, but that can cause compatibility issues for a lot of software.
If your machine is only using 0.25GB, that seems pretty wierd - unless memory isn't your bottleneck at all.
If your machine "appears to hang", my guess would be that you're doing a lot of hard disk access. That may make the Windows XP memory use issue relevant - Windows 7 is much more aggressive about predicting what you'll use in the near future and caching it. Windows XP won't even use that much memory for the cache, let alone try to do predictive caching, so you're much more likely to "thrash" the hard disk.
If that's the problem, some options to consider are...
Try using a ramdisk, to minimise the use of the harddisk. Put the documents there, and ensure any temporary files are also stored there. There are even some ramdisks available that will use RAM outside of the 4GB that 32-bit Windows XP can access.
Use a raid array with striping, to improve your hard disk performance.
Use a solid state hard disk - but be aware that if your application is doing a lot of writes, that may cause excess wear and tear for that SSD. Also, Windows XP isn't optimised for SSDs - look out for updates to support Trim.
Basically, the idea is either (1) to force your system to use RAM, or (2, 3) to invest in hardware that will reduce your performance bottleneck.