Hi Could you please help me with a PowerPivot question?

The data file in Excel format is of huge size 500M+. It’s the source data and stored on OneDrive.

I want to create a Pivot table based on above source file and save it as a separate file. The final size shall be much smaller.

I tried hours exploring the use of PowerPivot with file stored on OneDrive, also Googled and read through some materials, but still can’t get the right way and even don’t know how to import the data properly.

Where can I find the tutorial and step-by-step guide of it?

Thank you.


There are few methods to use the Excel File stored on ONE DRIVE.

  1. Connect to OneDrive.Live.Com using your Microsoft ID. You find attached File Icons, Right Click and hit either Open in Excel Online option or Open in Excel or Download.

  2. Other is, load Excel, click File Open command, you find One Drive - Personal Icon, just Click it and proceed.

After you open the File, first create Pivot Table. Then to work with Power Pivot, portion of the File can be saved as Data Model. Which is always lined with Source Data range.

I do hope this definitely help you, in case my solution differs or you still find problem, just drop the comment.

  • 1
    Nice to hear from you @MarkK ,, Keep asking. – Rajesh S Jan 4 '18 at 8:33

Just in case somebody needs the steps. Here's what's working for me.

For example I am on the webpage of OneDrive. Sync the OneDrive so to ensure the files are visible on my explorer (folder), and also Open OneDrive from Excel.

The way I am using:

  1. Open a blank work book
  2. Power Pivot > Manage
  3. (In the new window) From Other Sources, choose (Text Files) Excel Files and Next.
  4. In the Table Import Wizard window, Browse Excel File Path
  5. Find and choose the file in OneDrive location.
  6. Test Connection, and it shall tell “Test Connection Succeeded”
  7. Next
  8. Choose the Source Table wanted
  9. Finish
  10. (It takes some time for loading)
  11. Then it tells the importing is ok, Close

It shows the data is loaded.

  1. On the button ribbon, click PivotTable
  2. Choose flattened PivotTable
  3. Choose to create a New Worksheet, “OK”

Then you can work with a most likely regular PivotTable. When this new Excel file is saved separated, it’s much slimmer than the data source file.

  • 1
    Wonderful job @markK . – Rajesh S Jan 4 '18 at 8:34

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.