Excel Data Visualization Part 1: Charts & Graphs

1. Course Structure and Outline: all right. Hello, everyone. And welcome to Excel Analytics, Advanced Excel charts and graphs. I'm so excited to have you here, and I'm so excited to share this content with all of you. So before we dive in, when we just take a minute and talk through the course outline and structure terms, of course structure. What's nice is that I've included downloadable cheat sheets for 20 plus chart types In Excel. You show you when and wide to use each type of visualization, including or basics like bars, columns, lines and pies, and more advanced creative options like surface and contour charts, waterfalls, tree map, sunbursts, even geospatial heat maps. Now the bulk of the content in this course takes place through screen casts and practice files. This is where all actually guide you through hands on demos every step of the way so that you can learn at your own pace. I'll start by covering your basic out of the box chart templates and will work towards more advanced, custom built interactive data visualizations, of course, outline starts with some data visualization. Best practices. I'll cover some key principles and talk about what I like to call the 12th rule and then walk through some good, bad and ugly examples of data. Viz Second will go into chart customization in Excel. This is just a quick primer to make sure that you're comfortable working with chart elements and formatting options and working with custom templates. Third, we get into the real meat of the course. This is all about mastering the basic charts, and graphs will review and demo each of those 20 plus chart types in Excel. And then last but not least, we've got our next level exercises. This is really what sets this course. Apart from the rest, I'll walk through over a dozen custom built advanced demos to really turn you into a data visualization expert, and I guarantee these air demos that you've never seen before and that you'd be shocked that Excel is capable of ready to get started. Let's do this 2. Key Data Viz Principles & The 10-Second Rule : Let's talk about the key principles of data visualisation because at the end of the day, it doesn't matter how technically proficient you aren't creating charts. All that is meaningless if you don't understand how to effectively tell a story. So number one stripe for clarity and simplicity above all else date of is is about maximising impact and minimizing noise. Don't be one of those people who adds all these bells and whistles to their charts. Tries to plot 456 data Siris on the same axis Just because it's an option. Listen, at the end of the day, if it doesn't add value or serve a purpose, just get rid of it. Second, always focus on creating and narrative data. Viz isn't just showing data, it's telling a story, so make sure you're communicating your key. Insights clearly, quickly and powerful e finally strike a balance between design and function. When it comes to design, selecting the right type of chart is critical and always remember that beautiful is good, functional is better and both is ideal. So I have a rule of thumb that I call the 12th rule, which basically means that if a viewer can't interpret the story within 10 seconds, it's time to simplify. So when I'm about to do is show you a chart on the screen with a 12th timer. At the end of those 10 seconds, I want you to pause the video and see if you can explain exactly what story that Sharp was intended to tell. Ready, Go, Time's up. So how do you do? You may have noticed that were plotting average temperatures here by city by month, and that certain cities follow one pattern while others follow another. But realistically, 10 seconds is not a lot of time to understand and digest that information. So now what I'm gonna do is show you a different visualization built from the exact same data set and restart the timer. Ready? Let's try again. All right, How do you do that? Time as you can see here by applying some date of his techniques like custom formatting and using intuitive color scales. And by actually eliminating the raw data points, we can draw attention to this story at hand, which is the relative trend and pattern that we see at the city level. So as you continue through the rest of this course. Always remember the three principles focus on clarity and simplicity, create a narrative and strike a balance between design and function. 3. The Good, The Bad, and The Ugly: Alright, it's time for one of my favorite sections of the course called The Good, The Bad and the Ugly. Now, in this section, I'm gonna walk through some examples that I feel are strong, weak and downright ugly cases of data visualisation. So first, let's start with the good. Here we have a nice overlapping area chart. It shows political polarization in the U. S. Also has an option to change the date range to show animations over time. Really nice, really simple, really effective. Next, we have a custom built pacing chart where we're looking at cumulative revenue over time against a goal or target. And here we're using some really clever, dynamic formatting rules to shade the difference. Different colors based on whether we're under pacing are over pacing that target and third simple, intuitive, custom designed gauge chart. This chart is really only intended to show one thing, which is where a particular value falls along a given range, in this case from 0 to 100%. But it's a nice example of how to combine individual chart types in excel to create something brand new and totally unique. So the good news is that I'm gonna show you how to build all three of these types of charts at the end of this course, along with many, many more. So stay tuned for that. Which brings us to the bad first case. Here we have a really busy, really cluttered radial chart. This is one of those cases where it seems to be really heavy on designed and really light on function, meaning I might print it out because it looks nice. But I really don't know what sort of insight I'm supposed to derive from this. Second, we've got a line chart with just way too many data. Siri's. There's no legend. There's no clear narrative. I don't even know where to start with this one. And then third good example of a misleading chart type. We're looking at monthly sales from September through January here. Technically, this is valid because the percentages do add up to 100%. The problem is when you're trying to show relative volume or trending over time, there are other charts that communicate that much more effectively. Like column charts, airline charts Last but not least, we've got the ugly. Here's one of those cases where you know, it's just way too many elements. There's a distracting three D design, another clear cut case of all design. No function. Next up, we have what seems to be a really simple chart, which is total revenue. April versus May. Now, if I asked you tell me what happened month over month, you might be tempted to say, Well, Chris, it's pretty clear that revenue plummeted in May. Now my response would be take a look at the Y Axis because the minimum is 175 K The maximum is only 189 which means we're looking at a pretty thin slice of the data here. And in fact, when you zoom out, that story completely changes in the context of the bigger picture. That's why it could be really dangerous to incorporate misleading access scales. Last but not least, or perhaps last and least. We have this infographic style visual, which uses improper use of percentages. They don't add up to 100%. On top of that, we've got inconsistent scaling, so somehow the 78% slice is actually thinner than the 61% slice, and in this case, the visualization actually does nothing but detract from the data itself. So there you go. Some examples of good, bad and ugly date of is 4. The 3 Key Questions: the last topic that I want to cover before we dive into it. So it's something that I call the three key questions. And these are the questions that you need to ask yourself before you start building or designing or playing with data visualizations to make sure that you're actually on the right track. So first, what type of data are you working with? This has a huge impact in determining which type of visualization you actually use. So whether you have integers, real numbers, categorical data, time series, data, geospatial, etcetera. So, for instance, categorical data tends to work really well with things like clustered bars or clustered columns. For instance, Time Series Data works great with line charts, and obviously, Geospatial data lends itself very well to maps. Similar kind of question. What are you trying to actually communicate? So what's the point you're getting across? Is that the relationship between variables? Is it a comparison? Are you looking at the composition or how composition has changed over time distribution? How things are trending. All of these stories need to be communicated in very different ways. So, for instance, scatter plots are a great way to show relationships, pie charts or 100% Stack columns are a great way to show compositions and so on and so forth. And then last but not least, who is the end user consuming this information the type of visualization that you build for a fellow analyst, for instance, is gonna look very different from the visualization that you build for a CEO. So keep in mind who's actually the end user before even start building. So with that, that wraps up our introduction. Next up, we're gonna be talking about actually customizing chart elements, formats and templates in Excel, so stay tuned. 5. Customizing Charts in Excel: Setting Expectations: before we shift gears into excel, it just want to take one minute to set some expectations. First, I'll be using Excel 2016 for all of the upcoming demos and exercise is what that means is that what you see on my screen might not always match what you see on your own and some chart types specifically tree maps, sunbursts, waterfalls, etcetera won't be available in earlier versions of Excel. Second, this course is designed with PC users in mind. That said, if you're a Mac user, by all means, feel free to join. Feel free to apply the same key concepts. But just keep in mind that the user experience is significantly different across platforms . Last but not least, if you're already in Advanced user, feel free to bounce around. This course really is a nonlinear course, meaning that you can explore the chart types, demos or content that's most relevant or interesting to you. And last but not least, as always, feel free to post on the discussion board or shoot me a message directly. If you have any questions along the way, 6. Chart Elements, Layouts, Colors, and Styles: Okay, so we're gonna get started with talking about inserting charts into excel and customizing some of the chart elements and layouts and colors and styles. So if you download the data visualization exercises Excel workbook, you'll see a number of tabs here for this section. We're gonna be focusing on this first time customizing charts, but you'll see moving left to right. There are a number of charts specific tabs with data sets already pre populated. That's where we're gonna be building out each of these different chart types tree map, sunbursts, waterfalls, radar surface charts and so on and so forth. As you continue to move to the right, you'll see these green tabs. This is where we're gonna do our next level date of his exercises. So let's go all the way back to the left and focus on the customizing charts tab. So I've got a basic sample data set here. I've got data by month with mobile spend desktop spent total clicks, transactions, etcetera. One tip that I like to use is whenever I'm doing anything with charts, graphs, dashboards or data visit in general and excel, I like to get rid of the grid lines and that really makes it feel more like a canvas to me as opposed to a spreadsheet. So you can go into the view menu and just uncheck that grid lines box Easy peasy. Now, I've got a nice, clean white canvas to work with. Now, in terms of actually inserting the charts into excel, I assume most of you already know this, But we're gonna work on our insert menu and this is kind of home base here, this chart section So there are two options for inserting charts. The first, you know, with any cell selected in the data range, I could just go in and say drop in a clustered column. And when I do that, Excel searches the data on the sheet and it says, Okay, this looks like an X axis. These look like column headers. This looks like the raw data that this users looking to plot. So in this case, I don't want all of these metrics to be plotted, especially something like transaction rate alongside clicks and spend, which is just on completely different scales. So I'm gonna delete that and take a different approach, which is to actually select the data that I want to highlight, and I can use the control key. What I'll do is select columns A D and E through Row 13 and then jumped back into that insert menu again. Choose Clustered Column And this time, as you can see, it's only plotting total spend and clicks, which makes a lot more sense. So once you have a chart inserted, you'll see this kind of top level tab appear called chart Tools and within chart tools. You've got design and format. Think of the design options as architecture of your charts. So how is it built? What features do you want to show you want data labels, access titles, chart titles, legends, trend lines, etcetera. Whereas format is more about look and feel, so do you want Phil options or outline options or shape effects built in there? So for this lecture, let's focus on the design options, and we'll start all the way on the left with this ad chart element option. So this is really the most important option on this tab because it allows you to insert or delete or modify any particular element of the chart. Now I'm going to go through this kind of quickly because I'll be using just about all of these chart elements in contacts later on in this course. So for now, I'll just give you the quick primer. Your access options let you add a remove primary, horizontal or vertical axes. You can add titles, which really is the best practice. You want to show the user exactly what they're looking at. You can add a chart title above or over laid can add data labels with different orientations. Data Table just puts the actual raw data beneath the chart. Error bars aren't particularly useful in this case, but when you have data with interesting standard deviations, you can use error bars to visualize those grid lines. You can customize vertical and horizontal as you see fit. Legend. Another kind of best practice here again. Show the user what they're looking at. Don't make them guess and then finally have got trend lines here that you can set for any particular Siri's you choose. And there you go. So those are the chart elements. Quick layouts are basically just prepackaged collections of elements, so these are ones that Excel has already built out that it thinks might be helpful for you if we're being totally honest. I don't typically use these just because I have all the rock pieces. I'd rather customized the view myself, but it's fair game to kind of poke through here and see if there's one that serves your needs. Similar with change colors, these air just prepackaged pallets that you can scroll through to change the look of your chart and then chart styles here. Similar idea to the layouts. Thes air. Just prepackaged styles, which include chart elements and colors and fill effects. And all that good stuff. So these air kind of your your final products almost again. I don't tend to use thes because I like to customize things myself, but they're there in case you want them on. Then you have a few other options. Here. You can switch the row and column so you can either look at spend and clicks by month or month by spend and clicks, which doesn't really make a whole lot of sense. In this case, select data allows you to modify your data, Siri's or Your Axis labels what I could do. Here's edit this total spend Siri's and just change the name to spend. For instance again, I'll show you many, many more examples of this as we move on, and then the change chart type option I'll also be talking about in a bit. This is where you can kind of a just and say All right, instead of clustered columns, let's do an area or let's do a pie chart. So there we have it, those air, the basic chart elements, layouts, colors and styles. 7. Chart Formatting Options: OK, now it's time to talk about chart formats, and I feel very strongly about formatting. I think it's one of those skill sets that tends to go very much under utilised. Thing is, if you take the time to master the options and capabilities that Excel offers in terms of chart formatting, you can build some incredibly beautiful, unique visualizations in Excel. So let's go back to our customizing charts tab. And like most things in Excel, there are multiple ways to accomplish the same thing on this case. I'm gonna show you two different approaches to formatting and chart, and the first is to select either the whole chart or a particular element within the chart . I'm going to select the entire one first and go into that chart tool center and drill down into the format options. Now these options are pretty high level. They're very much focused on colors and styles and effects, so what you can do is play with different fills. As you can see, it's filling the entire chart area. Since that's what I have selected, you can add an outline to if you want or shape effect, and just like we saw with Thea Elements in the design tab. There are preset styles that you can use again. I don't like using them. I like to create things myself, and so you can see the difference if I select an individual element now. Now I have some different options. And if I choose fillets Onley filling that element that I've selected on and same goes for these text effects. So I don't want to spend much more time on these options because, to be honest there, pretty weak. The way that I like to format my charts is through the second option, which is by right clicking any element within your chart and choosing the format, axis or format option, which tends to be the bottom choice in your menu. So because I've selected the Axis, I see format access. I can right click format legend. You can right click a Siri's format data. Siri's same goes with title, so if I click through any of these format links, it's gonna open up my formatting pane to the right of the screen. And this is where I have full access into the most granular formatting tools and options. You can imagine any element within your chart has its own set of formatting options. So right now, since I clicked through the title formatting, I'm seeing a pre selected list of options that could be applied to the chart title so I could do Phil options here. If I want to give it some sort of background, I could do that. I can give it a border. Let's just make it a dark grey border. And what's important to keep in mind is that I'm actually not limited to just the options here in this formatting pane. I can go all the way up to my home tab and make any adjustments to the font or alignment that I could with any other text. So let's make this chart title bold and let's drop it down to size 12. So there you go. And what's awesome about this format panel is now. It could drop down this little arrow spinner here and see a menu of elements that are available for me to format. So I'm in the chart title options right now. I could go toothy vertical value access here, and you can see a whole different set of options appeared now There are hundreds and hundreds of options and sub options within this formatting pane, so I'm not gonna go through all of them. But what I will do is show you a number of these different choices and options in context as we go through demos and exercises later in the course. So I'll show you just a couple here. The access options include a new Siri's here or a new setting here with a bunch of drop down. So access options. Let me do things like change the minimum or maximum again, Being careful not to show a misleading or skewed access can change things like the tick marks. You know, it could make inside tick marks for the majors. I can also change the units so run outs counting by 100,000 that can change that to count by 200 thousands and eliminate some of the values. Or the labels can also change the number from currency to a number with it without a separator. And again, just like the chart title conjunction my home menu. If I choose, maybe drop down the size, maybe make it bold if I choose, you know that's up to me now. The last thing I'll note is that you can also select and just straight up remove elements of the chart if you choose to. So these data labels obviously are just really busy and distracting. So you can select one set of data labels and just hit delete, and they go away doesn't change the underlying data or anything like that. It just removes the labels, and I'll show you one more example before we move on to right, click the data Siri's. And here I have specific Siri's options, and this is great. This is where it could plot on a secondary axis. Or it could change the overlap or the gap with. So let's say I want to make these columns a little wider. I can go ahead and change these options right here in my serious options. So there you go. That's your quick primer on formatting charts. 8. Changing Chart Types & Adding a Secondary Axis: Okay, next up. Just want to take one minute to talk about changing chart types and adding things like a secondary axis. So right now on plotting total spend and clicks as a clustered column both these air volume metrics they're both on a similar scale, so it makes sense to compare them on the same axis. But let's say that instead of spend and clicks, I want to visualize, spend and cost per click, which is a rate metric that's on a completely different scale. What I can do is right click my plot area, choose select data, delete or remove that click option and add a new Siri's. So the name of this Siri's is cost per click and sell F one and values are the values in F 23 ref 13 kid. Okay, and then horizontal Axis labels can edit those as well, just to make sure that all of my Siris are reading from the same horizontal axis. Then hit Okay. As you can see, it's brought the field in, but we can't see it because it's so small compared to total spend. And because we have this access minimum set that's well above the values for cost per click . So my option here is to go on a chart tools change chart type. And again, this is where you can just change the format or change the chart type toe any of the stock options in this case I want to go all the way to the bottom two combo. And here's where it introduces the secondary access option. So Excel is saying, Okay, you've got to Siri's, you've got spend and cost per click. It's already recommending that I make my cost per click field a line, and because it's on such a different scale, all you need to do is check that box. And now, in the preview pain, it shows that Okay, I've got cost per click mapped out on my secondary axis and spend still mapped out on my first axis. And one kind of side note about using combo charts is that if I try to keep this as a clustered column, you'll see that serious are completely overlapped. And that's because now each Siri's kind of lives in its own axis, and so you lose that side by side effect that you get with a traditional clustered column with both fields on the same axis. So in this case, a line is actually just a much more effective way to visualize this trend. So I can hit. Okay, And there you go, so I could do the same formatting options on this axis. I'll make it bold and a bit smaller, and that's the first way to add that secondary access. The second approach is to start in the chart right click format the data Siri's itself, and you get your same two options there as well. So secondary allows me to visualize that serious because it's on a completely different scale. So there you go, changing chart types and adding a secondary axis. 9. Creating, Modifying & Applying Custom Templates : Okay, The last topic that I want to hit before we go into the actual building of charts is templates. So, as you might imagine, because there are so many different elements that we can tweak and customize and style eyes , we may very well come up with a style that we really like and that we want to apply to a different data Siri's or a different chart altogether. And that's where templates come into play. So let's take a look at our combo chart that we built in this customizing charts tab, and the first thing that we need to do with this is eliminate those restrictions that we had set on the Y axis. And the reason is that those restrictions will carry through with the template and prevent us from applying it to a data Siri's that might not fall in that same range. So I can just right click, going to format access with that primary vertical selected and just reset my minimum bounds . Reset my major units and now Excel will automatically choose what those bounds look like based on the data set that's feeding into the chart. Um, and now I'm going to just make a Siris of stylistic changes and formatting adjustments. You can follow along step by step or you can choose your own. It really doesn't matter. It's up to you. So what I'll do is select my secondary. You can see that it defaulted back to the, uh, the default format there. So we're going to the home tab, make it bold and size eight just for consistency. And I'm gonna make some changes to the, uh, to my line chart cost per click data. Siri's go into the Phil and line options that you want to make this solid line Nice, Dark blue and one of my favorite options all the way down the bottom Here is this smooth line check box that just changes it from a jagged line to smooth, which I personally prefer. Now I'll select my spend Siri's. I'm gonna give it just a light, bluish gray Phil and a dark blue border. I'll change the width to 1.5. So there we have it. Let's say that I've made these adjustments and I say, OK, this is the perfect style. I want to apply this for all of my charts that are showing similar column and line metrics . So what I can do is just right. Click and shoes save as template and let's just name it combo chart. It's safe now. Here's the magic I'm showing spend and cost per click. But what if I want to show transactions and transaction rate? I could just select X values control, select G and H and now insert So there are a couple approaches that I can take here. I can click the recommended chart option and tab over to all charts. Or it can cancel that and choose any one of these options and go down to the more chart option at the bottom of the menu. Either way, it will take me to the same place. And once I'm here, you'll see this templates folder right near the top. And there's my template. I could double click, and it immediately applies all of those settings that we had created to my new data. Siri's So as you can see here, I'm looking at spending cost per click my top chart and transactions in transaction rate. My bottom chart. Both are formatted with the exact same style. So last but not least What if we want to make a slight adjustment to the template? I'll go into my first chart again and let's say OK instead of a light blue Phil, we actually want this to be a light green. Phil, I've made that change. All I need to do is right Click say this template over right, that combo chart template that we had built or it could name it Calmer chart underscore to whatever. And now back in my second chart, just going to chart tools changed chart type. Back in the templates you can see the template has updated, just double click again. And there you go. It's updated with the new style and format. So there you go quick primer on using chart templates. 10. Bar & Column Charts: all right. At this point, we've gone through the groundwork. We've talked about date of his best practices, gone into inserting customizing formatting charts and templates and excel. Now it's time for the good stuff. Now it's time to actually do some deep dives into each of these chart types. So we're gonna kick things off with bar and column charts, one of the most common chart options in Excel and these air commonly used for things like comparing numerical data across categories. The only real difference between a bar and a column is the orientation. So bar charts tended to a really good job at categorical comparisons and column charts can show the same thing or also introduce some element of time serious trending so data by category by month by year but quarter, whatever it might be. So some examples of data sets that you might visualize with bar or column charts include things like total sales by product type population by different countries or revenue by department by quarter. So pro tip here any time you're viewing data by multiple categories or multiple sub categories, for instance, by month, by genre or by department by quarter, you need to use stacked or clustered bars or columns that allows you to group by those sub categories or compare multiple metrics. And I'll show you an example of that in just a second second pro tip. You can use custom formatting rules toe actually color code bars or columns dynamically based on their values. And that's actually a lot trickier to do than it sounds. And it's one of our next level demos towards the end of the course. So stay tuned for that. With that, let's dive into Excel and actually get her hands dirty. Okay, once you've opened up the Excel workbook, go ahead and click on the first Orange tab bar and column chart tab. And let's see what data we have to play with. Looks like we've got ticket sales here organized by genre, and by month we also have aggregate values in column end, which is total sales by genre as well as rose seven, which is total sales by month. And this is a great data set to use for something like a bar column chart, because it's categorical data with a number of different dimensions that we can visualize. So why don't we start with a simple bar chart, I'll go into insert Top Left is my bar and column options due to D Bar. You'll notice that Excel didn't really have, ah, best guess for me. So it just left the visualization blank. That's okay. It just means that will insert the Siri's ourselves. So right Click. Choose select data when you add a Siri's and for the name let's call this sales by genre, and the values will just be those totals and cells and three through End six. So press OK, and once the press OK, you can see that it populated the values. But the Axis labels are kind of just default. 1234 values so I can edit those labels right here. Click at it now the Axis Label range. It's just the name of genres in a 336 press. Okay, and there you go. It's applied them. So once I hit OK, again, I'm all set. That's my basic bar chart Now. It just comes down to formatting as you see fit. So choose the X axis. Let's just reduce that text size a bit, so it's more readable. Let's make our labels bold increase the size to make him pop a little bit. We can go into the format data. Siri's reduced the gap with a little bit, and this is totally up to you how you want to format things. So there you go. This does a really good job showing relative sales volume by genre. So it's immediately clear that comedies drive the most sales, followed by thrillers than romance and last but not least, documentaries. So let's do a similar exercise, but work with a column chart to show that totals by month. Same thing when you insert this time a two D column right click. Select my data. Add a Siri's. We'll call this one sales by month, and the values here will just be B seven through M seven and again, same exact case just dropped in default values for my Axis labels so I can edit and just select the months since those air the labels that I want to appear along my X axis. So OK, great, I'll just do a little formatting. Reduce the size of my ex labels will make my y axis bold. And there he goes. There's a basic column chart. Now if we want to get a little bit more fancy and we want to show the actual cross tab data in other words, the data by genre by month, that's where we can start playing with things like clustered or stacked columns or bars. So one kind of demo that I want to show you if I try to insert the chart the same way. So let's just do a column and go through that same process that just did select the data at a Siri's. And let's say we call it sales by month and genre. If I try to select values in a two dimensional array like this, Excel will be unhappy. It's gonna tell me that the reference is not valid and that it needs to be a single cell row or column. So what I have to do is cancel out, and if I were to do it manually, I would need to add one Siri's at a time. So the Siri's name would be comedy for the first values would be the comedy sales by month . Okay, at another one where the name is Thriller and the values air the thriller sales by month. And as I do this, you can see that it's adding, you know, these additional Siri's. You know the documentary documentary sales by month, so it's clustering them as an adding each Siri's manually. But there's a better way. So I'm gonna delete this, show you the easier way, which is starting with the selection in place. Now if I go to insert Clustered Column, it knows what I'm trying to show. So it's recognized the label headers and access labels based on the original selection. So there you go. This is a perfect example of a clustered column chart, which now shows two elements or two dimensions of the data, which are sales by category or by genre by month. So I could give it a chart title here, sales by genre and a month. Let's be consistent. There we go. And again I conform. At this, however I choose, I can kind of drag things, reposition things. I'll make the Y axis bold again and out. Here's what's great is that this is a really nice way to tell a story that I'm trying to tell. Which is how did ticket sales evolve throughout the year by different genres. But there are some other options that I have to visualize this. And if I go into my chart tools, change the chart type one option is right here. The stacked column. So if I select stacked column now what it's showing me, it's the height of the entire column. Includes total sales for the month, and the individual components of that column show how those sales air distributed by genre . So this tells kind of two interesting pictures gives you the sense of total ticket sales, which, as you can see, mirrors the single column chart that we just built. But it also adds that element of Okay, what's the composition of those sales? How many of the sales were driven by comedy versus Thriller Verses, documentary etcetera. So a really nice, powerful visual and the final option here again I can go into change. Chart type is the 100% stacked column, which does the same thing, except it just normalizes all the volume to 100% and it shows the composition by genre based on a percentage. So what percentage of ticket sales in January did comedy make up versus Thriller and so on and so forth. So there you go good example of when and how to use bar and column charts in Excel. 11. Histograms & Pareto Charts: the next types of charts that will dive into our hissed A Grams and Pareto charts. They sound super fancy, but trust me, they're not that complicated. The one thing I will preface is that hissed a grams look a whole lot like column charts, but they're very, very different. So don't confuse the two column charts. Air designed to show volume, hissed a grams air designed to show frequencies. So along the X axis of a hist a gram, rather than having category labels, you've got bins or ranges of values where the height of the rectangle associated with those bins indicate the number of observations that fall within those ranges. A Pareto chart is just a slight variation of a hist a gram where the bins are reorganized from highest frequency too low and a line is over laid, showing the total cumulative frequency. So these charts are commonly used for showing the distribution of a continuous data set. It's great for showing things like the frequency of test scores among a student group or distribution of a population by ages or by age group. One of the most common examples is to show how a population is distributed in terms of heights or weights. Eso pro tips here The first is that you can actually adjust the bin size to customize how you want a group. The values or ranges of values and second Pareto charts are a great way to show the cumulative impact of each been ordered by frequency or significance. So it's practice building some of these. Okay, so in your data visualization exercises workbook, go ahead and click the hissed A gram and Pareto charts tab here. We've got a great little sample data set with a number of observations that include names, heights, weights and ages. If I scroll down, looks like I've got just over 1000 observations, which is great, hissed. A grams tend to work best when you have a large data set containing continuous variables. So in this case, the goal is gonna be to show how heights, weights and ages are distributed among this sample population. So all I need to do is select the column that I'm interested in. Let's start with height and I'm going to insert my statistic chart section and choose hissed a gram. So Excel drops in and automatically determined what it think makes sense for bin sizes. I can actually adjust these as I see fit, so these bins represent ranges of heights in inches and the number of observations that fall within those ranges. So this tells me that there 89 people in the sample who are between 70.2 and 71 inches tall , so it's a been size of 710.8 inches. It can right click and format the axis and customized the bins myself so you can see it defaults to automatic. This case, let's set that been with toe one inch just to make it a bit more intuitive and readable. So you see kind of a normal distribution, this classic bell curve here, where the majority of observations fall within a relatively narrow range of heights. And then you've got some outliers on either end of the tails. So let's add a title just so that it's a bit more clear what we're looking at. Let's call it height distribution, and there you go. So now if I want to create the Peredo version of this, I'm not going to start from scratch. All I need to do is copy this, hissed a gram paste it here and I can either going to chart tools changed chart type or it can right click change chart type. And here's my burrito option. As you can see, all it did was just reorganized those bins from highest frequency to lowest and overlay that line chart showing cumulative frequency. So if we wanted to do the same thing for weight again, let's not reinvent the wheel here. Let's just copy what we've got. Paste them right here below and with my first hissed a gram selected, all you need to do is drag this reference that Excel has highlighted for me over in the Wake column, and there we have it. I'm done, so I'll do the same thing and and customize my bin sizes right now defaulted to £7.3. Let's make it £10 just to make it super clear and changed the titles that we're not looking at heights anymore. We're looking at weight distribution and then same case with my Peredo boom. Just drag it over format my axis change the been with to £10 and change my title to wait. So now same exact process here can just copy those, paste them down below De select and and go into my weight hissed a gram and just drag it right on over. Age customize the bin sizes. Why don't we make this, uh, two years instead of 1.5? Change the chart title age distribution and then, you know the drill. Same deal with burrito dragging over age format. The axis customized the been wit, too, just so that we're being consistent and will change that title to age distribution. So they're going about two or three minutes have created hissed a grams and Pareto charts showing how this population is distributed in terms of heights, weights and ages. So good example of how to use sista, grams and parade of charts in Excel. 12. Line Charts & Trendlines: Okay, next up, we've got our trusty old friend the line chart. I'm sure just about all of you are familiar with what line charts are a Z. You may have guessed there commonly used for visualizing trends over time. So examples of when you might use line charts stock prices by our by day average temperature by month. Profit by quarter. You know the list goes on and on. The idea is showing how value changes over time. So Pro tips First is that you can use trend lines linear polynomial logarithmic on whatever you choose to visualize patterns and visualize lines of best fit and use those to forecast future periods. You can also combine line and column charts or other types of charts to trend two variables on different scales, and we saw that with the spend and cost per click data back in section one of this course. So let's bounce on over to excel. So once you're in Excel, make sure you've got the line charts tab selected. And as you can see, we've got a simple data set here, just showing closing stock prices by day for Google and Amazon. And if we want to implement or insert a line chart Very easy here. Just select all of column a shift click column C So I have the entire range could go to insert here in my line chart options. So, as you can see under two D line, I've got your classic line option and do a stacked line which essentially, uh, adds the values from the to Siri's, which in this case doesn't make any sense. Do 100% stacked, and you can choose to add markers or leave them without markers. Keep in mind you can always add markers later. So So I'm just going to select the basic line here and there you have it. It's plotted out daily stock prices for Google and Amazon. Let's just, uh, give this a title, so we know we're looking at closing Price and because I'm trying to look at two things here , I'm trying to look at the relative performance in terms of stock price between Google and Amazon, and I'm trying to see how those prices have changed over time. And this line chart gives me a great visual, great insight into that, So to make it a bit more readable, since there are no stock prices that ever closed down in the 12 $300 mark in a right click and format that Y axis and at a minimum bound of 300 that just helps to emphasize the difference is a little bit make it a bit more readable. So this is a great data set to practice using trend lines with so one of go to chart tools designed tab and add a chart element. And here we can select any type of trendline we choose. Let's start with the linear and let's apply it to the Google data. And there you go. It's added a linear trend line. I want to apply a trend line to Amazon. I could go through that same process, or the shortcut is just to right. Click the Siri's and choose the ad trendline option here. And then once you've added a trendline, it becomes a new chart element that can be independently formatted or stylized. However you choose eso. I've already got my format pain open, but if you don't you can right click the trend line. Choose the format trendline option and you'll see this view right here So this is where with either trendline, you can determine how it's defined. Exponential, logarithmic, etcetera. Let's just keep the Google trend line as linear you can also do is insert a forecast period . Now your periods are defined by how granular data set is. Since we're looking at daily data, one period equals one day. So if we want to forecast out a month, for instance, would type in 30 periods and you can see that Google Trendline extend an additional 30 days . So let's do something similar at the Amazon trendline, and we can play with polynomial here. Make it 1/3 order polynomial. Granted, this is over fitting the trend line a bit, but it's interesting to see kind of how it trends, and we can apply the same forecast here of 30 periods. And as you can see, based on these two trend lines, which may not necessarily be statistically valid, we might expect the prices to converge by the end of June. The last thing I'll note about line charts and trend lines is that you can also show the equation of the trend line or the R squared value on the chart and what the R squared value essentially does is it indicates the fit of that trend line to the data set. So it's a number between zero and one where the higher the number, the better the fit. So in this case, that Google Trend Line has an R squared of 56% and the Amazon data has an R squared of 68% . So there you go very simple, very clear demonstration of how to use line charts and trend lines in Excel. 13. Area Charts: already time to talk about one of my all time favorite chart types, the area chart and the reason I love the area charges because it can pack a ton of insight into a relatively simple single visualization. It's commonly used for showing changes in data composition over time. Although there are a few ways to build area charts, the default area chart actually shows overlapping Siri's out like the screenshot shown in the bottom here. Or you can use a stacked area chart just like you would use a stacked column toe actually show the composition trending over time. So a few examples here. Sales by department by month. Downloads by browser by week, population by continent by decade. All of those air great examples of one. An area chart can really paint an insightful picture. Couple Pro tips here for stuff. Just keep the number of unique categories or data. Siri's low. I'd recommend fewer than six, really, just to maintain clarity. And second, we're gonna dive into this in the next level demos towards in the course. But there are some really clever ways to use data validation and custom formatting to dynamically highlight specific slices of data or Siris of data within a stacked area, so it gives it a very cool kind of interactive, dynamic effect. So it's bounced on over to Excel and build some of these already. So once you're in Excel, go ahead and select the area charts tab and the status that should look familiar. This is our same 2015 movie ticket sales by genre by month, the same day that we used to build out our bar and column charts. And, in fact, the area charts that we're about to build well, actually tell the same story that we're starting to tell through the stacked column charts . So let's go back to our area charts tab. And if you recall, when we create source data for a chart that's two dimensional something like a two through M six, it's helpful to make the selection first and then insert the chart. That way, we avoid having toe add individual Siri's manually. So our area charts live together here in the line section, and I'll start with a traditional two D area. And as you can see, the traditional version of an area chart actually overlaps the different data. Siri's, which isn't particularly helpful in this case because actually can't see what's going on with comedy sales after June or July because they become obstructed by thriller sales. One way to try to get around. That might be to do some clever formatting where you change the Phil to something transparent. I could do the same with a number of these different data sets just for sake of example. But even so, it quickly gets distracting and messy, especially if you have more than two or three data Siri's so that's not really going to serve our purposes. I'll delete that, and instead I'm gonna insert a stacked area, which is the second option, and this is more helpful for two reasons. First off, we can see each of our data. Siri's independently and second. It allows us to see overall volume shown by the total height of the area, as well as how composition changes. As the months go by. Eso. It's really doing exactly what we're trying to do. Let's give it a chart title. It's a bit more descriptive ticket sales by genre by month, and then just for sake of example, I could copy this and paste it here just to see what a 100% stacked area looks like. Change the chart Type two. The third option, and there you have it, So just re scales that Y axis to a 0 to 100% range. Now you may be asking yourself, What's the difference between this area chart we just built and the stacked 100% columns that we built earlier in the course? And to be totally honest with you, there isn't much of a difference. In fact, I'll show you. So let's paste this chart again and change the chart type to a 100% stacked column. And when I do that, you can see that essentially, the area chart is the same thing, just without the gaps between the Collins. So I format the data Siri's and dropped the gap. With down to zero, it becomes even more clear, so it's really the same picture here. It's just that our area chart is a bit more smoothed out. So there you have it a good example of how to use area charts in Excel to show changes in composition over time, 14. Pies, Donuts, and Race Tracks : All right, Next up, we've got pie and doughnut charts. Sure, you already know what a pie chart is one of the most basic chart types out there. It's really just used to compare proportions totaling 100%. But if you don't know much about doughnut charts, you'll probably want to pay attention because doughnut charts aren't just pie charts with the center cut out, which is how they got their name. But they also introduced some capabilities that go well beyond what a pie chart can do. So, for instance, you can add multiple rings to a doughnut chart like this to show additional data Siri's or metrics within the same visualization. You can also get clever and build some sort of a race track or pacing style visualization like this, which I'll show you in just a minute. So examples of one you would use either a pie or doughnut chart anything that adds up to a percentage of the whole So percentage of budget by department Internet users by age range a breakdown of site traffic by source. You get the idea so pro tips here, first off, just like areas and clustered or stacked columns keep the number of slices or data serious , small and recommend fewer than six again just to maximise readability. And Second, like I mentioned, that doughnut chart is really cool way to either visualize more than one Siri's at once, or to create custom racetrack style pacing visualizations. All right, so head on over to Excel. Go ahead and click on the pie and Donut chart tab. Here we've got a small, pretty basic data set. We're looking at category level cost and revenue for peril, electron ICS, toys and games and housewares. We also have some additional fields here. Revenue, gap, revenue target and percent to target these air, the fields that will be using to create that custom racetrack visualization that I hinted at earlier. So why don't we start simple and just include a basic pie chart showing the distribution of cost by category so I can select a one through Be five insert basic too deep I and there you have it with most pie charts. I like to add some data labels, and rather than show the value, I'd prefer to show percentage. Then you can position those however you choose. I'm going to make them bold and just increased the size a bit. So obviously nothing to write home about. But the pie chart is doing its job. It's showing which categories drove the largest share of spend. So in this case, peril drove 38% electron ICS 27 so on and so forth. So if I go ahead and copy this entire chart and duplicate it, I'll show you what it doughnut chart looks like. So I could just change the chart type, go to this fifth option, which is doughnut and, as is all it does, is just remove the center hole and make it look like a doughnut, which you might think is kind of silly and could certainly just be an option with a pie chart. But it actually did much more than that. What it allowed me to do now is select my data and add a new Siri's. We're gonna add a Siri's called revenue with the values from C two through C five. And when I do that, as you can see now, it's created a second ring with the same category level data, just showing a second metric. So if I tried to do that with my pie chart. Same exact approach at a series called Revenue with C two through C five. It doesn't know what to do. It doesn't do anything, in fact, so that's one huge benefit of using a doughnut chart. Now the only catch using doughnuts like this is that the legend won't really tell the user what the differences between the inner ring and the outer ring eso we might have to get a little bit clever. One option is to edit your data labels and, instead of a percentage show the actual Siri's name. So that way this is telling me. Okay, this inner ring is showing cost. I could do the same thing at data labels to my outer ring, and I conform at those to show Siri's name as well. Don't need value. Don't need leader lines that could make those bold and a little bit bigger just to make them consistent. Obviously, there's a lot going on here. One kind of customization that could make is to format the data Siri's and just shrink down the hole size of the doughnut a bit just to give a bit more room for labels and actually don't need all of these labels. I can go in and just pinpoint and delete three out of four because really, obviously they're They're all telling me the same thing s if I just leave one that will help tell user. Okay, I'm looking at a cost in my entering revenue in my outer ring. You know, if you want to take it one step further, I'm just trying to resize this to make it a bit more readable. Uh, you want to take it one step further and you want to show that this blue segment is associated with the same blue segment in the outer ring, but that we're looking at two different things. You can change your fill options, and what you can do is choose the same color blue. But maybe with a little bit of ah, transparency through the same thing with these other colors just to show you kind of what I'm getting at. So choose the same orange here with some transparency. I'm gonna choose the same gray with some transparency and last but not least, some yellow. So that's just kind of one way toe to stylized things a little bit and show the user. Okay, We're looking at the same category, but two different metrics. So that's that's a doughnut chart. Next up, I'm going to show you the fancy pants racetrack chart, which uses a doughnut, but it's a little bit of a hack, so I'm gonna go through this quickly, try to bear with me. So what I need to do here is use this new data that includes the revenue gap in the revenue target as well. So must select my source data, which comes from column A and then control Click Column, C, D and E. That's my source for my race track. So I'm gonna insert a doughnut using that as my source and the first thing I need to do. Just get rid of the chart title. And I'm not gonna need a legend in this case, believe it or not, Um, and finally, I'll choose the format data Siri's to reduce that doughnut hole size, um so that it's a little bit more easy to work with. So now what I'm looking at here. As I select these rings, you can see exactly what it's referring to. Each ring represents a different metric where each segment of the ring is a category. This actually is not what I want. What I would like is for each ring to represent category and each segment to represent one of the metrics. So I want one segment of the ring to represent revenue. The next segment represent the revenue gap and the final segment to represent the revenue target. So to do that, all I need to do is just switch my rows and columns. And now if I select the outer ring, you could see it's a horizontal source array. The outer ring is housewares, where each segment is revenue, revenue gap and revenue target. So we're getting closer. The next step that we need to do is just eliminate these gray pieces because really their only there's placeholders to calculate 50% of the full circle so I can go into my fill options and just select no Phil. And that essentially just makes make sure you only have the great piece selected, essentially just makes it invisible. There you go, so we're getting even closer. But the catch now is that each ring has the same exact color palette, so it's impossible to tell, uh, which category they're associated with. So what I'm gonna do is use the same palette that was determined by my pie chart just for consistency here. So this outer ring is housewares, which was the yellow slice. So I'm gonna drill down into this blue piece. I'm just gonna fill them myself. So the first pieces actual revenue driven by housewares, the second segment is the revenue gap. So it's still associated with housewares, so I'll do the yellow Phil, But just like before, I'm going to give it 50% transparency. And now we'll go through the same process. Toys and games is my great column. So I want this slice to be gray and the toys and games gap to be grey with a 50% shade. And then here we go for electron ICS, which is my orange slice. So I'm gonna give it a solid orange, and this little bit is going to be solid orange with 50%. And then last but not least, got apparel, which is blue. So that slices. Okay, just gonna fill that last little piece with blue and 50%. And there you go. So now we're getting really, really close. This is actually showing how much each categories pacing. The problem is that because we kind of hacked this thing together, we can't use the default labels in the way that they're designed to be used. But that's not a problem. We can just insert text box. I'm gonna do this over here, so I stay away from the chart. It's gonna type in each of these categories, so housewares, toys and games, electron, ICS and apparel and I'll just select them all. Make them bold will do a write a line here and last but not least, uniform at this shape. I don't want to fill it. I don't want a line, and this is kind of like my manual little version of custom labels so I can increase the size a little bit just to make it a line and overlay it right on top of my chart. And there you have it. It's kind of via the roundabout way of adding your own chart labels and now last but not least, if you want to get some next level customization in here, you could insert another text box, and instead of typing in the value in the formula bar. You could just say I want this text box to equal the value and sell F two. So I want this to be my apparel percent to target. When you hit, Enter it, populates it with the value you can then format that value, just like you would otherwise could make it bold. You can increase the size, could drag it over this one's apparel. So I put it right here. Let's just do the same formatting rules. No, Phil, no line, Uh, and you could repeat the process for the other four. But essentially what this does is now. If the apparel revenue target changed from 190,000 for instance, 230,000 the segment of the racetrack changed as well as the percentage. So let's see that one more time. Let's say that the target is actually way higher. It's 250,000 now was similarly 47%. If it was 400,000 we're only 30% eso. It's just obviously a lot of work. It's it's pretty manual, little bit tedious, but it's a really interesting way to use a pretty basic chart type and excel to create something totally custom Eso you go that's using pie charts and doughnuts to show percentages and excel. 15. Scatter Plots: All right, let's talk. Scatter plots. Scatter plots are a great way to explore correlations or relationships between Siri's. So, for instance, if you want to know if Major League baseball players who hit more home runs are more likely to earn higher salaries, a scatter plot is the perfect way to address that question. Other examples could be measuring the relationship between ice cream sales and average temperature. Looking at hours of television watched by age literally any two data sets that you want to test for correlation, you can use a scatter plot to do so. Pro tips here first and foremost at a trend line or line of best fit to show correlation between variables. You can also add an R squared value like the ones in the screenshots, to really quantify how strong that correlation really is. Second, last but not least, remember that correlation does not imply causation. What that means is that two variables or two data sets might move in the same direction and might seem very tightly correlated. But that does not indicate that one causes the other. Here's a perfect example. We see a very close correlation between ice cream sales and deaths by drowning. Now ask yourself, Do you think ice cream sales are actually causing drowning deaths? Of course not. They're both associated with higher temperatures and summer seasons, so both tend to reflect the same seasonality with that. I've got a great data set in Excel that we can use to build some of these scattered plots. So let's go ahead and open up our workbook. All right, so here we are on the scatter plots and bubble charts Tab and the data set we're looking at here is Major League Baseball data by year by team. Got a number of metrics here. Home runs, run scored run differential, which is the difference between runs allowed and run scored, wins, losses and team salary. And we got data going all the way down from 2000 to 2015. So this will give us a really nice sample data set to build some scatter plots Now. The first thing that one might hypothesize is that the number of runs scored may be highly correlated with the number of wins that a team scores in a given season. So let's go ahead and try to plot that with a scatter plot. It can select all of column G control. Click all of Colin K. RS is my runs scored column W's My Winds column. I could go into insert my scatter plots air right down here, so insert just a regular scatter plot and drag it over right next door data set. So it's a little bit messy right now. We don't have access labels. The title just says W. So we'll need to change some things here. First off for the title. What we're looking at here, all of these individual points are individual teams. So I'm gonna title this MLB teams 2000 through 2015. Next up, I want to add some access labels just to make it clear. So primary Vertical. This is my winds axis, and then I'm gonna add a 2nd 1 for my primary horizontal Mrs Runs scored. Now, obviously, it's tough to get in the inside of this because it's just kind of a little cloud of data. That's because both of our axes are extending all the way to a minimum of zero so we can do here's format. The Axis. We know that there's no team that scored fewer than 450 runs in a given year, so it can use that as a minimum. That really just helps expand the picture and see things a bit more clearly and similar. Case with winds weaken. Set a minimum 2 40 And now we get kind of a better picture of what this cloud looks like and what the relationship might look like. So if I right click the data, Siri's I can add a trend line here. It's kind of tough to read someone. Format this. Ah, with Phil option, I'm gonna make it a solid line. This orange is fine. All this make the width three point so it jumps out a little bit. Can also format my data. Siri's all these individual points, and the options look kind of minimal here. If you try to mess with line options. Obviously, that's bad news. These are really just points are markers, so you need to go into your marker options, and I don't want any borders on these. I want to fill them with just a lighter shade of blue said they're not as, ah, not as distracting. Compared to the trend line, it's now with the trend line. I'll just go ahead and display that r squared value. I'll make that bold so it jumps out a little bit and moving into some free space. There you go. What this is telling me is that the correlation between runs scored and winds is not very strong. It's only in R squared value of 0.26 So what if we test something else? If you know baseball, you might be thinking, Well, some teams, they're going to score a lot of runs. But if they also allow a lot of runs than not gonna win a lot of games, so winds are more likely a function of both runs scored and runs allowed. So why don't we try building a scatter on run differential instead of just runs scored? So same process here. I'm going to select I and select K with control. I'm gonna insert another scattered here, and we can already see that there's, um, tighter relationship here. Between these two variables. It's a little wonky because that run differential field obviously has positive and negative values. So the best way to combat that it's just a right click format. The axis and this area that says vertical axis crosses right now it's automatic set to cross it. Zero. I can manually say Nope. Let's have that cross at negative 400 and it just moves it right on over. So this looks great again. I'm gonna change the title here to the same thing. MLB teams 2000 through 2015. Perfect. Just for the sake of being consistent when we had access titles again, Vertical is again wins and horizontal this time isn't run, scored its run differential. So it's right. Click the Siri's that's at a trendline. And let's do just what we did before. Make it a solid orange line. Three point with and all also update my data. Siri's. So I want to change the marker options. No border solid Phil. So there you go. That's basically the same scatter plot. The only difference is that I'm showing runs scored against winds on the top one and run differential against winds on the bottom. So when we go ahead and add that R squared value here and as you can see, R squared is 0.875 which is a much, much tighter fit So there you go. That's two examples of building scatter plots to show the relationships between two Siris of data. 16. Bubble Charts: All right, Next up, we've got bubble charts. Bubble charts are really, really similar to scatter plots. The only difference is that they add 1/3 dimension, which is the size of the bubble to the original scatter plot format. So where is a scatter plot Plots X and Y values. The bubble chart essentially plots X, Y and Z, where Z is the size of the points of the markers. So some examples here, Maybe you're plotting product sales on your X axis revenue on your Y axis. You could use something like market share as the size of the bubbles, where each bubble represents a company. Or you could look at income per capita on your ex life expectancy on your why and population as your size by country, so pro tips. In terms of bubble charts, you do have the option to use custom color formatting as 1/4 dimension to even further differentiate categories. Just keep in mind that if it doesn't add any value if it's not really necessary, it may just make the picture a bit more complicated than it needs to be. A second pro tip can use cell formulas and form controls to create dynamic animated bubble charts. That's a really interesting way to add the element of time to this is, well, so it's bounced back to excel and actually build one of these things. All right, so we'll head on back to our scatter plot and bubble chart tab since will use the same data set here to build a new bubble chart. And one thing to note is that since bubble charts are designed to really showcase and feature the relative size of each bubble, if you have a chart with hundreds or thousands of data points, those relative sizes air going to get very much drown out. So what I'm gonna do is build a new bubble chart from scratch, and Onley show the teams from the year 2000 that will limit my number of observations to about 30 which is a much more reasonable number of data points to showcase. So let's go ahead start from scratch and we'll insert a bubble chart. You could do three D or two D In this case, I'll just start with two D, and I'm gonna put it over here where I have some empty space, make it nice and large. So if I right click and select the data and add a Siri's here, you can see my options. Give it a Siri's name. X and Y values just like a scatter plot. And then my third dimension, the Siri's bubble size. So a serious name. Let's call it MLB Teams 2000. My ex values go into our selector. Here will be the same two variables that I used in my second scatter plot down here. So it will be run differential, which in this case will be I to through I 31 which is the last at row in the year 2000. And then my Y values will be wins over that same time period que two through K 31 and then , finally, the Siri's bubble size. In this case, I want the relative size of each bubble to reflect the salary of each team. So that's in column M. So my bubble size will be M two through M 31. I hit. Okay, let's see what we've got so far, The bubbles are a little large, but we can adjust that in just a moment. First, let's take care of these axes. So same two issues we had before where we're crossing at zero and we need to set some access minimums. So start with my ex. And why don't we set a minimum here to negative 200 in this case and we'll have the vertical axis cross at that minimum of negative 200 and then for winds on my Y axis, go ahead and put a minimum of 40 winds right there. So it's starting to come together. As usual, We're gonna go into our chart elements. Add some titles. Primary vertical is winds gonna add a primary horizontal as well for run differential. Now, if I right click the actual bubbles themselves, I conform at the data. Siri's and I have some special options here so I can scale the bubble size or it can change the size. Representation from area to width actually changed, too, with it creates a little bit more differentiation between the larger and smaller bubbles. So stick with that option. I also want to scale these down, so let's make it 50% scaled. Um, that will just separate them a little bit and allow me to see some more differences between teams. And last but not least, I want to add some labels because I don't really know which bubble is which, unless I hover over it. So I get a little bit fancy here and add data labels. And then when I go into format, those I don't really want Siri's name. That's just MLB Teams 2000. I don't really want the X or Y values. Bubble sizes, the salary, none of these air really doing it for me. So what I'm gonna chooses value from cells. And then I'm going to select this range with my team I DS, which is basically just a shorthand version of the team name. So same array, essentially in column cease of C two through C 31. Now each of these team ideas associate ID with the data in the bubble chart. So now its added the team names. I can get rid of the Y values and the leader lines. I could position those right in the middle of the bubbles if I choose. Um, so there you go. Now I see which teams have the highest salaries by which ones were the largest that could see some interesting trends like San Francisco performed really, really well, the most wins out of any team. And it was about middle of the pack as far a salaries concern. So why don't we go ahead and just add a trend line here? Oh, format it kind of same way, keep it linear, making orange trendline. And just like before, we can go ahead and format that trendline and display the R squared value right here. So R squared 0.88 It's a really, really strong fit, especially just looking at the year 2000. So there you go. That's a bubble chart. It adds a new element and a new dimension of insight to a traditional scatter plot. 17. Box & Whisker Charts: all right, next on the list box and whiskers. So this mysterious, only named chart types really good at visualizing statistical characteristics across data Siri's so it may not look like it but actually plays a similar role in a similar function as a HIST a Graham does. In other words, it tells you a lot of characteristics about the statistical attributes of your data set. So we'll tell you things like your max, your men, your media and you're mean. You're court tiles, any outliers, all packed within one single compact visualization. So it's a really cool tool on a really cool statistical visual analysis. So one caveat. Here, this actually just got introduced in Excel 2016. So for your using an older version of the program probably won't be able to follow along. I apologize, but on that note I'd recommend that you check out Office 3 65 I promise. I'm not a paid spokesperson. I just believe that it's a great value. Essentially, it's a subscription based service that gives you access to all of the latest current office products. So Excel, Power Point word, all that good stuff. I think it's only 10 or 15 bucks a month, so check it out. But I digress. Let's get back to box and whiskers. So some examples of one you might use a box and whisker chart things like comparing rainfall amounts across cities, analyzing distributions of values and trying to visually identify outliers. Or, you know, things that a hist a gram would be really good at, like comparing mean and median heights and weights by country. So just one pro tip here by default, your core tiles are calculated by excluding the median. That calculation can be adjusted to include the median, but keep in mind that it might significantly change. The result, especially feel looking at a small data sample. So with that, one of the journey over to Excel and take a look. Okay, so in the Box and Whisker Tab, you'll see three columns of data sticking with the baseball theme. Here. We've got players in the B column. Home run counts in column See the data is organized by season. So to insert the box and whisker charts very, very simple. In this case, I'll select column, a shift click to grab column See, that gives me all of my data pop into insert and box and Whisker lives in the Statistic charts section, which is also where hissed a Grams live. So go and pop that in. It's dragging on out so we can see it a little bit better. And there you have it. So now all we really need to do is add a nice descriptive title. Let's call it top home run hitters and going to chart tools. And when we add primary vertical for home runs, now our audience knows exactly what they're looking at here. So inserting the chart was the easy part. Now we gotta understand how to interpret this darn thing. So as you can see for each my Siri's or each of my players, I've got a box like a rectangle and these vertical lines extending from the top and bottom . As you might have guessed, those are the whiskers of said box and whisker chart. So what the whiskers do is they show you the maximum and minimum values. So the distance between the top of the top whisker and the bottom of the bottom represents the spread from Max to men and then within the box here, you'll see two attributes. There's a horizontal line that shows you that medium and a little X that shows you the mean . So what you can do now is say, OK, the bottom of my bottom whisker to the bottom of the box represents my first quartile for this data range, distance from the bottom of the box to the horizontal line or median represents my second quarter mile top piece of the boxes. The third and from the top of the box to the top of the top whisker is finally your fourth quartile. So when I'm looking at this, there's one thing that really catches my eye, and it's this really long top whisker over here on this player named Kristen. So what this tells you is that there's a huge, huge gap between his Max value and his mean and his median. So if we actually look at the raw data, we can see what might be going on. Here's Chris, some clown. On 2011 he hit 14 home runs 2012 12 2013. He had 10. Then, out of nowhere, he hit 75 2014 followed by eight the next year. So when you visually represent that with the box and whisker chart, it becomes very clear, very evident that that one home run season was a bit of an out liar here. So speaking of outliers, if we right click and format the data Siri's we've got a few formatting options that are specific to box and whisker charts. We can show inner outer points we can show or hide out liars. In this case, even though this value for Chris Darden is very high, technically, it's not an outlier based on how they're statistically defined. In this case. You can also turn on or off the mean markers, and you can add a line connecting the means if you choose to do so. Last but not least, we've got this quartile calculation that I mentioned earlier. So in this case, if I were to calculate my court trials with the median included, the whole picture changes. And in fact, now a lot of these values technically do become outliers described by just a dot where you no longer see that top whisker. So just a word of warning. If you do change these quartile calculations, settings keep in mind that they might totally change the story, especially if you have a very, very small data set. So there you have it box and whisker charts. Really good tool to help describe statistical attributes of a data set. 18. Tree Maps & Sunburst Charts: so in the past, if you wanted to visualize hierarchical data, Excel was not a very good way to do that. But that's all changed in 2016 with the introduction of tree maps and sunburst charts. So these types of charts are a really great way to visualize data that has natural groups or sub grouping. So some examples might be if you want to look at relative revenue by book title, organized by genre and sub genre, or look at the number of employees by department, by office or population by city, state and region. All of these example data sets have natural groupings and sub groupings. A couple pro tips here, the first used tree maps when you're only visualizing one or two hierarchical levels. So, for instance, topic and sub topic or when relative sizes are really important to your story. Sunburst charts, in the other hand, are a great way to show the depth of different hierarchical levels with meaner data. Second, and this one's really, really important. Make sure your raw source data is already grouped and sorted before trying to create a tree map in a sunburst chart, and I'll show you an example of why that's so important. So let's take a look. All right, so here we are in the Tree Maps and Sunburst chart tab. Our date of his exercises, Workbook and here have got to sample data sets, and we use one to create a tree map and the other to create a sunburst chart. So starting with the top data set, this is ticket sales by title by rating by genre. So it's a good example of data that has natural groupings and sub groupings at a tree map would be perfect for, So I'll just select a one through D 14 and in the insert menu there's a new section and Excel 2016 that specifically includes tree maps and sunbursts, in this case will choose tree map. And there you go. So it looked at the source data that I had selected, and it said, Okay, see that genre is grouped and sorted and that their titles within genres and ticket sale value and it's created this tree map for me, and I could just go ahead and delete that title. Now, what this tree map is showing me is at a high level. The colored sections are giving me a sense of which genres in aggregate drive the most ticket sales. So in this case, the gray boxes make up the biggest proportion of the entire area of my chart. So I know that horror movies drove the largest overall chunk of ticket sales, followed that comedy, then drama, then SciFi. So within each of those colors that could hone in. And in fact, Excel 2016 has some cool interactivity built into some of these newer charts, so you can actually click through and drill into each section. So let's say I want to drill into comedies now within comedy. The relative size of the boxes within this entire section represent the proportion of ticket sales among comedies. So I know that trapeze Cat was three number one comedy in this particular timeframe and followed by too many balloons, G I, Jose and Adventure squirrels so you could do the same thing. Just drilling into any of these categories. Now you remember the pro tip where I said you have to make sure your datas group and sorted show an example of why that's the case. Let's say what's wrong with Grandma was no longer horror movie. Let's say it was a comedy If I changed that label to comedy. Now the tree map gets extremely confused, and it adds a second category for comedy. So it's showing comedy as blues and this one separate field just to account for that one line that's not grouped with the other comedies. So as you can see, that really kind of wreaks havoc on the tree maps. So just undo that step and another example. If I just selected the data and Columns B Through D and try to insert a tree map, the same thing happens. It's showing the duplicate values by ratings. It's just really not doing what I expected it to do. So there's a little bit of upfront work that you need to do to make sure that your hierarchical charts are formatted in the way that you expect them to be. Now let's scroll down and take a look at our second data set, which is a classic orig chart. We've got offices in Boston, New York and Chicago within offices. We have marketing sales, or I T departments. Under each department, we've got a supervisor, followed by a manager and an employee. So we're drilling down five levels deep here, which is deeper than a tree map would be able to show us in any sort of meaningful way. So this means it's a great candidate for a sunburst chart, and the last thing you'll notice is this Count Field. This is just because Excel needs some sort of a numerical value to know it to chart so I can go ahead and select a 17 through F 27 and insert sunburst chart. Go ahead and delete the title here. Eventually, there we go, and I'm just going to scroll down and make this nice and large because it's really a very, very cool looking chart. And what's even cooler is that has that same interactive effect that the tree map did, where you can start on the inner ring of the sunburst, which is your highest level grouping in this case, the office, and say, OK, show me all of the data points associated with the New York office or the Chicago office or the Boston office, and in this case, because we set the count values alta one, it means that you're not giving more credit or more value to any particular employee or manager. It just basically distributes things equally, depending on how many segments there are in each ring. So you can drill even deeper and say, Okay, with in New York, just show me people working in the sales department within sales Onley Show me people who Allen is supervising and then on Allen's team. Show me Tina's group. So there you go. You can kind of drill in and quickly get a sense visually, just based on the distribution or even the shape of this sunburst kind of some of the characteristics of our data. So tree maps, sunbursts, two brand new charts and excel that are great ways to visualize hierarchies in your data. 19. Waterfall Charts: another really cool, really useful chart that was just introduced in Excel 2016 is the waterfall chart. Now you used to be able to create similar looking waterfall or step charts using some hack kind of methods with older versions of Excel. These new 2016 waterfall charts make it so much easier, so waterfall charts are commonly used for showing the net value after a series of positive or negative contributions. So they're often used for things like corporate balance sheet analyses or personal income and spending. So great way to visualize progression from a starting point to a 19 point pro tip. Here you can use sub totals to create checkpoints midway through your waterfall. That helps to split up certain types of gains or losses. For instance, with a corporate balance sheet analysis, you might have gross revenue than subtract cost of goods sold, which takes you to gross profit and then gross profit minus operating expenses equals operating income, which is a second sub total. So Excel makes it very easy to account for those types of sub totals within a waterfall chart. So let's take a look. All right, so here in the waterfall and funnel chart Tab. I've got some data here that represents some sort of a corporate financial analysis. As you can see, I've got gross revenue here and Selby one. And then I've got a Siris of additions and subtractions. Got some sub totals like gross profit operating income, and it brings me all the way down to Row 13 which is net income at 26,200. So this is a perfect candidate for a waterfall chart so I can go ahead and select that entire array going to insert. This is an entirely new section and Excel 2016. I can just go ahead and select waterfall here, and why don't we expand it out? This case we don't really need title, and as you can see, the legend is showing us that the blue bars or the blue columns represent increases. The oranges represent decreases, and there is a grey legend for total. But nothing in my chart is defined as a total. So that's a problem, especially because what it's doing is it's counting these sub totals that 78,000 and the 32,000 and 26,200 as additional gains from my starting point. So all I need to do is select each of the bars that actually represent a total or sub total and just select set as total. So I'll do the same thing here with my gross profit. Same thing here with my operating income and last but not least, with my net income. You can also do that by right clicking formatting the data Siri's and you can set the total there as well. So now as you can see, we start with 100,000 lose 30,000 gained 8000 which takes us to 78 then we lose 10. Lose 4.5, 24 68 which takes us to 32 and you see that kind of natural waterfall, those natural steps as you progress from your starting point to your end total. So there you go. Super simple use waterfall charts to show the net value after a series of positive and negative contributions 20. Funnel Charts: all right. Next up, we've got one of the simpler charts in Excel but also one of the new ones introduced in 2016. The Funnel Chart. Now the funnel chart serves a very simple purpose. All it does is really show progress through stages of a funnel. In other words, it shows progression towards some sort of goal examples. Maybe you want to see the volume of views, clicks and sales on N e commerce site, where each of those metrics gets further and further down the funnel. Or maybe you want to look at the number of runners who reach certain checkpoints in a marathon, and that's the example will walk there in just a minute. Two Quick pro tips. Here you can use percent of total calculations to show percentages instead of raw numbers. Sometimes that just tells a more clear story. And second, you always have the option to customize your chart colors, and that can help to emphasize progression towards some sort of an end goal. So that's happen to excel and take a look. So back on the waterfall in funnel chart tab here have got a very simple little data set showing marathon progress. And I've got six different checkpoints, starting with the start line, moving on to the five K 10-K half marathon mild 25 finish. And with each of those checkpoints at the associated number of runners who made it to that stage. So if I just select the values and columns and be I can insert a funnel chart, it's right beneath the waterfall option. And, as you can see, very straightforward. It just dropped it in. I can change the chart title to marathon progress. Choose now. Unfortunately, you can't right click and format those data labels to just convert those 2% is the same way that you would in a pie chart, for instance. But what you can do is create a new column to quantify the percent of runners rather than the wrong number. So if I just select Onley the values in A and C and once again insert a funnel chart now I'm showing progress in terms of the percent of runners, which, you know, in my opinion, is just a little bit more clear, a little bit more intuitive. So title this the same marathon progress percent and we conform at these data labels, however we choose. So in this case, I'll select them all. And let's make them bold and white and just a little bit bigger to make them top. What's great about funnel charts is that it really shows you where in the funnel, which stages in the funnel lead to the biggest drop offs in volume. And that's especially valuable for something like N e commerce Company, where you have users or online shoppers progressing through different stages in the funnel where they land on your page, they view a product page. They add a product to their cart, they proceed to the check out, and then they make the purchase. You know, if you're an analyst measuring the purchase cycle or the purchase funnel for your E com client, and you see that there's a massive drop off between the view product and the check out page , that will really help you hone in on some optimization to perhaps optimize or improve that sales funnel. And in this case, you know, looking at marathon runners, we see that it's very clear the biggest drop off occurs between the half marathon and the mile 25 mark. But once runners make it there, just about all of them get from mile 25 to the finish. So there you go. Pretty simple, but pretty cool. Chart the funnel chart in Excel. 21. Radar Charts: So one of the lesser known but really cool charts in Excel is the radar chart. Radar chart is used to plot three or more quantitative variables on a two dimensional chart relative to Central Point, so it's almost like a line chart that wraps around in a circle and connects to itself. Another way to think about it might be like a bicycle wheel, with the number of spokes associated with different dimensions or characteristics. Now each data point would fall somewhere along those spokes, where the further out from the center of the wheel indicates more skew nous towards that particular characteristic or dimension. Trust me, it'll be easier to explain once we actually have our hands on some data. Some example. Data sets where you might use radar charts, something like comparing test scores across multiple subjects, looking at sales of different types of vegetables by month, visualizing personality test results across subjects. The idea is to get a quick visual representation of which directions the data skews. So pro tips here. First and foremost, it can be really helpful to normalize each metric onto the same scale, like a 011 to 10 1 to 100 could be anything but Normalizing helps to improve readability and create more intuitive, more noticeable comparisons across your data. Siri's that way. You don't have one data. Siri's completely drowning out the others. Second, just like pies, clustered columns, etcetera, limit the number of categories that you're looking at. The idea is to minimise noise, maximize impact, so stick with only a handful of categories. With that, let's take a look at some examples. All right, so on the radar chart tab now we've got a few data sets that will help to demonstrate how radar charts can be used for. Anyone who studied psychology will probably recognize the's Big Five personality traits. Openness, conscientiousness, neuroticism, extroversion and agreeableness included some definitions there for anyone who is curious, and what's nice about this data set is got pre normalized nice data set toe work with with three different test respondents Tim, Katie and George. So what I can do is select a one through F four insert going to my radar and surface area, going and pop in a regular radar chart. You can see I can choose an option with markers or a solid Phil. I don't really know the point of the solid fill. It just kind of overlaps your data just like an overlapped area chart and makes things tough to read. So I prefer the standard. Let's do a little bit of formatting on this. Get rid of the title. I'm going to right click, format my legend and just move it to the bottom. And since this is all scaled on 0 100 I really don't need the axis itself. So delete that. And last, but not least, just going to go into Phil options with the grid lines selected and make these little bit more noticeable with a solid fill in a little bit of transparency. And there you have it. So one kind of sidebar here. You see how my chart is overlapping this data? Let's say I want to insert some rose and shift This stated down If I try to do that since the chart overlaps that road that charts gonna grow as I make those changes, I have the option that changed that by going to the format options go all the way to the right to size and properties, drill down the properties options and see how move and size with cells is selected. That's the default option. I can go ahead and select, don't move or size with cells. And now, as I add those rose and press F four to repeat that process, the charts stays put and doesn't adjust accordingly. So that's actually really helpful. If you have charts that are part of dashboards that live on worksheets, and you need to just column with the row heights and you don't want to move those charts, you have the sizes and proportions locked in. That's a great little option to keep in mind. So anyway, back to the radar chart. Now let's talk about how to actually read this thing. So in this case, we have five different dimensions or characteristics that kind of form this Pentagon shape . So the best way to think about it is that a respondent who is extremely balanced to score similarly across all five dimensions will basically create a shape that looks very much like a Pentagon Eso Tim is a very balanced individual. In this case, he tends to score similarly across all of these different personality traits. On the other hand, you've got Katie here who skews very much the left. She scored well, higher than either Timur George in terms of extroversion, agreeableness and openness. And then you've got a guy like George who kind of out indexed everyone on the conscientiousness and neuroticism side. So radar chart, in this case, is a really nice way to kind of get a sense for these people's personalities simply by the shape that's created by this chart. So really cool effect. Really cool visual tool. So let's scroll down and take a look at our second data set. We've got sales by month for four different crops who got carrots, potatoes, apples, watermelons. With 12 months of data and we've got two versions of this, I'm gonna show you the 1st 1 and then talk about what this second version means. So this is just volume of sales. If I select that Siri's and insert a radar, I'll just drag it down here. Let's get rid of the title. As you can see, it's a little bit tough to read because potatoes here there's so much higher volume in terms of sales that the tender just drown everything else out. So it's really tough to tell what's going on down here with watermelons and carrots because then volumes air just so small. So what I've done here is essentially convert this volume data set into a 0 100 scale to data set. And to do that, I used this very fancy, somewhat complicated formula that I just applied to this entire range. If you're interested in learning more about how this formula works, go ahead and shoot me a message. But essentially it allows you to convert any range of values into any new scale that you choose. So in this case, my new minimum value is one, and my new maximum value is 99 plus one or 100. So if I want anyone to 1000 scale, I could change this to 9 99 and apply this over. And there you go. It's the same relative values just scaled to a new Macs and men so under that, and just make it 99 again. And now if I select this entire array from a 27 through M 31 and insert another radar chart now if I just drag this next to original chart to lead the title as you can see we see a much, much clearer picture of relative trends between these four crops. Watermelons at tend to peak in summer months. June, July, August. You've got apples coming in in September and October during the fall picking season. You've got carrots, which sell well in November, December again in February and then potatoes, which really peak in January. Now, these aren't actual numbers. These air Just my best guess. So, all your farmers out there, please don't call me out on this. That gives you a good sense of the importance of when and why to use a re scaled or normalized data range, especially when you're looking at something like a radar chart. There you go. Right. Are charts in Excel really cool tool? 22. Stock Charts: stock charts air type of chart that serves a very specific purpose in this case, visualizing stock market data that could include volume high, low, open and closing prices. So pretty straightforward examples here you could look at something like Facebook's daily stock performance in 2015 or high low and closing prices for Google in Q one. Basically, anything related to a stock's performance can be visualized with a stock chart. So to pro tips here, this is a good opportunity to manually set your access minimum and sometimes maximum values to enhance the readability. It will default to minimums of zero, which sometimes creates a chart that's a bit tough to read. Second Pro tip, which is one that's really helpful not just for stock charts is that you can actually switch your X axis type from date to text to eliminate gaps when markets are closed. So by default you'll see blank spaces on Saturdays and Sundays because there's no market activity. By changing from a date to a text access, we can eliminate those. So let's head on over to our Excel workbook, and I'll show you exactly what I mean. Okay, so here in the stock charts Tab. As you can see, I've got daily trading volume for Apple stock. Got trading volume got open, high, low and close prices. So those are the five elements that make up a stock chart. And if I go into the insert menu here, actually live with waterfalls and funnels right here, and therefore different types that get increasingly more complicated starts with just high , low close those the only three inputs. Then you've got open, high, low, close volume Hailo clothes and finally, volume open, high, low, close. Now it's important to keep in mind with stock charts is that they're a bit finicky, and you have to feed it exactly the data that it's looking for in order to spit out the chart that you need. So in this case, I have all five elements in the exact order that Excel expects them to be. Volume open, high, low, close So I can select column B all the way through column G going to my insert menu. And as you can see, if I hover over these 1st 3 types, nothing populates, even though the elements that I need are included within the five that I've selected. It won't populated chart until I choose the correct one, which in this case is volume open, high, low, close. So let's go ahead and insert that shirt and expand it so that it's a bit more readable. And as you can see, we're gonna need to do a little bit of formatting on this to make it more user friendly, starting with the chart title. So it's a Apple stock training. Make it nice and bold. We need some access title, so going to my chart elements, we'll make a primary vertical. This is my volume access. Make that bold as well, and then I'm also going to need a secondary vertical axis. This is my price axis and, as you can see, because I entered a specific stock chart, Excel knows to put the price related information on that secondary axis. So now, as you can see my primary, why makes sense? The scale kind of fits the columns pretty well, but my secondary y axis, my price axis I really could use a minimum to make this more readable. So why don't we set the minimum to 80? And when I do that, you can see that spreads out nicely so that it's much more readable now. It can actually see the movement of the price over this time range. Next up, let's go ahead and edit our X axis Summit right Click and format. One of the nice kind of hidden gems here, all the way at the bottom is this number option. This allows me to change exactly how I'm showing these X axis labels. In this case, I'm gonna show them as a date. But instead of day, month, year, I just want the day in the month already know that I'm looking at 2016 data, so I don't need the year there. The shrink this down a little bit and now the pro tip that I mentioned. You see how there's these two gaps for Saturdays and Sundays when the market's closed to account for that conform at the axis and instead of automatically selecting based on data, I can change that to a text axis. That way, Excel doesn't know that I'm particularly looking at dates. It just sees an X Axis label called 41444546 etcetera and plots them sequentially. Last but not least we've got this legend here. I don't know if this is an Excel bug or what, but the legend really doesn't help us at all. So I'm gonna go ahead and delete that. And finally, we've got something that's nice and readable, and we can talk about what this actually means. So volume represented by columns that shows the trading volume on any given day. On top of that, you've got this almost like a box and whisker style visualization happening, and this shows you a number of different data points, just like a box and whisker. Those vertical lines extend from the minimum and maximum prices for that given day. What the box shows you is the difference between the open and close price. So if the boxes white or unfilled, that means that the stock started low and closed up. If the boxes black or filled and means it started at the top and closed down. So that's your basic primer on interpreting or understanding exactly what the's chart types are telling you now again, if I wanted to change this chart type, let's say I wanted to use something a bit more simple. Excel won't let me just simply change over, even though again, I have the components that I need. So it says arranged the data in this order. It's very, very specific, much more specific than other types of charts in Excel, so I can hit OK, and really, the approach here is to choose what kind of chart you want to show. For instance, if I want an open, high, low, close chart out, select the date and then open high, low, close, I'm control clicking to grab those extra columns. And now, if I insert, you can see once I get to that particular chart type that's associated with the data that I have selected Onley, then will it populated chart. So similar case. If I wanted a volume high, low, close chart, I would choose date volume high, low, close and then I can go ahead and insert that type of chart as well. So as you can see, stock charts can be a little bit tricky to work with, but I'd recommend just pulling in all of the metrics you need. Grab the volume data, grab the open, high, low and close prices. That way you can start with the most complex chart and pared down as need be so there you go. Stock charts in Excel 23. Heat Maps: Okay, next up, I want to talk about a type of data visualisation that's a little bit different. In this case, we're talking about heat maps that are built using self formatting as opposed to traditional chart types. So heat maps, in this sense are commonly used for visualizing trends or relationships simply using color scales. So some good examples might be accident rates by time of day or day of week, which is what we're looking at here on the bottom screenshot, or average temperature by city by month, which we're looking at here on the top, screenshot or anything, really like average sentiment by hashtag. Anything that has values that could be captured with some sort of a color scale could potentially be visualized with a heat map. So a couple pro tips here the first use intuitive color scales. So things like red to green when you're talking about bad or good outcomes or red to blue for talking about hot and cold. And one little tip here that I actually just recently learned is a really nice custom formatting tip toe hide the cell values. So that way, the formatting will still be based on the value, but you won't show the actual number in the cell. And that's accomplished with a custom formatting rule defined by three semi Coghlan's. I'll show you that in just a second, the second pro tip. You can use data validations or drop down menus on cell formulas to create dynamic heat maps based on user entered values. Basically, what I mean is that you don't have to hard code the numbers within heat map. You can use statistical functions like some ifs or countess, to make that data range dynamic so that as a user changes particular inputs, the heat map adjusts accordingly so you can create these beautiful living, breathing heat maps all within excel. So let's open up the Excel workbook and take a look. So on the heat maps tab, you'll see average temperature data for 10 different cities from January through December. Now this is a great candidate for a heat map because it will allow us to very quickly, very powerfully visualized trends using Onley colors so really simple. In terms of implementation. All I need to do is select that data array from C four through end 13 and instead of going into my insert menu and looking for chart. I'm actually gonna take a different route. I'm going to go into the home menu and drop into my conditional formatting options. Now, here I've got this built in color scale setting where I can choose a number of stock color scales. So, for instance, green, yellow, red or red, Yellow, green. This one's pretty good. It shows thehyperfix values and read the lower values in green. But there's actually better one here that serves my purpose, which is this one here. So I like the blue to red because it really gives that hot, cold kind of vibe about any customization necessary on my end. So I'm going to use this is my starting point and already can start to see how quickly these visual stories and these trends start to emerge simply by introducing the element of color. Now, the second pro tip here I select that data range right click formats cells. Here's that little custom formatting rule that I alluded to earlier where if I go into my custom option all the way at the bottom of this category list and under type and delete zero and just type three consecutive semi colons. That's a little special custom formatting rule that actually makes the numbers invisible, so they're still there, and the conditional formatting is still based on the values. But the user doesn't see them, so that's a great way if the user doesn't need to actually see numerically what the temperature waas. You just want to visualize the trends. That's a great custom formatting rule to use now, just to point out some options here. If I select this array again and let's say I want to choose a slightly different custom color palette for this color scale, it's very easy to do. It's just going to manage rules. Select the rule. Click at it. Now, let's say for the low value I want this different shade of blue. I could just hit OK and apply and OK and there you have it so totally custom and a really, really powerful yet simple way to tell a story with some color scales and conditional formatting 24. Surface & Contour Charts: surface and contour charts are undoubtedly one of the coolest looking charts in Excel. Whether or not they're the most useful, that's up to you. So what these air used for his plotting data in three dimensions to find optimum combinations of values. So whereas most charts like Scatter Plot, for instance, plot data along an X and A Y axis surface and Contour charts plot data along an X, Y and Z to give an indication of depth as well. So some examples here you could look at accident rates or accident frequencies by both our of day and day of week to see when those hot spots occur. You could look at elevation by latitude and longitude, which is how these contour charts got their names. Or maybe you just want to look at optimum oven temperature and baking time to cook the perfect cookie. In that case, you could plot cookie deliciousness by temperature and baking time, although I'm sure there are better ways to do that. So pro tips here first and foremost, don't use surface charts. If a simple heat map or simpler chart will tell the same story, there's a time and a place to use a contour chart, and there are many more times and many more places where not to use a contour chart, so use it with some discretion. Second, avoid using wire frame chart types one possible they could be really difficult to interpret , so I'll show you what I mean. Let's had to excel and take a look already. So here we've got an array or matrix of car accident frequencies in Cambridge, Massachusetts, between the years 2010 and 2013 plotted by day of week, shown along this Y axis as well as our of day shown along the X. So this is a good candidate to use something like a surface contour chart because we have three dimensions of data to work with time of day, day of week and frequency. So I'll start by selecting all of this data, including the labels in column A in Row three gonna go into my insert menu, and these live along with radar charts. As you can see, we have a few options here. We've got a three D surface and the wire frame version of the three D surface, and then we have what's called a contour chart, which is essentially like taking this three D surface and looking at it from directly overhead. And then again, you've got the wire frame version of the Contour as well. In this case, let's go with the classic three D surface, and I'm gonna go ahead and blow this out to make it much more readable. Plus, it's just really cool looking, so let's see what we got. We'll give it a chart title accident frequency by time of day and day of week, and we're gonna need to give it some access titles because it's not very clear of this point. Excels pretty smart when you go into the chart elements and click access titles as you can see her at. Not only do I have horizontal and vertical, but I've got a depth axis as well. So for horizontal, that's my 0 23 scale. That represents our of day for my vertical. That's the actual volume of of accidents. So I'm gonna call this accident frequency and then finally we've got depth, which represents day of week. Okay, so this is starting to be pretty interesting. I can see some peaks and valleys, but it's a little bit tough to reads that we're going to need to do some formatting here. Now, one of the most useful formatting options when it comes to surface and contour charts is to right click format the chart area. I'm gonna go into my effects options here and drill down into three D rotation. Now, I can rotate this visual anyway that I choose. So as you can see, if I change my Y rotation and increase the degrees now I'm looking at it more overhead versus more horizontally from the side. And so I'm gonna go with something like this because it gives me a decent sense of depth as well as height and width. But you can customize this however you choose. Now, the second customization that I'm gonna make here is to add some custom colors to make this a little bit more clear, something to make this a little bit bigger. And now, to change the actual colors of these bands, it's really not intuitive. It's kind of tricky. You actually have to go down into the legend itself and right click on each particular band or range of values. And from here you conform at the band or change your fill and outline options right here. So for the 0 to 20 accident frequencies, that's a good thing, because that's the fewest number of accidents in my data sample. So I'm going to give it a dark green and then 20 to 40. Why don't we give it a lighter shade of green? Let's do this middle green here that 40 to 60 were starting to get into dangerous territory here. So let's go with, uh, with a yellow and then 60 to 80. I'm gonna fill this with on Orange than 80 to 100. Those air my really high accident frequencies. I'm going to give these plus 100 to 1 20 dark shades of red. So now, once I've done that, it tells an even stronger story by adding the element of color along with height, width and depth. So that's your basic surface chart. If I copy this and paste it here and let me just scroll over so we can see it now, what I can do is right. Click and change the chart type to either wire frame, which, as you can see, is really just tough to read. or go back in my chart tools and change it to a contour chart. And again, this is kind of the overhead view, so you can see to some extent where the hot spots are, but again, pretty tough to read. So what I would recommend is that you think about these charts and say, OK, what's the story that I'm really trying to tell in this case? I'm trying to tell the user which combinations of Day of week and hour of day tend to lead to the most accidents. And this surface chart does that to some extent, although to be honest, it's a little bit tough to read. So take a step back and say, Can I accomplish the same thing? Using a simple, conditional formatting rule like a color scale? You know, it can also apply that same pro tip that we did earlier with a custom formatting rule to hide the numbers with three semi colons. And there you have it. So ask yourself, Is this heat map all? I really need to tell that story? It's very, very clear that that 8 to 9 a.m. Rush hour, especially on Mondays through Fridays, tends to lead to the most accidents. Or do I need something fancier, like this surface chart? Both accomplish the same thing to different visual approaches, and there you have it Surface and contour charts in Excel. 25. Geo-Spatial Maps with Power Map: when it comes to geospatial data or building what's called a chloroplast map, Excel isn't always the first thing to come to mind. A lot of people instead shoes shiny or date of his tools, like tableau or other Web based options. But the thing is, with excels, new power map plug in geospatial analysis is actually really easy and really simple. So in this example, I'm going to give you a very, very quick demonstration of how the power map tool works just to start to scratch the surface of some of its capabilities. So when it comes to geospatial data, the ideas that you're trying to visualize, any sort of location based data that could be locations defined by ZIP codes, cities, states, countries, etcetera so examples frequency of accidents by street address, unemployment rate by country, average rainfall by state. All of these examples are location based and geo spatial in nature. So pro tips like I mentioned you can use excels power map plug in to create geospatial visualizations, as well as animate changes over time now excels. Power map tool was introduced in 2013 and it only applies to certain versions of Excel, so If you're not sure whether or not you have it, go ahead and check out supported out office dot com to see if you can enable the plug in second Pro tip. Utilize attributes like color and size to visualize multiple attributes at once. So let's do a quick demo of how this power map tool actually works in the power map tab. You'll see three columns of data got state level data with average household income figures as well as the state population. So what I can do is select cell a one and you use a shortcut here and hold control shift arrow right arrow down. Note that in this case, I can't select the entire columns A through C, I have to select the distinct array containing data for this to work. So once I have my selection made in the insert menu here I have my three D map option. This is my power map option, so I can go ahead and click that. And as you can see, it launches power map, and it automatically starts to populate the data for me based on what geospatial attributes it confined. So in this case, I don't need the tour window because I'm not gonna be creating an animation, and I don't need the field list just yet. What I will do is change to a flat map view, and I'll add map labels just to see exactly what I'm working with. So these options here allow me to move, tilt and zoom to customize my view. And all it's done so far is just dropped data points on each state. So here in my layers tab, you can see that it's dropped state as my location field, and it's automatically assigned it as a state or province. So why don't we go ahead and add a name to this layer when we call it household income? And now it's defaulted to showing the visualization as a stacked column. So all it needs is a height in order to visualize some data so I can select household income here, and as you can see, it creates those columns by state, where the height of the column represents the average household income. So this box, I could just go ahead and delete that to get it out of the way, and I have other options here is well I've clustered columns. I've got bubbles, heat maps and regional charts as well. So, for instance, by changed to Clustered column and I add a field for population, it will add a second column as well. Obviously in this case, a little bit tough to read. I'm gonna delete both of those options and let's switch over to the region view, which essentially populates the view by highlighting the shapes or areas that represent the regions in my data set in this case states. So the value that I want to show by region is average household income, and what I can do here, down at the bottom in my layer options is to just things like the color scale and the opacity. So what is the color that I'm working with? So do shades of red here and now, as you can see, hovering over that states like California and states like Minnesota have very high average household incomes. Minnesota 61,814 whereas on the opposite end of the Spectrum, E. Of Mississippi, with 36,919 West Virginia with 38,000 and so on and so forth. So in a matter of minutes. I've created this really nice, really simple geospatial map using excels power map tool and last but not least, I've got a few other options here that will quickly breeze through. You can add a two D chart and formatted as necessary. If you want to add an additional element to this, you can also capture, screen and use this image as an input in other places. And last but not least, like I mentioned earlier, you can create multiple scenes here and then linked them together in a video or an animation so excels. Power map Tool is actually really simple, really intuitive and really powerful. 26. Basic Combo Charts: one of excels, most powerful data visualization capabilities is the option to combine charts together to form custom combo charts. Now, obviously there hundreds, if not thousands, of ways to combine different charts. But in this case, I'm gonna show you a few examples just to give you a sense of how these things work. So on the combo charts tab, you'll see this data set here. Now I've got a combination of volume metrics like desktop spend, total spend clicks and transactions, as well as rate metrics like cost per click and transaction rate. Now the reason that's worth noting is because those two types of metrics volumes versus rates tend to fall on completely different scales. And as a result, trying to visualize them or plot them on the same charts or graphs means that you really need to combine different types and use things like secondary axes to tell a meaningful story. So I'm going to start by inserting basic two D column chart and let's drag it right down here, right click and select my data. It's gonna add a Siris for clicks, where my values are in E 23 13 and my horizontal axis labels are my months, and I'll just add a second Siris for cost per click, where my values aren't f 23 F 13 press. OK, and he's gonna fall in the same X axis. So when I hit OK, it looks like it's only plotting clicks. In fact, Excel is plotting both. It's just that cost per click is completely outweighed by my volumetric clicks so I can right click change the chart type down here the bottom. I have my combo option. And as you can see, it's already determined that cost per click probably makes sense as a line. All I need to do is select the secondary access option and boom. There you go. It added a secondary access for cost per click. Now I can go ahead into my chart elements. Add those titles so that the user knows what they're looking at, got clicks on my primary, and I've got CPC otherwise known as cost per click on my secondary vertical. Now I'm gonna do a little bit of formatting just to stylized it a bit. So format that line going my fill options. Let's make it a solid, dark blue line with a two point with. And we can get fancy here, too, at a preset shadow and want to do an outer shadow at 75% opacity and then back in the fill options. One of my favorite formats here is the smooth line option. There you go. Now let's do some quick formatting on my column data Siri's. It's making a solid Phil kind of a light blue bar and go into the Siri's options and just reduce that gap size a little bit. There you go again. This is purely as a demonstration you conform at and tweak and style eyes anyway, that you choose. And now So I've got a great visualization showing clicks and cost per click. But what if I want to create something similar for transactions and transaction rate instead of starting from scratch? Let's copy this chart, paste it right next to it. And now, with any serious selected Excel, will show me the source data that's feeding that piece of the chart, and all you need to do is grab the edge and drag it over. So instead of clicks, I want to show transactions here and selecting the line. I can say instead of cost per click. I want transaction rate now, two things to keep in mind. A. It will strip the four Matics. I've gotta reform at this and be it won't change the Axis labels. So I need to manually say this Primary vertical is transactions and the secondary vertical is now transaction rate, so we can go ahead and form at that data. Siri's Let's just make it quick like green and will make the line a solid dark green with a two point with and that same preset shadow effect just to be consistent. So there you go. I've got a basic kind of dashboard view already coming together where I'm showing a ton of information packed into two relatively simple combo charts. Now, the last example I want to show you is using a 100% stacked column. And for that, I'm going to use mobile and desktop spent by month. So select a one The receipt 13 insert, 100% stacked. Just gonna drag it up over here. I don't need the title in this case, so I'll delete that. And let's just format the data Siri's gonna make my desktop spend. It's green with a little bit of transparency, and I'll make my mobile spend and dark blue with some transparency as well. Now I want to add one more Siri's for transaction rate, so I'm gonna add the Siri's here. Name it H one, which is transaction right and pulling the values there and similar case Teoh cost per click. You know what? My axis labels the same. It's gonna look like that Syria's that I just added Transaction rate didn't actually get out into the chart. In fact, it did. It's just totally outweighed by the others. So you know the deal. I can either right click or go up into my chart tools and use the change chart type option . Pop down into combo. You'll note that when I do that, Excel changes what it's showing here. So it's trying to change it to Clustered column. I'm going to say, Oh, no, you don't. I want 100% stack. Call him still could change it right back, and I want my transaction percentage or transaction rate to be a line on the secondary axis . And there you go. It's created that second axis and it's overlay in my line now just for demonstration. Let's go ahead and form at that line. I'm gonna make it a nice dark blue two point with smooth line with a preset shadow. And then, since we haven't really played with marker options, let's test that out here. My marker options right here within the fill in line choices. So what I can do is drill down into marker options, building some markers here, change the shape to diamond I change the size, will keep it at size five and want to get fancy and say, White Phil with solid, dark blue border That's 1.5 points and again customize it, however, receive fit. These are all the options you have available to you. So now when I d select, let's make this one a little bit bigger so you can see it better. Got this really nice combo chart that tells you to really interesting stories at the same time, it tells you how the composition of your spend has changed month over month. So how much are you investing in mobile versus desktop? And as a result, how's your transaction rate changing so pretty clear Story is actually starting to emerge here, where it looks like in this particular case, we're shifting. Spend away from mobile starting in June, July and August and into desktop and at the same time. As a result, we're starting to see an improvement in transaction rate. So those are the types of stories and types of insights that you can generate by creatively using combo charts in Excel. 27. Sparklines: The last type of visualization that we're gonna cover is called a spark line, and the best way to think of a spark line is that it's basically a miniature version of a line charter column chart packed into a single cell. So in the Spark Lines tab here, I've got my average high temperature data for 10 different cities from January through December. And what I want to do is just insert a tiny little spark line and column. Oh, and the idea will be to give a very quick sense of how these temperatures trend over time, so I don't need to build out a full scale column or line chart to do that. I just want to give a quick, basic visual representation. So in the insert menu, right next to my classic chart options, I've got three Spark Line options Line column or win loss. Win loss is typically used when you have positive and negative values. In this case, I'm gonna choose either line or call him. So let's start with a line option and the data range that it wanted. Visualize is right here, C four through and for, and I want to visualize that data within cell. Oh, for which I have selected Press. OK, and there you have it. It's created that tiny little line charts spark line for me. Now, with the cell selected, I've got spark line tools that can change the type to call him if I choose. And I can choose which markers to show. Don't want to mark the high point, the low point, any negative points or the first end or last point in this case, why don't we mark the high point? We've got style options here as well. Kind of like this option. Let's stick with this one. Or you can manually choose Spark Line and marker colors here as well, as well as access options. So there you have. It gives a very, very quick sense of seasonal trend without a full scale chart. And what's nice is that I can take this and just drag it down. Kind of like it would've Formula and Excel will populate these with the correct relative source data references. So there you have it. Super simple spark lines. In a nutshell. 28. Conclusion: Well, guys, its official. You've completed Excel Analytics, Advanced Excel charts and graphs. So, first and foremost, congratulations and know how much it takes to actually sit down and learn and practice this stuff and expand your skill set. So huge props to you. The good news is that you did it your date of his rock star. So congrats again before I let you go. Just want to hit some quick resource is and next steps first, Some more shameless self promotion. If you're interested in diving into the formula and function side of things, check out my advanced formula in function course. I know we hit quite a few formulas and functions, especially during the demo section of this course that we really didn't spend as much time focusing on as we could have. So that course includes a ton of content about six hours, and it contains everything you could possibly want to learn about formulas or functions. So stats, logical operators, condition, ALS, text functions, array formulas, look up reference and so much more. A second, there are a few sites and blog's that I tend to go to for support. I figured I'd share those with you guys. First and foremost support dot office dot com is really helpful when it comes to the basic charts or formulas. Also check out Office 3 65 For anyone who doesn't have the latest version of Excel again, 3 65 is a great, affordable, subscription based service to make sure you always have the latest office products. And I swear I'm not a paid spokesperson. Promise a second stack overflow. Really, really excellent site for form supports. If you have specific questions or project based needs, Stack overflows a great spot. And then I found this Google site E 90 e 50 charts that has some insanely advanced stuff that really dives into things like VB, a coating and super advanced math and statistics. So if you really want to push yourself and you're into that kind of thing, go ahead and check that you are all right there. Finally, ratings and reviews are what keeps courses like this alive. So I encourage you to please share feedback if you have it for better, for worse. And I mean that the positive feedback, of course, is always very much appreciated. But the constructive feedback is great to any feedback that you guys can share to make sure I'm producing the best content that I possibly can goes a long way. So please don't hesitate to reach out with that. Thank you so much. I really, really appreciate you. Taking the time to stop by the course really means a lot to me. And I hope to see you guys in some of my other courses as well. So thanks again and good night.