Unit 1: $ Symbols in Formulas, Effective Formatting, Logical Functions and Date Functions
Module 1: $ Symbols in Formulas & Functions
An absolute reference in Excel refers to a reference that is "locked" so that rows and columns will not change when copied. A mixed reference is a reference that refers to a specific row or column.
Topic 1: How to use the $ in your Formula?
- Using Absolute Addressing formula
- Using Mixed Referencing in formula
The Microsoft Excel COUNTIFS function counts the number of cells in a range, that meets a single or multiple criterion. The SUMIFS function allows summation of cell values that meets a single or multiple criterion.
Topic 2: Evaluate Data Using Statistical and Math Functions
- Using COUNTIFS Function
- Using SUMIFS Function
Module 2: Effective Formatting and Logical Functions
The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.
The IF function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect. An IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.
Topic 1: Logical Function
- IFERROR Function
- IF Function
- Nested If Function
Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell's value.
Topic 2: Conditional Formatting
- Highlight Cell Rules
- Using Data Bars
- Using Icon Sets
- Creating New Rules with Formula
Module 3: Date Formulas
In this session, you will learn date functions that is used to handle dates within Excel. Some the functions taught will be calculation between 2 dates, contract date expiry calculation and working day function.
Topic 1: Working with Date Functions
- Date Calculation
- Add A Number to Current Date to Get New Dates
- Contract Date Expiry Calculation
- Calculate a duration Between Two Dates
- EDATE Function
- NETWORKDAYS Function
Unit 2: Analyzing Data with PivotTable, Handling Database and Data Integrity with Text and Subtotal Functions
Module 1: Ensuring Database Integrity and Handling Text Data Functions
Data validation is a feature in Excel used to control what a user can enter a cell.
Topic 1: Data Validation
- What Is Data Validation?
- Validating Your Data
- Types of Validation and Alerts
In this topic, you will learn text functions that is used to handle text within Excel. Some these functions will trim off excess spaces, combines text multiple ranges and/or strings, segregating data functions and change case function.
Topic 2: Handling Text Data
- Segregate Data Using the Text to Columns Wizard
- TRIM Function
- SUBSTITUTE Function
- CONCATENATE Function
- Data Extraction Function
- Change Case Functions
Module 2: Handling Database and Subtotal Function
Dynamic tables in excel are the tables where when a new value is inserted to it, the table adjust its size by itself. In this topic, you will learn how to filter records using Auto Filter. Normal or simple filter is used to only filter the selected data items.
Topic 1: Creating Table
- Creating Table
- Auto Filter
- Styles and Quick Style Sets
- Adding New Record
- Creating New Field
- Customizing Row Display
- Table Modification Options
- Summary Functions in Tables
Advance filter is used to filter selected data item like normal filter along with an additional feature (Copy filtered data in another location. Besides filter, you will learn to remove duplicate records and sorting of data.
Topic 2: Sort and Filter Data
- Difference Between Sorting and Filtering
- Sorting Data
- Advanced Filtering
- Removing Duplicate Values
The Microsoft Excel SUBTOTAL function returns the subtotal of the numbers in a column in a list or database.
Topic 3: Subtotals and Grouping Data
- Creating Subtotals
- Using Automatic Outlining
- Grouping Data Manually
- Displaying and Collapsing Levels
Module 3: Analyzing Data with PivotTable, Slicer and PivotChart
A pivot table is a data summarization tool that is used in the context of data processing. Pivot tables are used to summarize, sort, reorganize, group, count, total or average data stored in a database. It allows its users to transform columns into rows and rows into columns.
Topic 1: PivotTable
- Create PivotTable Report
- Show Values as Functionality of a PivotTable
Slicers provide buttons that you can click to filter PivotTables. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is currently displayed.
Topic 2: Slicer
- Insert Slicer Dialog Box
- Filtering Data
Pivot Chart in Excel is an in-built tool in Excel which helps you out to summarize selected rows and column of data in a spreadsheet. It is the visual representation of a pivot table or any tabular data which helps to summarize & analyze the datasets, patterns, and trends.
Topic 3: PivotChart
- Creating PivotChart
- Applying a Style to a PivotChart