Advanced Excel Techniques (Pivot Table and Micros)

Training Objectives

• Delegates will be able to use advanced formulas
• Delegates will be able to use the advanced excel tools
• Delegates will be able to use specific excel tools needed by users that already master MS. Excel
• Delegates will be able to practice cases that are related to their day-to-day operations.
• Delegates will be able to create report with charts and objects, and Pivot Charts.
• Delegates will be able to validate excel data and protect it.

Module Outlines

Module 1: Perform Operations with Formulas and Functions

Summarize data by using functions

• Insert references, perform calculations by using the SUM function, perform calculations by using MIN and MAX functions, perform calculations by using the COUNT function, perform calculations by using the AVERAGE function

Perform conditional operations by using functions

• Perform logical operations by using the IF function, perform logical operations by using the SUMIF function, perform logical operations by using the AVERAGEIF function, perform statistical operations by using the COUNTIF function

Format and modify text by using functions

• Format text by using RIGHT, LEFT, and MID functions; format text by using UPPER, LOWER, and PROPER functions; format text by using the CONCATENATE function

Module 2: Create Charts and Objects

Create Charts

• Create a new chart, add additional data series, switch between rows and columns in source data, analyze data by using Quick Analysis

Format graphic elements

• Resize charts, add and modify chart elements, apply chart layouts and styles, move charts to a chart sheet

Insert and format objects

• Insert text boxes and shapes, insert images, modify object properties, add alternative text to objects for accessibility

Module 3: Create advanced charts and tables

Create advanced charts

• Add trendlines to charts, create dual-axis charts, save a chart as a template

Create and manage PivotTables

• Create PivotTables, modify field selections and options, create slicers, group PivotTable data, reference data in a PivotTable by using the GETPIVOTDATA function, add calculated fields, format data

Create and manage PivotCharts

• Create PivotCharts, manipulate options in existing PivotCharts, apply styles to PivotCharts, drill down into PivotChart details

Module 4: Data Management

Data Validation

• Data validation to restrict the type of data or the values that users enter into a cell. One of the most common data validation uses is to create a drop-down list

Text to Column

• You can take the text in one or more cells, and spread it out across multiple cells. This is called parsing and is the opposite of concatenating, where you can combine text from two or more cells into one cell

Excel Protection

• Protect a worksheet, protect a workbook & Lock or unlock specific areas of a protected worksheet

Customized Learning

Leap To Success is offering a variety of learning options to meet current realities and can be adapted to suit your business needs. These options include variants of online, blended and on-site course formats.

Face To Face Learning

Enabling you to have a face to face interactive and engaging learning experiences led by renowned industry experts and thought leaders with extensive practical experience who will employ a variety of interactive learning techniques, including short high-impact videos, case studies, assessments, role plays, in addition to on-going support.

Virtual Learning Labs

Interactive online learning held in real-time using Zoom and are led by international subject matter experts who incorporate case studies, breakout rooms, guided practice, simulations and discussions to maximise your learning experience.

Methodology and Evaluation

General Methodology:

Similar to any L2S training program, this program offers an interactive learning experience in which will allow the delegates to reflect on their learning through an informative and indulging at different training stages. In Addition to that, this course is technical by nature. Therefore, to ensure the assimilation of the techniques introduced, the training will be given in a computer lab, providing each attendee with a PC. The training will also contain a practical part that puts each training in practical situations, engaging them in day-to-day examples and case studies.