Microsoft Excel – Advanced

COURSE

DURATION

Microsoft Excel – Advanced

30 Hrs

PROGRAMME OVERVIEW

In this Advanced Excel course, you will learn, using formatting and functions, working with data and data analysis, you will gain the skills necessary to create macros, collaborate with others, audit and analyze worksheet data, create PivotTables and Pivot Charts, you will be ready to start designing excel dashboards.

TARGETED TRAINEES

This course is helpful for employees at all levels across verticals (HR, finance, sales and marketing, etc.) who work on large spreadsheets and are responsible for reporting and managing MIS. Also designed for students who want to learn how sort and filter data, analyze data using the rich features of Microsoft Excel.

TOPICS COVERED / OUTLINE

Using Formatting and Functions

    • Applying Formatting to Numbers
    • Creating Custom Number Formats
    • Applying Conditional Formats
    • Using Formulas in Conditional Formats
    • Absolute and Relative References
    • Naming a Range
    • Using a Named Range in a Formula
    • Using Lookup Functions (vlookup, hlookup)
    • Logical Functions (if, sumif, countif etc…)

Working with Data

    • What is a List?
    • Sorting Data
    • Subtotaling and Outlining Data
    • Extracting Data with Filters
    • Applying Filters
    • Autofill

Analyzing Data

    • Using PivotTable
    • Managing PivotTable Reports
    • Creating PivotChart Reports
    • Using Charts and Trend lines

Working with Data tools

    • What if analysis (Scenario Manager, Goal Seek)
    • Text to Columns
    • Removing duplication
    • Data Validations

Sharing Workbooks

    • Creating a Shared Workbooks
    • Consolidating Data
    • Security Features in Excel
    • Assigning a Password
    • Adding Comments
    • Preparing a Workbook

3 D reference Formula

    • What is an Excel 3-D reference?
    • Creating a 3D reference in Excel
    • Including a new sheet in an existing 3D formula
    • Creating a defined name for a 3D reference

Macros

    • Adding the Developer Tab
    • Starting the Excel macro recorder
    • Excel macro recorder options
    • Recording the macro
    • Running the macro
    • Macro errors / Editing the macro

CERTIFICATION

Certificate from Polyglot Institute approved by MOMP.