Microsoft Excel Video Training – Advanced

Training Theme 

Improve your Microsoft Excel skills and knowledge with this training! Learn how your spreadsheet software can be used to analyze and summarize information, using multiple tables.

Microsoft Excel can perform simple or complex calculations, using databases. Tools like Power Query, Power Pivot, and some macros can help you prepare, sort, and link data to perform your analysis.

At the end of this course, creating and using databases in Microsoft Excel will no longer hold any secrets for you!

Training Objectives 

You work with databases and want to improve your efficiency with Microsoft Excel.

At the end of this training, you will know:

•     How to use Power Query and Power Pivot to search for data sources and make connections to analyze and conceptualize data models
•     How to use macros to automate actions and calculations

Prerequisite and Target Audience 

Target audience: Any experienced Excel user who wants to start integrating data from an external database to build dashboards in Excel
Startup Level: Advanced
Prerequisite: Have in-depth knowledge of Excel and database concepts

Means and Methods 

• E-Learning Modules: A video series to help you achieve the defined educational objective.

Training Content 

28 Video Tutorials on Power Query, Power Pivot and macros broken down into 3 lessons

•     Power Query
  •     Introduction to Power Query
  •     How to install Power Query
  •     Import options presentation
  •     Import a Microsoft Excel file
  •     Import a series of files into a folder
  •     Import data from an API
  •     Power Query overview
  •     Change the order of columns
  •     Delete columns
  •     Duplicate a column
  •     Merge two columns
  •     Delete rows
  •     Remove duplicates
  •     Split a column
  •     Change the format of a column
  •     Add a column off an example
  •     Add a conditional column
  •     Replace values
  •     How to refresh your data after import
•     Power Pivot
  •     Power Pivot Overview and Installation
  •     Import a table into the data model
  •     Create relationships between tables
  •     Create measurement functions
  •     Use Power Pivot in a Pivot Table
•     Macros
  •     Work with macros
  •     Edit a macro
  •     Save your macro
  •     Assign a button to a macro

Learn more about Microsoft Excel

Microsoft Excel has several tools to allow you to perform complex calculations and get the most out of your data, performing advanced and accurate analyses. But gathering and sorting through your databases can be a long and tedious job. That's where Power Query comes in! Integrated into Microsoft Excel, it allows you, thanks to its own interface, to define your data sources, collect them and start transforming them to get results.

Once your data is prepared, always use the Power Pivot tool in Microsoft Excel to create relationships between your data. Then create data models that you can process directly in Power Pivot. The application allows you to modify large amounts of data from different sources, perform information analysis quickly, and share analyses very simply.

Finally, to save time and be even more efficient, you can create macros in Microsoft Excel to record and then automate the processes that you repeat regularly.