How Business Analysts Use Excel - Business Analysis Software Tutorial
Summary
TLDRThis 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
π 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.
π 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.
π 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.
π 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.
π 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.
Mindmap
Keywords
π‘Excel
π‘Business Analyst (BA)
π‘COUNTIF
π‘SUMIF
π‘AVERAGEIF
π‘VLOOKUP
π‘XLOOKUP
π‘IF Statement
π‘Pivot Table
π‘Data.gov
π‘Database
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
5.0 / 5 (0 votes)