Shared Excel file with vlookup's expanding exponentially.

**jkoch1** · 01-16-2013, 03:06 PM

I have a shared excel file that I update daily with a vlookup that is expanding exponentially when after the vlookup is complete and I save. Any any given time 3-15 users are in the file when I do the vlookup. The file is about 30 columns with by 6000 rows long. I have minimal formats in the file.
I do the vllookup very quickly and then paste the results as text so the formula does not continue to run. I reduced the share history to keep down to 1 day. Despite these efforts, the file size continues to grow which leads to problems and lock outs during saving, opening, closing.

I've stumped my IT group here so I'm turning to the forums!

Any help would be greatly appreciated!

**FDibbins** · 01-16-2013, 03:32 PM

hi and welcome to the forum

30 columns and 6000 rows is not that big a worksheet, but without seeing what you are working with/doing, its hard to offer any advice

do any of your formulas include entire rows or columns?
do you have any array formulas
do you have any conditional formatting
are there any unused rows and/or columns? ie, when you press end-home, is the cell you end up in, the bottom right corner of your table, or do you end up in the middle of nowhere (you may notice that the scroll bar "handle" is really small)? If thats the case delete all unused rows and columns and save again

**jkoch1** · 01-16-2013, 04:53 PM

Thanks FDibbins. I use the foum all the time and usually find my answers in other peoples Q's and A's but couldnt find the answer to this one so i finally joined and posted.
To answer your questions:
1. my vloopups are run on 3 whole columns.(1 column = 6000 cells x 3 columns = 18000 cells) but like I said, right after i do the vlookups i paste values.
2. no array fomulas
3. no conditional fomating
4. no unused rows between the end of my data and the bottom of the sheet. (i have see the bottom row default to 1,300,000 in a previous case, but not here)

2 thoughts. 1. The change history is what is making the file so big. or 2. it has something to do with the fact that it's shared work book.

**:) Sixthsense :)** · 01-17-2013, 01:33 AM

Shared Workbooks Need Maintenance Otherwise even an 1 MB file will grow into 1 GB file. On a (Once in a day or Two) regular basis you have to unshare the workbook and save the file and apply the sharing once again (Ensure that You are the only person should get connected while doing this), which will reduce the file size considerably.

In further once you have unshared the workbook select the unused rows and columns and Press ALT+E+A+A to clear the clusters and save and Share the file for use.

**FDibbins** · 01-17-2013, 01:41 AM

Thanks Six, we appreciate your help

**:) Sixthsense :)** · 01-17-2013, 01:57 AM

@ FDibbins,

Thanks for the rep

I believe we includes me. Glad to stay here as one of the member

**jkoch1** · 01-17-2013, 11:59 AM

Thanks Guys.
I'm pretty convinced that the change tracker log is what is expanding the file.
Un-sharing and re-sharing does clear the log which in turn decreased the file size back to "normal".