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.
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.
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.