Transcripts
1. Intro: Setting Expectations: all right, so by now you're probably master of just about every chart type that Excel 2016 has to offer. So let's go ahead and turn things up a notch. In this next section, we're gonna dive into some really unique, really advanced, next level date of his exercises. But before we do, let's just take one minute to set some expectations. Number one advanced charts and graphs often require the use of advanced formulas and functions. At the end of the day, that's what makes them advanced. So I know this isn't a formulas and functions. Course you came here to learn date of is. But the fact of the matter is, formulas and functions are critical components to enable some really, really cool functionality with advanced charts and graphs. So during the demos, I'll talk about the formulas. I'll give you a quick summaries of the formulas, but by no means will it be a comprehensive review of formulas and functions. Second, some of these demos air pretty easy. You might find some really hard. Some might make you want to curl up and cry. Now, that's okay. All I ask is that you proceed with an open mind and trust me, you'll emerge. A date of his wizard last but not least, Number three. If you do want to step up your formula game, learn more about some of these functions that we cover here. Some ifs. Index match offset Count Day array functions, etcetera. Check out my Excel Analytics, advanced formulas and functions Course and yeah, yeah, I know I'm biased as the author of the course, but trust me, it's a really nice, really comprehensive review of basically all of the formulas and functions that you might encounter in Excel. So with that, let's go ahead and get started.
2. Custom Image Overlay Charts: all right, so let's kick things off with something that's a little on the easier side, but still pretty unique and creative nonetheless. And that's custom image overlay charts. So in the data visualization exercises workbook head on over to that first green tab, the image overlay charts tab, and we'll see what we're working with. And as you can see up here, we've got just a few data points to work with. This is basic fundraising results. We have a user entered value for dollars raised and sell A to got a goal of $10,000 the dollars raised and percent to go in column C and D, both of which are calculated based on A and B. And the idea here will be to create something that's a little bit more unique and a little bit more creative. Then your standard 100% stacked column. And to do that, we're gonna integrate our own custom graphics. So I've built out two examples here and inserted them into the worksheet, got a thermometer and a thumbs up icon, and you can use anything you choose. Just keep in mind that no matter what type of graphic you're using. Make sure that it has some solid, opaque background and some transparent area where you want the chart to shine through from behind. So as you can see if I right click and bring my thumbs up icon to the front. Same case here. It's got a solid, opaque white background, with the transparent area just within the outline of the thumbs up itself. So let's go and get started and actually insert a stacked column. I'm going to select a one and a two control Select C one and C two that gives him at dollars raised and dollars remaining. How insert 100% stacked swap their own column that I have both my Siri's stacked together on the same column, and now we do a little formatting so I don't need the chart title and don't actually need the X axis. This orange boxes, my dollars remaining box. This I actually don't want with any sort of solid fill so I can choose No, Phil, no line for my dollars raised. Siri's Let's give it a solid fill with a nice, dark red kind of classic, their mom and her color, and at this point, what's important is that it drag it over and a line zero and 100% marks with the thermometer icon. So I'll just do that manually. That looks like a pretty good alignment. And then once I've done that, actually don't even need this. Why access anymore? You can choose to keep the grid lines or lose them if you want. In this case, I'll just keep them. Last thing I want to do here. Just right Click Format. The chart area. I don't want any area Phil or chart border. And now, finally, if I just my data, Siri's can reduce the gap with just to make sure that the Siri's is as wide as the entire chart area. So I've boiled this down really into just a single rectangle that grows based on the value and sell eight to, so I'll shrink it down to what I need, and then just shift and drag over right click. Send it back, and so now it's basically behind are obscured by my thermometer graphic. The Onley area that shines through is the piece of my graphic that's transparent, so I'm almost there. The last thing I need to do is just fill in this little bulb here, the bottom, which is actually below 0%. So it's not technically part of our graph. I'm just going to insert shape, grab the oval tool. If I hold shift while I'm dragging this out, I can adjust the size. I'm gonna overlay it right on the bulb there. Let's give it that same red Phil and no line and we'll right click and send this to the back as well. And there you have it. Now, as the user changes its value in a two, let's say now they've raised 5800 are stacked area climbs, and it makes it appear as though this thermometer is increasing in temperature or growing up to that 100% goal. So here we go. We've got 8123 50 super simple, but kind of a nice creative effect that most people wouldn't think to do in excel. And now what I can do is just It's kind of tough to access this. I'm gonna drag my overlay out of the way just so I can select my chart press control. See to copy it, just gonna make a duplicate version of it. And then I can drag my chart right back over, and I'm going to use this one as my source for the thumbs up, since it uses the exact same data. Um, why don't we just change the color of the Phil to a light green? And in this case, let's get river grid lines and we'll spread it out a little bit. And basically, what I want to do here is re scale it a bit again so that 100% hits just the top of a thumb . And 0% is right here at the bottom so I can test it out just by typing in 10,000. Just that it's clear exactly where my top is. Looks like that will get me right there. Bottom, as you can see, is aligned, so that looks good. Now it's just a matter of stretching this out to fill the full shape. Right click send to back There you have it. So let's try plugging in some values here in Sal A. To how about 2500? There you go. It's about 1/4 filled 5000 obviously gets this halfway. 66 82. We'll get us a little further, and there you go to different examples of putting your own creative spin on a relatively simple stacked column chart to create a custom image overlay.
3. Adding Binary Values to Highlight Date Ranges: for the next demo. I want to talk about adding binary values to highlight date ranges within a chart. So head to the second tab that binary date ranges tab and here you'll see. We just have to columns of data. We're looking at daily revenue trying it out, and what we want to showcase here is a circumstance where you might have dates or ranges of dates that are meaningful in some way, dates that you want to draw attention to or highlight within a chart. In this case, in column A. You'll see some values or some dates formatted with a light green Phil. And let's just say these dates represent sale or promo periods for this particular example . Realistically, that could be anything. Maybe you just want to showcase weekends in your data. Or maybe you want to highlight every Wednesday or Monday. Whatever it is, the point is that you're trying to highlight certain dates or date ranges in your chart, so we'll kick it off by selecting A and B and just inserting a basic line chart, drag it up and spread it out just a little bit. And why don't we go ahead and format that ugly X axis. Give it a major unit of four simply to make that a bit more readable. So there you go. We've got trended revenue shown in the form of a line chart. And now, when it comes time to actually featuring those date ranges within this chart, what I'm gonna do is add a new column in C called promo, and it will have a default value of zero. So I could just double click to apply that zero all the way down. And now all I need to do is replace the zero with a one for any dates that fall within a promo period. So 18 through 20 for instance, I can copy that one. Apply it down here to rose 29 through 34 51 3 62 67 3 69 and finally 81 through 85. What I'm left with is a binary Siri's and column see that takes either a value of zero if the date does not fall within a promo period or one. If the date does so, at this point, it's just a matter of getting this binary. Siri's into this chart in a way that makes some sort of sense so simple enough. I can right click select data. Add a second, Siri's Let's just call It promo and for serious values all select Cell C to control Shift Arrow Down to take me all the way down to the edge of this array, which is seen 91 Press. Okay twice. Scroll back up, And as you can see, we're not quite there. All it's done is add a second line on the same axis, which either takes a value of zero or one, and it's just completely drowned out by the revenue totals. So clearly we need to use a combo chart. In this case, I'll head up to chart tools, change my chart type select combo here at the bottom of the list. And as you can see, Excel tried to get fancy and change the chart type on me. In this case, I don't want revenue to be clustered. COLUMN Want to go back to the original line chart that I had selected earlier and for the promo period, which is my binary Siri's, this one? I want to be 100% stack column on the secondary axis. I do that. You can see we're starting to get a little bit closer here. So what I want to do is select that Siri's format it and I want to give it a very nice, light, solid Phil, because I want to draw attention of those dates. But I don't want to drown out everything else, and I don't want to add too much noise to this visualization. So if I go down to Siri's options, last thing I'll do with this Siri's is just eliminate the gap with and with that essentially does is it creates these columns of data, the width of which represent all of the dates that fall within a particular promo period. But you might notice that we still have the secondary access here, which at this point is kind of meaningless. That really is nothing that falls along this year to 100 scale. But the problem is, if I delete it, it deletes the Siri's with it, so controls you to undo that, and we'll just use a little shortcut going to the home menu, and we can do the white Phil here. Another option, if we undo that, is to use the conditional formatting trick that we used during one of the earlier lectures format that access go down to the number options, and we're going to use a custom category, and the code will be three consecutive semi colons that essentially makes the text of the Axis completely invisible. So at this point, it's really just whatever formatting you'd like in this case, I'll make it. A solid dark blue line will smooth it out just because I like smooth lines. And then, obviously you can add axis labels, chart titles, whatever your heart desires. But there you go. Pretty cool. Pretty interesting way to feature or highlight specific date ranges within a chart in Excel .
4. Automation with OFFSET, COUNTA & Named Ranges: The next demo is one of the most important exercises in the entire course, and it's about automating charts using Offset, Count A and Named Ranges. Now the reason this lecture is so important is because functions like Offset and Count Day are the basic building blocks to creating truly dynamic charts and graphs. Basically, they allow you to create fluid, dynamic charts, source data rather than traditional fixed arrays, which really lets you bring your visualizations toe life and integrate some much more advanced functionality. So head to the automatic chart updates tab. And as you can see here, we've got a simple data set. Two columns, date and temperature. And let's say my job is to update a trending temperature chart with every day that I collect new data. What you could do is select cell a one B 24 and insert a line chart just like normal. And that's totally fine. The only challenges that, as you add more days worth of data and manually enter new temperatures chart doesn't know to update because, as you can see, its source data is fixed through Road 24 so it doesn't see any new data points that are added beneath that one option and Excel is to convert or format this data range as a table and then tab down to add new roast that table. But I'm gonna show you an approach that's a little more flexible and a lot more powerful, and that's using those offset and count a functions in the form of named Ranges. So what we need to do here is head up to our formulas tab and click the define name button . We're gonna do this twice first for X values, which are dates and second for why Values Which air temperatures. Let's start with dates. So actually, just name it dates weaken scope this named array to a particular tab or to the workbook as a whole. That's fine for now. And here's where we would typically see a fixed array of cells serving as the source data for our chart. What we want to do is delete that and writing offset function, and what offset does too quickly summarize is it tells Excel. I want you to start at this particular cell that you define wanting to move down a certain number of rows left to right. A certain number of columns and return an array of cells with a particular height and with defined by the number of rows in the number of columns. And the beautiful thing is that you can nest functions within their so that instead of hard coding the specific dimensions of the resulting array, you can make it fluid and dynamic. That's exactly what we're gonna do, using the count a function. So with Offset. It's a little bit tricky to see, because when you're typing functions here in the name Manager, Excel doesn't help you along step by step. So watch carefully. Luckily, have written a few of these, so I know the steps, but basically we're gonna open the parenthesis, and the first piece of our offset function is the starting. So so for our dates array, the starting cell will be the first date in the array, which is sell a to, and then, as we comma over will move to each component of the function. The next piece of the offset function says, Do you want to move down or up any rose from that starting point? This case no want to keep that a zero. That's our starting point and always will be the next comment says, Do you want to move left or right from that starting point again? The answer is no. Which brings us to the next component, which is the height of our resulting array. And this is where we want Count A. Because, well, we could say OK, we want an array with a height of 25 rows and hard coded 25 in there. Then, when we add new data, it's still going to read the 1st 25 rows and serve the same purpose as a traditional fixed array, which really doesn't get us anywhere. So instead of hard coding a number there, we're going to use the count, a function which basically counts all of the cells within a given range that contains some value. So any non blank cells and the range that we're gonna look in in this case open the parenthesis and select all of column A and close the parenthesis. So now the count a function is saying, Look at the entire column. A. Tell me how many cells within column may contain something, So in this case, the Count Day function would look at the entire column A and count 24 cells that contain data, which is almost what we want except we don't want to count the header. So you just need to subtract one here and comma over to the final component of the function , which is what's the width of the resulting array. In terms of the number of columns, this case can enter one because our X values are all contained within a one dimensional array that's one column wide so I can close that parenthesis out that wraps up our offset and count day function. Compress. Okay. And now we're gonna do the exact same thing for temperatures and column B. So to find name, call it temperatures workbook scope is fine. And again, we're gonna type of offset function here. This case, our starting point. It's Selby, too. We don't want to move any rose. We don't want to move any columns from that starting point. The height of our resulting array is Count A this case of column B minus one for the header comma One column wide close the parenthesis Boom. There you go. So now we have named a raise to define in a much more dynamic way. How many values we need to plot from columns A R X values and columns B R Y. Values. So the final step is just adjusting our chart source by selecting the data and starting with temperature, which is our why values. We can edit that Siri's, as you can see here Serious is currently fixed at cells B two through B 24. So that range is never gonna change. And it's never going to see any new data points that get added beneath it. All I need to do is delete everything after that exclamation point and type temperatures, which is the named Array that we just created for us. Okay, same exact thing for horizontal X axis. It's fixed at a two through a 24. I'm gonna delete everything after the exclamation point and type dates. Press OK and want to do that. You won't see anything change initially, but as you add more data and type in more temperatures, you can see now that our chart is automatically updating with those new data points. If you select the line and select the Siri's, you'll now see that the Siri's reflects all of the data through Road 27 now. So there we have a quick primer and using offset and count day functions within named ranges to create automatically updating charts with fluid, dynamic source data.
5. Adding Scroll & Zoom Functionality with Form Control: This next demo is one of my favorites. It's a really cool one. It's something that a lot of people don't realize that you could even do in Excel. It's all about building charts that can dynamically scroll and zoom. To do that, we're gonna use the offset function. But instead of combining it with Count Day like we've done in the past, this time we're gonna insert something called form controls, which will allow users to actually click and drag and manipulate scroll bars in the workbook to dynamically change the view that they're seeing within the chart. So it's a really cool combination of tools to create a really interesting effect. So in this scroll and zoom charts tab, first things first, make sure that you have the developer tab in your ribbon. If not, it's super easy to add. Just head to the file option goingto options at the bottom customized ribbon and here, on the right side of the pain, you should see a check box next to the developer line. If that's unchecked, go ahead and give it a check and press OK and you should see it pop up right there and now the developer Tab is where you can go to edit visual basic or VB a code to write or edit macros. And it also includes the form controls that we're gonna be using for this particular demo. So in the insert option here, go ahead and select the scroll bar form control. And these are really cool. You can actually drag them out into the exact shape that you want. So something like this will work great. And with its still selected, I'm gonna hit control C select an empty cell control V to paste it. And now I have to scroll bars one to divine scroll and one to define Zoom. Now, to actually customize these and make sure that they're doing what we need them to do, you can right click go into your format control options. So in this case, we're looking at the scroll bar, which defines the window of data that a user is looking at either shifted up or down within this array here. So the minimum value here will be zero and the maximum value will be 11. Because if they're starting at January as a default scrolling 11 months down, we'll get you right to the bottom of this array in row 14. I don't need to change the incremental or page change options. What's important is this cell link here. So if I select the selector, I'm gonna link this form control to sell d 16 when I press OK and d select it. Now, as I manipulate this, you can see the value and sell D 16 updating accordingly. And we could just confirm we've got a maximum of 11 and a minimum of zero, just like we set so same deal with zoom format control. In this case, zoom is defined by the number of rows of viewer is looking at at any given time, and that number of rows can grow or shrink. But the bottom line is that you have to be looking at at least one data point or at least one row of data, so we'll give it a minimum of one and a maximum of 12 which is the height of our raw data. Ray here and in this case, my cell link will be so d 17 I'll press OK and confirmed that my minimum is one. My maximum is 12 so let's go ahead and just set these somewhere in the middle. And I want to kick things off by inserting a chart just so that I could tweak and polish and finalize formatting before taking the steps to actually make it dynamic. So in this case, I'm gonna control Click Columns B and C along with column E. Because I want to create a combo chart where impressions are my columns, click. The rate is my line. Feel free to use any combination of these three metrics or follow along with me totally up to you so my insert options go into columns. More column charts Combo the road. Click the rate as a line on my secondary axis and now again, feel free to follow along with these formatting adjustments that I'm making or format it however you choose. You know, this really isn't the important part of the lecture. I'm just gonna format the plot area, get rid of my fill and border, make some changes to my line. Siri's. I could solid, dark grand with the width of two. Smooth it out and add a quick, preset shadow than 80% transparency and then with my columns, give these a light green Phil and adjust the gap with down. And finally, I'll just add a chart element with some secondary minor horizontal grid lines. So there you go. I'm happy with that formatting again. Customize it as you see fit. Now it's time to actually make this thing dynamic because right now it feeds from these traditional fixed data arrays, which will need to update toe link to her scroll and zoom values. So to do that, I'm gonna go into formulas, define a name, and let's start with our X values. So we'll call this Scroll X, and instead of this fixed cell reference here, we're gonna type in offset function. My ex values are my months. So my starting point is my first month, which lives in cell B three and the number of rows that I want to shift That starting point is gonna be a function of the scroll index, which lives in cell D 16. Because remember, as a user shifts that scroll bar left and right there essentially moving the starting point of that data window that they're looking at up or down. The next piece is how many columns over I want to shift that starting point type zero cause I don't want to shift the starting point at all. My months always live in column B. The second to last piece, remember, is the height which in this case is defined by the zoom Index. Since users will be shifting that zoom bar and essentially growing or shrinking, the window that they're looking at finally with in terms of the number of columns is again just one so enclosed that parenthesis press okay and our scroll X values are defined. Now I'm gonna cruise through that same process for each of our Y values, starting with scroll, why or squirrely And again we'll just type offset here starting point for our first Y values which are impressions lives in C three Gonna shift it down by the value in d 16. Don't want to move any columns. The height is the zoom index and D 17. That width is one close the parenthesis press OK, and one more time for scroll wide. To offset this case. My first click, the rate value Celie three number of rows. I'm shifting his d 16 number of columns zero height is D 17 with is one so same process essentially repeated three times Now the last step is to actually integrate those new named Fields that we created into our chart so I can right click select data. And in each of the Siri's options, I'm gonna click edit and rather than the fixed array from C 33 c 14. This is my Impressions column, which is my scroll. Why column So I can just type the name of that new named Array right there. Squirrel. Why and press OK and repeat the process for click through rate, delete everything after the exclamation point and just type scroll. Why, too, press OK, and now one really, really important note is that I need to edit my X axis as well, so I'll go into edit, delete everything after the exclamation point, this one's scroll X press. OK, but I'm not done yet. Even though it looks like there's just one value that needs to be adjusted for my X axis, I need to change it twice once with impression selected and again with Click the rate selected. It's a really, really easy thing to miss. But now, as you can see, if selected click the rate. If I go back into my edit options, you see that fixed reference right there again. So I need to enter Scroll X twice and now the X axis associated with Click the Rate and the X axis associated with impressions, which, realistically, is the same. X axis is reflecting the new named Array in all instances, so I can press. Okay, so let's see if that actually worked. Try testing. Our scroll seems to work great. All the way on the left means we're starting at January. Right now, we're looking at a seven month window, which makes sense. Since we have a zoom index of seven. We want of you additional months, I can scroll to the right. Now we're looking at the full window, or you can drill down to just two or three or four months at a time. And there you go. How cool is that? It's a really unique, really interactive chart that you can build with pretty simple form controls and named a race in itself. But there's one issue here that we need to address, which is that if we zoom all the way out, we actually have no guard rails in place to keep this array within the bounds of our original source data. So as you can see scrolling right past all of the data into an array that just no longer makes any sense and nothing's incorrect about this, it's still plotting the right data. We just need to find a way to again put some guard rails in place to prevent this from happening. So if you play with this a bit, you'll notice that any combination of scroll and zoom in next values that are equal to or less than 12 produce a valid view. So in other words, if I'm zoomed all the way out to 12 months and my scroll index is set to zero, meaning I'm starting in January, this view is legit. But as soon as I try to scroll beyond December, now I'm in trouble. Same case. If I zoom down to eight months and scroll past a value of four, you can see him into that danger zone once again. So all I need to do here is right, a formula to define the maximum value that I want the scroll index to take, and so it could do that, using a conditional if statement here. And I'll say, if my zoom Index plus my scroll index is less than or equal to 12 then just returned, whatever the squirrel index would have been, because we know that if the combination of those indices is less than 12 then we have a valid view, so I can go ahead and just mirror the scroll index in that case, otherwise the value if false, if the total is greater than 12 that can prevent the view from exceeding the bounds just by taking 12 minus the zoom in next value I hit enter. You can see that any time the total exceeds 12. My Max scroll fixes its value any time. The total is under 12 or equal to 12 Micmac Scroll mirrors the original scroll index. So to implement this change and make sure the changes reflected in our chart, I can go back to my formulas tab, head into the name manager and adjust those three named ranges that I created, starting with Scroll X and compress at it. All I need to do here is change my reference to D 16 which is my original scroll index to D 18 which is my max scroll. So I could just grab the six changes to in 18 and press OK, do the same thing with scroll. Why change the 16? Two in 18 and scroll Lie to 16 two in 18. And now if we scroll to the right 2 October, November, December. Now, as you can see, it doesn't allow me to scroll any further. So essentially, what that means is that if I'm zoomed all the way out to the max view of 12 now, the scroll bar is essentially useless because it's not allowing me to scroll outside of the realm or the bounds of my original. All right, so there you have it using form controls and offset functions to create dynamic scrolling and zooming charts in Excel.
6. Animating Changes Over Time: So the other day I was online and it came across very cool. Example of date of is that I want to share with you guys found it on the Pew Research Center site, which is an organization that captures a lot of US politics and policy related data. And basically they had this really cool piece about political polarization, and they built out this animated visual that I thought was just the coolest thing ever. It's a really great example of using overlapping area charts and animating them over time to tell a really clear story. And what we're looking at here is basically Democrat and Republican viewpoints in terms of being consistently liberal versus consistently conservative. And essentially, what this area chart is doing is it's almost acting like a dual hissed a gram, where you're seeing the distribution of two different data sets changing over time as well as the overlap between them. And so they've got five different years in the sample, and as you page through, you can see how, over time these viewpoints have shifted and eventually, as you get to 2014 you can see this very clear division or polarisation taking place so naturally I saw this and said, I just have to replicate this thing and excel because it's It's so clean. It's so cool It tells such a powerful story. So I thought this would be a great example to use as a demo to walk you guys through from start to finish. So let's head into excel and we're gonna be on the animating changes over time Tab. Basically, what I did was re create a new raw data set, and we've got the same five years, 94 through 2014. And for each year, we've got 21 different data points arose, which shows that score from liberal all the way down to conservative, where the middle represents mixed viewpoints. And then we have the number of Democrats and the number of Republicans who align with each of those scores in each of these years. So what we'll do is start by just creating an area chart for one given year. Just that we can get the formatting right and start to get our bearings. So I'm gonna select be won through d 22. I don't need the actual year data point in here, so I'm gonna insert within the line, options a to D area and do a little bit of formatting here. So let's make it big, because it's really cool. Let's get rid of the chart title here, and I'm gonna form at the chart area. I just don't really want a border around it. And then for Democrats, Republicans in the legend, I want to make that a lot bigger, bold and just spread it out a little bit. So next I want to change the actual colors and transparency of the two data. Siri's within here, and I'd like to align them with the appropriate colors. So for my Democrats, Siri's I'm going to give it a dark blue solid Phil. And let's call it, let's call it 30% transparency. And for my Republican, Siri's going to give it a dark red Phil with that same 30% transparency. There we go. Now we've got a Blue Democrats. Siri's a red Republican, Siri's. It's very clear where the overlap lies, and that's a really nice example of a date of is if you were looking at a snapshot in time from 1994 so already we've done a pretty good job visualizing this data set at hand and in this case actually don't really need the y axis labels because it doesn't really add much to see the Count's. We're really just interested in the relative differences between the two data sets. So that's step one building one example. Now, the key is to actually animate this thing over time. We've got five years in the data set. I want to see how things have changed over the course of that time span. So I'm gonna do is just type select year and so f five make it bold and italic. Give it a right align and then here and sell g five. I'm going to use a little bit of data validation. And what I want to do is create a cell where users can drop down and select any of those five years in the sample just like we saw in the site. So to do that, I need a source list for this drop down, and so s one. I'm just gonna enter the years. It's 94 99 you know, I guess I'll do it The legitimate way could select all of my years here. Even with duplicates, I could pace them here and then in data just hit the remove duplicates button. And there you go. So that leaves me with my five unique years in the data set and now back to G five. I'm going to use a data validation option, which is in my data tab. Data validation. And what I want to allow in this cell is a list of values and the source list of those values lives right here from s one. The rest five press. OK, there you go. Got my list. Let's make it bold and centered and give it a little bit of Phil just so that users No. Okay, this is something that I can select. So now we've got our drop down, but it's not linked to anything. All you're doing is just changing the value and G five itself. So what we need to do is create a second data set, which is gonna be the new source data for this chart and have that data set be dynamic based on the year that a user has selected. So I'm gonna start by grabbing the headers and pasting them down here below the chart. This will be my new dynamic source data set, and I can grab all of the scores, since those will be the same and paste them right here and now, the year it's going to equal. Whatever the user has entered in G five, I'm gonna press F four to fix that reference. If you want to learn more about reference types, definitely recommend checking out My Excel Analytics, Advanced formulas and functions. Course there's a whole section on reference types, but so basically, that allows me to drag this down and have the reference remained fixed. A G five in every room. So now if I change the 94 to 99 my 94 changes to a 99 here is well in my source data. Now the key is to actually populate the values for Democrats and Republicans because that's what's really gonna drive the area chart above. So there are a few ways to do that. A few different functions that you can use it could use index match. In this case, I'm going to use a some its function. So basically what some ifs does is it sums the values in a particular column, given certain criteria. So in this case, I want to some the values in the Democrats column and column C and fix that where the criteria range. Call him a is equal to the year and sell G 33 I can fix just the G piece of that. And the second criteria is where the score and column B is equal to. The score just narrowed over because it's tough to see the score in h 33. I'll fix the H there and let 33 shift to 34 35 36 as I apply this formula down. So I know that was a very quick run through of the summits function again. I don't want to take too much time digging into formulas in this course, but there's a whole section on statistical functions in my advanced formulas and functions course. So definitely check that out. Try to replicate exactly what I've written here, and when I press OK and drag that down, it populates on my Democrat values, and we can do a quick Q A. This is 1999 data, which has 9 2023 38 9 2023 38 looks good. And now I can copy and paste that formula in the Republicans column, and the only thing I need to change is the column that were summing values from So it's no longer column C. It's now call him D, which is where my Republican data lives. And same case. I could just drag that down and do a quick Q A. So 234 16 19 234 16 19 looks good. And now again, I'll change this 2004 year changes. And because these air now coated in as some ifs functions, the values for Democrats and Republicans change as well. So we're almost there. All we need to do is now tie the source data, which we had originally set just for 1994 down to this dynamic array that we've created. And here's a really, really great tip that I actually didn't learn until just recently, and it's been such a lifesaver, which is that I think starting in Excel 2013 or maybe 2010 they change things so that if you drag the source data and just try to move it for a given chart, so you know, it reformatted things. I'll do the same thing with Republican data set here. Let's drag it down. See, it just defaulted back to the blue and orange, in which case I would have to go through and say, All right, I want to fill it with red again and give it that 30% transparency. There's actually a work around to prevent this from happening, and so I'm gonna undo both of those steps. I just took head to the file option going to options down here. If you go into Advanced and Scroll all the way down to chart this last check box that says properties follow chart data point for current workbook. Just uncheck that and press OK, I have no idea why the default is to totally strip your formatting when you move a source data Ray. That doesn't make much sense to me, but it is what it is. And luckily, once you know that little trick, it's an easy fix. So now, instead of doing reformatting, I can just grab my source array and shifted into place for both my Siri's. So there's Democrats and see how it stays that blue and here's my red Republican seriously can drag it right there. I can also change the headers, but realistically, it's the same header in each case, so it doesn't really matter. But let's go ahead and do that. So now our sources linked to our dynamic array and what that means is that boom. You can change the drop down, select a new year and because their source status changing the graph itself is changing as well. So you can see that really cool distribution happening now. So if you want to stop there, that's totally fine. I think that's awesome. I'm gonna show you one other kind of bonus step just to make it a little bit easier to page through the animations instead of having to click and select from a list each time. And to do that, we're going to use a different kind of form control from our developer Tab, and I'm gonna insert what's called a spin button, and basically that's just an upper down button that changes the value in a given. So so follow along here. I'm going to do this quickly and form at the control, and I'm going to give it a minimum value of one maximum value of five, and I'm gonna link it to sell our one and press OK? And what I could do. Here's go back into my data tab with G five selected, and I actually want to remove the data validation list settings that I had originally done . So I'm gonna change this toe any value, and that basically resets this cell. So now, as you can see, this spin button changes the value in our one from 1 to 5. And actually, what I'll do is want to cut that and paste it in Q instead of r. And I'll show you why in just a second it's still linked to the spinner. And now, and call him are it is type 12345 And essentially, that's assigning an index number to each of the years that I have available. So an index of one corresponds with 94 to corresponds with 99 so on and so forth. So what that allows me to do in cell G five is to replace this year value with the look up function, and I'm gonna say, look up the value that my spinner is currently producing within this little array from our one through us. Five. Fix that entire ray and then the column indexes to because I want to pull the value to the second column over from my index and my range. Look up zero, cause I want to find the exact value and press enter. So again, kind of like the summits. That was a very quick crash course in v lookups, but essentially, the look up function allows you to search for a particular value. This case were searching for an index number from 1 to 5 and return the data from a corresponding column. So we found the next number. We returned the year value from the next column over. That's what that column index of two means. So what that allows me to do is tie the year value to my little spinner here and make it just a little bit more user friendly to page through the five years in my sample. So there we have it. This is an excellent way to visualize trends over time, using animated overlapping area charts
7. Building a Dynamic Dashboard Part 1: the next two demos air all about building dynamic dashboards. And that's a really hot topic in Excel because it requires you to tie together a number of different skills, you have to use formulas and functions to dynamically pull in the right pieces of your data . You have to use charts and graphs to visualize your data in a meaningful way, and you have to combine all that to essentially transform a raw data set into a clean and interactive user interface. So in the dynamic dashboard tab built out a simple framework for dashboard that we're going to start populating And if you check this little plus box, appear above column que that will actually reveal the raw data, which at this point lives in columns a through P. And just if you're curious about how to set that up, you can just select any grouping of columns or rows. Then, in the data tab, there's this group option here, and that just consolidates them so you can hide them from view. In this case, I've got my raw data right here, side by side with my dashboard. But obviously you could put your raw data on a different tab. If you choose, that's totally up to you. So the data we're looking at here is baseball data by player. We've got year, team and league, followed by a number of offensive metrics, games at bats, runs, hits, doubles, triples and so on and so forth. And then here in the little framework that I've started to build out, I've got a data validation cell where user can select any player. If you want to see the source list for this, go into data data validation. And here's the source list AI to through a I 100 and, as you can see over here, have just hidden the column to keep it out of you. But here's my player list. I also have a list of unique metrics, which will be using later on. So let's just hide that again and start building this out. So to pull the data in, I'm going to use one of my favorite functions called some ifs. And what some ifs does is it pulls or sums data from a given column under a number of specific criteria. So in this cell, here s five. What I want to do is some data from my at bat column, which is right here, and call him F where two criteria are true. The first is that the player equals the name that a user has selected and the year equals whatever value corresponds in column are so to do that I'm gonna type equals some ifs and my some ranges My at bat column. So select the entire column F I'm gonna fix it by pressing the F four shortcut. And now the criteria Range number one. Let's use player Name is our first criteria. So my criteria ranges the name column and a fixed that one as well. And the criteria with the value in this column needs to equal is whatever's in cell s to this one will be fixed as well. Now, my second criteria range is gonna be my year column, which is B at four to fix it and the criteria that needs to be true Here. Notice how my, um, my formulas blocking cell are five years. Soto access are five. I could just arrow to the left, and that will populate our five into my formula. Now, instead of fixing the entire art five. I want to press F four to cycle through my different reference types, and I only want to fix the Are. And the reason I'm doing that is so that, as I drag this formula down, it will read from our six are seven R eight or nine and pulling data for the correct year. If I fixed the Are and the five, it would always read data from 2010 no matter where this formula lives. And the reason that fixed the column piece of that is so that I can drag this formula to the right and have it always read the years in column are instead of having that column shift toe s or tea or you, the point is referenced. Types fixed and relative are incredibly important, especially when you're replicating formulas in a number of places. And having a really firm understanding of reference types will help you work really, really efficiently. So this some its function is good to go in a press enter and then dragged this down. As you can see, it's populating at bat data for Albert Pools for each of these six years in the sample. And now, just as a quick shortcut because I set my reference types in an intelligent way. I could just copy those values, paste them here and now the only piece that I need to change is the sum range. So instead of F which was my at bat column Now I want some H, which is my hits column and the other criteria the player name and the year are gonna be true for all of these metrics. So that's the only thing I need to change this with some range press enter there, apply that down and I'm gonna repeat this process for all of my volumetrics. So runs, doubles, triples home runs, R B I's stolen bases, caught stealing walks In case you also see that I have three rate metrics in here batting average, stolen base percentage and strikeouts toe walks. Those are gonna be calculated using different, simpler functions. So right now, I'm just gonna go through and continue with my son ifs to get my volumetrics all in place so I could just paste this all the way through and then just one by one, change my column reference. So runs are in column G. So change the H two g doubles aren't calling. I so change the age to and I triples R and J H 20 J. And in this case, have you noticed that I have a number of metrics in the same order that doubles triples home runs, R B I stolen base caught stealing are all in the same order here. So a little shortcut bears to actually remove the fixed reference from my column and that will allow that j to shift to a k, l m and n. And as you can see, looking at the formula as it page to the right, you'll see that that's happening. And now walks are in o and strikeouts. Eso Samos k isn't p. So there you go. I've got all my volume metrics done. Drag all these down, drag him down, and I'll apply my rate metrics. These air just calculated fields, so averages just can equal hits over at bats. And one tip that's really useful here is because I'm building a dashboard where user might be able to select a player that doesn't have values in every single cell. Instead of allowing this calculated column to return an ugly N a a reference error. I'm just gonna wrap this function in an if error statement. So if error open parentheses T five divided by s five and comma gets you to this value if error piece and I just want to return a little dash surrounded by quotes. So now if I get to a player who doesn't have values for a given year, this will just show a little dash, which is a lot more polished than the traditional error function. So the same thing with stolen base rate, it's gonna equal stolen bases divided by open parenthesis, stolen bases plus caught stealing, which is total intense. And I could keep it just like that or same case I can wrap it right after the equal sign within if error open, Prentice's comma, dash and quotes. And there you have it and same exact deal strikeout toe walker issue. It's gonna be a e divided by a D, and I'll use the affair there as well. So there you go now for my totals could just use some functions problem. My volumes and I could just pace to these down and it will bring in my sons and then little tip here. No. Now the font size is bigger and bold in this row. If I want to apply this function without having to rewrite it here, I can hit control C and then use it all. Tip shortcut, Ault H v f And that just pastes the formula while maintaining the formatting of the cell you're pasting into. So nice little trick their control. See Ault, H B f and last but not least, all th VF. And there he goes. And I've got all of my raw data populated such that you can select any player here and all of your data will update accordingly. So notice how for stolen base percentage Billy Butler has a number of years where he didn't steal or attempt any stolen bases. Those cases, you just get that nice little dash, which is super clean instead of the air statement. So now that we've got a raw data, I want to start using some techniques to draw attention to fields that we care about. So let's say average R B I's and strikeout to walk ratio are kind of our key metrics here. You know, an easy thing to do would just be to make those bold to kind of draw more attention of those fields. Another thing that we can do is add some conditional formatting with a nice color scale here to draw attention to years where this player performed particularly well or poorly. And so you can do is something like this with a green toe white. What's nice is that can manage that rule. Select the rule I just created and edit it so that instead of going from green to white ago from green to this light blue, which matches the cells around it, that makes it just blend in nicely. Here, I can just select that array in the home tab. I can click the format painter button and just click C five, and that will apply the same formatting. Know that it changed the number as well. So just format the cells and change the number back to zero decimal points. And there you go now for strikeout to walk ratio. I want to do something very similar, but in this case, higher number is worse because it means you're striking out more often than you're walking . So I want the same conditional formatting just reversed so that the green numbers of the lower numbers so I could go back in to my color scales and I'll just do a white to Green, which is the opposite of green toe White and then same deal. Manage that. Edit the rule and we'll change the maximum to this light blue so that it blends in really nicely. So let's insert some combo charts. I'm gonna build them from scratch, starting with the column. Let's resize it down here. I will select the data where my first Siri's is gonna be hits. My serious values will be sells t five through t 10 and my ex values will be my years 2010 through 2015. Let's add a second Siris for batting average. My values will be you five there, you 10. My x axis will be the same six years in the sample press. OK, going to chart tools changed chart type two combo. And here I want my average to be a line on the secondary axis compress okay there. And now it just comes down to formatting. So let's add some elements gonna add primary vertical axis title, which is hits. I'm gonna add a secondary vertical axis title for average. I'm gonna shrink down the size my access labels a bit. Make my years bold and shrink those down. Now let's do some data. Serious formatting shrink the gap a little bit. What's Phillies with a light blue and let's format are lying to be smoothed dark blue two point with and then on the last point in the line. What's added data label And we conform at that label so that it's above the data point that has a background fill of dark blue. Now the text is white and bold. The last piece on this. We can just add a marker here, which you can access right here on marker options. Let's make it built in the diamond size seven White Phil and a solid dark blue line that's 1.5 points wide and last, but not least, just gonna for him at the chart area and get rid of the border. And there you have it. So nice little clean visual representation of hits and average plotted over time. And now, because my data is dynamic, that means my chart is dynamic as well. Now all we need to do is copy paste that and select different data. Siri's So in this case, I want to show at bats and r B I drag hits two at bats. I'll drag my line from average Teoh R B I. Now all we need to do is make a few tweaks to the formatting. So hits becomes at bats. Average becomes R B I. And let's tweak the formatting just a bit. We'll do a light green here with a dark green line, and we need to edit the marker. Linus. Well, so Marker options Border will be a dark green. And then same with the label Phil. Behind the label will be a dark green. So now we've built a second chart that again changes to show to new metrics at bats and R B I. And let's just repeat that process one more time to show stolen bases here in column A A and stolen base rate. Good use of the combo chart because we have two metrics that are on completely different scales and then you know the drill. Let's change the baby to an SB an r B i Teoh sp percent. We'll change the color to in this case, a light orange and the line to dark orange. We need to change your marker, and our label background of our marker would be a dark orange again, and we'll edit that marker with a dark orange solid line. Now you can play with alignment a little bit here, but that's your basic idea. So we've built really nice, really clean dashboard that shows all of this different data for any player you choose and does a really good job drawing attention to the key metrics and how they've tried it over time. So that's part one of building dynamic dashboards and excel.
8. Building a Dynamic Dashboard Part 2: Okay, so welcome to part two of our interactive dashboard demo. The goal of Part two is to essentially add some more interactivity to the dashboard that we built out in part one. So looking at our original dashboard, you'll see that we chose to visualize three different pairs of metrics. We're showing hits an average at bats, an R B I, and stolen base in stolen base rate. But what if a user would rather see runs and caught stealing or triples and strikeouts? What if they want to see any different combination of the combs that we've included here? To allow that to happen? We need to create a dynamic version of one of these charts such that a user can select any two metrics and those metrics would be visualized. So let's start by just rearranging things a little bit. I'm gonna shift click these two charts, shrink him down just a little bit, and I'm gonna just reposition one above the other. And then this 3rd 1 is the one that I'm gonna turn into my dynamic chart something drag this out and make this kind of the featured chart in the dashboard And first things First, we need to tell the user that they can select metrics here. So it's a select metric one right here in X 14. Bold italic. Give it a write a line, and I'll just fill the cell right next to it. Why 14 with the light yellow Phil kind of to match this data validation cell appear nous to And then same deal here in column A A select metric to can use the little format. Painter shortcut. There it was the same Phil. And now let's just turn these into data validation cells with drop down lists, just like our player name. If you recall, I can just unhygienic these two columns and my metrics list lives right here and columns A . H So in, why 14 I'll go into data data validation. Let's allow a list and our source list as a H two through H 15. There you go and then select metric to same process data validation list and the same source array. So now a user can select from any of the metrics and choose any pair. So do a little bit of formatting there, just to center and bold from that painter and when we hide our source list. We don't need that anymore. Okay, so now you can select any two metrics and we want those metrics to be visualized here in this chart instead of having the chart reading from a fixed array from the table above. There are a couple ways to do this. And the first way is to take a similar approach that we took with our animated overlapping area chart, which is to create a new dynamic source data array down below the chart. And if we're gonna do that, we could just type in our years and drag it down and fill a Siri's and create headers based on the metrics selections one and two. And now what we could do is use a combination of functions like index and match toe, actually populate the correct data here by indexing this array and matching to the correct a year in column are and metric in row four. That would look something like this equals index. Here's our entire source data array. Fix it with that four. The road number that we want to move down to. We're gonna match the year here. Fix the X. We're gonna match that within the year. Source array, which is our five. There are 10 four zero just means I wanted an exact match and then a comma over to which column I want to pull from my array. That's another match function, Remini matching the metric header. I fixed the road 32 because my headers always live in Road 32. I'm gonna search for that within the metric header array, which is s four through a f for fixed with F four common zero, its exact match close to parentheses. Hit, enter. And then because I've set my reference types well, I can apply it all the way to both metrics and essentially What that does is it pulls in the data from somewhere up here based on the two metric selected so at bats to 42 6 11 that matches hits 50 to 1 85 hits, 50 to 1 85 As you can see, change that dropped down and the data and source data changes as well. So that's option one. I could just basically drag my source reference down to this dynamic array that I've built . And while I would be done I don't want to take that approach. I want to show you a slightly more elegant way to do this without having to build this random floating source data ray below my chart. Not that this is the worst thing in the world. You know, You could just throw a white font on it and no one will know it's there. You know, the danger of doing that is that someone inevitably adds a row or column or doesn't see it and delete something. And boom, there goes all your hard work. So that is one option. The option that I prefer that I'm gonna show you right now is to use the match function, but to use it in the context of an offset function that we're gonna build into a named range. And what that will do is allow us to pull in dynamically data from the correct column into her chart without having to build another source data array somewhere. So let's get to it. We'll head up into our formulas tab, define a new name, and we're gonna call this metric one. We'll repeat the process for metric to eventually and instead of a fixed reference here. We're going to start by typing an offset function and within the offset function or starting point will be cell s five and then the number of rows that we want to shift. That starting point is zero, because our first data point should always be the 2010 day two point just right there in Row five. But then, if I comma over to the column offset, This is where we need to get creative. Because if you think about it, when a user select a B in the drop down and sell, why 14? Then we don't want to shift our starting point at all because at bats are in column s. If, however, user selected H or hits here in UAE 14 then we do want to shift our starting 140.1 column to the right so that we get to our hits column. So we know this piece of the function needs to be dynamic. And to do that, we're gonna insert a match function and all will do. Here's try to match the value that the user entered in. Why 14 within the array of headers in Row four and note that you need a one dimensional array in order for the match function to work. And the last piece of match is the match type, which is zero for exact match. Close the parenthesis and we're almost done. Except we have to make one final adjustment, which is to subtract one. And if you think about it, it's because if it user selected a be the match function says Okay, I'm gonna find a B within this array and row. For there it is in the first cell in that array. So the match function returns the number one and the number one in the context of an offset function tells excel. Okay, Now move our starting 10.1 column to the right, which would get you to the hits column, where we really wanted the values in that bats. So that minus one just corrects for that comma to the second, the last component of the offset function, which is thehyperfix of our resulting array. In this case, we want six data points, one for each year. So the height will always be six and the width will always be just one column wide close. The parenthesis press OK, and we've got metric one defined. Let's do the same thing to find a name for metric to. And you should be a pro at this by now, we're gonna use the offset function again. And really, the only thing that's gonna change is which metric we're matching. In this case, it's going to be a B 14 instead of Y 14. So our starting points there, we don't want to shift any Rose column shift is a function of matching the value and a B 14 within our header row exact match. Close one parenthesis minus one. Height is six. With this one, close it. Okay, Now, that brings us to our last step, which is just editing the Siri's within this chart to read from our new named Ranges instead of these fixed ranges here. So it's right. Click select the data and here where it says stolen bases. I'm gonna edit this and for values everything. After the exclamation point, I'm gonna replace with metric one just the name of our first named Ray. And for our 2nd 1 delete everything after the exclamation point. Call it in metric to press. OK, both cases my X axis will remain fixed as just these six years. That's okay. I can press. Okay. Now, one thing you might have noticed is that the names of the Siri's didn't change their still called stolen bases stolen base percentage. But in this case, I'm actually visualizing at bats and runs so Excel won't be able to dynamically change your axis labels in the traditional way. So we're gonna do a little work around and delete the labels here and clear a little bit of room for ourselves. And I'm gonna get a little bit fancy here and insert a text box. And I can set this text box to just equal the value in some cell. So I'm gonna set this text box to equal whatever is in life 14 and I'll center it. There we go. And we rotate that 90 degrees, do a little formatting to get rid of the Phil and the border. I'm gonna drag that here like a makeshift access title and then copy and paste it again, dragging over here. Flip it and set. This one equals a B 14 which is the second metric that the user has selected. So I've created kind of my own hacked versions of access labels. But what that will do is allow those to change as the user changes the drop down. So as you can see here, I'm looking at at bats and R B. I's just to mirror this lower chart, and it looks like it matches nicely. Let's try doing the same thing with the top chart hits and average. There you go to 71 all matches up and there you go. So we we've just added a cool new layer of interactivity to make a truly dynamic user facing dashboard in Excel.
9. Value Based Dynamic Formatting: okay. Our next demos about value based dynamic formatting, which is essentially trying to format individual data, points within a Siri's with unique formatting rules based on its value. So to give you an example, let's look at our value based formatting tab, and here have just got two columns of data looking at click volume by day. So if I were to select and be and just insert your basic column chart now, as you can see if I right click the Siri's and format, any changes I make here like Phil, for instance, are applied to the entire Siri's. But what if I want to feature highlight individual data points or columns based on their values? So let's say I want to highlight any point where click volume is greater than 8000. So this point, this one, this one and so on and so forth you could individually select data points and manually changed. They're Phil, but that's obviously not a scalable solution, and it doesn't allow you to change or adjust your thresholds, automate that process. I'm gonna undo that. Change what we essentially need to do here. It's split out or segment our original Siri's into multiple sub series that fall into the buckets that we want to format with unique rules. So, for instance, in this case, if I wanted to apply that 8000 threshold that we just talked about, I could create two new columns in C and D, where one column populates the click volume for any dates where cliques are greater than 8000 and the second column populates clicks for any dates or clicks or less than 8000 and then update my graph to read from those two columns instead of my original column. Be that way it could form at my greater than 8000 column, one caller and my less than 8000 column, another color. So we're going to do something similar but get a little bit fancier here. And what I want to do in this case is create four sub segments of data based on my click volumes. In other words, I want to calculate quartile 123 and four and then format those court tiles as individual data. Siri's. So to do that in cell C one, I'm gonna enter a new column header called percent rank, and here I'm gonna use excels percent rank function where my array is calling B and my value or my ex is the cell right to the left. So in this case, beat too, and I can close that format it as a percentage and apply it down. And essentially, what this is doing is it's telling me where each value and Colin be falls along a 0 to 100% scale. So the largest click volume in this case 9972 equates to a percent rank of 100 and the smallest, which in this case is 5029 equates to zero. And I'm gonna use these percent rank values to split out this original Siri's into four sub segments based on court tiles. So my header for D one will be 10.25 e one will be 10.5, f will be 0.75 and G will be one, and I could just grab the format painter, give those the same kind of header formats. Just expand the decimal two points there, and now we need to write some functions that essentially bucket the click numbers into one of these four columns based on its value, So we'll need to use some conditional statements and logical operators to make that happen . So starting cell D to we'll say, if the percent rank in column C is less than or equal to value in my header here d one, then I want to return the click volume. Otherwise, I want to leave this blank. And I could do that just by taping two consecutive quotation marks. And before I lock this in, I want to make some reference type adjustments. So see to I just want to lock the column because eventually I want to apply these formulas cross columns and down rows. So I'm trying to be very conscious about what type of reference types I set. And then for D one, which is my header Headers always live in Row one so I can just fix the row and leave the D relatives the deacon shift as I move column to call him and then similar to the sea to here . I'm gonna lock just the column for the B because I want this formula to just rose as I move downward. So hit. Okay? And now if I apply this down you'll note that the only sells populating values are cells where percent rank falls under 25. So there's a 2024 15 etcetera. So now a similar case here, we just need to write a new function because here we need values that are greater than 0.25 but less than or equal 2.5. It's a similar kind of case here, but we're going to use if with an end conditional statement. And our first logical is if percent rank is greater than 0.25 and our second logical is it far percent. Rank is less than or equal to 0.5, and then we can close. The first parenthesis value, if true, will be our click value, which isn't beat to value of false will be blank. And again, I'm gonna go through and edit my reference types using the F four shortcut. So for C two, I just want to fix the column. Do you want? I just want to fix the row again, see to just the column e one just the row and then be to just the column and then press enter and I should be able to apply this over and down and have everything populate properly. So now if this all worked correctly, every data point in Colin be our original. Siri's has now been buck. It'd into one of these four columns D E. F or G. So altogether we have every data point that we had an original Siri's and column B. We just have them broken out by value, and that will allow us to create whatever formatting rules we choose on each of these Siri's individually. So let's go ahead and insert a new column chart and add some serious to it. So select data at a Siri's first series will be our first quartile in a control shift. Arrow down to grab all my values there. Press OK, my X axis. There's on my dates, which are a two through a 61. Add a second Siri's for my 0.5. Everything that falls into my second quartile. There we go again. My X axis in states. I'm gonna repeat this two more times for my third quartile. There we go, a little bit manual, but sometimes you got to do what you gotta do. And then finally, we're adding our fourth and final quartile with our values from column G, and this should just about wrapped things up. It's OK, so now let's drag this out that we conform at the data Siri's reduce the gap with and reduce the overlap kind of replicate. Original column chart style. And now, as you select, you'll notice that you can select each of these Siri's individually, and then you're dropped down from your formatting pane. You can also select each of the force that this now breaks it out into individual Siri's that we conform at however we choose. So in this case for 0.25 why don't we give it a solid Phil with kind of a light gray? Then for 0.5, let's do a solid fill with a light green 4.75 We'll do kind of darker green. And then finally, for one, let's give it the darkest dream them all, and now you press OK. As you can see, this Siri's is exactly the same. Whether we selected the original composite Siri's in column B or combination of the four sub Siri's that we built out, which, when aggregated, also reflect Colin be and you can see that it's the same values. Except now I've got individual formatting rules based on these four core tiles of values, and then the last thing we can do just to make this a little bit fun is instead of hard coding, the click column. I just want to show you because it's It's a nice visual way to test that What we've done truly is dynamic. So replace these with a random number generator. Someone use rand between 5000 and 10,000. You could do any values here if you want, and when I do that and apply it down now, these air random numbers that will randomize every time I calculate the worksheet. So I go into formulas and just hit. Calculate now, over and over. And as you can see, no matter what value a particular date or data point takes, it will always be formatted properly. Based on those quartile breakouts. It's a really cool way to add a unique form of dynamic formatting to your excel visuals
10. Dynamically Highlighting a Data Series: our next demo is hands down one of my favorites. It's about dynamically highlighting a specific data, Siri's. So in the dynamic, serious election tab, you'll see some familiar data. We've got our movie ticket sales by month for four different genres. Comedy, thriller, documentary and romance and what I Want to do here is build a stacked area chart that dynamically features or highlights whichever genre user has selected in this data validation cell here and beat to. So to do that, we're gonna need to create two versions of each of these four. Siri's one to define the formatting when that genre is not selected and Selby two and the second to define the formatting when that genre is selected. So in other words, each of these four genres will have a light filled version and a dark filled version, and only the dark filled version will populate data and be visualized in the chart when that genre is selected here in the drop down. So to kick things off, I'm gonna grab each of our genres and just paste two versions of each down in a new source data array below and what we need to do to populate this source data is used some conditional statements that will return data from this array above, but only in cases where this genre has been selected in Selby to otherwise. I want to return blank values so I'll start with comedy equals if the drop down Selby too fixed with that for equals my value in a 28 and I'm gonna fix just the column there, if that's true. So in other words, if the user has selected comedy, then I want to pull in the value from my comedy row in my source data Fix the road here. Otherwise, I want this to be blank so used to quotation marks and close it out. And then if I dragged this on over, looks like it's formatted as currency for some reason. So let's just go ahead and, uh, change that to play number. So there you go. Because comedy is selected, this whole row of data has been populated. If user selected thriller see that data disappears. So now I just need one more formula to basically define the opposite. I mean, a copy and paste. This one basically say, if b to the drop down is not equal to 8 29 then pull in my ticket sale data from above, otherwise blank. So these will all be blank right now because comedy is selected above. But again, if I just that dropped down to Thriller, for instance, you see the data just moved from the selected row to the non selected row. Now we just need to repeat the process for the other three genres. I'm gonna borrow the formulas that it just wrote for comedy and paste them in each of the other three cases and all I need to do for Thriller. For instance, in Row 30 it could press F two to drill into the formula. All I need to do is drag that reference down to the thriller line in both of these cases, and there you go. I can grab thriller Rose, drag him all the way out to December. And as you can see now, that thriller is selected and Selby, too. The top row of thriller data is populated in the bottom row returns nothing so similar Case with documentary drag it down to the documentary sales in both cases and then romance will be the value in B 26. Now I can dry these last four all the way out. And there he goes, have created a new source data array by duplicating the data for each genre and creating a rule where the data will only populate if that particular genre is selected by the user and Selby, too. So one more example. If you go to romance, you'll see the de selected version of each of the other genres is populating. But the selected version, the top Row for romance, is now populating. So this is the data set that will now use to build are Stacked area, and it will be built based on all eight of these data. Siri's even though only four will be visible at one time. So let's go ahead and insert our line options stacked area, and we can select the data and just get rid of what Excel tried to add. And now it can add my first Siri's. And in this case, I'm going to give it custom names just to make it clear when we're formatting leader. So this is gonna be my first Siri's, which is the top row of my comedy data so I'm gonna call it comedy with an s for selected values Will be B 28 through M 28. Okay, My horizontal labels will be January through December. Kind of tough to see, but it goes through M 22 and let me just drag this out of the way to make this a little bit easier. Go back into select data. Okay, so I've got my comedy Selected Row. Second Row is comedy D for D. Selected those values live right here. Press OK, horizontal values. Every time we're gonna be the same months right there in row 22. I'm gonna repeat this process for each Siri's thriller selected is my top thriller Row Horizontal Labels or My Months and Thriller De Selected. It's my second thriller row When my ex values are my months You could see it added that in adding 1/3 1 Doc selected top documentary Row Doc De selected. It's my second documentary row and you can see we're slowly building out each slice of our area chart here Here's romance selected and this is the one that currently has data because that's what our data validation cell is currently reflecting and finally, romance de selected, which is the last row in our ray right here. Okay, so there you have it. We built out are stacked area, as you can see on Lee four. Siri's air being shown because only four of the eight rows in this raw, dynamic source array currently contained data. And in fact, only four ever will at one time. So now let's customize the fill options for each of the eight possible Siri's you can right click format data Siri's, and this is where this drop down is incredibly helpful because it lists all eight here. And if I were to try to right click on each of the individual Siri's, there's no way to isolate and click on any of the four that are currently blank. So I'm gonna kind of use this is my control panel here. So let's just go down the list top to bottom, starting with comedy and why do we just pick a color? That comedy will represent whether it's selected or de selected, so I'll give it a solid Phil. Why don't we say comedy is shades of blue, so when comedy is selected, I want to give it a bright blue color and then we'll just go right down the line. When comedy is de selected, let's give it a lighter shade of blue. Let's actually do the lightest shade we can. We'll go to thriller when Thriller selected when I make thriller Orange and We'll thrillers de selected very, very light Shade of Orange, moving on to documentary documentary selected 70 Yellow and when documentaries de selected later Shade of Yellow and then we get to romance, Let's make a romance green, and when it's de selected, it will be a lighter green. It's not a press, OK, and as you can see, since three of the four Siri's that are being shown here are not selected and Selby to we see the light filled version of those Siris in the Onley Siri's that showing its darker version is romance, which is the one selected. So if we've done our job correctly, it means that as the user changes the value in this drop down and be too, it should dynamically highlight just that one. Siri's and revert all the other three. Siri's back to their lighter filled version, and there he goes. There's comedy thriller documentary and romance. How cool is that? So finally, we can just do a little bit of formatting here. Let's give it a chart title above the chart will call it ticket sales by months, and then at this point, it's really easy to just copy and paste this going over chart tools maybe want to change it to 100% Stacked column. Instead, you know, recon format that Siri's shrink the gap down something like that and then changed the title to percent of ticket sales by month. Since now, we're looking at 100% stacked and both will act exactly the same. So really just a stylistic choice. But there you have it really unique custom formatting rule that you can apply to your charts and graphs to really help bring things to life.
11. Building a Custom Pacing Chart: already for a next demo, I'm gonna show you how to build a really cool, really unique custom pacing chart. If you open up the custom pacing chart tab in our Excel workbook, you'll see a bunch of columns of data here that essentially show revenue pacing towards a goal. So in column B, these air daily revenue totals that are hard coded in as values column C basically just translates that into cumulative revenue. Day over day looks like we've got a goal of 150,000 to hit by the end of the month. Gap is just The shortfall between cumulative and goal percent of month is a really cool use of Dayton time functions to calculate each day is a percentage of the month, and then Target basically says, given how far you are through the month and given what our goal is, how much revenue should we have driven on each day to maintain pace so that we hit that 150 k by the end of the month? So what we need to do now is create a visual that really tells that story and makes it clear how we're currently pacing in this case for the month of April. So why don't we just get started by inserting a line chart and I'm gonna manually add data ? Siri's to this. The first Siri's that I'd like to add here is my goal, and my values will be all of the values from Row two through 31. There you go. And my X axis labels are all with my dates and column A. I'll add a second. Siri's for target. And you know the drill. G 23 g 31 x axis, Same dates, and then all I had one more for now, which is gonna be my actual cumulative revenue. I'm only gonna select down through Rhode 21. But when I do select my X axis labels, I will bring in the entire array don to row 31 because I want basically, I want this line or the state of Siri's to end at the current date. But I don't want to limit the X axis to that date. I want to see the X axis extending through the whole month. So here's our starting point, and we can do a little formatting right now and format this gold data. Siri's you know, maybe want a solid green line with a lighter Phil. And maybe use some of these compound types where you can do a double line or something like that, obviously totally up to you for the target line, the daily pace that I'm trying to hit. Let's make this kind of a light gray two point with, and this is a nice opportunity. Use a dash type here because it helps indicate that that's kind of a guideline more than anything else. And then, finally, I can edit my actual cumulative revenue line. I want to make this one stand out. Since this is kind of a key metric here, let's do a nice, dark blue, smooth line, and we can go ahead and add markers to that as well. You know, I'm a fan of these diamonds. Bump him up to size six. Give him a solid Phil with white and a solid, dark blue border. There we go. Okay, so we've plotted three of these Siri's and we've already done a pretty good job showing how a cumulative revenue is pacing through the first few weeks of the month and how that relates to the target pace that we need to keep track of to reach our 150 k goal by the end of the month. So a lot of people would stop here because it it is a pretty solid visual. But this is the next level exercise. So obviously we've got to take things a step further, and what I want to do here is highlight these gaps. So when cumulative revenue falls beneath the target, I want this space between the two lines to be formatted dark red and if revenue exceeds the target, want this space to be filled with green, just as an additional visual indicator that things air pacing in a negative or positive direction. The first thing that we need to do is actually define those areas in a mathematical sense. And that's what these four placeholder columns are intended to do. Let's drag that out of the way. So for lower essentially, what I'm looking for here is on any given date. What's the lowest point that you hit? It's either a cumulative revenue or the target line. It's got to be one of the two, so all we need to do is type of men function or minimum, it's gonna be the minimum of either cumulative revenue Column C or Target and Colin G. Upper is just the flip side. So on any given date, what's the highest value? Is that the target or is it cumulative revenue? So just type a max function. Same exact idea here now, under and over. This is where we're actually gonna calculate the difference between these two lines, depending on which ones higher so are under Target. Column is where we want to populate values that reflect the difference between the two lines in cases where a cumulative revenue is beneath the target line Over target is where we want to calculate the space between the lines where revenue is over or outpacing the target line. So and sell J to weaken type equals. If are cumulative. Revenue is beneath the target in the value we want to return is the difference between the lines, which is just the upper bound, minus the lower bound value. It false is just a blank, so to quotation marks and then very similar case for over target equals if our revenue is greater than or equal to the target then again return that difference, which is upper minus lower, otherwise blank. So now if I grab all four of those fields, we just calculated double click. You'll see that values populate in column J, where cumulative revenue falls beneath the target and values populate in column K In any case where cumulative revenue outpaces the target and these are the two Siri's that we're gonna build into our chart that can be formatted with those green and red fills. So let's start adding some serious here. I'm gonna select a new Siri's. The 1st 1 I'm gonna add is lower and you'll see why in just a minute values for lower I'm gonna be rose two through 31 X axis dates. Okay, now I'm gonna add under target dragged down the values Onley through the date that we have data for press OK, again. Same case. I want my X axis labels to extend all the way through the month, even though I don't have data for all of those days. Last but not least over Target dragged down to Road 21 and then we'll grab all of our dates in column A And there you go so things don't look quite right yet. And that's okay, because we gotta do a little bit of work to turn this into what we need to. So I'm gonna go on a chart. Tools change the chart type going to combo. So you're probably thinking, Oh, my God, What has gone wrong here? This is This is just absolute chaos, but we're smarter than excel. So we're gonna tell Excel exactly what we're trying to do here one by one. Let's go to reach our Siri's and decide what it needs to be visualized. As so our goal. You see, it was changed to a Clustered column. We just want that to be a line again. Our target. Same thing, just a line and our cumulative revenue. Let's do a line with markers. Those were the three that we originally set back to their original form. And now these three, the new ones that we added lower, under and over. These are all going to be stacked areas. So do the same thing for all three all on the same axis and press OK, and now you're seeing that we're getting a little bit closer here and in fact, all we need to do. It's edit some of these area Siri's, and we should be golden. So first and foremost, this is our lower, which basically gives us the area up to the minimum point, whether it's revenue or target. No need to do is give it no Phil and no border, which really just leaves us with the under and over that need to be adjusted. So here's the under, which we can fill with a red and some transparency. If you want, it looks fine, and then I can select over Target, and we'll give that one nice dark green with a similar transparency. So now when we extend this out, we've got these custom shaded areas above and below our target line. At this point, it's really just a function of doing whatever formatting you want to do. Maybe we change the units to every two days, space things out a little bit, probably helpful to add a legend here as well. So let's put a legend at the bottom and space this out. We don't need a legend for lower cause. It's really just invisible. Anyway, I don't make that bold, you know, and of course, you can add data labels if you want. Maybe you just want to add a data label you know to your current date revenue value. And you know you can give it a nice, dark blue Phil with bold white font. That's kind of a nice effect, and you can go into the options but above your data point. And really, from here, it's just up to you how you want to tweak and personalize this. But there you go. Very unique, an effective way to visualize something like pacing against a goal.
12. Designing a Custom Gauge Chart: This next demo is a fun one, and it's a good example of how to combine two very different types of charts together to create something totally brand new and unique. So in this case, we're actually going to be combining a doughnut chart with a scatter plot of all things to create a gauge and needle effect. So that type of gage chart visualization can be a really nice, really simple way to show where given value falls along a certain scale. So in the gauge chart tab you could see have put some objects in place to start to create a little bit of a template. The's shapes will just create almost like a background effect for gauge on. These text boxes will just align with different points along the edge of the gauge. So I've got a few place all their columns here, got chart value and sell A to this. Is the user entered value that you'll be plotting on the charts? Really, Only one value that's gonna be shown, and then a max Value and Selby to In this case, we're looking at a 0 to 10 scale could be 0 to 100 year 2000 really could be anything. So the first thing that we need to do is create a donut chart, which will serve as the background of our gauge. And in this case, I want a doughnut with five segments along the top half of the circle. So to do that, I'm gonna type 10% copy and paste that total of five times and then in cell C seven type 50%. The reason I'm doing that is because I need these to add up to 100% even though I won't be using the 50% chunk. What will basically do is just format this 50% slice with no, Phil said, it becomes invisible. And then I have five equally sized segments along the top half of my semi circle. So let's grab these values from C two through C seven and insert doughnut. I don't need a chart title here or a legend, and all do is select that 50% piece format the data point and give it no Phil and no line. And then for the chart itself uniform at the chart area. No, Phil, no line. And that's good for now. We're gonna make obviously a few additional formatting adjustments with colors and alignments momentarily. But the next piece that I want to do is start preparing my data for the scatter plot values that will be over laid on top of this doughnut chart. It's basically the idea here is to create a combo chart that plots that doughnut slices as well as X values and why values to feed my scatter plot. So Step one to generate those values is to calculate the current value in terms of degrees . That sounds complicated, but it's really not. First things. First, we think about the current value as a portion or percentage of the max. In this case, Value five would return 50.5 or 50%. All we need to do is multiply that by the 180 total degrees in our semi circle to translate that into degrees instead of a percentage. So there you go. Value of five out of 10 means we'd be looking for a vertical line or 90 degree right angle now to figure out the actual X and Y values that we need to plot along a scatter plot to define the position of the needle will need to dust off our trigonometry books and go back to the basics. I found a website that's actually really helpful to understand this, So let's take a quick look. So here we are at www dot maths is fun dot com slash geometry slash unit circle dot html. And this is a great interactive visualization that shows the relationships between angles, signs, co sines and tangents and the important thing to watch years as I move along the arc of this circle. The Green Line, which is the height of this point, or the Y value, is defined by the sine function or the sine of the angle. The blue line, which is the X value of this point, is defined by the co sine of the angle. So with that in mind, I know that we can use excels, sign and co sign trigonometry functions to define the exact positioning of this point, which, when you think about it, will essentially serve as the endpoint of my needle. So let's give this a shot in excel. All right, so the needle of my gauge needs two points because those two points will be connected by a line which essentially creates the needle itself. First point has to be the origin or 00 and that really reflects its point right here. The base of the needle that will never move. It will always be fixed at 00 The second point is the point that I need to define using those sign and co sign functions that I just mentioned. So remember the why values air defined by sign so I could just type equals sign And here's the catch. It can't just do the sign of the degrees because Excel is actually looking for this number in terms of radiance. But luckily, they have a function to convert it by just typing radiance in here. So sign radiance D to there you go. That returns at one, which in this case, is exactly what we're looking for. Because with a value of five out of 10 we'd be looking for a vertical needle with a Y value of one, which is the max for X values. Let's start with just a co sign function, so co sign radiance de to close out with two parentheses, And there we go. So now we've got the points that we need for a scatter plot, and it just comes down to adding that as a new Siri's to create our combo chart, right click the doughnut Select data. Add a new Siri's that will call Needle and the Siri's values. For now, I'm just going to select one of these either the X or the Y. In this case, I'll select the lie values in F two through F three Press. OK, and as you can see, Excel defaulted to just adding a second ring of my doughnut. So if we're going to chart tools and change the chart type, you're in a combo and serious one, which is my doughnut will remain a doughnut, and my needle Siri's that, I just added, will be a scatter with straight lines and compress. Okay, And now that we've defined our second Siri's as a scatter plot, we can go back into select data. You can edit the data for that needle, and now we can enter a proper X and y values. So X values they're going to be e 233 And why will be after the referee? There we go. So now we've done the hard part. We've gotten the data defined in a way that makes sense. Now we need to just make some tweaks and adjustments to these charts to get things to align and look nice. So first things first on my scatter plot, I'm gonna right click and form at the X axis. I want a minimum of negative one and a maximum of one same deal with the why minimum of negative one, a maximum of one. And I can actually delete the Axis itself because I don't need the numbers. I can delete the grid lines, and I conform at this line anyway, that I choose. So I don't need to make it a solid black with a little bit of a shadow effect on it as well . There we go now onto our doughnut. Let's form at the donut. Siri's what I want to do. Seo. It's rotated on its side. I want to flip it so that my equally size segments are on top so I can change the angle of the first slice all the way to 270 degrees. There you go. Increase the donut hole size a bit. That looks nice. Now let's drag this combo chart right into our template. Could make it a little bit bigger just so that it fits nicely. There we go. And why don't we color code the segments so that the colors represent something meaningful . For instance, if this gauge reflects values that progress from good to bad, then maybe want green on the left and red on the right, so you could just give it a solid Phil, maybe a dark green there, lighter green for the second segment, yellow for the third orange for the fourth and finally a dark red for the fifth. That just really helps to visually emphasize that progression from good to bad or bad to good. So let's test it out and see if this thing is working as we expect it to do. A chart value. 2.5 Well, that looks wrong. Should be the opposite. What if we do value of 10? See, that's aligning zero, which seems a little off. If we do zero, it's shifting over to the max. If we return to our online tool, you can see that in this case the angles are increasing as we move counterclockwise along the arc of this circle, whereas in our case we want our angle to be increasing as we move clockwise along the arc of the circle. So all we need to do it's a very easy fix is just invert this coastline function by adding a negative. So we're taking the negative co sign now, and there you go. So now value five straight up and down. And as we increase, we're moving clockwise along this gauge. So hopefully enjoyed that one of the more unusual Excel tutorials we'll probably ever see. But I think it's a great example of really pushing the combo chart concept to a whole different level.
13. Visualize a Percentage Using Array Functions: So one of the more powerful tools and excel that is typically misunderstood is theory. A function, and the reason it's so misunderstood and so mysterious is that it operates in a way that's different from any other cell formula or function in Excel. So because of that, they could be very tough toe learn and very tough to master. But once you do get a grasp of how Ray functions actually work, they can be an incredibly powerful tool. And I'm gonna show you an example of how that can also be used in a context of building data visualizations in excel. So on the Array Percentage grid tab, we've got a user to find value and be to where you can just type in any particular percentage. And then we have four columns with the ray functions in Collins G through J. Now, rather than writing these out from scratch and really not doing them justice, I figured I'd put them in here now so that you can dig in as much or as little as you choose to kind of decode what the's functions air actually doing. But in the interest of time and keeping it data visualization focused. I'll just give you a quick summary of what these four are actually trying to accomplish here. So the array, function and column G essentially just uses mod and row functions to define an array 100 rose tall that looks something like this. Basically, it just counts from 0 to 9 and then repeats the process from zero again. The second Array Function column h uses integer and row formulas to produce the array that we see here, which is basically counting groups of 10. So they're 10 zeroes, 10 ones 10 two's all the way down to 10 nines, and we scroll back up. Basically, what these formulas and Collins, G and H are doing is they're creating pairs of points that can be used to populate a 10 by 10 grid using the scatter plot chart type. And what you end up with is 100 individual points. And so these two columns are essentially just creating a template. That's why we're calling them empty boxes that we can use to visualize whatever percentage is written and B two and then the second to ray functions. Basically, all they're doing is they're using this percentage to replicate the values and columns G and H down to a particular row, so in this case have written 15%. So you'll notice that the 1st 15 rows are just duplicates of G and H. And then there's zeroed out. After that, you could see if I change this to 12. Now only the 1st 12 rows or duplicates change it to only the 1st 2 And what this will allow us to do is add a second Siri's to our scatter plot, using these combinations of points to overlay our grid with filled boxes to indicate the percentage. And Selby, too. So let's give this thing a shot. I'm gonna insert a scatter plot, drag it right here under my percentage Select data at a Siri's name. The first, Siri's empty and the X values weaken. Just select G three control shift arrow down to G one of two. And then the Y values H three control shift are down to H 102 It Okay, Okay. I'll do a little bit of formatting here. No title. No grid lines don't actually even need the axes themselves. And if we form at the plot area, I don't want any Phil or any border. And last but not least, I'll edit the Siri's itself and in my fill and line options and go into my marker options here. Use built in markers with squares and let's make them nice and big. How about 15 points squares and why don't we fill those markers with just a light gray? Since this is kind of our template, this reflects our empty boxes. Do no border on those, and there you have it. So as I kind of shift spacing around, this becomes a really clean 10 by 10 grid. Oops, Let's get rid of that border format chart area No border Aereo. So you've got this clean 10 by 10 grid that's populated with these two array functions here , and you may have noticed in the formulas tab. If you look at your name manager, you may have seen these four named arrays, which correspond with the rays that I just walked through in Collins G through J. So I've already built these in for you as named ranges, which means you have the option. Either create your source data like I just did, or if you'd rather you can go into select data and you can edit the Siri's. And instead of G 33 G one or two, you could just type array, underscore X and you're wise would be array underscore why? And that does the exact same thing, just two ways to accomplish the same goal. And now all that's left to do is at a second Siri's, and we're gonna call this full and the X values will be the array produced in cells. I three there I want to. Or it could type after the exclamation point array X X, and same deal here. I could select this whole range from J three to J 102 or just type array. Why, why? And enter and press. OK, And I see these two little dots because I've only selected a percentage of to only two of these boxes are gonna be filled. So maybe we up it to 25. So it's a little bit more readable. And now we conform at that second data Siri's and this is up to you. You could keep them as points or diamonds or triangles or whatever you want. In this case, I'm going to do, built in markers, the same square sized markers as my empty boxes. So size 15 just so that they overlay really nicely on top. I'm gonna give him a Navy blue Phil, and I don't want a border on those. And there you go. So I've got my two over laid 10 by 10 grids with square markers built out in a way that any time I change this percentage, the right number of boxes will fill up to reflect that here on the grid a really interesting way to visualize something that is pretty simple but can be visualized in a way that's a lot cooler than most people tend to show it. So that's your quick 101 crash course in using array functions to build out a percentage grid.
14. 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.
Chris Dutton, Founder, Excel Maven
