Diabetic Data Analysis and
Visualization
Purpose
This assignment is the first part of your Data Storytelling Hallmark project. You will analyze a diabetic
dataset and create data visualizations.
Objectives
Using Excel, analyze the data provided
Summarize your findings
Using Excel, create data visualizations including a pivot table, bar chart, pie chart, line chart,
and a stacked column chart
3.1b Assess population health data.
3.1c Assess the priorities of the community and/or the affected clinical population.
Overview
This assignment is the first part of your Data Storytelling Hallmark project.
You will analyze the following diabetic dataset and create data visualizations. The information and
visualizations created during this assignment will be applied in following assignments.
In this assignment, you are provided with a dataset and will be guided through the steps for
analyzing data, interpreting/summarizing data, and creating data visualizations.
Part I.
Excel Diabetic Dataset
Important: Please use the Nightingale College Office 365 Microsoft Excel Desktop version provided to you by the
college to complete this assignment.
Be sure to submit the Excel spreadsheet and this Word Document to the Canvas assignment submission
when complete.
Scenario
You are a new nurse informaticist at a small-sized regional hospital that has been actively collecting
diabetes treatment data over the past year. The hospital aims to evaluate the effectiveness of its
diabetes management programs and identify areas for improvement, particularly in terms of patient
compliance and overall treatment outcomes.
It is just your luck that the person who was to present the data to stakeholders had to have emergency
surgery and will be out for at least a month. The much-anticipated stakeholder meeting is in 2 weeks.
The task of presenting the data now falls on you. Luckily, the data collection is complete, but the analysis
and presentation now fall on your shoulders.
Your task is to analyze the collected data, focusing on treatment compliance and its impact on patient
outcomes such as HbA1c levels, complications, and hospital admissions related to diabetes. Your analysis
will help the hospital understand which factors are contributing to successful treatment outcomes and
where additional resources or interventions are needed.
Data Analysis
This dataset represents a one-year study of diabetic patients seen in the hospital’s diabetic clinic.
Instructions
Calculate Descriptive Statistics
NOTE: It is recommended that you copy and paste the formulas. You may or may not have to hit “enter” if you
are using a Windows computer or “return” if you are using a Mac after you paste in the correct cells.
Example: In cell R2 place the following: =AVERAGE(B2:B26)
Mean Age: Use the AVERAGE function to calculate the mean age of the patients.
In cell R2 place the following: =AVERAGE(B2:B26)
Gender Distribution: Use the COUNTIF function to count the number of males and females.
In cell U2 place the following: =COUNTIF(C2:C26, "M")
In cell U3 place the following: =COUNTIF(C2:C26, "F")
Ethnicity: Use the COUNTIF function to count the number of patients in each ethnic group
In cell W3 place the following: =COUNTIF(D2:D26,"Hispanic")
In cell X3 place the following: =COUNTIF(D2:D26,"White")
In cell Y3 place the following: =COUNTIF(D2:D26,"Black")
In cell Z3 place the following: =COUNTIF(D2:D26,"Asian")
Treatment type: Use the COUNTIF function to count the number of patients in each treatment type
category
In cell R7 place the following: =COUNTIF(G2:G26, "Oral Med")
In cell S7 place the following: =COUNTIF(G2:G26, "Diet and exercise")
In cell T7 place the following: =COUNTIF(G2:G26, "Insulin")
In cell U7 place the following: =COUNTIF(G2:G26, "Combo")
Treatment Compliance: Use the COUNTIF function to count the number of patients who were and who
were not compliant with their prescribed treatments
In cell W7 place the following: =COUNTIF(M2:M26, "Yes")
In cell X7 place the following: =COUNTIF(M2:M26, "No")
Complications: Use the COUNTIF function to count the number of patients who suffered from each type
of diabetes-related complication
In cell R11 place the following: =COUNTIF(O2:O26, "none")
In cell S11 place the following: =COUNTIF(O2:O26, "Cardiovascular")
In cell T11 place the following: =COUNTIF(O2:O26, "Nephropathy")
In cell U11 place the following: =COUNTIF(O2:O26, "Neuropathy")
In cell V11 place the following: =COUNTIF(O2:O26, "Retinopathy")
In cell W11 place the following: =COUNTIF(O2:O26, "Multiple")
Visualize the Data
Create a pie chart depicting ethnicity
Determine how many patients comprise each ethnicity
o Highlight cells W2:Z2 and W3:Z3 à
o “Insert”, “charts” – “2D pie” pie chart
o Change the title of the pie chart to “Ethnicity”
Click on the “+” sign after clicking on a white part of the pie chart – put checkmark next to “data
labels” so that the percentage breakdowns are listed on the pie chart.
Click on one of the numbers in a section of the pie chart – go to “Home” in excel at the top and
change the text color to white and bold so that the numbers in each section of the pie chart can be
more easily seen.
Move the chart somewhere on the sheet so that you can retrieve it later.
Create a pie chart to represent gender distribution
Determine the number of each gender type
Highlight cells T2:U2 and T3:U3 à
“Insert”, “charts” – “2D pie” pie chart
Change the title of the pie chart to “Gender”
Click on the “+” sign after clicking on a white part of the pie chart – put checkmark next to “data
labels” so that the number of each gender are listed on the pie chart.
Click on one of the numbers – go to “Home” and change the text color to white and bold so that it
can be more easily seen.
Move the chart to the side so that you can retrieve it later.
Visually show the relationship between treatment type and outcome
Create a bar chart
Highlight the treatment type column (column G) and drag it down to include the header and all the
data. Hold down “control” and click on the change in HbA1c (column J) columns and drag down to
include the header and all the data.
Insert – Recommended charts - Clustered bar chart (should be the first selection) - OK
Your chart will appear on a separate sheet (sheet 2) in excel. Double click on the tab that says Sheet
2 and change it to “Tx type and outcome”.
Click on the white part of the chart – Click on the “+” sign – put a checkmark next to “data table” to
select it. This will add a table at the bottom of the chart.
You will (barely) see labels for each solid line on the table in black text. Highlight this text area by
clicking on one of the text labels on a bar.
Go to Home on the excel ribbon at the top and change the text color to white and bold to make the
text more visible.
Move the chart under the numerical values on the same sheet
Visually demonstrate the relationship between compliance and hospital admissions
Go back to the diabetic data set sheet
Select the data (with headers) for treatment compliance (Column “M”) and # of hospital admissions
(Column “P”). Remember: After selecting the data and header in column M hold down the control
key and select column P to have 2 columns selected.
Click on the Insert tab at the top. Insert a clustered column chart (should be the first
recommendation under “recommended charts”) - OK
Your chart will appear on a new tab at the bottom of excel. Name this tab “Compliance and Hosp
Adm”
Move the chart under the numerical values on the same sheet and change the title of the chart to
“Compliance and Hospital Admissions”
Click on a white portion of the chart, then click on the “+” sign and click the box next to Axis titles.
Change the Y axis (vertical) title to “Number of hospital admissions”. Change the x axis (horizontal)
title to “Treatment Compliance”
Click on the “+” sign and click the box next to Data Labels so that the number of patients for each
compliance is shown.
Visually demonstrate the relationship between exercise and health
Go back to the diabetic data sheet
Select the data in the exercise frequency (column N) and change in HbA1c columns (column J)
Insert recommended chart – choose stacked column - OK
Click on the chart title and change it to “Exercise frequency and changes in HbA1c”
Click near the top of the chart until crosshairs appear and move the chart to a blank part of the data
sheet so that the data is not obscured.
Visually demonstrate the relationship between check-up frequency and control
Go back to the diabetic data sheet:
Select the data in the check-up frequency (column L) and Change in HbA1c column (Column J) by
selecting the data and header in column L, hold down the control key and then select the data and
header in column J.
Insert – recommended charts – clustered column – OK.
Click on the chart title and change it to “Check-up frequency and change in HbA1c”.
Click near the top of the chart until crosshairs appear and move the chart to the side.
Visually demonstrate the relationship between BMI and initial HbA1c levels
Go back to the diabetic data sheet:
Highlight the BMI column and the HbA1c at start of year column
Insert – scatter plot
Change the title of the graph to “BMI and initial HbA1c”
Click on a white portion of the chart, then click on the “+” sign. Click next to “Axis titles” and change
the Y axis (vertical) title to “Initial HbA1c”. Change the x axis (horizontal) to “BMI”
Click near the top of the chart until crosshairs appear and move the chart to the side.
Analyze the Data
Compare treatment type and control (changes in HbA1c levels)
Go to the sheet titled “Tx type and outcome”
Which treatment type(s) produced the greatest decrease in HbA1c?
Did ALL methods of treatment reduce HbA1c?
What meaningful conclusions can you draw, if any?
Compare compliance with hospital admissions
Go to the sheet titled “Compliance and Hosp. Adm.”
Which type of compliance resulted in fewer hospital admissions?
What conclusions can you draw, if any?
Compare frequency of exercise and control (changes in HbA1c levels)
Analyze the data and the chart titled “Exercise frequency and changes in HbA1c”
What conclusions can you draw, if any?
Compare the frequency of check-ups and control (changes in HbA1c levels)
Analyze the data and the chart titled “Check-up frequency and changes in HbA1c”
What conclusions can you draw, if any?
Compare the BMI and initial HbA1c readings
Analyze the scatter plot graph for BMI and initial HbA1c data
What conclusions can you draw, if any?
Part II.
Activity Submission
Instructions: Fill in the information below: Part A. Conclusions and Recommendations, Part B. Data
Visualizations
Save your document, and upload and submit it to Canvas.
A. Conclusions and Recommendations
Summarize your conclusions and add your recommendations. Use conclusions from your analysis to craft
your recommendations. Consider informatics tools and technologies.
B. Data Visualizations
Paste screenshots of each data visualization below.
Pie Chart Depicting Ethnicity
Pie Chart to Represent Gender Distribution
Bar Chart to Show the Relationship Between Treatment Type and Outcome
Clustered Column Chart to Demonstrate the Relationship Between Compliance and Hospital
Admissions
Stacked Column Chart to Demonstrate the Relationship Between Exercise and Health
Clustered Column Chart to Demonstrate the Relationship Between Check-up Frequency and Control
Scatter Plot Chart to Demonstrate the Relationship Between BMI and initial HbA1c levels
