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

af Afrikaans
ak Akan
sq Albanian
am Amharic
ar Arabic
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bem Bemba
bn Bengali
bh Bihari
bs Bosnian
br Breton
bg Bulgarian
km Cambodian
ca Catalan
ceb Cebuano
chr Cherokee
ny Chichewa
zh-CN Chinese (Simplified)
zh-TW Chinese (Traditional)
co Corsican
hr Croatian
cs Czech
da Danish
nl Dutch
en English
eo Esperanto
et Estonian
ee Ewe
fo Faroese
tl Filipino
fi Finnish
fy Frisian
gaa Ga
gl Galician
ka Georgian
de German
el Greek
gn Guarani
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hmn Hmong
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ia Interlingua
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
rw Kinyarwanda
rn Kirundi
kg Kongo
ko Korean
kri Krio (Sierra Leone)
ku Kurdish
ckb Kurdish (Soranรฎ)
ky Kyrgyz
lo Laothian
la Latin
lv Latvian
ln Lingala
lt Lithuanian
loz Lozi
lg Luganda
ach Luo
lb Luxembourgish
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mfe Mauritian Creole
mo Moldavian
mn Mongolian
my Myanmar (Burmese)
sr-ME Montenegrin
ne Nepali
pcm Nigerian Pidgin
nso Northern Sotho
no Norwegian
nn Norwegian (Nynorsk)
oc Occitan
or Oriya
om Oromo
ps Pashto
fa Persian
pl Polish
pt-BR Portuguese (Brazil)
pt Portuguese (Portugal)
pa Punjabi
qu Quechua
ro Romanian
rm Romansh
nyn Runyakitara
ru Russian
sm Samoan
gd Scots Gaelic
sr Serbian
sh Serbo-Croatian
st Sesotho
tn Setswana
crs Seychellois Creole
sn Shona
sd Sindhi
si Sinhalese
sk Slovak
sl Slovenian
so Somali
es Spanish
es-419 Spanish (Latin American)
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
tt Tatar
te Telugu
th Thai
ti Tigrinya
to Tonga
lua Tshiluba
tum Tumbuka
tr Turkish
tk Turkmen
tw Twi
ug Uighur
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
wo Wolof
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu

Original subtitles

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.