I have run into this problem a few times and it always takes me a while to remember the solution.
Installing PowerPivot on Excel 2010 appears to be successful, but the tabs do not appear. When I tried to add it directly in the Add-Ins section it still gives an error message “Not loaded. A runtime error occurred during the loading of the COM Add-in.”. The solution for me has been to install VSTO 4.0 Runtime.
See this thread on MSDN for the details