0

I have a custom SharePoint Online list with several calculated fields that use other fields for their calculations (e.g., the calculated field "Full Name" = [First Name] + [Last Name]). In my list views, only the calculated fields (e.g., "Full Name") are displayed, not the source fields (e.g., "First Name" and "Last Name"). However, when I export the list view to Excel, all the source fields used in the calculations are included in the export as empty columns, even though they are not displayed in the view. How can I prevent these unused source columns from appearing in the Excel export?

enter image description here

1 Answer 1

0

Instead of using the "Export to Excel" command, start in Excel and use Power Query (Get and Transform on the Data ribbon).

With Power Query, you can get data from a SharePoint list. Using implementation 2.0 (an option when you start the query), you can specify if you want all columns or only the columns of the detailed view returned. You can also edit the query and tidy up anything you don't want to see, filter data, sort data, the whole Power Query gamut, before you then load the result of the query into the worksheet.

You can refresh the Power Query with "Refresh All" on the data ribbon, just like you would an export from Sharepoint.

3
  • If that resolved your issue, please mark the answer as described in the tour. If it didn't, please leave a comment, so I can follow up.
    – teylyn
    Commented Jul 5 at 1:39
  • Thanks for the detailed answer @teylyn, but I'm looking for a solution that works for end users who would like to export the list data, using Power Query from the Excel side is less feasible for them.
    – ohalev
    Commented Jul 8 at 6:06
  • If you have a suitable view in place, it's just a few clicks. It's also possible that a Power User prepares the Excel file and distributes it to end users who just refresh the data.
    – teylyn
    Commented Jul 8 at 23:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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