EXCEL

SIGMA DHYANA

Excel for Data Analytics

Course Syllabus

Module 1: Basic Excel
Objective:
Introduce fundamental Excel functionalities for data manipulation and formatting.
Topics ▼
  • Introduction to Excel
  • Creating and Closing Files
  • Tabs and Groups
  • Exploring Styles and Clearing Formatting
  • Text to Columns
  • Freeze Panes
  • Cell References
  • Relative References
  • Absolute References
  • Data Validation
  • Grouping Items
  • Creating Range Names
  • Removing Unwanted Characters from Text
  • Remove Duplicates
  • Formulas and Functions
  • Find & Replace
  • Flash Fill Series
  • Consolidate
  • Subtotal
  • What-if Analysis
  • Watch Window
  • Formula Auditing
  • Protect Cells, Sheet, Workbook
  • Statistical Functions
  • Hyperlinks to Sheet and Webpage
  • Logical Tests
Hands-on Exercise:
  • Create a basic Excel file with formulas and functions.
  • Apply formatting styles and data validation rules.
Module 2: Conditional Functions
Objective:
Master conditional functions for logical operations and data analysis.
Topics ▼
  • IF Function
  • Nested IF
  • AND Function
  • SUMIF
  • COUNTIF
  • IFERROR
Hands-on Exercise:
  • Implement conditional functions in Excel formulas.
  • Use nested IF statements for complex conditions.
Module 3: Charts
Objective:
Explore chart creation and customization for visual data representation.
Topics ▼
  • Creating Simple Charts
  • Modifying Charts
  • Changing Chart Types
  • Chart Formatting
  • Formatting Chart Items
  • Formatting Plot Area
  • Formatting Data Markers
Hands-on Exercise:
  • Create various types of charts and customize them.
  • Format charts to enhance visual clarity.
Module 4: Date Formats
Objective:
Manipulate date formats and perform date-related calculations.
Topics ▼
  • Converting Dates to Different Formats
  • Obtaining Today’s Date
  • Finding Workdays After Specified Days
  • Extracting Year, Month, Day from Date
  • Extracting Day of the Week from Date
  • Calculating Differences Between Dates
Hands-on Exercise:
  • Convert date formats using Excel functions.
  • Perform date calculations based on given scenarios.
Module 5: Conditional Formatting
Objective:
Apply conditional formatting techniques to highlight data patterns.
Topics ▼
  • Highlight Cells Rules
  • Top / Bottom Rules
  • Data Bars
  • Color Scales
  • Icon Sets
  • New Rule
  • Clearing and Managing Rules
Hands-on Exercise:
  • Apply various conditional formatting rules to Excel data.
  • Manage and clear conditional formatting rules as needed.
Module 6: Sorting
Objective:
Sort data effectively based on different criteria in Excel.
Topics ▼
  • Sort by Text, Numbers, Dates
  • Sort by Cell Color, Font Color, Cell Icon
  • Sort by Custom List
  • Multi-column or Multi-row Sorting
Hands-on Exercise:
  • Sort Excel data using different sorting methods.
  • Perform multi-column sorting operations.
Module 7: Filtering
Objective:
Filter and extract specific data subsets from Excel datasets.
Topics ▼
  • Filter by Selected Values, Text, Date, Numbers
  • Filter by Cell Color
  • Clear Filters
  • Advanced Filtering Techniques
  • Using Slicers for Filtering
Hands-on Exercise:
  • Apply filters and Advance filterings to Excel data based on various criteria.
  • Use slicers to interactively filter data.
Module 8: Lookup Functions
Objective:
Utilize lookup functions to retrieve data from Excel tables efficiently.
Topics ▼
  • VLOOKUP and HLOOKUP Functions
  • INDEX and MATCH Functions
  • Range Lookup Techniques
Hands-on Exercise:
  • Perform lookup operations using VLOOKUP, HLOOKUP, INDEX, and MATCH functions.
  • Apply range lookup techniques in Excel formulas.
Module 9: Pivoting
Objective:
Master PivotTable and PivotChart creation for data summarization and analysis.
Topics ▼
  • Creating PivotTables
  • Recommended PivotTables
  • PivotTable Fields and Areas
  • PivotTable Nesting and Filtering
  • Summarizing Values by Calculations
  • PivotTable Tools and Customizations
  • Creating PivotCharts
  • Using Pictures in Charts
  • Advanced Chart Types: Band, Thermometer, Gantt, Waterfall
  • Sparklines
  • Working with Multiple Sheets
  • Creating a formula across multiple worksheets
  • Summarizing data in multiple worksheets
  • Merging two tables using Append Queries
Hands-on Exercise:
  • Create PivotTables and PivotCharts to summarize and visualize data.
  • Customize PivotTables and PivotCharts for multiple worksheets
Module 10: Macros and VBA
Objective:
Automate tasks in Excel using macros and understand VBA programming.
Topics ▼
  • Introduction to Macros
  • Recording and Running Macros
  • Introduction to VBA (Visual Basic for Applications)
  • Basic VBA Syntax
Hands-on Exercise:
  • Record and run macros to automate repetitive tasks.
  • Write basic VBA scripts for Excel operations.