Integrating Excel with other Apps – it really is easier than you think!
In this ebook, Mark explains how Excel can be integrated into Microsoft Forms, SharePoint Lists, and Mobile Apps. He also looks at how several people can work on the same spreadsheet at the same time and how the co-authoring features in Excel work.
How to Integrate Microsoft Excel with Microsoft Forms responses
In this tutorial, I want to demonstrate the following:
- How to create a form with Microsoft Forms.
- How to synchronize a Forms response between a Form and Microsoft Excel.
If you’ve not used Microsoft Forms, I strongly recommend you take a look at it. It’s a straightforward tool that allows you to gather feedback from users from within your organization and also externally.
Create a new form with Microsoft Forms.
Let’s get Microsoft Forms running by navigating to “https://forms.microsoft.com.” Once it’s loaded, login in with your Office 365 credentials.
From within Microsoft Forms, select the option to create a new form and call it “The Excel Summit Sports Quiz.” At this point, you can optionally customise your form with colors and images.
In this tutorial, let’s pick a theme to make it look a little more exciting on the right-hand side of the top toolbar and select “Theme.” You can choose whatever you prefer, but I am going for a brown theme.
Once you’ve given the Form a title and made it look more exciting, you will now need to add some questions. As you create the questions, you will notice that you can have many different types of questions.
As we’re not going too deep with Forms, I am going to use several “choice” questions. Our form is intending to survey the respondents to see who their favorite sportsperson is across several sports.
Add three choice questions as follows:
One of the benefits of Microsoft is that it’s a WYSIWYG (What You See Is What You Get) editor, meaning that as you design your form, it’s very close to what a respondent will see.
Once your form is complete, we now need to get some responses! To this, click the “Share” button on the top menu.
At this point, you can see we have two options:
- Anyone with the link can respond – meaning that anyone in the world can answer.
- Only people in my organization can respond – meaning that only people who are in your organization’s Azure Active Directory can fill in the form.
I love the first option, as this means you have a very cool way for people externally to interact with your business. When the data comes into Microsoft Forms, you can quickly forward it on to other services such as SharePoint or Excel (see the rest of this tutorial).
Click “Anyone with the link can respond” and click “Copy.”
To prove it works for anyone, load up an incognito browser (Chrome) or InPrivate (Edge) and paste the link into it. You will be presented with the form, as illustrated below.
Now fill in Form, choose your favourite sportspeople, and click “Submit.”
That’s it! You have now created a survey, and the response will have been saved inside of Microsoft Forms.
Analyzing the form response data.
Navigate back to the browser tab where you edited the form, and you should see you have a new response.
Here is an example of the Response (accessible by clicking on “Responses”):
You can also click the “Open in Excel” link. If you do, it will look something like this:
I am pretty sure you will agree that it was not only speedy but straightforward to go from nothing to a running form. As easy as it is, there are a few reasons why you would want the survey responses sent into another data store such as SharePoint or Excel. Here are some that come to mind:
- You may have an existing Excel Spreadsheet, and you’d like to supplement it with the responses.
- You may want your Team to be alerted to the response in Microsoft Teams.
- You may want to filter, sort, and share the responses with your team in SharePoint.
- You may want to share the live responses with your team as they come in. If you wanted to do this with Forms, you’d need to give everyone permission to see the responses and ask them to keep downloading the spreadsheet.
There will be many more use cases where you need the survey responses outside of Microsoft Forms. The next part of this tutorial explains how we are going to automatically send the answers from Forms to an existing Excel Spreadsheet.
How to synchronize a Forms response between a Form and Microsoft Excel.
To get around the drawbacks of keeping the data in an isolated spreadsheet, created by Forms, we’re now going to look at another Office 365 service called Power Automate.
Power Automate (previously Microsoft Flow) is an automation tool that allows you to pass data between one or more services. It can be configured to run on various triggers such as:
- When a new SharePoint list item is created, or
- When a new Form response is received
When the trigger (event) occurs, Forms will listen to it and carry out steps that you specify.
I like to think of Power Automate as being the “glue” between all of Microsoft’s services.
That’s a very rapid intro to Power Automate and you can find many more comprehensive articles on the Collab365 Community. Now it’s time to create a Flow within Power Automate that passes the response data as it comes into our Excel Spreadsheet.
Create an Excel Spreadsheet from within Microsoft Teams
Before we get started with creating the Flow within Power Automate, we need to store a spreadsheet in a destination that we can “see” from our Flow. You can either store your spreadsheet within OneDrive for Business or within a “normal” SharePoint site. However, in this example, we’re going to use Microsoft Teams.
Please note: If you don’t use Teams, then go ahead and store it a SharePoint document library or save it to the OneDrive for Business.
Open Microsoft Teams and navigate to a Team that you would like to create the Excel spreadsheet within.
Click on a Channel and navigate to the “Files” Tab.
On the “Files” tab, select “Add new Excel Spreadsheet.”
Once you’ve done this, give your Spreadsheet a memorable name as you will need to use that in Power Automate later on.
When your Spreadsheet has been created, open it, and add the following values into three adjacent cells:
- Best Footballer
- Best Rugby Player
- Best Tennis Player
Now highlight the three cells and choose “Insert” > “Table”
On the dialogue window that is then displayed, select “My table has headers” and click “OK.”
You should now see your spreadsheet looking something like this :
Excel has now created a data table that Power Automate can automatically send data to. As you will need to refer to the name of the Excel Table in your Flow, I strongly recommend you rename it to something more meaningful. At the time of writing this, it’s not possible to rename a table using Excel Online, so you need to do using the desktop version of Excel. So open the spreadsheet in desktop Excel and rename it.
Here’s a quick snippet of how you would achieve that:
That’s all the setup work we need to do in Excel, for now, so let’s work on getting the data from Forms into Excel via Power Automate.
Create the Flow in Power Automate
For the next part of the tutorial, navigate to a browser and open up https://flow.microsoft.com. Once it’s loaded, click “Sign in” and use your Office 365 credentials.
- From the menu on the left, select “My Flows.”
- Select “Create.”
- Select “Automated Flow.”
Now, we’ve initiated the creation of an automated flow we need to give it a name and select the trigger. The trigger defines when the flow starts, so in our case, we need it “wake up” and run when a new Microsoft Forms response is created.
Select the options as outlined in the following image.
Once you’ve filled in the dialog, hit “Create.” You will then be taken a designer canvas with just one trigger defined like this. Select the form you just created in the dropdown and click “New step.”
In the New step, we need to search for “Forms,” select “Microsoft Forms,” and then choose “Get response details” (as illustrated below). This step allows us to get and reference the incoming Forms response so that we can use it in a later step.
Once you’ve added the “Get response details” action to the canvas, you need to select the same form as in the first step and also set the “Response Id” to the “List of response notifications Response Id.”
If we saved and ran the flow now, it will do nothing more than run when a new response is submitted. It won’t do anything useful with the data. To continue adding functionality to it, we need to add another step, which will allow us to take the reference to the forms response (just created by the end-user) and send it over to Excel Online.
Once you’ve hit “New step,” please select the “Excel Online (Business)” option:
Then select the spreadsheet created in the previous section (using Microsoft Teams) and set the values in the data table.
From the list below the Excel Online (Business), choose the option to “Add a row into a table.”
The values to enter on this next step are pretty self-explanatory. You need to find the location, document library, and file name of where you stored the Excel Spreadsheet. You then need to choose the table name that you created.
If everything worked, you should now see your column headers, as shown below.
Next, we need to tell the step where to get the “Best Footballer Player,” “Best Rugby Player,” and “Best Tennis Player” from. To do this, click on “Best Footballer” in the Power Automate step and you should see a selection box automatically appear to the right as follows:
Click in each of the Footballer, Rugby and Tennis players and select the Forms field on the right. Once you’ve finished, it will look like this.
That’s it! Save the form and now go and add a response to your Microsoft Form and then check it got piped into the Excel Spreadsheet. If it did, you should see something like this.
How to sync data from Microsoft Excel into a SharePoint List?
In this tutorial, let’s find out how we automatically send data that is entered into an Excel spreadsheet directly into a SharePoint List.
There are many reasons for wanting to do this, such as:
- You may have a few columns that you want your team to see within SharePoint (say high-level sales data), but not the rest of the spreadsheet, or
- You may want to take advantage of filtering and sorting that SharePoint lists offer.
Create a table in Excel
In this fictional example, I’ve created an excel spreadsheet to collate an individual’s meal requirements for our team Christmas party and formatted this as a data table, as shown below.
It’s essential to name your data table as we will need to refer back to it later on in this example. If you go with the default, it will be tough to pick the correct one in Power Automate.
Create a List in SharePoint
In SharePoint, I have already created a list called “Xmas Party People,” which contains columns with similar headings as used in the Excel Spreadsheet. The example is going to be a straight copy, meaning what they enter in the spreadsheet will be synchronized to SharePoint.
Create a Flow in Power Automate
To synchronize the information entered into this spreadsheet with SharePoint, we need to create a flow using Power Automate.
Let’s open Power Automate and create a new Flow. In the Flow, let’s create a Flow containing three steps, as follows.
The First step you need to add is a “Recurrence step,” which will define how frequently the flow should run. The reason for running the Flow on a scheduled basis is because there are no triggers that we can react to from Excel. Although this could be possible, I guess that Microsoft doesn’t offer this for performance and licensing reasons. Excel spreadsheets generally have multiple cell updates so you wouldn’t want Flow running for every single edit.
For this example, I’ve chosen to run this every minute, but you can set this to what is suitable for you.
The second step is a “Get a row,” and it’s the job of this step to return the latest row from the data table contained within the Excel spreadsheet. The final two elements determine which rows of data will be included in the flow. In our case, we only want to include data when it has been approved. So our Key Column is the Approval column, and the Key Value is when this field is set to “Yes.”
In simple terms, only rows where the Approval is set to “yes” will progress to the next stage of the flow.
The Condition element will double-check to make sure the approval has been granted by the end-user editing the Spreadsheet.
If the approval status is Yes, then the flow will follow the left-hand “If Yes” path.
As you can see from above, the “Create item” step is what we will use to add a new list item into the SharePoint list. Once it’s run, the SharePoint List will have the person added to the Xmas Party People List.
Finally, the flow will delete the processed row from the Excel table (otherwise, when the flow runs again in 1 minute, the same data will be copied into the SharePoint list again.)
Testing the Flow
To check everything works, I’m going to add some data into my Excel spreadsheet.
The Flow should run as per the schedule you set in the Recurrence element. If you need to force it to run, you can use the “Test” option in the top right-hand corner of the screen.
Select “I’ll perform the trigger action” followed by “Save and Test.”
And then confirm by clicking “Run Flow” to manually set it off.
You should see a confirmation message as shown.
Back in SharePoint, you will need to refresh the page and should see a new row in your SharePoint List.
And in Excel, the data table is empty as the row was successfully deleted.
Create a card for Teams
We can expand the flow by adding a step to display this information entered into a Teams channel as a card. This is really useful for notifying your Team!
In between the “If Yes” and “Delete a row” steps, we add an action by clicking the plus symbol.
Select Microsoft Teams
Scroll down the list of available actions and select “Post a Message.” This will create a message in the conversations area within your chosen Teams channel.
You need to select a Team and Channel; for me, this is the Testing Team and the general channel, then add the message to be displayed.
The message can be dynamic with the available fields displayed in the Dynamic Content area.
To check this works, I can go back to my Excel spreadsheet and add another row.
After the Flow has run.. The SharePoint list now has two entries:
In Teams, a new message has been automatically posted to the General conversation
So that shows how you can collect data in Excel and automatically pull it through into a SharePoint list and also create a notification in Teams using Power Automate.
How to get Real-Time SharePoint data into Excel?
In this demonstration, we are going to effectively reverse the data flow we have just used, so we are going to take Christmas party information from the SharePoint List and send it back to an Excel table.
Firstly, I’ve turned the Flow created in the previous demo off to ensure it doesn’t interfere with my new flow.
Create a New Flow
For this example, I’m going to create a new automatic flow that will be triggered when a new item is created in SharePoint.
The first step is to identify the SharePoint Site Address and List that holds the data we want to move.
The next step is to go to Excel online and “Add a row into a table”
Initially, you need to identify the Location, Document Library, File Name, and table within the file that you want the data to be added to. Then you need to link or map the fields (column names) in the Excel table to the data you hold in the SharePoint list.
By using the Dynamic content fields again, we can correctly link the name, meal requested, entertainment idea, and the number of seats fields from SharePoint back to the relevant fields in the Excel table.
Remember to save the flow.
Initially, I’m going to leave the approval field as no and Test the flow to ensure it’s working as intended. I’m going to add another row into the Xmas Party People list in SharePoint.
When I open up the Excel list again, we see the new entry.
How do you build a mobile app (in 20 seconds) to view and edit Excel data?
To demonstrate this, I’ve downloaded a sample sales spreadsheet from Microsoft and saved it into DropBox calling it Financial sample.
In PowerApps, we’re going to create a straightforward app to allow people to review this data.
PowerApps provides a few popular options for the initial data source, but I need to choose the arrow to see more.
So I can select my Dropbox as the connection source and the Financial Sample spreadsheet I’ve already saved.
Followed by selecting the data table within the spreadsheet.
PowerApps runs a wizard to make sense of the data in the table and creates an initial app.
The display is a WYSIWIG editor, and as soon as you click around in the display, you can review and change the properties of that element.
In this demo, I’m not trying to show how to make a stylish app; instead, I’m trying to illustrate how easy it is to view and update data.
If you click on the first entry on the app, all the relevant details about it are displayed, and alongside the app view, I’m going to overlay the original spreadsheet.
So you can see in this example that the information in the app relates to the first row on the spreadsheet.
We can edit the app by clicking on the pencil in the top right corner of the screen
I’m going to change the sales to 20 and save the app, then go and take a look at the spreadsheet.
Because the spreadsheet is saved in Dropbox a notification is displayed stating that changes have been made.
We need to click “Get the latest version” to review the updated spreadsheet, and you will notice that the value has been updated.
Similarly, if you delete items from the app, they will be removed from the Excel table.
To add a new item to the Excel table from the App, click on the plus sign at the top left of the screen.
And you will be presented with a blank record to complete:
When you click the tick to save the data, this will get added to the bottom of the spreadsheet.
Although I’ve only scratched the surface of Power Apps in this section, hopefully, it has given you a good idea of what is possible and how you can update data in Excel spreadsheets from a Power App.
Co-authoring in Excel
Co-authoring allows two people to work on the same spreadsheet in Excel simultaneously. The spreadsheet needs to be stored in SharePoint Online or OneDrive for business and can then be accessed from Ipad, Android apps, within Teams, from the desktop, as well as online.
To demonstrate this, I’m going to call my colleague Fraser via an Audio Call within Teams, and we are both going to open the same Excel spreadsheet we’ve used previously called Sales Data.xls.
This is my view of the spreadsheet (open in my desktop app) before Fraser opens his copy.
When Fraser begins to edit the spreadsheet, a notification appears in the top right-hand corner of the window, as shown below.
Also, the cell he is currently working on is highlighted on the spreadsheet so I can see what he is doing.
When working with co-authors, you can add comments to cells in Excel as normal but also @mention your co-authors in the comment.
When using an @mention in the comment, the person you mention receives a notification about the comment and can then reply. The specific cell that is being commented on is included in the note (in this case cell L6)
If you happen to be offline or not working on that specific spreadsheet when a comment is added to the thread, you’ll receive an email to keep you up to date and also providing a link to the comment.
Clicking “Open” in the email will take you to the comment thread in Excel.
Once your comment has run its course or been resolved, you can delete it or mark it as resolved as required.
As people are working simultaneously on a spreadsheet, changes made by one person are immediately reflected in the views that everyone else sees. Please be aware that this in itself can cause problems and you do need to exercise caution!
Initially, I am working in cell L6 as shown.
After Fraser sorts the spreadsheet data numerically on the Gross Sales column, my view of the data is immediately updated and although I’m still in cell L6, this now relates to an entirely different piece of data to what I was looking at before.
The same features can be used in the desktop app, as shown below.
By clicking on the button in the top right corner of the screen, I can see what part of the spreadsheet Fraser is currently working on.
Again, as Fraser performs additional work on the spreadsheet, my view of the data is updated. In the picture below, Fraser has used the filter option to return only the top 10 entries.
These changes can be reviewed on any device. I have the same spreadsheet open on an Ipad (as well as online), and any changes Fraser makes are also being updated in both of my views.
Here is the view on my Ipad after Fraser enters another comment:
Co-authoring in Teams
The same Excel features are available in Teams. When working collaboratively on a spreadsheet in Teams, you can also display your Teams conversation alongside the spreadsheet.
Hopefully, you can see that not only are there fabulous was to work collaboratively on the same spreadsheet as your colleagues; you can also combine real-time conversations alongside your work which can hopefully allow items to be resolved faster as each party can be instantly updated with any changes made.