MASTERING DATA MANIPULATION WITH MICROSOFT EXCEL

Are you looking for a trainer with industry experience and Microsoft Office Specialist Master certification? This course may change how you deliver jobs in today’s competitive working environment. Just spend 2 days, and you could do things differently………

This course is suitable for

Account, Finance, Sales, Marketing, and Operations professionals responsible for summarizing, analysing, tabulating data, and making decisions for their companies, clients, and customers.

Course Introduction

A Pivot Table is a tool that allows you to discover, summarize and explore your data interactively. For example, pivot tables can sort, count, unique count, average, find the minimum or maximum of your data, and display the results in a new table showing the summarized data. This gives you a lot of flexibility and analytical power to extract the significance from detailed data sets.

With Power Query, you can import or connect to external data and then shape that data, for example, remove a column, change a data type, or merge tables in ways that meet your needs. Then, you can load your query into Excel to create charts and reports. Periodically, you can refresh the data to make it up to date.

Course Outline

Day 1

Pivot Table

Lesson 1 – Develop Formatted Table

• Accounting Styles and Formats

• Convert to Excel Table

• Apply Conditional Formatting

Lesson 2 – Data Manipulation

• Build Pivot Tables and Pivot Charts

• Name and Group Data

• Add Slicers to Pivot Tables

• Add “Show Values As”

Lesson 3 – Data Grouping, Calculations and Relationships

• Insert Calculated Fields and Items

• Create Reports from Pivot Table

• Use the Relationship Features

Lesson 4 – Table Consolidation

• Multiple Range Consolidation

• Use Formatted Table in Consolidation

• Add Page Fields

• Generate Value Reports

Day 2

Power Query

Lesson 5 – Data Queries and Sources

• Different Version of Power Query

• Excel Functions and PQ’s Terminology

• Data Sources

Lesson 6 – Data Connections

• Import data from Text and CSV Files

• Import from Excel Workbooks and Folders

• Import Tables from Websites

Lesson 7 – Data Cleaning

• Convert “NOT a Proper Data Set” into a Proper Data Set

• Import Multiple Text Files and Transform into Proper Data Set

• Power Query Tasks

Lesson 8 – Merge and Append Data

• VLOOKUP the Power Query Way

• Merge Data from Different Tables

• Append Multiple Files to Single File

• Append Files from a Folder

Leave a Comment

Your email address will not be published. Required fields are marked *