Say that you have a pivot table and need to send the file via e-mail. Use the trick in Episode 857 to dramatically reduce the size of your Excel workbook.
Transcript of the video:
Hey, alright, welcome back to the MrExcel netcast, I'm Bill Jelen.
Here's an amazing trick for those of you who use Pivot Tables.
I'm going to open up this file, you'll see that its 986kb, and this file has 5,000 rows of data on one sheet, and then a Pivot Table on the other sheet.
I need to send this out to a whole bunch of people, so what I'm going to do is, I'm going to select the Pivot Table, or use Ctrl+C; go to a New Workbook; and, amazingly, paste the Pivot Table.
Now, I'll save this: File; Save As; I'll call it Podcast857b; and we'll close everything-- so I'll hold down the Shift key, do File, Close All.
Now, here's what's amazing: When I go to open this file, you'll see that the file is a third of the size, now, of the other file.
857b, open it up, and I can still Pivot-- I can still add new fields in, take fields out, everything's there.
Why?
Because the data for the Pivot Table is actually stored in the cache, and so, basically, when we send that data and the Pivot Table out, we're sending the data out twice.
So, in this case, I was able to create a big savings.
Now, you say, "Well, what if we really need to see the detailed data?" Well, there's a way to always see all of the data in the cache, and that's basically the double-click on the Grand Total; so the person who receives the small file, double-clicks on the Grand Total, and will get a brand new worksheet with all of the original records-- well, 6,000 records in this case.
An amazing trick copying just the Pivot Table to a new workbook when you have to set it up.
Well, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
Here's an amazing trick for those of you who use Pivot Tables.
I'm going to open up this file, you'll see that its 986kb, and this file has 5,000 rows of data on one sheet, and then a Pivot Table on the other sheet.
I need to send this out to a whole bunch of people, so what I'm going to do is, I'm going to select the Pivot Table, or use Ctrl+C; go to a New Workbook; and, amazingly, paste the Pivot Table.
Now, I'll save this: File; Save As; I'll call it Podcast857b; and we'll close everything-- so I'll hold down the Shift key, do File, Close All.
Now, here's what's amazing: When I go to open this file, you'll see that the file is a third of the size, now, of the other file.
857b, open it up, and I can still Pivot-- I can still add new fields in, take fields out, everything's there.
Why?
Because the data for the Pivot Table is actually stored in the cache, and so, basically, when we send that data and the Pivot Table out, we're sending the data out twice.
So, in this case, I was able to create a big savings.
Now, you say, "Well, what if we really need to see the detailed data?" Well, there's a way to always see all of the data in the cache, and that's basically the double-click on the Grand Total; so the person who receives the small file, double-clicks on the Grand Total, and will get a brand new worksheet with all of the original records-- well, 6,000 records in this case.
An amazing trick copying just the Pivot Table to a new workbook when you have to set it up.
Well, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
