Duration
1 day
Introduction
This training empowers participants to convert their data in Excel into a consumable and professional format.
Course Objective
After completing this course, participant will be able to manage data in a table format, and sort or filter it according to their needs. Moreover, participants will be able to perform data clean up using various formulas, identify and eliminate duplicate data, and successfully extract information from their raw data.
Training Methodology
This is an instructor led training, fully interactive with practical hands-on. Personal interaction ensures participants are engaged throughout the session.
Day 1
9:00am - 10:30am
Managing Data
- Structure data for optimum usage
- Create a directory or table of contents with internal links
- Convert into table and range
- Formulas
10:30am - 10:45am Morning Break
10:45am - 12:30pm
- Conditional formatting
- Sort by cell colour, font colour, cell icon, column
- Sort data in random order
- Setup Single and Multiple level subtotal
12:30pm - 1:30pm Lunch Break
1:30pm - 3:30pm
- Expand and collapse display with grouping and outlining symbols
- Filter Single and Multiple Columns (Filter text, Date, Numeric, Top 10 filters)
- Custom Filters and Wildcards
3:30pm - 3:45pm Afternoon Break
3:45pm - 5:00pm
- Filter table data interactively using slicers
- Count cells using count(),counta() and countblank()
- Simple Excel Function
- Quick Access Bar
DAY 2
9:00am - 10:30am
Data clean up and validation
- Eliminate Duplicate Data – Remove Duplicates Command
- Eliminate Duplicate Data - Use formulas to identify duplicate data and number of unique entries
- Eliminate Duplicate Data - Use excel function to count unique entries and create unique Entries
10:30am - 10:45am Morning Break
10:45am - 12:30pm
- Transposing row-column into column-row layout
- Using Sort to eliminate empty rows
- Text functions
12:30pm - 1:30pm Lunch Break
1:30pm - 3:30pm
- Extracting number or characters from the cells
- Logical Excel Function
- Combining data using concatenate
- Correcting multiple misspelling
3:30pm - 3:45pm Afternoon Break
3:45pm - 5:00pm
- Identify number of missing data
- Application : IC - Cleaning, Retrieving and Validation - Gender, State
- What-if tools
Programme scope, delivery arrangements and suitability are confirmed during enquiry.