- Today I'm going to show
you how to build dashboards in Excel with a very
straightforward technique. I use this approach almost
in every single project that I worked on during my
time in consulting at McKinsey. The dashboards that I created this way not only were super
straightforward to create and I will show you in this video how exactly you can create
it in only 15 minutes or so but they will also regularly
impressed both clients and also other team members,
because they usually pretty cool, super helpful,
super straight forward. So welcome to another coffee break here on my channel, Firm Learning. Help yourself to a coffee,
my name is Heinrich and on my channel, Firm Learning, I want to help you to become successful in the first years of your career. I'm a former McKinsey consultant. I spend about six years with the firm and for sure Excel was one
of the two words I used most. And I'm very excited to teach
you about some of the skills that I found most useful
during my professional life. If you're interested in
this type of content, check out my channel, because I already did several other videos on Excel tips or tricks like this. And if actually interested to
play around with the dashboard that I created yourself if you maybe just wanna
use it to code along try it out for yourself, try it a bit. Maybe also see how exactly I did something and maybe you couldn't follow
a specific part of the video. I'm offering this very dashboard that I'm creating here with you today for free as a download. If you're interested in that, check out the link in
the video description to download this dashboard for you and then try it out and
play around with it. And I would like you to introduce you to the sponsor of this video, DataCamp. DataCamp is an online learning platform where you can improve
your data analysis kits. And you know that this is something I talk about all the time. This is really a crucial
part of the skill set if you want to succeed
in any consulting role not just in many roles, in industry, and for sure in banking as well. With DataCamp there are no
previous data skills needed to get started. You can learn from things
as basic as Excel 101 to more advanced technical things like for instance, database
manipulation and SQL. So for sure, if the things
that are showing you here in this video are over your head, you really have no idea
what this is all about. There are some also very
introductory courses on Excel and DataCamp,
which I trust can help you. They have over 300 courses
with Interactive learning and you can also learn on-the-go with the mobile app that they have. Personally, I very much
liked the introduction to SQL course, which already
helped me several times on my own projects when I needed
to interact with databases. So give it a try and I trust
that working with DataCamp, indeed can fast track
your data analysis skills. And these for sure are so
crucial in any consulting role but also many other related roles as well. So if this now peaked your interest and you would like to try out DataCamp, then invest in yourself. Use my link to DataCamp
in the video description and try out the first chapter
of every course for free. So let's start by looking into what we want to build in the end. And I'll put this here
into the reference sheet. This is pretty much what we want to build. And you here a pretty basic dashboard. Of course, this does
not look overly complex. So I did not wanted to
make the video too long. But the key thing is that if
you understand how this works, you can pretty easily expand into much more complex
extents of dashboards. Then of course, it's
also not really difficult to then just add some additional
charts in there as well. And the key idea is that this could, for instance, be a retail store, and then there are the store
selection, there's a dropdown where you can select different stores and then all the numbers
update accordingly. I believe just due to my screen recording that you cannot see the
overlay of the dropdown but if you then download the file then you will see just a normal dropdown that appears with all the stores. So this is it. Again, pretty straight format but this type of dropdown-based modeling, this type of dropdown-based dashboards, this is really important. And I did this so
frequently in consulting, just extremely helpful. But let's get started and let's try to just
build this here together. So I created an empty sheet and now I'm starting to formulate. I always like to create these
little edges at the end, I believe it always looks
a little bit cleaner instead of just starting
off from the beginning. So I'm just looking back
at the reference sheet to see how exactly it's
supposed to be set up. So just to copy the text and so on. So dashboard for the sales performance. So in this example, the
different items that you select in the dropdown on different stores, but, of course this
could easily be a company where they look at
different product lines, so different geographies,
different business units, right? So all of these things
could be easily built with such a dashboard. And trust me, this
would be really helpful, really insightful to many companies. Now here, I'm just creating
the front-end, right? I always believe it's helpful to start to create the front-end, to
start to create the things that then are supposed
to come out in the end. And this often helps you, at
least me later to then fill it. And now the fields that
the data should contain dynamic content are already highlight them with a border in order now for me later then to make it more
clear what is expected. Now, I copied this border
from top to bottom. I often believe it is easiest
if it had already copied it so that it has exactly the same height. This always makes it look
a little bit cleaner, and now I am coloring it just to create this little head area. And now I am freezing
it so that I can scroll. And especially if now the dashboard would be a little bit
bigger, a bit more extensive then it might be helpful to really fix this header segment over there so that you can still scroll
down and still see data. Now, again, copying the
elements, revenues, and margins just typing here, the text, nothing fancy happening for the moment. So this is the revenues
and margins section. We have the current trading so the current rev, net revenues,
and then also the profit as well as the margin that is created. Okay, so next is then that we also wanna
expand this a little bit just that it looks a bit more
like the original example in the reference sheet. Now we are boarding this so far so good. And now let's create
just a little line below just to show that this
is kind of a subheader, the revenues and margins sub header for this section of the dashboard. Okay, this looks good to me. And now I am continuing
to build the front-end by again putting borders
around these fields that then later will
be filled with numbers. And this is actually in this example, the dashboard where we have the data from January to end of November. So let's assume for instance it would be in December of 2020. So December of last year now
we would build a dashboard that goes from January to November. And then this is not the
data that is captured here is also shown in the last
update at the timestamp field. Now we have the cumulative data. So from January to November, and then in this dashboard example it shows the last five months, right? So July, August, September,
October, November. This is of course arbitrary. Now I can see that I need
to shift in the column K this a little bit around so
that it matches the example And now here, I just copied from above. Now I'm adjusting the column width, right? So I'm making all of
these have the same width and this is not a size 15. So this makes it look
a little bit cleaner. Now again, I'm making this,
this is a little bit smaller copying it over there so that I'm sure that I'm getting the
exact same distance here. And next I am now further cleaning it up but let's see what I put next exactly. This can be a little bit bigger as well. Let's also put this to 15. This now looks a little
bit better, I'd say. And now we're continuing
to build the lower section. So performance comparison here again then the historical development. So this is not the section
where we wanna compare the current performance
to how the performance looked like in the last years. So here we have, again, the gross revenue, we have the profit as well,
and we also have the margin. All right, so far so good. And now here what you wanna have is now for the years, 2015 to 2019,
so for the last five years and now assuming currently we are in 2020 that then we see the change. And now here we're putting
the year information so that we have this way covered as well. So what I like to do now is
to adjust these grid lines so that the grid lines
are not appearing anymore. So this, I believe always
gives a much cleaner look. So can very much recommend this just for this nice
clean look that we want. And now let's start looking
into the different data inputs. So here you have the store steps. So you see these 10
stores, that I just assumed this retail store has 10
stores around the world. You see a store ID, store name, the city, and the type of stores. Apparently we have your
flagship and boutique. It's only these two types. Then we go to the next tab. This is the revenue 2020 tabs. So this is month to date, right? So it contains all the months until now. So this example, it has the
data from January to November. So you see per store the
different monthly data. What's important to understand is that this is data that can easily come out of any business
intelligence system. This is just a flat
table with all the data. Pretty much any information
system that businesses have can give us such an output. And this is what you're
always looking for. You're looking for these clean
tables that you can then use. And then also later, if you can update it just copy it in the new
report, copy it into here. And then pretty much the whole dashboard updates automatically, right? This is pretty much the goal. And this is what then will
be achieved by setting it up in such a way, in a way
that is also shown here. Right here we have the different months. Again, this ends at November and then you have the
net revenue and the cost, the cost of goods sold, so the cost. And here now in the history tab, we have the years, 15, 16, 17, 18, 19. Again, flat table format. This would come out of
every business intelligence, BI system that you have and then you can use the
data for the dashboard. So far, so good. So let's build it, let's get practical. Let's see how exactly this is done. So let's start by creating the dropdown for the store selection. So the drop down for this field. And here again, I think it
doesn't show you the overlays but you need to click on data. And then in the data
menu on data validation and then the menu that pops up you click then the list type and then you can select from the kind of a list of sales that you should use as an
input for the dropdown. And of course, these input
sets should be the store names. So you now highlight these store names and then you press okay. And then these are exactly
all the names that then appear in the dropdown. Again, unfortunately
you cannot see it here but here you can select one of these. So you see now that I
clicked in the dropdown these different names. Now I will center it
and also color it white because I believe it looks a
little bit cleaner this way. And now here, this would, for instance be the dropdown for, I
believe this is the LA store. But now to get exactly this done, let's first start by now
also putting the store ID because maybe some employees
are not used to working with the store ID, the number
instead of just the name. So we will do exactly that. We will pull that based
on the lookup function. And we will now start actually
using VLOOKUP for that. And I know that I told you,
you shouldn't use VLOOKUP in a prior video of mine. So check that out for the whole argument. I will use VLOOKUP now because I believe this is a function that will be a bit more
well-known to most of you. It's pretty much just
the VLOOKUP function. Again, if you want to know
what this is all about, check out the video I made on this. I will link it somewhere above here so that you can have the full picture. And the idea now is that it
pretty much picks the names. So the LA as the was in example before then you need to select the table array and then it looks into
here now this example, you have the column B,
it looks for the name and then in this example
if you put the two for the second column to the right, to then return you the ID. And indeed now for LA, it
returns the store ID number four. And indeed, if you look at LA it had the number four next to it. So pretty much it looks okay. There is LA, that is next to it and this is the number that you get. And you can see now if you toggle between all the different
stores in the dropdown, then now suddenly you
have the store number that has changed accordingly. So let's now indeed, I
also fill here the city because now of course you want
that based on the selection. The city gets shown in
the correct way as well. So again, we use the VLOOKUP,
we take the store name and actually now you could
also use the store ID because we have it here as well. You highlight it, and all for the city, it is the third column. You always start by the
first column of the selection in the VLOOKUP formula to then count and then that's the third. And then we can just copy it over and use the number four to
give us the type as well. And New York was, as you've seen on the table before a
flagship store indeed. So hope this makes sense. Check it here, flagship store in New York. This is what it is guys. So now we build this here as well. We can, again, it looks
at the first column and then you count one, two, three, four. So the flagship is now in the column four. So this is why, then
there's the number four. Again, just bases of VLOOKUP and you see now that if you
toggle the store selection then on the right, the
inputs will change as well. So let's now continue
to fill these fields. And we will start now with
the accumulative value. So the value for January to November and we will do all of this
with the sum if formula. So again, if you wanna know
how sum if exactly works, check out the video on that because I already
covered this extensively. I will link it up here, but pretty much the idea is that with sum if, you can mark the column where you wanna sum the values from but then you can define conditions so that these values only get summed up if certain conditions are met. And you, of course first of all, we wanna start with the store, right? Because we only want to send the values that are from the store
that is currently selected. So here now we need to highlight that it only sum the
values that in the column A of the revenue tab has the same as here in the store selection. And if we do that now, it gives
us this crazy number here. Now, of course, it is
not formatted properly. So I would just format it
in the accounting format, the accounting format usually
is my go-to to do this. Again, I just opened
the formatting menu here just to format it with
the accounting format. And now with copying of the format, I pretty much copy this
format to these other sites. And now to get the month value, we need to pretty much just copy it over this whole sum if
formula that we just had, and we need to extend it. We need to define another
restricting condition because now wanna restrict it, not only to the store
but also to the month. So we wanna only sum the
values that had the store as New York, as the value that we put, but also that have as a monthly value, only the month that is selected. And thankfully enough, now already had the column
with the month names. So it can directly select July and it will look in the rows but can freely then change the columns. And this way, if I copy it over, you can see a directly
track, the columns correctly, it fixed the row and now
we have the monthly values. Looks good so far. So now we continue here
with the gross profit. And as you know, profit
is revenue minus cost, revenue minus costs. What we need to do is is we
need to take the revenue, which you already have above and then we need to subtract
the cost from it, right? So let's start by putting the cost here. So to do that, we take the formula, you see that here is summing
up currently the column E and the revenue tab, the column E but of course the column
E was the revenues. So now we need to,
instead of the revenues, sum up the cost, of course,
with the same condition. So if we go back, we see that revenues, again, are in column E but now we wanna sum the
values in the column F. Again, so we copy in
the exact same formula. We just change the
column input from E to F and this way now it gives us the cost, but again, we want the profit. So we need to take the revenues, which we have already
calculated above there. And then we deduct the cost and
then it gives us the profit. So the revenue that we
have above minus the cost this is what will give us the profit. Now, same thing is true here
for the monthly figures. We copy in the formula for the revenue, we replaced the E with an F because this is what we give us the cost. And then we take the revenues from above we subtract these costs
to then get the values. And then because we've set it up correctly with a fixed formula, et cetera,
we can just copy it over. And for the margin we
just take the percentage and calculate it here. Of course, this is now to be formulated as a percentage figure. And then we have pretty
much the gross margin that we are calculating here. So again, basic high school math. I hope you can follow along, hope this is not too complicated. All right, I'm just copying
the format of the percentage formatting below to the
margin line as well. And now you can see
that if it just toggled around the stores, all of this
gets adjusted accordingly. So next historical development. Again, the values from
the different years. Again, we can also do this with sum if's, Sum if is always super
helpful with these things. I just love sum if's, I pretty
much use it all the time because it's so versatile. You could use it for all
kinds of different things. So often it has (indistinct) It's pretty much just as
a LOOKUP. (laughs softly) So but I pretty much use
it for everything, right? And what we need to do here
is that we just take sum if. Now, indeed we need to
put the yearly figures for 2015, right, for 2015 for this specific store, Of course. So again, we set up the sum if's formula and to do this accordingly, and now we need to go
into this history tab. And then we select the
gross revenue column. Yeah, this is now where we
want to sum the values from then we set the conditions. So here the year column,
and then you can tell him that he only needs to sum the
values that have your 2015 in this example, in the year column. And then we fixed the lines and then we can just copy it over. Okay, so hope this makes sense, but not actually, I noticed
that I forgot something because, of course, I
should not only restricted by the years but I also
need to restrict it by the stores because
otherwise it's giving me the sum of all the store figures but I only want the sum of the lines where then he has the specific
store that I selected, right, which is why now I introduce
the second condition to the sum if formula, namely
the condition of the stores. So the next one is the cost. Here, again, revenue wasn't column E, the cost was in column F. So, you know, the drill, we just go back we copy the formula from above, we replaced the E by the F. But again, now we have only the costs but we want the profit. So we take the revenue and
we deduct the cost from it. So this then gives us the profit. And again, we just calculate the margin to see what the gross
margin was looking like. And here of course,
what you can see is that apparently in the current
year, January from November, the margin was much smaller, 33%. So the store is apparently down. And now we just here calculate
the percentage change from 2015 to 2019. So for this five-year period, so this is just basic
calculation of a percent change. There will be one value
over the other minus one. we changed the former
two percentage figures. And then this is what you get. Again, basic high school math,
hope you can follow along. And again, what you see here on this store has a little growth. The others are down, the other are down. So you see apparently tough times in retail at the moment, right? So this is what it is. Okay, so what we're left
here is now the trend line. And for this I use the sparkline
functionality of Excel. So what you can do is
just directly it gives you this little chart here, right? So you need to just highlight the values from which you wanna
generate the sparklines and then you click once
you've done this on insert then you click on sparkline
and then you can see it but just click on sparklines and then you click on lines
and then you asked you, okay where do you wanna let this sparkline diagram to be shown, right? So where do you want this to appear? And then you just click in the field where this is supposed to appear. So this is now the trend
field, and new press okay. And then it directly
gives you the sparkline. So super easy, super straight forward, but I will guess this is a little touch and now you can just copy it down. And then it'll help you shifts the rows down to the rows where you
wanna apply this through. and you see this as correct, right? In October, there was a spike. And then there was quite
a crash in November. This is exactly what you see
here in this diagram as well. Looks good to me. Again, let's compare it
to the reference sheet. So looks pretty similar, I'd say. Let's compare the venues for New York. So these are the exact same values that we have from this
reference sheet as well. So again, looks good
and looks correct to me. Let's now toggle it down around a bit here in our version as well. Looks all good. And again, so my point is now this, of course, is a
rather basic version. But if you can built things like this, this will put you ahead of 95% of people that work in corporate
firms, trust me guys. This is something that 95% people in even richer companies cannot do. And this of course was a
basic example of a dashboard. This was pretty
straightforward, pretty basic but with this, you can easily expand it. With this technique, I built
much complex dashboards, right? That I just cannot show you now just for the sake of the time,
but pretty much dashboards. But then you can also include
information on employees that work in the stores, maybe more complex data on
the different categories that are being sold,
merchandise categories that are being sold,
how does that compare? So the previous year,
you can then also include the little charts just with the
basic Excel chart functions. So this is super versatile
and this can really create very powerful dashboards. And I've really seen clients,
very often be super impressed. Even clients working in
managerial accounting, controlling departments by how quick and how straightforward you
can build these dashboards. And then of course updating them from the super easiest way, you just copy in the new monthly report once the new month has finished, then maybe you just need
to do little adjustments on the month columns. And as you've seen, because the months directly link into the table, just if you put a new
name of the new month in the overview, right in the front-end, then directly it automatically puts all the right numbers automatically. So this is very easy, just
a great way to set this up. And this is something that can
really recommend you to do. So I hope was helpful. Again, if you Wanna download the sheet, there's a link in the video description that you can download for
free, this very dashboard that we now created together here today. And if you have any questions as always, just leave me a comment
below in the comment section. I will answer every single
comment that you write. And as you know, if you write
a comment, it also helps me because lots of comments now
go to the YouTube systems. If you took any value out of this video, please hit the like button
for the YouTube algorithm and also subscribe to my channel to stay up to date on all my content. If you want to see even more from me, follow me on my Instagram,
my handle is @firmlearning. And you can also reach out on LinkedIn. Again, linked to the LinkedIn
in the video description. And I wanna say thank
you again to DataCamp for sponsoring this video. If you're interested to
try our DataCamp yourself, feel free to check out the link to them. I already took several
courses on DataCamp myself and I can tell you that the
courses are high quality. I now also wanna say thank you to all the members of the channel. Thank you so much for your contributions. You are really making a
difference for my work. And if you are interested
to become a member as well hit the join button next
to the subscribe button to learn about all the
perks that you will get if you decide to become a member. So if you watched to the very end, now let me know in the
comments that you did and thank you for doing that. I release weekly videos
every single Saturday. So looking forward to next
week's video with you guys. Until then, good weekend to all of you. This is Heinrich from Firm Learning. Bye-bye.
