EXCEL FOR DATA ANALYSIS & REPORTING (v2010)
What is this course about?
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, linking 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.
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?
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.
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 Pivot Table 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.
Understanding Cell References
Participant will learn how to use relative references, absolute reference, structured references and defined names to create formulas.
Case Study 1
Extracting Data with Advanced Filter
Participant will learn how to retrieve specific record rows and extract unique values from a column using advanced filter.
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.
Retrieving Data with Functions
Participant will learn how to extract data from a database using VLookup, Lookup, Index and Match 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.
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.
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.
Case Study 2
*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.
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.