Data Analysis with Microsoft Excel
Microsoft Excel is one of the most popular applications for data analysis. Equipped with built-in pivot tables, they are without a doubt the most sought-after analytic tool available. It is an all-in-one data management software that allows you to easily import, explore, clean, analyse, and visualize your data.
What you’ll learn
- Analyse data with Excel.
- Transform and clean data with Power Query.
- Create models and perform data analysis with Power Pivot.
- Summarize data with Pivot Tables.
- Create data visualization with Pivot Charts.
- Create macros.
- Debug Macros.
- Sort data using excel macros.
- Filter data using excel macros.
- Manually create macros with VBA.
Course Content
- Microsoft 365 Setup –> 6 lectures • 12min.
- VBA and Macros –> 16 lectures • 1hr 39min.
- Data Transformation with Power Query –> 6 lectures • 30min.
- Data Visualization and Analysis –> 8 lectures • 1hr 1min.
Requirements
Microsoft Excel is one of the most popular applications for data analysis. Equipped with built-in pivot tables, they are without a doubt the most sought-after analytic tool available. It is an all-in-one data management software that allows you to easily import, explore, clean, analyse, and visualize your data.
Pivot tables are known for being the most purposeful and powerful feature in Excel. We use them in summarizing the data stored in a table. They organize and rearrange statistics (or “pivot”) to bring crucial and valuable facts to attention. It helps take an extremely large data set and see the relevant data you need in a crisp, easy, and manageable way.
Sorting data is a very critical and vital part of Data Analysis. You can sort your Excel data by multiple columns or even a single column. The sorting is done in ascending or descending order as well.
What is Filter in Excel? The filter in excel helps display relevant data by eliminating the irrelevant entries temporarily from the view. The data is filtered as per the given criteria. The purpose of filtering is to focus on the crucial areas of a dataset.
Power Query allows a user to import data into Excel through external sources, such as Text files, CSV files, Web, or Excel workbooks, to list a few. The data can then be cleaned and prepared for our requirements.
Power Pivot is a data modelling technology that lets you create data models, establish relationships, and create calculations. With Power Pivot you can work with large data sets, build extensive relationships, and create complex (or simple) calculations, all in a high-performance environment, and all within the familiar experience of Excel.
A pivot table is a table of grouped values that aggregates the individual items of a more extensive table within one or more discrete categories. This summary might include sums, averages, or other statistics, which the pivot table groups together using a chosen aggregation function applied to the grouped values.
A pivot chart is the visual representation of a pivot table in Excel. Pivot charts and pivot tables are connected with each other.