Select tickets
Course Code: 0200-405-19-W
Course Format: Instructor-Led Online
Prerequisites:
- Excel Level 1 & 2 or equivalent knowledge.
In this course, you will cover creating and modifying table elements, adding and deleting records and fields, and using Data forms and Slicers with table data. You will import data from Access and Excel objects as well as text file types. Proper data management will be discussed to determine structure requirements, sorting, filtering, using flash fill, auto filter and advanced filtering features. The database functions will allow the user to extract data from a large list of content with precision based on specific criteria. Finally, the data modeling features will examine how to take spreadsheet data and create one to many relationships which will expand the functionality of a pivot table for a greater understanding of the data.
Topics:
Lesson 1: Tables
- Tables
- Creating a Table
- Table Components
- Records and Fields
- Resizing A Table
- Adding Records
- Deleting Records or Fields
- The Total Row
- Adding a Total Row
- Hiding the Total Row
- Using the QAT to Create a Total Row
- Data Forms
- Adding the Form Tool to the QAT
- Using a Form to Enter Records
- Slicers
Lesson 2: Power Query
- Intro to Power Query
- Get & Transform Data
- Navigator
- Power Query Editor
- Data Management
- Introduction to Power Query Formulas
- Data from Another Workbook
- Data from CSV files
- Data from DataBase
Lesson 3: Database Functions
- Database Functions
- Basic Syntax of D-Functions
- Creating a D-Function Formula
- Entering the Function Manually
- Expanding D-Functions
- Adding Drop-down Menu's
- Data Validation Lists
Lesson 4: Data Modeling
- Data Modeling.
- Enabling Power Pivot
- Understanding Relationships
- Relational Databases
- Preparing the Tables
- Structuring the Data
- Convert the Data to a Table
- Rename the Table
- Creating Relationships
- Find the Related Data
- Creating Relationships
- Managing the Data Model
- To Open Excel's Power Pivot window
- Power Pivot Views
- Adding a New Connection
- Creating PivotTables
- Creating PivotTables
- Working with a PivotTable
- Adding a Calculated Column
- Inserting a Function column