Using Quick Analysis and Flash Fill
Use relative references, absolute reference, structured references and defined names to create formulas; using Flash Fill to split text, change text cases, combine text, format numbers, format dates and display data automatically, using Quick Analysis tool to analyze the data faster and using Sparklines to show trend in the report
Building Dynamic Spreadsheet Reports
Create table-driven reports using Excel data and external data imported from Access database, how to sort data in different ways using custom list, how to filter the table data to show highest cost and revenue, how to filter table using Slicers, how to create relationships between two tables and how to create PivotTable from two related tables.
Creating PivotTable Reports
Creating cross-tabular PivotTable report from a data source, using the Create Relationship tool to build a
PivotTable from multiple data sources, using the Timeline Slicer to filter records based on dates, drilling
into PivotTable data, adding subtotals by grouping field, calculating sales performance and comparing two
customer lists by using a PivotTable.
Creating PivotChart Reports
Create PivotChart to represent data in a graphical portrayal, how to create a line -column PivotChart to
show different data series, how to build a standalone PivotChart from a PivotTable and how to use Timeline
Slicer to filter PivotChart based on dates.
Enhancing Report Visualization
Participant will learn how to enhance the report visualization by using Conditional Formatting rules to highlight duplicate records, highest values and lowest values in the report, using icons and using Data Bar to create in-cell data visualization.
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
Use absolute references and defined names in the formulas, how to calculate total sales and average sales as well as how to count the number of records that match specific conditions by using Database functions and Conditional Logic functions (DSUM, DCOUNT, DCOUNTA, DMIN, DMAX & DAVERAGE) and Conditional Logic functions (COUNTIFS, AVERAGEIFS, SUMIFS, MAXIFS & MINIFS).
Retrieving Data with Functions
Learn how to extract data from a database using VLookup functions, how to create an interactive form
using drop-down list and functions. When a product code is selected from the drop-down list, the description and price will be retrieved automatically.
Designing Interactive Forms
Design effective form that includes features such as auto fill-down formulas, auto calculating subtotals when conditions are met, applying cell formats based on predefined rules using a drop-down list, update formula results automatically when data changes, auto rejecting invalid data entry and displaying interactive error alert messages. Participant will also learn how to protect worksheet contents from editing and deleting by unauthorized users.
The case studies are based on real-life business scenarios and problems encountered by Excel users in their daily jobs. Participants will learn useful skills to provide efficient and practical solutions to overcome
the problems and increase productivity.