This accelerated course focuses on extending participants' knowledge of complex formulas, 3-D reference formulas and advanced functions, summarise and present large amounts of data with interactive Table and dynamic Chart reports; compute, analyse and present data faster, use aggregate functions to present concise reports in minutes and become proficient in frequently used shortcuts.

Objective

This course will train participants how to manage multiple Excel sheets and files, link data from multiple worksheets or workbooks using formulas, consolidate data from multiple worksheets or workbooks, create a live picture object that will update automatically when source data changes, link Excel objects to Microsoft PowerPoint, create and use names in formulas, analyse and retrieve data using functions, create Excel report using Table and Conditional Formatting.

Outline

Lesson 1: Working with Multiple Worksheets And Workbooks
1.1 Entering Data On Multiple Worksheets Concurrently
1.2 Moving And Copying Worksheets
1.3 Linking Worksheet And Workbook Formulas
1.4 Creating 3-D Reference Formulas
1.5 Consolidating Data From Multiple Worksheets
1.6 Consolidating Data From Multiple Workbooks
1.7 Editing Link To Multiple Workbooks

Lesson 2: Linking and Embedding Objects
2.1 Inserting Embedded And Linked Objects Into Worksheet
2.2 Creating Live Picture On Excel Worksheet Using Linked Picture
2.3 Embedding And Linking Excel Object To Microsoft PowerPoint

Lesson 3: Working with Names and Functions
3.1 Defining Names For Cell And Range
3.2 Generating Name From Selected Cells Automatically
3.3 Managing Names Using The Name Manager
3.4 Using Defined Names In Formulas
3.5 Logical Functions  IF, AND , OR, IFERROR
3.6 Lookup Functions  VLOOKUP, HLOOKUP
3.7 Statistical Functions  SUMIFS, COUNTIFS, AVERAGEIFS

Lesson 4: Working with Functions
4.1 Using AutoFill
4.2 Using Paste Special
4.3 Using Flash Fill
4.4 Using Quick Analysis

Lesson 5: Working with Spreadsheet Reports
5.1 Inserting Automatic Subtotal
5.2 Creating Excel Table Report
5.3 Creating Dynamic Chart From Excel Table
5.4 Sort And Filter Table Records
5.5 Applying Conditional Formatting
5.6 Sort Data By Color
5.7 Filter Data By Color

Who should attend

Methodology

This is a 1-day hands-on course.

Testimonial

The knowledge will definitely improve my productivity and presentation of my reports in future. The trainer is very knowledgeable of the subject.
PSA Corporation Limited

I learned a lot, I can apply it at my work in future.
National Environment Agency

Valene Ang's Profile

Valene Ang is a Microsoft Certified Trainer (MCT) with a degree in Business Computing. Her Professional qualifications including Advanced Certificate in Training and Assessment (ACTA) and Master Instructor for Microsoft Office Specialist (MOS). She has broad experience in corporate IT training and course materials development.

Valene has a broad experience in customizing Microsoft Office training programs, developing customized course outlines and course materials, assisting corporate clients in business data analysis and providing dynamic report solutions. Her training focuses on providing practical solutions to real life Excel problems.

Valene conducted many Microsoft Office training in Singapore, Malaysia and China. Her corporate clients include NOL, PSA, IRAS, DFS, CPF, PUB, MOM, MOE, NEA, DHL, SingTel, Singapore Expo, Changi Airport Group, SPRING Singapore, Nanyang Polytechnic, Singapore Polytechnic, Republic Polytechnic, Denza (ShenZhen) and etc..