Topics covered include Conditional Formatting wizard, Flash Fill, Name Manager, various functions (string, maths, logical, lookup), pivot tables, slicers and pivot charts to build a BI dashboard.

Having practiced all of these new skills extensively during the course you will be able to hit the ground running on your return to the office and should be able to complete your Excel work far more proficiently.

Learning Outcomes

Apply Fill Tools, Conditional Formatting and Filtering

• Using advanced Fill Series options
• Flash Fill
• Apply advanced conditional formatting and filtering n Creating filters
• Managing conditional formatting rules
• Conditional Formatting with Sorting & Filtering

Working with Data

• Getting External Data
• Restricting Cell Entries to Certain Data Types n Data Validation Tools
• Removing Duplicate Rows from a Worksheet n Sorting Data on a Single & Multiple Criteria n Sorting Data Using Cell Attributes
• Using AutoFilter
• Filtering Data Using Cell Attributes
• Outlining and Subtotaling data
• Removing Duplicates in a Table

Using Formulas

• Apply functions in formulas
• Functions Wizard
• Name Manager, Naming Ranges and Using them in Formulas
• Absolute vs Relative Referencing
• Formula Auditing
• Using nested functions
• Using the IF, AND, and OR functions
• Using the SUMIFS and AVERAGEIFS functions
• Using the VLOOKUP function
• Using the NOW and TODAY functions
• Using text manipulation functions like LEFT, RIGHT, MID, TRIM, CONCATENATE
• Create scenarios
• Using what-if analysis tools n Using the Scenario Manager

Data Analysis & and Manipulation using PivotTables & PivotCharts

• Create and manage PivotTables
• Modifying field selections and options
• Filters in PivotTables
• Creating slicers
• Create and manage PivotCharts
• Manipulating options in existing PivotCharts n Multiple PivotTables on same Sheet
• Applying styles to PivotCharts
• Building a BI Dashboard

Accreditation and MQF level

A CPD Award accredited with 1 ECTS credits (MQF Level 5) by the National Commission for Further and Higher Education (NCFHE), the Award in MS Excel Intermediate: The Use of Formulae & Intermediate Data Operations will be awarded.

Course Duration

This course is made up of 2 half-day sessions on the 6th and 17th March 2020.

Entry requirements

A foundation in basic Excel techniques is advised. This course will prepare students for a more advanced course, helping them to learn new tools and functions to make their work in the office more precise and efficient.

Venue

MMH Academy,
Triq Sir Anthony Mamo, San Gwann (view map)

Trainer

Andrew Catania

Andrew Catania is a Senior Marketing Analyst working for Tipico. He obtained his B.Sc. in Statistics, Operational Research and Mathematics at the University of Malta. He has been working in the online industry for the past 13 years in various positions which were mainly in Data Warehousing and Analytics. He is confident in handling large datasets and transform the data in actionable dashboards for the wider business. Furthermore, he is able to translate the data into insight to be used by stakeholders. His excel knowledge helps him do so in an easy and efficient manner.