Have a question?
Send enquiry
Message sent Close

Advanced Excel

What is Advanced Excel

  • Microsoft Excel is a leading and very high-demand office automation tool used all across industries, for example, Banks, Insurance, Telecom, Public Sector and many Small and Medium-size Organization to perform Complex calculations, Data Analysis, Reporting and Dashboards creation, understanding financial modeling, learning Advanced Excel is a highly valuable career Asset, a must-have criteria to qualify for many jobs!
  • Course participants will learn all the key Excel functions and features using Advanced Excel tools from basic to the mastery level of excel expertise, after completing the course participants would be able to perform their day-to-day job in more productive and professional way to improve organizational performance.

Who should attend?

  1. Professionals working in the field of accounting and financial reporting.
  2. CMAs, CPAs, ACCAs, Masters and Bachelors in Accounting and finance.
  3. Accountants
  4. Auditors                     
  5. Financial Analysts
  6. Portfolio Managers
  7. Management Accountants           
  8. Directors of Finance

 

Learning Objectives

  1. Take your Excel proficiency form Basic to Advance.
  2. Become more efficient in data analysis and reporting.
  3. Apply complex Excel functions and tools confidently.
  4. Better able to solve business problems using Excel

 

Agenda

Section 1

Excel: Advanced Formatting

  1. Using special number formats
    b. Built-in and custom formats Zero values
    c. Custom number formats
    d. Using functions to format text
    e. The PROPER, UPPER, and LOWER functions
    f. The SUBSTITUTE functions
    g. Working with styles
    h. Working with themes
    i. Theme colors
    j. Changing the orientation of cell contents
    k. Splitting merged cells
    l. Transposing data
    m. Using Paste Special operations
    n. Adding watermarks

Section 2

Excel: Range Names

  1. Defining names for cells or ranges
    b. Using a named range as a reference in a formula
    c. The Create from Selection command
    d. The Apply Names commands. The Name Manager

Section 3

Excel:  Functions

  1. Excel: Logical and Statistical Functions
    i. The IF function
    ii. The OR, AND, and NOT functions
    iii. Nested IF functions
    iv. The IFERROR function Math and statistical functions
    v. The SUMIF function
    vi. The COUNTIF function
    vii. The AVERAGEIF function
    viii. The ROUND function
    b. Excel: Financial and Date Functions
    i. The PMT function
    ii. TODAY, YEAR, DAYS, YEARFRAC
    iii. NETWORKDAYS
    iv. NOW
    v. Subtracting times
    vi. Creating array formulas
    vii. Applying arrays to functions
    viii. Modifying array formulas
    ix. Viewing/Hiding formulas in a worksheet
    x. Disabling automatic calculation of formulas
    c. Excel: Lookups
    i. Using VLOOKUP to find exact matches
    ii. Using VLOOKUP to find approximate matches
    iii. Using HLOOKUP to find exact matches
    iv. Using HLOOKUP to find approximate matches
    v. Using MATCH and INDEX

Section 4

Excel: Data Validation and Conditional Formatting

  1. Validating data
    Setting data validation rules
    c. Pasting validation rules
    d. Using date and list criteria in data validation rules
    e. Conditional Formatting Using a formula to apply conditional formatting

Section 5

Excel: Advanced Charts

  1. Chart formatting options
    Changing a chart’s scale
    c. Formatting data points
    d. Formatting pie charts
    e. Combination charts
    f. Adding trendlines
    g. Sparklines
    h. Chart templates
    i. Adding pictures

Section 6

Excel: Pivot Tables

  1. The PivotTable command
    b. Using an external data source
    c. Adding fields
    d. Using fields to filter data
    e. Inserting slicers
    f. Modifying slicers
    g. Deleting slicers
    h. Rearranging PivotTables
    i. Moving fields
    j. Collapsing and expanding details
    k. Refreshing PivotTable data
    l. Formatting PivotTables
    m. Using styles
    n. Changing field setting

Section 7

Excel: Data Analysis Tools

  1. Goal Seek
    b. Scenario Manager
    c. Solver
  2. Excel: Managing Large Workbooks
  3. Viewing large worksheets
    b. Locking row and column headings in place
    c. Opening and arranging windows
    d. Splitting a worksheet
    e. Hiding rows and columns
    f. Hiding and unhiding worksheets
    g. Minimizing the Ribbon to see more of the worksheet
    h. Printing large worksheets
    i. Print titles
    j. Page breaks
    k. Page Break Preview
    l. Using different even and odd headers and footers
  4. Printing multiple worksheets