Data Cleaning with Flash Fill and Power Query
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. With Power Query, you can search for data sources, make connections, and then transform that data in ways that meet your needs.
Data Analysis 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).
Data Filtering with Advanced Filter
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.
Quick Analysis tool allows you to quickly create graphs, sparklines, PivotTables, PivotCharts, 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.
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. Hyperlink is a reference to a specific location (cell or worksheet), document or web-page that the user can jump to by clicking the link. 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.
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.
The case studies are based on real-life business scenarios and problems encountered by Excel users in daily jobs.