All language subtitles for 4. Assembling a Dashboard - Part 3

So now we have pretty much all of the information that we need for our dashboard.

All we need to do now is put it all together.

Then it starts out with the easiest thing, first of all.

And that is just to move the charts across to the dashboard.

So if we go back to our first chance, let's pick it up.

Control, see and simply paste it Control V on the dashboard.

And of course, you can organize these however you want.

So I'm going to make this a little bit wider.

Let's go to our next chance.

Let's grab this one control.

See go to our dashboard control v to paste.

And this one is just going to go directly next to it.

Now I'm going to try my best to get all of this lined up, so it looks quite neat.

Okey dokey.

Let's do our final chart control.

See go across to the dashboard control v.

This one, which is going to make the full width of both of these.

So we can just drag.

That's out.

Now also notice that all of these charts have a very faint border around the outside, so you might

want to remove that to keep it nice and clean looking.

So unfortunately, you have to do this one at a time.

So let's click the first chart.

Let's go to format, and let's say that we want no outline.

Let's do the same for these others.

No outline.

And the final one down here.

No outline.

And if we want to actually see these in action, if we click the drop down watch for all the changes

across the charts, you can see that everything updates such a cool way of controlling multiple charts.

The final things we need to add here are our headline statistics.

So that's these ones at the top and also our gross sales for each of the different countries.

Now we have all of this information in our calculations workbook.

So all we really need to do here is link to the cells that contain the correct information.

So for this, we're going to start out by drawing a text box, and I'm going to draw this underneath

highest number of orders.

Now, by default, your text box is going to be white.

We're going to get rid of that background.

Fill in a moment now.

When you're linking to cells, you want to make sure that your cursor is up in that formula bar and

we need to type in equals.

Let's jump across to our calculations worksheet because the first value we want to pick up is the highest

number of orders.

And as we know, that is down here.

So in this little table where we have our max value, I could choose to show the value or I could choose

to show the label the product.

Now it's more meaningful if we show the product.

So I'm going to link to this cell just here and hit enter.

And you can see that that pulls through.

So let's do a bit of formatting.

Let's put this into the middle.

I'm going to make it slightly bigger.

Let's widen this out.

I'm going to change the font color to white and then we're going to change the background, fail to

no fill.

And we also don't want a border.

And there we go.

Now I'm going to copy this text box across for these other two.

So control D.

Let's drag this into place underneath highest costs this time I want to link to a different cell, so

up to the formula bar.

Let's type in equals.

Go back to calculations, and this time we want this one just here and hit enter.

Now all of these appear to be Vermont for 2020.

So these do change when we change the year and we need to go in and apply the same formatting.

Alike, so let's do control D to copy again.

Let's change our formula, so equals.

We want to go back to the calculations page and this time we won't sell RD 23, enter and apply a formatting.

And if you want to make sure that all three of these text boxes are lined up, you can select them all

and then use your alignment tools on the shape format ribbon.

So in a line, I can say that I want to align them all to the top.

Like, so let's check to make sure these are working.

So let's click the drop down.

Go to 2018 and I can see that yes, those are updating.

The final information that we need to add are the gross sales for each of the countries.

And we're going to do this in exactly the same way.

We're just linking via a text box.

So where are these calculations?

Well, let's have a look at our calculations tab and actually looking at this, I don't have the information

that I need.

So I'm going to quickly add something else to my calculations area.

I'm going to copy this table control.

See, and let's put this down here.

Control V and I'm going to give this a heading.

We're going to say gross sales by country and it supply the same formatting using the format painter.

Now what I want to work out here is not the units sold.

We want to do a some ifs so that we can find the gross sales by country and by the selected year.

So this needs to be gross sales.

We're going to do some ifs the some range we go back to our Pivot Tables workbook, the some range is

the gross sales.

So let's control shift down arrow to select.

Criteria range one will that is going to be the country range, so let's select control shift down arrow.

The criteria is going to be.

The country and then our criteria range to is going to be the year.

So back to our pivot table, let's select yeah, control shift down arrow comma and the year is going

to be picked up from sell a 15 effort to lock.

Close the bracket.

And and now I have that data and I can copy it down.

Let's apply some currency formatting and take those decimal places down.

So now I have this data I can link to this from the dashboard.

So let's go to the dashboard.

We're going to insert another text box.

This time we're going to type in equals.

We're going to jump across to the calculations worksheet and we want to pick up that value for Japan

and answer.

Let's change the formatting.

We're going to say no fail and no outline.

Now let's duplicate this text box control D. Let's drag that across.

And now we can simply change the cell that we're linking to.

So let's delete everything out equals and select the value for India and say, I'm going to repeat this

process.

Control D.

Drag it across equals, let's pick up the value for the UK and enter control d again.

And this one is going to be for the U.S. So let's grab that figure and enter now again, with all of

these, if you want to make sure that they are aligned, you can select them all and use your alignment

tools to align to the top, middle or bottom.

So let's say top and click away.

And that is our dashboard complete.

Let's make sure this is working.

Let's select a different year from that dropdown.

And would you take a look at that?

All of our headings at the top here update, as do all of our charts.

So hopefully there that has given you a taste of dashboards.

Things can get a lot more complex than what I've shown you here, but I feel that this is a pretty good

level as we're coming towards the end of this intermediate course.

It's also been a good opportunity to practice some of those really important formulas and skills we've

learned in earlier sections.

If you manage to follow along with all of that, then well done.

This is pretty complex stuff.

I'm pretty happy with what we've created, so I'm going to grab a cup of tea and then I will meet you

in the next section.