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. 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 train participants how to use Excel to convert raw data into Excel Table that will enable effective data filtering and sorting, format data in Excel table or PivotTable, validate data entry to accept only valid data, protect confidential contents, ing worksheet formulas and many more. 

At the end of each session, a case study which related to the topics learned will be given. Participants will have chance to discuss the case study scenario and come out with the solution. These case studies help participant to have better understanding on the topics learned and know how to apply the skills in their work. Model answers of the case studies and samples Excel worksheets will be given to participants for reference and revision after the course.

This course will be running on PC Based windows software and not Macintosh. 

This intermediate level course is aimed at anyone who has to analyse 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

Disclaimer:
Due to the nature of this course , we will want to ensure the suitability and level is right for you. Participants must have a thorough understanding of file handling, spreadsheet fundamentals and be confident creating complex functions and formulas and creating simple charts.

Creating Impressive Spreadsheet Report

Participant will learn how to organize data using Excel table and apply appropriate filter to show only require records. In this module, participant will also learn how to format report using conditional formatting based on predefined rules and conditions.

Using  Advanced Filter and Custom Sort

Participant will learn how to extract specific database records to another location and extract unique values from a range of data using advanced filter. Participant will be introduced about sorting records in different ways based on predefined custom lists.

Analysing Data using Functions

Participant will learn how to calculate total and average, count number of records that match specific conditions using Database and Conditional Logic functions. In this module, participant will be introduced about relative and absolute references and how to use range name to create user-friendly formulas.

Extracting Data with Functions

Participant will learn how to extract data from database based on specific fields or criteria using basic Vlookup and Hlookup functions. In this module, participant will be introduced about creating an interactive data entry form using drop-down list and functions.

Building Interactive Reports

Participant will learn how to develop powerful spreadsheet report that includes features such as auto-refreshing data, auto fill-down formulas, calculating subtotals and grand totals based on condition, applying conditional formatting, sorting data using Custom Lists, advanced filtering, database functions, lookup functions, logical functions, data validation drop-down and error alert messages.

Protecting Worksheet Contents

Participant will learn how to protect worksheet contents and workbook structure using passwords. If more than one user is allowed to edit the content, different range passwords can be set up for different users. This will allow user to edit cell content only when they type a correct range password

Creating PivotTable and PivotChart

Participant will learn how to interactively create and build cross-tabular reports from a list of available fields in source data using PivotTable and then represent data in a graphical portrayal using PivotChart. A PivotTable helps you to analyze trends in data without having to worry about what formulas to be written.  PivotTable reports turns the data into small, concise reports that tell you exactly what you need to know.

Consolidating Data

Participant will learn how to consolidate data in identical location or different location of different worksheets or workbooks into a summary worksheet. Consolidating data from multiple workbooks into a single worksheet helps the analysis process by summarizing large amounts of data in a single interface so that it can be updated on a regular basic or more easily. This feature can be used to summarize results from different regional offices expense worksheets into a master corporate expense worksheet.

Importing External Data

Participant will learn how to import data into Excel Table or PivotTable from external data sources such as text file or Access database in order to take advantage of the data analysis and charting features, the flexibility in data arrangement and layout, or functions that are not available in Access.

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.

Rate this course:

Log in to submit

Comments