Importing Data from a Pivot Table (Excel file) to a Sharepoint List

Question

I have an excel file that contains 2 tabs with pivot tables.

Is it possible to import this data into a SharePoint list? If so, what would be the easiest solution?

I considered using Power Automate, but I'm not sure if or how it would work with pivot tables?

I thought of converting my pivot table into a regular table beforehand, so I copy/pasted (Values) it, then had it formatted as a table, but because I have multiple fields in the Row section of my Pivot Table Fields, it probably wouldn't import/sort the data into the proper columns of my SharePoint list. Afterwards, I tried pasting (Formula) my pivot table using a different method, which seemed a lot better, however, when I went to create my flow, it couldn't pick up on my table (even though I formatted it as a table).

Thank you in advance for your help!

Answer 1

Hi,

You can use Power Query in Excel to import data from an Excel file with pivot tables into a SharePoint list.

Here's how you can do it-

Open your Excel file that contains the pivot tables.

Go to the "Data" tab in Excel and click on "From Table/Range" in the "Get & Transform Data" section. This will open the Power Query Editor.

In the Power Query Editor, you will see a table representing your pivot table data. Click on the "Close & Load To" button in the Home tab.

In the "Import Data" dialog box, select the option to import the data into a "Table" in a new worksheet or an existing worksheet. Choose the appropriate option based on your preference.

Click on "Load" to import the data into Excel.

Now, you have a regular table in Excel that contains the data from the pivot table. You can format this table as needed.

Next, open your SharePoint site and navigate to the desired list where you want to import the data.

Go to the "List" tab in SharePoint and click on "Quick Edit" in the "Customize List" section. This will open the list in Quick Edit mode.

In Excel, select the entire table (including headers) that you want to import into SharePoint.

Copy the selected table.

Go back to the SharePoint list in Quick Edit mode and paste the copied table. The data should be pasted into the corresponding columns of the SharePoint list.

Review the data in the SharePoint list and make any necessary adjustments or modifications.

If you have any doubts, please let me know.

Best Regards.