Course Overview

This course covers the use of advanced functions in Excel, including logical, financial, and lookup functions. Students will learn how to use these functions to perform complex calculations, data analysis, and data management.

Course Objectives

  • Understand the use of logical, financial, and lookup functions in Excel
  • Learn how to perform complex calculations and data analysis using advanced functions
  • Apply advanced functions to real-world scenarios, such as financial analysis and data management
  • Learn how to use advanced techniques to process and analyze data in Excel.
  • Understand how to use various tools and features to extract insights from data.
  • Discover how to create and customize pivot tables, charts, and other visualizations to communicate data insights.
  • Learn how to use data validation rules to ensure data accuracy and prevent errors.
  • Understand how to use analysis tools to analyze and summarize data.

 

Section-1

Logical Functions

1. IF : Using the IF function to perform conditional calculations
2. AND : Using the AND function to perform multiple conditional calculations
3. OR : Using the OR function to perform multiple conditional calculations
4. More Logical Functions : Introduction to other logical functions, such as NOT, XOR, and TRUE/FALSE

Financial Functions

1. PMT : Using the PMT function to calculate loan payments
2. PV : Using the PV function to calculate present value
3. FV : Using the FV function to calculate future value
4. Rate : Using the Rate function to calculate interest rates
5. More Financial Functions : Introduction to other financial functions, such as IRR, XIRR, and NPV

Lookup Functions

1. VLOOKUP : Using the VLOOKUP function to look up values in a table
2. HLOOKUP : Using the HLOOKUP function to look up values in a table
3. MATCH and INDEX : Using the MATCH and INDEX functions to look up values in a table
4. Bank Registration Statement : Using lookup functions to create a bank registration statement

Depreciation Functions

1. Depreciation : Using the depreciation function to calculate asset depreciation
Section-2

1. Pivot Tables and Charts

  • Creating and customizing pivot tables
  • Creating and customizing charts
  • Using pivot tables and charts to analyze data

2. Conditional Formatting and Data Validation

  • Using conditional formatting to highlight important data
  • Creating and using data validation rules
  • Using data validation to ensure data accuracy and prevent errors

3. Analysis Tools

  • Overview of analysis tools in Excel
  • Using Global, Seek, Solver, Scenario, and Data Table
  • Using analysis tools to analyze and summarize data

4. Auto and Advanced Filters

  • Using auto filters to quickly filter data
  • Creating and using advanced filters
  • Using filters to analyze and summarize data

5. Sorting, Subtotal, and Consolidate

  • Using sorting to organize data
  • Using subtotal to summarize data
  • Using consolidate to combine data from multiple worksheets

6. Forward Auditing and Workbook Protection

  • Using forward auditing to track changes made to a worksheet
  • Protecting a workbook from unauthorized changes
  • Using workbook protection to secure data

7. Creating Templates

  • Creating templates for worksheets
  • Using templates to streamline data entry and analysis
  • Best practices for creating and using templates