Course Content – Data Analytics using
Excel
I) INTRODUCTION TO EXCELYTICS
Understand Excel:
1. What is Excel & its complete History
2. Basic terminology of Excel
3. Spreadsheet environment
4. Object Model of Excel
5. Different versions of Excel (97-2003,2007,2010, 2013, 2016,2019 & 2021)
6. Different file formats - .xls,.xlsx,.xlsm,.xlsb,.xlam,.csv…etc., and when to use which format of Excel
7. How to customize your Excel according to your requirement.
8. R1C1 Reference Style VS A1 Reference Style
Formatting:
1. Number formatting: General, Number, Currency, Accounting, Short Date, Long Date, Time, Percentage,
Fraction, Scientific
2. Custom number formatting and where you can create your own number formatting
3. Advance techniques in Number formatting
Conditional formatting:
1. What is Conditional formatting & how to change the existing formatting using this feature
2. Quick format technique to highlight Duplicate/Unique values
3. We will discuss more than 25 real time examples of Conditional formatting
4. Writing Complex Conditional Formatting rules using formulas
5. Interview Related Questions related to Conditional Formatting
Format as Table (FT):
1. What is Format as Table
2. Differences between normal range and FT
3. How to make Dynamic Drop down list using FT
4. Multiple filters in a single sheet using FT
5. Summarize & Analyze your data real quick using FT
6. Structural references in FT and how this feature enable you to write effective formulas
7. How to make source data of Pivot Table dynamic by using FT
8. Many more other awesome features of FT
9. Interview Related Questions related to FT
II) EXCEL FORMULAS
Cell Reference Styles:
1. Relative Reference Style
2. Absolute Reference Style
3. 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:
1. Understanding the concept of a formula
2. Understanding the Mathematical operators & Comparative operators
3. Boolean Logic in Excel
4. Referencing Functions
5. How many ways you can write a formula in a cell – Literal values, Cell References & Formula
6. Creating formulas
7. Text Functions – Ex: Find, Search, Left, Right, Mid, Len, Trim…..
8. Date and Time Functions – Ex: NetWorkDays, EDate, WeekDay….
9. Lookup & Reference Functions – Ex: Index, Match, VLookup, Offset, Indirect….
10. Mathematical Functions – Ex: Fact, Abs, Mod, RandBetween, Sumifs…
11. Statistical Functions – Ex: Countifs, Median, Large, Small, Rank….
12. Information Functions – Ex: Cell, IsError, ErrorType, IsLogical….
13. Logical Functions – Ex: If, And, Or, Not, IfError
14. Financial Functions – Ex: PMT, PPMT, IPMT, PV, RRI, PRICE & NPV.
15. What is volatile functions in Excel & list of Volatile Functions
16. Formula auditing
17. Circular Reference error
18. Formula Error Types - #DIV/0,#NULL,#VALUE,#REF,#N/A,#NUM
19. 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:
1. How to apply filters on Text, Date & Numbers
2. How to filter your data using Cell color, Font Color, Cell Icon & Values
3. Advanced Filter for Complex Criterion
Sorting:
1. How to apply sort on Text, Date & Numbers
2. How to sort your data using Font color, Text Color & Custom Filter
3. Custom sorting
Pivot Table (PT) & Pivot Chart:
1. What is Pivot Table
2. Insert Pivot Table – different types
3. Pivot Table fields Section
4. Pivot Table Areas Section (Rows, Columns, Values & Filter)
5. How to make your PT dynamic without changing the data source every time
6. How to insert Slicers in Pivot Table
7. Difference between Slicer and Report Filter
8. How to Connect multiple Pivot Tables using Slicers
9. How to insert Calculated Field/Formula in PT
10. Pivot Table Value field settings
11. Insert Timeline in PT
12. Grouping/Ungrouping of fields
Power Pivot & Power Query
1. Intro to Power query & Power Pivot
2. Data manipulations with Power Query
3. Difference between Power Pivot & Pivot
4. How to install/Enable Power Pivot
5. What is the use of Power Pivot
6. Import Data from different sources (Excel, Text file, Access, SQL etc.,) into Power Pivot
7. How to reduce file size drastically using Power Pivot
8. Connect to multiple different external datasets that can be refreshed with a single click
9. DAX Functions
10. Faster calculating than large array formula equivalents using Power Pivot
IV) CREATING CHARTS AND GRAPHICS
1. Various charts – Bar, Column, Line, Area, Pie, Bubble & etc.,
2. Combo Charts
3. Objects in Charts, Working with Objects of Chart
4. Dynamic charts and Dynamic data source for charts
V) DATA PROTECTION TECHNIQUES
1. Worksheet protection
2. How to protect specific range
3. Allowing users to edit the protected range
4. Workbook protection and Encryption
VI) Dashboard
1. Dashboard Creation and picking different charts
2. Importance of slicers and connecting with slicers
3. Sheet lay out change & making interactive dash boards
