Analysis of Patient Safety Culture Survey using Excel and Power BI
In this article, we will look at the process of conducting the analysis for the ‘Patient Safety Culture Survey’.
If you are new to the term ‘Patient Safety Culture Survey’, please read my previous article on the same topic to have clarity about the concept I will be discussing here.
Please note that the dataset that I have taken is self-generated data and it does not belong to any healthcare organization
In my questionnaire of data collection, I have changed a few background questions.
You can reframe background questions but not the sectional questions
For data collection, you can create a google questionnaire and share it with your hospital staff. After the required number of responses are collected then you can download the data easily in Excel format.
Before starting with the data analysis, we will make some changes to make our data analysis process easy-going.
Keep one backup copy of your dataset ready if in case you went wrong
We will use Find and Replace function of Excel in this step. To use it click Ctrl + F, type the word you want to replace in ‘Find What’ and the replacement word in ‘Replace With’, then click Replace All (This will replace all the words in the dataset)
Here are the changes we need to make (Don’t worry I will reveal the reason behind this at right time)
· Replace ‘Does not apply or Don’t Know’ with Blank space
· Replace ‘Strongly Agree’ with ‘Agree_Strongly’
· Replace ‘Strongly Disagree’ with ‘Disagree_Strongly’
· Replace ‘Always’ with ‘Agree_Strongly’
· Replace ‘Most of the time’ with ‘Agree’
· Replace ‘Rarely’ with ‘Disagree’
· Replace ‘Never’ with ‘Disagree_Strongly’
While replacing, type the exact word which you want to replace and avoid any spaces
In the below image, I have given an example of replacing the value.
Similarly, replace all other words that I have mentioned previously.
Now we will change the question heading with the question code which has been provided to us in reference of the Patient Safety Culture Survey. If you don’t have the reference here is the link
Note: I have selected AHRQ (Agency for Healthcare Research & Quality) as a reference
According to the reference, the first sectional question- ‘In this unit, we work together as an effective team’ is coded as A1 so we will change the heading to A1, and for identification purpose, we will write the entire question in the comment. You can get the option of adding a comment to the cell by right-clicking the cell.
Similarly, we will change the heading of all the sectional questions.
Now we will create another sheet to record the positive score according to workplace and staff position for every question. Don’t get confuse I have provided the dataset in my Github profile, follow that. As of now refer below image to have clarity on my saying.
If you are having confusion about Positive and Negative questions, then read the reference pdf here. You will notice that there are total 19 positively worded questions and 13 negatively worded questions.
Now we are ready to move towards our Data Analysis!!
Data Analysis:
We will do the data analysis with the help of the Pivot Table. To create the Pivot table, click on the Insert tab, then select Pivot Table (this will automatically select the entire sheet), and lastly click ‘OK’.
In the Pivot Table, Drag the Department field under Values and A1 under the Rows. Next right click on Count of Department, go to show values as, and select % of Grand Total. Now you will see the responses in the form of percentages.
Here is one thing we need to remember while calculating the positive score for the questions we are required to drop the responses with ‘Does not Apply or Don’t Know’. In the pivot table, you will see the blank as the last option because we have replaced the word previously. So we will deselect the blanks by clicking on the drop-down of Row Labels.
If you will see my previous article or the reference you will notice that to calculate a positive score for the question, we need to add the percentage responses of ‘Agree’ and ‘Strongly Agree’.
To automate this step of addition, we will create a sum formula.
Click anywhere outside the Pivot Table, type =SUM(B5:B6) because these cells give the response of ‘Agree’ and ‘Strongly Agree’. Decrease the decimal point by using the Decrease decimal function under the Number field of Home tab. Now we have received the Positive score for question A1. Now the screen shall look like this:
Some revelations:
I suggested you to do some replacements in the initial phase, the reason behind that:
· Because we replace the value ‘Does not Apply or Don’t Know’ with Blank cell it is automatically coming at the end as ‘Blank’ which makes us easier to deselect while doing calculations
· You can see that as we have changed the words Strongly Agree and Strongly Disagree, they are now getting displayed together with Agree and Disagree which otherwise would have not and this helps us to set our addition formula easily. The same logic is applied to words- Always, Most of the time, Never, and Rarely.
Data Analysis for Negatively worded questions:
The rest of the steps will remain the same for negatively worded questions only the last step of percent addition will get changed. The reason behind this is that as it is a negatively worded question to calculate a positive score we should consider Disagree and Disagree_Strongly and hence will require to change the formula. So now the formula will become =SUM(B7:B8) as these cells give the response of ‘Disagree’ and ‘Disagree_Strongly’. Refer to the below image
Caution!!
If in case for any given question, Not even a single respondent opted for a particular option, then the sequence of row labels will change and hence our formula will also get affected. See the below example:
Over here, no Doctor selected the Agree_Strongly option because of which the formula for calculating positive percent is getting affected. As you can see that A14 is a negative question so to calculate a positive score we should consider values of Disagree and Disagree_Strongly, the addition of which is 91% but as one row label is missing, our formula is giving the wrong percentage. In similar cases, you will have to manually do the calculation and need to be alert for such type of misleading situations.
Follow the same steps for every question and record the positive score in the sheet created previously. Here are the steps to follow:
· First start with all the positively worded questions
· Every time you select a new question make sure you deselect the blank row labels
· Keep your eyes open for missing row labels
· If you want to have a detailed analysis according to workplace and staff position then drag these items under the filters field and select the filter you want to apply
· Remember to change the formula for negatively worded questions
As we have calculated the positive score for all the questions, we will now proceed to calculate the composite measure score. To know more about the composite score and how to calculate it, read my previous article.
Proceeding further, we will create another sheet to calculate the composite score based on the given positive score of the questions. Refer below image for the format.
Question codes mentioned against each score are given in the reference
Now we will use the VLOOKUP function to fill out the positive score according to questions, place of work, and staff position.
Click on cell C2 and type the above arguments, now we will see every argument one by one:
1. Select the lookup value as B3 because we want Excel to find the positive score of the question code mentioned in cell B3. Make column B absolute by clicking F4 as only this column contains the question code.
2. Click on the previous sheet we have created and select the field from cell B2: M34 because that is the field that contains the positive score of every question. Make this range absolute because the table array will remain the same in the entire calculation.
3. Select column index no as 2 because that is the parameter whose value we want to get reflected (which simply means that this argument will bring the positive score of the question mentioned in B3 (question-A1) where the parameter matches the value in column no 2 (Hospital)
4. Select the value FALSE because we want the exact value to be reflected
Press Enter!
You will see that we have received the positive score of question A1 for Hospital in the cell. To calculate the positive score horizontally (that is according to workplace and staff position) drag the formula horizontally and change the column index no as 3, 4, and so on because we want the positive score to be calculated for every changing parameter. Now drag each formula vertically to calculate the positive score for all the questions.
Now coming to the last step of our analysis!
Insert a row at the end of every score that is below Row 5 for teamwork, below Row 10 for staffing & work pace, and so on…
Now click on cell C6 and type the formula of average. Type =AVERAGE(C3:C5), this will calculate the Positive score for Teamwork in the hospital. Drag the formula horizontally to calculate the positive score for all other parameters. Follow the same steps for the rest of the measures. Your complete sheet will look like this
Congratulations! You made it till the end.
You have successfully completed the analysis of the Patient Safety Culture Survey. Please note that there are many ways through which you can do your analysis, I have selected Excel as it is easily understandable and accessible in all the hospitals.
You can showcase your results through PowerPoint Presentations or any other medium, I have created a Power BI dashboard to showcase the results of the Analysis. Please find the glimpses of the dashboard at the end.
All the necessary materials and resources are available on my GitHub profile.
I hope you understood the entire process of conducting the Analysis using Excel, if in case you have any doubts, please feel free to contact me on LinkedIn.
Thank You!
