Microsoft Excel (Intermediate)
Course Information
1 Day Course
Time: 9am – 5pm
Contact: bes@dnb.com.sg
Course Introduction
Computing skills are one of the top skills employers desired in their employees. Microsoft Office suite of applications, being the most popular software in the world, are extremely useful and widely used in organisations, schools and homes. It is the most practical and productive tool for documenting and organising information, delivering presentations, as well as processing data in work environments across the globe. Microsoft Office has been designed to be user friendly, interactive and powerful, which drastically helps increase users’ productivity within the office environment.
Microsoft Office is a collection of purpose build programmes including:
1) Microsoft Excel – Creates simple to complex data/numerical spreadsheets.
2) Microsoft PowerPoint – Creates professional multimedia presentations.
3) Microsoft Word – Create text documents.
Course Outlines
Topic 1: Using Name Range
- Introduction to name range
- Create name range using define name, create form selection
Topic 2: Advanced Filtering
- Introducing advanced filters
- Setting up different criteria logic and comparison operators including wildcards
- Using formulas in the filter criteria
- Extracting results of specific columns
- Copying filtered rows to another worksheet
Topic 3: Searching for data types
- Using COUNTA, COUNT, COUNTBLANK, COUNTIF
Topic 4: Using Data Validation
- Introduction to data validation
- Using formulas to validate date e.g. checking for duplicate entry using COUNTIF
Topic 5: Remove Duplicate feature
Topic 6: Consolidation
- Internal consolidation
- External consolidation
- Consolidate feature
- Merge and Compare
Topic 7: Date and Mathematical functions
- Date (), Month (), Year ()
- SUMIF, AVERAGEIF
- TEXT Functions and Flashfill
- Round (), MROUND (), INT (), etc
Topic 8: Lookup Functions
- Using VLOOKUP and HLOOKUP
- Lookup exact value and approximate value
Topic 9: Using Pivot Tables
- Understanding dimensions and measures
- Pivot table terminology
- Implementing Calculations in Pivot Tables
- Using Page Filters, Slicers and Timelines
- Using Pivot Charts
- Creating a simple dashboard using Pivot Table
Topic 10: Conditional Formatting
- Understanding how it works
- Highlight Cell Rules and comparing cell values
- Highlight Duplicates
- Highlight Values above and below average
- Using Icon sets / traffic lights
- Highlight dates based on occurrence
- Highlight top % and bottom %
Topic 11: Using Database Functions
- DSUM function
- DAVERAGE function
- DMAX and DMIN functions
- DCOUNT and DCOUNTA functions
- Using Name Range with database functions
Trainer's Profile
Mr. Peter Low
Peter has more than 20 years of working experience. He has worked for different multi-national companies like CapitaLand, Hewlett Packard, Fuji Xerox and Philip Morris. In his last role, he was the department head for the sales information team whereby he was responsible for providing data analytics for the organization as well as managing system implementations in the company.
From February 2016, Peter made a career switch and became a freelance trainer in IT and Leadership courses. He completed his Advanced Certificate in Training and Assessment (ACTA) certification and Specialist Diploma in Leadership and People Management (SP LPM) in 2016 and has conducted SSG and non-SSG courses for public run as well as corporate clients.