[MUSIC PLAYING]
Welcome to the first video in this new class, Data Analysis
and Business Intelligence Made Easy with Excel Power Tools.
Now this class really should be called Excel Data Analysis
Basics or E-DAB, because if you've never
done data analysis before and you
want to learn the basics of data analysis,
you came to the right place.
Now why Excel?
Because Excel is the most used data analysis
tool in the world.
We have straight Excel features.
We can build pivot tables, use Power Query, the new Power
Pivot and even Power BI Desktop.
Yes, indeed we'll learn all these tools,
so you can take all that data you have
and convert it into insight.
Now this is video number 1, an introduction
so we have to go over who the instructor is,
what is the scope of the class, what version of Excel
you should be using.
We're going to define data analysis and business
intelligence, talk about the goals of the class.
We'll talk about the videos that will be presented
in this class, the amazing files you can download and use
to follow along, and what you will
gain after taking this class.
My name is Mike Girvin.
I'm a Highline College instructor, a Microsoft Excel
MVP since 2013.
And I created the excelisfun YouTube channel,
been posting videos since 2008 with over 3,000 videos
and over 100 playlists and classes.
Now the scope of this class, this
is an introduction to data analysis.
It is for people with no experience with data analysis.
Although, if you like watching Fun with Excel
and you know a little bit about data analysis,
doesn't hurt to watch this.
So with no experience, you'll get
to learn how to turn data into useful information and insight.
And this is a prerequisite for the advanced data analysis
class here at the excelisfun channel, called MSPTDA.
And we will definitely have a link
to that playlist and that class at the end of this video
and also at the end of the class.
Now what version of Excel are we going to use for this class?
Excel Office 365.
Now really, you may have all sorts of other versions
like Excel 2016, Excel 2019.
Those will work for almost everything in this class.
But Office 365 has the advantage.
It's the only version that gets updates with new features
every month.
And in fact, moving forward in Excel history,
it's the only version to have.
Now you may not have it right now
and some of the other versions will work.
But get it as soon as you can.
Now we'll also be using Power BI Desktop.
That's a free download from Microsoft.
And we'll download it during the last two videos of this class.
Now we need to define data analysis and business
intelligence.
Now data analysis the definition,
to convert data into useful information for decision
makers.
Now business intelligence is exactly the same definition,
except for in a business context.
A simple example-- and simple examples
are always awesome when you're learning
something for the first time.
Here's raw data.
We have two columns--
sales rep and sales.
The first row or record show sales
rep Ty had sales of 7,500.
The third record shows Abdi sales rep sales of 11,500.
Now that's the raw data.
But if our goal is to determine whose sales were biggest,
well the raw data alone is not going to give us the answer.
We have to convert that raw data into useful information.
Notice, we first create a unique list of sales rep names.
And then we do our adding.
We're adding the sales for each sales rep.
Boom, we have our useful information.
Now we can answer our question.
The answer, Gigi.
Now business intelligence, we'll define this as convert data
into useful, actionable, refreshable information
for decision makers in a business situation.
Right, we have a business situation.
But now instead of just useful, it says actionable.
All that means is from the useful information,
we can act to make a decision.
Now refreshable, that's the amazing part.
That's the part that Excel and the power tools can do so well.
That just means when we get new data,
we want our useful information to update.
Now why is it called business intelligence?
Well because the business is intelligent
because it's making decisions based on data.
Now both data analysis and business intelligence
will help to see trends and patterns,
ask questions, and gain insight.
Short and sweet definition for both, here it is.
We're simply taking data and converting it into information.
Now the goal of data analysis and business intelligence,
well, we need to create useful updateable,
actionable information for decision makers
in the form of reports, visualizations, and dashboards.
Here's an example of a report we need
where it shows the total revenue by company and product.
But look at this.
We're given data in the form of various text files.
And we have some data inside an Excel spreadsheet.
Now the cool thing is inside of Excel,
we'll get to use Power Query to take all this data,
convert it to the right form, and then
we'll use a pivot table to create our report.
As a second example, over in Power BI Desktop,
we'll have to create this visualization
from various Excel files.
Well, we'll use Power Query to go and get the data,
transform it, and load it into Power BI Desktop.
And then we'll use the various visualizations to plot sales
revenue on a map and sales by product in a bar chart
and then sales by city and sales rep in a second bar chart.
Now, next we want to talk about some of the goals
for this class, some of the achievements you should expect.
Now we're going to get an introduction to these Excel
power categories, Excel spreadsheet formulas
and features, the standard pivot table, Power
Query in both Excel and Power BI Desktop,
data model pivot tables using Excel Power Pivot,
and data model visualizations using Power BI Desktop.
More specifically, goals, we want
to get an introduction to the specific data analysis power
tools in Office 365.
We get to learn about the Sort feature, Filter, Excel table
features for dynamic data, spreadsheet functions
like SUMIFS, COUNTIFS, AVERAGEIFS, the amazing Office
365 dynamic arrays, standard pivot table and slicers, charts
and visualizations, Power Query, the VLOOKUP function,
the relationship feature, Power Pivot data model,
DAX functions which are the functions inside Power
Pivot and Power BI Desktop data model, SUMX, AVERAGEX,
RELATED, and COUNTROWS, and of course Power BI Desktop.
We'll also have an introduction to data analysis terms
and processes, such as data, proper data set, delimiter,
relationship, ETL, data model, columnar database, dashboard,
and much more.
We also, as goals of the class, need
to learn how to perform these data analysis steps.
Import the data, then clean and transform it.
Build the data model.
Build reports, visualizations, and dashboards that deliver
that useful information.
Then when the new data arrives, we're
going to have to make sure we've built the solution so we
can refresh that information.
Then we have to assess the usefulness of that information
and repeat steps as necessary.
Now actually, there are a few steps before
importing the data.
For example you might have to determine
what questions need to be asked, what sort of reports you need.
You might have to search throughout the particular
entity, find out what people need,
find out where the data is.
But we're going to start at importing data.
As a preview of the videos we will have--
well, number 1, you're watching that one right now.
Number 2, we'll define data, proper data sets,
Excel table, sorting, filtering, and the ever important
logical tests--
ubiquitous in data analysis.
Number 3, we'll make summary reports with Excel spreadsheet
formulas.
Then in number 4, we'll make summary reports
with standard pivot tables and slicers.
Then we'll talk about visualizations, tables, charts,
conditional formatting, and dashboards.
Then 6, we get our first introduction
to Power Query, where we get to learn how to clean, transform,
and import the data.
We'll talk about VLOOKUP which sometimes is good in the Excel
spreadsheet, the relationship feature
to create Power Pivot data models.
We'll talk about Power Query and Power Pivot
together for big data, and then Power BI desktop and Excel
in Power BI Desktop together.
Now the downloadable files and practice problems.
That means every video that you watch,
you can download all the files, the Excel files, Power BI,
and the source data files, so you
could follow along with exactly what you see in the video.
There'll be practice problems after each video
and some PDF notes summarizing each class video.
Now what will you gain after taking this class?
You will have experience with all the power tools in Excel
used for data analysis.
You'll be able to take your own data,
convert it to refreshable reports, visualizations,
and dashboards that you can use for your own decision making.
And finally, you'll have introductory level skills
in data analysis, So you can move on to more advanced data
analysis and business intelligence classes
for free here at YouTube.
And in fact, you can go on after this 11 video class
and study Excel Basics.
Now Excel Basics covers the same data analysis topics
that we cover in our class.
But if you like formulas, that's an awesome video.
If you like advance formulas, the Highline College Advanced
Excel class is for you.
And most of you will probably want
to move on to the Highline College BI348, Microsoft Power
Tools for Data Analysis.
That's the MSPTDA class.
And if you like what you see when
we learn about the new Office 365 dynamic arrays,
there's a whole playlist of videos just on that topic.
All right, if you like that video,
be sure to click that thumbs up, leave a comment, and subscribe,
cause there's 10 more videos coming in there's E-DAB class.
All right, I'll see you next video.
[MUSIC PLAYING]