Description

Microsoft Excel is a software for storing numerical data and analyzing them. It is one of the most flexible and commonly used applications in Office. Looking for a few cool tricks to impress your boss the next time he calls you in to do an urgent Excel project? The tricks in this course will ensure the data in your chart is accurate to avoid any costly screw-ups. You’ll also save a ton of time – perhaps even a few hours. You might even be able to leave the office on time. For one way or the other, we all work with numbers. When you want to record, analysis and save such numeric data, you will become a Superhero in Microsoft Excel in your organization.

Learning Outcome and Goals

• Working with Excel confidently
• Prevent common mistake in Microsoft Excel Reporting
• Prepare reports with time given; meeting all the dateline given

Course Requirement

• Participants should be able to use a PC at the beginner level
• Basic knowledge and functionality of Microsoft Excel
• Microsoft Office 2013 and above

Course Information

Course Outline

Must know about Microsoft Excel

• Start with the Essential Knowledge
• Excel Data Type
• How does this information affect us?
• A Quick Fix
• Understanding Microsoft Excel Date.
• Quick Questions:
• Understanding Excel Number Formatting
• Date Format
• Number Format
• Custom Number Formatting
• Writing Formula using Excel References
• Relative Reference
• Absolute Reference
• Mixed References
• Using Name as Reference (Name Range)

Daily used Formulas and Functions

• Essential Arithmetic Formula and Functions
• COUNT Functions
• COUNT Function
• COUNTA Function (Count All)
• COUNTBLANK Function
• COUNTIF Function
• DATE Functions
• Insert Excel Date
• Countdown between 2 date
• Find out the completion date based on number of days
• Find out the duration between two dates
• Adjust an existing date to a future date by number of months
• TIME Duration Calculations
• Normal punch card calculation
• How to deduct an hour from the duration between Time in and Time out
• Calculate the consultation Fee based on Hourly Rate
• Data Cleansing using Text Functions
• Using “&” symbol or Concatenate Function
• Using LEFT and RIGHT Functions to create a new Information
• Convert the text to Capital Letters
• Using Function to Split information to multiple columns
• Change all Uppercase text to Sentence case
• Removing additional spaces within a Text

Report Checking Features

• Conditional Formatting
• Find out which month achieved the highest sales
• Find out the months that achieved the given target
• Highlight the rows of data that meet the target
• Data Validation
• Circle the cell that does not meet the criteria given.
• Data Entry control using Data Validation feature
• Build a drop-down list using Data Validation List
• Formula Auditing
• Trace Precedents
• Trace Dependents

Common daily Copy and Paste Operation

• Paste Operation
• Paste Transpose
• Paste Column Width

Database Management

• Sorting Data
• Sorting data in ascending or descending order
• Sorting data according to month or day order
• Sorting data using our own priority order
• Perform multiple level of sorting
• Using Subtotal to generate Quick Summary
• Applying common AutoFilter
• Applying AutoFilter to show the records we want.
• Using Custom Filter to search for data
• Use Wildcard to filter the records
• Advance Filter
• Setting up before using the Advance Filter
• Filter with Multiple sets of Criteria and paste it in another location

Database Analysis & Reporting

• A quick interactive report/summary using Database Functions
• Generate Report using PivotTable
• Understand the PivotTable Elements
• Field List and Layout
• Value Field Settings
• Show Value as…
• Grouping Data
• Filtering report using Slicer
• Transform a PivotTable Report to PivotChart
• Insert a PivotChart as easy as 3 steps
• Impress your boss with a beautiful and interactive PivotChart

Advanced Popular Functions

• Applying Logical Functions
• Logical Function (IF, AND & OR)
• Using Logical Function to Suppress Error from appearing in the answer
• Lookup Functions

Data Analysis using What-IF

• Using Goal Seek to achieve the goal by changing one variable.
• Solution One: Extend the loan period
• Solution Two: Reduce the loan amount
• Data Table
• Calculate the Break-even point using One Input Data Table
• Find out the best offer for bulk purchase
• Scenario
• Adjust our Costing to meet the Customer Budget

Automate your Operation using Macro

• Macro Recording
• Assigning Macro to Button