How Business Analysts Use Excel - Business Analysis Software Tutorial

Summary

This video tutorial offers an introductory guide to essential Excel skills for business analysts. It emphasizes the importance of mastering basic functionalities such as COUNTIF, SUMIF, AVERAGEIF, VLOOKUP, and IF statements to efficiently analyze and interpret data. The presenter uses publicly available data to demonstrate these tools, highlighting the ease of filtering, counting, and matching data to gain quick insights. Additionally, the tutorial covers the creation and manipulation of pivot tables to provide a deeper understanding of data trends and facilitate decision-making.

Takeaways

• 📊 Basic Excel skills are essential for business analysts, including the ability to perform basic analysis activities like counting, averaging, and summing specific values.
• 📈 Publicly available datasets, such as those from data.gov, can be used for practice and to demonstrate Excel functionalities.
• 🔍 Excel's filtering and hovering features provide quick insights like averages and counts without the need for complex formulas.
• 📝 The COUNTIF function is a powerful tool for counting occurrences of specific values within a range, streamlining the analysis process.
• 📉 SUMIF and AVERAGEIF functions allow for conditional summing and averaging, respectively, which can be useful for analyzing subsets of data.
• 🔄 The XLOOKUP function is a versatile tool for matching and retrieving data from one table to another, simplifying data integration.
• 🚀 IF statements in Excel can be used to quickly transform data based on conditions, making it easier to identify and categorize information.
• 📋 Nested IF statements can provide more complex data transformations, offering deeper insights into the data.
• 📊 Pivot tables are an efficient way to summarize and analyze large datasets, allowing for quick slicing and dicing of information.
• 📈 Pivot tables can reveal trends and patterns in data, such as the popularity of electric vehicle models over the years.
• 🛠️ Learning and mastering Excel functionalities like XLOOKUP and pivot tables can significantly enhance a business analyst's ability to provide valuable insights and make data-driven decisions.

Q & A

What are the basic Excel functionalities that a business analyst should be familiar with according to the video?

The video covers the basics of Excel for business analysts, including using COUNTIF, SUMIF, AVERAGEIF for conditional calculations, using XLOOKUP or VLOOKUP for data matching, applying IF statements for conditional formatting and logic, and creating pivot tables for data summarization and analysis.

Why did the speaker choose data from data.gov for the Excel demonstration?

The speaker chose data from data.gov because it is publicly available, allowing anyone to practice Excel skills without needing proprietary company data. It also serves as an example of how to manipulate and analyze real-world data.

How can one find publicly available data to practice Excel or database skills?

One can visit data.gov, look for the most viewed datasets, and download them to practice various Excel functionalities and database skills.

What is the purpose of breaking up the original dataset into different sheets in the Excel file?

Breaking up the original dataset helps to illustrate different functionalities and simulate real-world scenarios where data may not come together. It also aids in understanding how to combine and analyze disparate data sources.

What is the COUNTIF function used for in Excel?

The COUNTIF function is used to count the number of cells within a range that meet a specific criterion, such as matching a particular value or condition.

Can you explain how the XLOOKUP function is used in the video to marry information from two different datasets?

The XLOOKUP function is used to find a value in one column (lookup value) and return a corresponding value from another column (return array) based on an exact match. This allows for the merging of related data from different datasets, enriching the information available for analysis.

What is the IF statement used for in Excel, and how does it help in data analysis?

The IF statement is used for conditional formatting and logic in Excel. It tests a condition and returns one value if the condition is true and another if it is false. This helps in transforming information and making it easier to digest, allowing for quick insights based on specific criteria.

How does a pivot table help in quickly summarizing and analyzing data in Excel?

A pivot table allows for the quick summarization of large datasets by placing data in a grid-like format with rows, columns, and values. Users can easily rearrange and filter the data to view summaries and trends, making it a powerful tool for data analysis.

What is the advantage of using tables in Excel when creating a pivot table?

Using tables in Excel automatically updates the range of the pivot table when new data is added, eliminating the need to manually adjust the range. This makes data management more efficient and the pivot table more dynamic.

How can a pivot table be used to identify trends in the adoption of electric vehicles over the years?

A pivot table can be used to group data by model year and count the number of vehicles for each year. This visual representation helps in identifying trends, such as the rise in popularity of certain models or the overall growth in electric vehicle adoption.

What is the recommendation for someone looking to improve their resume and confidence in business analyst interviews?

The speaker suggests checking out the link in the video description for additional training opportunities that can help step up one's resume, increase confidence in interviews, and become a standout business analyst candidate.

Outlines

00:00

📊 Excel Basics for Business Analysts

This paragraph introduces the video's focus on fundamental Excel skills essential for business analysts. The speaker emphasizes the importance of these basic functionalities for any business analyst and mentions that the data used in the video is publicly available from data.gov. The speaker also highlights the significance of understanding Excel's capabilities rather than memorizing every step, and demonstrates how to use Excel to analyze vehicle registration and electric vehicle data by breaking down information into two sheets for clarity.

05:00

🔍 Utilizing Conditional Functions in Excel

The speaker discusses the use of conditional functions like COUNTIF, SUMIF, and AVERAGEIF in Excel to perform basic analysis. They demonstrate how to apply these functions to count, sum, or average specific values within a dataset, such as counting the number of vehicles registered in particular model years. The paragraph also covers the process of using filters to narrow down data and the importance of understanding the tools' capabilities for efficient analysis.

10:01

🔄 Introduction to VLOOKUP and XLOOKUP Functions

This section covers the use of VLOOKUP and the newer XLOOKUP functions in Excel to combine information from different datasets, making it easier to understand and utilize. The speaker illustrates how to use XLOOKUP to find the electric range of vehicles by matching model numbers from registration data to a separate vehicle information dataset, emphasizing the efficiency of this method over manual lookup.

15:02

📋 Implementing IF Statements for Data Analysis

The speaker explains how to use IF statements in Excel to create conditions that transform data and make it more meaningful. They provide an example of creating an 'eligibility' column to determine which vehicles qualify for an incentive program based on their electric range, demonstrating how to set up the IF statement to return 'Eligible' or 'Not Eligible' based on whether the vehicle's range exceeds a certain threshold.

20:02

📈 Pivot Tables for Rapid Data Manipulation

The paragraph delves into the creation and use of pivot tables in Excel for quick and dynamic data analysis. The speaker shows how to generate a pivot table from existing data, manipulate it to count model years, and identify trends in electric vehicle popularity over time. They also discuss the flexibility of pivot tables to add filters and explore various data combinations for immediate insights and decision-making.

🛠️ Mastering Excel for Business Analysis

In the concluding paragraph, the speaker summarizes the key Excel skills a business analyst should master, including IF statements, COUNTIF, SUMIF, AVERAGEIF, XLOOKUP, and pivot tables. They encourage viewers to practice these skills and explore their applications further, offering help through comments or additional training opportunities. The speaker also invites viewers to subscribe for more content and assistance in their business analysis journey.

Keywords

💡Excel

Excel is a widely used spreadsheet program developed by Microsoft for data organization, analysis, and visualization. In the context of the video, Excel is the primary tool for business analysts to perform basic analysis activities, such as data sorting, filtering, and calculating statistics like averages and sums. The script mentions Excel's functionalities as essential for a business analyst to be efficient and perform quick data analysis.

💡Business Analyst (BA)

A Business Analyst is a professional who specializes in analyzing business processes and systems, often with the goal of improving performance and efficiency. In the video, the BA is expected to be proficient in Excel to handle basic to intermediate level analysis tasks, such as using COUNTIF, SUMIF, and VLOOKUP functions, which are critical for data manipulation and interpretation.

💡COUNTIF

COUNTIF is an Excel function used to count the number of cells within a range that meet a single criterion. The video script uses COUNTIF as an example to demonstrate how a BA can quickly count occurrences of specific values, such as the number of vehicles registered in a particular year, which is essential for data analysis in a business context.

💡SUMIF

SUMIF is another Excel function that allows for the summation of values in a range based on whether they meet a certain condition. While the script does not explicitly mention SUMIF, it is conceptually similar to COUNTIF and would be used in a business analyst's toolkit for aggregating data based on specific criteria.

💡AVERAGEIF

AVERAGEIF is an Excel function that calculates the average of all the cells within a range that meet a single criterion. The script touches on this function as a means for a BA to find the average model year of vehicles, which helps in understanding trends or patterns within the data.

💡VLOOKUP

VLOOKUP, which stands for Vertical Lookup, is an Excel function used to retrieve information from a table based on a key value. The script discusses an alternative to VLOOKUP, the XLOOKUP function, which is recommended for its ease of use and versatility in matching and retrieving data from different data sets.

💡XLOOKUP

XLOOKUP is a newer Excel function that provides more flexibility and ease of use compared to VLOOKUP. It is mentioned in the script as the preferred method for performing lookups, allowing a BA to merge information from different data sets, such as matching vehicle models with their respective electric ranges.

💡IF Statement

An IF Statement in Excel is a logical test that returns one value if the condition is TRUE and another value if the condition is FALSE. The video script describes using IF Statements to determine eligibility for incentives based on vehicle electric range, showcasing how BAs can use this function to make decisions based on data-driven criteria.

💡Pivot Table

A Pivot Table in Excel is a dynamic tool used to summarize and analyze data by organizing it into a multi-dimensional table. The script explains how a BA can use Pivot Tables to quickly slice and dice data, such as counting the number of electric vehicles by model year, providing valuable insights for decision-making.

💡Data.gov

Data.gov is a public-facing website that provides access to datasets generated by the US government. The script mentions using Data.gov as a source for publicly available data, which can be used by BAs to practice Excel skills and analyze real-world data sets for insights.

💡Database

A database is an organized collection of data, typically stored and accessed electronically. The video script suggests that practicing database skills can be valuable for a BA, and using Excel to manipulate data is akin to working with databases, as both involve organizing, retrieving, and analyzing information.

Highlights

Basic Excel functionalities for business analysts are covered, emphasizing the importance of these skills for a BA.

Data used in the video is publicly available from data.gov, suggesting a resource for practicing Excel and database skills.

The significance of understanding Excel tools' capabilities rather than memorizing every step is highlighted.

Demonstration of organizing data from a single large table into two separate sheets for clarity.

Introduction to 'CountIf' function to tally specific values within a dataset.

Explanation of using 'SumIf' and 'AverageIf' for conditional numerical analysis.

The 'XLOOKUP' function is introduced as an efficient way to merge information from different datasets.

A practical example of using 'XLOOKUP' to match vehicle registrations with their electric range.

IF statements are discussed as a method to transform and understand data based on certain conditions.

Creating an 'Eligibility' column using IF statements to filter vehicles based on electric range for incentives.

Pivot tables are introduced as a powerful tool for summarizing and analyzing large datasets.

A step-by-step guide on creating a pivot table to count electric vehicle model years.

Using pivot tables to identify trends in electric vehicle popularity over the years.

Demonstration of how to filter pivot tables to provide specific insights, such as the impact of banning a vehicle model.

The importance of learning Excel functionalities for quick data analysis and decision-making is emphasized.

Encouragement for viewers to practice creating pivot tables to understand different data manipulation techniques.

A call to action for viewers to subscribe for more content and to engage with the creator for additional questions or training.

Transcripts

in this video I'm going to be covering

the basics of Excel for business

analysts note that these are pretty much

the bare minimums for a business analyst

every ba should be expected to be able

to do this this is very basic and

generic level

analysis activities

one thing I like to note is that this

data is not something that I got from my

company this data is publicly available

and If you are wanting to practice Excel

or maybe you want to input some

information into a database to practice

some database

skills and techniques there is lots of

publicly available data out there I

actually just went to data.gov

went to the most viewed data sets

and just picked from the number one data

set and I downloaded it and kind of

looked it over to see how I could move

things around to best talk about these

functionalities so if you're looking for

data that's data.gov find some data sets

download them play around with them and

see what you can do

another important thing to mention is

that it doesn't matter which tool you're

using the more important things that you

remember that these tools are capable of

doing these things more than likely

you're not going to remember every step

I go through while going through this

video and things may change as well over

time depending on at when you're

watching this video but if you

understand that the tools can do these

things you can at minimum look up how to

do them again and then you can do your

analysis work faster and be more

efficient as a business analyst foreign

if we look at my file you can see I have

two different sheets I actually broke up

the information that was available at

data.gov to kind of better illustrate

the types of functionalities that you

need to be able to execute as a ba all

of this information was originally kind

of together in one big table but I broke

it up because if you were getting this

information in the real world it may not

come together and these features and

functionalities are really to help you

put it together so you could make sense

of what you're looking at in this case I

got registration information so if you

look at this data this is essentially

the individual who registered it or at

least a unique vehicle that was

registered where it was registered and

then the model in year number

and then here would be like if you were

to get information from just like a

vehicle information database so the make

model what kind of car type it is in

this case we're only dealing with

electric vehicles so these are either

electric plug-in hybrid Etc and then

maybe the vehicle range and there might

be a database or that information exists

separately from vehicle registration

data and so those are the two kind of

data sets that we're playing with

generic vehicle data and then vehicle

registration data that of like a local

government

so the first kind of set of

functionalities I want to cover is what

I call the ifs if you look at Excel

Excel does make it easy to do a lot of

things really quickly so if we look at

these rows here or these columns rather

if I just hover over one it's going to

give me some information right away so

if we look down here at the bottom you

can see the average the count and the

sum and so that's if you added all these

numbers together this is the number

you'll get obviously these are years so

that's not necessarily valuable account

so I immediately know that there's about

13 000 or excuse me 130 000 rows here

as well as an average so the average

model year is 2019 and so I can get that

right away

without having to know any

functionalities and all I've done is

hovered over the columns so same here

the count obviously should be the

similar

it is important to note that it doesn't

count blanks so that's really what the

count is all the non-blank rows that are

available so there's a few less here for

some reason

versus model years which means there's

some blanks in the data somewhere but

that's okay that's not relevant to what

we need to accomplish

okay so let's talk about the ifs this is

kind of you know basic analysis

one-on-one 101 we looked at the total

numbers but what if I just want to count

or an average or a sum of a specific

value right so if I just wanted to know

how many rows are 2018 versus 2017 or

whatever the quickest way I could do

that if I add filters up here is to

click the filter find that value

and so let's get this at 2018 so if I go

to find 2018 I can apply the filter and

then it's going to give me a count down

at the bottom of essentially what that

filter down to which is a quick way if I

want to know just 2018 but if I want to

get the numbers for all of them fairly

quickly then I have to go through each

one of these it'd be kind of tedious and

I gotta you know scroll down so what I

could do instead is let's say I wanted

to get a count of everything that's

2018.

so I'm going to type in 2018 here

because that's what I'm looking for and

then we're going to use the basic

formula functionality here

yeah so I'm going to find my formula is

here it gives you kind of a shortcut of

all the formulas that are available

these most recently used are really my

most recently used but as you can see

there is a ton and I honestly don't know

what many of these do and as a business

analyst you may never need to find out

but you know the more you explore the

more trainings you get on Excel the more

powerful you can become a tool but back

to what we're focused on the countif and

so if we go here we select the count if

and we go ahead and insert the function

in this case because I'm using this row

or this column as kind of my reference

I'm going to point at that so the range

that I'm really wanting to count is this

model here so the range is like all of

the cells that you want to to count so

I'm going to just select the whole row

or column rather

for my range and then for the criteria I

really want it to be 2018 I could input

2018 right here but to make it more

efficient I really want to say I want

you to use this column so anytime in

this range where this number is equal to

what's in this column I want you to

count it so I'm going to go ahead and

say done

and so for the model year 2018

there's about 14 000

Vehicles okay so for 2018 we have about

fourteen thousand but I want to know

2019 2020 Etc so I can go here to 2019.

uh 2020.

1

too good I think I have this as a

general if I have it if I had it listed

as a number I could have just selected

this right here and went down and it

would have Auto populated oops

um but that's okay

we missed that shortcut and then now I

can go to this and rather than having to

recreate that formula I'm essentially

going to copy the formula down so

instead of referencing

um 2018 it comes down and it represents

references the next one down to 2019 and

on and on and on and now I can see how

many cars are in 2019 20 21 and 22. so

that's account if

um

similar to the sum and average

unfortunately my data set here doesn't

really have numbers that are super

valuable for an average right there's no

point in averaging the model year for

the year 2018 because the average will

come out to be 2018 but if say you're

selling something and at different rates

and maybe you wanted to understand you

know what's the average of you know a

particular product or maybe a particular

demographic or whatever it may be you

can use that quit countif or some if or

average if and you're really just saying

hey give me an average but only if this

is true about the value

so I'm going to actually clear this

and show it in a slightly different way

and so that was if I wanted to show it

right here on the screen in our case I

actually have another table with all of

these vehicles so maybe rather than

creating my own new table I say hey how

about I just count

here right and I create account so I I

can create a new column here call it

count and I want to know

the count of these model numbers right

so again I can go to my countif

I could insert the function the range

that I want to count so the range is

actually over on the other table so I'm

on this sheet I can go back to this

sheet

click this model number

and that's the range I want to look at

and the criteria is this model name so

I'm going to just go ahead and select it

done

and it auto populates so now I know I

have a count of each one of these

vehicles and how often they essentially

come up in the Washington State Electric

Vehicle database

so that's count again you could do this

similar with average or some in this

case this data is just not great for

averaging or something but likewise I

could say sum or average all the

information

that falls within this criteria

so next let's talk about a vlookup a

vlookup is a way to marry information to

make it a little bit easier for you to

understand the information and maybe do

something valuable with that information

so in this case I'm looking at this

vehicle registration information so

these are all the registrations in in

the state

um by model number one thing that's not

available in this data set is the

vehicle range and that vehicle range is

used to determine a few other things

about this vehicle's registration right

and so that range is here so one way I

could do it if I wanted to do it the

long way is I could come here I could

find the range of a particular vehicle

so the Audi Q5 has a range of 23 an

electric range of 23 miles I'm going to

come back here and I can say maybe

filter on all the Audi q5s and input 23

miles obviously that would take a very

long time to do that for all of the

different rows so the way they do that

more quickly is via an X lookup an X

lookup is a somewhat new version of the

vlookup it's a little bit easier to use

in the vlookup and if you know how to do

an X lookup you can accomplish

everything that a vlookup does so I

could just I just recommend learning how

to do the X lookup

um so let's go ahead and do that so we

we're here I want to know the electric

range for all the the essentially

registrations in my County so I'm going

to come here I'm going to go ahead and

put EV range that's what I'm going to

call my new

um column

and then I'm going to do an X lookup so

let's start the X lookup formula so I'm

going to come here select X lookup hit

insert function so I can kind of start

my path

um the first thing I need is to look up

value so this is what I want to compare

it against and for us it's the model

name so I'm going to go ahead and select

I'm I've selected this field I'm going

to hit Model X

and now it's always going to look up the

the name Model X

and then you're going to select your

lookup array and this is essentially the

the column that you want to reference so

in this case it's over here in this

other sheet inside a table and obviously

I'm looking up model number so I want to

match up the lookup value from the other

one with the model number here so I'm

just going to select this whole column

so it's referenced

and then next it's going to ask me to

for my return array right and so that's

of all of these rows which thing do I

want it to return back to this space

right so I go to the return space

obviously I want the EV range that's

over here in column D so I'm going to

select column D there are a few other

fields you could use here depending on

the type of lookup that you're doing

sometimes you don't have an exact match

and you maybe want to use some what they

call fuzzy logic if you want to learn

more about if not found match mode in

search mode when you're here you can

just click more help on this function

and it'll give you a full explanation on

what all of those things are what values

you could put potentially put in there

to maybe help clarify your search in

this case I know I have exact matches so

I don't actually have to mess with these

because the default is to find the exact

match so now that I know what I want to

reference which column I want to compare

it against and then what information I

want it to return I'm going to go ahead

and hit done

and now for every row I have the EV

range for this model number obviously

there's a lot of ways you could use this

but it's really to marry information

from one data set into another so you

can have it all in place so you can

begin the slice index

okay so the next type of functionality I

want to talk about is an if statement I

mean if statement is one of the most

basic ways that you could easily

essentially transform information to

help you gain something out of it add a

little pepper to it to help you

understand the information a little bit

better make it easier to digest with

whatever it is that you need to

understand in this case we're talking

about vehicle data and let's say we we

maybe have like a an HOV lane or a toll

Lane that we want to make it free for

anybody who has a a vehicle that's you

know above a certain range right above a

certain electric range right so we're

going to call this the

eligibility

column

and we want to know of these vehicles

who who's going to be eligible for this

new incentive program and we'll set the

limit at 50. so for every car model who

has a electric range that is greater

than 50 we're going to give them this

incentive so how do how do we kind of

get that information in here so we could

slice and dice and we do that we can do

that quickly and easily with an if

statement so if we go up to our top

column or top row here

um you know if you hit function it opens

this menu here you hit if statement the

if statement has three um

segments this there's the test so the

thing you're measuring against what you

want it to show if it's true and what

you want it to show it if it's false and

so in this case our if statement is 50

50 mile or greater range so ours is

going to be the the test is going to be

if this range is greater than 50. so

we're going to say if this range

is greater

than 50.

then we want it to be eligible so we're

just going to use the word eligible

and if it's not eligible we could just

say not eligible

and it's just that easy so hit done

and very quickly and easily I can see

all the vehicles that are eligible

versus not eligible if I was sending a

communication I could just filter and

say okay and for everyone who's eligible

go ahead and send out send out an email

or send out a letter or whatever it may

send out the sticker so they can go

through the the toll without having to

pay

um and it just makes it easy to

essentially slice and dice the

information based off of values that are

available here

um you could also do nested if

statements to say you know if this is

true then in this rather than having

just eligible or not eligible you could

have another calculation and if it's

false you could have a different

calculation to give you

smart intelligent information to help

you slice and dice

obviously you should look up these

functionalities

on your own to learn more dig into them

see what information is available but as

a business analyst these functionalities

will help you slice and dice information

really quickly get to answers more

quickly and you don't have to like count

row by row or filter like a hundred

times to get each individual

premutation you just throw in the

formula and you get to where you need to

get

so the last thing I want to cover is the

pivot table

um a pivot table is one of the easiest

ways to size and Max information quickly

in Excel so I'm going to go here to

insert you can see here that this has

already been created as a table so when

I come here to create a pivot table it's

already going to reference that table

name that of the table that I've already

created you don't have to create a table

you can just use arrange I just find it

easier because if I use a table name and

I add information to this table I don't

have to go back and adjust the range

it's just going to be automatic so let's

create this pivot table and let's just

quickly see some of the things you might

be able to do and so in this case you

know it automatically gives you the all

the column names here and you know

that's how you can start slicing and

dicing this information so let's start

with something like model year that

we've talked about

it'll give you all the model years that

are available in the data set and here

we might want to know how many vehicles

of each model year exist and so we can

come here and go to Value so that's

going to give you

this additional value and another column

in this case I decided to sum it up so

the model here is a number Excel doesn't

know that that number in this case is a

year so I thought maybe the smartest way

to put that information together is to

just add it all together in this case

that is not the case we actually just

want a count of how many times that

model year shows up so we can come here

click this eye icon and change that to

count

and we can very quickly see the count of

model years electric vehicle model years

that exist in the data set by the model

year I'm it's very interesting I I

didn't know those electric vehicles way

back then these must be hybrids of some

kind I'm not sure we can go back and

find oh actually we don't have to go

back and find out if we want to find out

um which vehicles these are we can come

back here and come to the model and just

add it underneath and then now I can see

exactly what vehicles these are I didn't

know these vehicles existed as EVS I'm

at that point in time but hey that's the

the purpose of creating a pivot table

like this helps you learn information

really quickly so you can see back in

2011 the most popular electric car was a

Nissan Leaf that was one of the only

cars available within 2012 the the

market went up quite a bit and added

many more cars the volt started catching

up here and on and on we've started

adding more and more electric vehicles

to the lineup so it helped us very

quickly see that and learn additional

information so that's kind of how a

pivot table works you could add

additional rows you could add additional

columns in this case I don't know that

there's a column that's

particularly valuable

um

maybe if I'd Done It This Way model year

you can see

how often a certain vehicle has been

purchased per model year so you can see

you know if we go back to

um

the the leaf which was one of the early

ones you can kind of see the trend of

how it's it's grown so over the years

people have bought more and more and

more then it kind of started to Trail

off down here and we could probably

guess that that's due to the fact that

many more cars became available on the

market but I have very quickly slice and

dice that information so it allows me to

essentially provide information decision

making information

to stakeholders very quickly

whatever it might be so maybe we want to

understand if we say hey we're going to

ban all Audi e-trons how many cars are

we really talking about how how many

angry customers are or how many angry

residents will there be in this case we

know there'll be

884 or if we were to say

you know we're only going to allow a

certain incentive for vehicles past 2018

then again you can come back here and

see okay if we do it by that let's get

rid of model

if we do it that way this is the number

Beyond 2018 you can add filters as well

so here we're talking about model years

so if I added model year as a filter

this is showing all I can you know

deselect and say just include

you know from 2021 onward right and I

can get those counts

um and it got rid of so we can go to

models just so we can get some

information so I've filtered based off a

model year and I can see all the models

there so it allows me to slice and dice

really quickly without making a bunch of

fancy functions um there's a lot of

different things you could do and I

highly recommend jumping in here

creating a pivot table and just playing

around with the data and seeing how

different different ways of manipulating

manipulating it can create value while

you're looking at the information

so that was my brief rundown of the most

basic valuable things that a ba should

be able to do in Microsoft Excel and

those are the ifs so account if average

if sum if a lookup in this case I went

with an X lookup vlookup is the

alternative but I recommend sticking

with X lookup and finally being able to

create a pivot table to really be able

to slice and dice information in

whatever way you need quickly and easily

so you can answer questions and provide

valuable Insight right away

hey if you found that video useful let

me know by giving me a thumbs up if you

have any additional questions about this

video or ba work in general let me know

in the comments and I'll either answer

you there or create a whole new video to

answer your questions so subscribe to

see your questions get answered and if

you would like something a little bit

more comprehensive to help you step up

your resume be a little bit more

confident in your ba interviews and be a

standout ba candidate check out the link

in the descriptions for some additional

training opportunities and as always

thanks for watching