This is how I ACTUALLY analyze data using Excel
1 Video View·Nov 7, 2024
00:00 The data analysis exercise 03:44 Suppliers, Brands, Categories 05:22 Year on Year Growth 08:40 Market Share 12:13 YTD Profit and Moving Annual Total Profit
It's been six years now since the first time I analyzed a bunch of data at work using Microsoft
Excel and today I want to show you my Excel data analysis workflow that covers 99% of the types
of analysis I do using the tool. This is not going to be a video about building beautiful
but nevertheless useless and random dashboards. This is going to be a video about how I solve
business problems using Excel, how I analyze data and get insights and next steps using Excel.
For those of you who are new here, my name is Mo Chen and I work as a data and analytics analyst
in the financial services industry. Excel is my go-to tool for any quick ad hoc analysis up to
say a million rows. Anytime when I want quick answers to some business critical problems,
I use Excel. Let me show you how by walking you through a hands-on practical example where I'll
identify the top suppliers and brands across various categories in my fictitious gym equipment
sales data set, look at year-on-year growth to see how sales have changed over time, analyze market
shares and even calculate year-to-date and moving annual total profit figures. I think that's enough
of an overview for now. Let's dive into the analysis. All right, cool. So this is going to
be a pure data analysis exercise. This is like I said not going to be one of those YouTube videos
where I'm going to create a bunch of shiny stuff that is completely useless and random. I'm going
to show you what I use Excel for which is quick ad hoc data analysis to get the answers to whatever
business critical problems that I have and that I just move on. So this is our data set. It's a
fictitious gym equipment profit data set and a super quick tip for you. If you get a data set
and the columns are all over the place, you can click in the top left and then double click and
then you can see that all of the columns are beautifully adjusted. Obviously this works for
rows as well, so let me just mess up my data set just a little bit. You can see that I'll mess this
column up as well and this one and this one. Click on the top left, double click and then you
can double click again and you have a neat and tidy data set. So first things first, let's
understand the columns. We have only six. Again, it's a simple fictitious data set, so I try to
filter out all of the noise and we'll just focus on these columns. Category, suppliers, brands,
year, month and the monthly profit. So that's the data that we have. Before we even jump in,
one thing I like to do is you can see that this is what's called a range and I like using tables
because it's just so much easier to use tables when you have a bunch of pivot tables and pivot
charts and then everything automatically updates. I won't go into too much detail about this
in this video, but I guess if you want to learn more about Excel or SQL actually or Tableau or
Python, then Course Creators have a super data analytics course. They've been a partner of
mine for quite a while now. So yeah, they're good. Check out their course if you want to.
But anyway, let's turn this into a table and the easiest way to turn this into a table is
to press CTRL and then T. I'm a man of shortcuts. You hit OK and there you go. What's the big
difference now? If you go to the top here, you can see table design which you didn't have before. Let
me just undo this. You see at the top now you don't have table design. So CTRL T, hit OK and
then this is a bit too much for me, this design, so I'm going to tone it down just a little bit.
I think this looks about right. So the first thing I really want to know is how many suppliers do I
have? How many brands do I have for each supplier? And how many categories of gym equipment, well,
profit data do I have here? So a really simple way to do it is to insert a pivot table and start
doing some analysis. So I'm going to do that. I'll show you two ways to do it. I'll show you
the shortcut way which is just going to be ALT and VT. Hit enter and then that's a pivot table.
It took me probably two seconds to do it, but I'll delete the sheet for now and I'll show you
the point and click way as well. So you go to the top, insert pivot table from table, hit OK.
There you go, you have your pivot table. And then let's see how many suppliers do we have.
Three suppliers, OK, fantastic, easy. And if you're not sure about the count or the sum or
any kind of summary figures that Excel would show you, you can always go to the bottom right,
right here, and then you'll see the numbers. All right, so we have three suppliers. How many brands
do we have? We have nine brands and what I like to do is go to design. Within design, change the
report layout to show in tabular form, remove the totals, and then it's just an easier way
to look at all of the brands. And then I'll drag in the categories as well. So how many categories
do I have? I have three categories, air bikes, rowing machines, and treadmills. Super simple.
All right, so now I answered some super basic questions. So we have three suppliers,
nine brands, and then we have three categories. Just click into the filter and you'll know.
Next thing I want to know is the year-on-year growth for each of these brands in terms of
profits. So what I will do is I'll go to my worksheet on the bottom here, hold down control,
and then duplicate the worksheet. Yeah, so this is good because you're duplicating the pivot table
as well, which is going to come in really handy when you're starting to use slicers and timeline
filters across all of your pivot tables and pivot charts in a dynamic dashboard. So yeah, it's going
be great for that. I'm not going to go into much detail in this video on that kind of stuff. If you
really want to learn more, you can get some certifications and you can do some courses
online. Yeah, they'll teach you a lot about it. Yeah, and I guess on that note, you could say,
for example, go to Datacamp. They have a bunch of good courses, not just on Excel, but a bunch
of other data stuff as well. I learned a bunch of Tableau skills on Datacamp because they have
like a skills track, a certifications track, they have case studies, they have various tutorials. So
yeah, they're pretty good. And I recently started partnering with them as well, because yeah,
they're genuinely good. But anyway, yeah, second question, year on year growth. How am I going to
solve that problem? So not that difficult. Let me remove the category for now, drop in the monthly
profit, and then let me grab the year and move it onto the columns. So now these numbers here,
these are the total profit numbers. But what I'm looking for is the year on year difference. So
basically, I want to see what's the difference between 2019 and 2018, 2020 and 2019, so on and
so forth. So I go to values, value field settings, show values as, and then all I have to do is select
percent difference from, and then the base field is obviously year, and then the base item is
previous. Yep. And then I hit OK. And there you go. It looks, yeah, pretty good, I think. There
you go. Some quick calculations. 2024 is straight away, I can see, off the charts. Now, why is that?
We can go into the data, and we can have a look quickly. So let me go 2024, select a couple here.
There you go. 2024 is off the charts, because we only have five months worth of data up to and
including May. That's why the numbers are off the charts, because you're comparing five months of
profits to 12 months of profits in 2023. So let me just remove 2024, because it's not a full year,
and then the numbers look much better, I think. All right, so these numbers, obviously, it's, you
know, quite hard to spot which one's good, which one's bad. What I like to do is, this is honestly
so simple. Go to conditional formatting, and then choose some color scales. So whatever you want,
you can choose your own rules. I don't care too much, because I'm really just after some numbers.
So I'll go with the... let me go with this one. I think blue is good, red is bad. Yeah, there we go.
So the darker the blue, the larger the increase, and the darker the red, the larger the decrease.
So that's some quick insights for you right there. Biggest growth, 6.91% right here.
Biggest decrease, what was it, 7.7% right here. Yeah, that's it. Quick insights. All right, so I
answered year-on-year growth. Now the next thing I probably want to look into is market share. So let
me again duplicate my sheet, hold down control, drag and drop the sheet, and then I have a new sheet now.
So all I'm going to do differently this time is, I'm going to move the year onto the rows,
I'll move probably suppliers onto the columns. Let me move that away, and then... oh actually,
yeah, I want to move the brand probably onto the columns. Yeah, that makes a lot more sense.
And this time, let's change the calculation. So go to value field settings, show values as,
and it's not a difference from. All I want to see really is the row total, the percent of row total.
Hit okay, and now I want to add back 2024, because we're comparing like for like.
And what I want to do now is remove the conditional formatting. So you go to conditional
formatting, manage rules, and then you just delete the rule. Hit apply, hit okay, done.
So now what's happening here is that each of these rows should add up to 100. And if you go to the
bottom right here, you can see that the sum is indeed 100. You can check this one as well, is
indeed 100, or you can just do a sum function. So it's going to be sum b5 to, what is it, j5.
There we go. And then you can drag it down, and there you go. Everything's 100 percent. Fantastic.
Okay, so the market share cannot be more than 100 percent, right? Because that's all of the profits
in the market. So that's the entire market. So all I'm going to do here is insert a line chart.
I'll pick this one, and then it looks obviously not great right now, because we didn't actually
select in which category do we want to look at the market share. So I go to pivot table, analyze,
and then insert slicer, and I want the category. Hit okay, and now we have a pretty decent slicer.
All right, so air bikes. Cool. Let me look at that. There we go. Make it a bit bigger, and yeah,
there you go. I have three different categories you can click through, and you can see how the
market shares change over time. If you don't like the layout of this chart, you can quickly
change the layout into something else. Let me pick something that looks, well, not ugly. So
yeah, maybe this one. I think this one looks all right, and then you can change the colors as well.
People like dark stuff. Oh, that is very glowy. Maybe this one. Yeah, all right. Okay,
this is not too bad. So I'll go with this one, and let me just interpret the charts for you
really quickly. So let's pick air bikes, for example. What can I see straight away? So the
thing I can see straight away is that Apex Athletics, they started increasing their
market share and their profits. It's at 17.97%. Fantastic. And then what's that? Spartan Sports,
they're the worst as of 2024. Rowing machines, I can see that. Is this Elevate Fitness? Yes.
Elevate Fitness improved over time, which is fantastic. And then the purple one, Titan
Training, they definitely didn't improve over time. They're actually at the bottom of the list
now. And if we're looking at treadmills, it's going to be Spartan Sports, who have, I wouldn't
say dominated the market share, but obviously they increased their market share over time.
And then Hercules Gear did the exact opposite. Simple analysis. That's it. Let's move on.
So the next thing we're going to do is, I did tell you that we were going to do some calculations. So
we're going to do some year-to-date calculations, and we're going to do some
moving annual total profit calculations. All right. Super quick break. And don't worry,
this is not a sponsored message. This is a message from me, Moe. If you gained any value out of this
video or any one of my videos so far, then can I ask you three simple favors, please?
One, subscribe to the channel. It helps everything I do more than you know. Two, check out the
Ultimate Data Portfolio. Why? Because it gets you closer to your next data job. If you don't
believe me, just check out what others have said. And three, if you're truly committed to getting
your next data job and making more money, then check out my one-to-one mentorship program.
So year-to-date essentially is, let me pick an example. Say if we're in 2018 May, I want all of
the profit to be summed up from January to May. That's it. And then if we're in August, then I want
all of these profits, so the eight months worth of profits, to be summed up. But if we're in 2019
July, then I want obviously the first seven months of profits to be summed up. So let me call this
new column profit year-to-date, because, well, it is the profit year-to-date. And it's going to be
super simple, so nothing to worry about. A bunch of sum ifs. So first of all, it's asking for the sum range.
So I'm just going to use my keyboard to navigate around. If you're on a Windows, it's just the arrow
keys and the control key. And then if you're on a Mac, it's going to be the arrow keys and the
command key. Super simple. So okay, so the sum range for me, and I'm going to hold down control right now
and press space, and then I select the entire column. Fantastic. Okay, the first criteria range
is going to be the category, and then the category obviously has to be equal to the
to the category of the current row. So yeah, treadmill in this case. And then criteria range two,
supplier. Again, it has to be the supplier that occupies that row of data. So in this case, peak
performance year. Criteria range three, it's gonna have to be the brand that's in the current row.
And then year, it has to be the current year. So again, in this case, 2018. And then month.
And this is where it's going to get a little bit tricky, not too tricky, but because we want the
year-to-date profits to be summed up, we want all of the months up to and including the current month.
So it's just a simple math calculation, less than or equal to, ampersand sign, and then you pass in
the current month. And then if this doesn't work now, then I'm gonna look like a fool. But it did
work. Fantastic. Okay, so let me just do some formatting, because we're talking about profit
figures here. Currency. And you can see that it's a British pound here, because I live in the United
Kingdom. So yeah, it's not US dollars this time. I won't change it to dollars, because yeah, whatever,
it can be pounds this time for a change. So let's do some sense check. So for the first year,
these profits, right, let's just do some sense check. So let's pick 2018 May, and then the 65476,
this number should be equal to the sum of these five months. And you can go to the bottom and
check 65476. Yes, it is. Fantastic. All right, we can move on. And obviously, we can do the first
12 months. There you go. Profit year to date, it's going to be 152585. And on the bottom here,
you can see that it's 152585. Indeed, so the profit year to date numbers are legit. Fantastic.
Let's just pick another one, just in case. This one right here, let's add up the profits for the
first three months right here. So this 41036, 41036. Okay, I did a sense check. Everything
works. Cool. Next thing I'm going to do is we're going to calculate the profit. What should I call
a total moving annual total? I think that sounds good. Because essentially, what we're going to do
now is, let me explain really simply, we're always going to look at a 12 month period. So I want the
profit figures summed up for the last 12 months, no matter where I am. So if I pick 2019 May,
for example, I want the profits for this year. So this is the year to date profit. And on top of
that, I would also like seven months from the year before. So all of these months, so seven months
from the year before five months from the current year. So that's seven plus 512 months. And that
would be the profit for the last 12 months. But if I pick another month, for example, 2020 October,
so I have 10 months in this year, I want all of these profits. And I would like two more months
from the last year. So to calculate the profit moving annual total, we need to, first of all,
pick the profit year to date. And on top of that, we just add on whatever months we want to
make it a full 12 month period from the previous year. So this is going to be profit year to date
plus again, it's going to be SUMIFS. I bet it's boring by now, but the sum range is going to be
the profit month. And then the category again has to be equal to the category of the current
row. And then second criteria supplier has to be equal to the supplier in the current row.
And then we have brand has to be obviously equal to the brand of the current row. And then we have
year and it gets a little bit tricky here because I want obviously the year but I want the previous
year. So take away one, because you already included the profit year to date. So you already
have the profits from the current year, what you're trying to do is onto the current year's profit,
you want to add on last year's profit, however many months you need to make it a full 12 month
period. So that's why the minus one to get to the previous year. And then in terms of months,
again, simple math calculation. So the month has to be greater than whatever current month you're
in this year. So greater than, and then ampersand sign, and then the current month,
and then hit enter. And again, if it's not going to work, I'm going to look like a fool,
but it did work. So good. Oh, by the way, the only thing I would like to add as well,
if you want to learn this type of thinking, how to actually solve problems using data,
and how to like, you know, actually approach job applications and how to do stuff in the real
world, then I would honestly, I would, I would urge you to go onto my websites and look around
there and check out the resources because yeah, you know, like all the stuff I created, really,
the ultimate data portfolio, the ultimate data roadmap, and like the six other bonuses that come
with it, the job seeker Academy, the ultimate project builder, they focus on implementation,
they focus on hands on stuff, they focus on practical stuff, they focus on relevant stuff.
So I've done so much learning. And sometimes, you know, you're in this forever cycle of
not implementing things. So make sure you actually implement things like this, for example.
Okay, let's compare the numbers for the first 12 months, they should be the same,
because there was no previous year makes sense. All of these numbers are the same. Now,
if we look at row 14, my profit moving annual total is 152423. So this should be the profit
January, plus the 11 months before. So if I select these 12 months, I can see if I go to the bottom
right that I have 152423. Okay, great. Let's pick something randomly, I'll pick row 86. So I'm in
August, which means this profit right here, this 95552, this should be the year to date profit
plus the four months from before. So 12345678. And then Oh, sorry, yeah, actually, I picked the
wrong one. Because obviously, I'm in 2018. Here, there's no prior year apologies. So let me pick
another one 2019. August, for example, it just goes to show you know, with data analysis, trial
and error. And I'm pretty certain I'm going to leave this piece in as well. I'm not going to
cut it out just to show you that, you know, not everything has to be perfect first time. Clearly,
I didn't pay attention here. And I should have lesson learned. So 2019, August, the moving annual
total profit is 152337. So this number should be these eight months in the current year. So this
is the year to date, and then the four months from the previous year. So 1234. And what's the number
152337 on the bottom right here? Fantastic. The profit moving annual total numbers are correct
as well. And just to do some, I guess, you know, pivot table data analysis manipulation with it.
Let me go back to the sheet right here. And let me duplicate it. So holding down control dragging
and dropping the sheet. I'll remove this chart because I don't need it. I'll remove the slicer
as well. Click into the table. And this time, let's do something different. So first of all,
go to pivot table analyze, and then you go refresh, refresh all. And then now all of a sudden,
you see that our two new calculations, the two new columns, they appeared right here. Okay,
so I'm going to remove the monthly profit figures because I don't need them. And then the next thing
I'm going to do put year on the filters, I'll put month on the filters, I'll put brand on the rows,
and then I'll put suppliers on the rows as well. And now the only thing I really want to see
is the moving annual total profit for a specific month. Obviously, these figures now are large,
very, very large, because my year and my month, I'm selecting all, which I shouldn't. I just want
the moving annual total profit for the last month of data that I have. So I'll pick 2024.
2024, I think May was the last month, probably. Yeah, let me just go back and see 2024.
And yes, May is the last month. Okay, so I'm back here. Let me just format this. Oh, yeah,
this is another thing you can do. You don't always have to go to values right here on the right,
and then go to value field settings. You can just double click, and it gets you to the same place.
So I'll do some quick formatting, number format, number, yeah, thousand separator. Okay, it's just
easier to look at the number. And there you go. This is the moving annual total profit for these
brands, for these suppliers, which means that over the last 12 months, up to and including 2024
May, Iron Strength Equipment Co. for the Forge fitness brand sold, well, made this much profit.
And you can obviously add in the category as well, if you want to, and then you'll see a different
kind of breakdown. And then if you select everything, then you will see the total
across all of the brands, all of the categories and all of the suppliers. So yeah, I think pretty
simple stuff. That's about it. All right, that's all from me for now. If you enjoyed this video,
then please subscribe to the channel, as there's definitely a lot more value add content like this
one coming. And you should probably also check out these videos right here. Thank you so much for
taking just a little time out of your day to watch this, and I shall see you in the next one.

