I work for an organization that allows us to create SharePoint Sites / lists, etc. I have NO backend access to SharePoint, nor do I have access to SQL, SSRS, etc. I do have Access and Excel.

That said, my list in SharePoint is a list of "unique" projects. Each project has a choice field, with an option for multiple check box selections for "product". So - one to many. One Project may have 1 or MORE Products. I have been asked to generate graphical representation/report - using Excel - of the count of PRODUCTS in our PROJECTS.

When I export data to excel - and attempt to create a pivot table, each project has the PRODUCT lists sorted by a ;#. Which makes it impossible to get an aggregated count by product.

I have multiple fields like this - and hundreds of projects and products - so it's not conducive to manually break them all out - and I'll have to run the data on demand or at a minimum monthly.

IS there a way to process this through Excel with limited manual intervention to group / count the individual PRODUCTS?

1 Answer 1


This is how the lookup data is stored internally. Your best option is to create some client side JavaScript code which reads and formats the data in the required format and dump it into a CSV file. Then open the CSV file in Excel.

You can execute the JavaScript code by opening browser console window.

  1. Querying data in JavaScript
  2. Expand lookup fields
  3. Create CSV file using JavaScript

Another Option

Create a REST query and execute it in a new browser tab. This will give back the JSON/XML format. Now convert this to CSV format using online tools -

  1. https://konklone.io/json/
  2. http://www.convertcsv.com/xml-to-csv.htm
  • 1
    Or create a Macro in Excel that can split and reformat as desired Aug 15, 2016 at 17:10
  • @EricAlexander in this case will the lookup fields get expanded with actual values? Aug 15, 2016 at 17:11
  • 1
    Exports with lookup values typically come over in the export to excel as ID;#Value like she mentions Aug 15, 2016 at 17:14
  • Here is a 3rd option to convert: JSON to CSV and XML to CSV (from Data.Page)
    – Stack Man
    Jul 1, 2021 at 18:01

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.