End users are the core foundation of SharePoint usage, SharePoint was made to serve end users, developers do custom solutions to serve end users, and admins make sure the environment is working fine so end users can do their work easily. One of the features that SharePoint provides out of the box, is the ability for end users to use the tools they already know, and integrate it with SharePoint, and one of these tools is Excel.
This blog post is targeted towards end users who are new to SharePoint and wondering how they can use Excel within SharePoint, so let's see some super easy steps on what you can do with SharePoint.
Let's say you are working on an Excel document locally on your computer, for me for example, let's say am working on a document listing partners for my company, my Excel workbook looks like this:
Now I would like to have this in SharePoint as a list, so I can share it with others. When you want to import an Excel sheet do SharePoint, SharePoint will not import the whole document, it will ask you for a specific range that you may want to upload to it. For example, If you have an Excel workbook with columns A through J, you might want to upload the columns A to F only, so you don't have to create a new Excel sheet just for these columns. There are multiple ways to do so, but the easiest way is, to highlight the area that you want to upload to SharePoint
And then click
Add a descriptive name:
And Save your document, and make sure to close it.
What did we just do? We have our Excel sheet as it is, we just gave a name to the selected area that you want to upload to SharePoint, that's it, nothing major.
Now to upload your document to SharePoint, you have to forget about all other browsers, and use
So open Internet Explorer, and select the gear icon, then choose
In the search box, you can type:
Then you need to add the current SharePoint site to the list of trusted sites in Internet Explorer, to do so, click on the gear icon in Internet Explorer on the far top right corner:
Make sure to add the current SharePoint site URL, and click Add, then Close. Then try to import the document again. If it gave you the same error, then try restarting Internet Explorer, and repeat the steps before to
Now, once you click on
If you check the columns for this list, you'll see that SharePoint is smart enough to infer the types of columns, and it will create a Number column for our "Number of Projects" column:
You successfully uploaded a Range in your Excel workbook to SharePoint and created a list out of it! That's all good, and it's all basic stuff. Now let's try it backwards! Let's say you have a SharePoint list, that's called Our Partners, and you want to be able to view it in Excel locally, without opening the site. What can you do in this case? Remember that the Import process is just one time push from Excel to SharePoint, and it won't make any kind of connection between the document on your computer and the list.
Click on the
Now you'll see the data coming in Excel and it looks like this:
REMINDER: This is a .iqy file, it doesn't hold the actual data, it just gets the data from SharePoint and shows it to you, if you're offline, it will not be able to display your data. So what to do? We can save it as an Excel sheet now, click
Now we have our Excel sheet with the data locally, so we don't have to be connected to the internet to see the data .. But .. How can we get the latest data from SharePoint? Suppose that I am collaborating with other people, and they add data to this list, and I have this Excel file on my computer and am too lazy to open the browser and check the updates, I can click on the "Data" tab, and hit
But.. Yes, another but.. What if I am too lazy to keep clicking
Then I can choose:
Click OK and close the Excel sheet. Let's try it out, add a new entry in the SharePoint list:
Then open your document again, it might ask you to Enable External Connections if it's the first time you open it after the previous configuration, so click
Then the records added in SharePoint will appear in this document:
Now we don't need to see the last 2 columns, am going to highlight them and right-click then click
Then save your document. That's it, now every time you open this Excel document, it will auto refresh from SharePoint and show you only the data that you need.
We learned in this post, how to get data from Excel into SharePoint as a list, how to give an Excel sheet a Named Range. We learned that Importing Data from Excel will require Internet Explorer as the importing process will require a browser that supports ActiveX controls. We learned how to pull data from SharePoint as a Web Query file (.iqy), which is a file that just connects to SharePoint and doesn't hold any real data, and if you want to save the data, you can save this file as an Excel Document (.xlsx), and we learned how to get the latest data from SharePoint without having to manually asking for it.