Syllabus
Course Syllabus
Module 1 - Introduction to Data Analysis Using Spreadsheets
- Introduction to Spreadsheets
- Spreadsheet Basics
- Using Spreadsheets as a Data Analysis Tool
Module 2 - Getting Started with Using Excel Spreadsheets
- Viewing, Entering, and Editing Data
- Copying, Filling, and Formatting Cells and Data
- The Basics of Formulas
- Intro to Functions
- Referencing Data in Formulas
Module 3 - Basics of Data Quality and Privacy
- Overview of the Data Analyst Ecosystem
- Types of Data
- Understanding Different Types of File Formats
- Sources of Data Using Service Bindings
- Languages for Data Professionals
Module 4 - Cleaning Data
- Removing Duplicated or Inaccurate Data and Empty Rows
- Dealing with Inconsistencies in Data
- More Excel Features for Cleaning Data
Module 5 - Data Analysis Basics, Filtering and Sorting Data
- Intro to Analyzing Data Using Spreadsheets
- Filtering and Sorting Data in Excel
- Useful Functions for Data Analysis
- Using VLOOKUP and HLOOKUP Functions
Module 6 - Using Pivot Table
- Introduction to Creating Pivot Tables in Excel
- Pivot Table Features
Final Project - Part 1 and Part 2
Reading: Excel Keyboard Shortcuts
Estimated time needed: n/a
The table below lists keyboard shortcuts for some of the most common Excel tasks.
| Task | Shortcut |
|---|---|
| Close a workbook | Ctrl+W |
| Open a workbook | Ctrl+O |
| Save a workbook | Ctrl+S |
| Copy | Ctrl+C |
| Cut | Ctrl+X |
| Paste | Ctrl+V |
| Undo | Ctrl+Z |
| Remove cell contents | Delete |
| Bold | Ctrl+B |
| Open context menu | Shift+F10 |
| Expand or collapse the ribbon | Ctrl+F1 |
| Move up one cell in the worksheet | Up arrow key |
| Move down one cell in the worksheet | Down arrow key |
| Move one cell left in the worksheet | Left arrow key |
| Move one cell right in the worksheet | Right arrow key |
| Move to the edge of the current data region in the worksheet (e.g. end of column) | Ctrl+Arrow key (e.g. Ctrl+Down arrow) |
| Move to the last cell on a worksheet | Ctrl+End |
| Move to the beginning of a worksheet | Ctrl+Home |
| Extend the selection of cells to the last used cell on a worksheet (lower right corner) | Ctrl+Shift+End |
| Move to the cell in the upper-left corner of the window (when Scroll Lock is On) | Home+Scroll Lock |
| Move one screen down in a worksheet | Page Down |
| Move one screen up in a worksheet | Page Up |
| Move one screen to the right in a worksheet | Alt+Page Down |
| Move one screen to the left in a worksheet | Alt+Page Up |
| Move to the next sheet in a workbook | Ctrl+Page Down |
| Move to the previous sheet in a workbook | Ctrl+Page Up |
| Edit the active cell and put the cursor at the end of the cell's contents | F2 |
| Enter the current time | Ctrl+Shift+colon (:) |
| Enter the current date | Ctrl+semi-colon (;) |
Learning Objectives
After completing this module, you will be able to:
Learning Objectives
After completing this module, you will be able to:
- Explain the importance of data quality. (Watch)
- Import file data into Excel. (Watch)
- Describe the fundamentals of data privacy. (Watch)
In this lesson, you have learned the following information:
The Five Traits of Data Quality:
Accuracy
Completeness
Reliability
Relevance
Timeliness
Importing Text:
You can use the ‘Text Import Wizard’ to import data from other formats, such as plain text, or comma-separated value files.
The Three Fundamentals of Data Privacy:
Confidentiality
Collection and Use
Compliance
Learning Objectives
- Remove duplicate and inaccurate data, and empty rows. (Watch)
- Handle inconsistencies in data. (Watch)
- Use the Flash Fill and Text to Columns features in Excel. (Watch)
Reading: Summary and Highlights
In this lesson, you have learned the following information:
It’s important to remove any duplicated or inaccurate data, and it’s important to remove any empty rows in your dataset.
There are several other types of data inconsistency that you may need to resolve, in order to properly clean your data:
Change the case of text
Fix date formatting errors
Trim whitespace from your data
You can use the Flash Fill and Text to Columns features in Excel to manipulate and standardize your data, and functions can also be used to help manipulate and standardize your data.
Learning Objectives
- Describe the fundamentals of analyzing data using a spreadsheet. (Watch)
- Filter and sort data in a worksheet. (Watch)
- Use some of the most useful functions for a data analyst. Like IF, IFS, COUNTIF, SUMIF (Watch)
- Use the VLOOKUP and HLOOKUP functions. (Watch)
Reading: Summary and Highlights
In this lesson, you have learned the following information:
Before shaping your data, you need to visualize the final output, and ask yourself the following questions:
How big is the dataset?
What type of filtering is required to find the necessary information?
How should the data be sorted?
What type of calculations are needed?
There are several advantages to formatting your data as a table:
Automatic calculations even when filtering
Column headings never disappear
Banded rows to make reading easier
Tables will automatically expand when adding new rows
The most basic way of shaping your data is to sort and filter it:
Sorting data helps you to organize it by a specified criteria, such as numerically, alphabetically, or chronologically.
Filtering our data makes it easier to control what data is displayed and what is hidden, based on filtered fields.
Excel Functions:
Functions in Excel are arranged into multiple categories; including mathematical, statistical, logical, financial, and date and time-based.
Common functions for a data analyst include IF, IFS, COUNTIF, SUMIF, VLOOKUP, HLOOKUP
Learning Objectives
After completing this module, you will be able to:
Reading: Summary and Highlights
In this lesson, you have learned the following information:
Pivot Tables:
To obtain usable and presentable insights into your data you need to use Pivot Tables.
Pivot tables provide a simple and quick way to summarize and analyze data, to observe trends and patterns in your data and to make comparisons of your data.
Pivot tables are dynamic, so as you change and add data to the original dataset on which the pivot table is based, the analysis and summary information changes too.
A Data Analyst can use pivot tables to draw useful and relevant conclusions about, and create insights into, an organization’s data in order to present those insights to interested parties within the company.
Use this Pivot Table checklist to ensure your data is in a fit state to make a Pivot Table:
Format your data as a table for best results.
Ensure column headings are correct, and there is only one header row, as these column headings become the field names in a Pivot Table.
Remove any blank rows and columns, and try to eliminate blank cells also.
Ensure value fields are formatted as numbers, and not text, and ensure date fields are formatted as dates, and not text.
Arranging Pivot Tables with Filters and Recommended Tables:
You use the Pivot Table Fields pane to add and arrange data fields in your pivot table.
Recommended Pivot Tables are a list of suggested different combinations of data that could be used when creating a Pivot Table, based on the data selected in the worksheet.
Filters and Slicers:
Slicers are on-screen graphical filter objects that enable you to filter your data using buttons, which makes it easier to perform quick filtering of your pivot table data.
Timelines are another type of filter tool that enable you to filter specifically on date-related data in your pivot table. This is a much quicker and more effective way of dynamically filtering by date, rather than having to create and adjust filters on your date columns.
0 Comments