MS Excel In 15 days
MS Excel Course Outline
Class 1:
Introduction to MS Excel
- Objective: Familiarize students with the MS Excel
interface and basic functions.
- Topics:
- Overview of Excel Interface: Ribbon,
Worksheets, Cells, Rows, and Columns
- Basic Navigation and Cell Selection
- Creating, Saving, and Opening Workbooks
- Entering Data: Text, Numbers, Dates
- Exercise:
- Create a new workbook.
- Enter data in a range of cells.
- Save and close the workbook.
- Assignment: Click Here
Class 2: Basic
Formatting and Cell Styles
- Objective: Learn to format cells and use basic
styling features.
- Topics:
- Formatting Cells: Font, Size, Color,
Borders, Fill
- Aligning Text: Horizontal and Vertical
Alignment
- Using Cell Styles and Themes
- Number Formatting: Currency, Percentage,
Date
- Exercise:
- Format a given dataset with different
styles.
- Apply number formatting to a list of
financial data.
- Assignment: Click Here
Class 3: Working
with Formulas and Functions
- Objective: Introduce basic formulas and functions.
- Topics:
- Basic Formulas: Addition, Subtraction,
Multiplication, Division
- Common Functions: SUM, AVERAGE, MIN, MAX,
COUNT
- Using Cell References: Relative,
Absolute, Mixed
- Exercise:
- Create a worksheet to calculate totals
and averages for a set of data.
- Use various functions to summarize data.
Class 4: Data
Sorting and Filtering
- Objective: Learn to sort and filter data
effectively.
- Topics:
- Sorting Data: Ascending, Descending,
Custom Sort
- Filtering Data: Using AutoFilter, Custom
Filters
- Advanced Filtering: Using Criteria
- Exercise:
- Sort a dataset by different columns.
- Apply filters to display specific data.
Class 5: Working
with Charts and Graphs
- Objective: Create and customize charts and graphs.
- Topics:
- Types of Charts: Column, Line, Pie, Bar,
etc.
- Creating Charts from Data
- Customizing Charts: Titles, Legends, Data
Labels
- Exercise:
- Create various types of charts based on
provided data.
- Customize chart elements and analyze data
visually.
Class 6: Data
Validation and Conditional Formatting
- Objective: Implement data validation rules and use
conditional formatting.
- Topics:
- Data Validation: Setting Rules, Drop-Down
Lists
- Conditional Formatting: Highlight Cells,
Data Bars, Color Scales
- Exercise:
- Apply data validation to a range of
cells.
- Use conditional formatting to highlight
cells based on conditions.
Class 7: Advanced
Formulas and Functions
- Objective: Explore advanced Excel functions and
formulas.
- Topics:
- Logical Functions: IF, AND, OR
- Lookup Functions: VLOOKUP, HLOOKUP,
INDEX, MATCH
- Text Functions: CONCATENATE, LEFT, RIGHT,
MID
- Exercise:
- Use advanced functions to solve complex
problems.
- Create a formula that combines text from
multiple cells.
Class 8:
PivotTables and PivotCharts
- Objective: Learn to create and manipulate
PivotTables and PivotCharts.
- Topics:
- Creating PivotTables: Data Source,
Fields, Values
- Customizing PivotTables: Filters,
Sorting, Grouping
- Creating PivotCharts from PivotTables
- Exercise:
- Create a PivotTable from a dataset.
- Analyze data using PivotCharts.
Class 9: Working
with Multiple Worksheets and Workbooks
- Objective: Manage and link data across multiple
worksheets and workbooks.
- Topics:
- Navigating Multiple Worksheets
- Linking Data Between Worksheets and
Workbooks
- Consolidating Data from Multiple Sources
- Exercise:
- Create a workbook with multiple sheets.
- Link data between sheets and summarize information.
Class 10: Excel
Tables and Structured References
- Objective: Use Excel Tables for structured data
management.
- Topics:
- Creating and Formatting Tables
- Using Structured References in Formulas
- Sorting and Filtering in Tables
- Exercise:
- Convert a dataset into an Excel Table.
- Use structured references in formulas.
Class 11:
Introduction to Macros and VBA
- Objective: Understand the basics of Macros and VBA.
- Topics:
- Recording and Running Macros
- Basic VBA Programming Concepts
- Editing Macros in the VBA Editor
- Exercise:
- Record a simple macro to automate a
repetitive task.
- Write a basic VBA script to perform a
specific function.
Class 12: Data
Analysis Tools: Solver and Goal Seek
- Objective: Utilize advanced data analysis tools in
Excel.
- Topics:
- Using Goal Seek to Find Desired Results
- Solver Add-in: Setting Up and Solving
Optimization Problems
- Exercise:
- Use Goal Seek to determine input values
for a target result.
- Set up Solver to solve a business
problem.
Class 13: Using
Excel for Financial Analysis
- Objective: Apply Excel for financial modeling and
analysis.
- Topics:
- Financial Functions: PV, FV, NPV, IRR
- Creating Financial Models: Budgeting,
Forecasting
- Exercise:
- Build a simple financial model for
budgeting.
- Use financial functions to analyze
investment scenarios.
Class 14: Data
Import and Export
- Objective: Learn to import data from various sources
and export Excel data.
- Topics:
- Importing Data: CSV, Text Files, Other
Excel Files
- Exporting Data to Different Formats: PDF,
CSV
- Exercise:
- Import a dataset from a CSV file into
Excel.
- Export a workbook to PDF format.
Class 15: Review
and Final Project
- Objective: Review key concepts and complete a final
project.
- Topics:
- Review of Key Features and Functions
- Final Project Instructions: A
comprehensive task combining multiple skills
- Exercise:
- Complete a project that incorporates data
entry, formatting, formulas, charts, and analysis.
- Present and discuss the final project.
*******************************************
