Afrikaans
Akan
Albanian
Amharic
Arabic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
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.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.