“Can you export this to Excel?” I bet you hear this request all the time. If you are an avid Excel user or just prefer to visualize things in Excel, you are in luck. Being a Microsoft product, SharePoint has native integration with Excel. And any content can easily be exported to Excel.
What can I export from SharePoint to Excel?
Any information contained in any list can be exported to Excel. This includes the following Out of the Box web parts:
- Contacts
- Calendar
- Tasks
- Issues
- Custom List
- Discussion Board
- Document Library
Yes, you can also export document libraries [metadata, not the actual files] to Excel, since they are also lists.
How do I export from SharePoint to Excel?
It is quite easy. Here are detailed instructions:
- Make sure you use Internet Explorer Browser. Export to Excel does not work from Google Chrome
- Go to the list you want to export to Excel [i.e. Calendar]
- Navigate to the view you would like to export. The way it works with export to Excel, what you see in SharePoint is what you get in Excel. In other words, if you are exporting a particular view and you only see 5 columns, you will get that view/those columns exported. So if you are looking to export the completed List [database], make sure you have all the columns visible in your view [i.e. All Items View]. Also make sure your view is not grouped! [Grouped columns will not get exported to Excel]
- Click on List tab up top, select Export to Excel in the middle of the ribbon
- You might get a warning message from the browser – just click OK or proceed
- Excel will open up, but you might get another warning – just click Enable
- Your Excel will open up now with data exported!
Refresh the Data in Excel without doing another Export
One important thing to know about export to SharePoint is that the Excel file you exported to still maintains a connection to SharePoint. That means that any changes in the list in SharePoint will propagate down to your Excel file on a computer. Excel will not change automatically. You have to refresh it for changes to trickle down. Here is how to do this:
- From Excel, click on Data Tab, then Connections
- On the pop-up screen that appears, click Refresh button, then Close
- Your data in Excel has been updated!
This refresh functionality is really cool if you let’s say build some sort of pivot tables/charts based off SharePoint data and want to update your chart based on changes in SharePoint. This way, you do not need to reinvent the wheel and re-export data, build charts, etc. All you have to do is just click that Refresh button. Very powerful!
Important to note that the connection between SharePoint and Excel is 1-way. In other words, information flows form SharePoint to Excel, not the other way around! So any changes in Excel will not go back to SharePoint. Once you click on Refresh, SharePoint will overwrite whatever you did in Excel.
Break the connection between Excel and SharePoint
Unless you specifically refresh using instructions above, your data in Excel will remain as it was at the time of the export. If, for whatever reason, you would like to remove that connection permanently, please follow the instructions below:
- From Excel, click on Data Tab, then Connections
- On the pop-up screen that appears, click Remove
- On the warning message that appears, click OK
In this article we will see how to export SharePoint groups users and their all details into excel
Now we will follow below steps one by one to achieve our requirement :
Step 1 : Open a new Excel workbook
Step 2 : Navigate to Data tab in ribbon bar and then click on New Query Option, then click From Other sources and then click From OData Feed
Data Tab > New Query > Other Sources > From ODATA Feed
Step 3 : Paste Below URL into URL Field and change the Group ID & Site collection URL
SiteCollection/_api/Web/SiteGroups/GetByID[11]/Users
To get the group ID, you need to open the SharePoint group and in URL of that group you will find Group ID like below :
Step 4 : Click on Ok button
Now Select Organization account
Now Click on Sign in button using your O365 or On Premises credentials to log in.
Once you are singed in then click on Connect Button.
Step 5 : Now Click on Load Button once the data is loaded then you will see all your SharePoint group users in to excel.
Note : you can modify the column in excel at the time of data load also you can change the list view of SharePoint group to to add/remove columns which are going to export in excel.
I hope this was helpful and saves some of your valuable time.
Hi, I work as a SharePoint Developer in India. I have been working on SharePoint and related technologies for past 5 years and specialize in SharePoint SPFX/2019/2016/2013/2010, SharePoint Online [Office 365] and Microsoft Azure. I enjoy working in SharePoint and love being up-to-date on new development in the field. Beside SharePoint, Whatever I learn or find new in my daily work, I blog. It’s a free website for professional who are interested in Microsoft technologies mainly in SharePoint, Office 365 and Azure. View all posts by vivekmaheshwari1012