About Course
Course Objectives
-
Import and manage data from different sources into Excel workbooks.
-
Format worksheets and organize data effectively.
-
Use formulas and functions for data calculations and analysis.
-
Apply conditional formatting and visualization techniques.
-
Create and manage tables, charts, and interactive dashboards.
-
Perform advanced data analysis using Excel tools such as PivotTables, Power Query, and Power Pivot.
-
Transform and model data for reporting and business insights.
-
Build dynamic dashboards to present analytical results clearly.
Course Outline
Module 1: Working with Workbooks and Data
-
Import data from text and CSV files
-
Navigate within workbooks
-
Search and locate data
-
Insert and manage hyperlinks
Module 2: Formatting Worksheets and Workbooks
-
Adjust row height and column width
-
Modify page setup
-
Customize headers and footers
-
Modify workbook views and properties
Module 3: Managing and Formatting Data
-
Insert and delete rows, columns, and cells
-
Apply number formats and cell styles
-
Merge and align cells
-
Use Format Painter and wrap text
Module 4: Working with Tables
-
Create Excel tables
-
Apply table styles
-
Add or remove rows and columns
-
Configure total rows
Module 5: Sorting and Filtering Data
-
Filter records
-
Sort data by multiple columns
Module 6: Working with Formulas and Functions
-
Use relative, absolute, and mixed references
-
Perform calculations using SUM, AVERAGE, MAX, MIN
-
Use COUNT, COUNTA, and COUNTBLANK
-
Apply logical functions such as IF
Module 7: Text Functions
-
Use LEFT, RIGHT, MID functions
-
Apply UPPER, LOWER, LEN
-
Combine text using CONCAT and TEXTJOIN
Module 8: Data Visualization
-
Insert Sparklines
-
Apply conditional formatting
-
Summarize data visually
Module 9: Creating and Formatting Charts
-
Create charts and chart sheets
-
Modify chart elements
-
Apply chart styles and layouts
Module 10: Introduction to Data Analytics
-
Data cleaning techniques
-
Data analytics project phases
-
Qualitative and quantitative analysis
Module 11: Advanced Functions for Data Analysis
-
Logical functions (IF, AND, OR, IFERROR)
-
Date functions (EOMONTH, EDATE, TODAY)
-
Lookup functions (VLOOKUP, HLOOKUP, XLOOKUP)
-
Advanced functions (INDEX, MATCH, OFFSET, CHOOSE)
Module 12: Advanced Excel Data Analysis Tools
-
Data validation and drop-down lists
-
What-if analysis (Goal Seek, Scenario Manager, Data Tables)
-
Working with large datasets
Module 13: Data Modeling with Power Query
-
Import data from external sources
-
Transform and clean data
-
Merge and append queries
-
Load data to pivot tables
Module 14: PivotTables and Power Pivot
-
Creating PivotTables
-
Adding filters, rows, columns, and values
-
Using calculated fields
-
Analyzing data with PivotTables
