How to shrink an Excel when connecting to a DB

November 12, 2013

no comments

A colleague in the IT department wanted to send his clients a report in Excel which was actually a pivot table on data from their DWH. He created the file easily enough. He defined on the Data tab in Excel a connection from SQL Server. He chose to connect to a specific table in the relevant DB. He could then edit the connection he created through the Data tab > connections > choosing the relevant connection and then properties and then edit the query on the Definition tab, through the Command Text box.

 

Excel Small Shrink

 

He then went into the Insert tab and chose Pivot Table using an external data source, the one he just created.

He got the pivot table he wanted, but the data caused the Excel file to be too big to send through the email.

We could see from copying the pivot table as “values” that the data stored from the DB in the Excel file is the reason for it being so big. So we need to take that part out. So where is that?

Go to the Pivot Table Options tab (which shows when you’re on the pivot table), click Options and on the Data tab uncheck the Save source data with file.

 

Excel Small Shrink

 

If your users will need to change anything in the pivot table, they can refresh the connection, which will make the file connect to the DB and read the data. The file shrunk getting resized from a few MB to a few kb.

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>