Home->Course->IT courses
MS Excel The Power User

MS Excel The Power User

Putting data to work in a new and creative ways is a huge part of what makes Excel great. In this course, users will learn how to take new and familiar tools and use them to create dynamic and easy-to-use workbooks. Users will learn to put Data Mining tools like VLOOKUP and INDEX/MATCH to work when processing information. Strategies for building complex charts and pivot tables will be discussed along with a crash course on building Array functions/formulas and macros. This course is the natural next step for those who feel like “There has to be a better way!”.

Course Features

1. Introduction

2. Use of Relative or Absolute Referencing

3. Excel Functions

  • IF, AND
  • Nesting The IF Statement
  • Advanced Excel Functions: Sum; Sumif; Sumifs
  • Advanced Excel Functions: Round functions
  • Advanced Excel Functions: Vlookup & Hlookup
  • Advanced Excel Functions: Index; Match; Index & Match
  • Advanced Excel Functions: Vlookup & Match
  • Financial Functions: FV and PV
  • Discounting Cash flows and calculating Net Present Value (NPV)
  • Financial Functions: IRR
  • Date Functions

4. Manipulating Text

  • LEFT And RIGHT: Text Manipulation
  • LEN And TRIM: String Extractions
  • FIND And MID: Text Functions Working Together
  • CONCATENATE: Building Strings From Multiple Cells
  • REPLACE And SUBSTITUTE: Two More String Manipulation Functions
  • Formatting Numeric And Date Values Using TEXT

5. Pivot Table

  • Creating Own PivotTables
  • Changing The Formatting And Formulas In PivotTable Summaries
  • Moving And Deleting PivotTables
  • Sorting The PivotTable Columns
  • PivotTable Styles

6. Macros

  • Creating And Running Your First Macro
  • Saving Workbooks With Macros

7. Data Manipulation

  • What ifs Analysis
  • Working with workbooks – Merging and sharing of workbook.

Apply Now Inquiry