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 let's start assembling this really cool, interactive dashboard.
Now, just to make things a little bit quicker, I've already done parts of the dashboard design.
Now the way that I've done this is just basically using shapes and text boxes.
So this green background is just a rectangle shape.
This is another rectangle shape, and all of these labels are just text boxes, and I've just formatted
the text inside.
So I will say that before you start to build your interactive dashboard, it's always good to have a
plan.
Think about the kind of information that you want to display on that dashboard before you even start
to do calculations and put together charts.
So we've established the information that I'm interested in.
I've got those headings on my dashboard.
And the only other thing I've really done here is that I've turned off the grid lines, so I have a
nice, clean page.
The rest of these tabs, I've pretty much removed everything that I had on the so I can show you how
to build them.
And on the calculations page, I don't have any of the calculations in here.
I just have these headings.
And of course, on the pivot tables page, there is +source data.
So if we jump back to the dashboard, the first thing I'm going to add onto here is that little combo
box dropdown.
And this is key to this entire dashboard because depending on the year that we select in that dropdown,
that is going to determine the data that we see.
Now this is something we haven't covered in this course.
And if you choose to go through the advanced course, there is a lot more on form controls.
But let me just give you a little sneak peek as to what these can help you with.
Now, form controls, you're going to find them on the developer ribbon now.
By default, you may not have your developer ribbon turned on.
If you don't jump into file, go down to options and then on Customize Ribbon.
Just make sure you have a check next to developer, and let's click on.
OK, so now I have my developer ribbon.
I want to insert a form control, and the one that we want to use here is this one, the combo box dropdown.
So let's click.
Notice that my car has changed to a small cross and I can now draw my little combo box control.
Like so, so I guess drop down, Arrow.
Currently, it's not showing me anything because I haven't specified what I want to put in there.
So what do I need to go in here?
I need the years to go in that and in my dataset, we basically have three different years 2018, 2019
and 2020.
And this combo box needs to get that information from somewhere.
So this is where we're going to go across to our calculation sheets.
Now, the first thing I'm going to do here is I'm going to get a unique list of the year's contained
within my data source.
Now for this, we can use an Excel function that I use all the time called unique or we need to do is
jump across to the pivot tables worksheets and select the column that we want to extract unique values
from.
So that is Column M the year column control shift down Arrow to select the entire range to enter.
And would you take a look at that?
We now have our unique list of yes.
If you have a fairly small list, you can also just type them in.
I could have done that, but I chose to use unique.
Now with this, you don't really want to have that unique formula sitting in the background.
So I'm going to do my little copy and paste values trick.
So let's highlight the values control, see, go up to pay special and just paste those values, and
that will effectively throw away the underlying unique formula.
Now I actually want to sort this year, so 2018 is at the top, so let's right click go to sort and
do smallest to largest.
Now what I'm going to do is down here in selected year, I'm going to do an index formula.
So we're going to type in index.
We're going to index this array just here for too long.
And the next argument we need here is the row number.
Now the row number is going to be in-cell a 12 f for close the bracket enter.
Now, if I type in number one into here, it's going to tell me that 2018 is number one in that list.
Number two is 2019.
Number three is 2020.
So this is how we want this to function.
Now that we have our years, we can use this unique list to populate the combo box on the dashboard.
So let's go back to the dashboard.
Let's select our control.
And then on the developer ribbon, we're going to open up the properties for this control.
And this is where we can select our input range and our cell link.
So our input range, which is basically where are the values we want to populate this combo box?
Well, our input range is on our calculations, worksheets, and it's this range just here.
What about the cell link?
Well, again, on the calculations page, this is going to be selling a 12 and click on.
OK, so now if I go to this dropdown, I have my different years in here.
So if I select 2018 and take a look at the calculations page, you can see that there.
The selected year is 2018 and the selection is number one.
If I change this to 2019 and take a look at my calculations page, you can see that all of these formulas
are updating correctly.
And this is really the key to this entire dashboard, so make sure that you get this combo box dropdown
correct.
The next thing I'm going to do is I'm going to prepare the data that I'm going to use in my column charts,
and my Clonshaugh is basically going to display the units sold by country.
Now this is where we need to use a some ifs formula, another skill that we've practiced in this course.
What I want to do is basically look at my source data and say some together the units sold whenever
you find the country Japan, India, United Kingdom, so on and so forth.
But we have an additional piece of criteria because we want this also to relate through to whatever
year is selected.
Because on the dashboard, if we've selected 2019, we only want to see the amount of units sold for
2019.
So that is why we use some ifs because we have two pieces of criteria to add.
So what is our summer range?
Well, it's the units sold.
So let's go across two pivot tables and let's go all the way to the top and find our units sold.
Column control shift down arrow to select comma.
And next argument is criteria range one.
Well, we're breaking this down by the country.
So my criteria range is going to be the country control shift down.
Narrow.
Comma, what is my first piece of criteria?
Well, if we jump back to the calculations tab, we wanted to pick up the values for Japan.
Comma, we can now specify criteria range two.
So our second piece of criteria is that we only want to see the units sold for Japan for whatever year
is selected.
So criteria range two is going to be the year.
So we go back to pivot tables.
Let's select our year column control, shift down arrow comma and our criteria is going to be whatever
is the selected year down here.
Remember, this changes depending on what we've selected in that combo box F4 to lock close the bracket
hit enter.
And now I can copy this formula down so everything is set up so far to link through.
Notice the top value here two three eight five six nine If I go to the dashboard and change this to
2020, you can see that these values have completely updated.
Now we're going to do something similar for the other chart.
So this is one table that is basically going to provide us with the number of orders by product chart
and also the cost vs. profit.
Now this time the breakdown is by the product, but we still want to pick out the data not only by the
product, but also by whatever year has been selected.
So when we're looking at number of orders this time we want to do a count ifs because again, we have
two pieces of criteria.
What is our first criteria range?
Well, our criteria is going to be the product.
So our criteria range is also going to be the product.
So let's select the product range control shift down arrow comma and our product is going to be Kensington
Comma Criteria Range two.
Well, that is going to be the year control shift down arrow comma.
And the year is going to be determined by whatever is incel a 15.
F4 to lock.
Close the bracket, enter, and there we have our results, let's copy this down now we're going to
do the same for cogs and profit, and both of these are going to be some IFS calculations, so pretty
much the same as what we've done here.
The only difference is that instead of selecting the country column, we're going to select the product
column.
So let's type in equals some ifs.
What is some range, while this time with some in the COGS?
So we're going to go back to pivot tables and we're going to choose the COGS column.
Control shift down arrow to select all comma.
What is our first criteria range?
Well, our criteria range is going to be the product control shift down arrow to select that range.
The criteria we go back to calculations is the product criteria range to, well, that is going to be
the year again.
So back to pivot tables, select the control shift down arrow comma.
What is the criteria?
Well, the criteria is going to be generated by sell a 15.
EF four, to lock close the bracket, enter and then we can copy that down, and we're going to do exactly
the same for the profit.
It's the same some ifs calculation, but the sum range is just the profit column as opposed to the Cox
column.
So I'm going to leave you to do that and have a little practice, and I will join you in the next lesson
to continue this dashboard.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.