Excel PRO TIPS Part 4: Data Visualization

1. Visualization Tips Intro: Hey there. Welcome to the visualization section of the course Here will cover a number of unique and powerful date of his techniques, from designing custom templates and chart types to building interactive tools using named ranges and form controls. Remember, this is a nonlinear course, so feel free to explore the content in any order that you choose enjoy. 2. Chart Properties (Move & Size): all right. Hey, everyone, for this next pro tip, this is a one star date of his tip and talk about preventing charts from moving or sizing. Now an excel by default, a chart or an object that you had to work sheet is allowed to move or size with the underlying cells. That means it's going to stretch your shift as those underlying rows or columns get adjusted or resized. In other words, it's like the chart is kind of attached to the underlying grid. Now you can customize this behavior using the format chart area options under the properties section. So in this case, we're gonna look at a chart like this. This is a Pareto chart type of hissed a gram. And if you right click, kind of right on the edge of that chart, you're gonna be able to access the format chart area options that look like this. And in the third little section there the size and property section, you'll see three different options for properties. The 1st 1 is your default. It's move and size with cells and again that allows those charts or those objects to move and stretch the second option moved, but don't size essentially allows the charts to move horizontally or vertically, but not actually stretch or distort, and then finally, don't move or size exactly like you'd expect. It just fixes that chart position in place, regardless of how any of the cells in that worksheet are adjusted. So I like to think about that option is kind of disconnecting the chart from the underlying grid Couple of common use cases here. For one, you might want to lock in your chart positions if you're building a user facing reports or dashboards to prevent them from getting distorted. Second, allowing charts to move but not stretch out when Rose air filtered or sorted. So let's hop into excel. I'll show you what this looks like, all right, So from our pro tip workbook, look for your yellow visualization tip demos here and specifically the Chart Properties Move and size demo. Go ahead and click that link button to jump out to the Yellow Tab, and what we have here is a bunch of data about wine. Got wind names, Colin Be Country of Origin column A and then average point rating and column C and the average price Income D. If I scroll down, I believe we have about 50. 600 observations arose in this sample, and what we're doing is visualizing that data these price values specifically using something called a Pareto chart. It's a variation of a history Graham that essentially shows us the frequency or the number of wines that fall into given price ranges or buckets. And basically, what the Peredo does is it sorts those buckets or bins descending by frequency and adds this cumulative total line as well. Now what I want to take a look at are the size and property options here, a right click kind of near the edge of the chart, navigate to my format chart area options and had to this third bucket here size and properties. And these are the three options that we're interested in. Number one move in size with cells. That's the one that's typically activated by default. And what that means is that if we make changes to underlying grid, we can see that that chart will move left or right or up or down, or stretch if we impact actual underlying cells. Just like so. So, in other words, this object. This chart is pretty much tied to the underlying grid. If we wanted to manipulate or filter, you know, our source data here like maybe look, it just winds from Austria and press. OK, That chart disappears because it was tied to the rose in which it lived. And now those rows are hidden so we can go ahead and clear that filter to get our chart back and watch this. Let's change some of these properties. Go back into those options. Let's try the second option move, but don't size now. Any change that's made outside of the range of the chart that will force it to move, we'll still have an impact like so. But changes toe underlying cells won't make that chart distort like they did before. And then one more time. Going back into those options. Our third choice here is the right bet. If we don't want this chart toe ever move, no matter what kind of work book change the user makes that way, it still won't distort right as we drag these underlying cells, and it also won't move at all. So it's essentially just disconnected from the underlying grid and in a case like this where we may want to filter the source data and Hydro's. Maybe we only want to look at French wines, for instance. Now we can compare different visualizations based on those different filter settings without worrying about our chart disappearing or distorting. So let's go ahead and unfiltered that and there you have it, using these size and property options to prevent charts from moving with self. 3. Hidden Chart Source Data: all right for this pro tip. It's a one star, very basic date of his tip, but it's a feature that a lot of people don't know exists, which is allowing Your Excel charts to reference hidden data in your worksheets. So by default, most types of Excel charts won't display source data if that data lives in hidden rows or columns. But we can get around that and force our charts to display that hidden data. To do that, we're going to right click on the chart, choose select data and then click the hidden and empty cells button in the lower left. And from there it's a simplest checking off the box that says show data and hidden rows and columns so that option is kind of tucked away in there. And that's why a lot of people, I think don't even realize it exists. Common use cases again, designing reports that have the source data and visuals on the same town, or preventing users from accessing or modifying a charts actual raw source data. So let's jump into the pro tip workbook and I'll show you exactly how to change this setting. All right, so go ahead and open up your pro tip workbook. Had your table of contents, were looking for our visualization tips section in yellow and take a look for that hidden source data demo again. That's a one star demo. Go ahead and Click link to jump straight to that yellow tab. And here we've got stock trending data for Amazon through 2017 with columns for the trading volume, open price, the high, the low and the clothes, which allows us to create a nice looking stock visual like this. But the only problem is that I don't really want all of this data here in front of the users. It's kind of distracting. I really just want to focus on the visual trend here with the chart catches that if I select columns a through gene holding shift there to grab all of those columns at once by right click and hide those columns, my chart disappears, which is obviously no good. That's not gonna help anyone out. So it's control Z undo hiding those rose and watch this all we need to do right click Select the data in our chart, click this hidden and empty cell button and check off that box that says show data and hidden rows and columns press. OK, Okay. Once more. And now watch this Hide columns A through G. And our chart is preserved. So a really nice way to kind of keep your source data on hand. Keep it right there side by side with your visual, but just hide it from view while continuing to populate on your chart or your graph. 4. Filled Maps (Excel 2016+): all right for this pro tip. Want to talk about using excels filled map visual for quick geospatial analysis. Now recent versions of Excel 2016 plus or Office 3 65 We'll include a new type of visual called a filled map to help you quickly and easily plot geospatial data. So in this case, we're gonna be looking at state level data with a few different metrics. Here we're looking at household income and column B Just like any other chart. We can select that range all the way down to 50 states and to our insert tab. And this map dropped down will include one option right now, which is thief filled map visual, and that produces something like this. And what's so powerful about this is that just like treating date and time fields, Excel is ableto automatically recognize many different types or variations of geospatial information, things like state names, city names, countries, zip codes, latitudes and longitudes, which may seem obvious but is actually quite impressive. Now, if some of your values aren't recognized like if you've misspelled state names or if you're using stranger unfamiliar abbreviations, you may see a warning symbol. Pop up in your chart that says something like, Hey, we plotted x percent of the locations with high confidence, but the others were not so sure about now. Quick thing to note here again, these filled maps are currently unavailable in older versions of Excel in pre 2016. And keep in mind you do need to be connected to the Internet for these to function properly . And that's because they're actually power behind the scenes by bing maps. Now pretty straightforward. Use cases here for one, quickly visualizing regional patterns or trends. Maybe you're comparing census information like we are here with household income or metrics like population gross domestic product, birth rates, etcetera. So, without further ado, let's jump into our pro tip workbook and practice inserting one of these geospatial maps already from your protest workbook. We're looking for our visualization tips. Here in yellow, we're gonna find that filled map Excel 2016 demo. Go ahead and link out that yellow town, and here you'll find that state level data got 52 rows here with two different metrics to compare. Got Household Incomes Column B. We have populations in column C. So again, just like any other charts. We're gonna start by grabbing the information we want. I'm gonna grab some of the values from A and B and then use one of my favorite shortcuts. Control shift arrow down to grab off the rose within those columns. And then let's scroll back up head to our insert menu. And here's our map dropped down with that filled map visual option, and there you have it is a simple is that we can treat this chart just like any other charts or object. We can hold Ault as we drag it to kind of snap it to the grid. I can resize to snap two column K, and we have the same access to these design and formatting tools that we would with anything else, like a pie or a line or column chart. We can access those formatting options either from the ribbon or from this paintbrush icon with chart style and color options right here. So let's go ahead and navigate to color. It's give this a similar monochromatic kind of blue tone here, which looks like was the default in this case, and then what I want to do is just customize the title here to household income. Here we go, and let's create a copy of this chart by selecting the whole object control. See selecting a cell beneath the visual and control V to paste it and instead of kind of recreating the wheel here, let's just go ahead and drag the metric that we want over the population, and you'll see that chart dynamically populate here. Now let's give it a different title population. And perhaps you want to change at the color of the style a little bit for this one to something a bit different, like orange or yellow, totally up to you. So there you go, in this case, because we're dealing with simple geospatial fields that we know like state names. We are plotting all of the values in these charts. But if, for instance, we messed up a good chunk of these labels like deleted them, for instance, you'll see these error or info icons pop up that we can expand to see. OK, we've plotted 78% of those locations with high confidence and the other ones we've just left with this default gray tone here, and sometimes that great tone could be a little bit tricky, little bit hard to see, so it's important to be careful and make sure that you've got consistent and accurate chart labels. Now all we need to do is control Z to bring those backing. You'll see that re populate in both of our visuals. And there you have it very simple visuals to use, but a really powerful way to integrate geospatial trends and patterns and analysis into your excel workflow. 5. Customizing Charts to Tell a Story: Let's take a few minutes and talk about how to customize your Excel charts to tell a story . Now I see it way too often. People just kind of settle for the boring default standard chart formats and templates in Excel. But one thing that makes Excel so beautiful and so powerful and flexible is the fact that we have access to all of these different tools for customization and personalization, like chart titles like formatting tools like Data Labels and Much, much More. So for this demo, what we're gonna do is look at it pretty small sample of data here. We're looking at five different countries across four different Winter Olympic Games and retracting the percent of gold medals won by each of those countries. And the idea is to visualize it using something like this. It's a pretty simple chart. It's a clustered column chart, but all we've done is used these different formatting tools in a clever way, like data labels like titles and subtitles like strategic use of color to not just present the data, but to craft the narrative that we want to tell our end user. So by using some of these simple tools that a lot of people overlook were able to make the key insight into key story crystal clear. Now the common use cases here for one, preparing visuals for use in presentations where audience comprehension is really, really important. Or one of my favorites reducing the chance of users misinterpreting this story that a charter graph is designed to communicate. So let's jump into our Excel workbook and practice using some of these customization options. All right, so if you're following along with the course, you open up your pro tip workbook. We're looking for the customizing charts. Demo is to star demo in our Visualization Tips section Link right out to that yellow town, and here you'll see our little sample of data got five countries Canada, Germany, Norway, Sweden and Russia. These are the top five countries in terms of Winter Olympic medal counts, and what we're looking at here is the share of gold medals. A percentage of total gold medals won by each of these countries across these four Olympic Games, 2000 to 6 2010 and 2014. So let's go ahead and select our data starting and Selby three going to insert in this case , I want a clustered column. But if I look at the preview here, this isn't exactly what I want. I don't want to cluster the years by country. I wanna cluster the country's by year so we could just go to more column chart at the bottom. This will give us the two variations we want the 2nd 1 in this case purse. OK, and there you go. So now we're looking at for each year. What share of metals or gold medals did each of these five countries capture or win? And if we resize this a bit, most Excel users would throw a title on here and call it a day. But we aren't most Excel users. We are power users, and we're gonna use the tools available to us to make this visual much, much more meaningful. So let's go ahead and start customizing some of these options gonna start just by right clicking one of the data, Siri's formatting, that data Siri's and all I want to do. Is it just some of the spacing here? I'm gonna can't tighten up the clusters that they're a bit closer together. Negative. 15. It's fine And then I want to increase the gap with a bit between those clusters just to add a bit more separation between them, 2 50 looks good, and now, as faras, the colors are concerned. This looks OK, but I don't like that this main serious here. Canada, which is kind of my central focus. As you can see the ideas Canada is just dominating these Winter Olympics. I don't really want to draw attention of that. I don't like that this fifth. Siri's Russia is so close in tone, more shade. Let's go ahead and just right Click that one. Siri's format it going to her fill online options, given a solid Phil and just choose a green option to really differentiate it from the other four. Siri's. Now it can make some other adjustments, like maybe spread out our legend a bit to make it a bit more readable. Let's make our years bold command be going to her home tab in increased font size a bit to make those really pop. I don't really care about the percentage itself along the axis. I care more about the difference between the different Siri's, so it's actually sighs this down a bit to seven. Use control I to make it a talek, and that looks good. And now, because we're starting to see this pattern or the story emerging, which is all about Canada's dominance, let's go ahead and select that Canada Siri's right click and add data labels just for that . Siri's click the labels to actually format how they look and are displayed, and in this case, let's actually give them Blue Phil White font. Make it bold. Whoops. If somehow gotten into this sub menu here, select those again, make the bold and drop down to about eight. So all those labels do is help draw attention to the Siri's. That we feel is kind of driving the story or the insight here. And I'm pretty happy with how things look right now. I think the last thing that I'd like to do is actually used the title and the subtitle to help tell the story and actually communicate. You know, this narrative that is coming together. So I'm gonna just delete the original chart title. You gonna select kind of background and create a bit of space here and now with the chart selected oven insert a new shape and I want a text box here. I'm just gonna drag a text box into this space, will be able to resize it as we go. And now I'm going to start with kind of a primary title or first line, which is kind of telling users what they're looking at, right? So in this case, it's the share of Winter Olympic gold medals, and this is really just for the top five countries, and you can use this exact formatting and wording, or you can write your own this case. Grab that top five countries text control I to make it. A Talic can make the rest of this header bold to make it pop and then select the whole thing and increased to about a size 12 here. So that's just a descriptive header. What do you looking at here? It's the share of Winter Olympic gold medals for these top five countries. Now the subtitle is something that a lot of people don't use, but it's a great opportunity to communicate what we're seeing here, right? And what we're seeing is that Canada dominated the Winter Games from 2000 to 2014 you know , capturing on unprecedented 36% of all gold medals in 2010. Again, feel free to customize. Tweak this as you see fit. But something like that that really tells the story in a crystal clear way. Go ahead and shrink that down to size 11 can resize er chart a bit, and we may need to just tweak that text box to make sure it's visible. All I'm doing is just making some adjustments. Here's to that subtitle at second line Lives on One Mine. And to really make that pop and draw attention to the key points in that subtitle, let's grab Canada. Make it Bold and to tie it to its data, Siri's and the Day labels. It's make the font that same bright blue color. Same thing goes with this. 36% make it bold and blue and very have it, so that sounds pretty good. You can kind of tweak and adjust as you see fit, but now what we've done is told the story. You've drawn attention to the right places, and we've done all this just by using some of these relatively simple chart formatting tools and because we had entered this text box. With the chart selected, you'll notice that these two objects move together as one unit. So there you have it. A few pretty basic tips to help bring your data to life and tell stories with your charts. 6. Adding Sparklines: all right. This next pro tip covers a date of his tool in Excel that I'm a huge fan of called spark lines and what I'm gonna demonstrate. It's how we can quickly visualize patterns and trends in our data using these spark line tools. So if you're not familiar with what spark lines are all about, essentially their tiny little charts that live within the confines of a single cell and because they're designed to be so small and minimalist you won't have all of the same formatting options that you would with a traditional chart or graph. But that said, they could be a great way to quickly and suddenly visualize patterns in your data. So for this demo, we're gonna look at monthly sales volume against goal for five different sales reps, and the idea will be too quickly. Visualize the seasonal patterns and sales trends for each of these sales people right there in the cells in column. Oh, so to do that, we can head to our insert menu, choose one of the three standard Spark Line options line column or win loss in this case will choose column, and then we'll simply confirm where the data lives and where the spark line should be placed. And just like that, it will insert these miniature column charts right there within cells. 04567 and eight. That gives a very clear picture of how each sales person is trended throughout the year. So with a few simple formatting adjustments were able to show positive values. It's green. Negatives is red highest value, our highest total for the year in that bright green color to kind of pop bit more so. As you can see, this is a great way to pack a ton of information and a ton of insight into a very, very small amount of real estate in your worksheet. So a ton of different use cases here. For one, you can embed simple line or column spark lines to show trends at a glance like we're about to do in this demo. One thing that I like to do personally is create KP I cells that show a key metric value with a spark line in the background to kind of show how that value has trended over time. So let's jump in or a pro tip workbook and add some custom spark lines. All right, so once you have your pro tip workbook open, look at the visualization tips here in yellow and looking for specifically the adding spark lines. Demo two Star demo. Go ahead and link right out to that yellow tab. And here we've got a sales data by month against goal for five different sales. Reps got Skeeter, My Gavin and Spandex purred happily, Lloyd Christmas. And, of course, our good friends stand pancake. Now it's pretty clear right off the bat that we really can't discern any sort of meaningful pattern or insight here just by looking at the raw data. It's very, very difficult. So we're gonna need some sort of visual aid, and there's no right or wrong approach here. Could enter or add a traditional line chart with five Siris in it. But in this case, I think it's a good opportunity to practice something like spark lines, where we can add a good kind of visual indicator right here within the worksheet cells. So the easiest way to add a spark line is to simply select all of the data that you want to visualize. In this case cells C four through an eight headgear insert tab, you'll see the Spark Line group right here with three different options Line column and win loss. Let's go ahead and start with a line spark line. It's gonna open up this dialog box that says, All right, you're data lives and C four through an eight. Where do you want to drop those spark lines? And in our case, we want, 04 through eight and press OK. And just like that, it's populated these nice little mini line charts right there within the cells and even just that one step has helped us understand or really see what kind of seasonal patterns air going on here. Looks like Stan had a pretty rough start to the year kind of clawed his way back up, built all the way up to Q three and then kind of fell back for the last quarter of the year . And one thing you'll notice here is that if you select any one of these spark lines, but kind of all get grouped together by default, and if you don't want that to be the case, for instance, if you want to change the formatting rule or the chart type. But just for one spark line, go into your spark line tools, you can select all of the spark lines and click on group that will allow you to edit each one individually. I actually prefer to keep them grouped in most cases just because, generally speaking, if I'm making formatting changes or changing the chart type, that's something that I want to do universally, not for individual spark lines. So within the Spark Line Tools menu, let's see what different design options we've got. If our raw data changed, we could actually edit the source data here. Right now, we're looking at a line chart, but we could change that to a column. For instance, kind of creates the same pattern or a win loss, which essentially just converts any positives or any negatives to the same maximum bar height. And really, that's just if you want to simplify things and Onley pay attention a positive versus negative as opposed to the actual value itself. Now, in this case, I think columns gonna be our best bet. So let's stick with that chart type Here in the show group, we can identify which specific types of points to draw attention to, like the highest point of the month or the lowest point or the first or the last now in this case, and I really want those options. But I do really want to draw attention to the negative points, and from here we can go into different styles. Maybe we want something like a green red to really feature that positive negative difference. And then finally, we can customize the colors here with these options. So maybe want a darker green and our marker color for negatives. It's a bright red, maybe want that little bit darker. And then if we change our mind, we actually do want to highlight the high point. We can go ahead and customize the color of that high point as well, and I think this bright green actually looks pretty good. I'm so with just a few clicks of the mouse, we've added some interesting insight here and created a visualization that really does tell a story at a very quick glance, you know, so you can see that most of these sales people had a really good August, or in Anne's case, September looks like Stan had a very tough start to the year. He was negative for the 1st 4 months, uh, and pretty much crushed it. She only had one negative month where she missed her goal. I'm and that was in March. So these are the types of stories and insights that spark lines, especially ones like this are very well equipped to tell, even though they're relatively simple and simplistic tools. Let's go ahead and select one of these. Get back in your spark line tools and last options Here for access, you can customize general versus date axes. We want to show the Axis. It's just gonna draw kind of a horizontal line between the positives and negatives there. And you've got some other options here, like you can set Max and Men for a single spark line or for all of them as a group. So a lot of really interesting options here, but I think that's gonna do it for us. That's telling the story we need. It's simple, it's clear, and it's powerful. So there you have it. Crash course on using spark lines in Excel 7. Custom Chart Templates: all right. I want to take a few minutes to talk about designing and applying your very own custom chart templates and excel. So let's say you've taken some time to apply a very specific set of formatting rules to a chart, and you've created a style that you really, really like and that you want to be able to apply to other charts not just in your worksheet or workbook, but in other files as well. So maybe it looks like something like this where you've got this great background. Phil Grady INTs, data labels, etcetera. From here, you can simply right click that chart and choose save as template. And once you've done that by selecting another chart in your workbook like this one, you can right click. Open up the change chart type dialogue box and you'll see that templates option right there at the top of the list containing the template that you've defined. So it's a very straightforward process here. Essentially, what we're doing is using templates, much like we would use the format painter to grab formatting from one cell and apply it to another. In this case, we're just working with charts or visuals. So the common use cases here again, saving those specific formats or styles that you really like for future use, either in the current workbook or in others, and also ensuring consistent formatting across all of the visuals in a report so that you're not manually trying to replicate format settings each time. So with that, let's go into our pro tip workbook and practice designing and saving our own custom chart template. All right, so in our pro tip workbook, look for the visualisation tips and we're gonna be drilling into the custom templates. Demo here can link straight out to that yellow tab. And here we're looking at 2017 North America sales by month for a very strange company that only sells three products hot dogs, snow shovels and tube socks. And what we've done is insert three charts here above to basically show those seasonal patterns for monthly sales of each product. And the idea is that you know we want to spruce these up a little bit. I don't love the default format here for these column charts. Let's go ahead and try toe format or customize this first chart. Save that style that we've created as a template and then apply it with the click of a button to charts two and three. So keep in mind, you don't have to follow along with anything that I'm doing. What I'm gonna do is quickly run through a bunch of different formatting options just to give this chart a very different kind of look and feel. You can go rogue and create whatever options air setting to choose, or you can try to follow along with me. Exactly. It's totally up to you. So what I'm gonna do to start is just right. Click the chart area and format that area to give it a nice, solid background, Phil. And I'm gonna give it kind of dark charcoal grey right there. Like to update these grid lines as well. Some select just the grid lines. Give them a white Phil or a white line with almost full transparency here just to make them kind of subtle. An 80% looks good. My data bars or my columns themselves definitely want to change the color here, so let's go ahead. Give it a Grady int. Phil, um, you may see a different default set of greedy INTs. In this case, I'm using this kind of bright, awkward blue color that it kind of like So color looks good. Let's jump into our Siri's options here and by reducing the gap with it can essentially bring these columns a little bit closer together. I'm something like 75. Think that looks nice? And then last but not least, this dark gray for X Y and title is a little bit tough to read. So let's select those X labels, make him white, gonna convert to bold and drop down to size eight. That looks good. Why Axis again? Bold white Funt to make it I talic and quite a bit smaller, maybe a size six there and same story with her title Kind of tough to read. There. It's Go ahead and highlight the title. Make it white. Bold Size 12 looks fine, and then the final update that I'm gonna make here it's just add some data labels to that Siri's. So I want to show you how those come get applied as well. A little bit tough to read. But there they are. Make those white metallic and very, very small. Maybe like a size five just so that they're there if you need them. And that looks good. So I have to find the chart template, that style that I want. Now the idea is to save. This is a template so we can pop it into these two charts very, very quickly. So right click Save as template. It should automatically navigate to this roaming Microsoft Templates folder or something similar. Just gonna give it a name like awesome chart, for instance. It's saving it as a chart template file types specifically and press save. And from here, all we have to do is grab one of our other charts, either going to chart tools and click change chart type. Or we can right click and access that same dialog box from here, and we're gonna go up to templates. Awesome chart here and press OK now, everything applied, except for some reason the chart title didn't apply. This has happened in the past. I think it may just be buggy behavior. If that's the case, we can go ahead and head to her home menu, make it white, bold and 12 and that should just about do it. So let's apply that same process to our third chart change chart type templates, Awesome chart. And then again, we have to customize that header that title for some weird reason. Not sure why. So there you go. Great way to design and apply your own custom chart templates in Excel. 8. Heat Maps with Color Scales: I want to take some time to share a pro tip. That's one of my favorites. It's designing your own conditional heat maps and excel. Now, in my opinion, one of the most effective types of visualizations in Excel isn't a chart or graph at all. It's a heat map that you can create with basic conditional formatting rules. So what we're gonna do in this demo is start with a very basic range of values. We're going to select that range access or conditional formatting options from the home tab , and we're gonna insert a color scale from there either red to green or green to red based on the context. And that's gonna turn those raw values into something like this, a really powerful, really effective heat map that draws attention to these patterns in these hot spots within your data. Now you'll notice that you don't actually see the numbers themselves anymore. That's a little bonus tip here. Toe Hide that text. You can apply a custom number format and set the type to the triple semi colon, which essentially makes the text invisible. So a few different use cases here, one for very quickly identifying these types of patterns or trends in the underlying values , using color scales like red, green, white to red, white to blue, or even a custom rule that you define, and then also for highlighting hot spots or outliers using these colors on their own. So let's shift gears and do, excel and build one of these heat maps of our own. All right, So if you've been following along with the course, go ahead and open up your pro tip workbook and look for the conditional heat maps Demo to star demo in our yellow visualization section here, and we'll go ahead and link straight out to that sheet, and what you'll see here are accident counts or frequencies based on the hour of the day and the day of the week. So I've got my hours here in Row three from midnight to 11 p.m. I've got my days of the week here in column A from Monday through Sunday, and these values here represent the actual number of accidents that took place during these days and times and you see, have broken it out a little bit. Here. Column B. I've got my aggregated counts, which are based on Lee on the day of the week in Row four have aggregated counts based on Lee on the hour of day and then in this range here from D six through a 12. I have the frequencies broken down by both our day and day of week. And looking at it thes three different ways will help us identify and expose some really interesting insights and patterns in this data. So at first glance, you know, if you look kind of closely, you'll start to see some patterns, definitely some high volume, mid morning, mid day afternoon. And then it kind of peters out very, very low volume of accidents from 2 to 5 a.m. which you'd expect. But we really can't understand the big picture without applying some sort of visual aid here and a case like this where values were broken out in a matrix kind of format like this , a conditional formatting heat map is a perfect choice. So let's go ahead and start by just selecting one of the ranges of cells that we want to format. In this case, I'm gonna choose that main array, a range of cells going ahead to conditional formatting in the hometown Choose color scales . And as I hover over the options, you'll see them applied to the underlying cells and you can choose whatever options you want here. One thing that I would recommend is that you avoid using the default option green, yellow, red scale. Because by default, high numbers are gonna show up as green in this scale as if that's a good thing. But in our data, high number indicates a high frequency of accidents taking place, which is not such a good thing. So in this case, I'm gonna choose the second option the red, yellow green. I could also use the red white green totally up to you and just click to apply and check it out right off the bat. These stories and these patterns are emerging like these cells here immediately tell me that Monday through Friday, specifically at 8 to 9 a.m. We have an unusually high frequency of accidents taking place, and it really doesn't take long to realize that this is rush hour on the five work days of the week, and then we have kind of a similar bump during the afternoon rush hour. from 5 to 6 p.m. Generally speaking, the accident rates are a little bit lighter on weekends, since fewer people are on the road. And let's go ahead and just apply that same color scale to the other two components of this heat map. There we go, and then we'll pick the column B cells, color scales red to green. And there you go. So just by looking at you know, these other components like column B tells me that regardless of the hour of day, Fridays tend to be the worst days in terms of accident frequencies. Same story here in Roe for just looking at this row, regardless of the day of the week. 8 to 9 a.m. is pretty bad 3 p.m. And 5 to 6 compared to late night or early morning hours. Now we've already created a tremendous amount of value just by adding that color scale and in many cases you may want to keep these values visible, just like they are, in which case you could call it a day and be, you know, 100% done, totally valid. But what if we don't want to actually show or expose the numbers themselves. We just want to pay attention to the colors and the relative colors in this scale. Well, we can't just delete the values right, because if we delete the values, the color scale, which is dependent on those values, disappears. So let's undo that. We can shrink the text size down a bit, but we can't really get it all the way invisible. So what we need to do here is used a custom number format to actually hide the text or make it invisible. So what we'll do is grab all of the cells that contain numbers. We're gonna use control one or right click to jump into the format cells dialog box, and we're gonna jump down to custom at the end. And right here in this Barbara, it says, General, delete that, and type three consecutive semi colons press OK. That custom number format essentially makes any text or values in those cells completely invisible, and note that it didn't delete those values. You click the cells, you'll still see that those values exist. It just simply displayed them as invisible. So this is a great tool to use when you don't want to overwhelm the user with the actual values. You want to simplify the visualization a little bit and really just pay attention to those hot spots using the color scales themselves. So there you have it. One of my favorite Excel tips, using basic conditional formatting to create heat maps and excel. 9. Analyzing Distribution with Histograms: All right, let's talk about data distribution and, specifically, how to analyze and visualize distributions in Excel. Using hissed, a grams now hissed A grams are great tool to understand and visualize how the values in a data set are distributed. So when I talk about distribution, I'm talking about the frequency of observations or rose that fall within given ranges of values, which we call bins. Now, in this demo, we're gonna look at a sample 2000 Olympic athletes. We've got names, genders and sports, as well as numerical fields to capture their age, their height and their weight. And these are gonna be perfect fields to use for this type of distribution analysis, because we'll be able to see how those ages and heights and weights are distributed throughout this sample. And then we can look at subsets as well to see how those distributions compare for men versus women or for athletes within specific sports. So to do this, we're gonna insert a new chart. We're gonna drill into our statistic chart options and select hissed a gram in the top left , and that's gonna create these visualizations just like the ones you see here now you'll notice that many of them have this kind of a bell curve shape to them. That's a very commonly occurring pattern in nature, called a normal distribution. And what this shows us are the buckets or bins that occur most frequently towards the center of this curve, as well as those that occur very infrequently towards the tales of the curve on either side . So common use cases here visualizing population demographics like we are here. Heights, weights, incomes, ages, etcetera or identifying the most or least common values in a sample now important note, hissed. A grams aren't available in older versions of Excel as a standard chart type. I believe they were introduced in 2016. You can access them with Office 3 65 and I believe if you're using an earlier version of Excel, you may be able to activate the analysis tool Pack Adan Ah, which does include the hissed a gram option. But I don't think you'll see it as a standard tool like I'm showing here with that. Let's jump into excel and analyze some data already, so if you want to follow along, go ahead and open up your pro tip workbook. We're gonna be working through the analyzing distributions demo in the yellow Visualization tips section, and we'll go ahead and link out to go straight to that yellow town. And here we've got our sample of 2000 Olympic athletes. Keep in mind, 2000 is actually a very small number of observations, especially when it comes to this sort of statistical analysis. But for the sake of the demonstration, it'll get the job done. It'll be a good enough sample for us to get some experience practicing, working with hissed A grams. So what I'd like to actually do here is insert the hissed a grams above this table so that we can filter and slice and dice and look at different sub segments of our sample. So first things first, let's right click Row one in certain row and then use F four to repeat that action a number of times until you've got about I don't know, 15 rose above your table. And now let's go ahead and select the first field that we'd like to visualize with Instagram. In this case, I want height first, so I'm gonna select E 18 the header and use my trusty control shift arrow down shortcut to grab all of the values and little tip here. If I insert the chart right now, it's gonna insert it way down here at the bottom of my list. I don't really want that. So I'm gonna scroll all the way back up, and now I'm gonna navigate to insert autistic tools. Top left option is the hissed a gram. So there we go. You just inserted our first hissed a gram. Don't resize it cause I'm gonna put a 2nd 1 appears well and basically what you see is that cell has automatically plotted the frequencies. It's automatically determined a number of bins here on the X axis and right off the bat, we see that kind of normal distribution curve taking place. Now with hissed a grams, you can right click the Benz format the axis and change things like they've been with or specify a certain number of bins. And often people ask, you know, how do you determine the right number of bins? And the answer is that there's no hard and fast rule. You know, it always kind of depends for me. I follow two rules. Most the time. One is to come up with bin widths that are readable and then around it to hold numbers. And two, I usually follow a rule called Sturges Room, which is a calculation that's based on the number of rows or observations in your sample. And this is what Sturges rule looks like equal to one plus 3.3 to 2 times the log of the number of rows 2000. So we close that out and hit. Enter Sturges rules suggests just about 12 bins in our history. Graham, for a sample of this size so we can do is go ahead and right click those bins format the axis and let's see what 12 bins looks like. Well, it's a smaller number. Visually, I think it still makes sense. Problem is that in our bins aren't really readable at all, cause we have these fractions of centimeters that we're dealing with. So what I'd recommend is starting with Sturgis recommendation and then just going to been with and rounding it to close hole number like six centimeters. And there we go. That looks pretty good. We still get that bell curve type of shape, but we're now looking at bins that each contain, you know, six centimeters worth of data. So 1 73 to 1 79 here in the middle, 1 79 to 1 85 and so on. Let's go ahead and give this title called Heights and we're good and I want to insert one more for weight. But instead of starting from scratch, here's a little pro tip. You select this first chart, kind of hover over the edge until you see that four pointed arrow, click and hold, then holds control and drag to the right. And what I'm doing is creating a copy of this chart as I drag very, very nice, like little nifty tip to save a few seconds. Now, once I have that duplicate height chart, all we need to do is grab the range right on the edge and drag it over to the values that I want, which are the weights in kilograms. So all you have to do here has changed the title to wait our weights and then adjust the Benz just like we did. So since it's based on the same sample, Sturgis rule would spit out 12 again, so you can see what 12 would look like, and then it just have been with to the nearest whole number. So maybe 10 kilograms in this case. And now we have nice, readable bins for both our height and our weight, hissed a grams. You can see that the weight pattern or distribution already looks a little bit different. It's skewed more towards, um, athletes who are weighing in at 55 to 65 or 65 to 75 kilograms. But where it really gets interesting is if we actually filter down to sub segments of our sample here. So we're looking at the entire population right now. But what if you want to compare how females vs males compare? Let's filter just to the F for female, you can see our hissed, a grams adjust. In this case, the most frequent been for female heights is 167 to 173. So about 170 centimeters and for weights, about 60 kilograms. And when we swap this over to M for male now, all the sudden we see the most common heights from 1 74 to 1 80 or 1 86 Most common weights fall around 80 kilograms. We can continue to drill in as much as we choose. Let's unfiltered er by gender. We can look at how you know different sports. Compare here is well, so maybe you want to look at something like basketball where we know we'll see it. Very, very tall athletes. So in this case, the most frequent height been for basketball. Olympic athletes is over 190 centimeters. Weights 88 to 98 and we compare that to something like gymnastics, for instance. Now the most common height is 1 67 to 1 73 The most common weight is 55 to 65. So we're identifying all of these different patterns and trends in the distribution of these height and weight values based on sub segments of our population. So let's go ahead and unfiltered this. Feel free to explore this data on your own or some really interesting kind of patterns and insights to be discovered here. So there you go quick little summary of how to use hissed a grams to analyze distributions in your data 10. Goal Pacing with Gauge Charts: all right. Time to do a little bit of Excel magic and convert doughnuts into gauges. And what I mean by that is inserting a standard doughnut or pie chart and excel, applying some clever formatting tricks to it and converting it into a gauge style visual. Now these gauges do an excellent job quickly showing progress towards something like a benchmark or goal and their great tool to use for things like scorecards or executive dashboards. So we'll make this work by following a few different steps. We're gonna start by adding some cells containing three core values. The current value, the gap and the goal in that exact order from there will insert a doughnut or a pie, chart your call and then format that goal. Siri's, which represents half of the chart with no Phil and no border to essentially make it invisible. From there. We're gonna right click and format the data Siri's to basically rotate our entire doughnut or pi into a configuration that makes sense. And to do that, we're gonna set the angle of first slice to 270 degrees. So extra tip here. You can also add a percent to gold calculation as 1/4 value and insert a data label into doughnut that's linked to that cell value. And I'll explain exactly why we do that and how we do it in just a second. Now the common use cases here again tracking performance against Benchmarks goals or designing those exact style scorecards that help people visualize key metrics and gold pacing at a glance. So let's get her hands dirty jumping to excel and create some of these gauges. All right, so in the pro tip workbook in the table of contents were gonna head to the custom gauge charts Demo in the yellow visualization section. I'm gonna link straight to that yellow tab. And here you see it got some basic placeholders with three different types of goals. Have a lead goal here, 1000 revenue goal of 100,000 a profit goal of 50,000. And keep in mind, these numbers are hard coated. They're totally arbitrary. Feel free to change them if you like. And essentially our first task here is to populate these four cells that's going to drive our visualization or service the source range for this doughnut and the current value This is another hard coded value. It basically just indicates how far along are you currently to that goal? So just for the sake of demo, let's put something in here like 2 50 and press enter. And now that we have these two values, we can calculate the gap, which is a goal minus current G seven minus D five 7 50 makes sense and the percent two goal, which is current divided by goal d five divided by d seven or 25%. So super simple. That was all it took to populate our source data. Now let's go ahead and select those 1st 3 current value gap and Goal and insert a new chart . Go into our pie options and you'll find doughnut. Kind of done the bottom here, and we'll just drag it over, resize it a little bit to fit. And we don't need a legend in this case. Actually don't need a title either, so this is a pretty good starting point, and now we have to go through those steps and apply some of the's kind of clever formatting tricks here. I'm going to right click the data format. The data Siri's and this angle of first life's option, it's just gonna rotate that pie or that doughnut all the way around. And what we're gonna do is rotate it until the point where that 50% segment is right on the bottom, just like this. And while we're in here, let's also shrink down that doughnut hole size and make the segments just a little bit thicker. That looks pretty good. And from here, this bottom 50% Remember, that's what we want to just show up as totally invisible, right click and format that data point specifically, head to our fill and line options and say, Don't even fill this segment. Don't give it a border Anything like that just make it totally invisibles. Now always see are these blue and orange segments and let's go ahead and just select the actual chart area as well. It's kind of an empty space up here, and I don't want that border around the square of my chart. E guess we don't really need the background fill, either, and from here we can change these colors to something that's a bit more logical. Blue and orange doesn't really make a whole lot of sense. This is kind of up to you. What I like to do is, ah, use one color with a light fill for the portion of the gauge that represents the gap. So, like a light green solid Philip, for instance, and a darker version of that same color for the portion of the gauge that represents the current value. That just kind of makes more intuitive sense to me. It's a little bit more readable, in my opinion, and that, really just about does it for the basics. We can go ahead and test this out. So let's say we had 300 leads. Now see that gauge kind of bump up? We get 500 we should hit that halfway point. There we go. It's a vertical segment. 7 50 should get a 75%. And there you have it. So it's working properly. It's doing exactly what we want, and it's doing a really nice job, kind of showing that progression towards a goal in a very clear and simple way. Which is why these types of visuals I'm are often used in dashboards and executive summaries. Now the last thing here, which is optional but I'll show you how to do it is to add data labels here, and what we're gonna do is select the current value segment. Specifically, I'm going to right click and add data label, and it's kind of dark, so I'm gonna make it white so we can read it, and by default, it's gonna label it as the value itself. So 7 50 And that's helpful. That may be exactly what you want. But what if, instead of the number, you want to change that to a percentage? Well, you can right click or head over to your label options and check the percentage box. Now this tells me that our label for the current values 37% of the way to goal, which doesn't make sense because we know we're more than halfway to the goal. In fact, we're 75% of the way there, So 37 is understating that percentage by 50%. And remember, the reason that's happening is because even though this bottom segment, this goal segment is invisible, it's still part of the chart, and it still represents 50% of the total values, which is why that percentage is understated. So what we need to do here? It's click value from cells and point to that calculated cell in D eight. And when we do that and press OK, that's 75% in the label is what we want. And now we've got all this extra stuff in here to get rid of percentage, get rid of leader lines and the value still kind of hanging in here. I'm not sure why it does that you can actually just click, double, click in and delete the 7 50 component of it. And that should just about do it. I'm so let's make this label bold. Let's go to our home menu. Crank up the size 2 11 That looks good. Now let's test out that label. So 25 should be 25%. Oops, not 25 250 should be 25%. There we go. 630 should be 63%. And now check it out that labels working perfectly because it's now tied to that calculated . So now from here, it's just a matter of kind of tweaking the sizing and formatting. How do you like it? And once you have that first chart in a great place. The last thing you want to do to populate these revenue goals and profit goals is go through that exact same process and build new gauges from scratch. So what we're gonna do is we're gonna populates, um, current value numbers here in row five. So let's say we've hit 30,000 revenue so far. Safer profits doing pretty well. It's actually 40,000. Now we can do is copy the formula for Gap and paste it here columns. Agent Hell, do the same thing with percent to goal. So we're not recreating the wheel. And now check this out. When I select this chart, I'm gonna show you a really cool tip. If I wait for the four pointed arrow hovering over the edge of this gauge click and hold both control and shift drag to the right, that's gonna limit the movement to this horizontal plane and watch what happens when I let go of the mouse. It's created a duplicate copy of that chart as I dragged it over here towards my revenue. Cool. And now all I need to do is grab that source that reference. Drop it right here. And that recreates or Recalculates that gauge. And the only other thing now is this label, which is tied to a cell conform at it. Select the range that's pointed to, and now it's no longer D eight. Now it's this one here, h eight. And there you go. Now it says 30%. So same thing here. Select the edge of the chart control shift drag boom. Drop it to profit Drag that source reference over here to profit numbers. Right Click Format. The data label. Select the range and point to L eight instead. 80% looking good. Let's test these out. 25,050% Boom. We're good to go So we just created our own custom gauge chart visualizations by applying some very clever formatting to standard doughnut charts in Excel. 11. Highlighting Date Ranges: all right, I want to share with you guys a four star date of his pro tip. This is an advanced tip, definitely getting towards the expert end of spectrum. And what we're gonna talk about is how to highlight date ranges using combo charts. And what we're gonna do is use kind of an unusual version of a combo chart to visually highlight specific time periods or date ranges within a line chart. And for this demo, we're gonna be looking at some pretty simple time. Serious data got dates on column A got revenue trended out in column B. And the key here is that we're gonna add a new column like the one you see here called Sale that essentially flags are key rose or key dates that ones we want to highlight with a one and all of the rows in that column where they're gonna leave blank or drop in a zero. Now, from there, we're gonna plug in both the revenue and the sale columns into a new chart. We're gonna set the chart type two combo and configure it so that our sale column is plotted as a 100% stacked column on the secondary axis. Now, with a little bit of formatting tweaks. To get rid of the gap with will end up with something that looks like this, and it's really nice, clean and polished way to draw attention to a very specific time frame or span of dates. That helps to add context to the numbers that you're seeing. So in this case that dates that we've highlighted November 27th through November 30th represent a very large promotional period, which helps to provide context and explain why we see those very large revenue spikes right at the beginning and end of that time frame. Now little extra tip. Here you want to actually hide the secondary y axis like you see in the screen shot. What you can do is apply a custom number format to the axis itself with the three semi colons in a row, which essentially makes that axis and those labels completely invisible. So common use cases like we're showing here the most common one is highlighting things like sales or promotional periods to help add that extra context to the numbers shown in the chart and also drawing attention to other sort of patterns like seasonality or certain trends like peak versus off peak periods or maybe weekends versus week days. These are all great examples of how this tip can come into play. So let's jump into our pro tip workbook and actually practice building one of thes combo charts. All right, so for those of you who would like to follow along, we're heading to the highlighting date ranges Demo in our visualization tips section. Gonna go ahead and link straight out to that tab, and here you'll see our dates and our revenue values and can't be looking out a window of two months of time November, December 2015. And we've got this line chart showing that revenue trend over time and right off the bat, you can see some very significant peaks occurring towards the end of November. And I know that that's due to big promotional sale that this company runs right at this time of the year every single year. It's the black Friday cyber Monday promotion. So what I'd like to do is add a bit more context to this chart to help tell that story to our end users. So we're going to start by inserting a new column right here to the left of seem, Call it promo, Name it. Anything you want doesn't really matter. And the key is to navigate to those rows containing the dates that we want to highlight. And in this case, the promotion or the sale started on the 26th. So I'm gonna add it one or flag there, format it as currency. That doesn't really matter. But go ahead and form at the cells. Change it to general or number. And basically, this sale is going to extend from the 26th all the way through December 1st. So it's a six day promotion from the 26th of the first. And like we talked about, we can leave these other rose totally blank. We could populate with zero if we wanted to, but this is just fine for now. And what we're gonna do here is right Click our chart. We're gonna select the data because we need to add a new Siris in here. And Siri's name is promo Serious values. Basically all of these values, including the blanks right here in column C all the way down to rose 62. Let's go ahead and lock that in press. OK, Okay. Once more. And now it Look, I kind of useless right now because it plotted it on the same primary axis as another little line segment here, which isn't very helpful. So we need to navigate to chart tools, change chart type. And here's where we're gonna configure those combo chart settings to get this toe look the way we want it, Teoh and what we're gonna do, It's kind of taken a stab. It'd which is actually reversed. We want our revenue to remain a line and we want our promo to be a 100% stacked column right here in the top, right? And the key is to check this secondary access box. And that's all we need to do for configuration in this dialog box in a press, OK, still looks a little bit weird, but we're getting close now. We can select that promo. Siri's conform at it and we can remove that gap with pull those columns all the way together into a solid rectangle. And from here we can jump to her fill and line tools, give it a solid Phil, whatever color you want. I do kind of a yellow here and give it some transparency. I want to be subtle but noticeable enough that it draws the eye and it really draws attention to the specific range of dates that reflect the promo period. So that looks just about fine. You'll notice that I lost this smoothing on that line, Um, so you can just select the line and in the same kind of fill online settings, just click that smooth line button at the bottom And there you go. That just about does it. It's a subtle little effect, but it's quite effective at really getting the end user to realize that there's something special about this date range that's highlighted so kind of a unique tip, but one that I actually use quite a bit. And if you want to take it one step further, you'll notice here that it's added the secondary. Why access here, which is just 0 to 100% and because our values aren't really meaningful, it's just a binary one or zero, I guess. One or blank flag. Um, this axis doesn't really mean anything. In fact, it's kind of just distracting the problem is if we select it and delete it, that deletes the whole Siri's as well. So Control Z. To undo that, we could shrink it down. We could change it to white font, which is an okay solution. The version that I like to use or the approach that I like to take is to format that axis, go down to the number category at the very bottom instead of percentage, gonna choose custom and type in my own format code, which is that triple semi colon press ad. And that just makes the whole axis invisible. It continues to plot the Siri's. It just basically hides that 0 100 scale, um, from view. So that looks pretty good. I'm satisfied with that. You could continue to take this further and add markers or data labels to draw attention to some of these peaks. But for now, I think that will get the job done. So there you go kind of a unique way to use combo charts, toe highlight date ranges within your visualizations 12. Dynamic Source Ranges: All right, everyone. Time for an incredibly powerful date of his pro tip. Gonna talk about creating dynamic source data with named Ranges. Now, this is a five star tip fully on the expert end of the spectrum. Now, listen, if you can master when I'm about to show you, then by all means you have the right to call yourself an Excel data viz wizard because this is not easy stuff we're going to do here is used named ranges to define a dynamic cell reference, a reference of cells that can grow and shrink and move based on things like user inputs or sell values. And we're gonna do this using functions like offset and match within the name manager. So the idea is going to be to create something like this. You know, it's a basic line chart trended out over the course of a year, got a fancy little polynomial trendline in there as well. But the real key is that dropped down in the upper left at select metric box. So that's step one is to create that data validation list. And that list is gonna contain all of the column headers or all of the fields that you'd like a user to be able to visualize in this chart. And our end goal here is going to be the wire up this chart and connected to that dropped down data validation list in such a way that you can select any one of those metrics from the drop down and see the chart update and pull data from the correct column right in front of your eyes. And the way this is all gonna work is by defining a new named range, and we're gonna call it something like dynamic source or dynamic charts. Source. The name doesn't really matter. What matters is that formula where it says refers to. And if you're dealing with very simple named Ranges that usually is just a static cell range, you know, sells a one through a 100 is named List A or cells C six through C 20 is named holidays. Those are examples of named ranges, but they're much simpler versions of what we're about to do, because by using these reference, functions like Offset and Match were defining a list of cells or range of cells that will essentially bounce around depending on what value a user has selected from that drop down. And don't worry about the formula right now. I'm gonna show you exactly how that formula works as soon as we jump into excel. But the final step will be to right click that chart, select the data and then edit the actual data source to reference the named range that we just defined. And if we play our cards right, that's going to create the interactivity that we're looking for. So use cases here, creating interactive dashboards with user controlled charts and graphs and consolidating multiple visuals into a single chart template. So, in other words, were kind of creating one, ah, framework or placeholder for our chart and allowing our users, or, I guess, ourselves to be able to very quickly visualize all sorts of different Siri's and different metrics without inserting multiple new charts. So really, really powerful, extremely flexible and useful tool. Now, enough talk, I think I've hyped it up enough. Let's go ahead and jump in or pro tip workbook and let's walk through this step by step. All right. Now, for those of you who are brave enough to follow along with me go ahead and check out our visualization tips here in the table of contents, and I'm gonna jump right into the dynamic source data demo again. This is a five star, expert level demo link right out to the sheep, and let's see what we got to work with here. Looks like we have about eight years worth of data here with dates and column A. We're tracking three different metrics that daily temperature, the daily wind speed and the precipitation amount in inches, and you can see we've got a chart that's already inserted here. It's a line chart with the trend line. If I right click, select this data and at it Siri's. You'll see that it's a pretty standard fixed reference to a cell range B two through B 3 64 So the point is, this chart is a pretty standard plain Old Excel chart. It's not dynamic. It's not gonna be moving. It's always gonna reference those cells and column B. So our goal is to make it dynamic by creating a drop down list here in F three. That will allow a user to determine which of these three Siri's they'd like to plot So to get started, we're gonna head to data, gonna add some data validation criteria here. And within this cell F three, we're gonna allow a list of values and the list of values is equal to each of the headers and be 1 31 press, OK? And there you have it. Now we've got this user control where people can basically say Show me precipitation trended. Now show me temperature trended and this chart template will accommodate those selections and pull in the proper value from column B, C or D. Now, obviously, nothing's gonna happen yet because we haven't defined any sort of relationship between the chart or the Siri's within the chart and the drop down cell. So to do that, we need to use something called a named range, and we're gonna find are named Ranges in the Formula tab. And I'm gonna click this define name button, and this is where the magic is gonna happen. So this is where we're gonna define that dynamic, flexible range of cells that will shift around and land at the right column based on the user selected value in our drop down that we just created F three. Now a word of warning here. We're gonna get into some pretty heavy formulas. If you aren't familiar with the fundamentals of an offset function or a match, I recommend checking out my formulas and functions. Course otherwise. Just try to follow along the best you can. I will be working through this relatively quickly, so apologies in advance. Here we go. So let's name this range something meaningful like dynamic range, for instance. And here's where the magic is gonna happen right here in this cell that says refers to and in a standard or very simple named range, this would just be a cell reference. It would be self C two or so a one through a 100. When you do that, you're creating a static or a fixed range. What we want is something a little bit fancier, a little bit more dynamic. So we start with an offset. What's gonna make this even harder is that we don't have intel a sense we don't have a formula helper while we're here inside of our dialogue box. So it's kind of Ah, another challenge we've got to deal with here and follow along here. We're gonna offset from sell A to Okay, that's our starting point for the offset. Now Kama over the second argument of dysfunction says, OK, how many rows down do you want to shift right off the bat? And we don't want to shift down any rose. We want to stay on Row two at the top of a range, some type of zero there. Now the third argument is how many columns do on a shift to the right, and this argument is actually very important, right? Because if a user select temperature from our drop down, then we'd like to shift over one column from a to if the user select wind in mile per hour from the drop down, then we want to shift over two columns for May to they've selected precipitation. You get the picture, then we want to move three columns. So as you can see, it's dynamic, and it depends on the selected value here. That's where function like match is really helpful. So we're gonna see match selected value in F three, and we're gonna look for its match right here in our column. Headers be won through D one and then to close off this match function. We just need the match type exact match, which is a zero. And let's go ahead and close this out with the parenthesis. And that ends our match statement, which remember, is telling the offset how many columns to move from column A 12 or three, depending on the drop down selected. Now that's comin over to the next argument and offset, which is the height of the resulting range that we want to return. And there are two different ways we could approach this. I happen to know that there are 363 rows of data in our data set, so we could simply type 363 here, and that would do the trick just fine. We want to get a little bit more sophisticated and accommodate for the fact that this list make grow or this date range may expand over time. Then we can use something like count A and say, Don't just return 3 63 Return the count of all of the non blank cells in column A and then subtract one for the header. So in this case, it will return 3 63 But if we come back tomorrow and tack on seven days to the bottom of the list, then the count day function will return 3 70 which is what we want. So we're adding more flexibility here to allow for cases like that. And now final argument. One more common over is the actual with of the resulting array. In terms of the number of columns, all we want is one column wide. All right, we're just plotting a single Siri's. We can close that parenthesis and cross our fingers. That we wrote all that correctly is that was not simple, right? So press OK, so far, so good. Final step here is to actually tell our chart not just a point two column B but to point to that named range that we literally just defined. So right, click Select Data. We're gonna click this attempt. Siri's here and edit it. I'm gonna change two things here. The header is no longer fixed at B one, right? The header is whatever you've selected here in f three. That's the first update. Second update is to strip out this fixed reference to be two through B 3 64 And I'm gonna grab very carefully everything right after the exclamation point. I'm just going to get rid of everything after that. And here's where I'm gonna manually type in the name of the range that we just defined, which is dynamic range and press. OK, so far, so good press okay again and see what just happened. It changed. I already changed to precipitation because we had left this cell F three on precipitation. So that's a very good sign. What if we go back to 10th? Boom. There you go. Temperature, wind mile per hour. There you go. So we've wired it up using a named range and a data validation dropped down. And if you select the Siri's, you'll see it's working exactly like you'd expect. Precipitation pulling and values straight out of column D and let's just make sure it's going right to the end. Perfect. That's good. Changes over toe wind select the Siri's. That's exactly what we want to see. So I know that was kind of a whirlwind, really quick coverage of some pretty complicated formulas, but at the very least, I hope this got your gears turning, and I hope this got you excited about the potential to build some really clever, really advanced things right here within Excel 13. Interactive Form Controls: I want to share with you guys a pro tip that I absolutely love. And it's using tools called form controls to create interactive user controlled visuals and excel. Now, these form controls, which you can find in your developer tab, are used to create interactivity, using things like lists, buttons, check boxes, scroll bars and other similar types of objects. Now, in this case, we're gonna insert something called a list box, which looks something like this, and we're gonna format that. Control that list box to include a unique list of 10 different wine types based on an input range. Bordeaux Cabernet chardonnay, merlot, etcetera. And we'll also link this control to a cell. And this is very simple. It's gonna be a single cell that displays in number from 1 to 10 for this particular demo, based on whichever item a user has selected out of that list. So select the top item Bordeaux and that linked cell would display it. One. Select the bottom item, the list and that linked cell will display a 10. Now there are a number of different ways that you could wire something like this up to a chart to make it dynamic. In this case, you'll see that we have a list or a range of cells or values that are based on conditional stats functions like average ifs, functions. All we're going to do is work on modifying those functions and integrating something like a V. Look up in order to not just average out those rating and price values by country like you see here, but also based on the wine type selected. So what will end up with is something like this, where user can dynamically select all different values from that list box and watch the chart update in real time. So one thing to note here, If you don't see a developer tab, you can activate that tab from your file options menu under the customized ribbon section. Now, a couple of use cases here for one, integrating user controls into dynamic reports or dashboards, or to consolidating multiple visuals together into a single chart template. Really nice way to compress a ton of information into a relatively small amount of real estate. So, without further ado, let's jump into our pro tip workbook, add one of these form controls and create some interactive charts All right, So if you'd like to follow along, go ahead and open up Your pro tip workbook were in the yellow Visualization Tips section and look for the adding form controls. Demo five Star Expert level Tip Go and press link. And here we've got a bunch of data about wine. Got wine Varieties column A 10 different unique varieties. Got countries and column B and then rating point rating column. See an average price per bottle here in column D, and you'll notice that we've already inserted a clustered column chart, which is simply referencing this range of values here and rose three through 10. And if we drill into those values, what we're using is a conditional stats function. An average ifs function specifically to aggregate the average rating value from column C under one condition, which is that the country and column B must equal the country or match the country in each of these rows. Australia, Chile, France, Italy and so on. Same thing with price, except we're just averaging those values in column D instead of C. So that's great, but we can do better. We can add a layer of interactivity here and what I'd like to do is integrate this wine variety as well. So, in other words, were Onley filtering based on countries. Right now, I'd like to modify these formulas to also consider the wine variety based on some sort of user selected value. Now there are number of ways we could do this. We could add it, dropped down data validation, sell and use that to modify these formulas, we could adding named range. Or we could treat this as an opportunity to play with some of these awesome form controls in our developer town. So I say we go that route Now quick pause. If you don't see the developer, town had to file options all the way in the bottom. Navigate to the customized ribbon town and you'll see this little window here. And you may notice that there's a blank box unchecked next to developer. So if that's the case, go ahead, pop a little check in their press, okay? And you should instantly see that tab become available right here in your ribbon. So look for the little tool box icon that says insert. And this case, we're gonna grab a form control called a list box right here in the top row, and once we click, we'll get these ah kind of cross hair cursor icon here. We can just click and drag out that box right next to a chart here and right now it's blank , Which makes sense because we haven't given this box any input or any information. But since we eventually want this to show a unique list of wine varieties, then we know that we need to create a unique list somewhere in this worksheet. So great opportunity to use either the remove duplicates tool or the advanced filter, which is actually my preference. So let's go ahead and select a one control shift Arab down to grab all the values, including duplicates from Column A. Now check this out. We're gonna head to data advanced, and we don't want to filter this list in place because it's part of a larger range, a copy to a new location, and let's go ahead and copy it to sell K two right here. And here's the catch. We want to check this box that says unique records only, and now we press OK, there we go. We got our unique list of 10 different wine varieties from Bordeaux to Sarah. That looks great as is. It's just kind of expanded out of it. Okay, now let's go ahead and give our form box our list box. A little bit of information. We're gonna right click that box right on the edge, choose format control, and we need to determine two pieces of information the input range, which is what list we want the box to display. And that's that unique list of wines that we just created. So we're good to go there, and the second piece is the cell link. This is the cell that's gonna store given value based on what item happens to be selected. So let's point to sell J two. That should be just fine and press OK here and check it out. It's populated the list of lines and watch what happens when I select Bordeaux C J to just populated number one. Cab SAB number two chardonnay number three all the way to Sara number 10. And these list boxes, just like any other object, can be shaped and resized, so it's helpful to hold control and click in order to edit and I'm just gonna resize cause I don't like that all that extra space And there we go. So looks good now. So we're almost there. We created the form control. We fed it. A unique list of inputs linked it to a cell. Final step is to modify our chart source formulas to essentially react to this value or react to these selected values from the list. Now, how do we transform or convert a number from 1 to 10 into a specific wine variety? That's the catch, because we need to point this formula to a specific wine variety that it can use as a condition or filter to drill down to Onley the specific rows that match that variety here in column A. So one way to do this is to simply create a Siris of values right here to the left of our wine varieties. I'm going to use this auto fill, fill Siri's 1 to 10 and now what do we have here? Essentially, we have a look up table that will allow us to look up the currently selected value one through 10 within this range and pulling these second column over just the name of our wine variety. And if we can use that name as a condition or filter within our average its function, then we'll be good to go. So let's give that a shot. You start with Cell G three and remember, average ifs allows us to integrate multiple conditions here. In this case, we're only dealing with one, which is the country's column B. So I'm going to do is add a comma at the end of the formula to start a new criteria range. And in this case, our criteria range is now column A which contains all of those wine varieties press F four toe lock that in in the criteria itself. Which wine do we want to filter down to Has to depend on J two, which is linked to our check box or a list box. So what we're gonna do here is say be, look up that selected value J two lock it in cause it's always in j two. We're gonna find that value in this range of cells J three through K 12. Lock that in as well. And our column index is to because we want to pull in the second value or the value from these second column within that table, right? And we're gonna do an exact match here, the number zero close off that be like function, and that should do the trick. So if we press enter here now, all of a sudden it's changed to 91.43 So that's a good sign. Let's go ahead and drag that down to apply it. And there you have it. We've integrated a second criteria into our average. If statement to tie it to our list box selection. Now the only thing left to do is apply the same logic to our average price column. And rather than going through all those steps again, drag this one over, I'm going to change the reference. Instead of averaging from column C Price is averaging from column D. All of my other references were set properly, so I don't have to change anything else. There just press OK. We want to format it as a price we could or currency. There we go and simply drag it down to apply it to all Rose and we're in business. So let's test this out now. Look at Bordeaux's versus Cabernets versus chardonnays, and we're starting to see these different patterns and trends emerge as we click through these different wine varieties and filter our source data. So it turns out chardonnays are really expensive in France and looking at merlots Wow, merlots air very, very expensive in Italy and continuing through, you know, roses air pretty cheap across the board. So really great way to integrate tons of information and pack a bunch of insight into one single chart template using something like a form control.