Basic operations in SharePoint with Excel

 

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: 

 

1 Title 
2 Fictional Company 1 
3 Fictional Company 2 
Address 
MTL 
MTL 
Number of Projects

 

 

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 

 

Fictional Company 1 
Fictional Company 2 
Address 
MTL 
MTL 
Number of Projects 
2

 

 

And then click Formulas from the ribbon, and choose Define Name: 

 

Formulas 
Text Date & 
Time • 
Library 
-itle 
Data 
Lookup & 
Reference • 
Review 
Math & 
View 
More 
Power Pivot Tell me what you want to do 
—x @ Define Na me 
Use in Formu 
Na me 
Trace P re 
Trace Del 
Trig • Functions • 
Manager Create from Selection Remove 
Defined Names

 

 

Add a descriptive name: 

 

C:\Users\owner\AppData\Local\Temp\msohtmlclip1\02\clip_image004.png

 

 

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 Internet Explorer, because the upload to SharePoint functionality requires a browser that supports ActiveX control, so you will have an error like this if you try to use Edge or Chrome: 

 

technifier.sharepoint.com says: 
This feature requires a browser that supports ActiveX controls.

 

 

So open Internet Explorer, and select the gear icon, then choose Add an app: 

 

Change theme 
Office 365 settings 
Add an app 
Site conte 
Change the look 
Site settings

In the search box, you can type: Excel, then you'll see "Import Spreadsheet" app, click it and give it a name, in my case I'll just name it: Our Partners, give it a description if you want, and click browse, then choose your document, and hit import. If you get an error that looks like this: 

 

Message from webpage 
The specfied file is not a valid spreadsheet or contains no data to 
import.

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: 

 

Machine generated alternative text:
Print 
Zoom (100%) 
Saf ety 
Add site to Apps 
View downloads 
Manage add-ons 
F12 Developer Tools 
Go to pinned sites 
Compatibility View settings 
About Internet Explorer 
Ctrl+J

 

 

And choose Internet Options, Open Security tab, and click Trusted Sites, then click Sites button: 

 

 

Machine generated alternative text:
Genere!o Security Privacy Content Connections Programs 
Select a zone to Vien or change security settings. 
Internet Local Intranet Restricted 
sites 
T rusted sites 
This zone contains vvebsites that pu 
trust not to damage your computer or 
Advanced

 

 

 

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 Import from Excel sheet. 

 

Now, once you click on Import, your Excel document will open and you'll see a popup that looks like this: 

 

Import to Windows SharePoint Services list 
Range Iype : 
Table Range 
Range of Cells 
Table Ran e 
Named Ranae

 

 

Choose Named Range, and choose the Name you specified earlier, and hit Import. Wait for the SharePoint magic to take place, and you'll see your Excel sheet uploaded to SharePoint:

 

Our Partners 
@ new item or edit this list 
All Items 
Title 
Find an item 
p 
Address Number of Projects 
Fictional Company 1 
Fictional Company 2

 

 

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: 

 

Columns 
A column stores information about each item in the list The following columns are currently available in this list 
Column (click to edit) 
Title 
Address 
Number of Projects 
Modified 
Created 
Created By 
Modified By 
Type 
Single line of text 
Single line of text 
Number 
Date and Time 
Date and Time 
Person or Group 
Person or Group

 

 

 

 

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 List tab, and choose "Export to Excel"

 

Ccnnect to Export t 
Outlook Excel 
Open with Access 
Open with Project 
& Export 
Form Web Parts 
Edit List 
Customize in 
New Quick Step 
Customize List

 

 

 this will open a dialog asking you to either save it or open it, note this will not save or open an Excel file, it will open/save a .iqy file, which is a Web Query file, it will not contain any data, but it will hold a connection reference to your SharePoint list. You can choose to open it, and it will open in Excel, and Excel will ask you if you trust this source, for sure we trust it because it's coming from SharePoint, so click on Enable: 

 

Microsoft Excel Security Notice 
Microsoft Office has identified a potential security concern. 
File Path: 
Data connections have been blocked. If you choose to enable data 
connections, your computer may no longer be secure. Do not enable 
this content unless you trust the source of this file. 
Enable 
Disable

 

 

Now you'll see the data coming in Excel and it looks like this: 

 

Title 
2 Fictional Company 1 
3 Fictional Company 2 
Address Number of Projects Item Type Path 
MTL 
MTL 
5 Item 
2 Item 
sites/HaitiSchoolproject/Lists/Our Partners 
sites/HaitiSchoolproject/Lists/Our Partners

 

 

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 Control + S and save it somewhere on your computer. 

 

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 Refresh: 

 

Page Layout 
her Existing 
Formulas 
Review 
Show Queries 
From Table 
View Power Pivot 
Connections A 
Properties 
Query • Recent Sources 
Connections 
Refresh 
Edit Links 
All 
Connections 
Get & Transform 
Title 
dress Number of Projects Item Type Path 
5 Item 
2 Item 
sites/HaitiSchoolproj 
sites/HaitiSchoolproj

 

 

But.. Yes, another but.. What if I am too lazy to keep clicking Refresh to refresh my data? I can click on Connections - Just beside the Refresh button, and choose Properties: 

 

 

Review 
View 
Power Pivot 
Connections Al 
Design Tell me what you want to do 
Show Queries 
From Table 
Recent Sources 
Transform 
Properties 
Refresh 
Edit Links 
All 
Connections 
Clear 
Reapply 
Sort 
Filter 
Text to 
Flash Remove Data Consolidate Relationships Manage 
Advanced Columns 
Fill Duplicates Validation • 
Data Tools 
Data Model 
Add. 
Remove 
pr erties... 
Refresh 
Manage Sets... 
Close 
What-I f 
Analysis 
Sort & Filter 
acts Item Type Path 
5 Item 
2 Item 
sites/HaitiSchoolproject/Lists/Our Partners 
sites/HaitiSchoolproject/Lists/Our Partners 
Workbook Connections 
Name 
Description 
Last Refreshed 
Locations where connections are used in this workbook 
Click here to see where the selected connections are used

 

 

Then I can choose: Enable background refresh, and specify that I want to refresh data when the file is opened: 

 

Usage Definition 
Refresh control — 
Last Refreshed: 
Z] Enable background refresh 
Refresh every 60 
IV minutes 
Z] Refresh data when opening the file 
Remove data from the external data range before saving the workbook 
Z] Refresh this connection on Refresh All

 

Click OK and close the Excel sheet. Let's try it out, add a new entry in the SharePoint list: 

 

 

Address 
Fictional Company 3 
Fictional Address 
Number of Projects 51

 

 

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 Enable Content: 

 

SECURITY WARNING External Data Connections have been disabled 
Enable Content

 

 

Then the records added in SharePoint will appear in this document: 

 

 

Title 
Address 
Number of Projects Item Type Path 
2 
4 
Fictional Company 1 MTL 
Fictional Company 2 MTL 
Fictional Company 3 Fictional Address 
5 Item 
2 Item 
5 Item 
sites/HaitiSchoolproject/Lists/Our Partners 
sites/HaitiSchoolproject/Lists/Our Partners 
sites/HaitiSchoolproject/Lists/Our Partners

 

 

 

Now we don't need to see the last 2 columns, am going to highlight them and right-click then click Hide

 

 

 

Number of Projects Item TYE . 
5 Item 
2 Item 
5 tem 
Cut 
Copy 
Paste Options: 
Paste Special... 
Insert 
Delete 
Clear Contents 
Format Cells... 
Column Width... 
Unhide 
ject/Lists/Our Partners 
oject/Lists/Our Partners 
ject/Lists/Our Partners

 

 

 

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. 

 

Leave a Reply

Your email address will not be published. Required fields are marked *