If you are getting the Data source reference is not valid while trying to create a pivot table in Microsoft Excel, see this post for the fix.

If you cannot open an Excel file and see Excel cannot open the file because the file format or extension is not valid error, see this post.