If you've got a big Excel file (e.g. more than 10 mb) and you don't have tens of thousands of rows, you may want to see what you can do about bringing that size down.
If you have lots of data, you may want to consider linking it using PowerPivot, although that's a topic for a future post.
If your Excel file is unnecessarily large and you're not sure why, there are three things you can do and check for to bring it down in size.
1. Check for objects
If you copied data from a webpage or somewhere that had images, you could have objects on your spreadsheet without ever realizing it. Many times they'll appear invisible.
This has happened to me before and there's a quick way to check and Delete them if you do have them. Simply click F5, select Special, and then select Objects and hit OK.
If there aren't any in your sheet you'll get a message that none were found. If you don't get that message then clicking delete will remove those that are on the sheet.
This will only look on the individual sheet you're on so you may want to try this on all your sheets just to make sure.
2. Removing excess rows.
This on many cases is the culprit. People move data around and formatting is left behind and Excel holds data sometimes until the very last row. Again, this is an invisible problem that you won't easily spot.
What you can do to determine if you have this problem is click on a cell in your data set somewhere and click CTRL + END. This will take you to the very bottom of your data. If it stops where it should, then you're fine. If it takes you several thousand rows farther than you expect, or worse - to the bottom, then there's a good chance you've found what's making your spreadsheet all that bigger.
To fix the problem, select the rows from the very bottom all the way up to your last row. Delete these rows and then click save. The file size won't update until you click save.
Tip: if you have lots of worksheets to go through, user CTRL + PG UP/PG DOWN to cycle through the different sheets and then hit CTRL + END on each one and you can quickly see if any sheet has the problem.
3. Remove unneeded columns
If you really just have too much data, consider deleting excess columns. While in many cases people think of removing unneeded rows, they neglect the impact that columns have. If you've got tens of thousands of rows, even deleting one column will remove that many data points. Multiply that by how many columns are unnecessary and the data savings will quickly add up, and your file size will see a noticeable decrease.
These are three of the most common reasons your file might be bigger than it needs to be. Often times people think having a macro is going to do it, but that's not the case. Usually it's just having lots of data and doing calculations on all that data will take a big chunk of your computer's resources
