## I) INTRODUCTION TO EXCELYTICS

#### Understand Excel:

• What is Excel & its complete History
• Basic terminology of Excel
• Object Model of Excel
• Different versions of Excel (97-2003,2007,2010 & 2013) and what’s new in each version of it
• Different file formats - .xls,.xlsx,.xlsm,.xlsb,.xlam,.csv…etc., and when to use which format of Excel
• R1C1 Reference Style VS A1 Reference Style

• #### Formatting:

• Number formatting: General, Number, Currency, Accounting, Short Date, Long Date, Time, Percentage, Fraction, Scientific
• Custom number formatting and where you can create your own number formatting
• Advance techniques in Number formatting

• #### Conditional formatting:

• What is Conditional formatting & how to change the existing formatting using this feature
• Quick format technique to highlight Duplicate/Unique values
• We will discuss more than 25 real time examples of Conditional formatting
• Writing Complex Conditional Formatting rules using formulas
• Interview Related Questions related to Conditional Formatting

• #### Format as Table (FT):

• What is Format as Table
• Differences between normal range and FT
• How to make Dynamic Drop down list using FT
• Multiple filters in a single sheet using FT
• Summarize & Analyze your data real quick using FT
• Structural references in FT and how this feature enable you to write effective formulas
• How to make source data of Pivot Table dynamic by using FT
• Many more other awesome features of FT
• Interview Related Questions related to FT

## II) EXCEL FORMULAS

#### Cell Reference Styles:

• Relative Reference Style
• Absolute Reference Style
• Mixed Reference Style (Row Freeze & Column Freeze)

• We will discuss all above reference styles with examples and will explain how to use these styles in formulas

#### Working with Formulas:

• Understanding the concept of a formula
• Understanding the Mathematical operators & Comparative operators
• Boolean Logic in Excel
• Referencing Functions
• How many ways you can write a formula in a cell – Literal values, Cell References & Formula
• Creating formulas
• Text Functions – Ex: Find, Search, Left, Right, Mid, Len, Trim…..
• Date and Time Functions – Ex: NetWorkDays, EDate, WeekDay….
• Lookup & Reference Functions – Ex: Index, Match, VLookup, Offset, Indirect….
• Mathematical Functions – Ex: Fact, Abs, Mod, RandBetween, Sumifs…
• Statistical Functions – Ex: Countifs, Median, Large, Small, Rank….
• Information Functions – Ex: Cell, IsError, ErrorType, IsLogical….
• Logical Functions – Ex: If, And, Or, Not, IfError
• Financial Functions – Ex: PMT, PPMT, IPMT, PV, RRI, PRICE & NPV.
• What is volatile functions in Excel & list of Volatile Functions
• Formula auditing
• Circular Reference error
• Formula Error Types - #DIV/0,#NULL,#VALUE,#REF,#N/A,#NUM
• How to fix Formula Errors

• As we know that formulas are heart of excel, we teach around 150 formulas to make you awesome in Excel

## III) ANALYZING DATA WITH EXCEL

#### Filter:

• How to apply filters on Text, Date & Numbers
• How to filter your data using Cell color, Font Color, Cell Icon & Values
• Advanced Filter for Complex Criterion

• #### Sorting:

• How to apply sort on Text, Date & Numbers
• How to sort your data using Font color, Text Color & Custom Filter
• Custom sorting

• #### Pivot Table (PT) & Pivot Chart:

• What is Pivot Table
• Insert Pivot Table – different types
• Pivot Table fields Section
• Pivot Table Areas Section (Rows, Columns, Values & Filter)
• How to make your PT dynamic without changing the data source every time
• How to insert Slicers in Pivot Table
• Difference between Slicer and Report Filter
• How to Connect multiple Pivot Tables using Slicers
• How to insert Calculated Field/Formula in PT
• Pivot Table Value field settings
• Insert Timeline in PT
• Grouping/Ungrouping of fields

• #### Power Pivot:

• What is Power Pivot
• How to install/Enable Power Pivot
• What is the use of Power Pivot
• Import Data from different sources (Excel, Text file, Access, SQL etc.,) into Power Pivot
• How to reduce file size drastically using Power Pivot
• Connect to multiple different external datasets that can be refreshed with a single click
• DAX Functions
• Faster calculating than large array formula equivalents using Power Pivot

## IV) CREATING CHARTS AND GRAPHICS

• Various charts – Bar, Column, Line, Area, Pie, Bubble & etc.,
• Combo Charts
• Objects in Charts, Working with Objects of Chart
• Dynamic charts and Dynamic data source for charts

## V) DATA PROTECTION TECHNIQUES

• Worksheet protection
• How to protect specific range
• Allowing users to edit the protected range
• Workbook protection and Encryption

## VI) PRINTING AND VIEWING WORKSHEET

• Print Areas
• Views for a worksheet
• Various printing techniques
• REAL TIME EXERCISES