Outcome
At the end of this course you will be able to:
- Effectively Organize Data in a Manageable and Scalable Manner
- Handling Rapid Processing of Raw Data via Splitting, Removing Duplicate and Transposing its Orientation
- Processing Text Data
- Date and Time Calculation
- Sorting Data by Standard or Custom List Order
- Filtering Data Based on Standard and Complex Rules
- Looking Up for Additional Details Across Different Sets of Data
- Set Data Entry Control in Worksheet to Keep Invalid Data at Bay
- Ease Monitoring by Automatically Changing Cell Style
Prerequisites
To ensure your success, we recommend the delegates equip with the following or equivalent knowledge:
- Attended Excel Essential or able to demonstrate relevant competencies
- A working knowledge of Microsoft Windows
- A working knowledge of Microsoft Excel
- Able to perform simple formula calculation
Please Note: If a delegate attends this course and does not meet the
prerequisites the respective delegate may be asked to switch to different course.
- Organizing Data with Excel Tables
Tables provide an effective way in organizing data in Excel. With the right setup, it is a good source to churn out different reports. This topic guides us on ways to organise data for ease in future analysis and reporting.
- Rules for Setting Up an Excel Table
- Rules for Good Raw Data Structure
- Renaming Excel Table
- Quick Navigation to Excel Table
- Resizing Excel Table
- Changing Table Style
- Showing Total Row on Excel Table
- Adding a New Record in an Excel Table
- Deleting an Existing Record in an Excel Table
- Adding Auto-Populated Formula in a New Column
- Editing Formula in a Column
- Understanding Syntax of Structured Reference
- Using Structured Reference on Same Row in Same Excel Table
- Using Structured Reference on Entire Column in Same Excel Table
- Using Structured Reference Outside of Excel Table
- Converting Excel Table back to Normal Range
- Essential Processing Excel Data
When data are available in Excel, there are few ways to processing it. These are particularly true when it involves data from manual entry and from external parties.
- Remove Duplicated Rows
- Retrieve List of Unique Values
- Transposing Data with Copy and Paste
- Transposing Data with Function
- Splitting Text into Multiple Columns
- Sorting & Filtering Data
Long list of data does not serve good purpose until we are able to zoom into specific details we want. From there, we perform further analysis. This topic demonstrates on various ways in zooming into data we want.
- Sorting Data by Ascending Order
- Sorting Data by Descending Order
- Sorting Multiple Columns at the Same Time
- Sorting by Cell or Font Colour
- Creating a Customized List and Performing a Custom Sort
- Removing a Customised List
- Sorting Data by a Single or Multiple Columns
- Using Custom Sorting Order
- Turning AutoFilter on or off
- Observing Impact of Data Types to AutoFilter
- Using Standard Filter on Number Data
- Using Standard Filter on Text Data
- Using Standard Filter on Date Data
- Filtering Data by Cell or Font Colour
- Applying Multiple Filters to a Single Column
- Manipulating Alphabets with Text Functions
Many data are made from this type. Without proper knowledge and skill, these type of data is not easy to turn into information. This topic furnishes with relevant knowledge to process such text data.
- Understanding the Fundamental of Text Processing
- Removing Empty Lines
- Removing Empty Spaces
- Substituting Specific Alphabets with Another
- Changing Text to Upper and Lower Cases
- Repeating Specific Text Multiple Times
- Extracting Part of the Text
- Combining Multiple Texts into Single Text
- Converting Date Data to Desired Date Text
- Converting Date Text to Correct Date Data
- Calculating Date & Time
Date and time data are commonly found in various daily business activities. With right knowledge on processing these, can bring tremendous values in business planning and monitoring. That’s the purpose of this topic.
- Recognizing Date and Time Data Entry
- Understanding the Essence of Date & Time Data Type
- Aware the Secret of Date and Time Calculation
- Showing Dynamic or Static Date/ Time
- Calculate Past and Future Dates
- Extracting Day, Month and Year of a Date
- Calculating Number of Days, Months or Years Between Two Dates
- Calculating Number of Working Days Between Two Dates
- Calculating Number of Working Days Excluding Public Holidays
- Calculating Next Working Day with Consideration of Holidays
- Calculating Specific Duration of Time Towards the Past and Future
- Calculating Interval Between Two Time
- Adjusting Date and Time Formatting
- Data Validation
GIGO (Garbage In, Garbage Out) is the fundamental understanding on how bad data affects your final report. This topic shows the ways in only allowing good data coming through and keep bad data at bay.
- Knowing Standard Rules Data Validation
- Applying Data Validation on Standard Rule
- Setting meaningful Alert Message
- Provide friendly Input Message
- Using Custom Formula in Validation
- Ease Data Entry with Dropdown List
- Configuring Dynamic Dropdown List
- Circle Invalid Data
- Removing Data Validation
- Using Lookup and Reference Functions
Correct management and processing of data is the key to uncovering meaningful information from our datasets. This topic shows us how to use lookup functions to retrieve additional details elsewhere.
- Knowing the Situation on Using Lookup
- Understanding the Secret Recipe of VLOOKUP Function
- Aware the Limitation of VLOOKUP
- Overcoming Limitation of VLOOKUP with MATCH-INDEX
- Knowing the Secret Recipe of MATCH-INDEX
- Using VLOOKUP to Find Corresponding Values That Falls within a
Numeric Range
- Finding Unmatched Data Between Two Datasets
- Simple Way on Overcoming Error of #NA using IFERROR
- Conditional Formatting
Many are tracking and checking data by changing its formatting, such as colour and font. This topic demonstrates how easy it is to apply dynamic formatting which change according to the actual data and condition.
- Understanding Standard Conditional Formatting
- Highlighting Duplicate or Unique Values
- Applying Top/Bottom Rules on Separate Range of Number
- Editing Top/Bottom Rules
- Prioritizing Conditional Formatting Rules
- Identify Achieved and Missed Targets
- Showing KPI Performance with Traffic Light
- Proactive Monitoring on Tasks Progress - early, on-time, late
- Showing Trend with Data Bars
- Using Custom Rule to Highlight Specific Status
- Managing and Deleting Rules
Note: The outline coverage may be adjusted to meet the actual training pace.
