We’re pleased to announce that Microsoft Flow is now integrated into Excel. With this integration that uses the For a selected row trigger and the Flow launch panel, you can create and trigger on-demand flows for selected rows in any Excel table on spreadsheets hosted in SharePoint or OneDrive for Business. The Microsoft Flow for Excel add-in enables you to connect your data to a wide range of services such as SharePoint, Outlook 365, Dynamics 365, Teams, Visual Studio Online, Twitter, Approvals, etc. In this post, we’ll walk you through this new capability with a hands-on example.
Approve a Microsoft Forms response to add a row to an Excel spreadsheet. By Microsoft Power Automate Community. When a new response is submitted to Microsoft Forms, start an approval process. If approved, record the response in an Excel spreadsheet and send an email. Otherwise, send an email notifying that the approval was declined. Alternatively, you can use a household budget template in Excel. This template for a budget indicates estimated versus actual costs for individual items and automatically calculates the difference. Event budgeting templates itemize expenses, including costs for the venue, refreshments, entertainment and more, so you know where each penny goes. Elevate your fitness with a running log template, workout log, or weight loss tracker. Selling your car is easier when you have detailed service records to show prospective buyers. Document all routine maintenance and repairs with a vehicle service log template. Microsoft Excel log templates are not limited to personal use.
To get started, in Excel, go to the Insert tab in the ribbon and select Store. Then, in the dialog, search for Microsoft Flow. Then, click Add.
Let’s imagine that you work for Cronus Energy, a multi-national energy production company, which generates energy through wind turbines and hydroelectric power plants. Cronus is on the lookout for better ways to streamline and standardize internal processes to make things easier for their employees. They’ve identified a key process they want to modernize:
Transfer market data entered by Commercial Analysts (minimum energy to generate, maximum energy, and target energy based on revenue goals) to SharePoint so that the Operations team can decide which turbines to use for the week. After moving the data to SharePoint, they also want to send an alert to the team on Microsoft Teams and facilitate a discussion if needed. The Operations team uses a SharePoint list called Turbine Energy Distributions with the columns shown below.
Their development team is already short on resources, so they want to be able to stand up the solution quickly while avoiding as much custom development as possible. Let’s see how Flow can help.
The Commercial Analysts at Cronus Energy enter market data in a spreadsheet hosted in SharePoint. To follow along, download this spreadsheet and upload it to SharePoint or OneDrive for Business.
To get started, click the Flow menu from under the Data tab in the ribbon.
This will open the Flow launch panel in Excel where you will be prompted to Sign in and consent to the permissions requested by the add-in. Click Accept.
Once you’ve signed in, you can explore several templates to quickly connect to a wide variety of services with minimal set up. Scroll down the screen and choose Create an item in SharePoint for a selected row.
Selecting the template will prompt you for your credentials and provide additional details about the template. Click Continue.
In the trigger (For a selected row), click the drop-down next to the Table field and select your table, e.g. Table1. The trigger may be collapsed; if so, click on Edit and confirm that the Table field is set to Table1.
The For a selected row trigger is similar to other manual triggers like the Flow button for mobile or SharePoint’s For a selected item – users can be prompted for inputs when they run the flow (Text, Yes/No, File, Email, or Number) and all flows run with the credentials of the invoker. For this flow, add a Text input called Message with the hint text of “Enter a message for the team.”
In the SharePoint-Create item action, enter the Site Address and List Name for Cronus Energy’s Turbine Energy Distributions List.
Click the Week field and select Week from the Dynamic content pane.
The parameters in the Dynamic content pane consist of your table’s columns – Week, Min Energy (mWh), Target Energy (mWh), Max Energy (mWh), Price ($/mWh), Revenue, and Profit, information about the person invoking the flow – Timestamp, User email, User id, and User name, and lastly any “manual” Outputs you add to the trigger like Message.
Repeat this for the Energy Target, Min Energy, and Max Energy.
Now, add a Microsoft Teams – Post message action. Choose a Team and Channel to post your message to. In the Message field, enter a link to the newly created item along with the Message populated by the flow invoker.
Save the flow by clicking the Save button.
Select a row in the table and then click Run flow in the Flow launch panel.
The first time you run this flow, you’ll be asked to confirm your credentials. You can also learn more about what this flow does. Click Continue.
Now enter a message to send to your team, requesting feedback. Click Run flow.
Voila! An item is created in SharePoint with details from the row you selected in Excel and a message is posted on your behalf including your note asking for feedback and a link to the item.
Now that you’ve created the flow, you can share it with colleagues either by adding them as an owner of the flow or as a run-only user. The latter allows you to maintain ownership of the flow, while enabling your colleagues to run it. In Flow, head over to My Flows and choose the Create an item in SharePoint for a selected row flow. Note – To run the flow, they must have access to the spreadsheet.
Under Manage Run-Only Users, click Add another person.
Here you can enter individuals, AD security groups, O365 groups, or even anyone that has access to the SharePoint list. For each connector used in the flow, you can decide whether the invoker should bring their own credentials (“Provided by run-only user”) or use your credentials (“Use this connection”). Click Save to add the user as a run-only user.
We hope you enjoy this new update. If you have ideas for templates or other feedback, please leave us comments below or post on our Community forums.
Have you tried Microsoft Forms yet?
It’s a great new tool from Microsoft that allows you to quickly and easily create surveys, quizzes and polls.
There is a wide variety of potential uses for these. You could use them to get customer feedback, collect reviews and testimonials or even use them as data entry forms.
I’m using these using these forms in my courses to get student feedback and reviews so I can improve my teaching. I also suggested them to a friend who was looking for a simple data entry solution.
Let’s take a look!
Sign Up For Microsoft Forms
If you’re signed up with Office 365, then you already have Microsoft Forms and it can either be accessed from OneDrive, SharePoint, Excel Online or the Forms website.
If you don’t have an Office account, then you can still sign up to use forms for free here https://forms.office.com/ by creating a Microsoft account.
Creating a New Form or Quiz
There are a couple different ways to create a form or quiz with Microsoft Forms.
Creating a Form in OneDrive
You can create forms inside OneDrive personal or business. Navigate to the folder where you want to store your form results ➜ click on New ➜ select Forms for Excel.
You will then be asked to name the workbook associated with your form. This workbook will be saved in your chosen folder and will be where all the form submissions will be saved.
Creating a Form in SharePoint
The same thing can be done to create a form if you have an Office 365 business account with SharePoint online. Navigate to the folder where you want to store your form results ➜ click on New ➜ select Forms for Excel.
This also prompts you for a new workbook name where your form submissions will be saved.
Creating a Form in Excel Online
If you’re working with Excel Online, you can also create forms. Go to the Insert tab ➜ click on the Forms button ➜ select New Form from the menu.
This will create a form that’s linked to the current workbook.
Populate Microsoft Form From Excel
Creating a Form from the Website
Microsoft Forms To Excel
After you sign into https://forms.office.com/ you should be taken to the home page where you can create new forms and quizzes. If you don’t land on the home page, you can always get there from any screen using the button in the top left corner of the screen that’s labelled Forms.
From the home screen, click on either New Form or New Quiz.
The Different Types of Questions
Microsoft Forms currently has two types of forms. There are Forms and Quizzes. They both allow you to create the same type of questions. The only difference between them is you can assign point values and correct answers to quiz questions in order to calculate a quiz score.
All the questions can be accessed by clicking on the Add new button. This will show the list of available questions choices, but note that some are hidden in a menu accessible by clicking on the Ellipses.
Types of Questions
There are 7 types of questions available. Each has different options.
- The Choice option allows you to define a list of possible answers for the user to select one or more answers from.
- The Text option allows you to create long or short answer text questions.
- The Rating option allows you to create questions with a star or number rating between 2 and 10.
- The Date option allows the user to select a date from a calendar to answer the question.
- The Ranking questions allows a user to drag and drop items to answer questions like order of preference.
- The Likert option allows you to create “agree/disagree” scale type questions.
- The Net Promoter Score option allows you to create questions like “How likely are you to recommend [brand X] to a friend or colleague?” that utilize a net promoter style grading.
Tip: Some question types like the Choice and Ranking options allow you to copy and paste from a range in Excel or a line separated text file. This is handy if you have a long list of choices to add.
Each type of question has a different menu. For example, the above picture shows the available options for the Choice style questions.
- You can copy, delete or move the question from the menu in the top right of the question.
- You can add the actual question along with a subtitle (the subtitle option is found in the Ellipses menu).
- Forms has some built in AI capability to suggest answers for some types of questions. You can select individual items from its suggestions or add them all.
- For the multiple choices you can add or delete choices. You can mark the correct answer (for quizzes) and add comments to the choices.
- You can add more choice options.
- For quizzes, you can assign a point value for the purpose of calculating a quiz score.
- You can allow multiple answers and set the question to require an answer in order to submit the form.
- Further options are available in the Ellipses menu.
Form Sections
Sections in forms or quizzes allow you to break up the form into parts.
If you have a lot of questions in your form and don’t use sections, then the user would see all the questions on one page. Adding sections means you can break this up into multiple pages and the user will only see the next section of questions after completing the current section.
This can help with form submission rates, as seeing long lists of questions can discourage a user from answering all the questions and submitting the form.
You can add sections by clicking on Add new ➜ Ellipses menu ➜ Section.
Previewing a Form
When you’ve done creating your form, you can easily preview it and see exactly what a user will see.
Click on the Preview button in the top right to view and test the form. Careful though, as submitting the form in preview mode will still add the response to your results and you will have to manually delete the response to remove it from your results.
You’ll be able to preview what the form looks like on both mobile and desktop by using the buttons at the top right while in preview mode.
Form and Quiz Settings
Each form has some important settings that can be found in the Ellipses menu.
- For quizzes, you can choose to show the results to respondents automatically after submission.
- Forms and quizzes can either be public or private to an organization. When shared within an organization, you can chose to record the respondent’s name and limit users to one submission.
- There are options to open or close the form to accepting responses. You can set a start and end date for accepting responses. You can shuffle the order in which questions appear. You can add a custom thank you message that appears after a user submits the form.
- You can set notification options to send email notifications to each user or to yourself when a new response is received.
Form Branching
The above is an example of a form that uses branching.
Branching is one of the most useful features in Forms, but it’s unfortunately hidden inside an Ellipses menu. This will allow you to have different questions appear next based on how the user has answered a previous question.
To create a conditional form, click on the Ellipses found in the top right ➜ then select Branching.
This example asks the user if they’ve used Microsoft Forms before and gives two options, either Yes or No. If the user selects yes, then they are asked to rate the product out of 5 stars. If the user answers no, then they are asked why not. This way users are not shown questions that are not relevant to them.
Viewing Form Results
Multiple Microsoft Forms To Excel
At some point, you’re going to want to take a look at the answers that have been submitted by people using your form. This can be done in the Responses tab of any form where you can see a summarized version of the results.
- You can view the details of each result individually.
- You can view all the results in the associated Excel file.
- You can share the results by creating a summary link. Click on the Ellipses ➜ choose Create a summary link.
In fact, I created a summary link to the above example for which can be viewed here.
Form Themes
There’s not much you can do in order to change the look and feel of your forms, but you can change the colour or background image.
Go to the Theme menu in the top right. Here you can select from a couple preset themes or if you click on the plus icon, you can select a custom colour or background image.
Sharing Your Forms
How do are you going to use your new form?
The whole point of creating a form is to collect information from users, so after creating a form you’re going to need to share it with your user audience! This can all be done from the Share menu in the top right.
- You can choose to make the form available to anyone with the link or only people inside your organization.
- You can copy this link and send it to anyone you want to complete the form.
- Sharing the form can be done via link, QR code, embedded HTML form code (see example of embedded form above), or by email.
- You can share a copy of your form as a template via a link so others can modify it for their own use.
- You can collaborate on form creation within an organization or with external users with an Office account.
- You a link to collaborate on editing the form can be copied and shared with anyone you want to give access to edit the form.
Conclusions
If you need to collect information from different users, then Microsoft Forms might be the tool for you.
With forms, you can quickly and easily create questionnaires that you can share both internally or externally from your work.
These forms will automatically collect and store the responses inside an Excel workbook so they can be easily viewed and analyzed later.
It’s another great tool in the Office suite that works well with Excel and is one you’re definitely going to want to explore using.