Refreshing Portfolio Slicer Excel 2010 data
Before refreshing data in Excel, please make sure that:
- You have all 4 data files in the folder from where Excel is configured to load data. If you are using scripts that are linked from this website - please make sure that you execute these scripts BEFORE EACH REFRESH in Excel.
- Your source Excel tables do not have "empty rows" and do not have "bad data".
Steps to refresh Portfolio Slicer data are different in Excel 2010 and Excel 2013. Instructions on how to refresh data in Excel 2013 are here.
Refreshing Portfolio Slicer data in Excel 2010
Open Portfolio Slicer workbook in Excel 2010 and go to menu item “PowerPivot” and then click on “PowerPivot Window” button.
When PowerPivot window opens it might start updating linked tables, This can take 10-60 seconds. Please wait. When update completes, from “Home” menu click on bottom part of “Refresh” button and then choose option “Refresh All”.
You will see “Data Refresh” window open that will show tables loaded from external files. You will see number of records loaded from each file.
When refresh completes, click on the “Close” button in “Data Refresh” window and then close “PowerPivot” window. Then select any Pivot Table report, do right mouse click and choose menu option “Refresh”.
At this stage all reports will be re-calculated. This step can take about 40-60seconds.
This is what happened while you did data refresh following steps above. In Excel 2010 Portfolio Slicer data is updated in 3 stages:
- When you open PowerPivot window PowerPivot updates data from linked tables that are in this Excel workbook (Account, Symbol, Transactions, etc). At this point data is updated just in PowerPivot data model - no changes are visible in Excel reports.
- When in PowerPivot window you click on "Refresh" button, data from flat files is loaded into PowerPivot data model. Again, no changes are visible in Excel reports.
- When on any Pivot table you do right mouse click and click on menu item "Refresh", then data from PowerPivot model is read and all reports are re-calculated. Just after this you can see reports with updated data.
Steps above assumed that your PowerPivot workbook is configured with "Automatic" Linked Table Update Mode (default). You can check settigns by selecting any linked table in PowerPivot window (like Symbol, Trans) and then from "Linked Table" menu clicking on "Update Mode" button. There you will see current update mode and will be able to change update mode.
In automatic linked table update mode PowerPivot decides when linked tables needs to be updated every time you open PowerPivot window or switch between Excel and PowerPivot window. Many times PowerPivot will update linked tables even when there is no need to do so. If you just for example adding records to Trans table, you can choose switch to "Manual" linked table update mode, do changes to Trans table, then visit PowerPivot window, select Trans table and click on "Update Selected" button in "Linked Table" menu.
Such one table updates will be much faster and then you will need just update Portfolio Slicer reports by selecting any Pivot Table and choosing "Refresh" menu item.