Using Flash Fill and Quick Analysis
Participant will learn how use Flash Fill to split text, change text cases, combine text, format numbers, format dates and display data automatically, and use Quick Analysis tool to analyze the data faster and using Sparklines to show trend in the report.
Using Advanced Filter
Participant will learn how to retrieve specific record rows and extract unique values from a column to another worksheet or workbook based on multiple pre-defined conditions.
Analysing Data with Functions
Participant will learn how to use absolute references and defined names in the formulas, how to calculate total sales and average sales for each region as well as how to count the number of records that match specific conditions by using Database functions (DSUM, DCOUNT, DCOUNTA, DMIN, DMAX & DAVERAGE) and Conditional Logic functions (COUNTIFS, AVERAGEIFS & SUMIFS).
Retrieving Data with Functions
Participant will learn how to extract data from a database using VLOOKUP function and how to retrieve data from a table using INDEX and MATCH functions.
Designing Interactive Form
Participant will learn how to design interactive form that includes drop-down lists and ability to reject invalid data entry automatically with error alert messages. Participant will also learn how to protect worksheet contents from editing and deleting by unauthorized users.
Case Study 2
Participants will have chance to design a user-friendly dynamic form. The case study helps participant to apply the skills and functions they learned from the training.