This is how I ACTUALLY analyze data using Excel

Summary

In this video, Moan, a data and analytics analyst, shares his Excel data analysis workflow, focusing on solving business problems rather than creating aesthetic dashboards. He demonstrates how to analyze a fictitious gym equipment sales dataset, identifying top suppliers and brands, examining year-on-year growth, market shares, and calculating year-to-date and moving annual total profit figures. Moan emphasizes practical data analysis techniques, using pivot tables, conditional formatting, and data manipulation to extract actionable insights.

Takeaways

• 😀 The video is focused on practical Excel data analysis for solving business problems rather than creating aesthetically pleasing but non-functional dashboards.
• 💼 Moan, a data and analytics analyst in the financial services industry, uses Excel as his primary tool for quick, ad hoc analysis of data sets.
• 📊 Excel is particularly useful for answering business-critical questions involving large data sets, such as up to a million rows.
• 🧩 The video provides a hands-on example of analyzing a fictitious gym equipment sales data set to identify top suppliers, brands, and categories.
• 📈 The analysis includes year-on-year growth, market share calculations, and profit figures such as year-to-date and moving annual total.
• 📝 A quick tip is shared for organizing data sets where columns are misaligned: click on the top left, double-click, and then double-click again to align columns neatly.
• 📑 Converting data into an Excel table is recommended for easier manipulation and updating when using pivot tables and charts.
• 🔢 Pivot tables are used to quickly count the number of suppliers, brands, and categories within the data set.
• 📊 Conditional formatting is applied to highlight increases and decreases in year-on-year growth with color scales for quick visual insights.
• 📈 The video demonstrates how to create charts to visualize market share changes over time using slicers for interactive filtering.
• 📋 The script includes a step-by-step guide on calculating year-to-date and moving annual total profits using Excel functions like SUMIFS.

Q & A

What is the primary focus of the video script?

The primary focus of the video script is to demonstrate a practical Excel data analysis workflow for solving business problems, rather than creating visually appealing but non-functional dashboards.

Who is the speaker in the video script?

The speaker in the video script is Moan, who works as a data and analytics analyst in the financial services industry.

What is Moan's preferred tool for quick ad hoc analysis?

Moan's preferred tool for quick ad hoc analysis is Microsoft Excel, which he uses for any analysis up to a million rows when he needs quick answers to business critical problems.

What is the purpose of using tables in Excel as per the script?

Using tables in Excel simplifies the process of working with pivot tables and pivot charts, as everything automatically updates when changes are made to the table.

How does Moan suggest adjusting columns in Excel for better readability?

Moan suggests clicking on the top left cell and then double-clicking to automatically adjust all the columns for better readability, which also works for rows.

What is the significance of the fictitious gym equipment profit data set used in the script?

The fictitious gym equipment profit data set is used to demonstrate a hands-on practical example of how to identify top suppliers and brands, analyze year-on-year growth, market shares, and calculate year-to-date and moving annual total profit figures.

What is the shortcut Moan uses to create a pivot table in Excel?

Moan uses the shortcut 'Alt + VT' to quickly create a pivot table in Excel.

How does Moan approach analyzing year-on-year growth in the script?

Moan analyzes year-on-year growth by creating a pivot table, setting the year in the columns, and using the 'Show values as' feature to calculate the percent difference from the previous year.

What is the purpose of using conditional formatting in the pivot table as described in the script?

Conditional formatting is used to quickly visualize data by applying color scales to the pivot table, where darker colors represent larger increases or decreases in values.

How does Moan calculate market share in the pivot table?

Moan calculates market share by setting the year on the rows, brand on the columns, and using the 'Show values as' feature to display the percent of row total in the pivot table.

What is the significance of duplicating worksheets in Excel as mentioned in the script?

Duplicating worksheets in Excel is significant for creating multiple analyses that utilize the same pivot table, which updates dynamically across all copies when the original data changes.

Outlines

00:00

📊 Introduction to Excel Data Analysis Workflow

The speaker, Moan, introduces his Excel data analysis workflow, emphasizing its practicality for solving business problems rather than creating aesthetically pleasing but functionally limited dashboards. Moan, a data and analytics analyst in the financial services industry, uses Excel for quick ad hoc analysis on large datasets. He presents a hands-on example analyzing a fictitious gym equipment sales dataset to identify top suppliers and brands, examine year-on-year growth, market shares, and calculate profit figures. Moan shares a tip for organizing datasets in Excel by using the 'double-click' trick to align columns and rows neatly.

05:01

🔍 Analyzing Data with Pivot Tables and Charts

Moan demonstrates how to use pivot tables to analyze data, starting with counting the number of suppliers and brands. He explains the process of creating a pivot table quickly using the shortcut 'Alt + VT' and adjusting its design for clarity. The video then shifts to analyzing year-on-year growth by setting up a pivot table to show the percentage difference in profits between consecutive years. Moan uses conditional formatting to visually represent the data, highlighting increases and decreases in profits. He also discusses the importance of removing partial data sets, like the year 2024 with only five months of data, to ensure accurate year-on-year comparisons.

10:02

📈 Examining Market Share with Slicers and Charts

Continuing the analysis, Moan focuses on calculating market share by creating another pivot table that shows the percentage of total profits for each brand. He uses slicers to filter the data by category, allowing for a dynamic view of market share changes across different types of gym equipment. Moan adjusts the chart layout and color scheme for better visualization and interprets the chart to identify trends in market share, such as the rise of Apex Athletics and the decline of Titan Training in the airbikes category.

15:04

💹 Calculating Year-to-Date and Moving Annual Total Profits

Moan introduces the calculation of year-to-date (YTD) profits, explaining the SUMIFS function to sum profits from the beginning of the year to the current month. He performs sense checks to ensure the calculations are accurate. Next, he tackles the calculation of moving annual total profits, which involve summing profits from the last 12 months regardless of the current month. He demonstrates the SUMIFS function again, this time including logic to account for months from the previous year when necessary. Moan also promotes his website and resources for learning practical data analysis and job application skills.

20:05

📉 Final Analysis and Conclusion

In the final part, Moan revisits the pivot table to include the newly calculated year-to-date and moving annual total profit figures. He refines the pivot table to display only the moving annual total profit for the last month of available data, formatting the numbers for clarity. Moan concludes the video by summarizing the analysis and encourages viewers to subscribe for more content. He also points out a minor mistake in his analysis to emphasize the importance of careful data examination and the iterative nature of the data analysis process.

Keywords

💡Data Analysis

Data analysis refers to the process of inspecting, cleaning, transforming, and modeling data to extract useful information, draw conclusions, and support decision-making. In the video, the speaker uses Microsoft Excel to perform data analysis on a fictitious gym equipment sales dataset. The process involves identifying top suppliers and brands, analyzing year-on-year growth, and calculating profit figures, which are all critical for solving business problems.

💡Microsoft Excel

Microsoft Excel is a widely used spreadsheet program for organizing, analyzing, and presenting data. The video script emphasizes Excel as the primary tool for quick ad hoc analysis, demonstrating its versatility in handling data up to a million rows. The speaker showcases various Excel features like pivot tables, conditional formatting, and formulas to analyze the dataset and derive business insights.

💡Pivot Tables

Pivot tables are a powerful feature in Excel that allows users to summarize and analyze data by organizing it into a multi-dimensional table. In the script, the speaker creates pivot tables to count the number of suppliers, brands, and categories, and to calculate year-on-year growth and market share. This tool is instrumental in providing a structured view of the data for easier analysis.

💡Year-on-Year Growth

Year-on-year growth is a financial metric that compares data from the current year to the data from the previous year, typically used to measure the performance of a business or economic indicator. The video discusses analyzing year-on-year growth to understand how sales have changed over time, which is crucial for assessing the financial health and trends of the business.

💡Market Share

Market share represents the portion of the total available market that a particular product or service captures. In the context of the video, the speaker calculates the market share for different brands within various categories to understand their relative position and performance in the market.

💡Conditional Formatting

Conditional formatting in Excel is used to apply specific formatting to cells based on their values, such as colors or icons. The speaker uses this feature to visually represent the year-on-year growth, making it easier to spot increases and decreases in profits at a glance.

💡Slicers

Slicers in Excel are interactive filters that allow users to control and filter data in a pivot table or chart. The video script mentions using a slicer to filter data by category, which helps in dynamically viewing market share changes across different types of gym equipment.

💡Year-to-Date (YTD)

Year-to-date (YTD) refers to a period starting from the beginning of the current year up to the present. The speaker calculates YTD profit figures to understand the cumulative profit for the months leading up to the current month, which is essential for tracking financial performance throughout the year.

💡Moving Annual Total (MAT)

Moving Annual Total (MAT) is a calculation that sums up data points over a rolling 12-month period. In the script, the speaker computes the MAT profit to analyze the financial performance over a consistent timeframe, regardless of the month, providing a more stable view of the business's profitability.

💡Data Visualization

Data visualization involves creating graphical representations of data to communicate information clearly and effectively. The video discusses creating line charts and using color scales to represent data, which aids in interpreting complex data sets and making informed decisions.

Highlights

Introduction to a practical Excel data analysis workflow for business problem-solving.

Excel as a go-to tool for quick ad hoc analysis up to a million rows.

Demonstration of a hands-on example analyzing fictitious gym equipment sales data.

Technique to quickly organize a messy dataset in Excel by adjusting columns.

Advantages of using Excel tables for easier management of pivot tables and charts.

Step-by-step guide to creating a pivot table for basic data analysis.

Method to identify the number of suppliers, brands, and categories in a dataset.

How to calculate year-on-year growth in profits using pivot table value settings.

Use of conditional formatting to visually represent data changes.

Analysis of market share using pivot tables and slicers.

Explanation of year-to-date profit calculations with practical examples.

Tutorial on calculating moving annual total profit figures.

Importance of sense checks in data analysis to ensure accuracy.

Real-time demonstration of updating pivot tables with new calculations.

Final thoughts on the simplicity and effectiveness of the presented Excel analysis techniques.

Call to action for viewers to subscribe for more valuable content.

Transcripts

it's been 6 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 moan 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 very

ious 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 the 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 then I

just move on so this is our data set

it's a fictitious uh sales oh no it's a

fictitious profit data set sorry 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 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 col col s category

suppliers Brands year month and the

monthly profit so that's the data that

we have before we even jump in the 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 you know this in this video but uh

I guess yeah if you want to learn more

about Excel or SQL actually or Tableau

or python then course careers have a

super data analytics course they've been

a well yeah 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 um yeah so let's

turn this into a table and the easiest

way to turn this into a table is to

press control and then T I'm a man of

shortcuts you hit okay 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 contrl T hit

okay 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 uh 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 2 seconds to do it

but I'll uh delete this 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 okay there

you go you have your pivot table and

then let's see how many suppliers do we

have three three suppliers okay

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 airbikes 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 the

worksheet yeah so this is good because

you're duplicating the the uh pivot

table as well which is going to come in

really handy when you're starting to use

uh slicers and timeline filters across

all of your pivot tables and pivot

charts in a dynamic dashboard so yeah

it's going to be great for that I'm not

going to go into much detail in this

video on that kind of stuff if you you

know really want to learn more you can

get get some certifications and you can

do some courses online uh yeah they'll

teach you a lot about it yeah and I

guess on on that note you could you know

say for example go to data Camp they

have a bunch of good courses not just on

Excel but you know a bunch of other data

stuff as well I learned a bunch of

Tableau skills on data Camp because they

have a 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 um 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

201 18 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 okay 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 chart 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 5 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 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 cuz 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 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 J five there we go and then

you can drag it down and there you go

everything's 100% fantastic okay so the

market share cannot be more than 100%

right because that's 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 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 airbikes 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 you know 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

so people like dark stuff oh that is

very glowy um 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 airbikes 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 uh you know they yeah it's 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 yeah 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 uh I

wouldn't say dominated the market share

but obviously you know 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 Mo 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 onet toone mentorship

program so year to date essentially is

let me pick an example say if we're in

um 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 8

months worth of profits to be summed up

but if we're in 2019 July then I want

obviously the first s months of profits

to be summed up so let me call this uh

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 uh

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 gear criteria

range three it's going to 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 um

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 m%c

sign and then you pass in the current

month and then if this doesn't work now

then I'm going to look like a fool but

it did work fantastic okay so let me

just do some formatting because we're

talking about uh profit figures here

currency and you can see that it's uh

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 uh these profits right let's

just do some sense checks so let's pick

2018 May and then the 65 476 this number

should be equal to the sum of these 5

months and you can go to the bottom and

check 65 476 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 152

585 and on the bottom here you can see

that it's 152 585 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 41 036

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 um what should I call it

total moving annual total yeah 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 uh

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 5

months from the current year so that's 7

plus 5 12 months and that would be the

profit for the last 12 months but if I

pick another month for example 2020 uh

October so I have 10 months in this year

I want all of these profits and then I

would like two more months from the last

year so to calculate the profit moving

annual

total we need to first of all um well

yeah 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 Su ifs

I bet it's boring by now but the sum

range is going to be the profit month

and then the category again it 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 in this year so greater than

and then m per 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 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 road map and like the six other

bonuses that come with it the job Seeker

Academy the ultimate project Builder

they they focus on implementation they

focus on Hands-On stuff they focus on

practical stuff they focus on relevant

stuff so I've 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 152 423 so

this should be the profit in 20 19 uh

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 152

423 okay great let's pick something

randomly I'll pick row 86 so I'm in

August which means this profit right

here this

9555 2 this should be the year-to date

profit um

plus the the four months from before so

1 2 3 4 5 6 7 8 and then oh sorry yeah

actually I picked the wrong one uh

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

um the moving annual total profit is 152

337 so this number should be these 8

months in the current year so this is

the year to date and then the four

months from the previous year so 1 2 3 4

and what's the number 152 337 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

this 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 for 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 uh 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 there you go

this is the this is the moving annual

total profit for these brands for these

suppliers which means that over the last

12 months up to an 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 that if you select everything then

you would 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 ad 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

a

[Music]