Log In
Register
Home
October 25, 2013
Offline
Hi All,
I have a number of dashboards that I have created after going through Mynda's dashboard course.
My raw data I add to the dashboard daily is now getting to over 38,000 rows each on 2 tabs and is making the excel spreadsheet size to 15meg. The data ranges from 2016 through to present date.
I want to keep the year over year comparison, but is there are way to somehow consolidate each year 2016 and 2017 to a small number of rows and then delete these rows after consolidation but still show year over year comparison via the pivot tables and charts?
Thanks
July 16, 2010
Offline
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
October 25, 2013
Offline
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...
July 16, 2010
Offline
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
1 Guest(s)
