How to assess resistance with Microsoft 365

In my previous blog, I discussed the importance of the Resistance Assessment Questionnaire and the role it plays in managing resistance for change initiatives.

Now, I will walk through a simple, 4-step, no-code solution using a few of the M365 tools. This solution goes through capturing the data all the way through reporting on resistance. Although I go into explicit detail on each of the four steps, you can choose to gloss over areas that are already familiar to you. Similarly, there are many ways to accomplish the same results, but I chose to use Microsoft Forms, Power Automate, Excel, and Power BI for this solution. So, let’s get the party started!

Locate apps in Microsoft 365 or Office 365

Start by clicking on the waffle at the top left corner of any M365 app web page in the M365 environment.

Depending on where you are, if you do not see the application you need in the sidebar pop-up, then you may need to click on “Microsoft 365 →” or “Office 365→” or “All apps” to see all available apps.

Steps to create the resistance questionnaire using Microsoft Forms

As mentioned in my previous blog post, it is essential to select individuals throughout the organization to respond to the questionnaire. Therefore, the questionnaire is broken into 2 sections. The first section will ask questions related to the respondent’s demographics such as role, location, and department. The second section will ask for the respondent’s perception of the change including how well it is being handled, if the communication has been clear and consistent, and if the change aligns with the organization’s vision and business direction.

Step 1. Click on “Forms” to initiate the app

Step 2. Click on “New Form” to create the new questionnaire

Step 3. Click on “Untitled form” in the blank form

Step 4. Enter a name for the form and a description

Step 5. Optional: Click on “Add new” then click on the down arrow (or “More question types”) to select “Section”

NOTE: I have organized my questionnaire into 2 sections. The first section will capture the demographics of the respondent (in my questionnaire, I am capturing organizational role, department, and location). The second section will capture the responses.

a. Enter the name of the section and/or enter a description

Step 6. Click on “Add new” then click on the data type to be captured

a. When capturing demographics, I used “Choice”

i. Enter a question or description for the demographic to be captured;
ii. Enter the values for the demographic
iii. If needed, click “+ Add option” to list additional values
iv. If not all the values can be listed, then click “Add ‘Other’ option” to allow the respondent to enter a different response
v. By default, “Multiple answers” is switched off but can be toggled on, if needed
vi. By default, “Required” is switched off; however, I toggled it on to ensure the question is answered by the respondent

b. When capturing responses, I used “Rating” instead of “Likert” (located under “More question types”) so that I can capture values to summarize the responses

i. Enter a question or statement for the responses to be captured
ii. By default, “Levels” is set to 5 but can be decreased or increased, if needed
iii. By default, “Symbol” is set to “Star”; however, I switched it to “Number” so that I can use it in my Excel tables to calculate each data point
iv. By default, “Required” is switched off; however, I toggled it on to ensure the question is answered by the respondent

Step 7. Continue with step #5 (if including sections) and/or step #6 until the questionnaire is completed

You can choose to “Preview” the form at any time to see how the questionnaire is shaping up. By default, it displays as “Computer” view but there is a “Mobile” view as well. You can also choose a different “Theme” for your questionnaire. By default, it is the Teal color you see in my example, but there are other colors to choose from if you want it to match your organization’s color palette.

In addition, there are some images provided, but you can also choose to add your own image or customize the color as needed. Finally, there are additional settings you can explore including allowing anyone with the link to respond, adding a start/end date to the questionnaire, customizing a thank you to the respondent, etc. I chose to keep all the default values for my example.

Gather responses in an Excel table using Power Automate

An important part of the questionnaire process is gathering the responses. Although Microsoft Forms does collect the responses and allow export functionality to Excel, the data is very straightforward and basic such as identifying how many respondents were in each role. However, if I wanted to see how many of those respondents in each role were resistant to the change, I would need to export the data to Excel and manually apply formulas each time I wanted to analyze the responses received up to that point in time.

The other alternative would be to wait until all the responses were received before proceeding with the analysis. As a result, I decided to use Power Automate to push each response as soon as it is submitted to Excel to allow the data to be summarized in real-time in my respective groupings.

Step 1. First, create an Excel table

NOTE: In my example, I used the desktop version of Excel.

a. Open a new, blank workbook in Excel and highlight the first 16 columns of the first row for the table, then select “Insert” -> “Table”

b. In the “Create Table” pop-up, check the “My table has headers” checkbox then click “OK”

c. Rename the column headers accordingly

NOTE: When tracking Microsoft Forms responses in Excel, the spreadsheet must contain SubmissionTime and ResponderEmail, which are the first 2 columns in my table.

d. Name the table by selecting “Table Design” and entering “Responses”

e. Double-click the worksheet tab and rename it to “Responses”

f. Click on the row below the “Total” column heading (P2) and enter the following formula:
=SUM(Responses[@[Response1]:[Response10]])

NOTE: As I mentioned in the Microsoft Forms section, I chose “Rating” so that I could capture values to summarize the responses. In the next section, I will use the summarized “Total” for each row to provide additional insight into focus areas for the change management team.”

g. Save the Excel workbook in a document library that respondents will have access to – in my example, I chose a communication site.

NOTE: Be sure to have the site location, document library, file name, and table name available as these will be referenced in the next section

Step 2. Second, create a Power Automate flow

a. Click on “Power Automate” to initiate the app

b. Since the workflow is not complicated, select “+ Create” from the left navigation

c. Then select “Automated Flow”

d. From the “Build an automated flow” pop-up, enter a Flow name and select “When a new response is submitted” for Microsoft Forms under “Choose your flow’s trigger” then click “Create”

e. Click on the down arrow to select the questionnaire from the dropdown, then click “+ New step”

f. In the “Choose an action” dialog, type “get response” in the search bar, then select “Get response details” in the “Actions” results

g. In the “Get response details” dialog, click on the down arrow in “Form Id” to select the questionnaire from the dropdown

h. Click into “Response Id” and select the “Response Id” from the pop-up

i. Click on “+ New step” then click on “Excel Online (Business)” in the “Choose an action” dialog

j. Select “Add a row into a table” in the “Actions” results

k. In the “Add a row into a table” dialog, select the Location, Document Library, File, and Table of the Excel workbook created above

l. The “Add a row into a table” dialog will expand to list all the columns in the table. Click into each of the columns and select the corresponding value from the “Dynamic Content” pop-up until all of the columns have a value except the Total column (in the previous section, we created a formula, to sum up, all the responses)

m. Click on “+ New step” then enter “terminate” within “Search connectors and actions” in the “Choose an action” dialog

n. Select “Terminate” in the “Actions” results

o. In the “Terminate” dialog, click on the down arrow in “Status” and select “Succeeded”

p. Click “Save” to save the Power Automate flow

Summarize responses in additional Excel tables

As I mentioned in the above section, the main reason I chose to use Power Automate in my example is the ability to categorize and summarize the responses as it is submitted by each respondent. By creating formulas to calculate and tabulate the results into additional Excel tables, I can provide real-time insight into areas that the change management team will want to focus on to reduce resistance.

Open the Excel workbook created in the previous section. As I mentioned previously, I am using the desktop version of Excel.

Step 1. First, we will sum up the demographics

a. Click on the “+” to create a new sheet

b. Double-click the worksheet tab and rename it to “Demographics”

c. In the first row, add the following headers

d. In the first column, enter all your roles

NOTE: I chose to add a number before my roles so that I can use it for sorting

e. For the first role (Non-manager), click on each of the empty columns and enter the following formulas respectively

i. Resist Change
=COUNTIFS(Responses[Role], “Non-manager”, Responses[Total], “<=25”)

ii. Not Opposed
=COUNTIFS(Responses[Role], “Non-manager”, Responses[Total], “>=26”, Responses[Total], “<=35”)

iii. Accept Change
=COUNTIFS(Responses[Role], “Non-manager”, Responses[Total], “>=36”)

f. Repeat step e. with each of the remaining roles

NOTE: Make sure to change “Non-manager” in the formula to the correct role!

g. Create a table by highlighting all the cells you’ve just populated with the formula then select “Insert”  “Table”. Be sure to check the “My table has headers” checkbox then click “OK”.

h. Repeat steps c. through g. for the remaining demographics (specifically, Location and Department)
i. Save your changes to the Excel workbook

Step 2. Second, we will sum up the answers to each question

a. Click on the “+” to create a new sheet

b. Double-click the worksheet tab and rename it to “Answers”

c. In the first row, add the following headers

d. In the first column, enter a condensed but distinct description of all your questions

NOTE: Since I have 10 items on my questionnaire, I added a leading “0” for sorting purposes

e. For the first question, click on each of the empty columns and enter the following formulas respectively

i. Disagree
=COUNTIF(Responses[Response1], “1”)

ii. Somewhat Disagree
=COUNTIF(Responses[Response1], “2”)

 

iii. Neither Disagree/Agree
=COUNTIF(Responses[Response1], “3”)

iv. Somewhat Agree
=COUNTIF(Responses[Response1], “4”)

v. Agree
=COUNTIF(Responses[Response1], “5”)

f. Repeat step e. with each of the remaining questions

NOTE: Make sure to change “Respones1” in the formula to the correct response number!

g. Create a table by highlighting all the cells you’ve just populated with the formula then select “Insert”  “Table”. Be sure to check the “My table has headers” checkbox then click “OK”.

h. Save your changes to the Excel workbook

Submit responses to the resistance questionnaire

At this time, it is a good idea to submit a few responses to the questionnaire to ensure the form is communicating with the flow and the formulas are working in the workbook as expected. In the Microsoft 365 Developer Program subscription, you can install the user’s sample data pack which will create 16 fictitious users to simulate a small corporate environment.

If everything is working cohesively, then continue to submit responses to the questionnaire to get a good sampling of data across all roles, locations, departments as well as varying degrees of resistance.

Display summarized responses using Power BI

Are we there yet?? Absolutely! Now that we have created the questionnaire using Microsoft Forms, collected the data into an Excel table using Power Automate, classified the data in additional Excel tables, and confirmed everything is working together seamlessly, we are ready to visually pull everything together using Power BI.

Step 1. Click on “Power BI” to initiate the app

Step 2. Select “Get data” from the lower-left navigation

Step 3. From the “Get Data” page, select “Get” for “Files” under the “Create new content” section

Step 4. From the “Create new content” page, select “SharePoint – Team Sites”

Step 5. In the “Enter Site URL to Connect to” pop-up, enter the site location where you stored the Excel workbook above then click “Connect”

NOTE: Depending on where you stored the workbook, you may need to drill further into the document library. In my example, I created a folder within the Shared Documents library.

Step 6. Once you have drilled down to the Excel workbook, select it then click on “Connect”

Step 7. From the “Choose how to connect…” page, select “Import” under “Import Excel data into Power BI”

Step 8. From the “My Workspace” page, hover over the imported dataset then click “More Options”

Step 9. From the “More Options” pop-up, select “Create report”

Step 10. In the “Visualizations” panel on the right, hover over the icons and click one of the charts that will be used to display the summarized data

NOTE: In my example, I chose a pie chart for the overall resistance from respondents, a stacked column chart for the resistance by each demographic, and a clustered column chart for the resistance by question.

Step 11. In the “Fields” panel on the right, click on the down arrow to expand the table

Step 12. Drag each of the sum fields from the table to the visual on the left to chart the summarized data

Step 13. Double-click the page and rename it to “Total Resistance” then click on the “+” to create a new page

Step 14. Repeat steps 10. through 13. for the remaining charts

NOTE: In addition to the sum fields in step 12, be sure to select the demographic and/or statement for the remaining charts

a. If the chart is not displaying as expected hover over the visual, then select “…” (or “More” options)

b. Select the sort option needed

Step 15. Click “Save” to save the Power BI report

Step 16. In the “Save your report” pop-up, enter a name for your report then click “Save”

Conclusion

You did it! Thanks for hanging in there with me. I know there was a lot of information, but hopefully, you found it helpful. As I mentioned above, this is just one example of how you can use the M365 tools. I hope this inspired you to think of ways you can start using these tools to create similar simple, no-code solutions to capture, store, analyze, and report on data for your organization. Happy no-coding

Share and Enjoy !