About this Course

Excel is a powerful Data Analysis and Reporting tool. You could use Excel to gain new insights into the information and data that you work with in your job. 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. 

This intermediate level course is aimed at anyone who has to analyze data of any sort and create solid professional reports for the organization. This includes executives, managers, sales representatives and marketers who work in various industries such as retails, food and beverages, manufacturing, Insurance, shipping, warehouse, and etc... Participants must have some basic to intermediate knowledge of Excel features.

Introducing Excel 2013 Features

Participant will learn Excel 2013 new features such as using the new Start Screen, using Flash Fill to display data automatically, displaying one workbook in one window, using Quick Analysis tool to analyze the data faster, using the Recommended Chart tool to find most suitable chart for the data, using new charting and labeling features and viewing animation in charts.

Creating Table-Driven 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 the table data using Slicers and how to create relationships between two tables.

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 sparklines to show trend in the report and using Data Bar to create in-cell data visualization.

Creating PivotTable Reports

Participant will learn powerful report features such as 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

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 build a standalone PivotChart from a PivotTable and how to use Timeline Slicer to filter PivotChart based on dates.

Creating Power View Reports

Participant will learn how to create stunning, attractive and interactive Power View reports, filter the report using multiple slicers, enhances the report visualization using different chart types and map, display data using different tabular visualization such as table, matrix and card.

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 & Daverage) and Conditional Logic functions (Countifs, Averageifs &  Sumifs). When an item in a drop-down list is selected, all the related formulas will be updated automatically.

Extracting Data from Tables

Participant will learn how to extract data from a database using advanced filter and VLookup functions, how to create an interactive data entry 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.

Building Interactive Reports

Participant will learn how to develop powerful report 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.

Case Studies

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.

Course Durations: 2days

Quick Tip

PivotTable is a powerful reporting tool of Excel that let you quickly and visually group data in different ways without having to worry about which formula to use. PivotTable is best created from raw data without any totals and formatting. The creation of PivotTable is all about visualizing where your fields should go. You should stay focus on your analytical objectives and create tables that help you understand your data better.