What is this course about?
Excel 2016 is a powerful Data Analysis and Reporting tool that provides all the data crunching power including many new features such as forecasting functions to predict future values, multi-select slicer for data filter, automatic table relationship detection, PivotChart drill-down buttons to zoom into details and etc. The proper use of Excel features can greatly enhance the design of your report and help you to analyze your organization’s data more effectively.
If you find yourself struggling with duplicated data, or require an efficient way to analyze and retrieve your data from Excel table, if you deal with budgets, P&L, Sales or other administrative data and need to display the data using interactive Excel charts and reports, if you need to present your data using impressive report format, this is the right course for you.
This course will allow the participants to learn some powerful new features of Excel 2016 and train the participants how to convert raw data into Excel Table that will enable effective data filtering and sorting, format data in Excel table or PivotTable to highlight important figures using different colors based on pre-defined rules, extract useful information from the database using VLookup, Index and Match functions and advanced filter, validate data entry to accept only valid data and create drop-down list, protect confidential contents from unauthorized access using passwords, linking worksheet formulas and use database functions to create interactive and impressive reports for data analysis.
Participants will also learn how to use PivotTables to analyze large database more efficiently, create relationship between tables using automatic relationship detection, use PivotTable drill down buttons to view the details, use PivotChart to summarize important figures, and use PivotChart drill-down buttons to zoom in and out across data hierarchy.
At the end of each session, participants will have the opportunity to discuss the given case study scenario and come out with the best solution. These case studies will also help participants to have better understanding on the topics they learned and know exactly how to apply the skills in their work. Model answers of the case studies and sample Excel worksheets will be given to participants for reference and revision after the course.
Who is this course for?
This is a fast pace Intermediate level course and is not suitable for beginners who seldom use Excel at work. This course is for frequent Excel users who wish to learn how to increase their productivity with effective and time saving data analysis skills.The course is aimed at those who has to analyse data of any sort and create solid professional reports for the organization.
You must have a thorough understanding of spreadsheet fundamentals and some intermediate level knowledge of Excel features such as create nested functions and complex formulas, create simple PivotTable and PivotChart, sort data, use auto filter and custom filter, sorting data and create simple chart.
This course will be running and taught based on the PC Windows software and not Mac versions. (All Laptops and materials will be provided)
What will I learn?
Using Quick Analysis and Flash Fill
Participant will learn how to 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
Participant will learn how to 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
Participant will learn powerful report features such as creating cross-tabular PivotTable report from different data sources, using the Automatic relationship detection to discover and create relationships among the tables used for your workbook’s data model, using Timeline to filter date field, using multi-select Slicer to filter multiple items, drilling into PivotTable data, adding subtotals by grouping field, calculating sales performance and comparing two customer lists by using a PivotTable.
Creating PivotChart Reports
Participant will learn how to create PivotChart to represent data in a graphical portrayal, how to create a line-column PivotChart to show different data series, how to use multi-select Slicer to filter PivotChart, how to use Timeline to filter PivotChart based on dates ad how to use PivotChart drill-down buttons to zoom in and out across groupings of time and other hierarchical structures within your data.
Enhancing Report Visualizations
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
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, MAXIFS & MINIFS).
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 Forms
Participant will learn how to 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. If more than one user is allowed to edit the content, different range passwords can be set up for different 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.