Microsoft Excel: Basic to Advance Application

Section 1: Dynamic Data Manipulation

• A. Tips and Notes on Formula, Cell Reference, and Formula Copy
  a. Some Important Tips on Excel Formula
  b. Some Important Tips on Excel Cell Reference
  c. Other Important Notes
  B. Manipulating Text
  a. Setting text to sentence case
  b. Join text string
  c. Removing space from a text string
  d. Counting characters in a cell
  e. Extracting parts of a text string
  f. Finding a particular character
  g. Substituting text string
  h. Finding similar multiple characters
  i. Dynamic “Extracting parts of a text string”

Section 2: Conditional Analysis to Gain Insight

• a. Simple and multiple conditions- IF and Nested If
  b. Logical condition with AND & OR
  c. Counting that meets conditions- Countif/Counties
  d. Summing that meets conditions- Sumif/Sumifs

Section 3: Match and Lookup for Retrieving Info

• a. Find the row and column number by lookup matching
  b. Looking up on a left lookup column
  c. Looking up horizontally
  d. Hiding errors returned by lookup functions
  e. Difference between Exact and Approximate match
  f. Looking up on a two-way matrix
  g. Fill all cells in a table in one attempt by Lookup

Section 4: Conditional and Custom Formatting for Presenting Vivid Data

• a. Highlight cells that meet criteria b. Top/Bottom Rule
  c. Color Scale & Data bar, Icon Set d. Manage Rule
  e. Text Formula f. Custom Formatting
  g. Input different types of data in the same cell

Section 5: Data Analysis by Pivot Table

• a. Discussion on Table b. Create a Pivot Table
  c. Different Features of Pivot Table d. Application of Slicer on Table
  e. Pivot Chart