ADVANCE
EXCEL SYLLABUS
Advance Excel
33 139 17
Lessons Subtopics Resources
COURSE SYLLABUS
❖ Introduction
• Excel User Interface
• Home Tab
❖ Shortcut Keys
• Daily Usage of Shortcut for Smart Work
❖ Basic Math’s, Fill and Autofill
• Basic Math’s, Fill and Autofill
❖ Constant (Cell Reference)
• Absolute Reference
• Relative Reference
• Mixed Reference
• Constant Assignment
❖ Define Name
• Give Name to Specified region by Define Name or Name Box
• Define Name
• How to apply Define Name
❖ Regular use basic formulas
• Max, Min, Large & Small
• Rank & Product
• Assignment - B Formula
❖ Count for Selective Counting
• Count, CountA, Countblank
• Countif & Countifs for Specified criteria in region
• Assignment – C Formula
❖ Sum for Selective Adding up
• Sum
• Sumif & Sumifs for specified criteria in region
• Assignment – S Formula
❖ Average for mean of Selection)
• Average
• Averageif & Averageifs for specified criteria region
• Assignment – A Formula
❖ Test Yourself
• Ass_1 (Based on C & S Formulas)
• Ass_2 (Based on C & S Formulas)
• C_S_Assignment
❖ Dfunction
• Applying criteria through Dfunction for different conditions
• Application in D-sum, Max, Min, Average & Count
❖ Working with Text
• Istext – Text Showing True or False
• Upper, Lower, Proper – Convert a text in three different cases
• Len – Find the Length of String
• Using Left and Right for String Extraction
• Find and Mid Working together to extract Parts of Strings
• Concatenate – Joining text from different cells
• Exact - Working in Two Text
• Assignment – Ass_T
❖ Math Function
• Rounding to Fractional Values
• Mod, Quotient for Working out Remainders
• Generating a Random Number
• Ceiling & Floor to round to multiple of significance
• Roman convert Arabic to roman
• Other formulas to work on numbers
❖• Date & Time
Assignment Functions
– Ass_M
• Calculating days, months & year between two dates
• Extracting day, month or year
• Inserting current dates & times
• Calculating working days
• Extracting Seconds, Minute or Hours
• Calculating Birthdays
• Assignment – Ass_D & Ass-BD
• Assignment AE_1
❖ Logical Functioning
• The Syntax of IF
• Nesting the IF statement to put other formulas in excel
• Statistical IF Statement to put other formulas in excel
• Use the AND operator to reduce Quantity of Nested IF’S
• The NOT Operator within AND and OR Statements
• Assignment – Ass_1
• Assignment – Ass_2
• Assignment – Ass_3
❖• Sort & Filter
Assignment – Ass_4
•• Assignment
Sorting Data– Ass_5
•
• Multiple Sorting through Custom Sort
• Create Custom List for Custom Sorting
• Filter the data basic & advance filter
• Filter the data through logic of number or text filter
• Subtotal, Group & Ungroup – fastest sorting & summarizing data
❖ Using data tools for improve productivity
• Text to Column split cell data
• Remove duplicates avoid duplications
• Consolidate to gather data
• The Choose Lookup function
• Assignment- T_C, R_D, Consolidate
❖ Performing Lookup in Excel
• Basic lookup
• Vertical Lookup, True/False arguments & application
• Horizontal Lookup & Application
• Nested Lookups in Excel
• The Match Function
• The Index Function Syntax
• How to stop nonexistent Row or column Lookups in Index
• Assignment – Lookup, Vlookup, Hlookup, Index, Match,
Choose, Qtn
❖ Data Validation & Conditional Formats
• Data Validation avoid wrong input in validate cells
• Using data bars, color scale, & ready formulas & Custom Range
• Using Custom formulas & Custom Range
❖ Protecting Cells & Workbooks
• Protect complete sheet / by using editable cell range
• Working on what user can edit while protection
• Protect complete workbook by encryption document
• Assignment Emp_protection
1
❖ Working on Pivot Table
• What is a Pivot Table
• Steps to create a Pivot Table in Excel
• Rearranging Fields in a Pivot Table
• Creating a second (or more) Pivot Table on the same data
• Moving/ Removing a Pivot Table
• Making use of the report filter Option
• Sorting Pivot Table columns
• Creating your Own Custom Pivot Table Style
• Copying a Pivot Table Style between Workbooks
• Disabling and enabling grand and subtotals
• Filtering Columns and rows within Pivot Table
• Assignment -Pivot Table
❖ Graphical Display of Tables
• Inserting Pivot Charts through pivot table
• Design, Layout & Formatting the Charts
❖ Macros
• Recording Macros
• Applying keyboard shortcut & running macros
• Using macro button through developer tab
• Application of Macros
• Assignment - Macros
❖ Sparklines
• Creating a Sparkline in Excel
• Change the design of sparklines
• Dealing with Empty Cells
• Removing Sparklines from a Sheet
❖ Auditing & Troubleshooting Formulas
• Description of Tracer Arrows
• Tracing Precedents and Dependents
• Remover Tracer Arrows
• Error Checking Using Audition Tools
• Step by step Processing of formula to help with
❖ What-If Analysis
Troubleshooting
• Goal Seek
• Scenario Manager
• Data Table
❖ Custom Views in Excel
• Creating a Custom View of a Worksheet
• Changing from One Custom view to another
• Editing a Custom View
• How to Delete a Custom View
❖ Page Setup & Printing
• Putting Margins for page, Headers & Footers
• Setting Page Orientation & Page Size
• Inserting Header & Footer
• Setting print title for repeating rows and columns
• Viewing gridlines & Headings
❖ Project 1 – MIS Report
• Sales Invoice Tracker
• Project To Do List
• Profit & Loss Statement
❖• Project 2 – Budget
• Simple Monthly Budget
• Wedding Budget
• Monthly College Budget
❖ Project 3 – Attendance Tracker
• Employee Absence Schedule
• Employee Attendance Tracker