Lead Training Services

MS Excel Intermediate: The Use of Formulae & Intermediate Data Operations

LIVE WEBINARS
MQF Level 5 CPD Award
Starting: 21st May 2020

By the end of this course candidates will master the techniques of using the right excel tools to solve problems more efficiently. Also new features in the current Excel version will be highlighted.

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

NCFHE Logo - Lead Training Services MaltaA 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 live webinar sessions on the 21st & 28th May 2020, from 9:00 till 13:30. A 10-15 minute break will be allowed every hour during which students can also take this opportunity to submit further questions or comments to the trainer.

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.

Course Delivery Mode – Live Webinars

This course will be delivered via live online webinars.  The course trainers will be visible on screen via a professional webinar platform through which students can follow the trainer presenting live whilst also using a powerpoint presentation and sharing other resources as necessary.  Students will be able to interact with the trainer by sending questions or comments through the same webinar platform.  Our student support team will assist all students with simple instructions on how to access the webinar online.  A support line will also be made available to communicate with a student support officer in case of technical difficulties encountered during the live webinar.

All webinars will also be recorded and uploaded on the student online resources portal for future reference during home study.  Trainers will also dedicate very regular Q & A times during the webinar to ensure all students can have the chance to ask questions and reach the intended learning objectives.  The email and contact details of the trainer will also be made available in case students would wish to get in touch with the trainer throughout the course period.

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.

 

Course Funding Options

For Companies

  • INVESTING IN SKILLS
    Through this scheme, employers have the opportunity to get back a significant percentage of the costs that would have been incurred in any employee’s professional development via training. The ‘Investing in Skills’ scheme was launched to promote the training of persons actively participating in the Maltese Labour Market, with the aim to increase productivity and enhance adaptability.

    Learn more about this scheme »

For Individuals

  • GET QUALIFIED SCHEME
    Candidates can benefit from a 70% rebate of the course fee via tax credit. Get Qualified is an initiative that supports the personal development of individuals for the achievement of qualifications and certifications required by industry.

    Learn more about this scheme »
  • TRAINING PAYS SCHEME (TPS)
    Eligible candidates satisyfing a list of criteria can benefit from a 75% rebate on direct training cost.

    Learn more about this scheme »
Course Funding Options

Frequently Asked Questions

Who We Are

We are a team of specialists passionate about our work and with a common ambition to share learning experiences with people in the business community and civil society.

Under the brand name LEAD Training Services, our team has attracted an impressive pool of professional trainers from different areas of business.

This makes us the only professional training organisation in Malta that can offer cutting edge training programmes across a broad spectrum of topics. We do not believe in one-size-fits-all training programmes and in line with our belief we customise our programmes to our individual customer’s needs.

Let's Keep In Touch

Stay adjourned with our latest courses, news and events by subscribing to our newsletter. We will send you an email newsletter from time to time, and we promise to never sell your details to any third parties!

Our LatestNews

Posted on: Monday, March 23rd, 2020

Due to the COVID-19 Social Distancing Government Directive, LEAD Training Services will be offering the upcoming Spring/Summer 2020 courses online. Instead of traditional face to face classroom lectures, all sessions will be delivered through online live webinars.

LEAD has in the past days developing a professional state of the art online training platform to continue deliver high quality accredited courses even during these upcoming months. We believe that education and professional development cannot halt but to the contrary we believe professionals should turn this temporary crisis as an opportunity to up-skill their skills and competencies by one of our accredited courses. …read more »