About the 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 better and design the spreadsheet report to be more interactive and effective for data analysis purposes.
Who is this course for?
This course is aimed at anyone who has to analyse data of any sort and create solid professional reports for the organization.
This is a fast pace Intermediate level course and is not suitable for beginners who only know some basic functions and seldom use Microsoft Excel at work. This course is for frequent Excel users who know how to create simple PivotTable and perform simple data analysis tasks. You MUST have a thorough understanding of spreadsheet fundamentals and be confident creating complex functions and formulas and creating simple charts.
Course content
Day 1
Data Cleaning with Flash Fill and Functions
Flash Fill is a time-saving feature which fills in data automatically when a pattern is provided. You can use it to extract data, insert data, format text and concatenate text.
Data Analysing 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 using Conditional Logic functions (COUNTIFS, AVERAGEIFS & SUMIFS) and SUMPRODUCT function.
Data Filtering with Functions and Advanced Filter
Database functions (DSUM, DCOUNT, DCOUNTA, DMIN, DMAX & DAVERAGE) work with an Excel database to perform basic operations such as sum, average and count based on a given set of conditions. Advanced Filter is really helpful when it comes to finding data that meets two or more complex criteria such as extracting matches and differences between two columns, filtering rows that match items in another list, finding exact matches including uppercase and lowercase characters.
Data Extracting with Lookup Functions
VLOOKUP function lookup a value in a table by matching on the first column and retrieve the data (result values) in any column to the right. INDEX and MATCH functions lookup a value in a table by matching value in any column and retrieve the data from any column in the table.
Data Validation and Protection
Data validation rule is used to restrict the type of data or the values that users enter into a cell and create drop-down list. With worksheet protection, you can make only certain parts of the sheet editable and users will not be able to modify data in any other region in the sheet.
Day 2
Data Visualization
Quick Analysis tool allows you to quickly create graphs, sparklines, PivotTable, PivotChart and summary functions by just clicking on a button. Conditional formatting is a feature which allows you to apply a format to a cell or a range of cells based on certain criteria such as highlight duplicate records, highest values and lowest values. A Sparkline is a tiny chart in a worksheet cell that allows you to quickly visualize the overall trend of a set of values or to indicate maximum and minimum values.
Building Interactive Spreadsheet Report
Excel table enables you to manage and analyze a group of related data easier. You can create calculated columns, add slicer to Excel table and create relationships between two tables.Participant will learn how to create table-driven reports using Excel data and external data, sort data in different ways using custom list, filter the table data to show highest cost and revenue, filter the table using Slicers and create relationships between two tables.
Creating Dynamic PivotTable Report
Participant will learn powerful report features such as creating 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, adding subtotals by grouping field, calculating sales performance and comparing two customer lists by using a PivotTable.
Creating Impressive PivotChart Report
Participant will learn how to create PivotChart to represent data in a graphical portrayal, create a line-column PivotChart to show different data series, use multi-select Slicer to filter PivotChart, use Timeline to filter PivotChart based on dates and out across groupings of time and other hierarchical structures within your data.
Case Study
The case studies are based on real-life business scenarios and problems encountered by Excel users in daily jobs.
Pre-requisites
Please read carefully...
Notice:
This course will be running and taught based on thePC Windows software and not Mac versions.(All Laptops and materials will be provided)
*Important disclaimer for participants attending the 2016 version:
Participants are encouraged to inquire which version they have at work torealisewhich features may, or may not, be present on their work-based application.
Look at theFILEtab and search forACCOUNT. It will either highlight ‘PRODUCT ACTIVATED’or ‘SUBSCRIPTION’. Product Activated applications are typically a one-time purchase license and will not carry the same updated features as the Subscription versions. Companies may vary in polices of software purchase depending on the suitability of products. Subscription versions tend to be continually updated
As an example, one the features not available in the Product Activated version is IFS and MAXIFS Functions. Impress Training will be using the 365 subscription version for training purposes.
Valene Ang
Valene Ang is a Certified Microsoft Training Specialist who has more than 19 years of training experience and working with companies to improve their data processes. Her qualifications include a Bachelor’s in Business Computing, Microsoft Certified Trainer (MCT), Certified Instructor of Microsoft Certified Application Specialist (MCAS), and Master Instructor of Microsoft Office Specialist (MOS). Valene also holds an Advanced Certificate in Training and Assessment (ACTA) – a national qualification awarded by WDA (Workforce Development Authority)…
