Transcripts
1. Introduction: Four Hello, and welcome to visually stunning Dashboards intermediate
level cores. Now, following the
remarkable success of our visually stunning
dashboards basic level cores, we are thrilled to
present you with the next exciting chapter in your data visualization journey. In today's data driven world, the ability to transform
raw data into captivating, insightful and
interactive dashboards is a skill that sets you apart. Hello. I'm Shas Dak, and I'm excited to
be your mentor as we explore the fascinating world of dynamic dashboard creation. In this intermediate
level course, we'll take a deep
dive into the art of data visualization,
interactivity, and aesthetics. The basic course was a
game changer for me, but this intermediate
level is where I'm taking my data storytelling
skills to new heights. In my 20 years of experience, I've learned that data is
in just about numbers. It's a powerful narrative
waiting to be told, and this course is helping me tell you those
stories brilliantly. Discover advanced
charting techniques, master the art of integrating
data from diverse sources and infuse your dashboards with captivating interactivity. And don't worry about
device compatibility. We'll show you how to make
your dashboards responsive, ensuring that they
shine on any screen. Whether you are a data analyst, business professional, or
an aspiring data artist. This course is tailored
to amplify your skills and empower you to create dashboards that drive decisions. Together, we'll
explore the magic of real time data integration, advanced chart libraries, and the psychology behind
effective data presentation. Collaborate with
fellow learners, engage in hands on activities, and receive expert
guidance to craft dashboards that leave
a lasting impression. As you progress, you'll have
the exciting opportunity to replicate the
captivating dashboards we showcase throughout
the course. So prepare to unleash
your creative genius, visualize data in
extraordinary ways and make a significant
impact on your insights. So are you ready to embark on
this accelerating journey? Enroll now, and let's
embark on the path to creating visually stunning
dynamic dashboards together. I'm super excited to see you inside the course.
See you there.
2. Lecture 1 Dropdown Dashboard: Hey, welcome back. So the first chart
that we will learn in this entire lecture series is what I refer to as a
drop down dashboard. Okay? And what you see
on your screen right now is the dropdown dashboard
that I'm talking about. Now, why am I calling this
a dropdown dashboard? Let me ex that first. Okay? So what I have over here is I have a sales data for
my team over here. I'm assuming that my team
works all seven days a week, and these are the sales figures for each of the team members. Now I want to go ahead and
create this beautiful bar and line graph based upon the drop down that I
have created over here. What I've done over
here is I have created a small drop
down over here. Whenever I change the
name of my team members, you will observe that the
charts will change accordingly. For example, if I go
ahead and select, let's say Spider Man,
you will observe that this particular row has picked up the data
for Spider Man. And accordingly, my
chart has changed. Many a times, we
have to go ahead and create these features
within our dashboards, wherein you get dynamic outputs based upon the input
that you provide. Whenever I'm going ahead and
changing my input over here, you will observe that my output is changing and accordingly, even my charts are changing. This is what we will learn in this particular
lecture over here. Let us go ahead and construct this dropdown
chart right from scratch. Now in order to go
ahead and do that, what I'm going to
do is, I'm going to copy this entire raw data. And I will paste it on a
blank Excel sheet over here. I have copied this raw data. Now let's go ahead and start working on the data that
we have at our hand. First of all, what I need
is the employee name. I'm going to just go ahead
and select the cell, and I'm going to type employee. Next, in this
particular cell year, I need my drop down, the drop down of my
team members' names. What I'm going to
do is, I'm going to select this particular
cello here. I'm going to click on Data, and then I will click
on data validation and click on data
validation one more time. Over here, where it says, Allow, I'm going to click
on this dropdown and select the option
called List O here. I'm going to click on List. Now it is asking
me for the source. I'm going to click on
this arrow over here, and I'm going to select
my source data from here, which is the name
of my team members. I will just hit Enter
and then click on. Now you will observe
that a dropdown has been created over here with
the name of my team members. I can just go ahead and
select any name of my choice. Now next, I need the data based
upon the day of the week. I'm going to go ahead and select this Sunday to Saturday over here and I'm going
to paste it over here. Now, what I'm going to do is I'm going to make use
of the match function. Now, why am I making use
of the match function? You'll understand that as
we go ahead and use it. Let's start using
the match function first and I'll explain why I'm going ahead and making use of the match function
specifically. I'm going to say
is equal to match. I'm going to open
the brackets and I'm going to look up for the
value that is Sunday. Then I will give a and where do I want to look
this particular value? I want to look this
particular value in this particular
array over here. What I want to know is in
this particular array, at which particular position
the value Sunday appears. Since the array is
going to be constant, I'm going to press
F four and I'm going to freeze
this entire array. Then I will give a comma, I'm going to select
this particular option that says exact match, and I will close the
bracket and hit. Now, what it is telling me is in this particular
array over here, Sunday appears in
the first column. Similarly, if I go ahead and just drag this array over here, it says that Monday appears
in the second column, Tuesday appears in
the third column, and so on and so forth. Okay. Now I will go ahead and make use of a small
trick over here. What I'm going to do
is I'm going to make use of the V look up function in combination with
the match function in order to get my
desired output. What is my desired output? Let's say if at all I have selected Black
Panther over here, I need this particular data, which is the data for Black
Panther to appear over here. For that, what I'm
going to do is, I'm going to say is
equal to V lookup. I'm going to open brackets, and I'm going to say we look up on the basis of
this lookup value. Since once again,
this lookup value is going to remain constant, I'm going to press the F
four key on my keyboard. Then I will give a comma Now it is asking me
for the table array. My table array
appears over here. I want Excel to pull up the data from this
particular array over here. Now, once again, this
array remains constant. I'm going to press F four once again to freeze my
rose and columns. Then I will give a coma. Now I'm telling Excel that
you will have to go ahead and select the
column index number based upon my match function. However, I'm going to add
a plus one over here. Now, why am I adding a plus one over here? Let
me explain that. I have gone ahead and selected this entire array over here. Now in this entire array, this particular column
is my column number one. The column for Sunday will automatically become
my column number two. Monday will become
column number three. In order to ensure that it picks up the column
number accurately, I'm just going ahead and
adding an additional value, that is plus one because I have an additional
column over here. Okay. If you have observed, my Sunday was at
the first position. But when I select this
entire array over here, my Sunday goes at
the second position. That's why I'm just
adding a one over here. Then I will just
go ahead and give a zero so that I
get an exact match, and I will close the
bracket and hit. Now you will observe that
it has picked up the value of Black Panther
as 76 for Sunday. All I need to do now is just go ahead and drag this formula, and you will observe
that it has gone ahead and picked up the
values accordingly. Now you will observe
that if I go ahead and pick up any value
in my drop down, it will pick up the sales
values accordingly. Most of my work is now done. I'm just going ahead and
giving it a nice border. I'm going to just go ahead
and select outside border. Let me just align the data because I know it's
not necessary, but I like keeping my
data neat and clean. Yeah, I got it aligned. Now what I'm going
to do is based upon this particular
data over here, I'm going to go ahead
and click on Insert. Go to recommended
charts over here. Click on this drop
down over here and select three
D column charts. Paste it over here. Once again, just select this
particular data over here. Go to Insert. And select line charts, and let us select stacked
line with markers, and I will paste it over here. Now, all I need to do is just format my charts
as per my needs. What I'm going to
do is, I will just select this particular
chart over here. Okay. Go to Chart Design and let us select this
particular design over here. Okay. Then I can just
go ahead and click on the plus sign over here and
remove the grade lines. What I'm going to do is, I will just right click on my chart. Click on Format Data Series. I will select this option
that says color and fill, and I will select this option
that says colors by point. And I will close this. I will just resize my
chart a little bit. Okay. I will just paste it over here and just resize
this as well a little bit. Then I will select the plus sign over here and go to
the chart elements, and I will click on
this particular option that says data labels. Similarly, I can just go ahead and click on the
plus sign over here, go to chart elements, and
select data labels over here. Now what I'm going to
do is I'm going to select this entire
sheet over here. Go to view, and then I will remove the
grid lines over here. I'll like my charts on
a plain white sheet. Last but not the least, now I need to go ahead and
give my chart a nice title. For that, what I'm going to
do is I will click on Insert, and then I will
click on Word ar. I will select the word
art of my choice. You can select any
that you like. You can just paste it over here, and just call this
earth drop down chart. Now what we will do is, we will just go ahead
and test our chart. Let me just paste this
properly over here. Now what I will do is, I will
go to the drop down over here and let's try changing
the names over here. You will observe that the
moment I change the names, my chart changes accordingly. This is how you can
go ahead and create a beautiful dynamic
dropdown chart. I hope you enjoyed this
particular lecture. In the next lecture, we
will learn to go ahead and create another beautiful
chart. I'll see you there.
3. Lecture 2 Target Vs Achieved Chart: Hey, welcome back. Now, in this particular lecture, we are going to learn
how to go ahead and create this beautiful target
versus achieve chart. Now, why am I calling this a
target versus achieve chart? Okay? So I'm just going to show
you the raw data over here. So I have this particular
raw data over here, wherein it is the data for 25 different employees
within my organization. And every single day, each of my employees have
a target to achieve. We can assume that this could
be a sales target as such. So from Monday to Friday, the target is around 200
for each of the employees. And on Saturdays and Sundays, the target is 150 each. And this particular data
represents the sales that have been achieved by each of the employees on any given day. So there could be certain days when the target was achieved, and there could be certain days when the target was missed. Okay? Now, based upon
this particular data, I have gone ahead and created this wonderful target
versus achieve chart, wherein if I go ahead and click on any of the
employee names over here, You will observe that
it shows the target versus achieved chart for
each individual employee. Okay? It shows the
corresponding data. For example, if I click
on employee number 24, it shows me that on Monday, this particular employee
achieved 193 sales, on Tuesday, he achieved 99, O Wednesday, he achieved 101 and
so on and so forth. Okay? And similarly, if I keep changing the employee
number over here, it will give me the
corresponding data. So in a way, it is
a dynamic chart, and we can add any number
of employees over here. So it can be 2050020250300,
any number, okay? And anytime we go ahead and click on this
information over here, it will show us the
corresponding data for that particular employee. So let us understand
how do we go ahead and create this
wonderful chart. So first of all, what
I'm going to do is, I'm going to select
this entire raw data, copy it onto a
blank Excel sheet. And what I'm going to do
over here is, first of all, I'm going to go ahead and create this drop down kind of thing that I have gone ahead
and created over here. Okay. So in order to do that, what I would do is, I will go to my developer tab over here. Okay? Now, developer
tab might be visible or might not be
visible in your Excel sheet. But if it is not,
then in that case, what you can do is you
can click on the file, go to Options, and then you can click on
Customized ribbon. You can come down to
this particular dropdown over here and select main tabs, and you will find that
developer tab over here. So in case you don't see
it in your Excel sheet, you can just click on Ad O
here. And then click on. In my Excel sheet, it
is already available, so it is showing up on
the right hand side. In case it is not showing
up on your Excel sheet, just add it, click on, and it should appear
over you, okay? Now, what I'm going to do is, I'm going to go ahead and click on this developer tab over here. Okay. So first of all, I'll just create a new Excel sheet. And then I will click
on the Developer tab. Click on Insert. And over here, I see this particular
option that says List box Form Control. So I'm going to go ahead
and click on that. Okay? And I will just go ahead and create a list box over here. Okay. Now I need to go ahead and add the employee details to
this particular list box. So what I'm going to do is
I will right click on it, and then I will click
on Format Control. When I click on Format Control, I see these two
options over here. One says input range, and the other one
says sell Link. So what I'm going to do
is, I'm going to go ahead and click on the input
range over here. Come back to my sheet one, and I'm going to select
this entire data from employee number one
to Employee number 25. Okay? And I will hit Enter. Okay. And now it is asking
me for a cell link. Okay? Now, what is
this cell link? We'll understand
later, but as of now, I'm just selecting this
particular cell over here, H one, and I'm going to hit Enter. And then I will click on. Okay. So now you
will observe that the list box has
been created, okay? And it has all my employees
from employee number one to employee number 25. Okay? If I scroll down,
the last employee over here is employee number 25. Now, let us understand, why did I go ahead and select this particular
cell over here. Okay? So if I go ahead and select this list box over here, and let's say I click on
employee number three. You will see that the number
three appears over here. Which means in this
particular list over here, employee number three
is at the third spot. Okay? If I click on employee
number seven, it shows up the number
seven over here, which means employee number
seven is on the seventh spot. Now, how am I going to
use that in order to go ahead and create my chart?
We'll see that later. So now what I'm going
to do is I'm going to select this thing over here. Okay? I will just format
this a little bit. I will just increase
the size of this. Okay. And then I will just go ahead and place it
over here somewhere. Okay. Now, in order to go ahead
and create my chart. I need three
different data sets. One, I need the day of the week. Then I need the achieved data. And then I need the target. Okay. Okay. I'll just make this a
little bold over here. Okay. Now my day of the week
data is available over here. So what I'm going to
do is I will just select this data, copy it. Come back to my sheet
to click over here, go to paste special. And over here, I'm
going to select this particular option
that says transpose. Okay. And now I need the
achieved data. Now, in order to get
the achieved data, I will have to use
the lookup function. But before that, let us go ahead and fix the
target data over here. Okay? And once again, I will make use of
the lookup function only because you can see that my data is actually arranged horizontally
in a row wise fashion. So I'm going to make use
of the lookup function. So, how am I going to do that? I'm going to say is
equal to lookup. I'm going to open the brackets. I'm going to select
the reference or the lookup value as Monday. I will give a comma.
Then I need the array, so my array is over here. I'm going to select this, and my data is in row number two. I'm going to give the
number two, give a comma, and then I will close this. And since my data range or my table array
remains constant, I'm going to select it and
press F. Select FO once again. And I will hit Enter. So now it says, for Monday, my target is 200. So I'm going to just
go ahead and drag this and it will pick up
the data accordingly. Okay? Now, what I need
is the achieved data. Achieved data for
what? Let's say I need to achieved data
for employee number one. So how am I going
to achieve that? What I'm going to
do is, once again, I will make use of
the lookup function. Open brackets. My lookup value will be once again
the day of the week, and then I will give a comma. Okay? Where is my table array? My table array is over year. Okay, I'm going to select
this entire table array, and I will freeze it. Okay, so I will just go ahead
and press the F four key. So my data is frozen. I will give a comma now. Okay. And now, what I need
to do is I need to go ahead and pick up the
data for employee number one. Okay? So in order to do that, I'm going to go ahead and give the reference of this
particular cell over here. Okay. And then I will give a comma
and give the number zero. Why did I give the
reference of one? Because employee
number one's data is in the first row, a? And the value
corresponding to that is available in my one cell, a? And then I will close the
bracket and hit Enter. Okay. But you will observe
that I got an error over here. Now, why did I get
an error over here? Let us look at the
sheet over here. Now, if you observe my
first two rows over here, are occupied with
some other data. So the first row actually
gives me the day of the week. My second row gives
me the target. So my first two rows are some additional rows before it gives me the data for
employee number one. So in order to fix that, what I have to do is just where I gave the
reference of H one, I have to go ahead
and give a plus two. Okay? Why? Because there are two additional rows which
have some additional data. So I want to pick up the data from row number three onwards. I will just go ahead and
give plus two and hit Enter. And now it says, employee number one on
Monday has hit 143 sales. So let's go ahead
and confirm that. So on the first day,
that is Monday, employee number one
had hit 143 sales. So my data is correct. Now, all I need to do is once again freeze this
particular see as well, hit enter, and then just
drag this particular data. And you will observe that it picked up the corresponding data for employee number one
from Monday to Saturday. Okay? And now if I go ahead and change any
value over here, you will observe that it is giving me the
corresponding value. Now my job is simple. All I need to do is go
ahead and create my chart. And for that, what
I'm going to do is, I'm going to select this
entire data over here. Go to insert, go
to column charts, and I'm going to select
this particular chart over here, stacked column chart. Okay? And I will paste
it somewhere over here. I will just increase the
size of my chart. Okay? And let's just go
ahead and format the chart now in order to
get us the required output. Now, but if I observe
this particular chart, you will observe
that if I click on this particular value
over here. Okay? It is going somewhere
close to 300. Now, why is it happening? Because what has happened
is in my stacked chart, it has gone ahead and added
the two values, 101 and 200. So the value over here
is approximately 301. Okay, but that is
not what I want. I want my data for Target
and achieved separately. Okay? So what I'm
going to do is, I'm going to select this
particular data over here. Okay, the orange bars
I'm going to select, which correspond to my target. Right click on it,
and then I will click on Change
Series Chart Type. Okay? And now what I'm going to do is this particular target chart
that I have over here, I'm going to put it
on a second re axis. And then click on. Okay. Okay. So now, what I'm going to do is, I will just go ahead and right click on the
Oge chart once again. Click on Format Data Series. And now this particular gap
width that I see one 50%, I'm going to change it to 50%. Okay. And then I will go to the pill and color
option. Go to Phil. I will select a light
green shade over here, and I will change the
transparency from zero to around 40%. Or let's say 48%. Okay? Now what I'm going to
do is I'm going to select my blue charts over here, which corresponds
to my achieved. Right click on it, and then
click on Format Data Series. I'll go to the color and
fill options, and this time, I'm going to go ahead and select the dark green shade
over here. Okay. And now I will just go ahead
and give this chart a title, so I'm going to call it a
target versus achieved. Chart. Okay. And now I will go ahead and format my chartl
a little bit more. So what I'm going to do is,
I will select the chart. Click on the Plus sign over here and click on
chart elements. Okay? I will remove the
grid lines first of all. Okay? And now what
I'm going to do is, I'm going to go ahead and select the dark green part of my chart, which is the Achieve chart. Okay, click on the Plus sign. And then click on
Add Data labels. Okay, so my data labels
have appeared over here. And I don't need the
axis over here now. So what I'm going to
do is, I'm going to go ahead and click
on the Plus sign, and I'm going to remove
the axis as well. Okay. Then I will just go ahead and format my
chart a little bit. I'll just make it a
little bigger in size, and I will also go ahead
and change the size of my list box corresponding
to the size of the chart. Okay. I will just go ahead and arrange the title correctly. And now I will just go ahead and right click on the chart. Click on Outline. And I will give a nice black
outline for my chart. And I will give the outline a little bit of
weight as well. Okay. And now what I'm going to do is, I'm going to select this
entire sheet over here. Click on the view option, and I'm going to
hide the grid lines. Okay? And I will just format
this particular data, so it looks neat and tidy. So let's go to the home tab and give it
a nice color as such. Give it a nice border as such. Okay, so I'll just go ahead
and give it a border. We'll just arrange
the data properly. And last but not least, we can just go ahead
and hide this. What we can do is
we can just change the color to maybe white, so this particular one
data will not be visible. And last but not the least,
we'll add the title. So we'll click on Insert, click on Word art, select
any word art of your choice. And now, what I'm
going to do is, I'm going to go ahead
and call it a target, versus achieve chart. Okay. And now we'll just go ahead
and test the one last time. So if I go ahead and click on any of the employees over here, you will observe that my changes corresponding to the
employee data. Okay. So this is how we can
go ahead and create a wonderful target
versus achieved chart. I hope you enjoyed this lecture. In the next lecture, we'll
learn how to go ahead and create another beautiful
chart. So see you there.
4. Lecture 3 All in One Chart: Hey, welcome back. Now, in this particular lecture, we are going to learn an
interesting kind of a dashboard. Something that I refer to
as an all in one report, or you can see that it's been called an
annual report over here. But usually, I prefer calling it an all in one report
specifically, because it takes up all the, all the cumbersome data that is available in your data sheet, and creates a one page snapshot
of the entire dataset. Now, before we go ahead and jump into creating
this dashboard, let's first understand what
is this dashboard all about. So over here, I have
this raw data over here. And this is a sales
raw data and runs into approximately 10,000 records,
around 9,865 records. So basically, this is
a day wise sales data, wherein first column,
I have the date, then I have the week,
I have the month. I have the quarter,
I have the year. I have the employee name, the supervisor name, and the sales done for
that particular date. And as you can see, this data is running into data
for three years, so 2015, 2016, and 2017, a, each employee belongs to
a particular supervisor, and you can see that every year, the data is further broken down into quarter
wise data as well. Okay? And what I've done over here is I have gone ahead and created a one page snapshot of the entire data
that I have over here. So, for example, if I go ahead and remove all
the filters that I have over here, Okay. You can see that I can see the snapshot of all
the three years. Okay? 2015, 2016, and 2017. Let's say I click on a
particular year over year. The moment I click on
a particular year, it shows me the breakdown
for the quarters. So this is a quarter wise
breakdown for the year 2015. And now if I go ahead and click on any of the
quarters over here, let's say I click
on quarter two. It shows me the data for
April, May and June. That is the second quarter. This data can further be
drilled down supervisor wise, so I can click on a
particular supervisor's name, and this is showing
me the data for Mr. Avenger for 2015
and second quarter. And then I can even further slice down this
data, employee wise. So this is showing me
Captain America's data for April May and June, for the year 2015, for the second quarter. So basically, I can
go ahead and dissect my data and look at the data
at a microgranular level. And based upon this, I can go ahead and make some
good decision making. So basically, what I'm doing over here
is I'm taking the data for three years and creating a one page snapshot for my data. You can also see that
further I can even drill down my data month
wise and even day wise. Okay? So let us go ahead and
start constructing this wonderful all in one
report right from scratch. But to do that, first of all, I would need the raw data, and I would copy
this entire raw data by pressing the shift
control and the down key, copying it to a
blank Excel sheet. So I've gone ahead and copied my data onto a blank
Excel sheet now. And now, let me
start constructing our all in one chart
in a step wise manner. So the first thing that I'm
going to do is I'm going to select any particular cell
in this particular data. I will click on Insert, and then I will click
on Pivot table. So the Pivot table option is available over here, so I'm
going to click on that. And I'm going to select
this particular option that says new worksheet. So basically what
I'm going to do is, I'm going to go ahead and create my Pivot table on a new
worksheet altogether. So I will click on. Now, before I start
constructing my pivot table, the first thing that
I'm going to do is, I'm going to go ahead and
rename this particular sheet, and I'm going to
call it a Pivot. Okay, why I'm doing this, you'll understand that later
in this particular lecture. But as of now, let's start constructing our
Pivot table first. So the first thing that
I'm going to do over here is I'm going to
select the year over year. And then I'm going to
select the sales figures. Okay? So my first
pivot table is ready. Now, I need to just go ahead and format this pivot
table a little bit, so I'm going to select this
Pivot table. Click on Design. And I will click on
this particular option that says Grand Totals. And I will say of
for rosin columns, because I don't want the grand totals to appear over here. And the next thing
that I'm going to do is I'm going to
click on Report layout, and I'm going to say show this data in a tableular format. So now we're showing me the year and the sum of sales. Okay. Now what I'm going to
do is, I'm going to copy this data over here, and I'm going to
paste it over here. And this time, I just need to format this pivot table
a little bit more. So what I'm going to do
is, I'm going to push the year to the filter
section over here, and over here, I'm going
to drag the quarter. Okay. So now I have the
quarter wise data over here. I will once again copy this and I will
paste it over here. Okay. This time, in this
particular pivot table, I'm going to remove the year. I'm going to put the
quarter over here, and I'm going to
drag the month down. This gives me the month
wise breakup of my data. Next, what I'm going to do
is I will once again copy this data, paste it over here. Okay. And what I'm going to do is, I'm going to remove
the quarter from here, put the months over here
in my filter section, and I will take the weeks under the row section over here. Okay? So now it is giving
me a week wise break up. Okay, a week wise breakup
of the entire sales data. Now, the next thing that
I'm going to do is, once again, I will copy this
entire thing over here. Okay? And then I will
just paste it over here. Okay. Now, the only
modification that I would make over here now is, I
would take the week. I would remove the months from my filter section and then take the week into
the filter section. And then I will just drag the dates in my row
section over here. Okay? And all I have
to do over here is, I have to just remove
these years and quarters, because by default,
it gets grouped, and I will just right click on any of the month over here, and I will click on Ungroup. So it shows me the data
on a day wise format now. Okay. So we're done till here. Now, the next thing that
we're going to do is start creating our
slicers. Okay? And in order to go ahead
and create slicer, I would click on any of the
pivot tables over here. Click on Insert, and then
I will click on slices. And I need the slicer week wise, month wise, quarter
wise, and year wise. And I will click on. Okay. Okay, I will just place my slices just next
to each other. Okay. And now what I'm going to do is, I will select each of
the slicer over here, and I will click on
Report connections. And I will ensure that all the pivot tables
mentioned out here are connected with each of the slices that I have
mentioned over here. So I will just go ahead and create a connection over here. I will click on
Report connection, and then I will click on all
the pivot tables over here. Okay. Click on. Select
this pivot table, click on Report connections. Once again, put a check mark on all the pivot tables,
and then click on. Okay. Once again,
report connections. And then I will put
a check mark. Okay. So now, if at all, I make any changes to any of
the slices over here, you will see that
my pivot tables are changing accordingly. So that was the ultimate
motive of going ahead and creating
connections between them. Okay, so I'm going to remove
all these filters for now. Okay. And let's proceed further. So now, before we start
constructing a chart, I need some supporting data. Now, what is this supporting data that I'm looking out for? So first of all, I need a data called as
the first column. Now, what is this first column? First column is where
I will tell Excel, where will my data start from? Okay? And then I would need something called
as the last row. Now, what is the last row? Last row is where
I will tell Excel, where will my data end? And in order to achieve that, I will have to make use
of the if function, and my if function
goes like this. So I'm going to say if
this particular cell, n one, is not equal to all Then start my column from M one. But I have to specifically mention the word
column over here. I will say column. And I will put my M
one inside brackets. Okay. Then I will say
if open brackets, and if this particular value, the k one value is
not equal to all Then start my column from j one. But I have to mention the
word column over you. However, if my data over year, in the one column is not equal to all Then, I want to start my data
from column G one. Okay. Once again, if the
data in e one is not equal to all then start the data
from column D one. And if everything
else is a false, then start my data from A one. But I'll have to mention
the word, column over. Okay. Close the
brackets and hit. Okay. So now you
will observe that because all the first four
conditions are nullified, because in none of the
columns over here, it is not equal to all. So therefore, my data
starts from A one. But if at all, I go ahead and select any particular
year over year, let's say 2015, then this
particular column over year. Okay, this particular cell
over year is not equal to all. And therefore, my data will start from Column number four. So basically what
he's telling me is the first column
from where it will pick up the data is from
Column number four. Okay. Further, if I click on quarter, you will observe that it
will start picking up the data from Column
number seven, Okay, because my data starts
from this particular column. Okay? So that was the ultimate
purpose of going ahead and setting
up my first column. Now, I have to go ahead
and also mention, where will my data end? And for that, I'm
going to make use of the last row column over here. Okay? So in order to go ahead and mention my last
row over here, what I'm going to do
is, I'm going to come to this particular cell itself. I'm going to copy this formula. Then I will come to this
particular cell over here, and then I will just
paste the formula. Okay. And now I just need to go ahead and make a small
modification over here. Okay. Now let's understand what that modification
is all about. So when n one is
not equal to all, I mentioned that my data will
start from column M one. Okay? But where will it end? And where will be my sales data? My sales data will
be in column n over. Okay? And where will it end, where my column n will end. So in a way, I have
to just go ahead and take a count of my column n. So how am I going to do that? I'm just going to
go ahead and remove this column M one from here. And instead, write
count a open brackets, and I will just
give a reference of this particular
column over here. Okay? I will come to this
particular column over here. I will remove column J one, and I will just say
count A open brackets, and I will give the reference
of this particular column. Okay? When I come to this
particular column over here, once again, I will just
remove G one from here. And I will say count
A, open brackets, and I will give the reference of column. Close the brackets. Then I will come to my e column, and I will remove
this from here. I will say instead
of column D one, give me a count of
the entire e column, I will close the brackets, and over here, instead
of column A one, I will say count A, open brackets, select
this particular column. But I have to notice
one thing over here. Whenever I selected any
of the columns over here, my data started from
the first row itself. But when it comes
down to column A, the first two rows
are blank, Okay? Which means they will be
counted additionally. And for that, what
I'm going to do is, I will just give a number
plus two at the end, and then I will close the
brackets and hit Enter. Okay. Now, if I go ahead and
remove all the filters, you will observe that
my data will start from Column one end on
row number six. But however, one more thing, I just missed out over you. Whenever we select any of
the columns over here. We also have an additional
blancro in each one of them. Okay, so I have to take
care of that as well. So when I added a
plus two over here, I have to also add an
additional plus one for all the other count a functions that I've
used in my formula. So I will say count n plus one. Count k plus one. Okay, count plus one, and count E, plus one. Then I will hit Enter. Now you will observe
that my data, because none of the
filters are selected, starts from column one and
ends on row number six. But let's see what
happens when I select any particular
year over year. So if I select 2015, you will observe it starts
my data from column four, which is the year column, and ends on row number seven. Okay? If I go ahead and select
any particular quarter, you will observe that my data actually started on
Column number seven, but ended on row number six. Okay? So that was the ultimate
motive of going ahead and mentioning the first
column and the last row. Okay? So let's proceed further. So now to proceed further, I would need two more
sets of supporting data. One is my x value data. And then I would also
need my y value data. Now, what is this x
value and y value data? When I'm plotting my graph, all the data that is plotted on my horizontal axis
is my x value data. And all the data
that gets plotted on the vertical axis will be
referred to as my y value data. And specifically, because our
chart is a dynamic chart, and the values of x
value and y value will keep on changing based upon
the filters that we select? We have to go ahead and make use of the address
function over here. Okay. So let us understand how do we go ahead
and make use of the address function
in order to get our x value and y value. So my address function goes
something like this, okay? So it says, is equal
to address Okay, I'll open the brackets. It's asking me for
the row number. Now, all my data will always
start from row number four. Okay? So what I'm going to do
is, I'm going to specify the number four over here
and then give a coma. Then it is asking me
the column number. Where is my data go to end? My data will always end on this particular
value over here. That is. Okay? Then I will give a
comma once again, and then it is asking
me whether I want an absolute value or a
relative value or what? So I'm just going to select
the absolute value over here. Okay. And then I will go
ahead and close the brackets. And now what I will
do is I will type in and open double quotes, give a colon, give double
quotes once again, give a function once again, and then I will type address. Where is my X value
going to end? My row will end at the last row, and my column will once
again end on Q two. Once again, I'm looking
for the absolute value. I'm going to select
the number one and close the brackets
and hit Enter. Basically, what it
is telling Excel is my data range starts from
A four and ends at A six. That is my x value. Now, let us go ahead and
define our y value as well. What I'm going to
do is I'm going to copy this entire function. Go to this particular
see over here, and I will paste this particular function
once again over here. Okay. But now for my y value, I need the sales figures, right? And where will I find
the sales figures? So let us look at this. If at all, my column is M. Okay, then my sales data will be
in the column next to it. Okay. So if my first column is one, then my data will be available. That is my sales data will be available in Column number two, which means plus one. Okay? So all I have to do over here is in this
particular formula, wherever I see my columns, I'm going to just add a
plus one over here, okay? Even for this particular value, I will just go ahead
and say plus one. Okay? Why I did this? Once again, I'll explain this. If at all, my data
starts over year. Where will I find
my sales values, next to the column, next to M. If my data starts at the month, where will I find the
data for my sales? Column next to it. And how will I go to
the next column by just adding an
additional one to it? Okay? And I will hit. Enter. Okay. Now, one more thing
that I have to do is I have to tell Excel, where is this X value
and y value located? And for that, if you remember, in the beginning, we rename this particular sheet as Pivot. Okay? So we will tell Excel that this particular data
is available on the sheet called Pivot.
And how do we do that? So what I do is, I
click over here, and what I'm going to do is, I'm going to type double codes, Pivot, give a exclamation mark. Give double quotes once again, and give the n function. Okay and hit Enter. Similarly, I will go to
my y value over here. I will open up double
codes, Type en pivot, give a exclamation mark, close the doublees, and then I will give a
n function and hit. Er. Okay. So now basically, what I have told Excel is my X value and my y value
are mentioned over here, and they are available
on a sheet called Pivot, which is this particular
sheet over here. So when I start
creating the chart, Excel will know where to go ahead and find the first column, where to find the last row, where to find the x value, and where to find
my y value. Okay? Now, I would need
just one last thing in my supporting data, and that is my chart title. Why do I need to go ahead and mention my chart
title also over here? Because my chart
title will keep on changing as that too is dynamic. If at all I'm creating
a quarter wise data, then it will say
chart by quarter. If at all I'm creating
a data by month, then it should say
data by month. If at all I'm going ahead
and creating it by a week, then it should say chart by
week and so on and so forth. So to do that, what
I'm going to do is, I'm going to say is equal to. I'm going to open brackets, and I'm going to say
chart by give a space, give double quotes, give a
and function after this. And then I'm going to make
use of the indirect function. My indirect function
goes like this, indirect, and I will
now give the address. I will open the brackets, and my row always starts
from row number three. I'm going to say row number
three, give a comma. And where is my column? My column will be
in the cell number. I'm going to give this
particular reference over here. Once again, I will give
a comma once again, and then I will select
the value as absolute, and then close the
bracket and hit Enter. Okay. So currently, the chart
has been prepared by year, so it is saying chart by year. If at all, I click on this
particular year over year, immediately it goes
to chart by quarter. Okay? So I have gone
ahead and even made this particular
thing as dynamic. So now that my reference
data is ready, the next thing that
I have to do is give the names to
my reference data. And in order to do that, I'm going to make use
of the formula stab. So I will click on formulas, go to Name Manager, and I will click on new
Name Manager. Okay? And now what I'm going to do is, I'm going to name
this as X value. Then over here, I'm going to
say is equal to indirect, pen brackets, and
where is my x value? My x value is in this
particular cell. I'm going to go ahead and
give this as a reference, close the brackets
and hit Enter. I've gone ahead and created a
new name for the reference. Now I will go ahead and create one more. I'm going to say new. And this one, I'm going to
go ahead and call as sales. And where is my sales value? Sales value is in the y
value section over here. I'm going to say is
equal to indirect O pen brackets, and I'm
going to go ahead and give this particular
cell as the reference, close the brackets
and hit enter. And then I will click
on. Close. Okay. So now that we have
gone ahead and created all the raw material that we need to go ahead and
construct our chart. Let's start constructing the
chart right from scratch. So the first thing that
I'm going to do over here is I'm going to click
on the plus sign over here, and I'm going to rename the
sheet as the chart sheet. Okay. I will come back to my
pivot et over here, and I will go ahead and
create two more slices. So in order to do that,
I'm going to click on Insert and then click on slices. I'll just select any of the
pivot tables over here. And then I will click
on slices once again. And this time, I need two
more additional slices, one for the employee name and one for the supervisor name. So I'm going to go
ahead and click on. Okay. Once again, in
order to use the slices, I will have to build
connections for them. So I will select
each of the slices, go to report connections, and I will ensure that
they are connected to all the pivot tables
mentioned out here. Similarly, I will go to the supervisor slicer and then click on
report connections, and I will connect it to all the pivot tables
mentioned out here. Okay. And now what I can
do is I can just go ahead and select all
the slices together. Copy them, and I will paste it on my chart
sheet over here. And now I need to go
ahead and arrange them as per the
requirement of my chart. Okay? So let's go ahead and
format them one by one. So first of all, I would need
the year chart over here. I'm sorry, the year
slicer over here. So I will just go ahead
and place it over here. Select the slicer,
go to Slicer tab, and I will go ahead and
increase the columns to three, and I will resize this. Okay. And then I will just go ahead and
change the format. Next, I will need the quarter. I will set the
quarter slicer over here and change the
column size to four. Resize it, and
change the format. Next, I would need the month. I will place month over here. And then I will just go ahead and increase
the columns to three, resize it, and then
once again format it. Similarly, I'll do that for the rest of the
slices as well. I'm just going ahead and
formatting them one by one. Weeks, we can just go ahead and change it to maybe five columns, increase the size,
and format it. And then I need the
supervisor name. And I can place
the employee names at the extreme right over here. Let's quickly go ahead and format the supervisor
slicer as well. Okay. So all our
slices are done. Now we can start inserting
the chart over here. So for that, what
I'm going to do is, I will click on the Insert. I will click on the
Column chart over here. Okay? And I'm going to select
a three D Column chart, and I will place it
somewhere over here. Okay. And now I need to
go ahead and start inserting the data for my chart. So for that, what I'm going to do is I will right
click on the chart. And then I will click
on select data. That will just pop up a screen
called Select Data Source. And what I'm going to do is, I'm going to go
ahead and click on the ad button over here. Now it is asking me
the series name. Okay. And the name for
my series is sales. So I'm going toname it as sales. And now it is going ahead and asking me for the series value. So over here, what
I'm going to do is, I'm going to type pivot
give an exclamation mark. And then I will type the F
three key on my keyboard. Okay? And the moment I did that, you will observe that all
the names that I have gone ahead and created previously
have popped up on my screen. Okay? And my sales data is
available in the sales. Wherein I went ahead and
gave it a name as sales, wherein it will
pull up the value, that is the y value that
we mentioned earlier. Okay? So I'm going to double click on sales, and then click on, Okay. So the moment that I did that, you will observe
that it has popped up a column chart on our screen. Okay? But if you look at
the bottom of the screen, it shows numbers like one, two, three, four, five, wherein, it should ideally be
giving us the x axis. So, for example,
if we go ahead and remove all the filters
over here, ok? So ideally, when all the filters are cleared on our X axis, it should give us the year, like 2015, 2016, and 2017. But in this case, right now it's only showing
us the numbers. So let's go ahead and fix that. So once again, I will right
click on the chart over here, and then I will click
on Select Data. And this time, I will click on this particular tab
called as Edit. Okay? And once I click on Edit, another screen pops up, which says Access labels. And it is asking me
for Axis label range. So once again, over here, what I'm going to do is,
I'm going to type Pivot. And give an exclamation mark, and then punch in
the F three key. And this time, I
need the x value, the x value that we
mentioned earlier. Okay? And that is what
will give us our x axis. So I'm going to go ahead and
select x value and click on and click on one more time. The moment I did that,
you will observe that now on our X axis,
we have the year. If at all, we go ahead and
select any of the slices, you will observe that
accordingly at the bottom, it is showing us
the necessary axis. So when we selected 2015, it is showing us all
the four quarters. If at all, I select
any of the quarters, it will show us the respective
name of the months. Okay. Now, our chart
is almost ready. All we need to go ahead and do is formatt it a little bit. So let's start
formatting a chart. So first of all, what
I'm going to do is, I'm going to select this
entire sheet over here. Click on the view tab and
hide the gridlines, okay? So that makes the entire
sheet wide and clean, and I love to see my
dashboards that way. So now I will just select
the chart over here, click on the chart elements, and I can remove the gridlines. Now, let's go ahead and select and IS design for our charts. For that, what I'm
going to do is, I will select the chart, Bot Chart Design, and let's select this particular
design over here. Looks neat and tidy. Let's go ahead and format
this a little bit. So in order to do that,
what I'm going to do is, I will just select the chart. Okay, right click on it, and then I will click on
this particular option that says format data series. Okay. You will observe that a
format data series table or a format data series menu has popped up on the right
hand side of the screen. Okay? So what I'm
going to do is, I will come down to
this particular option that says column
shape over here. And under Column shape, I will go ahead and
select cylinder. Okay? So you will observe
that the bar graphs are now changed to a
cylinder shape, okay? And now I will go to the fill
and color option over here. And then I will select this particular option
that says colors by point. Okay? And then I will
just close this. Now, also, I need to go ahead and make my chart title dynamic. Okay? Why? Because
let's say if at all, I'm looking at the
data year wise, then it should say
sales by year. If I'm looking at the
data as per quarters, then it should say
sales by quarters. So how do I go
ahead and do that? So in order to do that,
what I'm going to do is, I'm going to go
to my formula bar over here, click on equal two. Go to my pivot sheet, and go to my chart
title over here, wherein I have already
gone ahead and created a dynamic chart title. And I will hit. So now
it says Chart by year. Now, if at all, I go ahead
and change my filters, even my chart title will change accordingly. So let's try that. So when I select a
particular year and since it is showing me
the data quarter wise, it says, Chart by quarter. Okay? Let's go ahead and
test our data over here. Okay, let's select a
particular quarter. And you can see that it
is showing the months on the x axis and
respective sales data for those particular months. And accordingly, even my
chart title has changed. Similarly, I can go ahead and see the data supervisor wise or even advisor wise or
an employee wise. Okay? So we have almost completed going ahead
and creating a chart. The only thing
remaining is going ahead and giving it
a nice title now. So in order to do that, go
ahead and click on Insert, go to WordAt, and select a
word outart of your choice. Okay. So you can call it
an annual report or an all in one
chart, if you want. So I'm going to go ahead and
call it an all in one chart. Okay. And one last time, before we go ahead and close the lecture, let's go ahead and test
our data one more time. So I'm removing all
the filters out here. And you can see that it's
showing me the year wise data. Let's select a particular year, showing me the
quarter wise data. If at all, I go ahead
and select a month, it is showing me
the week wise data. And it is also showing me the data supervisor
wise and employee wise. Okay. So that's how we go ahead and
create an all in one chart or something that
we also refer to as a one page snapshot chart. So that brings us to the
end of the lecture series. I hope you had a fun time creating these
wonderful dashboards. And I would highly recommend
you go ahead and complete the assignments and look at the resource sheet
provided with the course. I hope to see you in the
next lecture series, which I will be launching soon on advanced Excel dashboards, till then, happy learning,
and God bless you.
The Guruskool, Data Analytics & Business Intelligence Leader
