The Pathway to Mastering Microsoft Excel in Data Analysis
GEORGIA INSTITUTE OF TECHNOLOGY
DATAWORKS
Table of Contents
Microsoft Excel Overview
The learning pathway in this module will engage in practical exercises and real-world application to solidify understanding. There are six engaging modules with a cumulative project to assess your knowledge and expertise in utilizing Microsoft Excel in Data Analytics. It is a progressive pathway with a structured approach by building foundational skill with the introduction of Excel and how it is utilized in data. Advanced techniques will be utilized to demonstrate the proficiency of formulas and functions, data cleaning, and automation.
Start Here
Whether you are just beginning your journey in Data Analysis or deciding to refresh your knowledge and skills, you are in the right place. Before we explore or relearn all of the beautiful things of Microsoft Excel, let’s see what you know first.
Module 1: Introduction to Excel for Data Analysis
Dataquest
This is beginner friendly and interactive learning pathway to introduce the foundation of data analysis in Excel.
This pathway has three individual lessons that will introduce how and why data is utilized in businesses and organizations. It can be self-paced or facilitated based on individual needs of the participant.
Activity 1
In these activities, you will demonstrate your knowledge and skills by creating and formatting spreadsheets. As the course continues, activities will be on beginner, developing, or advanced levels. Be sure to unzip each file to obtain the datasets in Excel.
Module 2: Data Manipulation and Visualization
Practice
Key Topic 1: Sort and Filter Data
Key Topic 2: Using Basic Functions (SUM, IF, AVERAGE, MIN, MAX)
Practice
Key Topic 3: Chart Formatting
In the lesson, there are two options: beginner or intermediate. Data fellows can choose which best fits their needs and advancement on charts and visualizations.
Beginner | Intermediate and/or Advanced |
This pathway is a more hands-ons approach as the engaging activity will be step-by-step in using different types of charts. Using the Dataquest platform, this will guide you as you navigate Excel and use various charts/representations. Lesson 1: Dataquest: Pie, Colum, and Histogram Charts | For more intermediate and/or advanced participants, there is a live demonstration along with an engaging activity which provides a visual representation and dataset Video: Charts in Excel | Excel Tutorials for Beginners Dataset: Excel Charts Tutorial File.xlsx |
Activity 2
In these activities, you will demonstrate your knowledge and skills by sorting and filtering datasets, calculating statistics, and creating and formatting various charts. The activities will be on beginner, developing, or advanced levels. Be sure to unzip each file to obtain the datasets in Excel.
Sort & Filter | Calculate Stats | Create & Format Charts |
|
Checkpoint 1
In this learning pathway, there will be three checkpoints to ensure data fellows understand the skills and apply the knowledge in a comprehensive assessment. This first checkpoint will assess the key objectives in modules one and two. Participants must score 80% or higher. If the score is not an 80% or higher, participants should be given another opportunity with another form of assessment.
Checkpoint #1:
Module 3: Advanced Functions, Formulas, and Data Cleaning
Practice
Key Topic 1: Logical functions: IF, AND, OR
Key Topic 2: Lookup functions: VLOOKUP, HLOOKUP, INDEX, MATCH
Practice
Key Topics 3: Remove duplicates, text functions, data validation
Activity 3
In these activities, you will demonstrate your knowledge and skills by using logical and lookup functions and cleaning and validating datasets. The activities will be on beginner, developing, or advanced levels. Be sure to unzip each file to obtain the datasets in Excel.
Module 4: Let’s Pivot with Tables and Charts
Practice
In this module, we will begin to visualize the data utilizing PivotTables and PivotCharts. You will explore these tools in Excel and learn how to summarize and analyze large datasets.
Key Topics 1 & 2: What are PivotTables and PivotCharts and how to create them
Pathway 1: Use the link to download the practice file (look in the description) and watch the guided video. How to Create Excel Pivot Tables & Pivot Charts - Beginner's Guide | Pathway 2: Use the link to download the practice file (look in the description) and watch the guided video. |
For more intermediate/advanced, this is an additional learning tool: Create Excel Pivot Table from Multiple Sheets: The FASTEST Way |
Practice
In this module, we will begin to visualize the data utilizing PivotTables and PivotCharts. You will explore these tools in Excel and learn how to summarize and analyze large datasets.
Key Topics 1 & 2: What are PivotTables and PivotCharts and how to create them (cont’d)
Key Topics 3: Data Filtering - slicers and timelines
Download the file from the description to engage in hands-on lesson.
3 Ways to Filter Data in Excel
Activity 4
In these activities, you will demonstrate your knowledge and skills by creating and summarizing data with PivotTables & generating PivotCharts to visualize summarized data. The activities will be on beginner, developing, or advanced levels. Be sure to unzip each file to obtain the datasets in Excel.
Checkpoint 2
In this learning pathway, there will be three checkpoints to ensure data fellows understand the skills and apply the knowledge in a comprehensive assessment. This checkpoint will assess the key objectives in modules three and four. Participants must score 80% or higher. If the score is not an 80% or higher, participants should be given another opportunity with another form of assessment.
Checkpoint #2:
Module 5: Advanced Tools and External Data
Practice
Key Topic 1: Use Goal Seek & Solver for optimization
Goal Seek | Solver |
Determines the value that you need to enter in a single input cell to produce a result that you want in a dependent (formula) cell. | Determines the values that you need to enter in multiple input cells to produce a result that you want. |
Practice
Key Topic 2: Utilize Analysis ToolPak
For additional learning, be sure to explore and learn more using the videos on Analysis ToolPak | |
Practice
Key Topics 3 and 4: Import & export data from various formats
Module 6: Using Automation with Macros
Practice
In this module, you will explore and utilize tools that will help automate repetitive tasks with Excel Macros and VBA (Virtual Basics for Applications).
Key Topics: Intro to VBA and recording/running macros
Practice
In this module there two pathways in learning this objective:
In this learning pathway, VBA is introduced with a engaging, hands-on approach. You will complete step-by-step to demonstrate how to use these tools in Excel. It is a great way to start or to advance your skills. |
Here you can watch and engage in a YouTube channel that will provide more advanced skills in using VBA and running macros. It will demonstrate how to record and create macros and understand the Visual Basic Application at a more advanced level. Based on your skills and knowledge, some videos may or may not apply. |
Activity 6
In these activities, you will demonstrate your knowledge and skills by recording and editing macros to automate tasks. The activities will be on beginner, developing, or advanced levels. Be sure to unzip each file to obtain the datasets in Excel.
Beginner | ||
Intermediate | ||
Advanced |
Checkpoint 3
In this learning pathway, there will be three checkpoints to ensure data fellows understand the skills and apply the knowledge in a comprehensive assessment. This checkpoint will assess the key objectives in modules five and six. Participants must score 80% or higher. If the score is not an 80% or higher, participants should be given another opportunity with another form of assessment.
Checkpoint #3:
Cumulative Project and Review
Now that you have attained the skills and knowledge of Microsoft Excel, you will apply your skills in this project. This is a hands-on approach so there will be a video to guide you through, however, you do not have to utilize it. First, download the data set here ------>>>>> Excel-Tutorial/Excel Project Dataset.xlsx at main. The video will demonstrate the skills below: Full Project in Excel | Excel Tutorials for Beginners
You must analyze a dataset from start to finish:
Continuous Learning & Additional Resources
For continuous learning to grow and develop into a Data Analysis, you can use the websites. You can continue to practice skills and obtain other valuable skills and knowledge. These are all free resources (you may have to create accounts).
Logical Function: Microsoft Excel Formulas Explained - FormulasHQ
Excel Exercises - Practice Problem Simulator
VBA in Excel (In Simple Steps)
Introduction to Data Analysis with Excel: 2-Hour Training Tutorial