Hi James,

You have a few options.

1. Use Power Pivot to store the data. In fact, put the source data in a separate file and get it with Power Pivot, that way it's only in the file once, i.e. in the Power Pivot model. Plus Power Pivot is more efficient at compressing data than Excel.

2. If your historical data is in its own tab i.e. it's in one of the 2 tabs you mention, then you could use a PivotTable to summarise the data and then delete the sheet containing the source data as the data will now be stored in the Pivot Cache, so there's no need to also have it in the worksheet.

3. Similar to option 2; move all of your source data to an external file and use PivotTables to analyse it. That way the data is only stored in the file once i.e. the Pivot Cache.

4. Use Power Query to group the historical data into less rows. Depending on the level of detail, this may not result in much compression.

I hope that gives you some ideas.

Mynda

Hi Mynda,

Thanks so much for the reply, these are very interesting options.

So, the options you have mentioned above, will there be any issue with other users using the dashboard, because this is shared with other Managers in the building.

Thanks again, I will review when I return to work tomorrow morning...

Hi James,

If you use the Power Pivot option then the other users also need a version of Excel that supports Power Pivot, and preferably the same version as yours because it is not backward compatible. i.e. Power Pivot models built in Excel 2013 or later will not work in Excel 2010.

The other options will be compatible with other users.

Mynda

Thank Mynda!