Microsoft Excel 2021: Data Analysis

Name: Microsoft Excel 2021: Data Analysis
Author: Bob Akbar

Obtain analysis of your data from internal and external sources.

Preview the first lesson free — get full access to all 3 lessons.

Course: On-Demand

Hello, my name is Bob Akbar, and in these lessons you will learn power queries, auto filters, and pivot tables. In this lesson, I will show you how to use power queries. Power queries enable you to be able to connect to external data sources. For our example, we have a comma separated values file. You may also connect to databases, such as SQL server databases, which are very common in business for storing data. Also Microsoft access databases, and to use these you can contact your database and or network administrators to see how you can connect to these files. The way when you connect is going to behave similarly to the actual CSV file, which is the one that we are going to use today for our demonstrative purposes. When we open it, it asks us where the file is, and you can see, even though Microsoft Excel does open it, I could have just opened it to a spreadsheet, I am going to choose to use the power query instead, so I can do some editing on it. We simply click on that and click import. And here we are. The delimiters, which is another way of saying how the data is separated can be columns equal signs, semicolons, commas, spaces, and tabs. Commas and tabs are the most common, and we would also try spaces. If you are trying to guess what the file is, often you would get these kind of files if you are getting a file that has been exported from another software, such as your accounting software or your lead tracking software or a government's report from the Federal Reserve that you may have downloaded. Okay, so we are going to stick with our comma, and there are a few other options. We can decide if you want it to be 200 rows of the entire database where it tries to guess what the structure is, or you can tell if not to detect any at all, and you can try to define it. We're going to leave it the way it is, and we're just going to say transform the data. And here we are. We have arrived at our power query editor, which is what this lesson covers. Now we can choose columns, we can choose which ones we want, we can remove columns, we can keep rows, and if you notice here, it tells us what we want to keep. We can also remove rows. Removing duplicates is one that comes in very handy, also removing blank rows. If there is one that doesn't have any data, that also comes in handy as well. Now we can go to advanced editor and put things in there, where it gives us the programming language that is behind it. And you can see some things that are familiar to you, such as the file location. It also gives you the type. This is an integer 64, the next one is text, that's what is detected. And once you get into understanding those, you can change those as well. Next we can see here, we can change the source type in the query. We can also go into managing parameters and put different settings in there. We can change the source of the file. Now going back to where the query is, if you notice when I double click on it, I can put in the headers. Some files do have the headers and if it did, it would have imported it for us. You can see here, it says use first row as header. But in our data, we do not have that. So we're going to put that in there. And for this, we're going to put item for column three. We are going to put in customer and so forth. So once we have all these things ready, if you notice this is previewed now, everything is set up, we can now go and transfer this into our actual column. Remember some of the key options here, if there's a header, we can put those in there. If we need to remove columns, we can choose which ones we want. Here we are. So if we only want the item and the customer, we can get rid of the rest. And this helps us quite a bit with cleaning up our data as far as what we want to import. We can deselect all, select all. We can also remove duplicates and blank rows. Those are two of the common ones. Also if there are any errors, there may be some that were not properly formatted coming in, we're able to take these out as well. Thanks for watching. Stay tuned for the next lesson where I will show you auto filters.

Advanced Provider : Bob Akbar 3 Lessons · 17m in English

Course Description

In these lessons, you’ll learn how to link data from internal or external sources into Excel for analysis. Many companies store their data in databases or text files, and these two methods are also the most common methods for exporting data, as well. By being able to link Excel to these sources, you’ll be able to harness the power of Excel analytics to properly sort through data and present your conclusions.

We’ll also discuss how to use Power Queries to filter and limit data into the required fields to produce a more user-friendly environment for analysis. Once the data is properly filtered, you’ll then learn how to apply additional filters based on your specific analytical needs. Finally, we’ll go over how to use Pivot Tables to present results in an orderly, easy-to-understand format.

What You'll Learn

Link data from internal or external sources, such as databases and text files, into Excel for analysis
Define queries and import data into Excel from another source
Use Power Queries to filter and limit data into the required fields for a more user-friendly analysis environment
Apply additional Auto Filters based on your specific analytical needs
Filter and format imported data for analysis
Use Pivot Tables to present results in an orderly, easy-to-understand format

Key Takeaways

Databases and text files are the most common methods companies use to store and export their data, and Excel can link to both.
Linking Excel to external data sources lets you apply Excel analytics to sort through data and present your conclusions.
Power Queries help filter and limit data into the required fields to create a more user-friendly analysis environment.
Once data is filtered, additional Auto Filters can be applied based on specific analytical needs.
Pivot Tables present analytical results in an orderly, easy-to-understand format.

Frequently Asked Questions

What will I learn in this Excel 2021 Data Analysis course?

You'll learn how to link data from internal or external sources into Excel, define and import queries, use Power Queries to filter and limit data, apply Auto Filters, and present results with Pivot Tables.

What data sources does this course cover linking to Excel?

The course covers linking Excel to databases and text files, which are described as the most common methods companies use to store and export their data.

What topics or lessons are included?

The lessons cover Power Queries, Auto Filters, and Pivot Tables.

What skills will I gain from this course?

You'll build skills in Analytical Skills, Microsoft Excel, Data Analysis, Data Driven Instruction, Microsoft Analysis Services, and XML For Analysis.

How are analysis results presented in this course?

The course covers using Pivot Tables to present results in an orderly, easy-to-understand format.