Advanced Excel II

This course will not only focus on exposing you to common Excel functions and features, but will also teach you how to think and problem-solve on your own

28 Oct
Price: FREE
-

Online

Dates: 10/26/2020 & 10/28/2020
Requirements: Working Laptop with Excel 2007 or newer
Topics covered:
Many of the challenges you will face while using Excel will be unique. Therefore, this course will not only focus on exposing you to common Excel functions and features, but will also teach you how to think and problem-solve on your own. This class will go over a few basic functions and formulas and teach you how to incorporate these concepts and solve challenges.

We will begin from the Excel building blocks and work up to some of the more pinnacle features such as creating unique data sets, using conditional summations, and graphically representing data. This course is also accompanied by videos that directly correlate to the lessons being learned in class. The link for the playlist is attached below. In the description of each video is a link were you can download the worksheet shown in the video.


  • Day 1 - 10/26/2020
    • Advanced Parsing Functions: Text and format manipulation
      • E.g. text, find, search, left, right, mid
      • Excel Videos: Video 1
    • Nested Functions: Combining multiple functions within one string
      • E.g. =Left(Text,search(Find_Text, Within_Text,[Start_Num])
      • Excel Videos : Video 2
    • Advanced Lookup and Limitations: Using Vlookup to fill out transactional data
    • Advance logic using both summation and basic excel logic
      • E.g If, and, or
    • Creating legend data and conditional computation
      • Using Solver and Goal Seek in order to solve algebraic functions
      • E.g. removing duplicates & adding numbers if they meet a criteria
      • Excel Videos: Video 8, Video 9
    • Intro to pivot tables and charts : Taking large datasets and learning how to mange basic datasets in excel
      • E.g.=Vlookup(Left(X.X),Table_Array,Col_Index_Num,Range_lookup)
  • Day 2 - 10/28/2020
    • Complete worksheets transactional data and create pivots to make useful analysis
      • E.g. Fill in UPC Master and Transaction Data
    • Creating Tables and Charts: making a graphical representation of data
      • E.g. making a bar chart
      • E.g advanced charting and when to use which chart
    • Advanced Pivot Table: How to create and manipulate pivot tables
      • E.g. Take transactional data and consolidate it into meaningful ways
    • Adding Add-ons: Excel- Solver, Data Analysis
      • E.g. How to enable and use these features
    • Other relevant information: Using IF, AND, and OR other use case examples.

Additional Resources: Useful videos and recommended sites to expand your Excel education.

Excel skills: More detail on how to maneuver and use Excel functions:

  • My E-Lesson
  • Chandoo: Business-focused, from basic to advanced (free and paid)
  • Excelisfun: Good general Excel knowledge from basic to really advance

Price

This event is free.