I recently started a consulting job for a construction company to fix their Excel quotation template. But even the best Excel based template in the world wasnt going to solve all their problems.
After a bit of investigation, I came to the conclusion that what they really needed was a custom quote app which could be built fairly easily with Microsoft PowerApps.
PowerApps can easily connect to and save data to many different data sources, so after a bit more investigating, I decided I would use SharePoint lists to store all the quote data from the app.
I had heard about SharePoint before, but I never really knew what it was.
I had also come across it in Excel since Excel Tables have the ability to export to SharePoint.
In this post, Im going to share with you what I found out about SharePoint and Excel.
As the name might suggest, its a place to share stuff.
Its a browser based platform from Microsoft that can house all the content (data, files, folders, photos, documents etc.) related to your work to make it easier to share and collaborate with others on the same work.
You can think of it as an internal company website (intranet) that can help improve your organizations data management. A sort of Wikipedia style server or information portal. Its also a bit like OneDrive in that it can store and manage your files in the cloud, but with some more advanced collaboration features.
I set up my SharePoint site quickly and didnt bother to edit or customize anything. This is what you get straight out of the box.
Theres a lot of customization possible, but Im just using it to store a couple data lists so didnt think it was worth spending any time on.
SharePoint doesnt come with any of the Office 365 Home products. Its really only intended for business use so its only available with an Office 365 Business account subscription.
In fact, youll have to shell out for the Office 365 Business Premium account if you want to use SharePoint. Its not available with the regular business account.
A SharePoint list is similar to an Excel Table, its a container for data.
SharePoint lists allow you to store data in list format with rows and columns.
You can use a list to house all your customer contact details, employee details, product pricing etc. If you can organize the data into a table structure, then it can be stored in a SharePoint list.
Unlike Excel tables, the columns in a SharePoint list allow you to set a column as a certain type of data like text, number, date, hyperlink etc A SharePoint list can even contain attachments so you could attach a photo to each employee in your employee details list.
Creating a new list in SharePoint is easy.
From the homepage of your SharePoint site, press the New button then choose List from the options. Give the list a name and description and press the Create button.
You will now have a blank SharePoint list!
All SharePoint lists contain a Title column, and this should usually be reserved for a field that contains unique values.
You can customize your new list by adding columns, assigning data types to each column and then adding data to the list.
The new list might appear in your site navigation menu, but if not you can use the Site Contents menu item to navigate back to the list from any place on your SharePoint site.
If your data already exists in Excel, then there is an easier way to create a new SharePoint list.
You can export data from Excel to SharePoint. This way, you can avoid the tedious task of creating and assigning data types to columns.
If your data already has column names in Excel, then these are also exported to your new SharePoint list. The Export wizard will also guess what SharePoint data types to use for the columns based on the data.
Create an Excel Table for the Data
First, the data is going to need to be in a table format.
Select the data then go to the Insert tab and choose Table. Another way to turn your data into a table is to use the Ctrl + T keyboard shortcut.
Make sure you name the table after by going to the Table Tools Design tab and giving it a new name in the Properties section.
Export the Excel Table to a SharePoint List
Select the table and go to the Table Tools Design tab and select the Export command then choose Export Table to SharePoint List.
The first step in the Export to SharePoint List wizard.
- Enter the URL for your SharePoint site.
- Check the box if you want to create a read-only connection to the SharePoint list. This means when you add data to the list on your SharePoint site, the new data will show up in your Excel table when you right click and refresh the table.
- Enter a Name for the new list.
- Enter a Description for the new list. This is an optional entry.
- Press the Next button to review the data types selected before exporting.
Your SharePoint URL should be something like https://yourcompany.sharepoint.com/sites/yoursitename.
In step 2 of the export wizard, review the data types for each of the columns in the data. If a selected data type is incorrect, youll need to cancel the export and either fix the key cell in the table or change the entire column of data in the table.
When youre happy with the data types in the export wizard, press the Finish button to complete the export.
How big the list is will affect how long the export takes. A handful of rows will be near instant but a couple thousand rows can take several minutes to export.
When the export process completes successfully, youll get a pop up notification with a link to the location of the new list.
You can click on this link and the SharePoint list will open in your default browser. Alternatively, you can find the list from the Site Contents navigation menu.
Refreshing an Excel Table with a Read-Only Connection to a SharePoint List
In the export table wizard there was an option to create a read-only connection to the new SharePoint list.
If you select this option then youll be able to refresh your Excel table with any new or changed data from the SharePoint list.
Select the table and go to the Table Tools Design tab and press the Refresh button or right click on the table and select Refresh from the menu.
The data in the Excel table will be updated with the latest data from the SharePoint list.
You can also move data the other way by exporting from SharePoint to Excel.
Theres a big Export to Excel button in each list.
Press this button and the browser will download a query.iqy file.
Open this file and Excel will launch and prompt you to choose where to load the data (Table, Pivot Table or Pivot Chart) and choose the location to load it (an existing worksheet, new worksheet or new workbook).
This is actually the exact same read-only setup that was an option when exporting from Excel. If you missed enabling that option, no worries because you can achieve the same thing by exporting from your SharePoint list.
Power query can be used to connect to all sorts of data sources and SharePoint lists are one of them.
This will give you more control over which data in the list you import as compared to creating a read-only connection to the SharePoint list.
Create a SharePoint Power Query
To create a new SharePoint list query, go to the Data tab select Get Data choose From Online Services choose From SharePoint Online List.
This option is also available under the From Other Sources menu as From SharePoint List.
You will be prompted to enter the URL to the SharePoint site. This should be of the format https://yourcompany.sharepoint.com/sites/yoursitename. Then you can press the OK button.
This will open the power query navigator preview where you can select the list you want to import. Once selected, a preview of the list will show on the right.
A large number of columns in the data preview will look unfamiliar. This is because SharePoint lists contain a lot of extra columns even if they arent being used.
For example, every SharePoint list will have an ID column which is a unique identifier automatically created for each row in the list.
You will need to edit the query slightly to avoid importing all these unfamiliar columns. Press the Transform Data button to open the power query editor.
There are a lot of extra columns, so it will usually be easier to select the columns you want to keep (hold the Ctrl key to select multiple columns). Then right click on any of the selected column headings and choose Remove Other Columns from the menu.
You can also do any other type of data transformation available with power query if you would like.
Once youre finished with the query, you can save it and load the data into Excel. Go to the Home tab in the power query editor and press the Close & Load command to save the query and load the data into an Excel table in your workbook.
SharePoint can store more than just lists, it can also store all types of files in a document library.
You can use use power query to import and combine data from multiple data files like Excel workbooks or CSVs.
What is a SharePoint Document Library or Folder?
A SharePoint document library is very similar to a folder on your computers hard drive. Its a place to store all your related files.
You might set up a document library for all your monthly sales files.
Here I have a document library called Sales and in a folder called 2018Q1 in which I have 3 Excel workbooks.
Each file has sales data by day for the particular month.
Export a Folder to Excel
If you click on the Export to Excel button found in the SharePoint folder this will download a query.iqy file.
When you open this file it launches Excel and creates a read-only connection to the list of files in the folder. You can refresh this just like when exporting a SharePoint List to an Excel Table.
This table only contains the names of files and not the actual contents of the files. There is a handy clickable hyperlink for each of the file or folder names. This will open up the SharePoint file or folder.
Create a From SharePoint Folder Query
Youll need power query if you want to import and combine the data inside all the files.
To create a SharePoint Folder query go to the Data tab select Get Data choose From File choose From SharePoint Folder.
Now youll need to input the site URL (not the folder URL).
The Navigation preview will show a list of all files in the site and youll need to use power query to filter down to the files you want. Press the Transform Data button.
Filter on the Folder Path column to the desired folder. Then further filter on any file Name or Extension to get exactly the files you want.
When only the files you want to import and combine are left, click on the Combine Files toggle in the Content column heading.
Power query will import and combine the files based on a sample file. You can then select which sheets to import and view a preview of the data on the right side. Press the OK button to combine the files.
You can then Close & Load the query to import all the data. Now if you add a new monthly sales files to the folder and refresh the query, the new files data will be included in the querys output.
Conclusions
SharePoint is a great place for storing files for collaboration and data thats shared throughout the company.
But it might not be the best place for further using or analyzing the files or data.
Thats ok though, SharePoint and Excel are very well integrated. Its easy to push and pull data from SharePoint to Excel.
You can quickly get data using the export options in either Excel or SharePoint. If youre comfortable with power query, you can even use that.