Excel - Part 1
Contents
Lesson 1: Getting Started with Microsoft Excel 2016.......
TOPIC A: Identify the Elements of the Excel Interface.
TOPIC B: Create a Basic Worksheet
Lesson 2: Performing Calculations..
TOPIC A: Create Formulas in a Worksheet
TOPIC B: Insert Functions in a Worksheet
TOPIC C: Reuse Formulas
Lesson 3: Modifying a Worksheet
TOPIC A: Manipulate Data.
TOPIC B: Insert, Manipulate, and Delete Cells, Columns, and Rows.
TOPIC C: Search For and Replace Data
TOPIC D: Spell Check a Worksheet.
Lesson 4: Formatting a Worksheet.
TOPIC A: Modify Fonts
TOPIC B: Add Borders and Colors to Cells
TOPIC C: Apply Number Formats
TOPIC D: Align Cell Contents.
TOPIC E: Apply Cell Styles.
Lesson 5: Printing Workbook Contents.
TOPIC A: Define the Basic Page Layout for a Workbook
TOPIC B: Refine the Page Layout and Apply Print Options
Lesson 6: Managing Large Workbooks
TOPIC A: Format Worksheet Tabs.
TOPIC B: Manage Worksheets..
TOPIC C: Manage the View of Worksheets and Workbooks
Lesson 7: Customizing the Excel Environment.
TOPIC A: Customize General and Language Options
TOPIC B: Customize Formula Options.
TOPIC C: Customize Proofing and Save Options.
TOPIC D: Customize the Ribbon and Quick Access Toolbar.. the Functionality of Excel by Enabling Add-Ins
TOPIC E: Customize TOPIC F: Customize Advanced and Trust Center Options
Excel - Part 2
Contents
Lesson 1: Creating Advanced Formulas
TOPIC A: Apply Range Names
TOPIC B: Use Specialized Functions.
Lesson 2: Analyzing Data with Logical and Lookup Functions
TOPIC A: Use Text Functions.
TOPIC B: Use Logical Functions
TOPIC C: Use Lookup Functions
TOPIC D: Use Date Functions
TOPIC E: Use Financial Functions
Lesson 3: Organizing Worksheet Data with Tables.
TOPIC A: Create and Modify Tables
TOPIC B: Sort and Filter Data
TOPIC C: Use Subtotal and Database Functions to CalculateData
Lesson 4: Visualizing Data with Charts.
TOPIC A: Create Charts..
TOPIC B: Modify and Format Charts.
TOPIC C: Create a Trendline.
TOPIC D: Create Advanced Charts.
Lesson 5: Analyzing Data with PivotTables, Slicers, and PivotCharts.
TOPIC A: Create a PivotTable.
TOPIC B: Filter Data by Using Slicers
TOPIC C: Analyze Data with PivotCharts.
Lesson 6: Inserting Graphics.....
TOPIC A: Insert and Modify Graphic Objects
TOPIC B: Layer and Group Graphic Objects
TOPIC C: Incorporate SmartArt.
Lesson 7: Enhancing Workbooks.
TOPIC A: Customize Workbooks
TOPIC B: Manage Themes
TOPIC C: Create and Use Templates.
TOPIC D: Protect Files
TOPIC E: Preparing a Workbook for Multiple Audiences. Keyboard Shortcut Quick Reference Sheet
Excel - Part 3
Contents
Lesson 1: Automating Worksheet Functionality.
TOPIC A: Update Workbook Properties.
TOPIC B: Create and Edit a Macro
TOPIC D: Add Data Validation Criteria.
TOPIC C: Apply Conditional Formatting.
Lesson 2: Auditing Worksheets..
TOPIC A: Trace Cells
TOPIC B: Troubleshoot Invalid Data and Formula Errors
TOPIC C: Watch and Evaluate Formulas
TOPIC D: Create a Data List Outline
Lesson 3: Analyzing and Presenting Data.
TOPIC A: Create Sparklines
TOPIC B: Create Scenarios
TOPIC C: Perform a What-If Analysis
TOPIC D: Perform a Statistical Analysis with the Analysis ToolPak
TOPIC E: Create Interactive Data with Power View.
Lesson 4: Working with Multiple Workbooks.
TOPIC A: Consolidate Data
TOPIC B: Link Cells in Different Workbooks
TOPIC C: Merge Workbooks
Lesson 5: Exporting Excel Data.
TOPIC A: Export Excel Data
TOPIC B: Import a Delimited Text File
TOPIC C: Integrate Excel Data with the Web.
TOPIC D: Create a Web Query...
Lesson 6: Advanced Data Management Concepts
TOPIC A: Import and Export XML Data.
TOPIC B: Create Excel Forms
Keyboard Shortcut Quick Reference Sheet
Excel - Part 4
Contents
Worksheet & Functions
Exercise 1: Simple mathematical calculations
Exercise 2: Marksheet Version 1
Exercise 3: Marksheet Version 2
Exercise 4: Stock Register Version 1
Exercise 5: Stock register version 2
Exercise 6: Changing fonts
Exercise 7: Mathematical and Statistical functions
SUM Function.
PRODUCT Function
AVERAGE Function
MAX Function.
MIN Function
COUNT Function.
Exercise 8: Effects of blank cells on AVERAGE function
Exercise 9: Mathematical functions (ROUND, ABS, INT & MOD)
ROUND Function
ABS Function
INT Function
MOD Function.
Exercise 10: Stock register for items with 2 units.
Exercise 11: Formatting numbers
Exercise 12: Bank OD Account passbook.
Exercise 13: Date functions.
TODAY Function
NOW Function.
DATE Function
YEAR Function
MONTH Function
DAY Function..
EOMONTH Function
NETWORKDAYS Function
NETWORKDAYS.INTL Function
Exercise 14: String functions
UPPER Function.
LOWER Function
PROPER Function
REPLACE Function
CONCATENATE Function
LEFT Function
RIGHT Function
MID Function..
LEN Function
FIND Function.
Exercise 15: IF function
Exercise 16: Nested If Function.
Exercise 17: Marksheet Version 4
Exercise 18: Salary Sheet
Exercise 19: Aging Analysis Sheet
Exercise 20: Sales register with GST calculations
Exercise 21: Data validation
Exercise 22: Logical functions (OR & AND)
Exercise 23: Name Manager
Exercise 24: Conditional formatting
Exercise 25: SUMIF & SUMIFS functions
SUMIF Function.
SUMIFS Function.
Exercise 26: COUNT(IF/IFS), AVERAGE(IF/IFS) & COUNTBLANK
COUNIF Function...
COUNTIFS Function.
AVERAGEIF Function
AVERAGEIFS Function
COUNTBLANK Function
Exercise 27: Daily attendance sheet
Exercise 28: Daily wages sheet
Exercise 29: VLOOKUP function.
Exercise 30: VLOOKUP with Approx. Match
Exercise 31: MATCH and INDEX function.
Exercise 32: Financial functions to calculate depreciation.
SLN Function
DB Function.
Exercise 33: Financial function to calculate Future value (FV)
Exercise 34: FV on interest compounded monthly on lumpsum
Exercise 35: FV on investment done monthly
Exercise 36: FV on monthly investment compounded quarterly
EFFECT Function.
NOMINAL Function
Exercise 37: Financial functions (RATE, NPER, PV)
RATE Function
NPER Function.
PV Function
Exercise 38: Financial function to calculate EMI (PMT, IPMT & PPMT).
PMT Function.
IPMT Function
PPMT Function.
Data Analysis
Exercise 39: Sorting data.
Exercise 40: Filtering data.
Exercise 41: Using Advanced filter.
Exercise 42: Removing duplicates from records in a data
Exercise 43: Paste Special
Pasting Formulas.
Pasting Formulas and Number Formats
Pasting Values
Pasting Values and Number Formats.
Pasting Formats..
Pasting Data Validation.
Pasting all Without Borders
Pasting Column Widths.
Changing Orientation
Set Print Area
Remove Print Area..
Repeating Top Rows on Every Page
Inserting Page Break
Removing Page Break
Repeat column at left
Change the page order of printing.
Page Break Preview.
Normal View
Page Layout View.
Inserting Header
Inserting Footer
Adjusting the Page to Fit.
Center on Page
Excel - Part 1
Contents
Lesson 1: Getting Started with Microsoft Excel 2016.......
TOPIC A: Identify the Elements of the Excel Interface.
TOPIC B: Create a Basic Worksheet
Lesson 2: Performing Calculations..
TOPIC A: Create Formulas in a Worksheet
TOPIC B: Insert Functions in a Worksheet
TOPIC C: Reuse Formulas
Lesson 3: Modifying a Worksheet
TOPIC A: Manipulate Data.
TOPIC B: Insert, Manipulate, and Delete Cells, Columns, and Rows.
TOPIC C: Search For and Replace Data
TOPIC D: Spell Check a Worksheet.
Lesson 4: Formatting a Worksheet.
TOPIC A: Modify Fonts
TOPIC B: Add Borders and Colors to Cells
TOPIC C: Apply Number Formats
TOPIC D: Align Cell Contents.
TOPIC E: Apply Cell Styles.
Lesson 5: Printing Workbook Contents.
TOPIC A: Define the Basic Page Layout for a Workbook
TOPIC B: Refine the Page Layout and Apply Print Options
Lesson 6: Managing Large Workbooks
TOPIC A: Format Worksheet Tabs.
TOPIC B: Manage Worksheets..
TOPIC C: Manage the View of Worksheets and Workbooks
Lesson 7: Customizing the Excel Environment.
TOPIC A: Customize General and Language Options
TOPIC B: Customize Formula Options.
TOPIC C: Customize Proofing and Save Options.
TOPIC D: Customize the Ribbon and Quick Access Toolbar.. the Functionality of Excel by Enabling Add-Ins
TOPIC E: Customize TOPIC F: Customize Advanced and Trust Center Options
Excel - Part 2
Contents
Lesson 1: Creating Advanced Formulas
TOPIC A: Apply Range Names
TOPIC B: Use Specialized Functions.
Lesson 2: Analyzing Data with Logical and Lookup Functions
TOPIC A: Use Text Functions.
TOPIC B: Use Logical Functions
TOPIC C: Use Lookup Functions
TOPIC D: Use Date Functions
TOPIC E: Use Financial Functions
Lesson 3: Organizing Worksheet Data with Tables.
TOPIC A: Create and Modify Tables
TOPIC B: Sort and Filter Data
TOPIC C: Use Subtotal and Database Functions to CalculateData
Lesson 4: Visualizing Data with Charts.
TOPIC A: Create Charts..
TOPIC B: Modify and Format Charts.
TOPIC C: Create a Trendline.
TOPIC D: Create Advanced Charts.
Lesson 5: Analyzing Data with PivotTables, Slicers, and PivotCharts.
TOPIC A: Create a PivotTable.
TOPIC B: Filter Data by Using Slicers
TOPIC C: Analyze Data with PivotCharts.
Lesson 6: Inserting Graphics.....
TOPIC A: Insert and Modify Graphic Objects
TOPIC B: Layer and Group Graphic Objects
TOPIC C: Incorporate SmartArt.
Lesson 7: Enhancing Workbooks.
TOPIC A: Customize Workbooks
TOPIC B: Manage Themes
TOPIC C: Create and Use Templates.
TOPIC D: Protect Files
TOPIC E: Preparing a Workbook for Multiple Audiences. Keyboard Shortcut Quick Reference Sheet
Excel - Part 3
Contents
Lesson 1: Automating Worksheet Functionality.
TOPIC A: Update Workbook Properties.
TOPIC B: Create and Edit a Macro
TOPIC D: Add Data Validation Criteria.
TOPIC C: Apply Conditional Formatting.
Lesson 2: Auditing Worksheets..
TOPIC A: Trace Cells
TOPIC B: Troubleshoot Invalid Data and Formula Errors
TOPIC C: Watch and Evaluate Formulas
TOPIC D: Create a Data List Outline
Lesson 3: Analyzing and Presenting Data.
TOPIC A: Create Sparklines
TOPIC B: Create Scenarios
TOPIC C: Perform a What-If Analysis
TOPIC D: Perform a Statistical Analysis with the Analysis ToolPak
TOPIC E: Create Interactive Data with Power View.
Lesson 4: Working with Multiple Workbooks.
TOPIC A: Consolidate Data
TOPIC B: Link Cells in Different Workbooks
TOPIC C: Merge Workbooks
Lesson 5: Exporting Excel Data.
TOPIC A: Export Excel Data
TOPIC B: Import a Delimited Text File
TOPIC C: Integrate Excel Data with the Web.
TOPIC D: Create a Web Query...
Lesson 6: Advanced Data Management Concepts
TOPIC A: Import and Export XML Data.
TOPIC B: Create Excel Forms
Keyboard Shortcut Quick Reference Sheet
Excel - Part 4
Contents
Worksheet & Functions
Exercise 1: Simple mathematical calculations
Exercise 2: Marksheet Version 1
Exercise 3: Marksheet Version 2
Exercise 4: Stock Register Version 1
Exercise 5: Stock register version 2
Exercise 6: Changing fonts
Exercise 7: Mathematical and Statistical functions
SUM Function.
PRODUCT Function
AVERAGE Function
MAX Function.
MIN Function
COUNT Function.
Exercise 8: Effects of blank cells on AVERAGE function
Exercise 9: Mathematical functions (ROUND, ABS, INT & MOD)
ROUND Function
ABS Function
INT Function
MOD Function.
Exercise 10: Stock register for items with 2 units.
Exercise 11: Formatting numbers
Exercise 12: Bank OD Account passbook.
Exercise 13: Date functions.
TODAY Function
NOW Function.
DATE Function
YEAR Function
MONTH Function
DAY Function..
EOMONTH Function
NETWORKDAYS Function
NETWORKDAYS.INTL Function
Exercise 14: String functions
UPPER Function.
LOWER Function
PROPER Function
REPLACE Function
CONCATENATE Function
LEFT Function
RIGHT Function
MID Function..
LEN Function
FIND Function.
Exercise 15: IF function
Exercise 16: Nested If Function.
Exercise 17: Marksheet Version 4
Exercise 18: Salary Sheet
Exercise 19: Aging Analysis Sheet
Exercise 20: Sales register with GST calculations
Exercise 21: Data validation
Exercise 22: Logical functions (OR & AND)
Exercise 23: Name Manager
Exercise 24: Conditional formatting
Exercise 25: SUMIF & SUMIFS functions
SUMIF Function.
SUMIFS Function.
Exercise 26: COUNT(IF/IFS), AVERAGE(IF/IFS) & COUNTBLANK
COUNIF Function...
COUNTIFS Function.
AVERAGEIF Function
AVERAGEIFS Function
COUNTBLANK Function
Exercise 27: Daily attendance sheet
Exercise 28: Daily wages sheet
Exercise 29: VLOOKUP function.
Exercise 30: VLOOKUP with Approx. Match
Exercise 31: MATCH and INDEX function.
Exercise 32: Financial functions to calculate depreciation.
SLN Function
DB Function.
Exercise 33: Financial function to calculate Future value (FV)
Exercise 34: FV on interest compounded monthly on lumpsum
Exercise 35: FV on investment done monthly
Exercise 36: FV on monthly investment compounded quarterly
EFFECT Function.
NOMINAL Function
Exercise 37: Financial functions (RATE, NPER, PV)
RATE Function
NPER Function.
PV Function
Exercise 38: Financial function to calculate EMI (PMT, IPMT & PPMT).
PMT Function.
IPMT Function
PPMT Function.
Data Analysis
Exercise 39: Sorting data.
Exercise 40: Filtering data.
Exercise 41: Using Advanced filter.
Exercise 42: Removing duplicates from records in a data
Exercise 43: Paste Special
Pasting Formulas.
Pasting Formulas and Number Formats
Pasting Values
Pasting Values and Number Formats.
Pasting Formats..
Pasting Data Validation.
Pasting all Without Borders
Pasting Column Widths.
Changing Orientation
Set Print Area
Remove Print Area..
Repeating Top Rows on Every Page
Inserting Page Break
Removing Page Break
Repeat column at left
Change the page order of printing.
Page Break Preview.
Normal View
Page Layout View.
Inserting Header
Inserting Footer
Adjusting the Page to Fit.
Center on Page
