Excel & Advanced Excel
We will embark on a journey to master the art and science of Microsoft Excel, one of the most powerful and widely used spreadsheet applications. Whether you are a beginner eager to learn the basics or an experienced user looking to enhance your skills, this course is designed to cater to all proficiency levels.
Excel is not just a tool; it’s a gateway to efficiency, accuracy, and productivity. As we navigate through this syllabus, we will explore the fundamental functionalities of Excel, starting with the essentials such as navigating the interface, creating and formatting spreadsheets, and utilizing basic formulas and functions.
Our journey won’t stop there. We will delve into intermediate and advanced topics, including data analysis, data visualization, and automation through macros. You will discover how to transform raw data into meaningful insights, streamline workflows, and make informed decisions using Excel’s robust features.
Throughout the course, hands-on exercises and real-world examples will reinforce your understanding and provide practical applications for your newfound skills. Whether you are a student, professional, or enthusiast, the Excel Syllabus Course aims to empower you with the expertise to tackle complex tasks with confidence.
Get ready to unlock the full potential of Excel and elevate your spreadsheet proficiency. Let’s dive into the world of formulas, functions, charts, and data manipulation, shaping you into an Excel maestro.
Excel & Advanced Excel Syllabus
Module 1: Basic Excel (Duration: 10Hrs )
- Text to Columns
- Concatenate
- The Concatenate Function
- The Right Function with
- Concatenation
- Absolute Cell References
- Data Validation
- Time and Date Calculations
- Conditional Formatting
- Exploring Styles and
- Clearing Formatting
- Using Conditional
- Formatting to Hide Cells
- Using the IF Function
- þÿChanging the Value if
- false Condition to Text
- Pivot Tables
- Creating a Pivot Table
- Specifying PivotTable Data
- Changing a PivotTables
- Calculation
- Filtering and Sorting a
- PivotTable
- Creating a PivotChart
- Grouping Items
- Updating a PivotTable
- Formatting a PivotTable
- Using Slicers
- Charts
- Creating a Simple Chart
- Charting Non-Adjacent
- Cells
- Creating a Chart Using the
- Chart Wizard
- Modifying Charts
- Moving an Embedded
- Chart
- Sizing an Embedded Chart
- Changing the Chart Type
- Chart Types
- Changing the Way Data is
- Displayed
- Moving the Legend
- Formatting Charts
- Adding Chart Items
- Formatting All Text
- Formatting and Aligning
- Numbers
- Formatting the Plot Area
- Formatting Data Markers
- Pie Charts
- Creating a Pie Chart
- Moving the Pie Chart to its
- Own Sheet
- Adding Data Labels
- Exploding a Slice of a Pie
- Chart
- þÿData Analysis “ Overview
- types of Data Analysis
- Data Analysis Process
Working with Range Names - Copying Name using
- Formula Autocomplete
- Range Name Syntax Rules
- Creating Range Names
- Creating Names for
- Constants
- Managing Names
- Scope of a Name
- Editing Names
- Applying Names
- Using Names in a Formula
- Viewing Names in a Workbook
- Copying Formulas with
- Names
Difference between Tables - and Ranges
- Create Table
- Table Name
Managing Names in a Table - Table Headers replacing
- Column Letters
- Propagation of a Formula in a Table
Resize Table
Remove Duplicates
Convert to Range
Table Style Options
Table Styles
Cleaning Data with Text Functions
Removing Unwanted Characters from Text
Extracting Data Values from Text
Formatting Data with Text Functions
Advance Excel (Duration: 15Hrs)
Module 2: Date Formats (Duration: 2Hrs)
Converting Dates in Serial Format to Month-Day-Year Format
Converting Dates in Month-Day-Year Format to Serial Format
þÿObtaining Today s Date
Finding a Workday after Specified Days
Customizing the Definition of a Weekend
Number of Workdays between two given dates
Extracting Year, Month, Day from Date
Extracting Day of the Week from Date
Obtaining Date from Year, Month and Day
Calculating Years, Months and Days between two dates
Module 3: Conditional Formatting (Duration: 2Hrs)
Highlight Cells Rules
Top / Bottom Rules
Data Bars
Color Scales
Icon Sets
New Rule
Clear Rules
Manage Rules
Module 4: Sorting (Duration: 2Hrs)
Sort by Text
Sort by Numbers
Sort by Dates or Times
Sort by Cell Color
Sort by Font Color
Sort by Cell Icon
Sort by a Custom List
Sort by Rows
Sort by more than one Column or Row
Module 5: Filtering (Duration: 2Hrs)
Filter by Selected Values
Filter by Text
Filter by Date
Data Analysis with Excel
Filter by Numbers
Filter by Cell Color
Filter by Font Color
Filter by Cell Icon
Clear Filter
Advanced Filtering
Filter Using Slicers
Module 6: Other Topics (Duration: 2Hrs)
Subtotals with Ranges
Subtotals
Nested Subtotals
Quick Analysis
Quick Analysis with TOTALS
Sum
Average
Count
%Total
Running Total
Sum of Columns
Module 7: Lookup Functions (Duration: 3Hrs)
Using the VLOOKUP Function
Using VLOOKUP Function with range lookup TRUE
Using VLOOKUP Function with range lookup FALSE
Using the HLOOKUP Function
Using HLOOKUP Function with range lookup FALSE
Using HLOOKUP Function with range lookup TRUE
Using the INDEX Function
Using MATCH Function
Module 8: Pivoting (Duration: 2Hrs)
PivotTables
Creating PivotTable
Recommended PivotTables
PivotTable Fields
PivotTable Areas
Nesting in the PivotTable
Filters
Slicers
Summarizing Values by other Calculations
PivotTable Tools
Using Pictures in Column Charts
Band Chart
Thermometer Chart
Gantt Chart
Waterfall Chart
Sparklines
Pivot Charts
PivotChart from PivotTable
PivotChart without a PivotTable
Working with Multiple Sheets
Multiple Worksheets with same Structure
Creating a Formula across Multiple Worksheets
Summarizing Data in Multiple Worksheets
What-If A