The Association for Professionals in Infection Control and Epidemiology is holding their annual conference in Orlando June 26-28, 2023. I will be presenting a half-day pre-conference session on Sunday June 25, 2023 from 8 AM to 1 PM.
My goal in the session is to make you more comfortable with Microsoft Excel. In this preview, I download data from the CDC, do some simple clean-up. I use a few Excel formulas to add some useful metrics. I then display the results on a Map in Microsoft Excel.
Everyone who attends my session receives a copy of my book, MrExcel 2021 - Unmasking Excel.
My goal in the session is to make you more comfortable with Microsoft Excel. In this preview, I download data from the CDC, do some simple clean-up. I use a few Excel formulas to add some useful metrics. I then display the results on a Map in Microsoft Excel.
Everyone who attends my session receives a copy of my book, MrExcel 2021 - Unmasking Excel.
Transcript of the video:
Hello, APIC! You are coming to Orlando for your annual conference this year.
The day before on Sunday, June 25th, 2023.
I'll be hosting a pre-conference session on Excel for the Infection Preventionist.
My name is Bill Jelen and I run a little website called MrExcel.
I'm an entertaining and informing conference audiences for 17 years.
I am so excited to share my Excel tips and tricks with you.
There is great data out there, but it needs some cleanup.
My plan is to make you more comfortable with Microsoft Excel. I'm going to share some tricks on cleaning data, show you the amazing ways to summarize graph and find trends.
Now, the session runs from 8:00 AM to 1:00 PM on Sunday.
Everyone gets a copy of my Excel book to take home plus breakfast and lunch.
I'm kind of jazzed, they increased the room size a few times and there are still a handful of spots left.
You can still sign up.
My goal, by the end of the session, I want you to be comfortable enough with Microsoft Excel to do something like this example I'm about to show you.
All right, so here we are at the cdc.gov, some great data FluView Interactive.
I chose state over here, download data and show it by state.
Get me all states and as many seasons as they have, download data.
I get you a Zip file. Inside the Zip file is a CSV file.
All right, now, CSV files, those are perfect. They open in Excel with no problem.
And as I'm looking through the data here, this is great, region, state, year and week.
Oh, boy, that's a little bit of a problem because Excel really would rather deal with dates.
But I'll show you great formula to take this year and week data and convert it to a real date.
Total specimens, flu A, flu B, percent positive, percent A, percent B.
Yeah, this is interesting, but I think I really want to know total flu.
So we built a little formula here of total A plus total B.
How simple is that formula? Double click to copy that down.
And then here I have a great little table to get us population of each state.
Of course, California's going to have more flu than a small state like Rhode Island, so let's equalize that. And then flu per million.
Check out this cool little formula here.
So we take the total number of flu cases that week in that state divided by the population divided by a million, and we get a number that should be comparable from state to state, right?
If the percentage is high in California and the percentage is high in Rhode Island, even though the numbers are different, it should be an interesting metric to compare to each other.
All right, so all of this data over here in black came from the CDC.
All of this data in blue are simple little formulas that we can add to Excel, I'll show you those formulas.
And then we're going to take this data and visualize it on a map.
All right, now this is national data obviously, so we're going to see all of the states, but during the session, I'm going to show you how you can hyper localize this down to your facility.
If you have a map of your facility, we can look at the infection outbreaks as it spreads from room to room. All right, so here we go.
We're going to go into 3D map.
Most people don't realize that Excel has amazing mapping tools. Now, I've already baked this one.
We're going to go through this example slower in the session, but what we have is I have two layers over here.
I have the percent positive, so the tests, and then flu per million.
That's that new metric that I created using those extra columns. And in both of these layers, I have a time slider.
I've set those time sliders up to show us the date.
Now it is strange, I'm recording this, yeah, that makes sense, June 16th.
So the last date that the CDC has is from the last week in May.
All right, perfect. So we come back here to the very beginning.
In this particular case, the data changed in 2015. I get it.
So this particular data is limited just to 2015 and onwards.
And watch this, I'm going to animate this data over time in Excel and the percentage of orange.
So right here, we're kind of at the beginning of the flu season, and we should see the percentage of orange start to increase as we get into the winter like that.
So you're going to see it pulse from real light orange to dark orange each flu season.
Now watch this. All right, look at that.
Isn't that cool?
And we can stop this at any point, any point that looks interesting and go back and take a look and see what the numbers were for one particular state.
Right, they were clicking on Wisconsin and see that we're 16.83% positive that week, April 17th, which seems to me a little bit late, like it should be after flu season.
And maybe that's because down here in Florida, by April 17th, we are already into our summer or something like that, all right?
So this is just amazing that Excel has these tools and we can take a look at the data.
So if you're coming to Orlando and you're already coming to my session, wow, I'm so excited.
We're going to have fun.
If you're coming to Orlando and you're here early enough, there's still time to sign up with my session.
I have two questions for you.
First off, are there any particular challenges you have with your data in Excel?
You can leave me a note down in the YouTube comments. I'll reach out to you.
You can actually click on the about just below this video and find my email address if you want to send me specific questions. And then I have a question for you.
There is an amazing tool in Excel called the Forecast Sheet, and it can handle seasonality.
And I said, "Oh, this is going to be great. We're going to take a look at flu rates".
And sure enough, I downloaded the data and it is perfect.
There is that annual peak of flu, sometimes much worse than other times, but we have a really predictable peak, and I couldn't wait to throw this into the forecast sheet so we could forecast the future flu rates.
But, oh my gosh, look, we have an anomaly. The flu rates fell off during the pandemic.
Is that because people weren't going in to get tested?
Is it because they were all wearing masks? And so the flu rate really came down.
As a data guy I don't know how to deal with that anomaly.
We're not masked anymore, and people are going back to the hospital and life has resumed to normal.
So if this was your data, how would we handle that anomaly?
Do we go back and just assume this rate right here was the normal rate and then use that to project the future?
How would you go about doing something like that?
You're much more familiar with infection rates and things like this. I'm a data guy.
We'll figure it out together. Leave me a note down in the YouTube comments.
All right, so hey, we can't wait to see you all in Orlando, and if you're coming early, please check out my early session on Excel for the infection preventionist.
If you like these videos, please down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
The day before on Sunday, June 25th, 2023.
I'll be hosting a pre-conference session on Excel for the Infection Preventionist.
My name is Bill Jelen and I run a little website called MrExcel.
I'm an entertaining and informing conference audiences for 17 years.
I am so excited to share my Excel tips and tricks with you.
There is great data out there, but it needs some cleanup.
My plan is to make you more comfortable with Microsoft Excel. I'm going to share some tricks on cleaning data, show you the amazing ways to summarize graph and find trends.
Now, the session runs from 8:00 AM to 1:00 PM on Sunday.
Everyone gets a copy of my Excel book to take home plus breakfast and lunch.
I'm kind of jazzed, they increased the room size a few times and there are still a handful of spots left.
You can still sign up.
My goal, by the end of the session, I want you to be comfortable enough with Microsoft Excel to do something like this example I'm about to show you.
All right, so here we are at the cdc.gov, some great data FluView Interactive.
I chose state over here, download data and show it by state.
Get me all states and as many seasons as they have, download data.
I get you a Zip file. Inside the Zip file is a CSV file.
All right, now, CSV files, those are perfect. They open in Excel with no problem.
And as I'm looking through the data here, this is great, region, state, year and week.
Oh, boy, that's a little bit of a problem because Excel really would rather deal with dates.
But I'll show you great formula to take this year and week data and convert it to a real date.
Total specimens, flu A, flu B, percent positive, percent A, percent B.
Yeah, this is interesting, but I think I really want to know total flu.
So we built a little formula here of total A plus total B.
How simple is that formula? Double click to copy that down.
And then here I have a great little table to get us population of each state.
Of course, California's going to have more flu than a small state like Rhode Island, so let's equalize that. And then flu per million.
Check out this cool little formula here.
So we take the total number of flu cases that week in that state divided by the population divided by a million, and we get a number that should be comparable from state to state, right?
If the percentage is high in California and the percentage is high in Rhode Island, even though the numbers are different, it should be an interesting metric to compare to each other.
All right, so all of this data over here in black came from the CDC.
All of this data in blue are simple little formulas that we can add to Excel, I'll show you those formulas.
And then we're going to take this data and visualize it on a map.
All right, now this is national data obviously, so we're going to see all of the states, but during the session, I'm going to show you how you can hyper localize this down to your facility.
If you have a map of your facility, we can look at the infection outbreaks as it spreads from room to room. All right, so here we go.
We're going to go into 3D map.
Most people don't realize that Excel has amazing mapping tools. Now, I've already baked this one.
We're going to go through this example slower in the session, but what we have is I have two layers over here.
I have the percent positive, so the tests, and then flu per million.
That's that new metric that I created using those extra columns. And in both of these layers, I have a time slider.
I've set those time sliders up to show us the date.
Now it is strange, I'm recording this, yeah, that makes sense, June 16th.
So the last date that the CDC has is from the last week in May.
All right, perfect. So we come back here to the very beginning.
In this particular case, the data changed in 2015. I get it.
So this particular data is limited just to 2015 and onwards.
And watch this, I'm going to animate this data over time in Excel and the percentage of orange.
So right here, we're kind of at the beginning of the flu season, and we should see the percentage of orange start to increase as we get into the winter like that.
So you're going to see it pulse from real light orange to dark orange each flu season.
Now watch this. All right, look at that.
Isn't that cool?
And we can stop this at any point, any point that looks interesting and go back and take a look and see what the numbers were for one particular state.
Right, they were clicking on Wisconsin and see that we're 16.83% positive that week, April 17th, which seems to me a little bit late, like it should be after flu season.
And maybe that's because down here in Florida, by April 17th, we are already into our summer or something like that, all right?
So this is just amazing that Excel has these tools and we can take a look at the data.
So if you're coming to Orlando and you're already coming to my session, wow, I'm so excited.
We're going to have fun.
If you're coming to Orlando and you're here early enough, there's still time to sign up with my session.
I have two questions for you.
First off, are there any particular challenges you have with your data in Excel?
You can leave me a note down in the YouTube comments. I'll reach out to you.
You can actually click on the about just below this video and find my email address if you want to send me specific questions. And then I have a question for you.
There is an amazing tool in Excel called the Forecast Sheet, and it can handle seasonality.
And I said, "Oh, this is going to be great. We're going to take a look at flu rates".
And sure enough, I downloaded the data and it is perfect.
There is that annual peak of flu, sometimes much worse than other times, but we have a really predictable peak, and I couldn't wait to throw this into the forecast sheet so we could forecast the future flu rates.
But, oh my gosh, look, we have an anomaly. The flu rates fell off during the pandemic.
Is that because people weren't going in to get tested?
Is it because they were all wearing masks? And so the flu rate really came down.
As a data guy I don't know how to deal with that anomaly.
We're not masked anymore, and people are going back to the hospital and life has resumed to normal.
So if this was your data, how would we handle that anomaly?
Do we go back and just assume this rate right here was the normal rate and then use that to project the future?
How would you go about doing something like that?
You're much more familiar with infection rates and things like this. I'm a data guy.
We'll figure it out together. Leave me a note down in the YouTube comments.
All right, so hey, we can't wait to see you all in Orlando, and if you're coming early, please check out my early session on Excel for the infection preventionist.
If you like these videos, please down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
