‘Microsoft Excel for Data Analysts’ Overview
This Excel for Data Analysis course is suitable for participants who already have advanced knowledge of Microsoft Excel and wish to implement Business Intelligence components into their workbooks and use visualisation tools.
The course will cover Power Query from the Get and Transform in Excel and Power Pivot to model the data. Students will learn to obtain data from Access, Text Files, the Web, and Excel files and tables and then transform the tables by renaming, removing, splitting, and merging table columns. We will also cover various filtering techniques, such as aggregating values, calculating columns, unpivoting rows, and merging queries.
Course Code: EXLDAT
Duration: 1 Day
‘Microsoft Excel for Data Analysts’ Audience
This course will also introduce participants to Data Analysis Expression (DAX) language and bring together Text Based Visualisation with Pivot Tables using Cards and the Matrix views to see the results of DAX functions.
‘Microsoft Excel Data Analysts’ Prerequisites
This Data Analysis course is suitable for participants who want to extend their advanced Excel skills and be able to implement Business Intelligence within their Excel workbooks.
'Microsoft Excel for Data Analysts' Modules
Module 1: Get and Transform Introduction
- Link external data to Query
- Understand the Query Editor
- Use the Query Settings
- Apply Query Options
- Import Data into the Data Model
- Edit an Existing Query
Module 2: Accessing Data Types from Query
- Create a SQL Server connection
- Understand the Database Query Editor
- Review data download from the Database
- Import data from Text Files
- Import data from a Folder
- Import data from Excel files
- Work with data from the current Workbook
- Import data from an Access Database
- Import data from the Web
Module 3: Transforming Data in Query
- Name Columns
- Remove Columns
- Split Columns
- Merge Columns
- Set Column Data Types
- Filter Rows
- Filter Row Ranges
- Remove Duplicate Values
- Filter Out Rows with Errors
- Sort Columns
- Change Values in A Table
- Use Text Transformations
- Use Fill Up and Down to Replace Missing Values
- Aggregate Values
- Calculate Values across Custom Columns
- Duplicate Columns
- Unpivot Columns to Rows
- Merge Queries
Module 4: Importing Data into PowerPivot
- Load data from a Server Database
- Preview and Filter a Table
- Write queries to select data from a Database
- Load Views from a Database
- Load Tables from an Access Database
- Load Data from Text Files
- Load Data from Excel files
- Load Data from Excel Table
Module 5: Data Model Relationships
- Create table relationship Joins
- Manage Relationships
- View Relationships
Module 6: Transforming Data in PowerPivot
- Rename Tables
- Delete Tables
- Move a Table
- Freeze and Unfreeze Columns
- Hide Columns
- Filter Columns
- Sort Columns
- Sort a Column Based on Another Column
- Hide Tables
- Create a hierarchy
- Alter Table Behaviour
Module 7: PowerPivot vs Query
- Understand the difference between Power Query and PowerPivot
Module 8: DAX Measures for Columns
- Create a Concatenated column
- Create a Calculated column
- Use the RELATED function
- Complete a Task
- Create a Hierarchy
- Calculate Across Tables
- Use the RELATEDTABLE and ROWCOUNT functions
- Use the IF and ISBLANK Functions
Module 9: DAX Measures and Metrics
- Create a count Measure
- Create Measures with multiple tables
- Use the SUMX function
- Filter using the CALCULATE Function
- Use the FILTER function
Module 10: DAX Time Intelligences Calculations
- Create a Calendar
- Create Calendar Values
- Create Year to Date Sales
- Create Month to Date Sales
Module 11: Text Based Calculations
- Create a Pivot Table
- Create a Table Visualisation
- Modify and move the Table
- Create a Card Visualisation
- Create a Matrix Visualisation
- Filter a Table, Card and Matrix
Module 12: Power Maps
- Open Power Maps
- Add Geocode to Power Maps
- Navigating 3D Map
- Add Values to the 3D Ma
- Add Categories to the 3D Map
- Add Time Dimension to 3D Map
- Filter 3D Maps
- Playing a Tour
Related Courses…
Course Inclusions…
Certified Trainers
All our trainers are Microsoft 365 experts in their own right, and must first be qualified to teach in our own World Education Alliance (WEA) Training Certification Program before delivering any of our courses.
We feel that it is important that all of the trainers and instructors can take their real-world experiences into the classroom, in order to ensure that they are well equipped to respond to any queries and questions which may be put their way, as well as give accurate and relevant guidance in the most appropriate methods to leverage the technology which SharePoint and its related tools provide.
Course Materials Provided
Our Microsoft 365 courseware is known as being some of the best training materials in the world.
FREE Email Support
We offer free post-course support with all of our courses, available via email, telephone, or our Viva Engage community.
FREE Re-sits within 10 Months
Once you've attended any of our public courses, you can re-sit the same course as many times as you like within the next 10 months, as long as your re-sit course has reached the minimum number of paid attendees required to go ahead.
Certificate of Completion
Anyone attending a 3grow course will be provided with a personalised certificate of completion.
Get Hands-on Experience
All 3grow courses include plenty of hands-on experience, delivered via a combination of instructor-led follow-along demos and comprehensive lab exercises.
Personal Class Sizes
Although we allow a maximum of only 10 attendees on our public courses, the average is around 5 students, therefore each participant will leave the course feeling like they received the personal attention they deserve.
