[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]
