google407ec42f1ae5ff0e.html
 

Advance Excel

01

Microsoft Excel Fundamentals

  1. Launching Excel

  2. Microsoft Excel Start-up Screen

  3. Introduction to the Excel Interface

  4. Customizing the Excel Quick Access Toolbar

  5. More on the Excel Interface

  6. Understanding the Structure of an Excel Worksheet

  7. Saving an Excel Document

  8. Opening an Existing Excel Document

  9. Common Excel Shortcut Keys

  10. Font formats, Alignment, Borders, etc

  11. Basic conditional formatting

02

Entering and Editing Text and Formulas

  1. Entering Text to Create Spreadsheet Titles

  2. Working with Numeric Data in Excel

  3. Entering Date Values in Excel

  4. Working with Cell References

  5. Creating Basic Formulas in Excel

  6. Relative Versus Absolute Cell References in Formulas

  7. Understanding the Order of Operation

03

Working with Basic Excel Functions

  1. The structure of an Excel Function

  2. Working with the SUM() Function

  3. Working with the MIN() and MAX() Functions

  4. Working with the AVERAGE() Function

  5. Working with the COUNT() Function

  6. Adjacent Cells Error in Excel Calculations

  7. Using the AutoSum Command

  8. Excel's AutoSum Shortcut Key

  9. Using the AutoFill Command to Copy Formulas

04

Modifying an Excel Worksheet

  1. Moving and Copying Data in an Excel Worksheet

  2. Inserting and Deleting Rows and Columns

  3. Changing the Width and Height of Cells

  4. Hiding and Unhiding Excel Rows and Columns

  5. Renaming an Excel Worksheet

  6. Deleting an Excel Worksheet

  7. Moving and Copying an Excel Worksheet

05

Formatting Data in an Excel Worksheet

  1. Working with Font Formatting Commands

  2. Changing the Background Colour of a Cell

  3. Adding Borders to Cells

  4. Formatting Data as Currency Values

  5. Formatting Percentages

  6. Using Excel's Format Painter

  7. Creating Styles to Format Data

  8. Merging and Centring Cells

  9. Using Conditional Formatting

06

Protecting Excel

  1. File Level Protection

  2. Workbook, Worksheet Protection

  3. Add New Range fro protection

07

Text Functions

  1. Upper, Lower, Proper

  2. Left, Right, Mid

  3. Trim, Len, Exact

  4. Find, Search, Text, Number, Code, Substitute

  5. Concatenate, Replace, REPT

08

Mathematical Functions

  1. Sum, Sumif, Sumifs

  2. Average, Averageif, Averageifs

  3. Count, Countif, Countifs,

  4. Average, Averageif, Averageifs

  5. Max, Min

  6. If, ifs

  7. and, or, not and with if, or with if, not with if

09

Date & Time Functions

  1. Today, Now

  2. Day, Month, Year, YearFrac

  3. Date, Date if

  4. EOMonth, EDate, Weekday, WeekNUM, Workday

  5. Hour, Minute, Second

9-A

Financial Functions in Excel

  1. Future Value (FV)

  2. FVSCHEDULE

  3. Present Value (PV)

  4. Net Present Value (NPV)

  5. XNPV

  6. PMT

  7. PPMT

  8. Internal Rate of Return (IRR)

  9. Modified Internal Rate of Return (MIRR)

  10. XIRR

  11. NPER

  12. RATE

  13. EFFECT

  14. NOMINAL

  15. SLN

10

Advance Paste Special options

  1. Paste Formulas, Paste Formats

  2. Paste Validations

  3. Transpose Table

  4. Plan Paste

11

Slicer, Charts and Graphs

  1. Working with Charts

  2. Using SLICERS, Filter data with Slicers 

12

Pivot Tables

  1. Creating Simple Pivot Tables 

  2. Basic and Advanced Value Field Setting 

  3. Classic Pivot table 

  4. Choosing Field 

  5. Filtering PivotTables 

  6. Modifying PivotTable Data 

  7. Grouping based on numbers and Dates 

  8. Calculated Field & Calculated Items 

13

Printing Worksheet

  1. using of Page Break Preview

  2. Setting of Margin, Orientation & Page Size

  3. Print Area, Page Break and Print Titles

  4. Scale to Fit, Gridlines , Heading

14

Data

  1. Import & Export Data

  2. Sorting and Filtering

  3. Text to Column, FlashFill, Remove Duplicate

  4. Data Validation

  5. Consolidate the Data

  6. What-if-Analysis

  7. Scenario Manager

  8. Goel Seek

  9. Data Table

  10. Forecast Sheet

  11. Outline

  12. Group

  13. UnGroup

  14. Sub Total

  15. Solver Add-in

15

Lookup Functions

  1. Vlookup / HLookup 

  2. Index and Match 

  3. Creating Smooth User Interface Using Lookup 

  4. Nested Vlookup 

  5. Reverse Lookup using Choose Function 

  6. Worksheet linking using Indirect 

  7. Vlookup with Helper Column

16

VBA Macro

  1. What Is VBA?

  2. What Can You Do with VBA?

  3. Recording a Macro

  4. Procedure and functions in VBA

Thanks for submitting!