Build The Dashboard - Design a Better Dashboard Part 3

About this Tutorial

We're actually gonna go build the dashboard today.

Featured Formulas

CHAR() COUNTA() HYPERLINK() IF() INDEX() ROUNDUP() SUM()

Video Transcript

<div> Hey, welcome to part three. We're actually gonna go build the dashboard today. Actually we're gonna build one section of it. I already built these two so you can see what it looks like. And then we're gonna build this third one. I'm gonna show you each of these formulas, how to do them how to link. And the header here to a sheet. So this allows you to skip having to descri tell people where the data is. Because of the way I set it up, I, I sort of have data in one place. Then I have w worksheet where I do a lot of like the calculations.

We want to get that away from people that just wanna see this summary and this dashboard . But if somebody wants access to that or we want access to that, to double check the numbers. This Link makes it really easy. Yeah. And, and then we're gonna build this gauge chart this gauge chart just like I was describing in the first video.

A speedometer is a really good indicator of, you know, what speed you're at. If you're going too fast, too slow, and there's some data where say like rate of growth, well, not really rate of growth, but like rate a speed at which you're going.  A gauge is a really, really good way to see like, does going to fast decrease something. So in this use case, we're doing sales. These are like four sales reps doing some calls. They are making some sales and they have some revenue.

So,  we're gonna try to figure out, and we can see already here, Gladys is sort of getting into like, oh, she's doing a lot of calls per day. And then we can go back to the data here and see like her revenue is great and her sales are great. So really like this is a good indicator. Like, oh, we should be in that area. These are all good. Whereas like, See, a month ago was like not so good, right? Three calls a day. But like her, she had the lowest per sale, and that changed. So we can see changes month to month, we can.

Right now, this is just the last month and each month we add to it. This will automatically update. In the next video, I'm gonna show you how to make a little dropdown . List here to select which month we're looking at. So sometimes we wanna look at only one month, sort of the last thing we we did, but sometimes we wanna be able to flip between different months if we want, or weeks or days. If you're doing a dashboard that you want to check every day but wanna check. . So sort of a date date range . Okay, so how do we do this? We're gonna do this with total revenue.

See, we had calls, sales and revenue. So we wanna do this for revenue first. We're gonna merge all these up here. Let's let's use a nice orange. One thing I do wanna mention as I, as I do this, who's gonna take this text? And I think this was merged as. One thing I wanna mention while I'm doing this is that I like to put the headers as light colors. This is a personal choice and a personal design choice that I really like. I'm also going to make this comforter, make that a little easier to read, make that as well comforter. And I make the numbers more bolder. .

So how we get the last month. So on the data chart sort of, you can do your data in any way you want. I just did this very quick, sort of each month, March, April, may, had some calls for each rep, some sales numbers, some revenue, some per sale calculations , calls per day. But I wanted to put that in a chart where we had March, April horizontal. As we add, we can get the data much easier. So I created this worksheet, and so we now have total calls, and all they do is go to the other one. You can have your data in any way you want. I particularly like it like this. .

And this will come in handy. This version will come in handy in the next video when we create we're gonna switch our formulas. I'm, I did a very, very quick and dirty way to get this information this way, and then I show you another way to do it that's much better using index match . So basically we wanna get just the last month, like that's filled in here and that every time we fill it in, it updates automatically so we don't have to come and change this dashboard . So what I. Because I use just index and count all.

So basically the idea is take account of all of these cells , how many columns there are, and then I use index to find the last one. So I say, okay, there are four columns here, so give me the information in row three.  column four. Basically there's a count, and that's really easy. I'll do it here for you equals index . There's three, four three parts of this formula . You say what you want to reference , so that's a three to Let's do just three, so it goes across the entire row .

The row is the same one, so it's just one, and then the column is going to be a count all function where we count a. To three. So we're gonna go here and put a three to three there. And I highlight this part. See this says four. So this part is just a four, so it counts how many. Are there here? Four. So give me the fourth column of this row. That's what Index is doing. And so we get 1 75 and as we, if we took like, let's see, 1 78, put it there. Now there are five and we see it changed to 1 78. So that's all we're doing over here on this tab, on this place.

So we go equals.  say work a three to three for the whole row . One count all work a three to three. And again, this is like a quick and dirty way to get that last column basically. And we want to also get the delta, so I put, I did the work down here. The delta or the change of calls is going to be the eighth row. So we go here, double click there, and we take the same formula . We can just copy the whole formula . And all we're doing is changing this first one from three. To eight. We want row eight, and now we have the delta. Okay.

Let's do a nice orange. Let's do the, I think this is 25. Yeah, 25. And also center it. And center it vertically. And then get a nice.  dashboard here. So the last thing for this is we need an up chevron or a down Chevron saying, is it up or is it down? What we can do here is use an if statement and we're also gonna do conditional formatting . So we need, first I want to go through this. It's, it's it's two if statements. First off, let's do see this. So if it's above zero let's change that to N five.

So if N five is above zero, then if it's true, then give me an the up one, which is charge 7 0 8. In the last video, I went over that, and if not, there's now two options. It could either be zero or less than zero. So we put another if statement and we go if Yep. Do. If. What is this one? This is gonna be, N five is less than zero. We want character 7 0 9, which is the down one. And now if, if it's neither up nor down, then the only other option is it's the same.

So here we could do literally nothing. We could put a nothing there, or we could put this character like a three equal.  and call it today and say, okay, we now have up, or if this number is negative 34, see that changes. Oh, we just did that wrong. So this needs to be, oh, this needs to be less than zero. There we go. And now we have it down Chevron. Now all we do is say, let's change that to 25. We could do the color ourselves, but we want to do conditional format . So we say right click let me change this back.

Oops, this was eight to eight. Now I wanna change this to green when it's up and red to when it's down. So I control right click and I'm gonna go to conditional formatting to go up over here at a format . Let me move my face. . Now this is gonna get a little, this is a little complicated because we want it to, we, we don't know the number . We don't know if we can, we can put a value here, but what if we just wanna do a cut? We wanna do a custom formula , and, and this is how it goes. It looks a little weird, but basically the cus the custom formula is , is like an equal sign.

Equals doesn't mean it, it's equal to, it means for this one it is. It is. And then now we put the condition , we say N 35, we have to put a dollar sign there is greater than zero. So this N 35 is greater than zero is a formula , but this equal sign is not part of the formula . Just saying, this formula is we want the background to be none. We want the color to be. So now, now it's not changing. Oh, I know why. Actually, I don't know why. Let's figure that out. Alright, this was a silly mistake. It's N 35. It's actually N five.

It was going over I, I saw that 35 and I thought it was N 35. So it's actually N five is greater than. Zero. And we say Done. And now if it's less, then we add another rule and we go. Same thing. Custom formula is , we say the formula is if N five is less than zero, we want the background to be none. We want the color to be red, and now we can check it just by doing this. There it works. And if it's zero, , it'll be black. Great. Perfect. That's what we want.

So now the last thing we'll do is we will put a link up here. So sometimes you have this number and a, you wanna check it, but also anyone who's watching or, or viewing your data , they may want to just double check it, right? They may wanna see the numbers. So what you can do is command K or add.  and we can actually select a range of cells to go to. So we want to go to the data maybe, or actually we want to go, let's go here and we wanna say, okay, this is the calls here. Okay, let me see. Apply.

And now this link will take us right to.  and that enables us not to have to look through all these tabs. It doesn't, we don't have to do anything extra there. So we now made a nice, beautiful chart or dashboard , and now we're gonna do this, this gauge chart . It's a little complicated and you have to do the steps in this order, or it literally doesn't work, but let's do this. We have these numbers and, and there are numbers that we wanna say, like there is a high limit and a low limit , and we want it colored, but we want this like line, right?

So gauge chart is like just perfect for this sort of like speed we're going. So what we do first is let's do insert sure. And then we're going to cu chart . Scroll down to Gage Ch behind my head. There it is. Gage Chart has this, and we're going to do the data range . I can actually go pick a range . It's going to be on work. Let me move my head over here. It's gonna be work. I think it's going to be.  work. D 14 to D 17. Okay, but we actually missed one thing.

We have to put in actually A to D because we want the names as well. So that's just the data range of all the data , the labels. Let's just do this. Let's add a label . It's gonna be a to. And then the value is going to be D to D as well. You see that? Let's make it, so now we need to color it. We need to see like, okay, the colors, and we also have to set the minimum maximum. If you see right now it like you can't tell the difference. It's very small difference. So we go to customize, go to Gage. Our minimum is going to be zero, our maximum.

However, because these are small numbers and we're probably gonna get about a maximum of about seven, we're gonna change that. Now. You see big difference. You see the big difference between each of them. And now we can do, do our range colors. So we're gonna say five to seven is to fax, right? A pretty good number is five three to.  and then zero to three. See that? Actually let's do two. We have to change this to two and this to two. There you go.

And so now you see Gladys is on the high end and you see Nancy, Abe and Carl are all in the green, really low. What might you might run into is when you're setting these ranges , is this gauge range is too high up. , as you can tell there. So if you set it down here, it's fine. If you even set it a little too high, you'll see a white over here and maybe that's what you want.

Or if you mess up these minimum maximums and, and have a gap it'll show literally a gap here, a white gap right there. So maybe sometimes you want to not have the yellow, maybe it doesn't matter if it's in that range and you have a green or you have a yellow here, a z, a white, and then a. Totally dependent on what this means to you and how, what story you want to tell. If this is actually supposed to be, yeah. Five. No, this is four Four. Three three C. Oh, that's too small. So that's probably the other way. Five. Probably one right around there.

It's probably a good chart and that shows you that Gladys is in the yellow and everyone else is right around the the green. And the last thing we'll do on this chart is title it. So we go up to customize chart style . We can actually take the border off. A little nicer. We can go to the chart titles. Put in the chart titles. Calls are great. we now have a great little dashboard with a speedometer and great top line numbers. You can add in different numbers underneath this. So like you don't have to just do one top line.

You can have multiple sort of secondary numbers underneath this, maybe number of calls per person. Maybe you want to put, instead of just calls per day, you want to see different numbers here in between. Something to tell a story, but now you have this. , cute little dashboard , and you have a nice speedometer there. In the next video I'm gonna be talking about how to add a dropdown here so that we see the numbers change.

If we change the numbers, if we add let's say we just add these numbers here, and then we can just put these across and have the deltas. Now you'll see the numbers have.  and we have new numbers. So each time you add the month, these numbers are going to be different. So that's a quick and dirty way to make sure that these update per month you add in or if you're doing this every week. But the next one, in the next video I'm gonna show you how to make a dropdown menu and change this. And we're gonna use index match to match the month to the numbers.

Join me in part four.</div>