I understand I'll need to re-install all the applications, but will upgrading from Windows XP (32 bit) to Windows 7 (64 bit) automatically format my hard drive removing out all my stuff? Or I'll just need to install all the appls but my files (like pictures and documents) will remain on that hard drive? (Trying to figure out if I really need to get 500GB spare space anywhere to copy my data temporarily to.)
If you do an install without formatting the drive, you will have a Windows.Old folder in the root of your system drive when the install is complete. This will have all of your old files in it. You can remove this folder with Disk Cleanup after you have retrieved your files from it.
You should really still do a backup first, just in case. External hard drives are dirt cheap.
Edit: It moves all of your files from the previous install to windows.old, so all of your files will be there and you can cut and paste what you need into your new home folder. The problem is that you need a minimum of 20GB to install Win 7 x64. If you only have 5GB of free space you should probably get more storage anyway.
As for the backup. If your files are that important that you are asking about this, you should probably have a backup anyway. Now is as good a time as any.
I would suggest you to proceed with clean install - but without formatting: boot up with something like Mini-Windows from HirenCD, create new folder, move all your files and folders to that new folder - this will take just a second, then install Windows 7. All your old data will be safe.
Minimal impact of upgrading 32 to 64 can think of will be mess with the programs: 64-bit Windows 7 uses "Program Files" for 64-bit applications and "Program Files x86" for 32-bit.