How I Made the Charts That Reached Fauci

[Music] hey it's stephanie evergreen the founder of the data visualization academy so the academy has tutorials on how to make great charts in tableau are power bi even google sheets but i i think i'm probably best known for showing you how to hack excel to make great database and that's what we're going to focus on right now today is really all about smart decisions and you have already made one because you showed up here right now to learn how to make a high-powered chart with me i want you to know you can just relax okay you don't need to take notes this is being recorded after all so just kind of follow along with me and then when you're ready to practice this out with your own data just come on back and watch again okay another smart decision came from matt feldman who is in communications for research and development at novavax if you haven't heard of novovax they are a vaccine manufacturer they've developed a covid vaccine and matt knew that they were going to need absolutely stellar grafts to showcase the data from their clinical trials that was smart so he hired me to help also obviously a genius move together we talked through their data and and this is another smart decision we talked about the point he wanted to convey about their data so based on those details i drafted up a couple different chart options we tried a more basic clustered column chart a slope graph that is novel but messy in this case and the horizontal dumbbell dot it's always a smart move to test your data and a few different graph types to see which one really makes the story shine through matt loved the dumbbell dot even though he hadn't seen it before but he said it was going to have to be cleared by the scientists who crunch those numbers to make sure that they're okay with that kind of representation of their data so communications passed on to research who freaking loved it then matt ran it by the c-suite who loved it the c-suite ran by investors and i'm not allowed to tell you what they said but let's just say that the chart remained in the corporate deck afterwards so it became a part of their company's story it's the story that they told to the world vaccine congress the story they told to dr anthony fauci and the story that was on their fact sheet that made its way into the hands of every major news anchor in the world i mean wow it's just like a chain reaction of smart decisions last i heard this dot plot was headed into the new england journal of medicine so let me show you how i made these incredible charts we'll use them to talk about placebo versus vaccine but you can use them anytime you're comparing two groups or two points in time my clients at novavax live in a powerpoint world so i made these charts directly in their powerpoint slides but i'm going to show you how to make them here using excel it's the same method in both places since they're both microsoft programs our data will be on the mild local symptoms from dose ones so reactions people had to their first shot that were just a localized issue and a mild reaction okay prepare to be amazed if you want to amaze yourself download the worksheet at the link below and you can make this chart after i demonstrate we will create the dumbbell dot plot out of a stacked bar chart where the first stack is composed of the placebo values and the second stack is composed of the difference between placebo and vaccine values so that placebo plus difference equals the vaccine value get it okay so the first thing we're going to do is calculate the difference between the vaccine and placebo groups equal sign click inside this vaccine value minus sign click inside this placebo value hit enter then we'll copy this down the rest of the column here okay now we won't actually need to graph this vaccine data at all right we just need to graph the symptoms placebo indifference so i'm going to highlight columns a and b hold down my control key and highlight column d then i'm going to insert a stacked bar chart it's this one right here just a stacked bar nice and easy when you insert this chart you should see your symptoms along the y-axis here on this vertical axis if you don't see your symptoms then you're going to want to click this button that says switch row column or if you're on a mac it's going to say switch plot that's going to get your symptoms on the y-axis okay so our goal here is to add a dot to the right end of these placebo values you're going to love this hack all right so you're going to click on your placebo values just one of them it'll get all of them look up in your chart design tab at the top of your screen and head over to add chart elements click on this button and i want you to come on down to error bars and hover here you're going to get this pop off menu where i want you to select more error bars options and things get bad before they get great but hang in there and i promise it's all going to work out in the end so you're going to get this format menu that opens on the side of your screen that says for my error bars this is where we make with the magic and turn these things into circles so inside format error bars there are three icons we're going to start in this third one here the top section says direction and we're just going to skip that section altogether move down to the section that says end style and select the radio button for no cap that turns our errors into just a little stick we actually want the stick to be really little so come on down to error amount and click the radio button by percentage it's already gotten a lot smaller but let's go even further and replace this with 0.5 like a very tiny value we get these little pin points now for the error bars let's turn them into circles so of these three icons at the top of format error bars you're going to click in the first one and look for begin arrow type you open this up and choose the circle and there's your dot pretty cool right so you'll want to think about the color we i think chose this color for the placebo group and you'll want to think about the dot size if you want the size to get bigger you go to this width menu here and just increase the width you'll need to remember what number you land on because your other dots are going to have to be the same so i'm landing on 3.5 point amazing right super easy so this circle is representing the placebo data which means we're not going to need the bar to represent placebo at all so i'm going to come click on these bars and inside my format data series menu i'm going to open this fill section here and i'm going to say no fill so they they disappear but they're there to push my dots so they sit in the proper place now we're going to repeat this error bar process for the difference data these are ultimately what's going to represent our vaccine values when we put a dot to the right of these as well so we're going to follow the exact same steps we're going to go back to add chart element error bars more error bars options i'm going to start in this third icon we skip direction we go to end style and say no cap then we go to error amount and select percentage and change it to a small number like 0.5 that's a number you can play with for some reason your dots aren't working out i'm going to head over to the paint bucket and go to begin arrow type and choose the circle i'm going to make my width the exact same 3.5 i think it was and then we chose this color one more that's representing our vaccine values boom like we're pretty much done right but for further formatting we could decide to do a little bit with the dumbbell stick we want this to look more neutral so why don't we uh change this color to like a gray and then some people like it being this thick but you you have control over how thick it gets to do that you're gonna click on this third icon and adjust your gap width so the higher your gap width the skinnier your sticks are going to get awesome from here we we might want to do something to this x-axis with the novovax data they wanted their scales to go to 100 so we click on this axis and go to this fourth icon and we change the maximum to 1.0 to represent 100 to get rid of the trailing decimal you just come on down to number in this exact same menu come on down to number and change the decimal places here from one to zero we also changed the order of the y-axis so that any local symptom was on top and we kind of went greatest to least in order so that's actually the order we had things in the spreadsheet but excel graphs it in the opposite order that you have it super helpful so to change this i'm going to click on the y-axis and inside my format access menu i'm in this last icon inside this section that says axis options which might be closed you might have to click to open it okay what you're looking for in here is the checkbox that says categories in reverse order when you click that it's going to put your data in the opposite order so that now any local symptom is at the top when that happens it moved my x-axis to the top of the chart too now the folks at novovax really liked that look so we left it that way if you want your x-axis back down at the bottom you could adjust it in the same menu that's already open you're going to be looking for where it says horizontal axis crosses and you could say at maximum category that'll push it back down to the bottom or you could just leave it on automatic if you want it to be at the top okay we have to handle the legend it's not going to make any sense for people so you can just delete the existing legend you can just click on it hit the delete key on your keyboard or right click on it select delete either way we ultimately got our labels on our dots by using text boxes but you could really use whatever you like for your favorite excel hack to get labels in there so um to use text boxes we would go to the insert tab and click on text box and just drag it here into the spreadsheet maybe color code your font so that it matches your dot color i don't love how the grid line for 60 is running through this label so i'm going to actually fill it and there are several ways that you can do this but i'm just going to go to format shape right here because it's already open there's my paint bucket there's fill i'm going to click on solid fill and change that solid fill color to white cool right okay so i'm just going to copy paste this for placebo and we struggled a little bit with this one it didn't fit here very well so we ended up putting it up at the top and if that white fill is causing uh some of your values to get covered you can just change it back to no fill i don't think that one's really going to be a problem because there isn't a grid line running through those words last but not least i would take out my chart border you just click on the edge of it and go into your format menu and in border you're going to say no line and then you'd want to do something with your chart title so if this graph is going to live by itself you'd want to have a takeaway title up here that tells the story about what you're showing people in this data set if like with novovax this is just one chart among many charts that's going to go onto a slide you'd want your slide title to be that takeaway idea and you might use the chart title space here to just talk about the particular data set so something like um mild local dose one symptoms awesome easy right this chart is so fun to make because it pulls something amazing out of excel it's like pulling a rabbit out of the hat you feel like a total boss you impress your audience and you make your way to dr anthony fauci matt my guy at novovax he said vaccine r d is complex the kobet pandemic has taught us that to make progress against big global challenges science needs to be communicated more effectively than ever amen these dumbbells and waffle plots which i didn't show you helped us reimagine how we display our data and tell our story to different audiences perfect you can also use them to compare more than two groups sure you just have to do a little bit more in your spreadsheet it gets a little bit more complicated what if there was a case where the placebo group was higher than the vaccine group on one symptom that was definitely the case with some of our novovax data and it's just a little more complicated the directions for all of that along with video live inside my data visualization academy the database academy is my flagship online course we have over 200 videos on just about any chart type you can imagine well on the charts that are actually worth making and we'll teach you how to make them in excel tableau are like i said we even have those tutorials on google sheets and powerpoint and power bi but more than just learning how to push the buttons you'll learn how to think about the way you communicate your data in the first place i'll teach you the strategies and frameworks that make you a total pro at database our students use their skills to get promoted or to land dream jobs like kelsey who said i received my first evaluation consulting job as an associate for a firm because i was the only team member that could make pretty visualization that were easy for our clients to understand i got a similar email from liam the other day you told me i'm very thankful for the skills i've gained since starting my membership and believe these trainings played a significant role in helping me secure my first full-time job coming out of my master's program yes liam once you join you'll have instant access to everything i know about how to make great charts you'll be making magic like this before the end of the month you'll immediately have your hands on my which viz quiz a decision tree that helps you identify the right chart choice and then links you to the instructions on how to make it this one is totally a time saver and a game changer you get support through our monthly live webinar where i fix your database problems and in our fun community forums just for students and if you're the kind of person who needs to make awesome charts every once in a while no sweat you can come back to the academy anytime you want research templates freebies fun and unlimited access for fifteen hundred dollars we have budget friendly payment plans available too we also provide discounts for anyone from groups traditionally overlooked in tech and leadership settings so that would be any person of color white women anyone lgbtq plus anybody with a disability people who speak english as a second language and so on basically if you don't see yourself reflected in the list of ceos for fortune 500 companies you get a discount on enrollment check the link in the description for this video to get enrollment details we only open the doors twice a year if you're watching this with me live now is one of those times you could be diving into advanced charts in your favorite program or bridging your skill set to learn new software like chantel who told us learning how to use excel in a completely different way to create intuitive compelling data visualizations and learning how to use tableau beyond the basics has been invaluable to my career i've been able to add so much value to my organization by sharing eye-opening visualizations which have been incredibly useful for such a data-driven organization and my career field i love that you'll feel right at home among fellow data nerds from around the globe who are equally focused on using data to save the world or at least like your little corner of it students get even more bang for your buck through conversations with other students justin commented on this he said the academy has helped me be more creative in my own work developing data visualizations while also helping me appreciate the finer details and simplicity of a good design i value learning from our colleagues and i've appreciated the conversations that focus on weaving equity into our displays thank you for creating this community well justin we created together beyond knowing how to choose the right chart type for your data which itself is totally helpful you grow the ability to shift your company culture cam told us about this outcome when he said becoming a member of the academy has been one of the best choices of my professional life i've been given a great set of resources to help move my district and my own career in a positive direction after living a data life full of tables i went in and flipped them all over i love that it's no wonder hundreds of people sign up every time we open enrollment this is a monumental outcome at a really good deal those students all made smart decisions to come learn with me and enrolling in the academy will be your next smart decision too [Music] you

Channel: Evergreen Data

Views: 519

Rating: 5 out of 5

Keywords: datavis, dataviz, data visualization, graph, chart, dot plot, dumbbell dot plot, how to

Id: ppQHlDCUby4

Length: 18min 14sec (1094 seconds)

Published: Thu Oct 07 2021