Course Curriculum

Basic Discussion:

▪ Interface of Excel
▪ Quick Access Toolbar
▪ Customize Ribbon
▪ How to add Developer Tab
▪ Search Button
▪ Name Box
▪ Formula Bar
▪ Workbook Background

Worksheet Format:

• Row Height & Column Width Scale.
• Add Borders
• Apply Colors and Patterns
• Format Painter
• Format Painter (Advance)
• Merge Cells, Rotate Text
• Advance Tab of Headers Footers
• Inserting Objects
• Inserting Shapes
• Insert & Edit Picture
• Insert Equation
• Organogram
• Format Numbers & Values

Manage Workbook:

▪ Insert & Delete Worksheet
▪ Duplicate Removal
▪ Hide Columns, Rows and Sheets
▪ Protect Sheet
▪ Protect a Workbook
▪ Advance way to Protect by using Allow Edit Ranges
▪ Split and Freeze a Window
▪ Enter Date Values and using Auto-complete
▪ Custom List
▪ Insert Cell Comments & Note

Print Solutions:

▪ How to print large worksheet properly in A4 size page
▪ Adjust Page Margins and Orientation
▪ Add Print Titles and Grid-lines, rows to repeat at top of each page
▪ Print with column & Row Header
▪ Print with Comment

Data Entry Tricks:

▪ Paste Options
▪ Cell Reference
▪ Relative, Absolute & Mixed Cell References
▪ Dynamic Name Range
▪ Sort
▪ Advance Sort Tricks
▪ Filter
▪ Advance Filter Tricks

Conditional Formatting:

▪ Highlight Cell As Per Various Rules
▪ Top/ Bottom Rules
▪ Data Visualization with Bar Chart using Conditional Formatting

Data Validation:

▪ Drop Down List for Easy Data Entry
▪ Dependent Drop-down List
▪ Create Custom Data Validation Messages
▪ Encrypt a File & Protect Structure from unauthorized Data Entry

Dynamic Table:

▪ How to Insert Excel Table
▪ Various features of Table
▪ Dynamic Calculation in Table
▪ Duplicate Data Remove in Table
▪ Slicer in Table

Formula & Functions:

▪ Difference between Formula & Function
▪ Functions Basic Arithmetic Operations
▪ Edit & Copy Formulas
▪ Cell References practical example
▪ Formula Audit
▪ Text Function
▪ Trim
▪ Vlook-up
▪ Advance V-lookup
▪ Index Match
▪ If/ Ifs /Iferror
▪ Is, CountIf, Sumif, CountIfs, Sumifs, CountA, Countblank, Count, Concatenate, etc)
▪ Detail practical use of logical functions to prepare report with multiple conditions.

Charts:

▪ Create Charts
▪ Move and resize & change Chart
▪ Format and Edit Objects in a Chart
▪ Change Chart Source Data
▪ Change Chart Type and work with Pie Chart.
▪ Dual Axis/ Combo Chart
▪ Sparkline
▪ Forecast Chart

Pivot Table:

• Summarizing Data
• Grouping and Categorizing
• Filter and Sorting
• Calculate Percentages and Ratios
• Identifying Trends and Patterns
• Comparing Data Sets
• Visualizing Data
• Drilling Down into Details
• Handling Missing or Incomplete Data