There is a trick for those who are wondering if you export directly to excel and highlight, how come in the next refresh the pivot table misses some records.

 

Step 1

  • Export to a dynamic workbook
  • Use the save as function
  • Then open excel
  • Then open the xml file

Step 2

  • You will see a credential, type in the user name, password and click on refresh from CRM

 

credentials

 

Step 3

  • Click on insert pivot
  • Highlight all of the values
  • You will see it change from the named range Opportunities:A$:AZ54 to the connection see below
  • Then it will dynamically expand

spreadsheet