All language subtitles for 2. Assembling a Dashboard - Part 1

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.