Lead Training Services

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

MQF Level 5 CPD Award (In-House Training)
Duration: 7 contact hours

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 Malta Further & Higher Education Authority (MFHEA), 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.

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.

Trainer

Dr. Vincent Marmara

Vincent Marmarà is a Statistician and Researcher by profession. He obtained his first degree in Statistics, Operational Research and Mathematics at the University of Malta. He later advanced his studies by obtaining Masters of Science in Statistics at Sheffield University, UK. Furthermore, he has obtained his PhD in Mathematics (Statistics) from the University of Stirling, Scotland. He was entrusted with numerous research projects both at a national and international level. He led research groups and analyzed data to a high-level scientific extent.

Vincent has over 11 years of experience in the Remote Gaming Industry as a Business Intelligence Analyst and Consultant. He occupied several key important roles in various organisations such as: Student Representative of the Faculty Board of Science (University of Malta), President of the Science Student Society, President of the Malta Statistics and Operations Research Organisation (MSTOR), Financial Officer of the National Youth Council, Deputy CEO and Chief Regulatory Officer within the Malta Lotteries and Gaming Authority.

Dr. Vincent Marmarà lectures statistics at the Department of Management (FEMA) at the University of Malta. Vincent is a fellow member (FRSS) of the Royal Statistical Society (UK). Dr. Marmarà provided research consultancy services to various organisations and supervised / is currently supervising BSc, BCom, MBA and MSc research students at the University of Malta. Vincent published several academic articles on international journals and other scientific surveys in Malta. At present he is one of the partners and co-owner of the statistics research company, Sagalytics.

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 »
Course Funding Options

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: Thursday, March 17th, 2022

Here are 11 reasons why you should enroll in LEAD Training Services’ Corporate Series courses:

1.     They are accredited courses. Our Corporate Series courses are fully accredited by the MFHEA, at MQF Level 5, 2 ECTS. This means that the courses have employment and academic value. So our courses will help you to get the very best out of your investment and time.

2.     They are short courses. Sometimes, the best way to learn, is by getting to the gist of it. These courses are 7 to 10 hours long but well informative, split over 3 mornings, in order to give you enough time to balance your work schedule, while investing in your continuous professional development.

…read more »