Sign In

A Member of the CFA Institute Global Network of Societies

​​​​​​​

 Advanced Excel for Data Analysis

Course Goals and Overview: 

This course focuses on how to effectively and efficiently utilize Microsoft Excel for data analysis. A financial analyst will not only use Excel to build financial models, but also to crunch a large data dump. Learn how to minimize as much manual labor as possible, thereby saving time and performing more detailed analysis quickly. Apply commonly-used formulas in new and different ways; uncover often over-looked Excel formulas; streamline number crunching and analysis via functions and tools including pivot tables, sumif, sum+if, transpose, working with arrays, vlook-up, subtotals, and regression analysis; enhance your spreadsheets with drop-down boxes, data validation techniques, automation of alternate row shading; take Excel to the next level with an introduction to building and automating simple macros and more!

Learning Objectives:

  • Learn how to minimize as much manual labor as possible in data analysis 
  • Learn to use the most overlooked Excel formulas that will make your life easier 
  • Learn powerful functions built into Excel that streamline your analysis
  • Learn how to build macros to automate common tasks

Course Sections:

  • The most useful and overlooked Excel shortcuts to make life easier
  • Different ways to make your Excel worksheet into a model instead of just a flat analysis? Learn different "switches alternatives" (if, choose, offset)
  • Data validation techniques to dummy proof your model
  • Basic regression analysis using least squares approach
  • One-dimension and two-dimensional sensitivity analyses using data tables
  • Utilizing the vlookup function to its fullest to streamline tedious lookup jobs
  • Pivot Tables: Everybody's heard of them, but who knows how to use them?
    • Learn how to summarize and dissect large amounts of data for analysis
    • Even better - add built-in and custom calculated fields to really use pivot tables to the max
  • Using the sumif formula and sum+if array functions to simplify complex conditional calculations
  • Automate alternate row shading in a table of data using complex conditional formatting
  • Using the transpose array function
  • Adding some spice to your Excel analysis and models using drop-boxes

 

**IMPORTANT - PLEASE NOTE**

To maximize the educational value of these programs, we strongly recommend that you have an intermediate understanding of Excel. Lack of basic Excel skills will impede your ability to effectively acquire and implement the techniques and shortcuts that are presented in this program.  Our courses are extremely interactive, hands-on with intensive focus on Excel shortcuts and efficiency.

Bring a PC laptop with Microsoft Excel installed, and a working USB port for receiving class materials (in case our email containing in-class materials gets lost in your junk/spam folder, we can distribute them via flash drive). If you can only bring a Mac, please avoid Office 2008 and ideally set up a Windows environment via Boot Camp, Parallels, or VMware.

 Registration Fees

​CFAW Members: $499.00

Non-Members: $599.00

 Dates and Location

March 6, 2018

8:00 am - 4​​​:00 pm

George Mason University - Arlington ​​Campus
Founders Hall
Room 120
3351 North Fairfax Drive
Arlington, VA 22201

Click here to register.