Excel Advanced

This course builds on the skills and concepts taught in Excel Beyond Basics course. Students will learn details of PivotTables – beyond SUM! They will create and apply Named Ranges. Students will work with backbone functions like IF, AND, OR, IFERROR, VLOOKUP, MATCH, and INDEX. Calculations with Dates and Date functions is an important part of this course. In addition, students will learn about data validation. Macros will expose the students to automation inside Excel. Finally, students will learn techniques of Data merging, Protection, Conditional Formatting, and Charting.

PivotTables 
The Four Critical Pivot Table Options
Format Numbers (Not Cells)
Extracting Months, Quarters, and Years from a Date 
Style and Design Components to Format a Report
The Report: Sum, Count
The Report: Concept Of Data Cubes
The Problem Of Pivot Report not taking New Data
Creating Pivot Tables With Dynamic Named Range 
The Analytics:
Show Amount as Of Total
Year-To-Date Totals
Slicers: One Sheet, Multiple Reports (only for 2010 & 2013)
Create One Sheet Per Region With One Click: Use Report Filter

Named Range 
Static Named Range 
Constant Named Range 
Dynamic Named Range (with formulas)
 
The Seven Critical Functions (Use with Named Range) 
=IF() Function; =IF(IF(IF(IF))) [Nested IFs]
=AND() Function
=OR() Function
=VLOOKUP() Function
=IFERROR() Function
=MATCH() & =INDEX() Functions: for Reverse VLOOKUP, from Left to Right Lookup

Dates
Harmonizing Date Data
Static & Dynamic Date: Use of =TODAY() Function
Date Calculations
Using Advance Date Functions
= EDATE()
= NETWORKDAYS.INTL ()
= DATEDIF()
Using “&” Function in Dates

Protection
Sheet Protection Superior to PDF: NO Right Click, NO Left Click. NO Copying Allowed!!
Pivot Table Sheet: NO Copy-Paste, but Allowing Slicers to Work
Hiding Formulas
Protecting Parts of a Worksheet
Protecting Hidden Worksheets
Protecting a Workbook

Data Validation
Concept of Master Data
Data Validation using Named Range
A Drop-down List
Number & Dates Validation
Which Cell Have Data Validation – How To Find?

Excel Collabaration
Merging Workbooks: Let Excel Copy-Paste the Reports Sent by all Managers on a Standard Format
Sharing Workbooks: More than One Person Working on the Same File Simultaneously!!

Macros
Why You Should NOT Write a Macro?
Difference Between .XLSX and .XLSM Files
Macro in a File or Macro inside Excel Program
Recording a Macro
Using Pre-Written Macros
       
Conditional Formatting 
The Power of Data Bars in a Cell
The Constraints of Icons and Color Scales?
Highlighting Duplicates
Multiple Conditions to Format Same Range: Top 3 and Bottom 3 Condition in One Range
Conditional Formatting Using Formulas: Changing Cell Colors with Change in Date
Conditional Formatting a Pivot Table
Which Cell have Conditional Formatting – How To Find?

Charts
Creating Chart With One Key Press!
The FOUR Critical Steps
Beyond Standard Charts
“Others” In “Pie Of Pie” Charts
Country Flags In Column Charts
Trend Line In Line Charts
Combination Charts

Relevant Shortcuts

Demonstration: Interactive Dashboards without Macros