Training Theme 

In this course, you'll learn advanced Excel skills to improve your productivity and efficiency in data analysis. You'll learn how to display and protect multiple sheets simultaneously, integrate conditional formulas and create dynamic drop-down lists. You'll also explore in depth statistical functions, advanced conditional functions, date and time functions and complex string handling, as well as search functions and their options. You'll discover how to create combined graphs, and how to use the full power of pivot tables. 
Finally, you'll learn how to use Power Query, Power Pivot and macros to automate complex tasks or to familiarize yourself with mass data processing. By the end of the course, you'll be able to take full advantage of Excel's advanced features to manipulate, analyze and present your data in a professional way.

Training Objectives 

You need to familiarize yourself with the Microsoft Excel spreadsheet tool and understand its basic functions. 

By the end of this course, you'll be able to: 

• Make full use of Excel's advanced calculation functions 
• Generate advanced and combined graphs 
• Make full use of the possibilities offered by conditional formatting 
• Discover and exploit the power of pivot tables 
• Understand and use Power Query in conjunction with Power Pivot 
• Discover the logic and benefits of setting up macros

Prerequisite and Target Audience 

Target audience: Everyday users of Excel who want to master complex calculation functions as well as the exploitation and processing of mass data using all the power offered by TCD, Power Query and Power Pivot. 
Startup Level: Advanced 
Prerequisite: Regular use of Excel, particularly with regard to the use of formulas and the main functions associated with DCTs and data processing.

Means and Methods 

• E-Learning Modules: A video series to help you achieve the defined educational objective.
• Documents and other materials: Additional information or educational activities to complement this course.  

Training Content 

85 video tutorials on Microsoft Excel broken down into 15 lessons 

• View and Protection
  • How to simultaneously view multiple worksheets
• Conditional Formatting
  • Take conditional formatting to the next level
  • Manage conditional formatting
  • Use formulas to apply conditional formatting
• Data Validation
  • Create dynamic drop down lists
• Functions - Overall
  • Definition of statistical metrics
  • The Watch Window
  • Combine data from multiple worksheets
  • How to track and monitor values with the Watch Window
  • Use table references
• Functions Type - Statistical
  • Weighted average
• Functions Type - Conditional
  • IF with AND and OR
  • Advanced IF functions
  • Nested IF functions
  • Double conditions
  • IFS
  • SWITCH
  • Manage multiple sets of conditions
  • Manage multiple values
  • Apply multiple conditions on the number of values
• Functions Type - Date & Time
  • Convert separate items to date format
  • Retrieve information from a date
  • Convert separate items to time
  • Calculate the number of days between two dates
  • Increment a one-day date
  • Get the week and weekday number of a date
  • Convert a timestamp to date
  • Convert a number of seconds to a time format
• Functions Type - Lookup and Reference
  • XLOOKUP (Advanced Metrics)
  • Retrieve a set of values from a reference
  • Retrieve unique items from a list
  • Refer to a table by appying filters and sorts
• Functions Type - Text
  • Remove unnecessary spaces from text
  • TEXTJOIN
  • Text before & after Function
  • Retrieve part of a text in a cell
  • Identify the presence of an expression in text
• Functions Type - Information
  • Obtain the numeric coordinates of a cell
  • Recreate the coordinates of a cell
• Charts
  • One click Forecasting
  • Creating a forecast sheet
  • Line options
  • Create a combo chart
  • Combo chart
  • Relationship charts between available properties
  • Scatter plot
  • Add categories to a scatter plot
  • 3D Maps
• PivotTable & PivotChart
  • Create a two-level entry PivotTable
  • Create a two-dimensional PivotTable
  • Sort, filter, summarize and calculate your pivotetable data
  • Change how values are displayed
  • Group by values
  • Add slicers
  • Use slicers, timelines and PivotCharts to analyze your pivotetable data
  • The value references of a PivotTable
  • Add segment on PivotCharts
• Power Query
  • Introduction to Power Query
  • How to install Power Query
  • Import options presentation
  • Import an 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
• Macros
  • Work with macros
  • Save your macro
  • Edit a macro
  • Assign a button to a macro
• 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 PivotTable

Learn more about Microsoft Excel

Microsoft Excel has a number of tools to help you perform complex calculations and get the most out of your data, with advanced and accurate analysis. But collecting and sorting through your databases can be a long and tedious job. That's where Power Query comes in! Integrated into Microsoft Excel, its own interface lets you define your data sources, collect them and start transforming them into results.

Once you've prepared your data, 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 lets you modify large quantities of data from different sources, analyze information quickly and share analyses easily.

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